Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

SQL procedure Query Performance issue Expand / Collapse
Author
Message
Posted Thursday, July 24, 2014 1:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:27 PM
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.
Post #1595801
Posted Thursday, July 24, 2014 2:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:58 PM
Points: 6,259, Visits: 7,453
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
Post #1595805
Posted Thursday, July 24, 2014 2:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Post #1595807
Posted Thursday, July 24, 2014 3:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:27 PM
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
Post #1595827
Posted Thursday, July 24, 2014 3:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 1,990, Visits: 5,183
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.


Regarding the execution plans, I guess you are better off using estimated plans
Post #1595830
Posted Thursday, July 24, 2014 3:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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


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
Post #1595832
Posted Thursday, July 24, 2014 2:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2014 11:45 AM
Points: 275, Visits: 863
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?
Post #1596020
Posted Thursday, July 24, 2014 2:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 2,098, Visits: 3,155
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1596021
Posted Thursday, July 24, 2014 2:47 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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? -- Stephen Stills
Post #1596026
Posted Friday, July 25, 2014 1:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:27 PM
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


  Post Attachments 
MuM R.sqlplan (19 views, 1.10 MB)
DEL QUERY.sqlplan (7 views, 1.14 MB)
Post #1596109
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse