SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query running slow in Production and fast in Test environment


Query running slow in Production and fast in Test environment

Author
Message
muth_51
muth_51
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 2928
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?
vultar
vultar
Right there with Babe
Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)

Group: General Forum Members
Points: 763 Visits: 1220
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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20399 Visits: 7660
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
andrew.edgar
andrew.edgar
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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
muth_51
muth_51
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 2928
@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.

----------------------------------------------------------------
andrew.edgar
andrew.edgar
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 46
Is it a stored procedure?
muth_51
muth_51
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 2928
Its a View- and view is created with 4 tables joins.
muth_51
muth_51
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 2928
@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.
andrew.edgar
andrew.edgar
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 46
How abouit I/o stats,?

Estimated and actual rows in both systems?
muth_51
muth_51
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 2928
I am not finding the actual rows in the execution plan. So if they are not present then what does that mean?
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