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 the hosting provider patched from VMWare 5.5 to a newer…
-
-
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 what type to use. This procedure allows you to estimate…
-
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 date. I will also explain why your statistics may not…
-
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 collection sets. This is not a new SQL Server feature,…
-
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 correctly, these other serves could incur memory pressure. While the…
-
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 run-time performance information. Microsoft has taken an additional leap with…
-
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 estimations caused by stale or missing statistics (or even data…
-
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 and Join Predicates. Filtered Predicates cover your WHERE or HAVING…
-
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 plan that fulfills a query request. You will note that…
-
One of the things I’ve been able to implement to help with performance is changing from Update Statistics Synchronous to Auto Update Statistics Asynchronously. It’s a simple change that can have a big impact when implemented in highly transactional OLTP environments. Notice I said OLTP not OLAP, since data in an OLAP environment tends to not be as dynamic, so it’s rare to enable this in a data warehouse. So, what’s the difference between the two and why does it help? Synchronous (defaulted as AUTO_UPDATE_STATISTICS =TRUE) By default, when Auto Update Statistics is set to True, the SQL Server Query Optimizer will automatically update statistics when data has met a threshold…
