SQL Server, Analytics, .Net, Machine Learning, R, Python
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Thursday, March 03, 2011
SQL Server: Optimizing tempdb Performance : Moving tempDB to a New Location
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
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.
MSN, Email: mitch døt wheat at gmail.com