Most Recent Blog Posts

04/20/2011 - 11:28am

The effect of optimize for adhoc workloads–is this right?

Question: I recently read Kimberly’s post on USECOUNT=1 plans, and your related article Should I Optimize My SQL Server Instances for Ad Hoc....More
04/09/2011 - 7:56am

Why can a database snapshot run out of space?

A database snapshot does not reserve space when it is created so it is quite possible for it to run out of space (and hence become unusable)....More
04/04/2011 - 8:15am

Size-of-data operations when adding and removing columns

There are two main sets of column metadata maintained by SQL Server – one for the relational definition of the table, and one for how the columns are....More
03/22/2011 - 4:38pm

When do checkpoints occur for tempdb?

A checkpoint has two purposes: firstly to write out data pages so that what is in the data files is up-to-date with what is in the log, so there is....More
03/11/2011 - 11:11am

When did you last test your disaster recovery strategy?

When did you last test your SQL Server disaster recovery strategy? It’s all very well having a strategy, but how do you know it works?....More
03/01/2011 - 5:57pm

Why does my transaction log grow but my log backups don’t?

Whenever a database is changed, transaction log records are generated that describe the changes that are made. If something goes wrong with the....More
Boxer with red boxing gloves
02/20/2011 - 8:31pm

Dynamic SQL vs. OPTION (RECOMPILE)

Is it better to use OPTION (RECOMPILE) rather than dynamic SQL? Both options are likely to recompile each time, and forcing recompilation with OPTION....More
02/13/2011 - 4:56pm

Problems with Local Variables

Using local variables in statements and procedures can cause poor cardinality estimates. Alternative solutions can include rewriting the query to use....More
01/28/2011 - 2:56pm

Determine the Performance Effect of Query Plan Changes

How can you tell whether the stored procedure query plan changes you make actually improve your CPU time and logical reads?....More
01/24/2011 - 4:17pm

How Does Index Fragmentation Affect SELECT Performance?

To preserve application architecture, we want to retain the GUID as a primary key when clustering on a new INT column that’s not used by the....More
01/13/2011 - 5:15pm

How Can Fixing Database Corruption Shrink a Database?

How can I repair database corruption as quickly as possible if I don’t have workable backups? And after repairing some database corruption, my....More
12/27/2010 - 4:46pm

Why Doesn’t New Hardware Enhance SQL Server Performance?

We recently implemented a new server with faster CPUs, more cores, and double the amount of memory. We restored our production database to it and....More
12/13/2010 - 4:37pm

Choosing Default Sizes for Your Data and Log Files

Once you’ve chosen how you’re going to manage your transaction log, you can do capacity planning. Part of why you need to determine how you’re going....More
12/01/2010 - 9:34pm

When a Delete isn’t Really a Delete…

When I delete lots of data from a table with a clustered index using a DELETE statement, the table size isn’t reduced like I would expect it to be....More
11/26/2010 - 3:37pm

Should I Optimize My SQL Server Instances for Ad Hoc Workloads?

In general, I recommend that the SQL Server 2008 configuration option be enabled on all my customers’ servers. The original idea behind the option....More
Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×