Track resource usage with SQL Server 2005 SQL OS

Jeremy Kadlec, Edgewood Solutions

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

    Requires Free Membership to View

    Login

key processes in a separate layer.

As a DBA, what inherent benefits will I derive from the SQL OS?

  • The ability to review lower-level performance problems such as page swapping, memory thrashing, threading, scheduler or worker issues, I/O contention and maxed out CPUs that pertain directly to SQL Server.

  • The ability to use Transact-SQL dynamic memory allocation and CPU affinity masks without having to restart SQL Server.

  • The ability to use a Dedicated Administrator Connection to connect and troubleshoot SQL Server when the server is unable to accept user connections.

    • 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

    Source: SQL Server operating system related dynamic management views

    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:

    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.

    This was first published in August 2006

    Join the conversationComment

    Share
    Comments

      Results

      Contribute to the conversation

      All fields are required. Comments will appear at the bottom of the article.

      Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

      Back to top