good five years I have been blogging away
here and on SSC, the editors recently thankedus for our work. They also provided valuable feedback that we should give real-world situations that DBAs encounter. The following has a target of optimising performance, from an actual task that has re-
occurred several times since I first wrote on the subject, in various production environments, on an instance that is bogged down by that onemassive query within a stored procedure that has to run all the time, yet is so
huge, important and
/or complex everyone isafraid or unsure how to resolve.
of the use of data definition language for your temporary tables and
non-clustered indexes can improve the performance of stored procedures that join data from one or
manylarge tables by up to seventeen times (at least that was the case
previous time I saw this type of query to optimise) - as I have seen on
stored proc.s that work with tables in the tens of millions.
Temporary tables, if
used frequently or in stored procedures, in turn, end up with significant
input/output disk consumption. To start, one thing we should be aware of is that they are also
created as a heap by default. As experience has shown, if you are cutting
up a very large table and using the temporary database, it is best to first do
your DDL (data definition language) before running through the rest of your
operation with the temporary data - as opposed Select * INTO #temp. Thus, we
should avoid Select * into#temp as much as possible, unless the number of rows is insignificant,
because being in a single statement, it will create great disk
contention within the temp database:
(N.B. the assumed
pre-requisite is that you've identified the worst query from your plan cache or
have seen the code from Recent Expensive queries listed in Activity Monitor, sorted by worst performing resource)
#MyLot -- you’ll see that we only need a few columns join in the
end
table with the others and slice it up
-- horizontally and vertically
-- and
that is where we obtain the significant performance gains
-- the glaring ID field
#MyLot into the main super slow query
SELECT [BIResult].[Number], [Loc].[LocId], [BLoc].[BILocId],[BIResult].[LotCode], #MyLot.[LotId],[BIResult].[PCode],[P].[PId],[BIResult].[Stock],ISNULL([BIResult].[StatusCode],[BIResult].[UnitCode]
SMALLINT N'@Number'
NVARCHAR(18) N'@PCode'
NVARCHAR(4) N'@LotCode'
NVARCHAR(10) N'@LotId'
NVARCHAR(3) N'@StatusCode'
NVARCHAR(1) N'@UnitCode'
[BIResult].[SLocCode]
)
temp at the end of the stored proc.
respective index is dropped too with it
 |
| Shed the heavy weight of that extra slow query bogging your instance down. |
