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.
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:
cpu_count/hyperthread_ratio AS sockets
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?
hyperthread_ratio AS cores_or_logical_cpus_per_socket
WHEN virtual_memory_in_bytes / 1024 / (2048*1024)
< 1 THEN 'No switch'
physical_memory_in_bytes/1024 AS physical_memory_in_kb
I will be happy to answer your questions.