The size of tempdb can affect the performance of a system. For example, if the tempdb size is too small, the system processing could be too occupied with autogrowing the database to support your workload requirement every time that you start SQL Server. You can avoid this overhead by pre-sizing tempdb. For more information, see Optimizing tempdb Performance and Capacity Planning for tempdb.
Optimizing tempdb Performance
- Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload for the environment. This prevents tempdb from expanding too frequently, which can adversely affect performance. The tempdb database should be also set to autogrow, just in case of unplanned growth. But ideally, any tempDB expansions should be rare.
-
Put tempdb on a fast I/O subsystem. Use RAID 10 if there are sufficient directly attached disks available. Consider using directly attached Solid State Disks (SSD), such as a FusionIO drive.
-
Create multiple data files to maximize disk bandwidth. Using multiple data files can reduce tempdb contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create as many data files as 1/2 – 1/4 times the number of CPU cores. [Note that a quad-core CPU is considered to be four CPUs.]
-
Make each data file exactly the same size; this allows for optimal proportional-fill performance. Consider tuning on Trace Flag -T1116
-
Put the tempdb database on disks (physical spindles) separate from those used by user databases.
If you’re seeing PAGELATCH (not PAGEIOLATCH) waits on tempdb, then you can mitigate these using trace flag 1118 and creating multiple tempdb data files. Paul Randal wrote a blog post debunking some myths around this trace flag and why it’s still potentially required in SQL 2005 and 2008 – Misconceptions around TF 1118.
-- 1.Determine the logical file names of the tempdb database and current location on disk.
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
-- 2.Change the location of each file using ALTER DATABASE.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Data\tempdb.mdf', SIZE = 512MB, FILEGROWTH = 128MB);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\Logs\templog.ldf', SIZE = 64MB, FILEGROWTH = 64MB);
GO
-- 3.Stop and restart the instance of SQL Server.
net stop "SQL Server"
-- or
net stop "SQL Server (namedinstance)"
-- 4.Verify the file change.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
smoServer = ![smoServer gm </span></pre> So having done that we found a property named <a href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.defaultfile.aspx">DefaultFile</a> which looked promising, but it just returned an empty string. After a bit of digging around, it transpires that it only returns a path if the current location is different to where the master DB is located, so here it is in Powershell: <pre class="csharpcode"><span class="kwrd">function</span> Get-SQLServerDefaultDataFilePath( [<span class="kwrd">string</span>]](https://i0.wp.com/mitchwheat.com/wp-content/ql-cache/quicklatex.com-35dd1d61373d8311c1fda836c8af96a6_l3.png?resize=675%2C217&ssl=1 "Rendered by QuickLaTeX.com")
sqlServer = ![(<span class="kwrd">throw</span> <span class="str">'sqlServer is required'</span>)){ [reflection.assembly]::LoadWithPartialName(<span class="str">"Microsoft.SqlServer.Smo"</span>) >](https://i0.wp.com/mitchwheat.com/wp-content/ql-cache/quicklatex.com-7a11983740e13b5419d54ac7264e1d05_l3.png?resize=825%2C56&ssl=1 "Rendered by QuickLaTeX.com")
sqlServer
smoServer.DefaultFile
str}