Some of you could have gotten bored with my previous post http://blogs.msdn.com/slavao/archive/2005/01/27/361678.aspx. Yea, I know that VAS subject was covered multiple times and by multiple authors in the past and you probably know about it more than you ever wanted to know :-). However please bear with me. I would like all of us to be on the same page. I do need to cover some of OS internals so that I can cover the subject that all of you want to learn more about: SQL Server memory management. But I will continue with the subject I started next time 🙂
Today I would like to have a bit of fun. So far we have discussed VAS and how one can estimate its usage with vasummary view, discussed here http://blogs.msdn.com/slavao/archive/2005/01/26/360759.aspx . Now given this view and other dmvs we actually can learn more about SQL Server VAS. Lets begin!
As many of you know SQL Server’s thread stack size is 512KB (0x80000). It means that every thread for its stack reserves 512KB of VAS. (There is also extra 4KB of VAS that is used for Thread Environment Block, TEB, but this is not important right now). The question is – given vasummary view whether it is possible to estimate number of threads currently running in server. The answer is YES! Here is the query:
— Estimate number of threads in the server
select * from vasummary where size=0x0000000000080000
Size Reserved Free
—————————————— ———– ———–
0x0000000000080000 89 1
Wow, it is so simple J! This is just an estimate. It is also possible to get real number of threads by using sys.dm_os_threads view:
— Real number of threads in the system
select count(*) from sys.dm_os_threads
As you can see the numbers are different. Why? Well the reason is that other component inside of SQL Server reserved VAS of the same size. Usually there are only few of such components. It means that first query is an upper bound for a number of threads inside of SQL Server. Please keep in mind that the first query is more expensive than a second one. In real life you would rather use thread dmv to get number of threads in a SQL Server process. Remember we are just having fun here :-).
As you realize max server threads parameter in sp_configure is not actual upper limit for number of threads inside of SQL Server. Threads could be created by different dlls including netlibs, rpc, xps and etc.. In many cases we don’t have any control over those threads but still queries presented above do give you information about all the threads inside of SQL Server’s VAS not only threads created by us.
You can learn more about a thread VAS usage. Thread dmv includes information about thread’s stack and TEB:
— Dump thread’s TEB and its stack
select thread_address,stack_base_address from sys.dm_os_threads
Given this dmv and sys.dm_os_virtual_address_dump one can actually picture thread inside of VAS. Isn’t this cool? I will leave the actual query to accomplish this as an exercise for a reader…
Before we move on to modules, I would like to make you think, just a bit :-): How VAS and output from dmvs would be different if SQL Server runs in Fiber mode? (I will postpone answering this question until we talk about scheduling).
So far we have learned a way to estimate number of threads running inside of SQL Server. Unfortunately it is not possible to use vasummary view to learn about dll’s VAS usage. The reason is that different dlls have different sizes. However we can use sys.dm_os_loaded_modules and sys.dm_os_virtual_address_dump to learn about dlls.
— Find out how dlls map to our VAS
select base_address, name from sys.dm_os_loaded_modules
As with threads view, joining this view with sys.dm_os_virtual_address_dump will give you complete information about dll’s occupying SQL Server VAS. As you imagine this view is very important in understanding how SQL Sever VAS’s is used. It gives good indication of what lives in a SQL Server’s VAS. In many cases DBA’s and developers don’t have a clue what they end up loading into SQL Server VAS when using different external components. Currently I have a case where SQL Server runs out of VAS… Guess what? When I dumped loaded dlls I was shocked – SQL Server had the whole world loaded into its process space. Some of those dlls export UI functionality only. How would one force loading of UI dll into the server? I was laughing :-)….
Well I think we had enough fun for today, right? I will continue next time with other types of memory resources.
Have a good day and weekend!