Posted in Cool Script, DBA, DBA Interview, New Features, Performance Improvement, Sql Server 2008, Sql Server 2008 R2, Sql Server 2012, Sql Server 2014, Sql Server 2016, Technical Documentation, Tips and Techniques, TSQL, tagged avoid allocating mixed extents, enables Sql Server to grow all the files in the filegroup at the same time, Sql Server to allocate Uniform Extents, Trace Flags 1117, Trace Flags 1117 and 1118, Trace Flags 1118 on October 29, 2016|
Leave a Comment »
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.
Read Full Post »
Posted in DBA, DBA Interview, New Features, Sql Server 2008, Sql Server 2008 R2, Sql Server 2012, Sql Server 2014, Sql Server 2016, Technical Documentation, Tips and Techniques, Virtual Machines, tagged Configuration Tools, Installed Sql Server Features Discovery Report, Microsoft Sql Server, Sql Components, Sql Installer, Sql Server 2008 R2, Sql Server 2012, Sql Server 2014, Sql Server Installation Center, SqlDiscoveryReport.htm on October 8, 2016|
3 Comments »
Quick one today:
Not often, but once in a while, we need to check what Sql Components are installed on a given virtual machine; so we could replicate (install the same components) on any dependent database virtual machines. Very rarely do we get opportunities to appreciate the multiple features made available through the Sql Installer. Microsoft provides awesome tools to achieve this fashionably.
Follow these steps:
To open the Sql Installer, go to
- Microsoft Sql Server
- Configuration Tools
- Sql Server Installation Center
Sql Server Installation Center
Under Sql Server Installation Center, open Tools. Go to Installed Sql Server Features Discovery Report.
Sql Install Discovery Report
When you click on it, Sql Server Installation Center will generate the report.
Running Discovery Report
Once the report completes, it shows the output in, an easy to use, HTML format in a browser. Sample report is attached below:
Installed Components (Discovery Report)
Note: The same report is saved as SqlDiscoveryReport.htm under Program Files\Microsoft SQL Server\…\Setup Bootstrap\Log\…
Read Full Post »