• Home
• Topics
• Microsoft SQL Server Administration
• Microsoft SQL Server Performance Monitoring and Tuning
• Configure SQL Server 2005 TempDB for performance

For additional information about the SQL Server hard drive recommendations, reference Optimize SQL Server hardware performance, which outlines hardware best practices.

How do I move TempDB to a new disk drive?

ALTER DATABASE is the key T-SQL command to perform this operation. Microsoft offers a good example in SQL Server 2005 Books Online. The article name is ALTER DATABASE (Transact-SQL) and the specific section is 'G. Moving tempdb to a new location.'

How many database files should TempDB consist of in SQL Server?

Conventional wisdom has been to have a single database and transaction log file. Microsoft has subsequently communicated having a single file for each physical CPU on the SQL Server. For example, if you have 2 CPUs, then have 2 TempDB database files.

What other configurations are important?

Ensure that the TempDB files are preallocated and do not auto grow, which means SQL Server does not need to unnecessarily manage the database growth. If TempDB is undersized, then preallocate each of the files in equal increments so all files remain the same size.

For example, here is how TempDB can be configured on a 2-CPU SQL Server in a small environment:

What is the rule of thumb when sizing TempDB?

Based on your environment, the minimum size recommendations for TempDB are as follows:

You are the best judge of your environment and can scale this database to a much larger configuration to meet your needs.

About the author:Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Kadlec is SearchSQLServer.com's Performance Tuning expert. Ask him a question here.

More information from SearchSQLServer.com

• Webcast: SQL Server performance tuning myths
• Tip: SQL Server Clinic: T-SQL performance problems and solutions
• Ask the Experts: Ask Jeremy Kadlec your performance and tuning questions
• 
  

More from Related TechTarget Sites

• Business Analytics
• Data Center
• Data Management
• Data Management UK
• Oracle
• Content Management
• Windows Server

• Business Analytics

  • 'Big data' analytics spawns a revolution

    'Big data' analytics could fundamentally change how businesses operate, according to a keynote speaker at the SAS Analytics conference.

  • Apache Hadoop spurs hopes but creates hardships for analytics users

    Described as cutting edge, hot, niche and hard to use, Hadoop, like all celebrities, has its shining moments and its dismal displays.

  • Download the Big Data Report

    When you download this e-guide you will get expert strategies from Wayne Eckerson about how to integrate with Hadoop, some of the big data trends that is is seeing plus more about the future of Hadoop.

• Data Center

  • How integrated virtualization makes a UCS work

    Unified computing pulls servers, SAN connections and networking on to one platform. Integrated virtualization aims to make management easier.

  • Flexible computing in business: Move to cloud computing or go modular?

    A move to cloud computing could provide business IT with flexibility, but what are the options for those who aren't quite convinced about cloud?

  • Sales up for 16 Gbps Fibre Channel; is it going the way of the dodo?

    Sales for 16 Gbps Fibre Channel swell to their highest volume since 2008, but questions remain about the technology's future.

• Data Management

  • Zornes connects the dots between big data and master data management

    Consultant Aaron Zornes believes that MDM and big data were made for one another.

  • IT pros reveal benefits, drawbacks of data virtualization software

    IT pros attending Composite Software's Data Virtualization Day conference had plenty of advice for those considering a data virtualization project.

  • IBM takes on Oracle, SAP with new integrated data management systems

    IBM is introducing three new PureSystems high-performance appliances optimized for transactional and analytics uses involving large amounts of data.

• DataManagement UK

  • Dynamic Markets: CFOs see worth in data on balance sheet

    About 20% of UK companies are assigning a financial value to their data, according to research sponsored by SAS. But only 16% of UK companies have a formal data management strategy.

  • Data ethics: Author warns of ethical pitfalls of data collection

    Heedless data collection in business analytics programmes is breeding moral hazards, according to author Frank Buytendijk.

  • Data visualisation success hinges on solid storytelling skills

    Tableau's Jock Mackinlay explains why data is inert and worthless without the twin practices of visualisation and storytelling.

• Oracle

  • How HR Increases Value

    Modern applications and leading technology provided the foundation for Oracle to become what it is today. And so did their talent pool. How can you benefit from the way they built their strategic HR function—away from the administrative back office—to drive business in support of a company that was shooting for 80 acquisitions over an 8 year timeframe? Hint: they used IT to consolidate onto a single system and embraced an HR transformation that had never been seen before.

  • Social Recruiting Guide: How to Effectively Use Social Networks and Avoid Legal Risks

    Social networking sites are some of the most powerful tools available to recruiters today. If you're not sure where to start with a social recruiting strategy, this paper outlines why social recruiting is becoming an imperative today, what considerations you need to be aware of, and what first steps you can take. Plus you get the top 7 questions to consider before you make the leap to social networking.

  • Building Critical-Talent Pipelines

    Get a 12-step methodology for building critical-talent pipelines that are supported by a robust technology platform designed to allow you to accurately assess and predict gaps in critical skills and competencies, design more-effective development programs and recruiting strategies, improve ongoing efforts and introduce hidden efficiencies within your current infrastructure, and more.

• Content Management

  • Well planned is well executed with an information governance framework

    While there are common foundational pillars for effective information governance, there is no one-size-fits-all approach and success is typically tied to execution.

  • Secrets of Effective SharePoint Deployments

    Secrets of Effective SharePoint Deployments

  • Social task management can replace spreadsheets for business projects

    The emergence of social task management can foster collaboration on projects, according to a Constellation Research analyst.

• Windows Server

  • Word, Kerberos vulnerabilities highlight busy October Patch Tuesday

    Improperly time stamped certificates mean rereleased patches, on top of fixes for Microsoft Word and Kerberos vulnerabilities this month.

  • Ensuring hardware compatibility with a Windows Server 2012 upgrade

    With Windows Server 2012 available, admins have a lot to consider when it comes to hardware. Here's what you need to know before you make the move.

  • Windows Server 2012 failover clustering makes high availability possible

    In Windows Server 2012, failover clustering is configured to work with scale-out file servers for continuous availability. Here's how it works.

• About Us
• Contact Us
• Site Index
• Privacy policy
• Advertisers
• Business partners
• Events
• Media kit
• TechTarget Corporate site
• Reprints
• Site map