| Author |
Topic |
|
rossbevin
Starting Member
34 Posts |
 Posted - 2007-03-29 : 16:22:39
|
| Hi there,I am an application programmer who uses SQL Server; I'm not an expert and just know the basics. Our application has approximately 300 SQL tables and each table has just one primary index. We don't have stored procedures and only have 3 Views that a couple of reports use. Our database is approximately 26GB in size. We are planning on migrating from SQL 2000/Windows Server 2000 to SQL 2005/Windows Server 2003. Before doing this I decided to do some bench mark testing. I chose a simple SELECT statement on one of our larger tables. On SQL 2000 it ran in 22 seconds whereas on SQL 2005 it ran in 34 seconds.These are the steps I have done to try and improve performance, all to no avail.I tried both a passive and active upgrade and the results were the same. The passive way was to remove 2000, install 2005, create a new database and restore from a 2000 backup into the new 2005 database. The active way was to leave 2000 on the server with the database attached and upgrading to 2005.I did not change any defaults on the database except I have set the compatibility level to SQL 2005.The collation is set to SQL_Latin1_General_CP1_CS_AS.I have run an Optimize Index Task.I have run a Rebuild Index Task.I have run an Update Statistics Task.I have defragged the drive that the database resides on.Can anyone explain why SQL 2003 is dramatically slower than 2000? Any help would be greatly appreciated. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-29 : 16:42:23
|
| Is this all on the same hardware, and what is the hardware? It sounds like maybe you are hitting resource limitations (most likely memory) and installing the new OS and SQL Server are resulting in less resources being available with a corresponding drop in performance? |
 |
|
|
rossbevin
Starting Member
34 Posts |
Posted - 2007-03-29 : 18:03:03
|
| The benchmark testing was performed on the same hardware. It is an HP Pavilion with an AMD 64 Athlon X2 Processor, 2GB RAM. 400 GB Seagate Barracuda HD, 7200 RPM, 8MS. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-03-29 : 18:06:42
|
| Just to confirm ... you rebuilt all the indexes (not defragged them) after you changed the compatability mode to 90? You ran each of the queries multiple times to eliminate any cache issues? Do the two queries issue the same number of logical reads?===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
rossbevin
Starting Member
34 Posts |
Posted - 2007-03-29 : 18:14:25
|
| graz, No, I did not do a defrag of the indices after changing the compatibility to SQL 2005; I don't know how to and just use the Maintenance Plan Wizard. Yes, I have run the rebuilding of indices and statistics a couple of times; again after I defragged the hard drive. Yes, the query resulted in the same number of reads on both 2000 and 2005. |
|
|
|
rossbevin
Starting Member
34 Posts |
Posted - 2007-03-29 : 18:35:20
|
| One more thing that I wasn't clear on. I ran the SQL 2000 test and the SQL 2005 test both on Windows 2003 Server Standard. Both databases have exactly the same data in them. The query I did wasSELECT * FROM PH_TRDET WHERE CODIV = 'PFL' AND BRANCH = '23' AND (PC_YRMTH BETWEEN '200601' AND 200612')The query returned 1,724,066 rows from 63,006,330. I ran it twice on both tests and used the 2nd reading. It took 23 seconds on 2000 and 34 seconds on 2005. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-03-29 : 19:01:45
|
| Where did you run these SELECTs? The bottleneck in performance is returning 1,000,000+ rows, not the data processing. I suspect that you may be comparing the "efficiency" of the client tools for 2000 versus 2005, not the database engine ... in which case I would not doubt that SQL 2000's Query Analyzer is much faster at returning 1,000,000 rows rather than SQL 2005's management studio since QA is so much more lightweight and does a lot less.If you want to compare the two database engines, I would do something like comparing the performance ofSELECT COUNT(*) FROM (... your sql statement here) xbetween the two different servers, since all of the processing is done on the server and a minimal amount of data is being processed by the client.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
rossbevin
Starting Member
34 Posts |
Posted - 2007-03-29 : 19:17:07
|
| Hi jeff,I initially discovered the slowness with several of our report applications. They do a SELECT statement to retrieve the data. These reports were 50% slower on SQL 2005. So, I thought I would just do a SELECT using a SQL Query. i will do what you suggest though and do a count. I will need to restore an image of my server though with SQL 2000; this will take an hour or two. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-03-30 : 09:11:51
|
quote:
Originally posted by rossbevin
The benchmark testing was performed on the same hardware. It is an HP Pavilion with an AMD 64 Athlon X2 Processor, 2GB RAM. 400 GB Seagate Barracuda HD, 7200 RPM, 8MS.
Just to confirm. These are two separate machines, or are these two instances of SQL Server on the same physical box? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-03-30 : 10:13:43
|
| Not at all. Or at least I don't think so. I have a number of clients that have databases that big or larger that don't have full-time DBA's.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
rossbevin
Starting Member
34 Posts |
Posted - 2007-03-30 : 10:17:22
|
| mcrowley, I use Acronis Disk Imagining software. I have an image with a SQL 2000 system and an image with SQL 20005; I can restore these images in 6 minutes. The actual databases; one for 2000 and one for 2005 are on a separate drive. So yes, I am testing on the same hardware. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-03-30 : 10:19:58
|
quote:
Originally posted by graz
Not at all. Or at least I don't think so. I have a number of clients that have databases that big or larger that don't have full-time DBA's.===============================================Creating tomorrow's legacy systems today.One crisis at a time.
Are you serious? Doesn't sound like a good thingBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
rossbevin
Starting Member
34 Posts |
Posted - 2007-03-30 : 10:55:18
|
| jssmith8858, I did as you suggested above and the count was 20 seconds on SQL 2000 and 20 seconds on SQL 2005. So you are correct; the engine is working at the same speed. It is my understanding that my application is receiving the returned rows through the ODBC SQL Driver. Is there something that can be done at that end to speed things up? |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-03-30 : 11:32:13
|
| One thing you could try is look at the NIC settings. If it is set to auto-negotiate the bandwidth, I suggest remove the auto negotiate and put the bandwidth of your network as a value.Whether it is 1000MBPS or 100MBPS. Negotiating the bandwidth results in serious slow down in sql 2005 as against sql 2000.RegardsParesh MotiwalaBoston, USA |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
rossbevin
Starting Member
34 Posts |
Posted - 2007-03-30 : 13:32:26
|
| paresh, I am not accessing the database over the network; I am working on a stand alone test server. |
|
|
|
nyana
Starting Member
2 Posts |
Posted - 2007-03-30 : 13:35:27
|
| Hello everyone. This is very interesting. We use SQL2005 its fast.. I dont have a problem about 2005.*** Edited by moderator to remove commercial advertisement *** |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-30 : 13:46:30
|
quote:
The query I did wasSELECT * FROM PH_TRDET WHERE CODIV = 'PFL' AND BRANCH = '23' AND (PC_YRMTH BETWEEN '200601' AND 200612')
rossbevin, this may be stating the obvious, but just to be sure, you do also know that using SELECT * is a bad idea right? Especially in a case like this where you are getting lots of rows, in your client app, make absolutely sure that only the columns that are actually used are requested, using SELECT * is going to return a lot of unecessary data and slow everything down. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-03-30 : 14:18:19
|
quote:
jssmith8858, I did as you suggested above and the count was 20 seconds on SQL 2000 and 20 seconds on SQL 2005. So you are correct; the engine is working at the same speed. It is my understanding that my application is receiving the returned rows through the ODBC SQL Driver. Is there something that can be done at that end to speed things up?
I would definitely check the database drivers at the reporting client. I would also use OLEDB and not ODBC, if you can, to access the database, since it is more efficient when accessing SQL Server. What reporting tool are you using?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
Next Page
|
SQL 2005 50% Slower Than 2000