We all know that the user mode VAS(Virtual Address Space) limit for x86 bit server is 2GB and the same value for x64 bit server is pretty huge,and its 8 TB.

If you are interested in reading a lot about VAS,then I would recommend you reading the article written by Jonathan Kehayias(B/T) here.

I was trying to figure out a way on how I can demonstrate/confirm this fact to the users when dealing with technical talks or during initial discussion phase of a project.

The answer to my question was found in sys.dm_os_ring_buffers DMV.This dmv exposes the ring buffer structure within SQL Server and can be used to pull multiple diagnostic infotmation.

This DMV is relatively undocumented,however there are enough blog posts out there in web written by deep technical experts which will come to your rescue.

To double check the VAS details you need to query this DMV with a condition for ring_buffer_type = RING_BUFFER_RESOURCE_MONITOR. This allows you to see memory state changes due to various types of memory pressure.

SELECT Cast(record AS XML),  * 
FROM   sys.dm_os_ring_buffers 
WHERE  ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' 
go

We will get the xml record as the output for this code and you can review the XML to get information related to VAS.

Below are the captured view of XML records for both x86 and x64 bit versions of SQL Server.You can clearly understand the VAS limit from this.

For x86 bit versions

For x64 bit versions

If you closely observe the XML data,you can see that there are lot of very useful memory related information which you can pull.These info can be really helpful for your troubleshooting process.

Conclusion

sys.dm_os_ring_buffers is really a powerful DMV which expose the ring buffer structure with in SQL Server and you can leverage this DMV to troubleshoot multiple issues.

Thanks for reading.

Yesterday there was an interesting question which was floating around twitter.The question was when was the stored procedure last executed,Is it stored some where?

Immediate answer which came to my mind was there is no way we can get this information until and unless we were running some sort of tracing.

I decided to do some more R&D to find that this information can be found if a particular stored procedure is still in cache.

As we know if we dont clean up the cache or use RECOMPILE statements,the data will be available in the cache.

Let me show a demo to demonstrate this concept –

1. Create a database

--Create a database
CREATE DATABASE Test_SP_Cache
GO

2. Create a table

--Create a Table
Use[Test_SP_Cache]
CREATE TABLE Test_Table(ID Int Identity,Name Varchar(20))
GO

3.Insert data to the table

--Insert data to the table
Use[Test_SP_Cache]
INSERT INTO Test_Table VALUES('John')
GO
INSERT INTO Test_Table VALUES('Jack')
GO
INSERT INTO Test_Table VALUES('Mary')

4. Select data

--Select data
SELECT ID,Name FROM Test_Table

5. Create a procedure

--Create a stored procedure
Use[Test_SP_Cache]
CREATE PROCEDURE dbo.sp_Pull_Names
AS
 SELECT ID, Name FROM dbo.Test_Table

6.Execute the stored procedure

--Execute the procedure
EXEC dbo.sp_Pull_Names

Now as we have executed the procedure and we are pretty sure that the SP will be there in the cache.As mentioned earlier if the SP is in cache then we can get the details of execution by querying the DMV sys.dm_exec_query_stats and sys.dm_exec_sql_text

SELECT QT.[text] AS [SP Name], QS.last_execution_time, QS.execution_count AS [Execution Count] 
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS QT
WHERE QT.dbid = db_id() -- Filter by current database
AND QT.text LIKE '%dbo.sp_Pull_Names%'

Conclusion

We have yet again explored the power of DMV’s.I greatly respect the man who has helped me a lot to learn DMV’s and he is none other than Glenn Berry (@GlennAlanBerry)

Thanks for reading.

There are couple of new DMV’s which came to my attention today and I would like to share them with you all.

sys.dm_os_windows_info: This DMV will get you OS related information of the Server.

Sample result is –

windows_release	  windows_service_pack_level	windows_sku	os_language_version
5.1	          Service Pack 3	        NULL	        1033

sys.dm_server_registry: This DMV will get you information on registry keys which are used by the server.

Sample result is(I have trimmed the data) –

registry_key	                                        value_name	value_data
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DASANUP	ObjectName	LocalSystem
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DASANUP	ImagePath	"C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\sqlservr.exe" -sDASANUP

sys.dm_server_memory_dumps : This DMV will get you information on dumps which are generated by the server.

Sample result  is –

filename	                                                                        creation_time	                        size_in_bytes
C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0001.mdmp	2011-08-29 14:39:31.2129902 -07:00	6015847
C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0002.mdmp	2011-08-29 14:44:06.7232483 -07:00	6526667
C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0003.mdmp	2011-08-29 14:47:11.7133910 -07:00	6429912

sys.dm_server_services: This DMV will get you information on services and startup’s.

Sample result is –

servicename	         startup_type	 startup_type_desc status status_desc	process_id  last_startup_time	                service_account	filename	                                                                                is_clustered	cluster_nodename
SQL Server (DASANUP)	        3	 Manual	             4	  Running	 2252	    2011-10-27 15:53:26.3520221 -07:00	LocalSystem	"C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\sqlservr.exe" -sDASANUP	N	        NULL
SQL Server Agent (DASANUP)	2	 Automatic           4	  Running	 4308	    2011-10-27 15:54:01.0705499 -07:00	LocalSystem	"C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\SQLAGENT.EXE" -i DASANUP	N	        NULL