Posts Tagged ‘enables Sql Server to grow all the files in the filegroup at the same time’

Quick one today:

Optimizing performance of TempDB is critical to the overall performance improvement of VLDBs. Today, we’ll cover a couple of Trace Flags that add to TempDB’s performance improvement.

  • Trace Flag 1117
  • Trace Flag 1118


Trace Flag 1117

Each database has a filegroup; Each filegroup could have more than 1 files. Similarly, TempDB also could have multiple data files (for VLDBs). This Trance Flag 1117, enables Sql Server to grow all the files in the filegroup at the same time, when one file needs to be grown.

  • As you can imagine, this option has some pros and cons. It is generally a good idea to grow all the files in a filegroup at the same time. Since this flag impacts TempDB, which is utilized by all user databases, this could be an unwelcome option for some environments.
  • So a better compromise might be to pre-configure TempDB data files to the maximum size needed for your environment, so as to not encounter “file increment” events.


Trace Flag 1118

When space is allocated to a database file, internally Sql Server adds in increments of 64k size extents (group of 8 pages, which are 8k each; 8*8k=64k). This Trace Flag enables Sql Server to allocate Uniform Extents during this size increments (and avoid allocating mixed extents). It impacts all new object creations in all the databases.

  • This is important for Sql Server TempDB as it is utilized by all user databases at the same time. There is a possibility that it could result in reduction in efficiency of space utilization, but that is a small price to pay for better performance.


Hope this helps,

Read Full Post »