There are a number of dials and knobs in SQL Server that allow the DBA to fine tune almost every aspect of the server. In this post I highlight five quick wins that every DBA can enable on their server to improve performance. The best part is they will take all of three seconds of your time. As with almost any SQL Server setting configuring it incorrectly can cause harm. This make it difficult for the DBA to make changes without investigating the impact. The below options are ones I have found from experience DBAs can implement without making things worse.
Backup Compression
Introduced in SQL 2008 backup compression has become a favorite with DBAs because of the amount of space savings it offers. This feature has practically no downside. Sure if you google around you will get the same issue being touted around that it consumes extra CPU resources. It would be like complaining about the size of 2000 Rs note and so continuing to carry 20 X100 Rupees note instead. The significant improvement in IO far outweighs any hit (if at all) on the CPU. I enable this everywhere I go simply because maintenance windows are shrinking every day and with every release. We need all the time we can get and this one option can free up a significant portion of you maintenance window.
EXEC sp_configure 'backup compression default', 1 ; GO RECONFIGURE; GO
Optimize for AdHoc Workloads
If you are running for class president it might make sense for you to remember the name of all people you meet, even if you only deal with them once in a year. But if you are running for president of the country it is impractical to try and remember the names of everyone you met. It puts tremendous strain on your memory. Similarly it’s not worth the effort to try and store execution plans for queries that are Adhoc in nature and typically executed only once. By enabling the above Server option – SQL Server only stores a plan stub (not the execution plan) when a query is executed the first time. A plan stub is a polite way of saying “Hey buddy” instead of “Hey Jayanth” if you were president. If you see the same query executed a second time SQL knows there is a plan stub in place and therefore stores the execution plan. You can enable it using the script below
EXEC sp_configure 'optimize for ad hoc workloads', 1 ; GO RECONFIGURE; GO
Fill Factor
I don’t even know why this is a server level option to begin with. Fill Factor affect the amount of free space in a pager after the index is rebuilt. If you have been a DBA for even a few hours you will know that not all your tables are highly transactional tables. Even fewer tables have DML operations that affect the clustered index or any index key columns sort order. As a result the fill factor only matters for a few tables that encounter significant page splits. For the rest the Fill Factor by default should be 100%. Anything that’s missed should be identified and fill factor changed in the index rebuild statement. But if the default is 100% why am I even mentioning it here? Because I’ve seem articles on the internet recommending they be set to 90%.
EXEC sp_configure 'fill factor (%)', 100 ; GO RECONFIGURE; GO
More Info
In depth series on Fill Factor
Please Consider Subscribing
