Bob Blackburn
2015-08-06
If you have never done performance tuning, it can be a
little intimidating. There are whole books on the subject. For those of you who
are new to performance tuning, we will look at a quick way to get you started.
You are working on a new report or have been asked to look
into an existing report that is taking a long time. We have all seen where one
little change can cause a report to go from a run time of seconds to minutes
and even hours.
For our example, we will have a report query I was
given. It has some normal union, sub
select, and where column in sub select logic.
Here is the Estimated Execution Plan. Details for this are
not mandatory. We can see multiple paths; but, it is not pages and pages of
detail.
The query was taking over an hour on the development server
when I cancelled it. To get the Estimated Execution Plan in SSMS, use this Menu
option:
Here is where we get the help we need from SQL Server. Above
the plan will be some messages. If it recommends a missing index, it will tell
you. Here is the clip:
We can see the missing index has an estimated impact of 27%
of processing time. But wait, there’s more. If you right click the message,
SSMS will script the index create statement for you (almost).
You have to uncomment the create statement and change index
name to a valid meaningful name.
Original: CREATE
NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
…
Formatted:
CREATE NONCLUSTERED
INDEX [IX_table_name_column_name]
…
Execute the Create statement.
Here is the new plan. Don’t worry about
finding the differences. Especially since you can’t read the detail of each
box. The important thing is we did not receive any warning messages about
missing indexes.
Now, instead of over an hour, the query completes in under 1
minute. That is much better savings then
the estimated 27%.
As quick and convenient as this is, it is not the answer to
all your performance problems. You should not just create indexes for a report
every time it is recommended. But, it will show you how your data is being
used. Long term, it may be better for your system as a whole to rewrite the
query. If you don’t have authority to make changes in production, you at least
have more information to discuss possible changes with your team.
Labels: Performance, SQL Server
