SQL Server Performance Blog

Donabel Santos (twitter (@sqlbelle) | blog) – April 25, 2011 “Statistics provides tools that you need in order to react intelligently to information you hear or read” – David Lane, 2003 If there’s an upcoming election and you are running for office and getting ready to go from town to town city to city with [...]

Donabel Santos (twitter (@sqlbelle) | blog) – March 29, 2011 I’ve worked on a project that used XML heavily inside SQL Server. We really utilized SQL Server’s XML support almost to the full extent, but with some repercussions. As we did our load testing, performance did degrade and we had to step back and adjust [...]

A common sentiment that prevails in many SQL Server environments or shops is that when query performance begins to lag, it’s time to add new indexes. In many ways the existence of this sentiment helps underscore just how powerful indexes are when it comes to performance. And while it’s true that a core performance tuning consideration is the need to properly identify and add indexes, it’s important to remember that the addition of every index comes with a degree of overhead required to keep that index properly updated during UPDATE, INSERT, and DELETE operations. In other words, while adding properly defined indexes will typically provide substantial performance benefits, the existence of too many indexes can come with a penalty. Likewise, given the overhead associated with maintaining indexes, a general rule of thumb is that non-used indexes should be dropped if they’re truly not needed. (Or, if they’re needed only cyclically – say at the end of each quarter for special reports – it can even be a good idea to drop add them prior to special execution requirements and then drop them when done.)