Win2K - SQL2K - performance question

We have an 8-way server running just SQL on Windows.

The performance of the system was OK when it was running WinNT and SQL7.
When we installed Win2000 with SQL7, performance was terrible.
Now that we have Win2000 and SQL2000, performance is a little better, but still too slow.

We are running a third-party application and don't have access to the code
for tuning purposes. (The application is the same, no changes to it.)

Any ideas on why performance took such a dive?

Dave

Impossible to say without understanding what the app is doing.
Run a profiler trace and capture a set of queries which perform badly for a given session.
Maybe they are forcing optimiser hints which are no longer optimal, maybe the statistics are out of date etc, many potential reasons.

Could you elaborate a little on other potential reasons?

We did update statistics on the all of the databases on the system.
The application itself didn't change nor did the database, except for
being updated from SQL7 to SQL2000.

Thanks

i would run profiler first to find which queries are the cause, before looking at statistics, sometimes its not the detailed statistics but rather the pages which can be fixed with dbcc updateusage(0)

The script to update statistics changed to the database, then "exec sp_updatestats".
Is there a better way that this should have been done?

The databases are on an external disk array.
They were detached from SQL7, data cable unplugged, then WinNT was updated to Win2K and
the databases reattached.
For the next upgrade, the databases were detached from SQL7, data cable unplugged, then
SQL7 was uninstalled and SQL2000 was installed. Then, the databases were reattached.
The system went through the upgrade process on the databases - there was a whole
string of messages like "database version 501 upgraded to 502" for each database, then
the system said that the database had been upgraded (presumably to SQL2000).
Except for the SQL2000 upgrade process and the sp_updatestats process the
data and log files for these databases weren't touched.

Again, we run a third-party application with encrypted stored procedures,
would running profiler be that helpful?

Can I presume that this syntax for UPDATE STATISTICS would need exclusive access to the database (as would DBCC UPDATEUSAGE)?

Dave

although you cant modify the software, it might still help to profile it.
From this, you may be able to see indexes which would now be beneficial but perhaps werent as evident on sql 7. Or if the 3rd part ysql is using optimiser hints at least you have some ammo to ring up the vendor and ask for a version optimised for 2k

Interesting comment about indexes.

Would I be able to tell that they are using optimizer hints in
encrypted stored procedures?

Thanks for the input, I'll start profiling to see what I can see.

I would recommend rebuilding the indexes in the database. I had faced a similar situation where rebuilding indexes actually helped and reduced the space used in the database.

My 2 cents....

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Do you recommend DBCC CHECKDB or DBCC REINDEX ?
If DBCC REINDEX, is there a way to have it run across all tables in the database?

2 cents is still valuable.

Dave

DBCC DBREINDEX

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

No, without knowing the sql behind each stored proc, you cannot see if they use optimiser hints. <br />However, the encryption used by sql is very poor, and there are tools to decrypt stored procs. <br /><br />Be aware however, that this is considered reverse engineering and may be frowned upon under your countries laws, but in your situation, if all else fails, I think you might want to consider these tools in the interests of research! <img src='/community/emoticons/emotion-5.gif' alt=';-)' />