How to configure DTC for SQL Server in a Windows 2008 cluster [This post was revised 11 May 2009 with new mapping information, fixed formatting issues 23 March 2010] With Windows 2008 you now have the option to use multiple DTCs. There is a lot...

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...

Small Bites of Big Data Authors : Cindy Gross Microsoft SQLCAT PM, Ed Katibah Microsoft SQLCAT PM Tech Reviewers: Bob Beauchemin Developer Skills Partner at SQLSkills, Jeannine Nelson-Takaki Microsoft Technical Writer, John Sirmon Microsoft SQLCAT...

Many people are now upgrading from 32bit to 64bit SQL Servers. Most of you have a match between your operating system and your SQL Server platform. For example, most of you install a 32bit SQL Server on 32bit Windows, and if you have the x64 platform...

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...

Configuring MAXDOP One of the few sp_configure options that good DBAs regularly change is “max degree of parallelism” or MAXDOP. So of course everyone is looking for the one best value to use for that setting. As with most things in...

Using Mount Points with SQL Server Author: Cindy Gross, Dedicated Support Engineer Tech Reviewer: Min He, SQL Cluster PM Summary: Microsoft does NOT support installing or putting databases on the “root” of a mount point in SQL...

PowerShell for SQL Server - Basics Cindy Gross, Dedicated Support Engineer Audience: SQL DBAs and/or developers who are familiar with basic SQL Server commands, SQL Server Management Studio (SSMS), and SQL Agent jobs. Basics PowerShell...

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...

It is very important to understand DBCC CHECKDB. Make sure you run DBCC CHECKDB periodically and that if you do find some sort of corruption you address it immediately - both the current problem in the SQL Server database and the underlying problem that...

You still need to follow the standard SQL Server best practices, security, and preparation when you install on a VMware VM. Below are some additional considerations with regards to VMware. Key points: · You must double check that your combination...

I have written about this topic before (in my old SQLCommunity blog) but I still see lots of questions about it. So here is a simplified explanation of this memory trimming issue that is common with SQL Server on Windows 2003. On Windows 2003 you...

Release Candidate 0 (RC0) of SQL Server 2012 is available for download! That means you can start to experience a feature complete version of the next great SQL release. :-) We’re still looking at the first half of calendar year 2012 for the RTM...

If you are using SQL Server or Analysis Services: I suggest you double check that your SNP settings, especially TCP Chimney Offset, are all OFF unless your NIC vendor has verified they support it and you have installed their version of drivers that support...

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...

Leadership – Strengths and Weaknesses As part of my attempt to grow as a leader I took the StrengthsFinder survey. I got this one from the Go Lead Idaho sessions I am attending. It’s based on a Gallup survey and is all based on self...

I have copied this over from an older blog . I have cleaned it up a bit to clarify a few areas and added some links. SQL Server 2005 Clustering Tips/References This information will supplement the clustering information I wrote in chapter...

I've had several people ask how the compatibility mode/level fits in with the SQL Server lifecycle . For example, the question might be: once we no longer support SQL Server 2000, does that mean we don't support databases restored/attached to later versions...

Power Saving Options on SQL Server Windows 2008 and Windows 2008 R2 have settings for “Power Options”. Windows 2008 R2 has additional power improvements related to “core parking” (temporarily suspending certain cores) and...

For Windows 2008 and Windows 2008 R2 you can download a Windows storport enhancement (packaged as a hotfix). This enhancement can lead to faster root cause analysis for slow IO issues. Once you apply this Windows hotfix you can use Event Tracing for Windows...

You may receive the error "The cluster either has not been verified or there are errors or failures in the verification report" while installing a SQL Server clustered installation on Windows 2008. When see this error you must fix the underlying error...

I get a lot of questions about the "best" way to configure the Distributed Transaction Coordinator (DTC) for SQL Server on a Windows 2008 cluster. There is no one best way to do it, and the first question you ask should be "do you even use DTC and if...

Checklist for installing SQL Server 2005 as a clustered instance Windows/Hardware 1) Verify the Windows cluster is set up per basic best practices and that basic failover works. 2) Verify you have the latest patches, especially security patches...

DMV: sys.dm_db_partition_stats This DMV returns information for the current database context. It can tell you how many rows are in each table (per partition) and how much of the space is used and reserved for in-row, overflow, and LOB data (with...

Care and Feeding of the Transaction Log Want to learn more about how the transaction log works? Kalen Delaney, celebrated author of SQL Server 2008 Internals and the Inside SQL Server series as well as a world class trainer and performance tuner...