보미네

affinity mask
1. 인스턴스 별 코어 할당시 sql server instance 가 사용할 cpu core 를 선택하기 위해서
2. affinity i/o mask 가 설정되어 있을때 일반 cpu 작업을 sql server 가 사용하지 않게 하려고. 그래서 sp_configure 에 같이 살아요~

sql server 의 affinity mask 는 작업 관리자에서 프로세스 > 선호도 설정에서도 설정 할 수 있지만, sp_configure 에서 설정하는 것이 sql server 에서 가장 좋습니다. 작업 관리자에서 설정하고 affinity mask 잘못 설정될 경우 예상치 못한 결과가 나올 수 있습니다. soft numa 설정은 http://blogs.msdn.com/b/sqlserverfaq/archive/2010/06/28/how-to-set-soft-numa-for-sql-server-2008-r2.aspx 를 읽어 보세요.

affinity i/o mask 
32bit 시스템에서 메모리가 부족한 상황에서 대단히 많은 i/o 프로세싱을 할 때(dirty memory 내리기, 신규 페이지 퍼올리기, 페이지 체크 등) cpu core 를 더 할당해 주기 위한 방법 입니다. 또한 같은 코어를 cpu affinity mask 와 i/o affinity mask 로 등록 하시면 안됩니다.64비트에서는 상대적으로 메모리가 충분 할 수 있으므로 설정할 필요가 없습니다. (메모리를 더 사다 꼽는게 훨씬 유리함) http://blogs.msdn.com/b/psssql/archive/2010/11/19/how-it-works-io-affinity-mask-should-i-use-it.aspx 에서는 64bit 에서는 무시하라고 되어 있습니다.



.

오 ~ 멋지다...

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