dmv io讀寫高的sql
This is the second article in a continuing series on the many system tables, views, procedures and functions available in SQL Server. In the first part of this series, Discovering SQL server instance information using system views, you learned how to discover many attributes of a SQL Server instance you have been given access to. In this part, we will continue the journey and see what else we can find.
這是有關SQL Server中可用的許多系統表，視圖，過程和函數的連續系列文章的第二篇。 在本系列的第一部分“ 使用系統視圖發現SQL Server實例信息”中 ，您學習了如何發現已獲得訪問權限SQL Server實例的許多屬性。 在這一部分中，我們將繼續前進，看看還能找到什么。
You have access to at least one instance of SQL Server on one host. Are there any others? If so, can you connect to them and find out about them too? Let’s find out!
sqlserver讀寫分離實現。 您可以在一臺主機上訪問至少一個SQL Server實例。 還有其他嗎？ 如果是這樣，您是否可以連接到它們并了解它們？ 讓我們找出答案！
SQL Server stores instance information in the Windows registry under the key:
SQL Server將實例信息存儲在Windows注冊表中的項下：
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL
HKLM \軟件\ Microsoft \ Microsoft SQL Server \實例名稱\ SQL
sqlserver轉mysql。 Where “HKLM” is an abbreviation for the Registry section called “HKEY_LOCAL_MACHINE”. If you can access the registry on the server running the instance you are connected to, it might look like this in regedit:
其中“ HKLM”是注冊表部分“ HKEY_LOCAL_MACHINE”的縮寫。 如果您可以在運行所連接實例的服務器上訪問注冊表，則在regedit中可能如下所示：
This is from my laptop, which shows that there are two instances of SQL Server defined. However, maybe you cannot login to the host running SQL Server. Still, there may be a way for you to get the list of instances.
這是從我的筆記本電腦上獲得的，它表明已定義了兩個SQL Server實例。 但是，也許您無法登錄到運行SQL Server的主機。 盡管如此，您還是可以通過某種方式獲取實例列表。
io讀寫。 In the previous article, I mentioned the system stored procedure xp_cmdshell and the controversy surrounding it. If you are convinced, as I am, that the controversy is really a tempest in a teapot, we can use the command line version of regedit to get the same information. First, enable xp_cmdshell:
在上一篇文章中，我提到了系統存儲過程xp_cmdshell以及圍繞它的爭議。 如果您像我一樣確信該爭論確實是一場大風暴，那么我們可以使用regedit的命令行版本來獲取相同的信息。 首先，啟用xp_cmdshell：
?
USE master;??
GO??
EXEC sp_configure 'show_advanced_options', '1';??
RECONFIGURE;
GOEXEC sp_configure 'xp_cmdshell', '1';??
RECONFIGURE;
GONow, use the command line to get the information:
現在，使用命令行獲取信息：
?
EXEC xp_cmdshell 'reg query "HKLM\Software\Microsoft\Microsoft SQL
Server\Instance Names\SQL"';When I run this on my laptop, I receive output that contains:
設備讀寫錯誤。 當我在筆記本電腦上運行它時，我收到包含以下內容的輸出：
?
MSSQLSERVER????REG_SZ????MSSQL12.MSSQLSERVER
SQLEXPRESS2008R2????REG_SZ????MSSQL10_50.SQLEXPRESSIt’s easy to see that the instance names are in the first column of data returned. If you are able to see other instances, go ahead and see if you can connect to them. You may have permission to do and you will have a new world to explore!
很容易看到實例名稱在返回數據的第一列中。 如果您可以看到其他實例，請繼續查看是否可以連接到它們。 您可能有權這樣做，您將有一個新的世界可以探索！
Now, if you’re still worried about xp_cmdshell, go ahead and turn it off, using the same script as above but setting the new value for xp_cmdshell to 0 instead of 1.
現在，如果您仍然擔心xp_cmdshell ，請繼續使用上面的相同腳本將其關閉，但是將xp_cmdshell的新值設置為0而不是1。
sqlallochandle on sql_handle。 We’re not done with the registry, though. There is actually a whole set of undocumented, extended stored procedures that can be used to discover details about a SQL Server instance. Note that, because they are undocumented, they may change at any service pack or cumulative update without notice. Do not build production scripts using them!
但是，我們還沒有完成注冊表。 實際上，有一整套未記錄的擴展存儲過程，可用于發現有關SQL Server實例的詳細信息。 請注意，由于未記錄它們，因此它們可能會在任何Service Pack或累積更新中更改，恕不另行通知。 不要使用它們來構建生產腳本！
To discover how your SQL Server instance was set up, try this query:
要發現如何設置SQL Server實例，請嘗試以下查詢：
?
EXECUTE master.sys.xp_instance_regenumvalues 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLSERVER\Setup';On my laptop, this query produced 17 lines of output. Your mileage may vary. One item that I found interesting is this:
sqlserver數據庫讀寫分離， 在我的筆記本電腦上，此查詢產生了17行輸出。 你的旅費可能會改變。 我發現有趣的一項是：
This is the default location for new data files created by SQL Server, say when a new database is created. Of course that can be overridden when you create a database, so what if you want to know where the databases are really located? The view sys.master_files holds the answer:
這是SQL Server創建的新數據文件的默認位置，例如在創建新數據庫時。 當然，在創建數據庫時可以覆蓋它，那么如果您想知道數據庫的實際位置怎么辦？ 視圖sys.master_files包含答案：
?
SELECT * FROM sys.master_files;Yields, for me (partially):
sqlserver讀寫分離方案？ 對我來說（部分）收益：
Here, we can see where the files are really located for each database. The file_id can be used to get usage information about each file:
sqlserver讀寫分離？ 在這里，我們可以看到每個數據庫的文件實際位置。 file_id可用于獲取有關每個文件的使用情況信息：
?
SELECT mf.name,mf.physical_name, mf.size, fs.size_on_disk_bytes
FROM sys.master_files mf
CROSS APPLY
(SELECT * from sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) fsWHERE fs.file_id = mf.file_id
) fs;This uses the sys.dm_io_virtual_file_stats data management function. On my system, my results begin:
這使用sys.dm_io_virtual_file_stats數據管理功能。 在我的系統上，我的結果開始：
Which shows me the database name, the file path, the current size used in SQL Server pages (a page is 8 KB) and the current size on disk in bytes. Virtual file statistics also include the number of reads and writes against each file and the master file view also has columns showing the auto-grow parameters. See the references for more details.
oracle v$sql。 其中顯示了數據庫名稱，文件路徑，SQL Server頁面（頁面為8 KB）中使用的當前大小以及磁盤上的當前大小（以字節為單位）。 虛擬文件統計信息還包括對每個文件的讀寫次數，并且主文件視圖還具有顯示自動增長參數的列。 有關更多詳細信息，請參見參考。
The data_space_id column in sys.master_files identifies the filegroup. That means we can get a bit more information from the sys.filegroups view. On my laptop that’s not too exciting:
sys.master_files中的data_space_id列標識文件組。 這意味著我們可以從sys.filegroups視圖中獲得更多信息。 在我的筆記本電腦上，這并不令人興奮：
On a large system, with many filegroups (perhaps to support table partitioning), this would be a longer list. Note that the data_space_id column is there to join on, should you wish to.
sql view視圖。 在具有許多文件組（可能支持表分區）的大型系統上，這將是一個較長的列表。 請注意，如果需要，可以在其中加入data_space_id列。
Another interesting aspect of a SQL Server instance is connection possibilities. While you’re discovering things about an instance, it’s good to check out these as well. Try this query on any instance of SQL Server:
SQL Server實例的另一個有趣的方面是連接的可能性。 在發現有關實例的內容時，最好也檢查一下這些內容。 在任何SQL Server實例上嘗試以下查詢：
?
SELECT * FROM sys.dm_exec_connections;If I do this on my laptop, it’s a little boring:
如果我在筆記本電腦上進行此操作，那會有些無聊：
Though on a busier system things are more interesting:
盡管在繁忙的系統上，事情更有趣：
(For this screenshot, I connected to an AdventureWorks database hosted on Azure. You can see differing connection protocols (shared memory for my laptop, TCP for Azure), authentication, encryption and so on.
（對于此屏幕截圖，我連接到Azure托管的AdventureWorks數據庫。您可以看到不同的連接協議（筆記本電腦的共享內存，Azure的TCP），身份驗證，加密等。
Even more interesting is to combine this with active session information like this:
更加有趣的是將其與活動會話信息結合在一起，如下所示：
?
SELECT c.session_id, c.protocol_type, c.auth_scheme, c.client_net_address, s.host_name, s.login_name, s.login_time
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id;On the AdventureWorks connection, that gives me:
在AdventureWorks連接上，這給了我：
If you want to find out what those clients are doing, you could use sp_who2. That gives me:
如果要了解這些客戶端在做什么，可以使用sp_who2。 這給了我：
(There are more columns to the right.) If I may plug a fellow MVP, though, I’d recommend Adam Machanic’s excellent sp_whoisactive, which can give you much more information. Find a link to it in the “See Also” section below.
（右邊有更多列。）但是，如果我可以插入MVP伙伴，我建議Adam Machanic出色的sp_whoisactive，它可以為您提供更多信息。 在下面的“另請參見”部分中找到指向它的鏈接。
Using some of the queries above, you’ve found out who else is currently connected to the same instance as you. Maybe you’d like to know how many ways connections can be made? The place to start is with sys.endpoints. On my laptop:
使用上面的一些查詢，您發現了當前還有誰與您連接到同一實例。 也許您想知道可以進行多少種連接？ 起點是sys.endpoints。 在我的筆記本電腦上：
?
SELECT * FROM sys.endpoints;Yields:
產量：
You may see other endpoints, especially on an HADR (High Availability, Disaster Recovery) systems. Here though, I see 5 methods I can use to connect to SQL Server, TCP, shared memory, named pipes and VIA (Virtual Interface Adapter). There are actually two TCP endpoints, a normal one and one for the Dedicated Admin connection. If you want to know the port numbers for the TCP connections, you can find them here:
您可能會看到其他端點，尤其是在HADR（高可用性，災難恢復）系統上。 不過，在這里，我看到了5種可用于連接到SQL Server，TCP，共享內存，命名管道和VIA（虛擬接口適配器）的方法。 實際上有兩個TCP端點，一個正常，一個專用于Admin專用連接。 如果您想知道TCP連接的端口號，可以在這里找到它們：
?
SELECT * FROM sys.dm_tcp_listener_states;Which gives me:
這給了我：
On my local system. There are also a number of specialized endpoint views:
在我的本地系統上。 還有許多專用端點視圖：
In this article, we’ve continued our exploration of SQL Server using the built-in dynamic management views. If you were just starting out at a new company, using the techniques in this article and its predecessor would give you a well-rounded understanding of your new environment, and we haven’t even started to look at the databases yet!
在本文中，我們將繼續使用內置的動態管理視圖對SQL Server進行探索。 如果您只是剛成立一家新公司，那么使用本文中的技術及其前身將使您對您的新環境有一個全面的了解，而我們甚至還沒有開始研究數據庫！
Other articles in this series:
本系列的其他文章：
翻譯自: https://www.sqlshack.com/discovering-sql-server-information-using-built-dynamic-management-view-dmvs/
dmv io讀寫高的sql
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
