Best practices are absolutely relevant; they’re not gospel or absolutes, but they should be part of every DBA’s repertoire to help make appropriate, informed decisions. I think of best practices like my mom’s voice in the back of my mind whenever I go to grab a pot on the stove, “Use a towel, that’s probably going to burn you.” My neurosis aside, ignoring best practices can do just that - leave you burned.

Let’s look at two very basic “Best Practices”:

1. Disabling Auto-shrink
2. Fully-qualified Table Names

Disabling Auto-shrink
Auto-shrink can lead to serious fragmentation problems and resource contention at inopportune moments. The problem is auto-shrink works really well…at shrinking files. But shrinking files doesn’t mean shrinking seek times, and the “automatic” nature of the feature means it can fire during peaks in production workload processing. What auto-shrink is acutally doing is moving data pages, beginning from the last page, to the first available free slot available. In a previous post I mentioned that DBAs need to develop a plan for dealing with physical and logical fragmentation to help maintain query performance. Diskeeper or T-SQL can be used to deal with physical fragmentation, while logical fragmentation can be handled using DBCC DBREINDEX or ALTER INDEX … REBUILD. The thing is, even with a good maintenance plan, leaving auto-shrink enabled means that the feature could fire during or immediately after your maintenance task, effectively negating the defragmentation effort. Here’s an example of how enabling auto-shrink can take a new index from 0% fragmentation to 100% fragmentation in a single run. The example shows how an entire index is reorganized in opposite order (100% fragmented). Ouch.

Fully-qualified Table Names
It all comes down to plan reuse. Microsoft has published two excellent papers on this topic, the first is Troubleshooting Performance Problems in SQL Server 2005 and the second is Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005. The Cliff’s Notes summary is simply that plan reuse is jeopardized when objects are referenced without their schema prefix. I said that best practices are a guideline, so if you really don’t want to implement fully-qualified object names or you’re doubtful of the impact, look at the uid (user ID) column in sys.syscacheobjects. Only query plans with the same user ID can be reused. So, when the value for uid is -2, the query does not depend on implicit name resolution and can be shared among different user IDs. Of course, you could bypass the extra checking and just fully-qualify the object names (pick the pot up with a towel).