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
JamesI'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!!|||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!!|||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.|||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?|||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?|||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!!|||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].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity],
[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr
|--Filter(WHERE:(If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[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([@.ExcludeFutureDelist])=1) then 0 else NULL<>NULL) then If
(Convert([@.ExcludeFutureDelist])=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_ID]=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__StockLevelBackup__5E74FADA]),
SEEK:([StockLevel].[StockLocation_ID]=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(DEFINE:([Expr1032]=COUNT_BIG([StockTransaction].[StockAdjustmentQuantity]),
[Expr1033]=SUM([StockTransaction].[StockAdjustmentQuantity])))
|--Filter(WHERE:((([StockTransaction].[StockLine_ID]=[StockLevel].[StockLine_ID]
AND [StockTransaction].[ExcludeROS]=If ([@.ExcludeROS]<>NULL) then
[@.ExcludeROS] else [StockTransaction].[ExcludeROS]) AND [StockTrans
|--Bookmark
Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([foodcontrolSQL].[dbo].[StockTransaction]))
|--Index
Seek(OBJECT:([foodcontrolSQL].[dbo].[StockTransaction].[StockTransaction11]),
SEEK:([StockTransaction].[TransactionDate] >= [@.StartDate] AND
[StockTransaction].[TransactionDate] <= [@.EndDate]) OR
Query running on snapshot data (fast):
|--Sort(ORDER BY:([Expr1016] ASC, [Expr1017] ASC))
|--Compute
Scalar(DEFINE:([Expr1014]=[StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity],
[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr
|--Bookmark Lookup(BOOKMARK:([Bmk1011]),
OBJECT:([foodcontrolSnapshot].[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:([foodcontrolSnapshot].[dbo].[StockLine]),
WHERE:([StockLine].[StockLineStatus_ID]=1 AND
Convert([StockLine].[IsFutureDelist])=If (If
(Convert([@.ExcludeFutureDelist])=1) then 0 else NULL<>NULL)
| | |--Filter(WHERE:(If ([Expr1013]=0)
then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr1013])<=If
([@.MinDaysCover]<>NULL) then Convert([@.MinDaysCover]) else If ([Expr10
| | |--Merge Join(Left Outer
Join,
MERGE:([StockLevel].[StockLine_ID])=([StockTransaction].[StockLine_ID]),
RESIDUAL:([StockTransaction].[StockLine_ID]=[StockLevel].[StockLine_ID]))
| | |--Clustered Index
Seek(OBJECT:([foodcontrolSnapshot].[dbo].[StockLevel].[PK__StockLevel__6DA725A5]),
SEEK:([StockLevel].[StockLocation_ID]=1),
WHERE:([StockLevel].[CurrentStockQuantity]-[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:([StockTransaction].[StockLine_ID])
DEFINE:([Expr1035]=COUNT_BIG([StockTransaction].[StockAdjustmentQuantity]),
[Expr1036]=SUM([StockTransaction].[StockAdjustmentQuantity
| |
|--Sort(ORDER BY:([StockTransaction].[StockLine_ID] ASC))
| |
|--Clustered Index
Scan(OBJECT:([foodcontrolSnapshot].[dbo].[StockTransaction].[PK_StockTransaction]),
WHERE:(((([StockTransaction].[StockLocation_ID]=1 AND
[StockTransaction].[StockTransac
| |--Table
Scan(OBJECT:([foodcontrolSnapshot].[dbo].[Product]),
WHERE:([Product].[ProductStatus_ID]=2 AND [Product].[Supplier_ID]=If
(If (Convert([@.SupplierFilter])=1) then [@.Supplier_ID] else NULL<>NULL)
then If (Convert([@.Su
|--Index
Seek(OBJECT:([foodcontrolSnapshot].[dbo].[Supplier].[aaaaaSupplier_PK]),
SEEK:([Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDERED
FORWARD)|||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!!
No comments:
Post a Comment