Cindy Gross | Befriending Dragons

Tag: sql server

  • Moving data between 32-bit and 64-bit SQL Server instances

    I was recently asked about whether SQL Server data can move between architectures, say from x64 to x86.

     

     

    Yes, you can move SQL Server data back and forth between x64, x86, and IA64 architectures. The data and log files themselves do not store anything that indicates the architecture and work the same on either 32-bit or 64-bit. The same applies to the backup files. Given those facts it becomes clear that we can easily move data between architectures. You can backup on x86 and restore to x64. Detach/attach works fine. Log shipping works because it is basically backup/restore with some scheduling. Mirroring and transactional replication take data from the transaction log and push the data to another system so again they work across architectures. Merge replication is basically just another application sitting on top of SQL Server, it moves data by reading tables in one location and modifying data in another location. Again, this can all be done across architectures.

     

    Hopefully you are not installing new x86 boxes, 64-bit handles memory so much better. If you have legacy x86 boxes you can easily do a backup or detach from that old system and restore or attach on the new x64 instance. You can also reverse the process and copy data from x64 back to x86. The same logic applies to the other technologies listed above.

     

    Per BOL (I used the SQL 2008 R2 version):

    ·         The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, a database mirroring session can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.

    ·         Because the SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments, a replication topology can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.

    ·         The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, a log shipping configuration can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.

     

    If you’re doing SAN level replication you’ll need to talk to your SAN vendor about their support across platforms.

     

    Some x64 info:

    http://blogs.msdn.com/cindygross/archive/tags/x64/default.aspx

  • SQL Server Performance Tools – Boise Code Camp Presentation

    Today I am presenting about SQL Server Performance Tools at the Boise Code Camp. You can download the slides and supporting files here on this blog (at the bottom it says Attachment(s): PerformanceTools.zip ). The basic agenda of items covered is:

     

    ¢  Methodology

    ¢  SQLDiag

    ¢  PSSDiag

    ¢  SQLNexus

    ¢  Profiler

    ¢  PerfMon

    ¢  References

    The perfstats script I discussed can be found at:
    And the perfstats analysis tools are at:

    PerformanceTools.zip

  • What do those “IO requests taking longer than 15 seconds” messages on my SQL box mean?

    You may be sometimes seeing stuck/stalled IO messages on one or more of your SQL Server boxes. This is something it is important to understand so I am providing some background information on it.

     

    Here is the message you may see in the SQL error log:

    SQL Server has encountered xxx occurrence(s) of IO requests taking longer than 15 seconds to complete on file [mdf_or_ldf_file_path_name] in database [dbname] (dbid). The OS file handle is 0x…. The offset of the latest long IO is: 0x….”.

     

    The message indicates that SQL Server has been waiting on at least one I/O for 15 seconds or longer. The exact number of times you have exceeded this time for the specified file since the last message is included in the message. The messages will not be written more than once every five minutes. Keep in mind that read IOs on an average system should take no more than 10-20ms and writes should take no more than 3-5ms (the exact acceptable values vary depending on your business needs and technical configuration). So anything measured in seconds indicates a serious performance problem. The problem is NOT within SQL Server, this message indicates SQL has sent off an IO request and has waited more than 15 seconds for a response. The problem is somewhere in the disk IO subsystem. For example, the disk IO subsystem may have more load than it is designed to handle, there is a “bad” hardware or firmware somewhere along the path, filter drivers such as anti-virus software are interfering, your file layout is not optimal, or some IO subsystem setting such as HBA queue depth is not set optimally.

     

    Though the root cause is IO, you can see other symptoms that are a side effect and may lead you down the wrong troubleshooting path. For example, if enough IO is backed up behind the stalled IO then you may see blocking in SQL Server (because locks that are usually taken for very short periods of time are now held for seconds), new connections may not be allowed, and the CPU usage can increase (because many threads are waiting), and a clustered SQL Server can fail over (because the IsAlive checks which are just SQL queries fail to complete like all the other queued queries). You may see other errors returned to the user or in the various logs, such as timeouts.

     

    There are two ways to approach this problem. You can either reduce the IO on the system (change indexes or queries or archive data for example) or you can make the underlying system able to handle the IO load (fix hardware/firmware problems, change configurations, add disks or controllers, change the file layout, etc.).

     

    Background:

    ·         897284  Diagnostics in SQL Server 2000 SP4 and in later versions help detect stalled and stuck I/O operations

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;897284

    ·         Detecting and Resolving Stalled and Stuck I/O Issues in SQL Server 2000 SP 4 http://msdn.microsoft.com/en-us/library/aa175396(SQL.80).aspx

     

    Troubleshooting:

    ·         Every Windows 2003 SP1 or SP2 system should have this storport fix: 941276  A Windows Server 2003-based computer stops responding when the system is under a heavy load and when the Storport driver is being used http://support.microsoft.com/default.aspx?scid=kb;EN-US;941276

    ·         Use PerfMon to look at the disk counters for sec/read, sec/write, bytes/sec, current disk queue length, reads/sec, writes/sec

    ·         Collect data from sys.dm_io_virtual_file_stats and sys.dm_io_pending_io_requests.

    ·         Ask your storage admins to monitor the entire IO subsystem from the Windows system all the way through to the underlying disks.

  • How to Rename SQL Server

    How to rename a SQL Server varies a bit depending on the SQL version, whether it is clustered or not, and whether you want to rename the server/virtual server part of the name (works except for SQL 2000 clusters) or the instance part of the name (requires a reinstall). Also, you do not want to try renaming a server involved in replication as it will break replication (you have to drop/recreate all replication after a rename), and there are extra steps if mirroring is involved (stop mirroring before the rename, change configuration after). Be very careful to include the keyword “local” in the sp_addserver part of the steps (applies only to stand alone systems) and check @@SERVERNAME afterwards to make sure you have completed the steps correctly.

     

    SQL 2008:

    ·         How to: Rename a SQL Server Failover Cluster Instance http://msdn.microsoft.com/en-us/library/ms178083.aspx

    ·         How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server http://msdn.microsoft.com/en-us/library/ms143799.aspx

     

    SQL 2005:

    ·         How to: Rename a SQL Server 2005 Virtual Server http://msdn.microsoft.com/en-us/library/ms178083(SQL.90).aspx

    ·         How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server 2005 http://msdn.microsoft.com/en-us/library/ms143799(SQL.90).aspx

     

    SQL 2000:

    ·         The SQL Server Network Name resource cannot be renamed http://support.microsoft.com/kb/307336

    ·         Renaming a Server http://msdn.microsoft.com/en-us/library/aa197071(SQL.80).aspx

     

    From the BOL topics we can see that you can NOT rename the instance part of the name in SQL Server 2000, 2005, or 2008:

     

    SQL 2005 cluster:

    “The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). Although you can change the name of the virtual server, you cannot change the instance name. For example, you can change a virtual server named VS1instance1 to some other name, such as SQL35instance1, but the instance portion of the name, instance1, will remain unchanged.”

     

    SQL 2005 standalone:

    “These steps can be used only to rename the part of the instance name that corresponds to the computer name. For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance portion of the name, Instance1, will remain unchanged. In this example, the \ComputerNameInstanceName would be changed from \MB1Instance1 to \MB2Instance1.”

     

    SQL 2008 cluster:

    “Although you can change the name of the virtual server, you cannot change the instance name. For example, you can change a virtual server named VS1instance1 to some other name, such as SQL35instance1, but the instance portion of the name, instance1, will remain unchanged.”

     

    SQL 2008 standalone:

    “The following steps cannot be used to rename an instance of SQL Server. They can be used only to rename the part of the instance name that corresponds to the computer name. For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance part of the name, Instance1, will remain unchanged. In this example, the \ComputerNameInstanceName would be changed from \MB1Instance1 to \MB2Instance1.”

     

     

  • SQL DAC

    When you start SQL Server (2005+) it creates a separate “Dedicated Administrator Connection” or DAC using a special TCP port. One sysadmin at a time can connect with this DAC connection by specifying Admin:ServerNameInstance. From SQLCMD you can either prefix the server name with Admin: or you can use the /A switch. From SSMS you can use Admin: to make a “Query Editor” connection but you cannot use it in Object Explorer. DAC should only be used when other connection methods fail and you must collect information or think you might be able to kill some SPIDs to improve the situation. On a local connection you can always use DAC (as long as you are a sysadmin and no one else is using it) but for remote connections (and all connections to a cluster are considered remote) you must have enabled remote connections for DAC:

    EXEC sp_configure ‘remote admin connection’, 1

    RECONFIGURE

     

    If you want to see who if anyone is connected using DAC, try this query:

    SELECT dec.local_tcp_port AS DAC_Port, des.login_name AS LoginName, des.nt_domain AS NTDomain,

          des.nt_user_name AS NTUserName, dec.session_id AS SPID,

          dec.connect_time AS ConnectTime, dec.last_read AS LastRead, dec.last_write AS LastWrite,

          des.host_name AS HostName, dec.client_net_address AS ClientIP, des.program_name AS AppName,

          e.state AS EndpointState, e.is_admin_endpoint AS IsAdminEndpoint

          FROM sys.dm_exec_connections dec

          JOIN sys.endpoints e ON e.endpoint_id = dec.endpoint_id

          JOIN sys.dm_exec_sessions des ON des.session_id = dec.session_id

          WHERE e.name = ‘Dedicated Admin Connection’

     

    If you have any Express Editions, you have to use trace flag 7806 to enable DAC for Express.

     

    Using a Dedicated Administrator Connection

    http://msdn.microsoft.com/en-us/library/ms189595.aspx

     

    How to: Use the Dedicated Administrator Connection with SQL Server Management Studio

    http://msdn.microsoft.com/en-us/library/ms178068.aspx

  • How and Why to Enable Instant File Initialization

    See my new blog post (written with Denzil Ribeiro) about “How and Why to Enable Instant File Initialization” on our PFE blog. Keep an eye on the PFE blog for more posts from my team in the near future.

  • Professional SQL Server 2008 Internals and Troubleshooting

    Our new book, Professional SQL Server 2008 Internals and Troubleshooting, will be shipping soon! Order now! 🙂 Christian Bolton, Justin Langford, Brent Ozar, and James Rowland-Jones have each written several chapters in this book. Steven Wort, Jonathan Kehayias and I each contributed a chapter as well. The 1st half of the book introduces you to how things work within SQL Server at a level that will make it easier to understand the rest of the book. The 2nd half of the book focuses on troubleshooting common SQL Server problems.

    Download the first chapter and find out more about the book here: http://sqlservertroubleshooting.com/.

    Chapters include:

    1. SQL Server Architecture
    2. Understanding Memory
    3. SQL Server Waits and Extended Events
    4. Working with Storage
    5. CPU and Query Processing
    6. Locking and Latches
    7. Knowing Tempdb
    8. Defining Your Approach to Troubleshooting
    9. Viewing Server Performance with PerfMon and the PAL Tool
    10. Tracing SQL Server with SQL Trace and Profiler
    11. Consolidating Data Collection with SQLDiag and the PerfStats Script
    12. Introducing RML Utilities for Stress Testing and Trace File Analysis
    13. Bringing It All Together with SQL Nexus
    14. Using Management Studio Reports and the Performance Dashboard
    15. Using SQL Server Management Data Warehouse
    16. Shortcuts to Efficient Data Collection and Quick Analysis

    http://rcm.amazon.com/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=319D3A&t=cinisthou-20&o=1&p=8&l=as1&m=amazon&f=ifr&asins=0470484284

  • SQL Server’s Default Trace

    Are you familiar with SQL Server’s default trace setting? It can be helpful with finding basic who/when type information on major events. For example, you may want to know who was creating and dropping databases on a given instance.

     

    SQL Server has a couple of options that might help you find out more about when/by who the database is being created and dropped. One is Policy Based Management but you would need to configure it ahead of time. Another option is to run a profiler trace that captures information such as CREATE, ALTER, DROP DATABASE. Some of the DMVs might have the execution information if you capture it fast enough after it happens. XEvents can be used in SQL 2008 to find all sorts of information. However, the one that might be most appropriate in this case is the Default Trace.

     

    1)      Make sure the default trace is enabled in your configuration options for this instance. If it is not enabled, you can enable it through the sp_configure settings.

    — Check to see if the default trace is enabled (0=off, 1=on)

    EXEC sp_configure ‘default trace enabled’

    GO

    — Enabled the default trace

    EXEC sp_configure ‘default trace enabled’, 1

    GO

    RECONFIGURE

    2)      The trace files will eventually overwrite themselves, so check for the output soon after the problem occurs (perhaps make periodic copies of the files). They will be under the log directory where SQL Server is installed. For example, for my SQL 2008 instance named WASH the output files are in C:Program FilesMicrosoft SQL ServerMSSQL10.WASHMSSQLLog. The files will be named log_xxx.trc and there will be up to 5 of them.  

    3)      Find the trace which covers the time period when the database was created or dropped. You can either open it in the Profiler GUI or you can use the query below to pull out the appropriate data. Look for the create and/or drop events and see who executed them from what workstation and at what time. Some applications will send their “application name” so you may be able to tell that as well.

     

    Key Points:

    ·         You cannot control what is captured by the default trace, how many files it captures before rolling over, or any other options. Your only option is to turn it on or off. If you want a similar trace that differs in any way you can create your own and configure it to start when SQL Server starts (or whatever time period is appropriate).

    ·         The trace file name/number will continue to increase until you delete the files.

    ·         The trace does NOT capture all events, it is very lightweight.

     

    References:

    ·         SQL Server 2008 Internals – Chapter 1 page 73

    ·         Searching for a Trace – Solving the mystery of SQL Server 2005’s default trace enabled option http://www.sqlmag.com/Articles/ArticleID/48939/pg/1/1.html

    ·         SQL Server Default Trace http://blogs.technet.com/beatrice/archive/2008/04/29/sql-server-default-trace.aspx

    ·         Default Trace in SQL Server 2005 http://blogs.technet.com/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx

    ·         Default Trace in SQL Server 2005 http://www.mssqltips.com/tip.asp?tip=1111

     

    Query:

    — Example of using the default trace to find out more about who/when/why a database is dropped or created

     

    — Get current file name for existing traces

    SELECT * FROM ::fn_trace_getinfo(0)

     

    — CHANGE THIS VALUE to the current file name

    DECLARE @Path nvarchar(2000)

    SELECT  @Path = ‘C:Program FilesMicrosoft SQL ServerMSSQL10.WASHMSSQLLoglog_120.trc’

     

    — Get information most relevant to CREATE/DROP database

    SELECT SPID, LoginName, NTUserName, NTDomainName, HostName, ApplicationName, StartTime, ServerName, DatabaseName

          ,CASE EventClass

                WHEN 46 THEN ‘CREATE’

                WHEN 47 THEN ‘DROP’

                ELSE ‘OTHER’

           END AS EventClass

          , CASE ObjectType

                WHEN 16964 THEN ‘DATABASE’

                ELSE ‘OTHER’

           END AS ObjectType

          –,*

    FROM fn_trace_gettable

    (@Path, default)

    WHERE ObjectType = 16964 /* Database */ AND EventSubClass = 1 /* Committed */

    ORDER BY StartTime

    GO

     

    /* BOL

     

    == Event Class

    46 Object:Created

     Indicates that an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.

    47 Object:Deleted

     Indicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.

     

     == Object Type

     16964 Database

     

    == EventSubClass

     int Type of event subclass.

    0=Begin

    1=Commit

    2=Rollback

     */

     

  • Backing up a corrupted SQL Server database

    I had a question about how to do a backup and skip a corrupted block of data. First, DO NOT DO IT unless you absolutely have to, such as when you are taking a backup prior to trying to fix the corruption (which means you should be on the phone with Microsoft PSS). If you do skip corrupted data you have to consider the backup to be very suspect.

     

    Do not ever ignore any indication of data inconsistency in the database. If you have corrupted data it is almost certainly a problem caused by something below the SQL Server level. If it happened once, chances are it will happen again… and again…. and again until the source of the problem is fixed. This means the instant you have any indication of a corrupt SQL Server database you should immediately ask for low-level hardware diagnostics and a thorough review of all logs (event viewer, SQL, hardware, etc.). Double check that if write caching is enabled on the hardware that it is battery backed and the battery is healthy. Double check that all firmware is up to date. Run a DBCC CHECKDB WITH ALL_ERRORMSGS and pay very close attention to the output. Find the source of your corruption and fix it.

     

    There is a parameter CONTINUE_AFTER_ERROR for BACKUP and RESTORE, but it is a last ditch command that should only be used as a last resort. One example would be if it’s the only way to get a backup before you attempt to repair the corruption. It does not always work, it depends on what the error is. If you actually have to restore a database backup taken with this option, then you MUST fix the corruption before allowing users, applications, or other production processes back into the database. From BOL:

    “We strongly recommend that you reserve using the CONTINUE_AFTER_ERROR option until you have exhausted all alternatives.”

    “At the end of a restore sequence that continues despite errors, you may be able to repair the database with DBCC CHECKDB. For CHECKDB to run most consistently after using RESTORE CONTINUE_AFTER_ERROR, we recommend that you use the WITH TABLOCK option in your DBCC CHECKDB command.”

    “Use NO_TRUNCATE or CONTINUE_AFTER_ERROR only if you are backing up the tail of a damaged database.”

     

    Some suggestions:

    ·         For every 2005/2008 database, SET PAGE_VERIFY=CHECKSUM (in 2005 this cannot be turned on for TempDB, but it can be turned on for TempDB in 2008). For SQL Server 2000 set TORN_PAGE_DETECTION=ON. When upgrading from 2000 to newer versions set TORN_PAGE_DETECTION=OFF and SET PAGE_VERIFY=CHECKSUM.

    ·         For databases with CHECKSUM enabled, use the WITH CHECKSUM command on all backups.

    ·         Implement a “standards” or “best practices” document to handle corruption on each version of SQL Server.

    ·         Review your disaster recovery plans and upcoming testing. Testing of a full recovery of various scenarios should be done periodically. Some people think once a year is enough, others say monthly or quarterly is often enough. Having backups is not good enough, we have to know that they can be restored. There are also scenarios where backups are not the best way to recover from a problem.

     

    Some great info from the person who wrote CHECKDB:

    http://sqlskills.com/blogs/paul/post/Example-20002005-corrupt-databases-and-some-more-info-on-backup-restore-page-checksums-and-IO-errors.aspx

    http://sqlskills.com/BLOGS/PAUL/category/Corruption.aspx

  • Compilation of SQL Server TempDB IO Best Practices

    It is important to optimize TempDB for good performance. In particular, I am focusing on how to allocate files.

     

    TempDB is a unique database in several ways. The ones most relevant to this discussion are:

    ·         It is often one of the busiest databases on an instance. This means the performance of TempDB is critical to your instance’s overall performance.

    ·         It is recreated as a copy of model each time SQL Server starts, taking all the properties of model except for the location, number, and size of its data and log files.

    ·         TempDB has a very high rate of create/drop object activity. This means the system metadata related to object creation/deletion is heavily used.

    ·         Slightly different logging and latching behavior.

     

    General recommendations:

    ·         Pre-size TempDB appropriately. Leave autogrow on with instant file initialization enabled, but try to configure the database so that it never hits an autogrow event. Make sure the autogrow growth increment is appropriate.

    ·         Follow general IO recommendations for fast IO.

    ·         If your TempDB experiences metadata contention (waitresource = 2:1:1 or 2:1:3), you should split out your data onto multiple files. Generally you will want somewhere between 1/4 and 1 file per physical core. If you don’t want to wait to see if any metadata contention occurs you may want to start out with around 1/4 to 1/2 the number of data files as CPUs up to about 8 files. If you think you might need more than 8 files we should do some testing first to see what the impact is. For example, if you have 8 physical CPUs you may want to start with 2-4 data files and monitor for metadata contention.

    ·         All TempDB data files should be of equal size.

    ·         As with any database, your TempDB performance may improve if you spread it out over multiple drives. This only helps if each drive or mount point is truly a separate IO path. Whether each TempDB will have a measurable improvement from using multiple drives depends on the specific system.

    ·         In general you only need one log file. If you need to have multiple log files because you don’t have enough disk space on one drive that is fine, but there is no direct benefit from having the log on multiple files or drives.

    ·         On SQL Server 2000 and more rarely on SQL Server 2005 or later you may want to enable trace flag -T1118.

    ·         Avoid shrinking TempDB (or any database) files unless you are very certain you will never need the space again.

     

    References:

    ·         Working with tempdb in SQL Server 2005 http://technet.microsoft.com/en-us/library/cc966545.aspx

    o   “Divide tempdb into multiple data files of equal size. These multiple files don’t necessarily be on different disks/spindles unless you are also encountering I/O bottlenecks as well. The general recommendation is to have one file per CPU because only one thread is active per CPU at one time.”

    o   “Having too many files increases the cost of file switching, requires more IAM pages, and increases the manageability overhead.”

    ·         How many files should a database have? – Part 1: OLAP workloads http://sqlcat.com/technicalnotes/archive/2008/03/07/How-many-files-should-a-database-have-part-1-olap-workloads.aspx

    o   If you have too many files you can end up with smaller IO block sizes and decreased performance under extremely heavy load.

    o   If you have too few files you can end up with decreased performance to GAM/SGAM contention (generally the problem you see in TempDB) or PFS contention (extremely heavy inserts).

    o   The more files you have per database the longer it takes to do database recovery (bringing a database online, such as during SQL Server startup). This can become a problem with hundreds of files.

    ·         SQL Server Urban Legends Discussed http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

    o   ” SQL Server uses asynchronous I/O allowing any worker to issue an I/O requests regardless of the number and size of the database files or what scheduler is involved.”

    o   ” Tempdb is the database with the highest level of create and drop actions and under high stress the allocation pages, syscolumns and sysobjects can become bottlenecks.   SQL Server 2005 reduces contention with the ‘cached temp table’ feature and allocation contention skip ahead actions.”

    ·         Concurrency enhancements for the tempdb database http://support.microsoft.com/kb/328551

    o   Note that this was originally written for SQL Server 2000 (the applies to section only lists 2000) and there are some tweaks/considerations for later versions that are not covered completely in this article. For example, -T1118 is not only much less necessary on SQL Server 2005+, it can in some cases cause problems.

    ·         FIX: Blocking and performance problems may occur when you enable trace flag 1118 in SQL Server 2005 if the temporary table creation workload is high http://support.microsoft.com/default.aspx?scid=kb;EN-US;936185

    o   If you have SP2 based CU2 or later you will not see the problems described in this article. Also, on SP2 based CU2 or higher you are much less likely to even need -T1118 on a heavily used TempDB.

    o   ” This hotfix significantly reduces the need to force uniform allocations by using trace flag 1118. If you apply the fix and are still encountering TEMPDB contention, consider also turning on trace flag 1118.”

    ·         Misconceptions around TF 1118 http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx

    o   ” turn on TF1118, which makes the first 8 data pages in the temp table come from a dedicated extent “

    o   “Instead of a 1-1 mapping between processor cores and tempdb data files (*IF* there’s latch contention), now you don’t need so many – so the recommendation from the SQL team is the number of data files should be 1/4 to 1/2 the number of processor cores (again, only *IF* you have latch contention). The SQL CAT team has also found that in 2005 and 2008, there’s usually no gain from having more than 8 tempdb data files, even for systems with larger numbers of processor cores. Warning: generalization – your mileage may vary – don’t post a comment saying this is wrong because your system benefits from 12 data files. It’s a generalization, to which there are always exceptions.”

    ·         Storage Top 10 Best Practices http://sqlcat.com/top10lists/archive/2007/11/21/storage-top-10-best-practices.aspx  

    o   “Make sure to move TEMPDB to adequate storage and pre-size after installing SQL Server. “

    o   “Performance may benefit if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage). “

    o   “This is especially true for TEMPDB where the recommendation is 1 data file per CPU. “

    o   “Dual core counts as 2 CPUs; logical procs (hyperthreading) do not. “

    o   “Data files should be of equal size – SQL Server uses a proportional fill algorithm that favors allocations in files with more free space.

    o   “Pre-size data and log files. “

    o   “Do not rely on AUTOGROW, instead manage the growth of these files manually. You may leave AUTOGROW ON for safety reasons, but you should proactively manage the growth of the data files. “

    Optimizing tempdb Performance http://msdn.microsoft.com/en-us/library/ms175527.aspx