To achieve optimal performance, SQL Server and Windows depend on one another, as does Windows and the hardware...
platform. A problem in any one area can severely impact the other two. Unfortunately, gleaning insight into SQL Server's hardware usage through Windows in any supported manner has been difficult, at least with older versions of SQL Server. Only a few commands were available to capture statistics, yet this information was needed in troubleshooting performance issues. To address these needs, SQL Server 2005 ships with the SQL OS a means to view key resource usage within SQL Server, in Windows and in the hardware platform.
In this tip, I will address some common questions to help introduce you to SQL Server 2005's SQL OS.
What is the SQL OS?
The SQL OS is a layer between SQL Server and Windows where it is possible to gain visibility into how SQL Server uses Windows memory, the CPU, schedulers, tasks, I/O, disk drives and so on. This layer gives DBAs a look at how SQL Server uses resources at a much lower level. By understanding how resources are being used, you can better tune SQL Server for workloads.
The SQL OS is also the layer where CLR code is executed. Based on my research, the SQL OS is the first implementation of a layer between applications where other applications may run in future SQL Server versions. It appears as if the primary goal of this type of architecture is to improve the scalability and concurrency of applications by executing key processes in a separate layer.
As a DBA, what inherent benefits will I derive from the SQL OS?
How do I access the SQL OS?
The SQL OS has no direct GUI per se to monitor resource use, as is the case when managing a database with SQL Server 2005 Management Studio. However, Microsoft gives you more than 20 dynamic management views and functions to query. Below is a list of the SQL OS dynamic management objects and the information they provide.
|SQL Server 2005 Dynamic Management Objects -- SQL OS|
|ID||Dynamic Management Object||Description|
|1||sys.dm_os_buffer_descriptors||Buffer pool usage by database, file and page in addition to the page type (i.e., data, index or system related). Rows are included for user, system and resource databases|
|2||sys.dm_os_child_instances||User instances to support SQL Server Express Edition|
|3||sys.dm_os_cluster_nodes||Name of each node in a Windows cluster|
|4||sys.dm_os_hosts||Host client component (i.e., SQL Server, CLR (common language runtime), SNAC (SQL Native Client), etc.) in specific memory space|
|5||sys.dm_os_latch_stats||Single entry for each of more than 100 latches (light-weight locks) with the count of the latch type, aggregate wait time in milliseconds and max wait time for a single count for the latch type|
|6||sys.dm_os_loaded_modules||All DLLs loaded in Windows with version and location in the file system|
|7||sys.dm_os_memory_cache_clock_hands||Account for all objects in SQL Server's cache (i.e., databases, query plans, event notifications, etc.)|
|8||sys.dm_os_memory_cache_counters||Memory allocation in KB for SQL Server cache entries|
|9||sys.dm_os_memory_cache_entries||Individual cache entries with disk I/O use, context switches, etc.|
|10||sys.dm_os_memory_cache_hash_tables||Each cached value entry with cache hits and misses to determine if the query plans are to be reused or recompiled|
|11||sys.dm_os_memory_clerks||Memory clerks for each SQL Server application, such as CLR, connection pooling, full-text search, Service Broker and so on|
|12||sys.dm_os_memory_objects||Memory objects, including cached objects, Service Broker, SQL scheduler, statements and so on, with the page size and count for the object's use|
|13||sys.dm_os_memory_pools||Memory pool for items including the lock manager, database mirroring, Service Broker, etc., with max entries and free entries|
|14||sys.dm_os_performance_counters||Performance Monitor counters and values related to SQL Server for a specific instance|
|15||sys.dm_os_scheduler||Maps schedulers from memory to a specific CPU used by SQL Server|
|16||sys.dm_os_stacks||Call stack for SQL Server processes|
|17||sys.dm_os_sys_info||Windows system information, such as the CPU tick count, hyperthread ratio, physical and virtual memory, as well as the worker and scheduler counts|
|18||sys.dm_os_tasks||State of Windows tasks related to the session, scheduler and requests, in addition to the state, context switches, I/O usage and so on|
|19||sys.dm_os_threads||Threads used by SQL Server to identify if the thread was created by SQL Server, when it was started, memory usage time in the kernel and user mode|
|20||sys.dm_os_virtual_address_dump||Pages in virtual memory with the usage, protection levels and size|
|21||sys.dm_os_wait_stats||A listing of current wait stats, replacing DBCC SQLPERF('WAITSTATS')|
|22||sys.dm_os_waiting_tasks||Queued tasks with the wait type, duration and blocks|
|23||sys.dm_os_workers||Thread worker information to include the I/O count, tasks processed, memory and thread address|
Where can I find more information about the SQL OS?
Here are some great resources to learn more about the SQL OS at a detailed level:
- Slava Oks' WebLog: A new platform layer in SQL Server 2005 to exploit new hardware capabilities and their trends
- Slava Oks' WebLog: Set of new features in SQL Server 2005 enabled by SQLOS
- SQL Server 2005 Books Online: NUMA Scenarios
- Bob Dorr's SQL Server 2005 OS Foundation Elements
The SQL OS is here to stay as a means of bridging the gap between SQL Server, Windows and the underlying hardware platform. Since you can access all of the information via SQL Server, troubleshooting the lower levels of the platform should ease the previous performance-tuning challenges. Stay tuned for more SQL Server components moving to a SQL OS-like layer with the same level of valuable information.
About the author: Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Kadlec is also the SearchSQLServer.com Performance Tuning expert. Visit Ask the Expert to pose a question to him.