January 27, 2014 at 12:10 pm
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?
January 27, 2014 at 3:34 pm
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
January 27, 2014 at 4:24 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 28, 2014 at 4:58 am
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
January 28, 2014 at 7:24 am
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.
----------------------------------------------------------------
January 28, 2014 at 7:28 am
Is it a stored procedure?
January 28, 2014 at 8:03 am
Its a View- and view is created with 4 tables joins.
January 28, 2014 at 9:09 am
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.
January 28, 2014 at 9:14 am
How abouit I/o stats,?
Estimated and actual rows in both systems?
January 30, 2014 at 7:00 am
I am not finding the actual rows in the execution plan. So if they are not present then what does that mean?
January 31, 2014 at 8:50 am
Actual and estimated execution plan details are same in both environments.
Can any one please provide what should i be looking next.
The tables are very small not more than 10,000 rows.
January 31, 2014 at 8:59 am
IO statistics
February 4, 2014 at 4:40 pm
have you updated statistics and or see any differences in execution plans compared to prod. Like compare the tasks in the execution plans with prod's.
also maybe prod has buffer cache and nonprod has no plan cache if it was recently restarted or restored new db. my guess
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply