A few weeks ago I had the honor of hosting the Microsoft program managers who were directly involved with the new edition of SQL Server as well as the famous Bob Ward who has been involved with every edition of SQL Server since version 1.0. Bob served as the host of an AMA, (Ask Me Anything) about SQL Server 2022.
As we often do during our webinars at Pure Storage, we had a few polls and the first poll was to find out how many people knew that a new version of SQL Server, called SQL Server 2022, was being released this year. The results were shocking to me. Only 60% of the 422 participants had heard there was a new version of SQL Server coming. This shows me how important it is to get the word out. There are so many new and great features that will help us all support our clients and customers. I will be covering some of the features that have already been announced here on this blog and you can see the announcement on the Microsoft site as well.
In this blog and in the next I cover “tempdb”, because it affects so many aspects of performance. Tempdb has had improvements in almost every version since 2016, so, I am sure it is no surprise to anyone that there is another improvement to tempdb in this version of SQL Server.
Tempdb is a key part of the storage engine and is critical to performance and scalability. Let’s start with a bit of a history lesson and look at some of the improvements over the years. Some of these improvements are done with trace flags, which are improvements you can incorporate even if you are not on a newer version yet.
SQL Server 2016
Tempdb setup files
For years, the default setup for tempdb has been a single data file. As a consultant or staff DBA the first thing you did was add more data files. However, sysadmins or junior dbas often do not realize this and you would be surprised how many instances of SQL Server I have come across that still only have one data file.
As part of the setup in SQL Server 2016, for tempdb, you can choose the number of data files. In this version these defaults and setup of tempdb files changed. There is now a new drop down (/SQLTEMPDBFILECOUNT) that allows you to choose the number of data files you would like to use in SQL Server 2016. By default, this is 8 or the number of cores you have, whichever is less.
As a consultant I was seeing servers that were upgraded by simply adding more data files get 10% faster. Keep in mind that legacy coding practices can cause slowdowns in your code as you upgrade and the code may need to change to improve the speed of processing. What you get out of an upgrade is variable and must be tested. With any upgrade, your milage may vary.
Trace Flag 1117
Although this is flagged as a tempdb change, it actually affects all the data files, not just tempdb. If one file in a filegroup grows, it forces all files in that group to also grow. This is a recommendation by Microsoft and listed in their trace flag list. By default, this is turned on in SQL Server 2016. If you would like this feature on for earlier versions you can set the trace flag. If you would like it off for 2016 or later you can get the syntax in this alter statement, to turn it off. If you are concerned about edge cases, or simply averse to using trace flags, then pre-growing your tempdb files is another option. Be sure to leave enough room for monitoring. For earlier versions of SQL, pre-growing will mitigate the growth issue for your tempdb files only, not other data files.
Trace Flag 1118
Similar to 1117, Trace Flag 1118 is not just for tempdb but for all page allocations. This flag, forces page allocations to be uniform extents instead of mixed extents. It is done this way to reduce the contention on Shared Global Allocation Map (SGAM) pages. But what does that actually mean? It is important to understand that the default for a new object create is mixed extents for the first 8 pages and to keep track of those pages and the information of where they are stored on the SGAM page. To access them you would have to search the SGAM page first. The 1118 trace flag changes the default to make the default for new object creation to be uniform extents, which minimizes the need for the SGAM page scan. This is also on by default in 2016 but can be set to on in earlier versions.
These trace flags affect all of SQL Server page allocations, but they are particularly important to tempdb because that is where a fair amount of work is done for the queries we run, and these queries causes a lot of page extensions. When tempdb is heavily used, SQL Server can experience contention when trying to allocate pages. Common operations that can cause the contention include:
- Repeated create and drop of temporary tables (local or global).
- Table variables that use tempdb for storage purposes.
- Work tables associated with CURSORS.
- Work tables associated with an ORDER BY clause.
- Work tables associated with an GROUP BY clause.
- Work files associated with HASH PLANS.
This is just the beginning of some of the ways you can use tempdb to speed up your SQL Server. Check out the next blog to find out more and accelerate your data!
