Top 10 SQL Server Performance Tuning Tips for On-Premises and Virtualised SQL Servers
I have been thinking recently about what is the most effective changes you can make to a SQL Server system and subsequently how these changes rank for importance. Maybe it’s all those Youtube videos I watch, so here are our Top 10 SQL Performance Tuning tips in reverse order…
It’s a tough call because let’s say missing indexes is the number one villain for one SQL Server system. But for another SQL solution, the indexes could be entirely appropriate and reviewed after each new release of the app and the database. So, it’s not the number 1 suspect in this case, however, in general I’m going to have to say that maybe, just maybe, adding your missing indexes could bring the most overall improvement to your slow SQL Server systems. Plus, there’s one other approach which really boosts the SQL queries and that’s not even in the list. This list details the properties you can change, more easily.
In our time, Sequallity have health checked many SQL Servers. I couldn’t tell you exactly how many but it’s probably in the hundreds to thousands, as just one client in the past had well over 250 SQL Servers. Someone joked they saw a SQL Server in the coffee machine. Would that be Expresso edition?
The way that I decided which was the superior tip was by running a bubble sort on the two tips, for example, hmmm, would I rather add missing statistics or fix deadlocking in general…So a really nerdy SQL game of Would You Rather?
So, let’s get on with it and of course, let’s do it like they do on the internet in reverse countdown order…definitely too much Youtube…
10. Maintenance – Missing Statistics
I learned this years ago, it’s a nice little trick. I call this Missing Statistics and no one has told me off yet! Please don’t Microsoft, I mean well.
All compound indexes only use the first column in the statistic histogram. What sp_createstats, the ‘indexonly’ flavour does is add statistics for secondary and subsequent columns (in indexes) where they don’t exist. So the Query Optimizer has got a much better shot at getting a plan right and by using the correct indexes. It’s a no-brainer.
sp_createstats “create single-column statistics on columns that are not already the first column in a statistics object. Creating single-column statistics increases the number of histograms, which can improve cardinality estimates, query plans, and query performance. The first column of a statistics object has a histogram; other columns do not have a histogram.”
Check out sp_createstats…
9. Server Status – Deadlocks
On the whole I’d say deadlocks aren’t a problem. But, sometimes they can be hideous. I once saw an Oracle system ported to SQL code (yes literally, ported, line-by-line) and it suffered terrible deadlocks, there were literally hundreds. To overcome the majority of the issues, we enabled the snapshot isolation in SQL Server where writers don’t block readers. It really helped though it didn’t cure all of the problems (Oracle has a different locking mechanism) and then we cherry-picked what was left and worked on those.
8. Server Status – Blocking
Now blocking happens much more than deadlocking, this is where queries are holding up other queries. They will run, they’re just being held at the starting blocks. This can be for a variety of reasons and can include inappropriate locking mechanisms deployed. When I was younger (much younger), many years ago, I saw that an Service Pack fix of Microsoft said basically using NOLOCK brought back incorrect rows and using READ COMMITTED brought back incorrect rows. Shoot. I can’t win. Not only has my code got to be right, sometimes the Engine can be occasionally wrong too. It’s a good ancillary point, don’t always assume you’re to blame.
7. Server Configuration – TempDB
Now luckily, in latter versions of SQL Server, this is taken care of but if you’re on anything up to SQL Server 2014 and you don’t have a DBA or a DBA-aware developer, then your TempDB may not be not configured correctly. This includes having multiple data files plus all of them need to be the same size, and trace flags 1117 and trace flags 1118 should be enabled globally or considered.
I’d say typically I find that TempDB is hammered more than any other user database and setting it up optimally encourages sterling cross-server performance.
6. Server Status – Power Options
Unfortunately, Windows has the default power option set to Balanced. This is good to save the environment and power usage. For SQL Servers – this is not good. As SQL Server handles multiple queries simultaneously, any damping down effect of the CPUs is really bad for overall performance. This would be the same for other servers too, guests and hosts in Virtual environments. Plus your Exchange server, if it’s still on-premises. Even desktops and laptops improve with these changes. Check out the Windows Counter, % Performance Limit in Processor Information.
5. Maintenance – Reindexing
I always say in the SQL Server training courses we deliver, if I had to choose between updating statistics and reindexing tables which would I prefer? I know this is a strange, dystopian world whereby choice is limited. When the push correlates with the shove, it would always be to update those statistics. With up-to-date statistics, the Query Optimizer chooses optimal plans and you are getting the best out of the SQL Server. Reindexing can happen, weekly, monthly or quarterly. There’s no real need to reindex nightly. But, and there always is a but, if you have the window of overnight opportunity then I say, go for it (unless you’re going to destroy your SSDs, don’t do dat, as Arnie would say)
If you don’t have that luxury and you need management of your reindexing strategy then you need a different tack altogether. Maybe a bespoke script.
4. Server Configuration – Parallelism
OK, this is one of those default ones too…the default for Cost Threshold for Parallelism (CTFP) and MaxDOP are both incorrect by default. Now in SQL Server 2019, you can change the memory and the CTFP during the install, about time too!
When queries parallelise, they can be much quicker as more threads are helping solve the query. Small queries may well suffer because when a query is parallelised, the results are then glued back together and this gluing can add overhead with waiting as some threads will take their time. I have seen this in action with queries taking 11 seconds, dropping down to 0.5 seconds. It’s amazing.
MaxDop should be changed to 4 or 8 to match the cores and query profiles and CTFP should be set to 30, 50, 120 or whatever matches your server work. This could consist of lots of small fast queries, lots of long, intensive queries or a mixture of both.
3. Server Configuration – Memory
If this is not set and left at the default, Windows could be squeezed out by SQL Server. Don’t let SQL bully Windows, keep SQL in check by setting the Max Memory Setting in Server Properties – Memory.
Be aware however that from SQL 2012 onwards this figure refers to data buffer only (basically table and index structures). This means that SQL Server will defintely use more memory than this. As a rough estimate choose half the amount of RAM you allocate to the data buffers. There is always a sweet spot and with more Server Analysis, you can dial this up and down until you’ve hit that most sweetest of sweet spots.
2. Maintenance – Statistics
Ahhhh, the smell of freshly updated statistics, can you smell that? The Query Optimizer is finding everything a breeze as the statistics are telling it accurately about the tables in the database and how they’re constituted. Something like that, or statistics reveal the number of rows and the uniqueness of data or not. I’ve always loved up-to-date statistics.
1. Missing Indexes
Indexes make SQL Server faster. 1000s of percent faster (possibly). This isn’t always the case, but on systems with absent or missing indexes, they need to be added. I’m talking about the ones suggested by the Missing Index DMVs plus the ones you capture by analysing the queries (but this takes a lot longer).
Say I’m your Number One, Missing Indexes!
So there we have it, ‘Add your missing indexes’ is Top of the SQL Server Performance Pops.
Top 10 SQL Server Performance Tuning Tips Honourable Mentions
Just outside the Top 40…OK, I said Top 10 and now I’m blathering on about Top 40…
Query Reworking – Sometimes this is a last resort and cannot be done at all but sometimes it can and boy, can you get massive improvements with this. For our clients, we’ve got queries taking 15 minutes down to 45 seconds and then some… We really enjoy doing this. There’s Death by a Thousand Cuts or there’s Query Repetition due to badly behaving applications…there are all sorts in here.
Resource Waits – This tells you what is causing the most WAIT time on your SQL Server, it may be something to cogitate over or maybe something to celebrate over but bottom line, it may need some further investigation there Columbo, if there is just something you don’t understand.
Optimize for Ad-Hoc SQL – this tells SQL to reclaim space from the procedure cache for memory hungry single-use plans. Saves some critical memory for data, which ain’t bad.
Top 10 SQL Server Performance Tuning Tips Conclusions
To be fair, I changed the order this Top 10 SQL Server Performance Tuning Tips even as I posted it. Maybe there isn’t a special button you can push to make a slow SQL Server go faster, but if you approach it in a methodical way, analysing, investigating and testing before implementation, you’ll go a long way to performance tuning your slow SQL Server.
Top 10 SQL Server Performance Tuning Tips Script
And lo and behold, we’ve developed a script to run just this check here at Find the Top 10 SQL Server Performance Problems.