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
8  2055 
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!!
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].[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)
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!!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Muharram Mansoorizadeh |
last post by:
Hi there,
I've a table with 18 millions of recordes shaped like this :
Code nvarchar(80) , State int , school int , class int , Term nvarchar(80)
The following query takes too long to run ( more than 2 hours )
select State , school , class , term , count (term) as freq
Group by state , school , class , term
How may I speed up the query?
My Pc is PIV (3.6 GHz) Intell , Win2003 Server , 512 MB of RAM, 80 GB of HD
Regards,
|
by: robboll |
last post by:
Whenever I query my database using Enterprise Manager, the response
time is about a second. When I access the same data via a Cold Fusion
webpage, it takes about 15 seconds (or more) to resolve. I created an
index for the fields involved in the query and updated statistics. It
makes no difference. Is there anything else I should look at? The
table is only about 3000 records. Any suggestions to speed up queries
when using Cold Fusion?...
|
by: Jean |
last post by:
Hello,
I have the following query that I set up as a test, and it runs fine:
SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID =
PROBLEM_DE.PROBLEMNR
WHERE (((STATUSHISTORIE.STATUSDATUM)<#1/1/2005#) AND
((PROBLEM_DE.DATENBEREICH)='SPMO') AND
(((Left((.),InStr(.,"-")-2)))='K29')
|
by: serge |
last post by:
How do I determine which method I should use if
I want to optimize the performance of a database.
I took Northwind's database to run my example.
My query is I want to retrieve the Employees' First
and Last Names that sold between $100,000 and
$200,000.
First let me create a function that takes the EmployeeID
|
by: darnnews |
last post by:
Hi,
I have been creating a database to keep track of press clippings, but I
have hit a couple stumbling blocks. Any help is much appreciate.
1) Seeing if my query is done
I have the following code to define a query. I run the query and then
get a record deleted errors (3167) when I go to export to a
spreadsheet. It works if I put in a delay between the query and the
| | |
by: Paul H |
last post by:
I seem to end up with loads of append and update queries just because it's
quick and easy to build queries or make a new ones based on an existing
query. But I end up with loads of queries with long names like:
qryfrmCustomers_Diary_DeleteEntriesBeforeADate
qryfrmMaintenance_CloneOrder_Step1_CreateOrderIDAndDescription
Am I being lazy? Should I code most of my action queries to tidy up the
clutter in the database window?
|
by: - |
last post by:
I have only 1m records in my database running on a laptop of speed
1.6GHz, memory 512MB ram, and Toshiba MK8032GAX hard disk.
I use 'LIMIT x,10' for the query to utilise record paging.
When the value of x is nearer to 0, the query speed is fast.
Presently, 'LIMIT 0,10', 'LIMIT 50000,10' and 'LIMIT 100000,10' takes
about 0sec, 11secs and 4mins respectively.
1) Is there anything I should do, in terms of sql statement or database
design,...
|
by: wizofaus |
last post by:
I have a chunk of code which is essentially
IDbCommand cmd = db.CreateCommand();
cmd.CommandText = "SELECT X, Y, Count(*) FROM Foo WHERE Z = 1 GROUP BY
X, Y";
using (IDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
// grab values from query
}
|
by: comp_databases_ms-sqlserver |
last post by:
This post is related to SQL server 2000 and SQL Server 2005 all
editions.
Many of my stored procedures create temporary tables in the code. I
want to find a way to find the query plan for these procs
Repro
--***********************************
use pubs
go
CREATE PROCEDURE Test @percentage int
|
by: Don Li |
last post by:
Hi,
Env: MS SQL Server 2000
DB Info (sorry no DDL nor sample data):
tblA has 147249 rows -- clustered index on pk (one key of
datatype(int)) and
has two clumns, both are being used in joins;
intersecTbl4AB has 207016 rows -- clustered index on two fks and
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
