Upgrading from SQL70 to SQL2005 makes my DB run dog slow..

Question

Upgrading from SQL70 to SQL2005 makes my DB run dog slow..

creichert

Grasshopper

Points: 24
More actions
February 25, 2008 at 2:42 pm

#186928

I have a client with an NT4.0 domain, and SQL7.0 running. The dbs are accessed by a vertical industry app, Trapeze (transportation) which uses a client app which links via ODBC System DSNs.
We have installed a new super fast server, running Windows Server 2003 and SQL2005.
I copied the DBs across and fired them up on the SQL2005 server. I then pointed the XP Pro clients to the new server and DSNs (recreated the DSNs happily).
All works, for the most part. Two problems:
1. speed. It is dog slow on the new servers. On the 7 yo NT4 servers is it acceptable, snappy even. On the SQL2005/Win2K3 it is slooooww..
Any thoughts? Should I convert/upgrade/optimize?
2. Rights. The users can't access the Dbs unless I make the users part fo the Administrators group. This is just for testing, of course. How do I find the name of the NT4 group that gave them rights on the old server so that I can recreate a similar thing on the win2K3 server? Is this within SQL Manager or an AD issue?
Many thanks for your help with this.
Christopher

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

Have you updated your statistics? Run a DBCC SHOWSTATISTICS (tableName, IndexName) on some your larger tables, post the results. You may need to update statistics with a fullscan (sample script below).

Regarding the logins; you should be able to identify them on the SQL 2000 server via Enterprise Manager. You can use sp_help_revlogin to migrate your logins, see http://support.microsoft.com/kb/918992

Tommy

Follow @sqlscribe

danschl SSCertifiable Points: 5770 More actions · Answer 2

i usually run sp_updatestats

then i run sp_change_users_login 'auto_fix'

to fix the mismatched logins

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 3

Keep in mind that sp_updatestats updates statistics by using the default sampling which is typcially 10%. I would recommend a fullscan.

Tommy

Follow @sqlscribe

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Also - don't assume that SQL 2005 will use the same indexes as 7.0, nor that the optimizer will at all act the same as 7. It looks to me that the optimizer changed quite a bit between 2000 and 2005, so I can only imagine how different it might be from 7's. You may just need to spend some time look at what items are slow, and possibly re-write/reoptimize some of those.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Doug S. SSCrazy Points: 2801 More actions · Answer 5

Most likely this is the problem:

1) users need a default schema for DBO and you can take them out of the Admin group. Probably the default schema for jsmith is jsmith (not dbo) and they can't see the data

2) run profiler on sql 7 and sql 2005 and see if you are creating server side cursors in 2005.

Most likely its a problem in the OLE DB providers or ADO/RDO/DAO settings in the application. Find out what the application is using and post back.

Your SQL server is probably screaming, most likely its the application.

Doug

creichert Grasshopper Points: 24 More actions · Answer 6

Hi all,

thanks for all the ideas. I will be getting onto the system in the next day or so to try this all out.

I'll report back.

Thanks,

Christopher