February 4, 2015 at 8:35 am
Hi,
I've migrated my database from SQL 2000 to SQL 2012 using SQL2008 as intermediate. After migration, I found that one of my procedure is timing out from web site. When I run that procedure in Management Studio, it takes 42 seconds to display result. The same query is executed in 3 seconds in SQL 2000 version (in first run it takes 10 seconds in SQL 2000 and second run it took 3 seconds only). But in SQL 2012 second and third time also it took 36 seconds. Can anyone explain why the query takes longer time instead of less time after the upgrade?
Our prod deployment date is approaching so any quick help is appreciated.
Thanks in advance.
February 4, 2015 at 10:16 am
This usually has one of two causes. The first, and the easiest, is that your statistics need to get rebuilt. They'll be rebuilt automatically as you use them, but they'll be rebuilt using a sampled approach. You can try rebuilding them using a FULL SCAN. That might fix it right there.
The second, is tougher. In this case, you have a query that's probably fairly complex and while it wasn't fast in 2000, the performance was adequate. This is because the optimizer probably made a large number of different choices, maybe even timed out at a different state than in 2012. But now, the optimizer has a whole bunch of added and changed functionality. Suddenly, that adequate, but rocky, query is running very badly. The way to address this is, unfortunately going to require tough choices. You can try running it in Compatibility mode to see if that fixes things (although you can't run it in 2000 compatibility). However, that's going to impact all the functionality you can bring to the system and keep you from using 2012 functions. So, the second choice, is to tune the query. Identify what's causing it to run slow and fix it.
"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
February 4, 2015 at 12:52 pm
Thank you Grant. I think you are right. The new optimizer has new functionality that is causing the query to run slowly. I optimized the query and its running faster now. All other pages are working fine and faster. Thank you for your quick response.
February 4, 2015 at 12:53 pm
Thanks for reporting back what worked. That's really good to know and useful for people who find the question and answers when searching.
"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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply