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.

There are books and even sites dedicated to this sql-server-performance.com — Adriaan Stander
– Adriaan Stander, Commented Jun 10, 2011 at 5:00

marc_s · Accepted Answer · 2011-06-10 05:00:36Z

3

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)

answered Jun 10, 2011 at 5:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ahmet alp balkan Over a year ago

definitely. the keyword is profiler. if you search about it, you can find about them, too and then integrate to your environment and you can keep stats about query types you execute.

Eric Over a year ago

Great. Excellent place to start. Thanks!

Petar Ivanov · Accepted Answer · 2011-06-10 05:07:40Z

1

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.

answered Jun 10, 2011 at 5:07

Petar Ivanov

93.4k11 gold badges84 silver badges95 bronze badges

Collectives™ on Stack Overflow

Measuring the Performance of SQL Queries

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related