Skip to Content

Blog Team Contact

Completely Legal Performance Enhancements

There are a few groups of performance improvements that we talked about:

Low Level

These are OS and disk level performance fixes. They’re easy to set up correctly, but they’re a bit trickier to fix once you’re up and running.

64-bit everything Although everything should be 64-bit by now, sometimes there’s a server that’s old enough or important enough that it’s still a 32-bit SQL Server. Making the switch to a 64-bit environment will greatly increase the throughput of applications.

Disk Partition Alignment This isn’t an issue if you’re using disks that were formatted on Windows Server 2008 or newer, but if you’re migrating disks from older machines, this could be a big problem. This Microsoft whitepaper on partition alignment explains the problem beautifully and provides examples of the performance benefit of using aligned partitions.

TempDB Moving TempDB to its own drive may not be enough. I typically recommend adding additional TempDB files – these can all be on the same drive. Brent explains more in his SQL Server Post Install Checklist.

File Growth Management It’s possible to configure data files and log files so that they rarely grow. Rodney Landrum has a great article about managing data growth in SQL Server. You can also check out the SQL Server Post Install Checklist for some other great tips like turning on Instant File Initialization.

Indexes & Statistics

SARGability is a word that’s difficult to say. A query is SARGable when SQL Server can use indexes to make the query run faster. You can get a better understanding in the article Sargability: Why %string% Is Slow.

Understanding SQL Server’s statistics Understanding how statistics interact with indexes and performance is vital to keeping a SQL Server healthy and running smooth. Read more in Seeks, Scans, and Statistics in the Grocery Store. If that’s not enough, you can peruse the paper Statistics Used by the Query Optimizer in Microsoft SQL Server 2008.

Indexed Views can be a great way to improve performance, but you have to know how to create indexed views (there are special rules to follow) as well as techniques to use to get the most out of indexed views.