CHAPTER 28 ■ PERFORMANCE TUNING
Use ORDER BY only if you need ordered results. Sorting operations of larger result sets can incur additional overhead. If it isn’t necessary for your query, remove it.
Beware of testing in a vacuum. When developing your database on a test SQL Server instance, it is very important that you populate the tables with a representative data set. This means that you should populate the table with the estimated number of rows you would actually see in production, as well as a representative set of values. Don’t use dummy data in your development database and then expect the query to execute with similar performance in production. SQL Server performance is highly dependent on indexes and statistics, and SQL Server will make decisions based on the actual values contained within a table. If your test data isn’t representative of “real life” data, you’ll be in for a surprise when queries in produc- tion don’t perform as you saw them perform on the test database.
I pushed this point hard in Chapter 10, and I think it is worth repeating here. Stored procedures can often yield excellent performance gains over regular ad hoc query calls. Stored proce- dures also promote query execution stability (reusing existing query execution plans). If you have a query that executes with unpredictable durations, consider encapsulating the query in a stored procedure.
When reading about SQL Server performance tuning (like you are now), be careful about the words “never” and “always.” Instead, get comfortable with the answer “it depends.” When it comes to query-tuning, results may vary. There are certainly many good and bad practices to be aware of, but performance tuning is both art and science. Keep your options open and feel free to experiment (in a test environment, of course). Ask questions and don’t accept conventional wisdom at face value.
Capturing and Evaluating Query Performance
In this next set of recipes, I’ll demonstrate how to capture and evaluate query performance. This book has focused almost entirely on SQL Server 2005’s flavor of Transact-SQL, and hasn’t touched on the various graphical interface tools much. I’ll be breaking with this tradition in this chapter to briefly show you how to use SQL Profiler to capture query activity. Also, I’ll demonstrate how to graphically display the actual or estimated execution plan of a query so that you can understand the decisions that SQL Server is making in order to generate the query result set. I’ll also demon- strate several other Transact-SQL commands, which can be used to return detailed information about the query execution plan.
Capturing High Duration Queries Using SQL Server Profiler
There are usually two branches of query performance tuning: proactive and reactive. Proactive query tuning usually occurs during development. You design the database, populate it with data, and then start building queries. You build the queries based on application and end-user require- ments. For those queries that don’t perform well, you can tune them before deploying out in the stored procedure or to the application developer.
Reactive performance involves capturing poor performance after the code has already been deployed to production. Data changes over time, and so does the effectiveness of indexes and the queries that use them. Queries that once performed well may execute longer than they did origi- nally. You may hear complaints from end-users, or you might actually seek out poorly performing queries yourself.
One of the most valuable graphical interface tools in the SQL Server 2005 toolset is SQL Server Profiler. With SQL Server Profiler, you can monitor query activity as it occurs against your SQL