| Author |
Topic |
|
RichardAnderton
Starting Member
15 Posts |
 Posted - 2012-12-04 : 16:34:14
|
| 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 editionWindows 2008 R2 Std, SP14 x Intel Xeon CPU @2.67Ghz20Gb RAMServer running 2005:SQL Server 2005 Enterprise editionWindws 2003 R2 SP22 x Intel Xeon CPU @ 2.33Ghz12Gb RAMThe 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
38200 Posts |
|
|
RichardAnderton
Starting Member
15 Posts |
Posted - 2012-12-04 : 16:41:52
|
| 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 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
RichardAnderton
Starting Member
15 Posts |
Posted - 2012-12-04 : 17:17:15
|
| 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. |
|
|
|
johnson_ef
Starting Member
16 Posts |
Posted - 2012-12-05 : 03:41:02
|
| 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 index3)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 |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-12-05 : 09:04:30
|
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 |
|
|
|
RichardAnderton
Starting Member
15 Posts |
Posted - 2012-12-05 : 09:30:38
|
| 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. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-12-05 : 10:21:50
|
| 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. |
|
|
|
RichardAnderton
Starting Member
15 Posts |
Posted - 2012-12-05 : 10:48:59
|
| 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... |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-12-05 : 11:01:53
|
| 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. |
|
|
|
RichardAnderton
Starting Member
15 Posts |
Posted - 2012-12-05 : 11:24:09
|
| 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. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-12-05 : 11:28:38
|
| 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? |
|
|
|
|
Speed issue