How to check what is in SQL Server plan cache

What happens when a query is issued to SQL Server

In SQl Server, every query requires a query plan before it is executed. When you run a query the first time, the query gets compiled and a query plan is generated. This query plan is then saved in sql server query plan cache.

Next time when we run the same query, the cached query plan is reused. This means sql server does not have to create the plan again for that same query. So reusing a query plan can increase the performance. 

How long the query plan stays in the plan cache depends on how often the plan is reused besides other factors. The more often the plan is reused the longer it stays in the plan cache.
How to check what is in SQL Server plan cache

To see what is in SQL Server plan cache we will make use of the following 3 dynamic management views and functions provided by sql server
1. sys.dm_exec_cached_plans
2. sys.dm_exec_sql_text
3. sys.dm_exec_query_plan

Use the following query to see what is in the plan cache

SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

ORDER BY cp.usecounts DESC

As you can see we have sorted the result set by usecounts column in descending order, so we can see the most frequently reused query plans on the top. The output of the above query from my computer is shown below.



The following table explains what each column in the resultset contains

Column	Description
usecounts	Number of times the plan is reused
objtype	Specifies the type of object
text	Text of the SQL query
query_plan	Query execution plan in XML format

To remove all elements from the plan cache use the following command
DBCC FREEPROCCACHE

In older versions of SQL Server up to SQL Server 6.5 only stored procedure plans are cached. The query plans for Adhoc sql statements or dynamic sql statements are not cached, so they get compiled every time. With SQL Server 7, and later versions the query plans for Adhoc sql statements and dynamic sql statements are also cached.

Things to consider to promote query plan reusability

For example, when we execute the following query the first time. The query is compiled, a plan is created and put in the cache.
Select * From Employees Where FirstName = 'Mark'

When we execute the same query again, it looks up the plan cache, and if a plan is available, it reuses the existing plan instead of creating the plan again which can improve the performance of the query. However, one important thing to keep in mind is that, the cache lookup is by a hash value computed from the query text. If the query text changes even slightly, sql server will not be able to reuse the existing plan.

For example, even if you include an extra space somewhere in the query or you change the case, the query text hash will not match, and sql server will not be able find the plan in cache and ends up compiling the query again and creating a new plan.

Another example : If you want the same query to find an employee whose FirstName is Steve instead of Mark. You would issue the following query
Select * From Employees Where FirstName = 'Steve'

Even in this case, since the query text has changed the hash will not match, and sql server will not be able find the plan in cache and ends up compiling the query again and creating a new plan.

This is why, it is very important to use parameterised queries for sql server to be able to reuse cached query plans. With parameterised queries, sql server will not treat parameter values as part of the query text. So when you change the parameters values, sql server can still reuse the cached query plan.

The following query uses parameters. So even if you change parameter values, the same query plan is reused.

Declare @FirstName nvarchar(50)

Set @FirstName = 'Steve'

Execute sp_executesql N'Select * from Employees where FirstName=@FN', N'@FN nvarchar(50)', @FirstName

One important thing to keep in mind is that, when you have dynamic sql in a stored procedure, the query plan for the stored procedure does not include the dynamic SQL. The block of dynamic SQL has a query plan of its own.

Summary: Never ever concatenate user input values with strings to build dynamic sql statements. Always use parameterised queries which not only promotes cached query plans reuse but also prevent sql injection attacks.

Query for SQL Server SP Performance Indicator

SELECT TOP (20) 
  CASE WHEN database_id = 32767 THEN 'Resource' 
    ELSE DB_NAME(database_id)
  END AS DBName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME] 
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
   ,total_worker_time
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
   FROM sys.dm_exec_procedure_stats 
ORDER BY execution_count DESC

Database backup history

We can also use the following script:

1) if you want to get the database file storage usage:

SELECT d.name ,f.type_desc ,f.size [Size MB],f.state_desc
FROM [sys].[master_files] f
JOIN [sys].[databases]  d ON d.database_id =f.database_id

2) if you want to get the database backup storage usage:

SELECT SUBSTRING(a.database_name,1,35) AS 'Database',
DATEPART(YEAR,a.backup_start_date) AS 'year',
DATEPART(MONTH,a.backup_start_date) AS 'month' ,
AVG(CAST((a.backup_size /1073741824) AS DECIMAL (9,2)))AS 'Avg Gig' ,
AVG(CAST((a.backup_size /1048576) AS DECIMAL (9,2)))AS 'Avg MB' 
--,a.type
FROM  msdb.dbo.backupset a
JOIN  msdb.dbo.backupset b on a.server_name = b.server_name 
      AND a.database_name = b.database_name 
WHERE a.type = 'D'  
and b.type = 'D'         
  and a.database_name = 'MyDB_Test'
  --and a.backup_size > 1073741824   --   > 1 Gig
GROUP BY a.database_name,DATEPART(YEAR,a.backup_start_date)
  ,DATEPART(MONTH,a.backup_start_date)--,a.type
ORDER BY a.database_name,DATEPART(YEAR,a.backup_start_date) DESC
  ,DATEPART(MONTH,a.backup_start_date) DESC

 
3) if you want to get the latest backup only use the following command:

SELECT sdb.Name AS DatabaseName,
 COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') 
 AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

reference: http://blog.sqlauthority.com/2010/11/04/sql-server-finding-last-backup-time-for-all-database/

4) if you want to get the backup history for all databases in the last seven days:

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type 
       WHEN 'D' THEN 'Database' 
       WHEN 'L' THEN 'Log' 
   END AS backup_type, 
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset 
 ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

reference: http://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

Query to get database backup details

Backup Details.

SELECT  name ,
            recovery_model_desc ,
            state_desc ,
            d AS 'Last Full Backup' ,
            i AS 'Last Differential Backup' ,
            l AS 'Last log Backup'
    FROM    ( SELECT    db.name ,
                        db.state_desc ,
                        db.recovery_model_desc ,
                        type ,
                        backup_finish_date
              FROM      master.sys.databases db
                        LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
            ) AS Sourcetable 
        PIVOT 
            ( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup

We can write below query to include BackupSize and BackupSet.Name in this query. I omitted the pivoted data and make it simpler

WITH    backupsetSummary
          AS ( SELECT   bs.database_name ,
                        bs.type bstype ,
                        MAX(backup_finish_date) MAXbackup_finish_date
               FROM     msdb.dbo.backupset bs
               GROUP BY bs.database_name ,
                        bs.type
             ),
        MainBigSet
          AS ( SELECT   db.name ,
                        db.state_desc ,
                        db.recovery_model_desc ,
                        bs.type ,
                        bs.name AS BackupSetName ,
                        bs.backup_size ,
                        bs.backup_finish_date
               FROM     master.sys.databases db
                        LEFT OUTER JOIN backupsetSummary bss ON bss.database_name = db.name
                        LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name
                                                              AND bss.bstype = bs.type
                                                              AND bss.MAXbackup_finish_date = bs.backup_finish_date
             )
    SELECT  *
    FROM    MainBigSet