Category: Performance Tuning

What’s a Key Lookup?

  By ,

One of the easiest things to fix when performance tuning queries are Key Lookups or RID Lookups. The key lookup operator occurs when the query optimizer performs an index seek against a specific table and that index does not have all of the columns needed to fulfill the result set. SQL Server is forced to go back to the clustered index using the Primary Key and retrieve the remaining columns it needs to satisfy the request. A RID lookup is the same operation but is performed on a table with no clustered index, otherwise known as a heap.  It uses… Continue Reading

How Your Hypervisor Can Impact Your CPU

  By

Recently I had a client complain of chronic high CPU utilization. The performance of their SQL Server had degraded, and it appeared to be related to higher than normal CPU utilization in conjunction with symptoms of unresponsive user queries.  The root cause was twofold—a third party hosting provider had overallocated virtual processors on the physical host where the virtual machine (VM) running SQL Server was residing, as well as a recent upgrade from a version of VMWare that was not patched for Spectre and Meltdown. The host had 16 physical cores and was hyperthreading (making it effectively 32 cores) until… Continue Reading

How Much Will Compression Really Gain Me in SQL Server?

  By

Did you know compression can gain you more than just space on a disk, it can allow you to fit more data into the buffer pool? That means queries have faster and more efficient reads which can help reduce I/O. It is a performance tool I highly recommend you consider for your environments. But how do you know how much compression savings you can gain? SQL Server gives you a handy system stored procedure sp_estimate_data_compression_savings that will help you calculate compression gains. It’s a great tool to use when trying to decide if compression is right for your environment and… Continue Reading

Keeping Large Table Statistics Current -TF2371

  By

Statistics are the magic ingredient that helps the query optimizer create its best guess for generating an execution plan. Keeping statistics as accurate as possible will give the optimizer the information it uses to calculate estimated costs and cardinality, which enables it to properly allocate resources such as memory grants. In this blog I am not going to go into what statistics are and how they are used. There are many great resources out there to read, I am however going to tell you about a trace flag that can help you with keeping your large table statistics up to… Continue Reading

SQL Server Data Collection and Management Data Warehouse

  By

We all have the need to collect system and performance information regarding our SQL Servers. Some of us use third-party tools, SQL Trace, or a homegrown solution.  Did you know Microsoft has a built-in tool to help you accomplish this? It’s called Data Collection and the data it collects (collection sets) is stored in a relational Management Data Warehouse. The data collected is used to generate reports giving us in with very readable and useful insights to our servers. Not only can you get performance information, but you can also use SQL Profiler to export trace definition and create custom… Continue Reading

Careful with your SQL Server Max Memory settings

  By , ,

Quite often I see database administrators set SQL Server max server memory thinking everything related to SQL Server uses this shared memory pool. This is a mistake. There are many things that rely on memory that are not part of SQL Server. Best practices state that you should leave memory allotted for the operating system. However, did you know that if you are running services like SSIS, SSAS or SSRS on the same server as the database engine that it does not use the same memory you have allocated for SQL Server? If the Max Memory setting is not configured… Continue Reading

What is Automatic Tuning in Azure SQL Database

  By ,

Microsoft has long given us DBA tools and suggestions to help tune databases, to fix queries with indexes, and many times they were of questionable merit. We have all seen the dreaded dta_index names in a database and rolled our eyes knowing that sometimes these indexes are not what is needed. While the Database Tuning Advisor, has gotten better over time it is still not a tool more senior DBAs use. Microsoft made big steps in helping DBAs with performance with the introduction of Query Store in both Azure SQL Database and SQL Server 2016. Query Store collects execution and… Continue Reading

Importance of Statistics in SQL Server

  By

Following up on my last post about the Cardinality Estimator let’s talk about column statistics and how they work and play a part in execution plans. The cardinality estimator relies heavily on statistics to get the answer to selectivity (the ratio of distinct values to the total number of values) questions and calculate a cost estimate. This hopefully gives us the best possible execution plans for queries.  In this post, I will show you where to find information about what your statistics contain and information regarding each of those fields. Then we will look at the impact of over and under… Continue Reading

Query Predicates in SQL Server

  By

Sometimes people speak “SQL” and expect you know all the terminology. In recent a conversation about query plans I was ranting on and on about how the predicates were written when the person stopped me and asked a very simple question. Like a light bulb going off, it reminded me that not everyone knows what the other person is talking about when terminology is mentioned without clarification. So, this post will answer that persons very valid simple question. What is a SQL Predicate? Predicates are expressions that evaluate to TRUE, FALSE, UNKNOWN. There are two types of predicates Filtered Predicates… Continue Reading

5 Ways to Examine Cardinality Metrics

  By

In SQL Server the query optimizer uses a cardinality estimator to answer data SELECTIVITY questions like the ones below. The optimizer uses this cardinality data, which relies heavy on statistics to get the answers and calculate a cost estimate. It takes the #of Rows to Satisfy a Predicate/Total # of Input Rows. How many rows will satisfy a single filter predicate? Multiple? WHERE How many rows will satisfy a join predicate between two tables? JOIN How many distinct values do we expect from a specific column? DISTINCT GROUP BY From this estimate the optimizer is able to find an efficient execution… Continue Reading