SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Speed issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

RichardAnderton
Starting Member

15 Posts

Posted - 12/04/2012 :  16:34:14  Show Profile  Reply with Quote
Hi,

We recently upgraded from SQL 2005 to SQL 2008.

In trying to ensure maximum speed, I noticed the following:
I ran a query from the 2008 SQL management studio against its database, and the query completed in 4 minutes. I than ran exactly the same query from the 2005 SQL management studio, but against the database on the 2008 box. It completed in 3 minutes.

Server running 2008:
SQL Server 2008 Standard edition
Windows 2008 R2 Std, SP1
4 x Intel Xeon CPU @2.67Ghz
20Gb RAM

Server running 2005:
SQL Server 2005 Enterprise edition
Windws 2003 R2 SP2
2 x Intel Xeon CPU @ 2.33Ghz
12Gb RAM

The tests were run immediately after a restart on both machines.

I have checked out the enterprise v standard edition implications, and MS claim that the standard edition is not 'throttled back'

The 2008 setup is a dedicated database server, and has no application software running on it (while the 2005 server ran the application as well, and also served as a print server). All servers sit inside the company firewall, and have no anti-virus or any thing else running on them.

So, the question is... why is the query running slower when ran on the 2008 box, than if ran on the 2005 box against the 2008 data?

Any help much appreciated!

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 12/04/2012 :  16:37:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
Compare the execution plans.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

RichardAnderton
Starting Member

15 Posts

Posted - 12/04/2012 :  16:41:52  Show Profile  Reply with Quote
Hi,

I have tried that. 2008 is using a parallel quey plan, but apart from that both are the same. Can I / How do I enforce the same query plan on both servers so that I can rule out that potential issue?

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 12/04/2012 :  17:10:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
I would suggest updating stats on both.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

RichardAnderton
Starting Member

15 Posts

Posted - 12/04/2012 :  17:17:15  Show Profile  Reply with Quote
Hi,

Sorry - I may not have made the situation clear. They are not 2 databases The database is on the 2008 machine (there is no data on the 2005 machine anymore)

I am running the query from the 2005 environment by connecting to the databse engine on the 2008 machine from the 2005 SQL management studio. Hence my surprise at the result (querying the database from the 'old' hardware appears quicker than querying the database from the machine on which it resides)

I should also mention that neither CP spikes to 100% during the query, and watching memory usage in task manager, neither machine gets to even 25% of memory usage (both machines were restarted immediately before testing)

Thanks.

Edited by - RichardAnderton on 12/04/2012 17:19:47
Go to Top of Page

johnson_ef
Starting Member

India
16 Posts

Posted - 12/05/2012 :  03:41:02  Show Profile  Reply with Quote
As a part of upgrade/ migration, you also need to perform some tasks which make the Database ready with new version.
As Tara Kizer suggested, 'Update Stats' is one among them. Below listed are the steps I follow during SQL upgrade.

1)Execute DBCC UPDATEUSAGE on the restored database.
2)Index Rebuild :- This process drops the existing Index and Recreates the index.
or Index Reorganize :- This process physically reorganizes the leaf nodes of the index
3)sp_updatestats

If you suspect any issue of plan, you can also try to recompile the SP (sp_recompile 'procedureName'), This will recompile a particular stored procedure.

Check the performance after you follow these steps.
-Johnson


Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 12/05/2012 :  09:04:30  Show Profile  Visit jackv's Homepage  Reply with Quote
quote:
The tests were run immediately after a restart on both machines


Does this mean you a) reboot both servers, run from the sql server 2008 b) reboot both servers , run from sql server 2005 ?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

RichardAnderton
Starting Member

15 Posts

Posted - 12/05/2012 :  09:30:38  Show Profile  Reply with Quote
Hi,

johnson_ef - all these are up to date. As it is only one database, would the issue not manifest itself on both querys? Eitherway, I would expect the more modern setup to outperform the older setup.

Jack - No, I did not reboot the servers between each test - I appreciate that caching could therefore have swayed the results. The reason I did not reboot between tests is that the results were exactly the same as I had received when the query was run on the servers while under load (ie during the working week, with SQL already eating as much memory as we let it). The tests were also ran with the expected faster result first (ie 2005), to give 2008 a chance.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 12/05/2012 :  10:21:50  Show Profile  Reply with Quote
Something doesn't sound right here. SQL management studio is just a visual interface so you should not get different execution plans!

In terms of the time taken, are you running these from different Clients? For example if you are running the 2008 directly on the server and your 2005 is a Client on the network. That could explain it if your query is returning a lot of rows, in one case, you need to send the rows over the network and in the other case you don't.

Or maybe there are some settings on your SQL management studios that are different although I can't think of anything that would cause this.

Go to Top of Page

RichardAnderton
Starting Member

15 Posts

Posted - 12/05/2012 :  10:48:59  Show Profile  Reply with Quote
Hi Denis - you have exactly my concern... (execution plan difference explained by the no of cores - it has decided to parallise one query, and not the other)

From what I can deduce, the database engine is working well - it can return this particular dataset in 3 minutes to the 2005 setup (presumably the database engine on the 2008 server does the work, and just displays the data on the 'client' SQL studio on the 2005 machine - if anything, this should be the slowest result, as the data has to be passed from one machine to the other)

However, the exact same dataset, generated by the database engine on the 2008 setup, takes an additional minute to display in the SQL studio on the 2008 machine...

As you say - it sounds like a setup issue somewhere, but in the 'application' layer on the SQL 2008 box. I can't for the life of me get to the bottom of it.

There are some other issues that could effect it - the 2005 box has an older version of windows etc... etc... but I can't really hold out that they are the cause (why would 'older' software perform better? - by 25%)

Perplexed...

Thanks for any other thoughts / help...
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 12/05/2012 :  11:01:53  Show Profile  Reply with Quote
Sounds like you are running this from different computers.

Not a likely cause but if one computer is slower than the other, maybe it is slower generating the rows in the interface. How may rows are being returned?

Also could it be network access is slower for one or the other. i.e. slower network card, again that could only be an issue of you are dealing with many rows.

Of course none of that addresses why you have different execution plans.
Go to Top of Page

RichardAnderton
Starting Member

15 Posts

Posted - 12/05/2012 :  11:24:09  Show Profile  Reply with Quote
Hi Denis,

The query is being run from different machines - but against the same database. The concern is that the 'remote' machine (running older software) is taking 1 minute less than when the query is run on the machine that hosts the database with newer software, more memory, and faster processors.

1 million rows returned from a straight forward inner join on 2 tables.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 12/05/2012 :  11:28:38  Show Profile  Reply with Quote
Try the same experiment with a different query that also retuns a Million or so rows. But a query that gives the exact same execution plan. Maybe a staight select on one table.

Maybe it has something to do with one computers place in the Network?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000