보미네

오 ~ 멋지다...

More on DMV changes in SQL 2008 R2 SP1

Following our last post by Mehmet about new support and troubleshooting DMV’s, here are additional details on supportability DMV changes in SQL 2008 R2 SP1 and how you might use them. These changes are also available in “Denali”, the upcoming major release of SQL Server. With SQL 2008 R2 SP1 coming up soon, please give them a try and let us know what you think.

We’ll take a peek at these DMV changes as well as talk about in what circumstances these DMVs would come in handy. Note that all DMVs/DMF documented below require VIEW SERVER STATE permission.

1. Extended sys.dm_exec_query_stats with 4 new columns (total/last/min/max_row)

sys.dm_exec_query_stats is a very widely used DMV that provides  useful information in analyzing query performance. To troubleshoot long running queries, it is also helpful to have total/min/max/last row counts information in order to separate queries that are simply returning a large number of rows from those problematic ones due to, say, a missing index or  a bad query plan.

 2. sys.dm_os_volume_stats(f.database_id, f.file_id)

This is a new DMF (Dynamic Management Function) that helps check the free space on the partitions the SQL server instance resides on. The catalog view sys.database_files provides stats such as size per database file, however, without information about free space on the partition, the information is less actionable – a database file cannot autogrow even when the size is small if there is not sufficient space left on the partition.

For example, you can use the following T-SQL statements to get the stats for the current database.

select database_id, f.file_id, total_bytes, available_bytes from sys.database_files as f cross apply sys.dm_os_volume_stats(DB_ID(), f.file_id)

DMF definition:

3. sys.dm_os_windows_info

This new DMV provides information on the OS the SQL Server instance is running on, specifically including the following:

• Windows Release
• Windows Service Pack Level
• Windows SKU
• OS language version

DMV definition:

 Without this DMV, it is very difficult to get such information without calling into Windows APIs.

It’s also worth pointing out that a related change we made to existing DMV sys.dm_os_sys_info is to add two new columns (virtual_machine_type, virtual_machine_type_desc) to provide information when the SQL Server instance is running in a virtual machine environment.

 4. sys.dm_server_registry

This DMV provides registry key information related to the overall configuration/installation of the SQL Server instance.

DMV definition:

 The following groups of registry keys are covered in this DMV:

1)      To help find out what SQL Server services are available on the host machine

• HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\ObjectName
• HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\ImagePath
• HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\Start

2)      To help find out what SQL Agent services are available on the host machine

• HKLM\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT\ObjectName
• HKLM\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT\ImagePath
• HKLM\SYSTEM\CurrentControlSet\Services\ SQLSERVERAGENT\Start
• HKLM\SYSTEM\CurrentControlSet\Services\ SQLSERVERAGENT\DependOnService

3)      To find out the current version of SQL Engine

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ CurrentVersion

4)      To help with instance detectability, etc. the following registry keys are helpful

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ Parameters

5)      To help troubleshoot connectivity issues (intermittent connections, high latency, etc.) that may be a result of misconfiguration

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ AdminConnection\TCP
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Np
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Sm
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ TCP\ IP1… IPAll
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Via

6)      To help troubleshoot application issues due to incorrect settings in SQLServerAgent

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\ErrorLoggingLevel
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\JobHistoryMaxRows
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\JobHistoryMaxRowsPerJob
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\WorkingDirectory

 5. sys.dm_server_services

This DMV provides information about three services related to SQL Server, namely SQL Server, SQL Agent and Fulltext (only available as of Denali). The DMV includes the following information about the current SQL instance:

6. sys.dm_server_memory_dumps

This DMV provides information on memory dumps that have been generated as a result of a recent crash, for instance. Dump type may be minidump, all-thread dump or full dump.

DMV definition:

Thanks for reading this far. We’d love to hear your feedback once you get a chance to try them out.

-Xin Jin

1. Use of Linked Servers – You can find out the linked servers that being used in your environment using the sysservers system catalog.
2. Use of XML documents – You would have to find out if any queries or procedures perform any kind of XML data manipulation or use sp_xml_preparedocument.
3. Extended Stored Procedures or sp_OAcreate calls
   1. Extended stored procedure usage can be identified by inspecting the SQL Server Errorlog and searching for the word using. The first XSP calls is logged in the Errorlog in the following manner: Using ‘<dll name>’ version ‘<version>’ to execute extended stored procedure ‘<XSP name>’.
   2. If you are using sp_OAcreate, then this information would be logged in the SQL Errorlog for the first invocation of sp_OAcreate using the same pattern mentioned above. The only difference would be that the DLL name would be odsole70.dll.
4. Query Plans which are larger than 8 KB in size
   1. If you are using SQL Server 2000, query syscacheobjects and search for entries which have values greater than 1 for the pagesused column.
   2. If you are using SQL Server 2005, search for plans greater than 8KB using the DMV sys.dm_exec_cached_plans. Inspect the size_in_bytes column value and search for entries which have values greater than 8192 bytes.
5. Use of SQLCLR (Applicable to SQL Server 2005 and above) – Check the memory usage for SQLCLR clerk using DBCC MEMORYSTATUS.
6. Backups using larger MAXTRANSFERSIZE parameter – In such a case find out BACKUP DATABASE/LOG commands and inspect the MAXTRANSFERSIZE parameter value. The lowest size that can be provided is 65536 bytes.
7. Using Network Packet Size higher than 8192 bytes. You can find all connections on SQL Server 2005 that use more than 8KB of connection using the following DMV: select * from sys.dm_exec_connections:

memtoleave region viewer

http://blogs.msdn.com/b/sqlosteam/archive/2010/10/24/mapping-virtual-address-space-in-t-sql.aspx

모 팀장님께서 sql server worker thread 가 bpool 메모리에 있다고 강의를 한 모양이다.

동생 : 형? 정말이에요? 그렇다 카더라 하던데요....
나 : 아니 bpool 외 영역에 있지.......
동생 : 그럼 어떻게 그 사이즈를 모니터링 해요?
나 : 몰라~ 그냥 process thread count 보고 역 추적해야쥐....-_-
동생 : 다른데 보일 만한데나 증명할 방업이 없어요?
나 : 씨봉....바쁜데....

그래도 해보기로 했다. 왜냐구? 조낸 유명한 분이 한 강의란다. bpool 에 worker thread 메모리가 포함 된다고....
아닌데.......그럼 지금 쓰고 있는책 다 어퍼야 하는데...아~ 짱나....

간단히 증명해 보기로 한다.
일단 간단한 테이블을 만든다.

CREATE TABLE [dbo].[tblx](
 [idx] [int] NULL
) ON [PRIMARY]
GO

insert into tblx values (1)

음 프로시저도 하나 만들어야지....

CREATE proc [dbo].[a] 
as 
begin tran  
update tblx set idx = idx + 1
waitfor delay '00:00:59'
commit tran
GO

음 무식하게 세션 만들어 하기 싫으니 ostress 하나 깔아야 겠다.

자 이제 준비 끝!
net stop mssql$sql2008r2
net start mssql$sql2008r2

직 후 process 와 dbcc memorystatus 모니터링 결과

Process 에 sqlserver 모니터링 Private Bytes 376MB 에 Thread Count 87개 buffer pool 영역의 database 1319 page 가 로드되어 있음

ostress 로 아까 만든 프로시저 동작 시킨 후 결과

private byte 는 1.6GB 로 늘었으나 bpool 은 아주 조금 늘었다. 참고로 dbcc memory status 의 buffer pool 의 단위는 page 이다. 

안  믿어져요......좀 더 테스트 해주세요 졸라 유명한 분이라구요.....알았다. 좀 더 테스트 해줄께....
테이블 사이즈 2GB 짜리를 SQL 에 로드해 보았다.

sp_spaceused 로 2GB 짜리 확인 후 테이블 로딩

declare @cchar200 char(200)
select @cchar200 =cchar200 from t_others

로딩 후 결과

buffer pool 의 사이즈만 늘었고 나머지는 늘지 않았다. buffer pool database 만 272535 page 이므로, 2.23GB 가 올라왔다.

Private Byte 는 아까와 변함없다. worker thread 는 아직 유지하고 있기 때문에 줄지 않았다. ostress 종료 시키면 좀 있다. thread count 를 줄여 나갈 것이다.

실제 SQL Server 가 사용하는 Thread Stack 모니터링 툴
http://sqlsql.tistory.com/148