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.