Top 6 SQL Server Performance Problems

SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

Source : http://speedysql.com/2015/09/05/most-common-sql-server-performance-problems/

Top 6 SQL Server Performance Problems

In this post I am going to talk about the most common performance problems I come across when query tuning and how to solve them. These performance problems are specific to the database engine and must account for 90% of all the problems I deal with.

1. Missing Indexes
2. Parameter Sniffing
3. Statistics
4. Catch-All Queries
5. Table Variables
6. OR Operator in Join Conditions

Missing Indexes

This is probably the simplest of all the performance problems to resolve as SQL Server provides the missing index recommendation as part of the query execution plan. Having said that, it’s not as simple as just creating the missing index from the script provided. You need to check what indexes already exist on the table and see if it might not be better to alter an existing index to include the required columns.

The script below returns a list of missing indexes with an average user impact greater than 70 and an index advantage greater than 500,000. These are weights calculated by SQL Server to give an approximate idea of the importance of creating the missing index. I find that 70 is a good number to use to filter out the indexes that aren’t usually worth creating. Feel free to alter both of these numbers.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

WITH cte AS ( SELECT @@servername AS ServerName ,db_name(mid.database_id) AS DatabaseName ,CAST(avg_user_impact AS DECIMAL(3, 0)) AS avg_user_impact ,cast(index_advantage AS INT) AS index_advantage ,mid.STATEMENT AS table_name ,mid.equality_columns ,mid.inequality_columns ,mid.included_columns FROM ( SELECT (user_seeks + user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage ,migs.* FROM sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ) AS migs_adv ,sys.dm_db_missing_index_groups mig WITH (NOLOCK) ,sys.dm_db_missing_index_details mid WITH (NOLOCK) WHERE migs_adv.group_handle = mig.index_group_handle AND mig.index_handle = mid.index_handle ) SELECT * FROM cte WHERE avg_user_impact > 70 AND index_advantage > 500000 ORDER BY index_advantage DESC;

If you want to be proactive and find execution plans with missing index recommendations, you can use the following script to do so. Again, I’ve filtered the results to only return missing indexes with an impact greater than 70 so alter this number as required. The advantage of this script is that you can see which query or stored procedure will benefit from the missing index.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

--Returns top 10 queries by logical reads with missing indexes. USE MASTER; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp SELECT TOP 10 qs.plan_handle ,ph.query_plan ,qs.total_elapsed_time / qs.execution_count AS avg_worker ,qs.execution_count ,CASE WHEN qs.execution_count = 0 THEN NULL ELSE qs.total_logical_reads / qs.execution_count END AS avg_logical_reads ,qs.last_logical_reads ,qs.min_logical_reads ,qs.max_logical_reads INTO #tmp FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS ph WHERE ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup)[1]') = 1 AND qs.execution_count >= 1 AND ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/@Impact)[1]', 'float') > 70 --Impact AND ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/@Database)[1]', 'nvarchar (max)') != '[MSDB]' ORDER BY CASE WHEN qs.execution_count = 0 THEN NULL ELSE qs.total_logical_reads / qs.execution_count END DESC; WITH xmlnamespaces (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') ,CachedPlans AS ( SELECT object_name(st.objectid, st.dbid) obj ,n.value('../../../@StatementText', 'varchar(4000)') AS statement_text ,n.value('../../RelOp[1]/@EstimateRows', 'decimal(10,2)') AS [EstimateRows] ,n.value('../../RelOp[1]/@EstimatedTotalSubtreeCost', 'decimal(10,2)') AS [EstimatedSubTreeCost] ,n.value('../../RelOp[1]/@EstimateIO', 'decimal(10,2)') AS [EstimateIO] ,qs.* ,n.value('@Impact', 'decimal(3,0)') AS Impact ,n.value('MissingIndex[1]/@Database', 'varchar(128)') AS [Database] ,n.value('MissingIndex[1]/@Table', 'varchar(128)') AS [TableName] ,( SELECT ( SELECT c.value('@Name', 'varchar(128)') + ' ' FROM n.nodes('MissingIndex/ColumnGroup[@Usage="EQUALITY"][1]') AS t(cg) CROSS APPLY cg.nodes('Column') AS r(c) FOR XML PATH('') ) ) AS equality_columns ,( SELECT ( SELECT c.value('@Name', 'varchar(128)') + ' ' FROM n.nodes('MissingIndex/ColumnGroup[@Usage="INEQUALITY"][1]') AS t(cg) CROSS APPLY cg.nodes('Column') AS r(c) FOR XML PATH('') <a href="https://support.microsoft.com/en-us/kb/2754171" target="_blank">2371</a>               ) ) AS inequality_columns ,( SELECT ( SELECT c.value('@Name', 'varchar(128)') + ' ' FROM n.nodes('MissingIndex/ColumnGroup[@Usage="INCLUDE"][1]') AS t(cg) CROSS APPLY cg.nodes('Column') AS r(c) FOR XML PATH('') ) ) AS include_columns FROM #tmp qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS st LEFT JOIN sys.dm_exec_procedure_stats p ON p.plan_handle = qs.plan_handle CROSS APPLY qs.query_plan.nodes('//MissingIndexGroup') AS m(n) ) SELECT * FROM CachedPlans

Parameter Sniffing

Parameter sniffing is a well-known problem and has been widely documented so I am not going to go into detail in describing it here. There are many good articles on this subject, the best of which I think is Paul White’s article on SQL Performance. A quick way to check if a stored procedure’s performance is being impacted by parameter sniffing is to recompile the procedure or remove its execution plan from the plan cache. I prefer to remove the plan from the cache using DBCC FREEPROCCACHE. You can do this using the following code:

1 2 3 4 5 6 7 8 9

DECLARE @PlanHandle VARBINARY(64); SELECT @PlanHandle = p.plan_handle FROM sys.dm_exec_procedure_stats AS p WHERE p.object_id = OBJECT_ID('dbo.ProcName'); IF @PlanHandle IS NOT NULL BEGIN DBCC FREEPROCCACHE (@PlanHandle); END GO

If the stored procedure’s performance suddenly improves, you know that the problem was parameter sniffing and you can implement one of the suggested solutions from Paul’s article. I always do this before updating statistics because an execution plan will be recompiled if updated statistics are available. If you update statistics first, you won’t know whether you had a statistics problem or a parameter sniffing problem.

Statistics

Statistics are almost as important as indexes to ensure good performance but seem to be given much less attention. SQL Server automatically updates statistics by default but the procedure it uses (sp_autostats) isn’t always enough. The approximate threshold for sp_autostats to run is when 20% of a table has been modified, as documented in this KB article. For larger tables this threshold is rarely reached before a scheduled UPDATE STATISTICS job runs (this is something that a DBA should always set up) and manual intervention is therefore sometimes required. Trace Flag 2371was introduced in SQL Server 2008 R2 Service Pack 1 to help mitigate this issue and you should evaluate whether you need to turn this on for your SQL Servers.

Another common statisitcs-related problem is for the most recently added data to be missing from the statistics histogram. As it is often the most recent data that is being queried, this can have a big impact on performance. Date columns are often affected by this problem. Yesterday’s data is loaded but is not enough to trigger sp_autostats and consequently the histogram does not contain the date. This results in poor cardinality estimates and, in turn, inefficient query execution plans. Trace flags 2389 and 2390 were introduced in SQL Server 2005 Service Pack 1 to help combat this problem. Fabiano Amorim wrote a comprehensive article on these two trace flags.

If I am tuning a query that doesn’t have any missing indexes and has already been recompiled, I will always update statistics on the tables involved before going any further

Catch-All Queries

These types of queries are usually behind a form that requires user input. The developer does not know which parameters will contain values and which will be empty so they create an all-purpose query. Kimberly Tripp wrote a goodarticle on the subject with some possible solutions.

Table Variables

Table variables perform well with small data sets (a few hundred rows at the most) and when not used in join conditions. They can perform horribly when used to join onto other tables due to the fact that SQL estimates them to contain only one row. If you need to join onto other tables, consider using temporary tables. If you are not able to alter the T-SQL code, consider using trace flag 2453, introduced in SQL Server 2012 Service Pack 2, which aims to improve cardinality estimates for table variables. Aaron Bertrand has written a very comprehensive article on the subject, which is worth reading.

OR Operator in Join Conditions

This problem is much less obvious but I still see it quite often. SQL Server sometimes generates an inefficient plan when a query has the following format:

1 2 3 4

SELECT a.col1 FROM dbo.table1 a INNER JOIN dbo.table2 b ON a.col1 = b.col2 WHERE a.col1 = @val1 OR b.col2 = @val2;

This a simplified example but a more complex example can be seen in the following MSDN article. The solution is to rewrite the query using UNION instead of OR, splitting it into two selects.

Conclusion

These are the most common performance problems that I see when tuning queries in OLTP databases. I think they cover about 90% of the performance problems I see. The other 10% of problems require usually require a deeper knowledge of SQL Server internals. I will be looking at some of these cases in the next few weeks.