SQL Server 2012 Best Practices Analyzer TempDB Recommendations

By:   |   Comments (3)   |   Related: > SQL Server Configurations


Problem

In a previous tip, SQL Server 2012 cluster with TempDB on local disk, we discussed Microsoft's official support of local disk tempdb in SQL Server cluster configurations. This tip will show you how to utilize the SQL Server 2012 Best Practices Analyzer (BPA) to review best practice recommendations and make the appropriate changes to attain an optimal tempdb configuration, at least according to the best practice.

Solution

The Microsoft SQL Server 2012 Best Practices Analyzer is a free diagnostic tool available here from Microsoft. The BPA gathers information about a server and a SQL 2012 instance installed on that server. It then determines if the configuration settings are set according to best practice recommendations and reports in the form of errors and warnings on settings that deviate from the best practice recommendation.

The SQL 2012 BPA is ran within the Microsoft Baseline Configuration Analyzer (MBCA) available here. The MBCA is a utility that analyzes the current system configuration against a predefined best practice model provided by the various Microsoft product teams. These include BPA's for Exchange, ForeFront, SharePoint and SQL Server to name a few.

In this tip we will be running the SQL 2012 BPA and configuring recommended settings from the resulting tempdb errors. Check out the tip Using the Microsoft SQL Server 2012 Best Practice Analyzer to further familiarize yourself with this useful utility.

Running the SQL Server Best Practices Analyzer

Prior to starting the first BPA scan, ensure that the account you are using has a SQL Server login with sysadmin role membership. Otherwise you will encounter the error "Login does not exist or is not a member of the Systems Administrator role" as shown below.

Running the Microsoft SQL Server 2012 Best Practices Analyzer 1.0

Note: The login needs to be an explicit member of the sysadmin role and cannot just be a member of a group included in the sysadmin role.

The login needs to be an explicit member of the sysadmin role

Using an account with SQL Server sysadmin privileges launch the SQL 2012 BPA through the Microsoft Baseline Configuration Analyzer.

Launch the SQL 2012 BPA through the Microsoft Baseline Configuration Analyzer

Provide the instance name and select the server engine and server setup parameters, in testing I found that you can leave the instance name blank if it is the default instance of MSSQLSERVER. If you have other services installed you can select those parameters as well, otherwise you will receive errors stating the service is not available or not configured.

Analyze the SQL Server instances with the Microsoft SQL Server 2012 BPA 1.0

After analyzing the server a report listing configurations that are not set according to best practices is displayed. In this case we are only receiving the error related to the tempdb database not being configured optimally. Some of the other errors or warnings you may receive include: you do not have recent database backups, do not have a recent database consistency check, have too many logins in the sysadmin role or have experienced unexpected server shutdowns; to name a few.

 
View Baseline Configuration with the SQL Server BPA - We are only receiving the error related to the tempdb database

Click on the error to read the full description and view the recommended resolution. In this case we have 4 processors and only 1 tempdb data file. It is considered a general best practice to have the same number of tempdb data files as processors, up to 8. The more information link in the BPA report provides further detail in a Microsoft article on reducing tempdb contention.

 
Click on the error in the Microsoft SQL Server 2012 BPA 1.0 to read the full description

To comply with the best practice it is recommended that we add 3 more data files to equal the number of processors. You may use multiple drives to spread the workload over various physical disks or you can use the same drive as the current tempdb file. In this case we have a SQL Server 2012 cluster with local tempdb configured on an internal SSD drive in each node and we will use that same path. Open up SSMS and run the following commands. Substitute your own drive letters for your tempdb location.

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'F:\MSSQL11.MSSQLSERVER\MSSQL\Data\tempdb2.ndf', SIZE = 1024);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'F:\MSSQL11.MSSQLSERVER\MSSQL\Data\tempdb3.ndf', SIZE = 1024);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'F:\MSSQL11.MSSQLSERVER\MSSQL\Data\tempdb4.ndf', SIZE = 1024);
GO

In our case we are using local drive tempdb for our cluster and a message appears in the output window stating "Local directory 'F:\MSSQL11.MSSQLSERVER\MSSQL\Data\' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server service has read/write permission on it." This is warning you to ensure the same path with the proper permissions exists on all nodes in the cluster or else SQL Server would fail to start on another node if it is missing the proper path or permissions.

Create additional files for the TempDB database

Let's verify our tempdb file placement by running the following T-SQL code:

Select name, physical_name as CurrentLocation
From sys.master_files
Where database_id = DB_ID(N'tempdb');
GO

Verify our SQL Server tempdb file placement

Now we can run the BPA again to reanalyze the configuration after the additional tempdb files have been added. In this case zero noncompliant configuration settings are reported after making our changes.

Run the BPA again to reanalyze the configuration after the additional tempdb files have been added to ensure there are no additional issues

Final Thoughts

If you follow the steps in this tip you should be compliant according to Microsoft SQL Server 2012 best practices; however your environment may require more or less tempdb data files for optimal performance. Additionally you may want to consider the use of trace flag -T1118 as mentioned in the Microsoft article for certain workloads. As with many technical solutions you should analyze your environment and then closely monitor it for increases or decreases in performance as a result of adding additional tempdb files or utilizing other performance tuning methods.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dan Quinones Dan Quinones is an Architect/Systems Engineer with over 11 years of experience specializing in Microsoft Server and Database technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, June 3, 2013 - 4:42:01 PM - Julia Back To Top (25263)

Dan,

I think the requirements creating as many tempdb datafiles as many processors are on the server are outdated. New servers often have only two six or even eitht-core processors. Tha's a whole reason Microsoft switched to per-core licensing beginning SQL 2012 vs per-processor for previous versions.

In such case: would you recommend 2 tempdb files (one per processor) or 12 - one per a core (2 CPU x 6 cores)?

Thank you,

Julia


Tuesday, December 18, 2012 - 2:14:01 PM - Dan Quinones Back To Top (21019)

Hi Matt,

I would absolutely recommend performing your own performance tests.  Start out with establishing a baseline with your current configuration, follow that up with making any configuration changes as recommended from the BPA and then repeat your performance tests to observe for any increases (or decreases) in performance as a result.  One way to get started is checking out some of the performance tips here, such as Benchmarking SQL Server with SQLIO.

The BPA recommendations are general best practices, which should benefit a majority of configurations out there.  However I would not classify it as a "one size fits all" recommedations, as some environments may benefit from more or less tempdb files, in this case.  

 

Thanks for your comment.


Tuesday, December 18, 2012 - 9:04:29 AM - Matt Lund Back To Top (21007)

Can you do some performance tests with these configs?  I think that would really help convince my team.  Just because Microsoft says whatever they still do not agree...

Anywho...TIA

-- Matt















get free sql tips
agree to terms