Sometimes what seems like a reasonable thing to do to SQL Server can in fact hurt it -- often to the point that it negatively impacts performance. Here are five things you should stop doing immediately on all of the SQL Servers you’re responsible for:
1. Configuring automatic "stuff"
If you look in your database’s settings you’ll notice several “auto” settings. In general, I like to see Auto Generate Statistics and Auto Update Statistics turned on because these help ensure that SQL Server can select the best query execution plans given the current state of your database, its indexes and so on.
When it comes to database files, however, I usually like to see Auto Grow and Auto Shrink turned off. Yes, Auto Grow can save you from an embarrassing situation when a database or log file fills up, but it can also have a really negative impact on performance. I’d rather keep an eye on file size and manually grow files during a maintenance window as needed.
2. Processor and memory configuration
Don’t throttle SQL Server’s use of memory. Windows and SQL Server are perfectly capable of negotiating who gets how much memory, even when other processes are running on the same server. Remember -- you don’t know better than Windows does from moment-to-moment.
The same goes for processor affinity – don’t configure it. In today’s world of multi-socket, multi-core processors, Windows does a much better job of multithreading SQL Server when it can move threads to whatever processor cores it wants to based on the conditions at that time.
3. Index maintenance
This is a biggie. The best effect you can have on SQL Server performance is through the regular reorganization of indexes for databases that have data added, changed or removed. Rebuilding indexes from time to time -- I do it monthly during a maintenance window for busy databases -- is a chance for SQL Server to recreate indexes so that they can be best used by queries. A properly maintained index can also speed up database writes because the index will have free space to accommodate new data.
4. The SQL Authentication account
I wouldn’t be surprised if everyone at Microsoft wished that databases in general never had this “super user” account. This is because developers now think they should hardcode it – and its password – into their applications.
You should look into something called application roles and try to convince your developers to switch over to them. It’s only a minor change in database connection strings and it’ll give developers much of the same functionality without needing access to that all-powerful SA account.
If you’re taking even a daily full backup of a busy database, you’re not doing enough. Who wants to lose a full day’s worth of data? Configure transaction log backups as often as every quarter-hour. They’re quick, easy to dump to a separate disk, and can help you recover most of your work in a very granular fashion.
Start looking at these quick fixes on all of your SQL Server computers and you’ll be on the path to better performance and maintainability in no time.
Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.