July 3, 2015 at 3:07 am
Hi,
I have a stored proc that is executing in 2 sec on production and test database. It is taking more than a min on dev environment.
I have verified sqlserver version is same on both of the server.Prod is running on 2012Sp1 however dev don't have sp1. I am downloading it.
Both are 64bit, has same collation and compatibility level.I have confirmed that sp on both servers has same execution plan. I have reset and import stats from prod too.
Dev server don't seem under resource. What should I explore more to make it as fast as prod ?
Thanks
July 3, 2015 at 3:56 am
If the execution plans are the same then it's likely down to resources. Take a look at the waits statistics before and after the run in order to determine the waits that the query experiences in the development environment. That will give you a very strong indication of where the likely bottleneck is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2015 at 4:03 am
Also check that statistics and index maintenance have been performed.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 3, 2015 at 4:37 am
Grant Fritchey (7/3/2015)
If the execution plans are the same then it's likely down to resources. Take a look at the waits statistics before and after the run in order to determine the waits that the query experiences in the development environment. That will give you a very strong indication of where the likely bottleneck is.
can you guide me how to do that ?
July 3, 2015 at 6:07 am
thbaig (7/3/2015)
Grant Fritchey (7/3/2015)
If the execution plans are the same then it's likely down to resources. Take a look at the waits statistics before and after the run in order to determine the waits that the query experiences in the development environment. That will give you a very strong indication of where the likely bottleneck is.can you guide me how to do that ?
Just answered this on another question.
Two options. The easy option, query sys.dm_os_wait_stats before and after you run the offending query. Compare the differences. While easy, it won't be accurate. To get a very accurate measure of exactly what's going on, use extended events. Paul Randal has a blog post on this topic.[/url]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 7, 2015 at 2:16 am
wait type found is "CXPACKET" . This is because of an insert statement to temp table "insert into #tmp as select ........"
What other parameter should I consider and move further ?
July 7, 2015 at 3:16 am
CXPACKET waits are just an indication of parallel execution. They're neither good nor bad. What other waits are being experienced? Are both production and dev set to the same number of CPUs and same cost threshold for parallelism?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 7, 2015 at 9:22 am
production has 16 cores but dev has 8.
July 7, 2015 at 9:26 am
You may have found the difference then. If this query needs to execute in parallel and production has more CPUs than Dev, that would explain why it's slower in Dev.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply