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 12»»

Query running slow in Production and fast in Test environment Expand / Collapse
Author
Message
Posted Monday, January 27, 2014 12:10 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 414, Visits: 2,081
Hi,

We have SQL 2008 R2 servers in our AWS cloud instances. Both environments are in cloud and have same configuration and disk layout.
But one of our SQL query ran with in 0 sec in Test environment and in production it is taking 7 mins to complete.
Test environment is restored with prod backup. So both are exact same copies.

After creating some indexes on Prod server- the query ran in 0sec in production. So i would like to know how we should understand this kind of scenario? How can i found the root cause of this issue as why it happened?

As without indexes in Test environment the query ran faster and with some new indexes(after performing tuning) the query is fixed in Prod.
Can some one suggest what should i be looking at?
Post #1535158
Posted Monday, January 27, 2014 3:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:41 AM
Points: 238, Visits: 1,108
Was the proc in live taking 7 mins repeatedly or was it just the one occurrence ?

Did you look at the actual execution plans from the procs in live & test.. We're there any differences ?

Cheers

Vultar
Post #1535232
Posted Monday, January 27, 2014 4:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 5,446, Visits: 7,616
muthyala_51 (1/27/2014)
Hi,
Can some one suggest what should i be looking at?


Concurrency.

The most typical reason two identical environments behave differently (and I do mean identical, down to the data in them) is the variability of usage. Test environments are usually quiet. Prod environments are usually noisy.

My guess is you had a different long running query doing updates or inserts and this one was stuck waiting for it somewhere. The new indexes probably allowed that to complete faster as well. But to determine that, you'd have had to been exploring the blocking chains.

If it was just indexing and no blocking, then you were getting different execution plans. To determine why, you'd have to examine them. It could be fragmentation differences, a change in parallelism... any number of things.



- 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 #1535252
Posted Tuesday, January 28, 2014 4:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 8:21 AM
Points: 7, Visits: 46
How is the query being run in the 2 environments,

Is it a Sproc?
Is it being executed by SSMS in both envinonments or is it bein executed by an application?

fill in a couple of details about the execution context..

cheers
Post #1535385
Posted Tuesday, January 28, 2014 7:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 414, Visits: 2,081
@andrew:

The query is running from .net application. But when the application running in staging it is fetching the data immediately but in production it is giving us the below error:
When we run the query in SSMS in production it took 7mins.



------------
Server Error in '/' Application.
--------------------------------------------------------------------------------

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Description: An unhandled exception occurred.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

----------------------------------------------------------------
Post #1535437
Posted Tuesday, January 28, 2014 7:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 8:21 AM
Points: 7, Visits: 46
Is it a stored procedure?
Post #1535441
Posted Tuesday, January 28, 2014 8:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 414, Visits: 2,081
Its a View- and view is created with 4 tables joins.

Post #1535462
Posted Tuesday, January 28, 2014 9:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 414, Visits: 2,081
@Vultar

Was the proc in live taking 7 mins repeatedly or was it just the one occurrence ?
-- it was taking repeatedly more than 7mins to execute and application is timing out with error.

Did you look at the actual execution plans from the procs in live & test.. We're there any differences ?
-- Both the execution plans looks exactly same.




Post #1535522
Posted Tuesday, January 28, 2014 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 8:21 AM
Points: 7, Visits: 46
How abouit I/o stats,?

Estimated and actual rows in both systems?
Post #1535525
Posted Thursday, January 30, 2014 7:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 414, Visits: 2,081
I am not finding the actual rows in the execution plan. So if they are not present then what does that mean?
Post #1536311
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse