I recently looked at an underperformaing 32-bit SQL Server 2000 server recently, and had to come up with some suggestions as to how performance could be increased. Surprisingly the ‘best practice’ guides for this version of SQL Server were hard to come by, and quite often refered to facilities only available in SQL Server 2005 and above.
This is not a comprehensive list, but more of a starting point:
1) Implement new indexes
Run profiler against a realistic load of your database, and take note of the Reads, Writes and CPU count of each operation. Quite often a disproportionatly expensive query does not have a matching optimized index and is performing full table scans. If that query is very common, then you have an obvious candidate for performance improvement.
2) Remove duplicate existing indexes
Conversly you can have too many indexes, which are expensive to maintain. If you have two similar indexes, one on columns A & B and another on A & B & C, consider removing the second one. You may find overall perfroamnce gains
3) Consider using AWE
If your server has a lot of RAM and your SQL Server edition allows it, then consider turning on AWE to start accessing more than 1.7GB of RAM for your database. This will:
- Reduce use of TempDB
- Reduce general disk access
4) Consider upgrading to SQL Server 2005 64-bit
Although you can get 64 bit memory access in SQL Server 2000 Data Center edition, a more cost effective (and now supported) option would be to migrate up to SQL Server 2005
5) Maintainance Plan that Rebuilds Indexes Frequently
Fairly obvious, but a good maintainance plan will optimise your database on a nightly basis. (assuming you can afford the downtime overnight)
6) Remove use of Cursors
Despite what some people say, there are very few scenarios where you need to use cursors. Replace them with good queries inside Stored Procedures and see great increases in performance.
7) Rewrite queries that use ‘SELECT *’
Should be obvious, but a query that runs SELECT * is probably scanning the table far more than it really needs to. It also makes changes to that table risky.
8) Remove HINTS from queries
There might be some debate about the usefullness of HINTS, but the reality is that SQL Server 2000 can optimise queries far better than you can with a manual HINT prompt. If anything you are likely to make the query slower as a result by using it.