Hi again!

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.
more ▼

asked Apr 27, 2012 at 11:06 PM in Default

Maripili gravatar image

394 32 33 35

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Apr 28, 2012 at 12:08 AM

Tim gravatar image

36.4k 38 41 139

(comments are locked)
10|1200 characters needed characters left

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][2] 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.

[2]: http://www.sqlservercentral.com/articles/Performance/71001/
more ▼

answered Apr 28, 2012 at 12:04 AM

TimothyAWiseman gravatar image

15.6k 21 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 27, 2012 at 11:06 PM

Seen: 626 times

Last Updated: Apr 28, 2012 at 12:08 AM