By: Dan Quinones | 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.
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.
Using an account with SQL Server sysadmin privileges 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.
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.
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.
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.
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 
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.
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
- Get started with SQL Server 2012 BPA by reading the tip Using the Microsoft SQL Server 2012 Best Practice Analyzer
- Review Recommendations to reduce allocation contention in SQL Server tempdb database
- Check out these other TempDB related tips:
About the author
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
