Gettin’ Techie SET STATISTICS TIME

Today we’re going to be getting down and dirty with some good, old-fashioned techno-babble. I’m going to be discussing the finer points of database performance to give you a better understanding of what your server is doing, and how it’s doing it. Let me just say upfront that this isn’t about quick fixes. In fact, it’s not about fixing problems at all. This is about arming you with the tools to see what’s happening inside your server.

Why is it so slow?

A common question that DBA’s get revolves around speed of servers. Everyone wants their business-critical applications to be running at top speed, why wouldn’t you? When you’re dealing with huge numbers of transactions, then shaving minute amounts of time off each operation can be a huge boost to performance. But how do we really know what that speed is?

That’s where SET STATISTICS TIME comes into play. Turning this on will return the amount of time taken to execute the query, broken into two key metrics: Parse and compile time, and execution time. Both parameters give a CPU time and an elapsed time, with the key being CPU time. Elapsed time takes into account server queuing and will vary from execution to execution; CPU time is a direct expression of the load on the server from this query.

Build it

Parse and compile time is how long it takes for the server to check and build the query that it has been passed. The larger the query, the longer it will take to build, so keeping unnecessary options out of queries is important from an input perspective. Once a query has run for the first time, parse and compile time should drop to zero for consecutive runs as the query becomes cached in the server’s memory. If this is consistently high, then you might be wasting resources through enabling the recompile option.

Run it

Execution time is, as the name suggests, the amount of time it takes the server to run the query. The CPU time displayed for this shouldn’t vary greatly from execution to execution, and is a great baseline for testing different queries and their resultant server loads. Elapsed time will incorporate other factors such as server load, bandwidth between server and client, database IO load. This can vary greatly and should be disregarded when baselining.

Armed with these two timings, you can begin to assess your server’s query performance. The best use of this information is in comparing different queries to see which is less taxing on the server. After all, less load each on the server per query equals more queries equals better performance.