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:
- Q. How many sockets does my machine have?
cpu_count/hyperthread_ratio AS sockets
- 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?
hyperthread_ratio AS cores_or_logical_cpus_per_socket
- Q. Does my 32 bit system have /3GB or /Userva switch in boot.ini?
WHEN virtual_memory_in_bytes / 1024 / (2048*1024)
< 1 THEN ‘No switch’
- Q. How much physical memory my machine has?
physical_memory_in_bytes/1024 AS physical_memory_in_kb
- Q. How many threads/workers SQL Server would use if the default value in sp_configure for max worker threads is zero:
- 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?
I will be happy to answer your questions.