SQL optimization is a transformation of SQL statements, which
produces exactly the same result, as an original query. This process
requires a lot of creativity and intuition; it is more an Art than a Science.
It is impossible to teach it in a small article, so I don't even try to
do it. Instead, I want to limit myself by classifying types of
optimization. So, when you are asked to optimize a database, you need
to know, what to ask and what to expect.
The result, produced by the optimized code must be exactly the same
as it was before: the second worst nightmare of a DBA (the first one is
losing data) is someone running to him crying 'you know, after you last
optimization all customer balances became negative!' So I will focus on
Potential danger on every level.
For that reason all types of optimization are sorted in order of
increasing intrusiveness, and I begin from the non-intrusive
0. Non-intrusive optimization
It is upgrading SQL server hardware and changing of SQL server
parameters. Adding more memory or processor power wont harm, you don't
need to test your product again.
Upgrading hardware you can spend a lot without any effect, because
it is possible that your server is suffering from Locks, while you will
be adding more memory - for vain. There is an easy way to determine,
what (in general) is the most serious problem on the server: CPU power,
IO, or Locks. To do it, use Lakeside Upgrade Advisor.
You can accelerate your IO by putting LOG and DATA on different
devices, separating indexes from data, but unfortunately, it is more
and more difficult to apply these old good recommendations in the
modern world. In many companies 'a server' is built based on the same
specification no matter if it is a file server or a database server.
One big RAID-5 drive is almost standard for the small servers. If you
ask them for separate drives for LOG and DATA, they will give you 2
separate LUNs on the same RAID-5 drive and on the same physical
channel, which is absolutely useless.
For the bigger servers more and more companies use big disk arrays
like SAN EMC - these arrays are very good for the OLAP systems, but are
bad for the OLTP.
Changing SQL server parameters also relatively safe, because you
don't change the code. Unfortunately, there is no parameter
'TURBO=TRUE', so you can play with these parameters, but in most cases
SQL server self-tuning is quite good, and effects of tuning can be
For the given hardware, applying all the recommendations you can
find in the Internet, you can gain 20-30%, even less if there is
nothing really wrong with you server. So, if your queries are running
several times slower then expected - then you don't have other choice
but change your TSQL code.
Potential danger: None
On this level of optimization, we change the database by creating and changing indexes, but we don't touch the code.
Of course, any DBA is very conservative about changing the code,
especially on the production. In some cases it is possible to make some
modifications which are not really considered as 'changes' by the
At first, all you can do with indexes, can affect the execution
plan, but the result is guaranteed to be the same (unless there are
bugs in the SQL server, but Microsoft spent much more time on testing
it then it is spent on any query you work with, so below we can think
about SQL server as a 'bug-free' product, which is not true, but is
almost true in comparison with the quality of our code)
You can create, drop indexes, change index types
(clustered/non-clustered), include or exclude columns from indexes. On
SQL 2005, check a new index option 'INCLUDING'. Don't try to index
everything - you will slow down updates. If SQL server does not use an
index, there may be a very good reason behind that decision.
Potential danger: Is it absolutely safe?
For most of the databases, it is safe. SQL server will automatically
adjust plans based on new indexes. However, if your database uses not
only DML but also a DDL, and it is generating tables and indexes on the
fly, then it is possible that some
exec('drop index '+@indname)
would fail. So be careful.
2. Hints and options
To use a hint you should modify an existing SQL code. Hence, it is a
modification, and theoretically, you need to retest all the system. But
with an assumption that SQL server is almost bug-free, a query with any
hint produces exactly the same result as without a hint.
So check the execution plan and check the following:
(index=...) - but be careful, usually, when SQL server ignores an index, there is a very good reason why.
OPTION (HASH JOIN) - try also other types of joins.
OPTION (FORCE ORDER)
OPTION (FAST 1) - these 3 options above are good candidates to try even without thinking, sometimes it helps.
(NOEXPAND) for indexed views
WITH RECOMPILE for a stored procedure
and some others...
But if it does not help, there is no other choice then change the code
- Some modifications can have a catastrophic impact on the performance.
- Some options can result in error type
'Query processor could not produce a query plan because of the hints defined in this query.'
- Some queries can fail on some data sets, check
OPTION (ROBUST PLAN)
3. Syntax-level optimization
The most primitive type of the optimization is based only on the
text of a query. We don't know even the underlying database structure,
so we are limited by the subset of syntax transformations, which do not
change the result in any case. For example:
select 'Low',A,B from T
select 'High',A1,B1 from T1
In this case we merge both datasets together and get rid of the
duplicates. However, there are no duplicates because the first column
is different for 2 sub-queries. Hence we can rewrite it as:
select 'Low',A,B from T
select 'High',A1,B1 from T1
update T set Flag='Y'
update T set Ref=0
In both queries we affect all the records, so we can change both columns at the same time:
update T set Flag='Y',Ref=0
We can merge together updates in even more complicated cases:
update T set Flag='Y'
update T set Ref=0 where Comment like '%copied%'
Both updates use a full table scan, so lets do both updates during the same pass:
update T set Flag='Y',
Ref = case when Comment like '%copied%'
else Ref -- keep the same
Such type of optimization can be dangerous. For example,
update T set Ref=0
update T set Amount=StoredValue
It is almost the same case as we had before. So we can rewrite it as
update T set Ref=0,Amount=StoredValue
Right? Yes, this transformation is correct... in 99% of cases, but not
always. The possible problem is that StoredValue can be a calculated
column, which depends on Ref. For example, lets say it is defined as
The original statement sets Ref to 0, and Amount to 1. Our optimized
query works differently, as UPDATE at first calculates all right parts
and only then assigns them to the columns. Therefore, it will set Ref
to 0, but Amount will be set to the old value of Ref plus 1.
Potential danger: As it was demonstrated
above, we should test the code after that modification. All levels
above are intrusive and require verification.
4. When we know database schema
When we know database scheme and we have a list of indexes we can make more serious changes. For example, when we see:
update T set Flag='A' where Opt=1
update T set Flag='B' where Opt=2
We can check if there is an index on the column Opt. Assuming, that
an index is created on a column with a high selectivity, we can leave
there statements as is. However, if there is no index on Opt, we can
rewrite it as:
set Flag=case when Opt=1 then 'A' else 'B' end
where Opt in (1,2)
to make an update during the same scan.
Potential danger: Testing is required
5. When we have an access to the actual data and know the meaning of the data
We can do it only when we have an access to the actual data. In the
example above, we could determine if Opt is a column with a high
selectivity. To do it we should compare the results of 2 queries:
select count(*) from T
select count(distinct Opt) from T
What is more important, we can find values with irregular
selectivity and handle them properly. Check this article to read more
about irregular selectivity: http://www.sqlsolutions.com/articles/articles/How_Values_with_Irregular_Selectivity_Impact_SQL_Server_Database_Performance.htm
But sometimes table and column names help, so we understand the
situation even without looking at the actual data. In what case, do you
think, SQL server uses an index and where it uses a full table scan?
update Patients set Flag='Y' where Gender='F'
update Sales set Flag='N' where Id=13848
In the first case SQL server uses a table scan, because Gender
column (M/F) has extremely low selectivity (about 50%). We can even
say, that Flag is a low selectivity value in both cases with values
'Y','N' and probably few others. But syntaxically, both queries are
Another example. Assuming that Gender is NOT NULL, does this statement update all the records?
update Patients set Flag=0 where Gender in ('M','F')
Yes. Now let's change the table name:
update Nouns set Flag=0 where Gender in ('M','F')
We update a noun, which have a grammar category of Gender, which may
be 'M', 'F' or Neutral! As you see, the only difference is a table
name, so we actually guessed the meaning.
We can base our optimization based on many real world constraints, like
- age is less then 255 and non-negative,
- mass is positive,
- InternalId int identity correlates with a date of the document
- SSN is unique.
Potential danger: Requires not only testing
but also verification, that constraints are not violated now (such type
of verification is possible by querying the existing data) and in the
future (not all data may be inserted). You can find that:
- Special value of -1 is used for age as some weird flag
- mass can be negative because sometimes it is delta from the actual and expected value
- InternalId int identity correlates with a date of the document except
for the documents, imported from another database a year ago
- SSN is unique, except for the value 'N/A', used for all illegal immigrant workers.
5. Actual or expected workload.
Some solutions have 2 sides: they accelerate data retrieval, for
example, but slow down the modification. Very good example is a
technique of 'indexed' or 'materialized'
views. Indexed views can
accelerate selects, but they slow down any updates significantly,
especially massive updates.
So before you make any modifications, you should know if data is
relatively static or not, how man by updates you expect per one select
On the actual data load the best way to make such analysis is to use SQL Trace Analyzer.
You need to ask right questions what you are asked to 'look, what is
wrong with this query' or 'optimize a stored procedure'. Before you
start an optimization, you need to know, what level of intrusiveness is
allowed and what information do you have as input.