Shrinking SQL Server Data Files – Best Practices, and Why It Sucks

I’ve gotten a few questions about shrinking SQL Server data files lately. What’s the best way to get shrink to run? And why might it fail in some cases? Traditionally, every time you ask a DBA how to make shrinking suck less, they start ranting how shrinking is bad and you just shouldn’t do it. Sometimes it sounds kinda angry. What people are trying to say is that shrinking data files is generally slow, frustrating, and painful for you. Shrinking data files sucks, and you don’t really have many ways to make it suck less Here’s what you’re up against, when it comes to shrinking: Shrinking can cause blocking while it runs. Here’s a post I wrote a while back with a demo script to reproduce the blocking. Shrinking may stop running and not tell you why. In one case, DBCC SHRINKFILE was stopping because it was hitting a deadlock and being declared the deadlock…
Read More

Required Testing for Installing SQL Server Cumulative Updates and Service Packs

Microsoft recently updated their policies and recommendations for installing cumulative updates. Now, in the header for each cumulative update, it reads: Microsoft recommends ongoing, proactive installation of CUs as they become available: SQL Server CUs are certified to the same levels as Service Packs, and should be installed at the same level of confidence. Historical data shows that a significant number of support cases involve an issue that has already been addressed in a released CU. CUs may contain added value over and above hotfixes. This includes supportability, manageability, and reliability updates. But Important Things Can Go Wrong in Cumulative Updates and Service Packs It says something else in the header of the cumulative update: Just as for SQL Server service packs, we recommend that you test CUs before you deploy them to production environments. That’s because nobody’s perfect. Something may break in your environment after you install a Service Pack…
Read More

Managing Statistics in SQL Server for DBAs (videos)

Want to learn more about managing statistics updates in SQL Server? Watch my free 27 minute presentation on managing statistics: You can also watch 12 minutes of audience Q&A on statistics from when I presented this on a live Hangout on Air. Questions include: Should I create single column statistics for non-leading key columns in an index? What is asynchronous statistics update? What are these statistics with “WA_sys” names? I have a bunch of user created statistics and it’s blocking a release. It is safe to drop them? Got more questions on statistics? Check out my reference post on Updating Statistics in SQL Server, and ask away in the comments of the post!
Read More

Updating Statistics in SQL Server: Maintenance Questions & Answers

I’ve been asked a lot of questions about updating statistics in SQL Server over the years. And I’ve asked a lot of questions myself! Here’s a rundown of all the practical questions that I tend to get about how to maintain these in SQL Server. I don’t dig into the internals of statistics and optimization in this post. If you’re interested in that, head on over and read the fahhhbulous white paper, Statistics Used by the Query Optimizer in SQL Server 2008. Then wait a couple days and chase it with it’s charming cousin, Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator. I’m also not talking about statistics for memory optimized tables in this article. If you’re interested in those, head over here. Quick Links to Jump Around This Article General Advice on Statistics Maintenance Which Free Third Party Tools Help Update Statistics? What are Statistics? What Creates Column Statistics? What are…
Read More