SQL 7.0 5x slower than 6.5

[guidovalduchi]

A customer of our recently updated their SQL 6.5 to 7.0. They are now noticing that any transactions are running roughly 5 times slower than the same transactions against 6.5.

Any help would be greatly appreciated!

 

[TomSark]

It usually takes about 3 days for SQL Server 7.0 to self tune itself, providing the activity on it is representative of the production environment. I don't know if you've given it enough time to do this or not...

There could be many factors why this is happening, however, you need to supply more details about the way things are architected & designed before we can respond with a good answer... there are many other things that could be affecting this as well... the clients could also be an issue (older MDACs used to build the apps, etc.)

But, one thing is for sure, 7.0 is lightning fast when compared to 6.5. You need to do some digging to find out why you are slowing down...

Tom

 

[guidovalduchi]

Tom, thanks for your reply. I talked to our application programers and we're confused. Do you need specific information on the application that we're running over the SQL backend, or the SQL server properties itself?

One of our DBA's in house suggested clustering the key indexes in the tables, would that help speed things up?

Thanks in advance!

 

[TomSark]

Hi Father Guido, ;-)

If you don't have indexes on your SQL Server tables, queries could be dramatically slow, regardless of the version of SQL Server you are running.

On the application side, an example can be using outdated MDAC, ODBC drivers, etc. can cause performance issues in some cases, but this is just an example, I'm not saying that this is your problem. I'm saying that you should do some digging.

Basically, there is a recommended way to architect your server and databases... there have been some other changes that might possibly have affected you as well. If you have dinked with your server settings, you could have shot yourself in the foot... have you done this?

This will be a needle in the haystack until you can begin to isolate your problem. I would suggest using the SQL Server related perfmon counters to start (for server, memory, disk i/o, network measurments, etc) in order to eliminate those possibilities.

But, normally when things run slow, it's usually caused by poorly designed queires, the lack of indexes, the lack of updated statistics, etc...

Again, 7.0 is much more efficient that 6.5 and has many optimizations that destroy 6.5s performance, therefore, I am inclined to believe that you've got some design deficiency or some architectural issue of some kind.

Tom

 

[foxdev]

In addition to Tom's pointers, I have also found that the default Named Pipes was 10x slower than TCP/IP, at least on the network I was using.

Robert Bradley

http://www.foxdev.com/