To ensure optimal performance of the database, an administrator must follow the most important database areas to maintenance including:
- Rebuilding indexes
- Minimizing procedure compilations and recompilations during heavy workloads.
- 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
- Looking into the plan cache to determine cache plan reuse is occurring.
- Monitor for multiple compilations and recompilation to make sure it is minimized and preplanned.
- 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''))'
