How to Find the Amount of Fragmentation on Your SQL Server Instance

Need to find the amount of fragmentation in all your indexes in all databases for a given instance of SQL Server 2005 or later? You can use the undocumented/unsupported sp_MSforeachdb to loop through all the databases. For more information on fragmentation see my previous blog http://blogs.msdn.com/cindygross/archive/2009/11/20/sql-server-and-fragmentation.aspx.   — Cindy Gross 2009 — find fragmentation on all indexes inContinue reading “How to Find the Amount of Fragmentation on Your SQL Server Instance”

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 thingsContinue reading “Professional SQL Server 2008 Internals and Troubleshooting”

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 aboutContinue reading “SQL Server’s Default Trace”

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 doContinue reading “Backing up a corrupted SQL Server database”

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 isContinue reading “Compilation of SQL Server TempDB IO Best Practices”

SQL Server and Fragmentation

There are several types of fragmentation that affect SQL Server, all of which have the potential to cause performance problems.   1)      At the disk/OS level individual files can become fragmented due to growth over time. This can be addressed by a defragmentation tool at the SAN or Windows level. Ask your storage team aboutContinue reading “SQL Server and Fragmentation”

SQL Server 2008 setup may report a Windows 2008 cluster validation failure

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 that caused Windows validation to fail. Even though in some cases you couldContinue reading “SQL Server 2008 setup may report a Windows 2008 cluster validation failure”

How to tell what edition your SQL Server setup files are

The setup files for SQL Server look pretty much the same for all editions. If you saved the files somewhere but didn’t indicate in your directory name or a readme file which edition they are for, how do you tell the edition? Running setup doesn’t necessarily help, the initial setup screens for SQL Server 2005Continue reading “How to tell what edition your SQL Server setup files are”

How to use PsGetSid for SQL Server

To translate a SID to a name or vice versa, you can use this tool from sysinternals:     PsGetSid v1.43 http://technet.microsoft.com/en-us/sysinternals/bb897417.aspx   You can use it to translate the SID in these registry keys into the SQL Domain Groups or local groups used for SQL Server 2005/2008 instance permissions. The SID is stored inContinue reading “How to use PsGetSid for SQL Server”