Measuring the Performance of SQL Queries

Question

Let me say ahead of time, that I have very little understanding of the algorithms that SQL queries go through, so please excuse my ignorance.

My question is: How do you go about evaluating the performance of a particular SQL query? And what metrics are considered?

For example,

SELECT * FROM MyTable;

and

SELECT * FROM MyTable UNION SELECT * From MyTable;

I'm guessing the second one is a lot slower even though both queries return the same results. But, how could someone evaluate the two and decide which one is better and why?

Are there tools to do this? Is there any type of SQL stack trace? Etc...

Thanks.

Answer 1

Assuming you're talking about SQL Server (you didn't specify...):

You need to look into SQL Server Profiler - and the best intro around is a six-part webcast series called

Mastering SQL Server Profiler

in which Brad MacGehee walks you through how to start using Profiler and what to get from it.

Red-Gate Software also publishes a free e-book on Mastering SQL Server Profiler (also by Brad)

Answer 2

Also assuming you are talking about SQL Server, if you are using SQL Server Management Studio, then you can try 'Display Estimatesd Execution Plan' and/or 'Include Actual Execution Plan' (from the Query menu).

The difference is that the first one doesn't execute the query, while the second does. So the second is more accurate, but the first is useful if you only want to execute the 'lighter' query.

Both of them display the execution tree. You can hover over each node and see statistics. The one to use to compare is 'Estimate Subtree Cost' (the higher the worse).

Hope this was helpful.