If you have ever attended one of my performance tuning sessions, you know I tend to talk about trace flags. Trace Flags can help fix performance issues and some are now defaulted in later SQL Server versions. In my opinion, when a trace flag’s behavior defaulted in a version, then you should potentially put them in place within environments that do not have them implemented. Below, are a few of these particular traces flag along with Microsoft’s definition of what each trace flag does, taken straight from MS documents. I have also included a brief commentary on each one. As with any change, you should be sure to thoroughly test…
-
-
Does your server look like this? Many of us have inherited a SQL Server instance that has all SQL Services installed. Someone, maybe even you, went through the SQL Server installation process using GUI and checked every option available to them, then just clicked Next, Next, Next and then Install. If this is your environment, please take a moment to evaluate and decide which of these services that are required. From a performance tuning perspective, it is important to only run the services that you need. Each of these services can consume resources on your server. Sharing resources reduces what youe SQL Server Engine, SQL Server (MSSQLSERVER) or named instance…
-
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…
-
It is widely known that I am a horrible speller and hate to type code. I tend to use a lot of code snippets and reuse code to avoid writing it. So, when I find little tips that make coding easier for me, I like to share. Did you know that you can surround your code with a click of a button with IF, BEGIN END, WHILE code blocks? SQL Server Management Studio (SSMS) gives us the basic structure of a Transact-SQL statement code block as a starting point. Take Look In SSMS In a query window, Right Click and Choose Surround With (note the hot keys of Ctrl+K or…
-
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…
-
Indexes can be incredibly beneficial to your database performance; however, they do come with a cost—indexes both consume storage space and affect insert performance. Therefore, it is important as part of your index maintenance procedures that you periodically check to see if your indexes are being used. Many times, indexes are created in the belief they are needed but in fact they are never used. You can reduce that IO overhead on inserts when you remove unnecessary indexes. How Do You Find Them? Glenn Berry (B|T) has a fantastic script as part of his diagnostic scripts (link) that helps identify index utilization. In his diagnostic scripts it is listed as…
-
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…
