SQLOS’s DMVs


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 (8)

  1. I’ll bite Slava… What is DBCC MEMORYSTATUS doing in a list of DMVs?

  2. slavao says:

    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.

  3. Glenn Berry says:

    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.

  4. gauravbi says:

    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 2048*1024 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

  5. slavao says:

     Gaurav, you are exactly right – there is a mistake. I have changed the post appropriately. Thanks

  6. Slava has written a post on the information available in SQL Server DMV – sys.dm_os_sys_info. This is…