Run dbcc prsqlcache to print summaries of the statements in the statement cache. The oid option allows you to specify the object ID of the statement to print, and the printopt option allows you to specify whether you print the trace description (specify 0) or the showplan option (specify 1). If you do not include any values for oid or printopt, dbcc prsqlcache displays the entire contents of the statement cache.
You must have the sa_role to run dbcc prsqlcache
This provides information for all statements in the cache:
dbcc prsqlcache
Start of SSQL Hash Table at 0xfc67d830 Memory configured: 1000 2k pages Memory used: 18 2k pages Bucket# 625 address 0xfc67ebb8 SSQL_DESC 0xfc67f9c0 ssql_name *ss1248998166_0290284638ss* ssql_hashkey 0x114d645e ssql_id 1248998166 ssql_suid 1 ssql_uid 1 ssql_dbid 1 ssql_status 0x28 ssql_parallel_deg 1 ssql_tab_count 0 ssql_isolate 1 ssql_tranmode 0 ssql_keep 0 ssql_usecnt 1 ssql_pgcount 8 SQL TEXT: select * from sysobjects where name like "sp%" Bucket# 852 address 0xfc67f2d0 SSQL_DESC 0xfc67f840 ssql_name *ss1232998109_1393445479ss* ssql_hashkey 0x530e4a67 ssql_id 1232998109 ssql_suid 1 ssql_uid 1 ssql_dbid 1 ssql_status 0x28 ssql_parallel_deg 1 ssql_tab_count 0 ssql_isolate 1 ssql_tranmode 0 ssql_keep 0 ssql_usecnt 1 ssql_pgcount 3 SQL TEXT: select name from systypes where allownulls = 0 End of SSQL Hash Table DBCC execution completed. If DBCC printed error messages, contact a user with
Or you can get information about a specific object ID:
dbcc prsqlcache (1232998109, 0)
SSQL_DESC 0xfc67f840 ssql_name *ss1232998109_1393445479ss* ssql_hashkey 0x530e4a67 ssql_id 1232998109 ssql_suid 1 ssql_uid 1 ssql_dbid 1 ssql_status 0x28 ssql_parallel_deg 1 ssql_tab_count 0 ssql_isolate 1 ssql_tranmode 0 ssql_keep 0 ssql_usecnt 1 ssql_pgcount 3 SQL TEXT: select name from systypes where allownulls = 0 DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
This example specifies 1 in the printopt parameter for the showplan output:
dbcc prsqlcache (1232998109, 1)
SSQL_DESC 0xfc67f840 ssql_name *ss1232998109_1393445479ss* ssql_hashkey 0x530e4a67 ssql_id 1232998109 ssql_suid 1 ssql_uid 1 ssql_dbid 1 ssql_status 0x28 ssql_parallel_deg 1 ssql_tab_count 0 ssql_isolate 1 ssql_tranmode 0 ssql_keep 0 ssql_usecnt 1 ssql_pgcount 3 SQL TEXT: select name from systypes where allownulls = 0 QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. FROM TABLE systypes Nested iteration. Table Scan. Forward scan. Positioning at start of table. Using I/O Size 2 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
