Tracking VAS in Yukon, cont...
Some of you could have gotten bored with my previous post https://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 https://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
-----------
86
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
thread_address stack_base_address
------------------------------------------ ----------------------
0x7FFDBE30 0x02820000
0x7FFDAE30 0x028B0000
0x7FFD9E30 0x02930000
0x7FFD8E30 0x029C0000
0x7FFD7E30 0x02A40000
0x7FFD6E30 0x02AD0000
0x7FFD5E30 0x02B50000
0x7FFD4E30 0x02BE0000
0x7FFAFE30 0x02C60000
0x7FFAEE30 0x02CF0000
0x7FFADE30 0x02D70000
0x7FFACE30 0x02E00000
0x7FFABE30 0x02E80000
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
base_address name
------------------------------------------ --------------------
0x00400000 D:\MSSQL\MSSQL.3\MSSQL\Binn\sqlservr.exe
0x7C800000 C:\WINNT\system32\ntdll.dll
0x77E40000 C:\WINNT\system32\kernel32.dll
0x7D000000 C:\WINNT\system32\MSVCR80.dll
0x77BA0000 C:\WINNT\system32\msvcrt.dll
0x7C080000 C:\WINNT\system32\MSVCP80.dll
0x77F50000 C:\WINNT\system32\ADVAPI32.dll
0x77C50000 C:\WINNT\system32\RPCRT4.dll
0x77380000 C:\WINNT\system32\USER32.dll
0x77C00000 C:\WINNT\system32\GDI32.dll
0x761B0000 C:\WINNT\system32\CRYPT32.dll
0x76190000 C:\WINNT\system32\MSASN1.dll
0x76F50000 C:\WINNT\system32\Secur32.dll
....
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!