Reading up on some of the new features found in SQL Server 2016 I came across a couple tips for optimizing earlier version. These performance options are implemented by setting trace flags at the startup of your SQL Server instance. They refer to T1117 for TempDb and T1118 for user databases.
T1118 is a trace flag that, when turned on, extends all TempDb data files, whenever any single TempDb data file requires auto growth. On multi-core machines, database performance is enhanced by having a separate database file in TempDb based on the core count. There is a bit of an art to this, so I won’t get into it hear. However, lets say you have four TempDb data files. In that case, you want them all to be the same size. If one grows, they all grow the exact same amount. When you configure your SQL Server Data Engine you configure the TempDb data files. Turning on trace flag T1118 keeps them all the same over time.
When SQL Server needs to allocate more space into the database structure, in order to add more data, it will grant a new extent of 8 pages of 8k each. By default, when you add data, it will add it to the first page available. As a result, data may be fragmented. Sometimes the fragmentation is in a single page. Usually, the pages may be fragmented themselves.
When SQL Server reads from the disk, it reads an entire Extent. If that Extent contains data only from one table, the likelihood of reading data you require is higher, and the performance of your database increases. A trace flag is available, T1118, which forces the SQL data engine to assign an entire Extent to a single table when an Extent is allocated. This means that your little 2K table will require 64k. However, when it comes to fragmentation, and the limited number of little 2k tables, this is a small price to pay for increased performance.
By default SQL Server 2016 already has these trace flags turned on by default, instead of turning them off, like previous versions. Moreover, they are converted into configuration options, allowing them to be configured without stopping and restarting the database engine. While you can turn a trace flag on using DBCC, if the instance of SQL Server is restarted, it returns to the default configuration.
So, now you have something new to try on your SQL engine.
Cheers,
Ben