It\u2019s a tough call because let\u2019s 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\u2019s not the number 1 suspect in this case, however, in general I\u2019m 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\u2019s one other approach which really boosts the SQL queries and that\u2019s not even in the list. This list details the properties you can change, more easily.<\/p>\n\n\n\n
In our time, Sequallity<\/a> have health checked<\/a> many SQL Servers. I couldn\u2019t tell you exactly how many but it\u2019s 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?<\/p>\n\n\n\n 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\u2026So a really nerdy SQL game of Would You Rather?<\/p>\n\n\n\n So, let\u2019s get on with it and of course, let\u2019s do it like they do on the internet in reverse countdown order\u2026definitely too much Youtube\u2026<\/p>\n\n\n\n I learned this years ago, it\u2019s a nice little trick. I call this Missing Statistics and no one has told me off yet! Please don\u2019t Microsoft<\/a>, I mean well.<\/p>\n\n\n\n All compound indexes only use the first column in the statistic histogram. What sp_createstats, the \u2018indexonly\u2019 flavour does is add statistics for secondary and subsequent columns (in indexes) where they don\u2019t exist. So the Query Optimizer has got a much better shot at getting a plan right and by using the correct indexes. It\u2019s a no-brainer.<\/p>\n\n\n\n sp_createstats \u201ccreate 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.\u201d<\/p>\n\n\n\n Check out sp_createstats<\/a>…<\/p>\n\n\n\n On the whole I\u2019d say deadlocks aren\u2019t a problem. But, sometimes they can be hideous. I once saw an Oracle<\/a> 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\u2019t block readers. It really helped though it didn\u2019t cure all of the problems (Oracle has a different locking mechanism) and then we cherry-picked what was left and worked on those.<\/p>\n\n\n\n Now blocking happens much more than deadlocking, this is where queries are holding up other queries. They will run, they\u2019re 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\u2019t win. Not only has my code got to be right, sometimes the Engine can be occasionally wrong too. It\u2019s a good ancillary point, don\u2019t always assume you\u2019re to blame.<\/p>\n\n\n\n Now luckily, in latter versions of SQL Server, this is taken care of but if you\u2019re on anything up to SQL Server 2014 and you don\u2019t 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.<\/p>\n\n\n\n I\u2019d say typically I find that TempDB is hammered more than any other user database and setting it up optimally encourages sterling cross-server performance.<\/p>\n\n\n\n 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\u2019s still on-premises. Even desktops and laptops improve with these changes. Check out the Windows Counter, % Performance Limit in Processor Information.<\/p>\n\n\n\n I always say in the SQL Server training courses<\/a> 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\u2019s 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\u2019re going to destroy your SSDs, don\u2019t do dat, as Arnie would say)<\/p>\n\n\n\n If you don\u2019t have that luxury and you need management of your reindexing strategy then you need a different tack altogether. Maybe a bespoke script.<\/p>\n\n\n\n OK, this is one of those default ones too\u2026the 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!<\/p>\n\n\n\n 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\u2019s amazing.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n If this is not set and left at the default, Windows could be squeezed out by SQL Server. Don\u2019t let SQL bully Windows, keep SQL in check by setting the Max Memory Setting in Server Properties – Memory.<\/p>\n\n\n\n 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\u2019ve hit that most sweetest of sweet spots.<\/p>\n\n\n\n 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\u2019re constituted. Something like that, or statistics reveal the number of rows and the uniqueness of data or not. I\u2019ve always loved up-to-date statistics.<\/p>\n\n\n\n Indexes make SQL Server faster. 1000s of percent faster (possibly). This isn\u2019t 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).<\/p>\n\n\n\n Say I\u2019m your Number One, Missing Indexes!<\/p>\n\n\n\n So there we have it, ‘Add your missing indexes’ is Top of the SQL Server Performance Pops.<\/p>\n\n\n\n Just outside the Top 40\u2026OK, I said Top 10 and now I\u2019m blathering on about Top 40…<\/p>\n\n\n\n Query Reworking \u2013 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\u2019ve got queries taking 15 minutes down to 45 seconds and then some\u2026 We really enjoy doing this. There\u2019s Death by a Thousand Cuts or there\u2019s Query Repetition due to badly behaving applications\u2026there are all sorts in here.<\/p>\n\n\n\n Resource Waits \u2013 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n And lo and behold, we’ve developed a script to run just this check here at Find the Top 10 SQL Server Performance Problems<\/em><\/strong><\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":" 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\u2019s all those Youtube videos I watch, so here are our Top 10 SQL Performance Tuning tips in reverse order… It\u2019s a tough call because let\u2019s say missing<\/p>\n![\"SQL](\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/04\/Number10.jpg\")
<\/figure>\n\n\n\n10. Maintenance – Missing Statistics<\/h2>\n\n\n\n
![\"SQL](\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/04\/Number9.jpg\")
<\/figure>\n\n\n\n9. Server Status – Deadlocks<\/h2>\n\n\n\n
![\"SQL](\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/04\/Number8.jpg\")
<\/figure>\n\n\n\n8. Server Status – Blocking<\/h2>\n\n\n\n
![\"SQL](\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/04\/Number7-2.jpg\")
<\/figure>\n\n\n\n7. Server Configuration – TempDB<\/h2>\n\n\n\n
![\"SQL](\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/04\/Number6.jpg\")
<\/figure>\n\n\n\n6. Server Status – Power Options<\/h2>\n\n\n\n
![\"SQL](\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/04\/Number5.jpg\")
<\/figure>\n\n\n\n5. Maintenance – Reindexing<\/h2>\n\n\n\n
![\"SQL](\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/04\/Number4.jpg\")
<\/figure>\n\n\n\n4. Server Configuration – Parallelism<\/h2>\n\n\n\n
![\"SQL](\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/04\/Number3.jpg\")
<\/figure>\n\n\n\n3. Server Configuration – Memory<\/h2>\n\n\n\n
![\"SQL](\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/04\/Number2.jpg\")
<\/figure>\n\n\n\n2. Maintenance \u2013 Statistics<\/h2>\n\n\n\n
![\"SQL](\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/04\/Number1.jpg\")
<\/figure>\n\n\n\n1. Missing Indexes<\/h2>\n\n\n\n
Top 10 SQL Server Performance Tuning Tips Honourable Mentions<\/h2>\n\n\n\n
Top 10 SQL Server Performance Tuning Tips Conclusions<\/h2>\n\n\n\n
Top 10 SQL Server Performance Tuning Tips Script<\/h2>\n\n\n\n