Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is SQL2K8 SSMS slower than SQL2K5 SSMS? Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 5:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:48 AM
Points: 2, Visits: 111
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?
Post #1434846
Posted Monday, March 25, 2013 6:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 15,738, Visits: 28,146
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1434875
Posted Monday, March 25, 2013 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:48 AM
Points: 2, Visits: 111
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?
Post #1434941
Posted Monday, March 25, 2013 8:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 15,738, Visits: 28,146
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1434949
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse