Microsoft – SQLServer – Under the Hood – Memory Grants

Reviewing a couple of SQL Server Instances and that review led me to what other things one can do to get a bit more under the hood.

It is probably no secret – SQL Server really has an engine.

I have being meaning to use those two words, No Secret, since I heard Kenny Chesney use it as an Introduction to his cover of Dave Loggins – “Come to Boston”.

http://www.youtube.com/watch?v=uCVOcQIkje8&feature=fvwrel

Well let us return back to the more mudane discusion of SQL Server and Memory Grants….

Here is a sample script for capturing memory grants (at a Session level)…

select 
	  object_name(tblST.objectid, tblST.dbid) as objectName
        , tblST.text
        , tblQMG.dop [degreeofParallism]
        , tblQMG.request_time
        , DATEDIFF(second
                    , tblQMG.request_time
                    , ISNULL(tblQMG.request_time
                              , tblQMG.grant_time)
                    ) as timeInBetweenInSeconds
        , tblQMG.requested_memory_kb
        , tblQMG.granted_memory_kb 
        , tblQMG.used_memory_kb  
        , tblQMG.max_used_memory_kb  
        , tblQMG.wait_order
        , tblQMG.is_next_candidate
        , tblQMG.wait_time_ms
        , tblQMG.plan_handle                 
        , tblQP.refcounts
        , tblQP.usecounts
        , tblQP.cacheobjtype
        , tblQP.objtype
        , substring(tblST.text
                     , (tblQS.statement_start_offset/2) + 1
                     ,
                       ((case statement_end_offset
                              when -1
                                   then datalength(tblST.text)
                              else
                                   tblQS.statement_end_offset
                              end
                         - tblQS.statement_start_offset)/2) + 1) as statement_text

from   sys.dm_exec_connections tblConn

			inner join sys.dm_exec_query_memory_grants tblQMG

				on tblConn.session_id = tblQMG.session_id

			cross apply sys.dm_exec_sql_text
                                    (tblConn.most_recent_sql_handle) tblST

			inner join sys.dm_exec_cached_plans tblQP

			    on tblQMG.plan_handle = tblQP.plan_handle

			inner join sys.dm_exec_query_stats tblQS

			    on tblConn.most_recent_sql_handle = tblQS.sql_handle

where  	tblConn.session_id != @@SPID		

order by
         tblConn.session_id

 

 

References:

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s