Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL procedure Query Performance issue


SQL procedure Query Performance issue

Author
Message
mallikachowdhary 98955
mallikachowdhary 98955
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 34
Hi ,

I have a issue regarding a query showing very late response on one server while it works perfectly on another.

I have a job defined in SQL server 2008 which runs a stored procedure of select query which fetches around 230000 rows . One one server this job runs in 6 hr but on another server this jobs takes around 1 day and 15Hr+.

Can anyone help me out in debugging this issue.

Thanks in advance.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
mallikachowdhary 98955 (7/24/2014)

I have a job defined in SQL server 2008 which runs a stored procedure of select query which fetches around 230000 rows . One one server this job runs in 6 hr but on another server this jobs takes around 1 day and 15Hr+.


6 hours for 230,000 rows sounds horrible in the first place, nevermind the extended time.

If you take a look at the link in my signature for index/tuning help, it'll show you how to get us what we need to help you. In particular, we need the execution plans. Understandably it'll be tough for you to get the actuals here (which are the preferred), but even the estimated ones and being able to see the differences between them would help.

This is more than a "Oh, it's this..." type of simple answer. It could be a tipping point in the data volume, a bad statistic, or any other number of items. It could even be a concurrency problem, but at 6 hours for the code I can't see us not being able to help you get that running with more speed.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
Compare the execution plans. Since the queries take so long to run, begin with the estimated execution plan. They will highlight differences between the two servers - say an index exists on one server but not the other. If you're not comfortable with interpreting execution plans, then post them here as .sqlplan attachments.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
mallikachowdhary 98955
mallikachowdhary 98955
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 34
Thanks Craig and Chris for the reply,

I have run the query on both the servers will post the execution plan once it runs, normally when running the same query independently (Not running the Job) takes around 6 hrs on one server and around a day on another Sad
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6837 Visits: 17775
mallikachowdhary 98955 (7/24/2014)
Hi ,

I have a issue regarding a query showing very late response on one server while it works perfectly on another.

I have a job defined in SQL server 2008 which runs a stored procedure of select query which fetches around 230000 rows . One one server this job runs in 6 hr but on another server this jobs takes around 1 day and 15Hr+.

Can anyone help me out in debugging this issue.

Thanks in advance.


Quick thought, even the "faster" server is only returning around 650 records a minute, for a non-transactional activity (select) it is very slow, which indicates that it is hitting bottlenecks in one form or another. I suggest looking into this first as it doesn't take much of a change in the environment to make it 6-7 times slower i.e. IO, memory pressure etc.. I have come across similar situations quite few times where the main difference was the actual hardware.
Cool

Regarding the execution plans, I guess you are better off using estimated plans;-)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
mallikachowdhary 98955 (7/24/2014)
Thanks Craig and Chris for the reply,

I have run the query on both the servers will post the execution plan once it runs, normally when running the same query independently (Not running the Job) takes around 6 hrs on one server and around a day on another Sad


In the meantime, can you post the query? There may be a few pointers to performance improvement.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
arnipetursson
arnipetursson
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 1019
I agree that 230k rows in 6 hours seems very slow.

Couple of additional questions:
Are the two servers equivalent in term of CPU,memory and I/O subsystem?
Do they have similar work load?
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3956 Visits: 6693
mallikachowdhary 98955 (7/24/2014)
Hi ,

I have a issue regarding a query showing very late response on one server while it works perfectly on another.

I have a job defined in SQL server 2008 which runs a stored procedure of select query which fetches around 230000 rows . One one server this job runs in 6 hr but on another server this jobs takes around 1 day and 15Hr+.

Can anyone help me out in debugging this issue.

Thanks in advance.


Are you literally doing "fetches", i.e, are you reading this data using a cursor?

If so, you need to re-write the code entirely to do set-based processing instead, or it will likely always be comparatively very slow.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6899
Another clarification needed. When you say "fetching" 230,000 rows do you mean that is the number of rows returned, or is that the size of the source table(s)? If the latter, how big are your source tables?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
mallikachowdhary 98955
mallikachowdhary 98955
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 34
Hi ,

I have attached the execution plans for the 2 servers here :
1. Mum plan (Server with better performance of 6 hr)
2. Del plan (I ran this one yesterday but still no out put so I stopped the query and saved the execution plan)

Please let me know if there are any differences in it.

This Job performs an "insert into table" action in the SQL table after fetching the data . The estimate number of rows which gets loaded daily is around ~230000 rows. The same solution is also deployed on the third server but there data is comparatively less and performance is also acceptable. But this 2 servers with similar data shows huge difference in performance.

The H/W (RAM, Processor, HDD and operating system) are same. Apart from this are there any other parameters that needs to be checked from SQL SERVER or H/W side , do let me know that.

Thanks in advance
Attachments
MuM R.sqlplan (24 views, 1.00 MB)
DEL QUERY.sqlplan (9 views, 1.00 MB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search