8

In the never-ending search for performance (and my own bludgeoning experience), I've learnt a few things that could drag down the performance of a SQL statement.

Obsessive Compulsive Subqueries Disorder
Doing crazy type conversions (and nest those into oblivion)
Group By on aggregate functions of said crazy type conversions
Where fldID in (select EVERYTHING from my 5mil record table)

I typically work with MSSQL. What tools are available to test the performance of a SQL statement? Are these tools built in and specific to each type of DB server? Or are there general tools available?

Shmiddty
  • 13,847
  • 1
  • 35
  • 52
MoSlo
  • 2,780
  • 4
  • 33
  • 37
  • You're bludgeoning things for the experience? Or is your experience *burgeoning*? +1 for Mitch's aswer. – DaveE Oct 12 '09 at 17:15

6 Answers6

10

SQL Profiler (built-in): Monitoring with SQL Profiler

SQL Benchmark Pro (Commercial)

SQL Server 2008 has the new Data Collector

SQL Server 2005 (onwards) has a missing indexes Dynamic Management View (DMV) which can be quite useful (but only for query plans currently in the plan cache): About the Missing Indexes Feature.

There is also the SQL Server Database Engine Tuning Advisor which does a reasonable job (just don't implement everything it suggests!)

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Thanks, that pretty much gets me started. This covers the MSSQL angle, are there general tools available for MySQL or Oracle? Or would those have their own tools? – MoSlo Oct 12 '09 at 19:55
3

I mostly just use Profiler and the execution plan viewer

Galwegian
  • 41,475
  • 16
  • 112
  • 158
1

Execution Plans are one of the first things to look at when debugging query performance problems. An execution plan will tell you how much time is roughly spent in each portion of your query, and can be used to quickly identify if you are missing indexes or have expensive joins or loops.

tbreffni
  • 5,082
  • 5
  • 31
  • 30
0

MSSQL has a database tuning advisor that will often recommend indexes for tables based upon common queries run during the tuning period, however it wo't rewrite a query for you.

In my opinion, experience and experimentation are the best tools for writing good SQL queries.

cjk
  • 45,739
  • 9
  • 81
  • 112
0

In mysql (may be in other databases too) you can EXPLAIN your query to see what database server thinks about it. This usually used to deside which indexes should be created. And this one is build-in, so you can use it without installing additional software.

Ivan Nevostruev
  • 28,143
  • 8
  • 66
  • 82
0

Adam Machanic has a simple tool called SqlQueryStress that might be of use. It is designed to be used to "run a quick performance test against a single query, in order to test ideas or validate changes".

adrianbanks
  • 81,306
  • 22
  • 176
  • 206
  • Adam's tool is really great. I've used it and found it very helpful for tuning my db. –  Jul 24 '11 at 06:38