To ensure optimal performance of the database, an administrator must follow the most important database areas to maintenance including: 

1. Rebuilding indexes 
2. Minimizing procedure compilations and recompilations during heavy workloads. 
3. Check page allocation, contention and aging. 

  Actions such as running the re-indexing script daily and checking for fragmentation are important for optimization health. 

Plan and SQL handles 

Optimization of code to reduce the amount of logical and physical lookups occurring in memory and disk should be the first method of troubleshooting performance issues.  When code has been released into a production environment, it is not always possible to change code to make it faster.  At times, what is needed is to monitor for plan changes and plan execution.  As part of this effort, it’s important to ensure that plans executed on the database maintain a consistent optimal execution plan or that no significant plan execution change occur.  In the Appendix, there are queries that will all an administrator observe plan changes and execution statistics.  Here is an example of how to investigate plan stability in a SQL-Server database.  The table below is a typical query used multiple times during the day with its respective plan handle which is a hash of the query plan.  You can map a particular SQL text to a plan handle using the dynamic management function (DMF) sys.dm_exec_sql_text(sql_handle | plan_handle). 

SQL Statement	Plan Handle
SELECT * FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @Country_region	0x0600060066E0F61940015D4D070000008800000000000000

Table G5-1 

Having multiple plan handles is not necessarily an issue and doesn’t mean the plan is running errantly.  Investigation of the query plan can help determine if the plan has a high cost to the optimizer. 

Steps necessary to determine if query execution is optimized includes 

1. Looking into the plan cache to determine cache plan reuse is occurring. 
2. Monitor for multiple compilations and recompilation to make sure it is minimized and preplanned. 
3. Report any issues with long running code to Itron Support. 

Checking for locking issues such has long PAGELATCHIO latch waits and deadlocks are critical.  Administrators must check for deadlock events either by running traces or dynamic management views. 

In the appendix below, there are examples of looking for specific plan handle performance and the use of plan guides.  Detail of this methods are beyond the scope of this report and should be researched and thoroughly tested before use. 

Plan Maintenance Analysis Queries

SET NOCOUNT ON 
GO 
 
/*  Query checks for memory usage in database o/s  */ 
 
SELECT 
(physical_memory_in_use_kb/1024.0/1024.0) AS Memory_usedby_Sqlserver_GB, 
(locked_page_allocations_kb/1024.0) AS Locked_pages_used_Sqlserver_MB, 
(total_virtual_address_space_kb/1024.0/1024.0) AS Total_VAS_in_GB, 
process_physical_memory_low, 
process_virtual_memory_low 
FROM sys.dm_os_process_memory; 


SELECT object_name, cntr_value 
  FROM sys.dm_os_performance_counters 
  WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)'); 
 
SELECT physical_memory_kb FROM sys.dm_os_sys_info; 
SELECT physical_memory_in_bytes FROM sys.dm_os_sys_info; 





SELECT top(5) sum(total_physical_reads) tot_prds, sum(max_physical_reads) tot_max_prds, sum(total_logical_reads) tot_lrds, sum(max_logical_reads) tot_max_lrds, 
 sum(total_logical_writes) tot_lwrts, sum(max_logical_writes) tot_max_lwrts 
 FROM   sys.dm_exec_query_stats stat 
GO  
 
SELECT usecounts, size_in_bytes, cacheobjtype, 
SUM(total_worker_time / 1000) AS total_cpu_time_in_ms, 
SUM(total_physical_reads) AS total_physical_reads, 
SUM(total_logical_reads) AS total_logical_reads, 
SUM(total_logical_writes) AS total_logical_write, 
REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text 
FROM sys.dm_exec_cached_plans AS p 
INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle 
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle) 
WHERE p.objtype = 'Proc' AND cacheobjtype = 'Compiled Plan' 
GROUP BY usecounts, size_in_bytes, cacheobjtype, [text] 
ORDER BY usecounts DESC, total_logical_reads DESC, total_logical_write DESC 
GO 
 
SELECT usecounts, size_in_bytes, cacheobjtype, 
SUM(total_worker_time / 1000) AS total_cpu_time_in_ms, 
SUM(total_physical_reads) AS total_physical_reads, 
SUM(total_logical_reads) AS total_logical_reads, 
SUM(total_logical_writes) AS total_logical_write, 
p.plan_handle, 
REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text 
FROM sys.dm_exec_cached_plans AS p 
INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle 
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle) 
WHERE p.objtype = 'Proc' AND cacheobjtype = 'Compiled Plan' 
GROUP BY usecounts, size_in_bytes, cacheobjtype, [text], p.plan_handle 
ORDER BY usecounts DESC, total_logical_reads DESC, total_logical_write DESC 
GO 
 


/* Can use query to check for specific plan handles based on performance */ 
 
SELECT usecounts, size_in_bytes, cacheobjtype, 
SUM(total_worker_time / 1000) AS total_cpu_time_in_ms, 
SUM(total_physical_reads) AS total_physical_reads, 
SUM(total_logical_reads) AS total_logical_reads, 
SUM(total_logical_writes) AS total_logical_write, 
p.plan_handle 
REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text 
FROM sys.dm_exec_cached_plans AS p 
INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle 
CROSS APPLY sys.dm_exec_sql_text (0x050005007E58905A307BC1EB1800000001000000000000000000000000000000000000000000000000000000) 
WHERE p.objtype = 'Proc' AND cacheobjtype = 'Compiled Plan' 
GROUP BY usecounts, size_in_bytes, cacheobjtype, [text], p.plan_handle 
ORDER BY usecounts DESC, total_logical_reads DESC, total_logical_write DESC 
GO 
 
select * from sys.dm_exec_cached_plan_dependent_objects(0x050005007E58905A307BC1EB1800000001000000000000000000000000000000000000000000000000000000) 
go 
 
sp_create_plan_guide_from handle 
go 
 
 
SELECT TOP(10) total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, 
min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, 
min_logical_writes, max_logical_writes, total_logical_reads, last_logical_reads, 
min_logical_reads, max_logical_reads,  
REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text 
FROM     sys.dm_exec_cached_plans AS p 
INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle 
CROSS APPLY sys.dm_exec_sql_text (stat.sql_handle) 
ORDER BY total_physical_reads, Total_logical_reads DESC 
GO 
 
SELECT TOP(10) total_physical_reads, max_physical_reads, total_logical_writes, max_logical_writes, 
REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text 
FROM     sys.dm_exec_cached_plans AS p 
INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle 
CROSS APPLY sys.dm_exec_sql_text (stat.sql_handle) 
WHERE p.objtype <> 'Proc' 
ORDER BY total_physical_reads, Total_logical_reads DESC 
GO 
 
 
SELECT t1.session_id, t1.request_id, t1.task_alloc, 
t1.task_dealloc, t2.statement_start_offset,  
t2.statement_end_offset, REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text 
FROM (Select session_id, request_id, 
SUM(internal_objects_alloc_page_count) AS task_alloc, 
SUM (internal_objects_dealloc_page_count) AS task_dealloc  
FROM sys.dm_db_task_space_usage  
GROUP BY session_id, request_id) AS t1,  
sys.dm_exec_requests AS t2 
INNER JOIN sys.dm_exec_query_stats stat ON t2.plan_handle = stat.plan_handle 
CROSS APPLY sys.dm_exec_sql_text (stat.sql_handle) 
WHERE t1.session_id = t2.session_id 
AND (t1.request_id = t2.request_id) 
 
-- I/O query stats based on the sql text 
 
SELECT top (10) (stats.total_logical_reads/stats.execution_count), 
    (stats.total_logical_writes/stats.execution_count), 
    (stats.total_physical_reads/stats.execution_count), 
    stats.execution_count, stats.sql_handle, stats.plan_handle, 
    REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text 
FROM sys.dm_exec_query_stats stats 
CROSS APPLY sys.dm_exec_sql_text (stats.sql_handle) 
ORDER BY (total_logical_reads + total_logical_writes) DESC 
 
 
 
-- Missed Index information 
 
select TOP(50) * from sys.dm_db_missing_index_group_stats 
GO 
 
select object_name(object_id) object, equality_columns, included_columns, statement from sys.dm_db_missing_index_details 
go 


/* Queries below use an option technique known as plan guides */ 
  
sp_create_plan_guide_from_handle @name = N'plan_guide_name' 
 
-- Create a plan guide for the query by specifying the query plan in the plan cache. 
DECLARE @plan_handle varbinary(64); 
DECLARE @offset int; 
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st 
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp 
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%'; 
 
EXECUTE sp_create_plan_guide_from_handle  
    @name =  N'Guide1', 
    @plan_handle = @plan_handle, 
    @statement_start_offset = @offset; 
GO 
-- Verify that the plan guide is created. 
SELECT * FROM sys.plan_guides 
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%'; 
GO 

Create Plan Guides in SQL-Server

sp_create_plan_guide  
@name = N'Guide1', 
@stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h, 
        Sales.Customer AS c, 
        Sales.SalesTerritory AS t 
        WHERE h.CustomerID = c.CustomerID  
            AND c.TerritoryID = t.TerritoryID 
            AND CountryRegionCode = @Country_region', 
@type = N'OBJECT', 
@module_or_batch = N'Sales.GetSalesOrderByCountry', 
@params = NULL, 
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'