Performance problems with 7.0 after upgrade from 6.5

[Marc ISRAEL]

Hi,

Hope somebody can help, because I am facing a major crisis...

We, at a customer's site, migrated many SQL Server 6.5 databases to 7.0, and the performances dropped down. We identified the problem was the stored procedures... Some procedures use parameters. If you substitute the parameters value manually the proc runs very fast (that is write the values directly into the queries), but if you leave the parameters, it runs very slowly (the worst example we have goes from 6 seconds to 6 minutes, for a single SELECT !). This phenomenon did not happen with 6.5. Is there a solution ? Thanks for your help !

Marc Israel
MCT-MCSE-MCSD-MCDBA-ACI

[Craig]

Have you tried applying SP1 to see if it corrects your problem?

Have you recompiled the SP's after an update_statistics to see if this helps?


------------
Marc ISRAEL at 8/27/99 4:52:32 AM


Hi,

Hope somebody can help, because I am facing a major crisis...

We, at a customer's site, migrated many SQL Server 6.5 databases to 7.0, and the performances dropped down. We identified the problem was the stored procedures... Some procedures use parameters. If you substitute the parameters value manually the proc runs very fast (that is write the values directly into the queries), but if you leave the parameters, it runs very slowly (the worst example we have goes from 6 seconds to 6 minutes, for a single SELECT !). This phenomenon did not happen with 6.5. Is there a solution ? Thanks for your help !

Marc Israel
MCT-MCSE-MCSD-MCDBA-ACI

[Patrick Logan]

SQL7 requires a totally different indexing strategy due to its heavy favortism toward clustered indexes. If in the past you used clustered indexes to spread data pages to avoid page contention during inserts, you might want to rethink and place the cluster on the primary key. All nonclustered indexes are then based off of the clustered key.

FYI: SQL7 will make the clustered index unique behind the scenes even if you don't ask for it or require it.

Bottomline: Totally rethink your indexing strategy and brushup on how SQL7 now builds and maintains its indexes. Also remember that the new statistics (pseudo indexes) are now coming into play.

I'd study the execution plans that SQL7 is selecting and determine why it is choosing the plan it did.

Hope this helps...


------------
Craig at 8/27/99 1:07:54 PM

Have you tried applying SP1 to see if it corrects your problem?

Have you recompiled the SP's after an update_statistics to see if this helps?


------------
Marc ISRAEL at 8/27/99 4:52:32 AM


Hi,

Hope somebody can help, because I am facing a major crisis...

We, at a customer's site, migrated many SQL Server 6.5 databases to 7.0, and the performances dropped down. We identified the problem was the stored procedures... Some procedures use parameters. If you substitute the parameters value manually the proc runs very fast (that is write the values directly into the queries), but if you leave the parameters, it runs very slowly (the worst example we have goes from 6 seconds to 6 minutes, for a single SELECT !). This phenomenon did not happen with 6.5. Is there a solution ? Thanks for your help !

Marc Israel
MCT-MCSE-MCSD-MCDBA-ACI