March 25, 2013 at 5:57 am
We have a DB [MYDB] in SQL2K5 box, we are migrating the DB to SQL2K8 box, hence restored the backup from 2K5 box to 2K8.
I have a question with following query
set statistics time on
SELECT TOP 1000 * FROM [MYDB].[my].[MY_TABLE]
set statistics time off
In 2K5 SSMS it takes around 3000 ms and in 2K8 SSMS it is taking 9000 ms.
Now if I execute the query from 2K5 SSMS for both the DB then execution in 2K8 DB is less than 3000 ms
and if I execute the query from 2K8 SSMS for both the DB then execution in 2K5 DB is more than 9000 ms
What could be the reason? Is there any feature I need to disable in SSMS 2K8?
March 25, 2013 at 6:34 am
Are you capturing the execution plan in the 2008 version of SSMS? Make sure you have the latest service packs installed on your server and on your client machine. Check the default settings for connections on both to ensure one isn't different than the other. After moving the database, you need to update statistics. If not, they get updated manually. Try updating them with FULLSCAN on the 2008 server to ensure you've got the best possible statistics. Not being there it's hard to know, but no, I've never seen any differences in execution time for queries run from 2005 or 2008 versions of SSMS.
"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
March 25, 2013 at 8:31 am
Updated statistics with FULLSCAN, but no improvement. My question is why the query taking more time while executing locally in SQL2K8 SSMS than executing remotely from SQL2K5 SSMS?
March 25, 2013 at 8:45 am
By default, all things being equal, it shouldn't be taking longer with 2008 SSMS. So, you have to figure out what's not equal. Again, are you capturing an execution plan? Do you have different connection settings, the ANSI settings? There has to be a difference somewhere.
"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