Query Execution Speed

[djwhisky]

Hi there - i'm hoping someone can help me!

I'm having a problem with a live database that i'm running on MSDE - It
seems to have slowed down quite considerably from the test environment
(even when all the data is the same). The is notably different on one
particular query that takes 1 sec on the test machine and almost 1 min
on the live machine

The total number of user connections on the live machine is normally 4
or so (found out through the Performance monitor). So I can't see that
it's MSDE's performance throttler...

Has anybody got any ideas on things i can check for??
Many thanks

James

[djwhisky]

Re: Query Execution Speed

I've done some more investigation ... I create a snapshot of the
database every evening so this is effectively the same data as at
midnight the previous day. If I run the stored procedure on the same
instance of SQL server but the snapshot database it executes in a
couple of seconds. Is there anything that could be slowing down this
one database? I've turned off the autoclose and autoshrink on it - but
the other copies have this set anyway!!

[djwhisky]

Re: Query Execution Speed

I've done some more investigation ... I create a snapshot of the
database every evening so this is effectively the same data as at
midnight the previous day. If I run the stored procedure on the same
instance of SQL server but the snapshot database it executes in a
couple of seconds. Is there anything that could be slowing down this
one database? I've turned off the autoclose and autoshrink on it - but
the other copies have this set anyway!!

Help gratefully accepted!!

[sqlmatt]

Re: Query Execution Speed

Do the execution plans look different on the two servers? We had
similar problems and it turned out that the one plan used parallelism
and the other didn't.

[djwhisky]

Re: Query Execution Speed

It seems that the execution plans are different!!! I'm a bit new at
this kind of configuration with SQL Server... what do I need to change
to make the execution plans the same on both machines?

[sqlmatt@gmail.com]

Re: Query Execution Speed

This is not unlike how two people perform the same task and get the
same result, but the process of performing it differs. In this case, I
assume the servers are not exactly the same, the physical distribution
of data on disk could be different, the load on the server could be
different, etc.

In general terms, I would try the following:

1. Update statistics on both servers, then compare execution plans
again.
2. Add query hints on the slower plan to get the desired result if the
above does not change things.

How are the plans different?

[djwhisky]

Re: Query Execution Speed

Sorry, I didnt' explain myself properly and I guess used the wrong
terminology somewhere along the line!!

Every evening, a copy of the database is made to another database on
the same instance of SQL Server. When I execute the SP on this
'snapshot' database it only takes a few seconds, but when I execute the
original it takes up to a minute or so.

So - the database is on the same SQL Server and has exactly (albeit to
a few hours) the same data as the live database, yet the execution
plans are different and the speed is dramatically different!!

[djwhisky]

Re: Query Execution Speed

Here are the two execution plans (I hope this is the right format that
you can understand...)

Query running on live data (slow):

|--Sort(ORDER BY:([Expr1016] ASC, [Expr1017] ASC))
|--Compute
Scalar(DEFINE:([Expr1014]=[StockLevel].[CurrentStockQua ntity]-[StockLevel].[CommittedStockQ uantity],
[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQua ntity]-[StockLevel].[CommittedStockQ uantity]))/[Expr
|--Filter(WHERE:(I f ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQua ntity]-[StockLevel].[CommittedStockQ uantity]))/[Expr1013])<=If
([@MinDaysCover]<>NULL) then Convert([@MinDaysCover]) else If
([Expr1013]=0) then 0 else (
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([StockLevel].[StockLine_ID]))
|--Bookmark Lookup(BOOKMARK :([Bmk1011]),
OBJECT:([foodcontrolSQL].[dbo].[Supplier]))
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES:([Product].[Supplier_ID]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([StockLine].[StockLine_ID]) WITH PREFETCH)
| |
|--Filter(WHERE:([StockLine].[StockLineStatus _ID]=1 AND
Convert([StockLine].[IsFutureDelist])=If (If
(Convert([@ExcludeFutureD elist])=1) then 0 else NULL<>NULL) then If
(Convert([@ExcludeFutureD elist])=1) then 0 el
| | | |--Bookmark
Lookup(BOOKMARK :([Bmk1007]),
OBJECT:([foodcontrolSQL].[dbo].[StockLine]))
| | | |--Nested
Loops(Inner Join, OUTER REFERENCES:([Product].[Product_ID]) WITH
PREFETCH)
| | | |--Table
Scan(OBJECT:([foodcontrolSQL].[dbo].[Product]),
WHERE:([Product].[ProductStatus_I D]=2 AND [Product].[Supplier_ID]=If
(If (Convert([@SupplierFilter])=1) then [@Supplier_ID] else NULL<>NULL)
t
| | | |--Index
Seek(OBJECT:([foodcontrolSQL].[dbo].[StockLine].[Product_ID]),
SEEK:([StockLine].[Product_ID]=[Product].[Product_ID]) ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([foodcontrolSQL].[dbo].[StockLevel].[PK__StockLevelB ackup__5E74FADA]),
SEEK:([StockLevel].[StockLocation_I D]=1 AND
[StockLevel].[StockLine_ID]=[StockLine].[StockLine_ID]), WHERE:([S
| |--Index
Seek(OBJECT:([foodcontrolSQL].[dbo].[Supplier].[Supplier_ID]),
SEEK:([Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDERED
FORWARD)
|--Compute
Scalar(DEFINE:([Expr1013]=Convert([Expr1004])/Convert([@NumDays])))
|--Compute Scalar(DEFINE:([Expr1004]=If
([Expr1032]=0) then NULL else [Expr1033]))
|--Stream
Aggregate(DEFIN E:([Expr1032]=COUNT_BIG([StockTransactio n].[StockAdjustment Quantity]),
[Expr1033]=SUM([StockTransactio n].[StockAdjustment Quantity])))

|--Filter(WHERE:(( ([StockTransactio n].[StockLine_ID]=[StockLevel].[StockLine_ID]
AND [StockTransactio n].[ExcludeROS]=If ([@ExcludeROS]<>NULL) then
[@ExcludeROS] else [StockTransactio n].[ExcludeROS]) AND [StockTrans
|--Bookmark
Lookup(BOOKMARK :([Bmk1000]),
OBJECT:([foodcontrolSQL].[dbo].[StockTransactio n]))
|--Index
Seek(OBJECT:([foodcontrolSQL].[dbo].[StockTransactio n].[StockTransactio n11]),
SEEK:([StockTransactio n].[TransactionDate] >= [@StartDate] AND
[StockTransactio n].[TransactionDate] <= [@EndDate]) OR



Query running on snapshot data (fast):

|--Sort(ORDER BY:([Expr1016] ASC, [Expr1017] ASC))
|--Compute
Scalar(DEFINE:([Expr1014]=[StockLevel].[CurrentStockQua ntity]-[StockLevel].[CommittedStockQ uantity],
[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQua ntity]-[StockLevel].[CommittedStockQ uantity]))/[Expr
|--Bookmark Lookup(BOOKMARK :([Bmk1011]),
OBJECT:([foodcontrolSnap shot].[dbo].[Supplier]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([Product].[Supplier_ID]))
|--Hash Match(Inner Join,
HASH:([StockLine].[Product_ID])=([Product].[Product_ID]),
RESIDUAL:([Product].[Product_ID]=[StockLine].[Product_ID]))
| |--Merge Join(Inner Join,
MERGE:([StockLine].[StockLine_ID])=([StockLevel].[StockLine_ID]),
RESIDUAL:([StockLevel].[StockLine_ID]=[StockLine].[StockLine_ID]))
| | |--Sort(ORDER
BY:([StockLine].[StockLine_ID] ASC))
| | | |--Table
Scan(OBJECT:([foodcontrolSnap shot].[dbo].[StockLine]),
WHERE:([StockLine].[StockLineStatus _ID]=1 AND
Convert([StockLine].[IsFutureDelist])=If (If
(Convert([@ExcludeFutureD elist])=1) then 0 else NULL<>NULL)
| | |--Filter(WHERE:(I f ([Expr1013]=0)
then 0 else
(Convert(([StockLevel].[CurrentStockQua ntity]-[StockLevel].[CommittedStockQ uantity]))/[Expr1013])<=If
([@MinDaysCover]<>NULL) then Convert([@MinDaysCover]) else If ([Expr10
| | |--Merge Join(Left Outer
Join,
MERGE:([StockLevel].[StockLine_ID])=([StockTransactio n].[StockLine_ID]),
RESIDUAL:([StockTransactio n].[StockLine_ID]=[StockLevel].[StockLine_ID]))
| | |--Clustered Index
Seek(OBJECT:([foodcontrolSnap shot].[dbo].[StockLevel].[PK__StockLevel_ _6DA725A5]),
SEEK:([StockLevel].[StockLocation_I D]=1),
WHERE:([StockLevel].[CurrentStockQua ntity]-[StockLevel].[Committ
| | |--Compute
Scalar(DEFINE:([Expr1013]=Convert([Expr1004])/Convert([@NumDays])))
| | |--Compute
Scalar(DEFINE:([Expr1004]=If ([Expr1035]=0) then NULL else [Expr1036]))
| | |--Stream
Aggregate(GROUP BY:([StockTransactio n].[StockLine_ID])
DEFINE:([Expr1035]=COUNT_BIG([StockTransactio n].[StockAdjustment Quantity]),
[Expr1036]=SUM([StockTransactio n].[StockAdjustment Quantity
| |
|--Sort(ORDER BY:([StockTransactio n].[StockLine_ID] ASC))
| |
|--Clustered Index
Scan(OBJECT:([foodcontrolSnap shot].[dbo].[StockTransactio n].[PK_StockTransac tion]),
WHERE:(((([StockTransactio n].[StockLocation_I D]=1 AND
[StockTransactio n].[StockTransac
| |--Table
Scan(OBJECT:([foodcontrolSnap shot].[dbo].[Product]),
WHERE:([Product].[ProductStatus_I D]=2 AND [Product].[Supplier_ID]=If
(If (Convert([@SupplierFilter])=1) then [@Supplier_ID] else NULL<>NULL)
then If (Convert([@Su
|--Index
Seek(OBJECT:([foodcontrolSnap shot].[dbo].[Supplier].[aaaaaSupplier_P K]),
SEEK:([Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDERED
FORWARD)

[djwhisky]

Re: Query Execution Speed

Just in case anybody is reading this topic and wants to know how i
solved the problem, I've run the command

UPDATE STATISTICS tablename

for each table that was dependent on the query and it's made the
queries run nice and fast again!!