Webinar Recap: Introduction to SQL Server Query Tuning

If you find yourself asking questions like, “How do I know if a query is running efficiently?” or, “What metrics and indicators tell me when a query is misbehaving?” then you need to watch my recent webinar Introduction to Query Tuning.

During the webinar, I walked through how to assess query performance, guarantee a consistent query tuning process, and avoid common performance killers in SELECT statements running on Microsoft SQL Server and Azure SQL Database. I demonstrated query tuning with both SSMS and the free SentryOne tool, Plan Explorer.

Webinar attendees learned how to:

• Collect and interpret a variety of important query metadata
• Identify queries CPU, IO, and wait statistics
• Review a query execution plan
• Look for red flags in the execution plan and in other query metadata

If you missed the live webinar, view it on-demand here. Also, check our webinar library to sign up for future webinars in this series.

I received a lot of great questions from attendees, which are captured below, along with my answers. Enjoy!

Webinar Q&A

Q: Can you share a SQL Server query to identify unused indexes?

A: Your question is not one that we cover in this webinar. However, that’s a query that I have handy which was originally written by Iain Stirk:

-- Missing Indexes for entire instance by Index Advantage
SELECT   M3.[statement] AS [Database.Schema.Table], 
    user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], M1.last_user_seek,
    M3.equality_columns,
    M3.inequality_columns, M3.included_columns,
    M1.unique_compiles, M1.user_seeks,
    M1.avg_total_user_cost, M1.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS M1 WITH(NOLOCK)
   INNER JOIN sys.dm_db_missing_index_groups AS M2 WITH(NOLOCK)
     ON M1.group_handle = M2.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details AS M3 WITH(NOLOCK)
     ON M2.index_handle = M3.index_handle
-- ORDER BY [Database.Schema.Table]
ORDER BY index_advantage DESC;]]>

Q: If I see a spool operator in an execution plan, how can I resolve it?

A: There are tons of variables involved. For starters, what kind of spool? Lazy or eager spool? The fix might range from adding an index to breaking the query apart into a couple of smaller queries, wrapped in a stored procedure. Remember that a spool operator indicates that SQL Server needs to create a temporary worktable for you in tempdb. So, when you are analyzing your query, it might be simply a matter of manually creating the temporary objects yourself.

Q: How can I identify parameter sniffing in a stored procedure?

A: I will show an example in a moment. If you suspect that parameter sniffing is what is killing your performance, it’s quite hard to find parameter sniffing using native tools. But in SentryOne Plan Explorer, just check the “Expressions” tab at the bottom of the page, and it will show you the “compiled” value and the “runtime” value for a parameter. If they differ greatly, it’s very likely a parameter sniffing problem.

Q: Is FREEPROCCACHE specific for a DB or an entire DB server?

 A: Both of the DBCC FREEPROCCACHE and DROPCLEANBUFFERS statements are instance-wide. If you want to clear the plan cache for a single database, you can use DBCC FREEPROCCACHE(db_id).

Q: Do you have articles regarding Residual I/O? I understand the general concept with the residual predicate but would like more info – especially if they are about perf tuning when they happen.

 A: Yes. Check out this article.

Q: Are there any common or obvious reasons as to why, after updating all statistics, est. vs. actual rows can still be > 100% off?

 A: Residual IO! You can read more in this SQLPerformance blog.

Q: Best vs. Optimal Plan Generation – How are large, complex queries handled?

 A: Not entirely sure what you mean. SQL Server can handle enormously complex queries. Mathematically speaking, the bigger the query in terms of JOIN and WHERE clauses, the more complexity you add to the query plan. I’ve seen plenty of queries with hundreds of items in the select item list and dozens of WHERE clauses and JOIN clauses. In a nutshell, though, SQL Server handles all queries the same, both big and small. Are you asking for a detailed explanation of how it handle a query with dozens of JOIN clauses? If so, I just did a deep dive webinar on that topic called “Introduction to SQL Server Internals.”

Q: For established production applications, do you recommend using “Legacy Cardinality Estimator” or taking the effort to modify the queries to run under the new Estimator?

 A: Well, it depends. There is not a rigid rule that you should always do it or not. Many times, if your users are not complaining about performance, then don’t change it. You may be putting in a lot more work than the benefit you get from all that work of making updates. However, the flip side is that when the old cardinality estimator gets it wrong, it is usually VERY wrong. Consequently, when you put in the effort, about 20% of the time, the queries/procs get dozens of times faster.

The bottom line is that you should have (or build) a suite of test queries which you can run under each configuration to see which provide overall performance improvements. Drop me a message offline if you want to discuss it further.

Q: I recently had a performance issue with implicit conversion…app sent variables as nvarchar, and SQL table is defined with varchar. How do I fix them?

A: This is especially common with ORMs like Entity Framework, and it KILLS your performance.

There are multiple ways to fix them. 1) Make sure the query/proc uses the same data type as the base table. 2) Manually use a CONVERT or CASE function in your query/proc, thus -explicitly- rather than implicitly converting the data type. There are other ways, but honestly, I need more coffee to recall them. Read this article for further insight.

Q: How do you know when you need to UPDATE STATS? Do you run the default values or specify the scan percentage or FULLSCAN?

 A: There are a few ways. First, as discussed in the webinar, do you ever seen a big difference between estimated and actual rows returned? There are some queries you can run that also show how long it has been since the last update on your stats. When I was supporting production systems, I simply ran UPDATE STATS on a regular schedule and afterwards didn’t worry about it unless someone complained about performance. Oh, and one more thing, if you are running SQL Server versions earlier than 2016 and you have large databases or even small databases containing one or two very large tables with more than 1m records, be sure to enable trace flag 2731. (This recommendation doesn’t apply at or after SQL Server 2016). Read more in this SQL Performance blog.

Q: Does the affinity mask from SQL Server settings affect query performance if I grant the server the right to use the first and last cores in a NUMA group?

A: There are one zillion things that can affect query performance and this is one of them, but there are one zillion -1 things that I would likely check first. First off, NUMA utilization depends on many different physical aspects of the hardware, like the number of sockets, the number of cores, etc. It also depends on the version and edition of SQL Server. For example, standard edition licenses of SQL Server cap you to 24 CPUs. As a result, you might have many CPUs that aren’t used by SQL Server at all, depending upon your config. Also, SQL Server 2016 has soft-NUMA features and automatically enables it, but only when it detects that a socket has 9 or more cores.

I’ve blabbed a lot more than I meant to. To be even more brief, yes – using affinity mask can negatively affect query performance both in terms of memory allocation to each CPU in the NUMA node and also in terms of cxpacket waits. Assuming you’re on a recent version of SQL Server, starting at SQL Server 2016, you can find more info here. If you’re running on VMware, you may also want to read this article.

Additional Resources

As stated in the “Introduction to Query Tuning” webinar, there are many ways to assess query performance, guarantee consistent query tuning, and avoid common performance killers in SELECT statements running on Microsoft SQL Server and Azure SQL Database. In summary:

• Examine the performance indicators for red flags
• Examine the execution plan for red flags
• Remember that many of the issues with query performance are self-inflicted:
  • Overly large and complicated queries
  • Missed indexes due to function on SARGs against indexed columns
  • Implicit conversion
  • Red-flag execution plan operators: Sorts, Spools, Spills, Hashes
  • Stale or missing index statistics

If you missed the live webinar, view it on-demand here. Also, for additional information, check out the following resources.

• eBook: Query Optimization with SentryOne Plan Explorer
• Cheat Sheet: Query Tuning and Index Analysis with SentryOne Plan Explorer
• Blog: PREVIEW: SentryOne Plan Explorer Extension for Azure Data Studio
• Webinar: Introduction to Azure Data Studio and the Plan Explorer Extension
• Webinar: Deadlocking for Mere Mortals
• Case Study: Fortified Data Keeps Complex Data Ecosystems Healthy with SentryOne
• Case Study: Microsoft Data Platform Consultancy Solves Customer Query Problems with SentryOne

Kevin Kline

Kevin (@kekline) serves as Principal Program Manager at SentryOne. He is a founder and former president of PASS and the author of popular IT books like SQL in a Nutshell.

Kevin is a renowned database expert, software industry veteran, Microsoft SQL Server MVP, and long-time blogger at SentryOne. As a noted leader in the SQL Server community, Kevin blogs about Microsoft Data Platform features and best practices, SQL Server trends, and professional development for data professionals.