Hello,
i am having a bizarre problem with the execution of a stored procedure on SQL server. up to now, the execution time was taking maximum ten minutes and then suddenly since a few days ago, it takes more than 40 minutes, without exception!!
i have tried to diagnose this problem with some tools that are provided out there such as executing a stored procedure called "proc track_waitstats" from which i learned that the “max degree of parallelism”. needed to be set (i set it to 3, it used to be zero. there are 4 processors). after rerunning the diagnosis, my SQL server seemed very happy with wait times. zero wait time for almost everything, but my stored procedure is still running as slow.
what is going on? how come it was running so much faster before and now it just doesn't?! (the data volume to process has not changed. i am using a test database with static data. so there is no increase in the load of data to be processed by the stored procedure)
More information: my stored procedure involves two complex SELECTs. The first select involves a few JOINS (inner and left) and takes about 2 seconds to execute. It returns about 6000 rows. The second select also involves a few JOINS (inner and left) and is executed for each row of the 6000 returned by the first select. It returns about 16,000 rows and it takes 40 minutes! (it used to take 10 minutes)
i haven't updated the server with any service packs and haven't changed the stored procedure either.
any help would be greatly appreciated!Hi Yassi,
Do you have a lot of applications talking to the server?
It might sound daft but to me it sounds like there might be multiple connections to the server slowing it down. What I mean by this is in your code check that the app is closing its connections once you are finished doing what you are doing. Failure to do so, over time, opens a very large amount of connections to the server hence slowing it down.
Barry Andrew|||Hello,
I ran an sql query analizer and this is what i received
Value Average
Application Profile Statistics
Timer resolution (milliseconds) 0 0
Number of INSERT, UPDATE, DELETE statements 0 0
Rows effected by INSERT, UPDATE, DELETE statements 0 0
Number of SELECT statements 2 2
Rows effected by SELECT statements 4 4
Number of user transactions 378 378
Average fetch time 0 0
Cumulative fetch time 0 0
Number of fetches 0 0
Number of open statement handles 0 0
Max number of opened statement handles 0 0
Cumulative number of statement handles 0 0
Network Statistics
Number of server roundtrips 3 3
Number of TDS packets sent 3 3
Number of TDS packets received 52 52
Number of bytes sent 238 238
Number of bytes received 202720 202720
Time Statistics
Cumulative client processing time 458 458
Cumulative wait time on server replies 75904 75904|||Maybe your data is growing and you can have a slowdown due to index fragmentation or poor designed index... is possible that when you have a few data sql server optimizer selects a plan that is different from the one selected when data grows.
So I would
- Watch the query execution plan and try to avoid clusterded index scans or table scans. Be sure that all columns involved in the joins has his own index... both primary key and foreign keys.
- Check index fragmentation and issue a defrag. refer to this MS white paper:
http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc|||i was able to solve my problem by restructuring my SELECT statements.
basically i reduced the number of inner joins in one SELECT, added a few temporary tables and especially reduced the number of left joins integrated with inner joins.
my query has suddenly become a million times faster.
so it was the structure of my SELECT statements with many inner joins and left joins in one statement that was causing the problem.
No comments:
Post a Comment