Share via

SQLOS's DMVs

  • Article

SQL Server 2005 has a large number of DMVs that you can take advantage of especially when you try to understand behavioral characteristics of your SQL Server 2005 installation. DMVs expose enormous amount of information but how can you make use of it? What questions you can answer using DMVs? 

 

So in order to make you life a bit easier, I would like to start series of posts dedicated to SQLOS DMVs with emphasis on actual problems.

 

Following set of DMVs is exposed by SQLOS or SQLOS related infrastructures.

 

sys.dm_os_sys_info

sys.dm_os_schedulers

sys.dm_os_waiting_tasks

sys.dm_os_wait_stats

sys.dm_os_systhrteads

sys.dm_os_virtual_address_dump

sys.dm_os_latch_stats

sys.dm_os_hosts

sys.dm_os_buffer_descriptors

sys.dm_os_perf_counters

sys.dm_os_ring_buffers

sys.dm_os_tasks

sys.dm_os_workers

vsys.dm_os_memory_clerks

sys.dm_os_memory_cache_counters

sys.dm_os_memory_cache_clock_hands

sys.dm_os_memory_cache_hash_tables

sys.dm_os_memory_cache_entries

dbcc memorystatus

 

I listed them in a random order J. You can find description for each one of them in BO so I won’t repeat the description here. The main point is figure out, understand, how to make use of them.

 

Once we are done with all posts you will be able to:

- Find out if you need to buy more CPUs

- Find out if you need to buy more memory

- Find out if you running under memory pressure

- Find out if you have problems with disks

- Find out if you need to move to 64bit based solution

- Find out if you need to change your application in order to take advantage of force parameterization

- Find out what applications you loaded in SQL Server address space

- Find out if SQL Server is paged out and if it affects performance of your application

- Find out if your hardware is NUMA

- And much more

 

So here it goes:

sys.dm_os_sys_info

  1. Q. How many sockets does my machine have?

select

cpu_count/hyperthread_ratio AS sockets

from

sys.dm_os_sys_info

 

  1. Q. Is my machine hyper threaded?

Well unfortunately you can’t derive this information using this DMV today though there is a column called hyperthread_ratio. On the other hand this column can tell you:

Q. How many either cores or logical CPU share the same socket?

select

hyperthread_ratio AS cores_or_logical_cpus_per_socket

from

sys.dm_os_sys_info

 

  1. Q. Does my 32 bit system have /3GB or /Userva switch in boot.ini?

select

     CASE

           WHEN virtual_memory_in_bytes / 1024 / (2048*1024)

< 1 THEN 'No switch'

           ELSE '/3GB'

     END

from sys.dm_os_sys_info

 

  1. Q. How much physical memory my machine has?

select

      physical_memory_in_bytes/1024 AS physical_memory_in_kb

from

      sys.dm_os_sys_info

  1. Q. How many threads/workers SQL Server would use if the default value in sp_configure for max worker threads is zero:

select

      max_workers_count

from

      sys.dm_os_sys_info

  1. Q. What is a size of AWE window or what is the max amount of memory right now that can be used by QO, QE and Caches simultaneously when SQL Server running in AWE mode?

select

      bpool_visible

from

      sys.dm_os_sys_info

 

I will be happy to answer your questions.

Comments

  • Anonymous
    August 22, 2006
    I'll bite Slava... What is DBCC MEMORYSTATUS doing in a list of DMVs?

  • Anonymous
    August 22, 2006
    This is a good question :-). In SQL Server 2005 we didn't have a chance to put all information from dbcc memorystatus into dmvs. In the next version we will. Some of the output from this command is very important for you to answer some of the interesting questions such as what is a reason for long waits on RESOURCE_SEMAPHORE_QUERY_COMPILE?  I will explain it as we go.

  • Anonymous
    August 22, 2006
    I'm looking forward to this series, keep them coming Slava. My growing collection of good DMV queries is a little weak on SQLOS specific stuff.

  • Anonymous
    August 22, 2006
    Slava, my laptop has 1 GB of memory but the result for following query showed /3GB switch.

    -- Does my 32 bit system have /3GB or /Userva switch in boot.ini?
    select
         CASE virtual_memory_in_bytes / 1024
               WHEN 20481024 THEN 'No switch'
               ELSE '/3GB'
         END
    from sys.dm_os_sys_info

    I guess this may be an accounting issue. Will this query be closer to what we want to achieve?

    select
         CASE
               WHEN virtual_memory_in_bytes / 1024 / (2048    1024) <= 1 THEN 'No switch'
               ELSE '/3GB'
         END
    from sys.dm_os_sys_info

  • Anonymous
    August 22, 2006
    PingBack from http://nielsb.wordpress.com/2006/08/23/sql-server-2005-and-dmvs/

  • Anonymous
    August 23, 2006
     Gaurav, you are exactly right - there is a mistake. I have changed the post appropriately. Thanks

  • Anonymous
    August 25, 2006
    Slava has written a post on the information available in SQL Server DMV - sys.dm_os_sys_info. This is...

  • Anonymous
    July 27, 2008
    PingBack from http://www.sqlserverfeeds.com/535/sqloss-dmvs/

  • Anonymous
    November 02, 2009
    May I know how to retrieve hardware key using SQL command ?