Do you know of some Microsoft Tool for tunning all the querys in SQL 2000? The request is to improve the performance of the DB, but for that, the developers have to know where is the problem. How can we get this information?
Thanks a lot for your answeres...
María del Pilar Oporto R.
You are at a disadvantage with SQL 2000, just like many of us that still have support it. The simplest way I know to speed things up on this beast is to run a trace capturing long running queries and then start there. If any of these queries are calling poorly written stored procedures you can tune them, you should also be looking to make sure you have the proper indexes for the most called queries/stored procedures.
You should also look at your disk, CPU, memory, etc. Being on SQL 2000, chances are you have older disk that probably are not aligned properly. You need to check the disk alignment.
Has the system always been running at the current speed or has performance slowly been getting worse? How is your index fragmentation? Are you updating statistics frequently?
Please give us a bit more information so we can give you a more targeted answer.
answered Apr 28, 2012 at 12:08 AM
A profiler trace can help you see what is running and how long it is taking, though it should be noted that the profiler itself can cause a (sometimes major) reduction in performance while it is running. Once you know what is being run the most and what seems to be taking a long time, you will know where to focus your tuning efforts.
The Index Tuning Wizard can help in tuning indexes in particular, but remember that it will generally not give results that are as good as hand tuning by someone with experience, and this was especially true in SQL Server 2000. I also have an article about general techniques to improve performance at SQL Server Central that might be of interest to you.
And it won't particularly help you if you are using SQL Server 2000, but I will point out that Dynamic Management Views can be very helpful for gathering information about where problem points are in later versions of SQL.
answered Apr 28, 2012 at 12:04 AM