I’ve written about statistics in SQL Server a few times now. Through conversations I am reminded that not everyone keeps their table statistics up to date. Keeping your statistics up to date is one of the more critical components of SQL Server performance issues. Please be sure you are doing more than just index maintenance to maintain your statistics. Index maintenance only maintains statistics created by indexes and single field predicate created table statistics. There are also column statistics that need to be maintained that are created by predicates usage in your queries or even manually created. For each insert, update, or delete the distribution of your data changes and…
-
-
Several times I’ve been asked what tools are in my performance tuning and SQL Server management arsenal, so I decided to just create this blog to list them out for you. Keep in mind, these are ones I personally use. There are many more out there that the community uses. Feel free to comment with any you may use that is not on my list. Community Tools (Free) Ola Hallengren’s Maintenance Scripts- Index Maintenance, Integrity and Backup Scripts https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html Glenn Berry’s Diagnostic Scripts -Various scripts based on DMVs to extract SQL Performance statistics and information https://www.sqlskills.com/blogs/glenn/category/dmv-queries/ Adam Machanic sp_WhoIsActive See what is currently going on in SQL…
-
My last two blogs were on understanding columnstore and creating columnstore indexes. In this post, I will touch on one last topic, when to use columnstore indexes. As noted in my prior posts these indexes are designed for large data warehouse workloads, not normal OLTP workload tables. As with any index design it is important to know your data and what you will be using the data for. Be sure to look to what types of queries you will be running against it, as well as how the data is loaded and maintained. Let’s look at a few questions you should ask yourself before deciding to implement a columnstore index.…
-
In my last blog I explained what a columnstore index is, in this blog, we will dive into creating a clustered columnstore index and look at the performance differences the index can make. Let’s get started. Using AdventureworksDW2016CTP3 we will work with the FactResellerSalesXL table which has 11.6 million rows in it. The simple query we will use as a demo just selects the ProductKey and returns some aggregations grouping them by the different product keys. First, we will run the query with no existing columnstore index and only using the current clustered rowstore (normal) index. Note that I turned on SET STATISTICS IO and TIME on. These two SET…
-
Recently I reviewed filtered indexes, this time let’s look at columnstore indexes. These indexes are very useful for data warehouse workloads and large tables. They can improve query performance by a factor of 10 in some cases, so knowing and understanding how they work is important if you work in an environment with larger scaled data. Now, I admit when these first were introduced in SQL Server 2012 I found them very intimidating (additionally, you couldn’t update them directly). For me, anytime you say columnstore, my mind tends to set off alarms saying wait stay away, this is too complicated. So, in this post I am going to try and…
-
When you run a query twice, and get dramatically different run times, your first step can be to try to identify the differences in execution plans. For many that means running the two different queries (before & after) and splitting you screen in order to visually compare the plans. Did you know SQL Server Management Studio gives you the option to compare to different execution plans? It makes it easy not only to visualize the differences, but it also shows you detail properties that allow you to dive into the numbers. This functionality was introduced with SQL Server 2016, and is also part of the functionality of the Query Store…
-
I give performance presentations at many different events to all levels of SQL Server professionals. Over time I’ve noticed that some DBAs and developers have never looked at the dynamic management views (dmv’s) that are available within SQL Server starting with SQL Server 2005. These DMVs are useful means to determine what is going on with a particular SQL Server instance. So, in this post I am going to list a few of my very favorite DMVs as a reference for those who may be new to them. Performance Tuning These dynamic management views are what I use first when looking to any performance issues or I need to know…
-
I am very excited to announce I will be delivering a pre-conference session on SQL Server Performance Tuning on November 5th at PASS Summit 2019. If you have ever attended one of my sessions, you know how passionate I am about this particular topic. So, you also know how very excited I am to have this opportunity to spend a full day talking about SQL Server Performance Tuning and Optimization. Performance Tuning is one of my favorite things to do with SQL Server. There is nothing like seeing performance improvements in an environment as your reward for your hard work. It’s tangible and gratifying. In this session you will be…
-
Another quick post of simple changes you can make to your code to create more optimal execution plans. This one is on implicit conversions. An implicit conversion is when SQL Server must automatically convert a data type from one type to another when comparing values, moving data or combining values with other values. When these values are converted, during the query process, it adds additional overhead and impacts performance. Here is a great chart by Microsoft that shows you conversions and which will cause an implicit or explicit conversion. In this post I will not go into explicit, just know that is what you explicitly tell SQL Server to CAST…
-
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 a row id instead of a primary key to do…
