Sql Server 6.5 faster than Sql Server 7.0?

[mark123]

Hello,<br><br>I have the same data and tables set up for Sql Server 6.5 and Sql Server 7.0. Our application is running approx. 2 times faster on 6.5 than 7.0. Can anyone point me in the right direction as far as DB configurations/setups, table indexes, locking type, etc. that might be causing this?<br><br>Thanks in advance,<br>Mark

 

[jnicho02]

Are you running on comparable hardware?<br>Or are you using the same server for 6.5 and 7.0? If so, is the 6.5 still switched on and using up memory and resources?<br><br>As you are using exactly the same table structures and indexes then I would think that it is something to do with the server setup, e.g. hardware setup or memory allocation.<br><br>The locking strategies on SS7 have been improved and should give better performance. <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br>

 

[mark123]

jnicho02,<br><br>Thanks for your reply! The SS6.5 and SS7 are running on different comparable machines and they have the exact same table structures and indexes. Also, the SQL that our application is running are basically simple select statements. No complicated inner joins, or other SQL statements that would appear to confuse SS7's optimizer.<br><br>Some more info: We have a table called Pshstj in the DB that has about 300 thousand records. When we truncate this table, the application works about 5 times faster. The catch is that the Sql that we are testing doesn't hit this table.<br><br>This caused us to believe that the problem may be related to how SS7 dynamically allocates space and memory in different circumstances. We turned this dynamic allocation off and the problem still didn't go away.<br><br>We still think that there may be a setting somewhere in SS7 that is causing the problem but we don't know enough about SS7 to know where to find it.<br><br>Any ideas?<br><br>Thanks again for your help,<br>Mark

 

[jnicho02]

As you've probably noticed, there aren't too many settings to fiddle with so maybe your tempdb isn't the same size as it was before.... or you've created your databases on a drive with not very much space.... or your virtual memory is smaller on the new server....<br><br>Another idea, are your index statistics definitely correct? i.e. have you added a load of new records and not done an 'update statistics'? On 7.0 in Enterprise Manager on the database properties you can set the update statistics to auto fire.<br><br>That thing about truncating the 300k record table is weird...can't see for the moment how it would affect things other than if the database is on the same drive as the page file...if the database shrinks then the page file might be able to grow more (?).<br><br>Just some ideas off the top of my head. Performance and tuning is a bit like being Sherlock Holmes. <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br>

 

[mark123]

Hi jnicho02,<br>Thanks again for your reply. I'm going to share your ideas with a few of the guys over here to see what we cn come up with.<br><br>Your help is greatly appreciated!<br>Mark