Hi..
I am Developer, good in T-SQL Performance Tuning. but i was allocated to Server Performace Tuning Task and i had a copy of Prod Server as a local Server in my Mechine. I have to give list of suggestions to improve the performance of Prod Server.
As i am new to this kind of Job. i refered online sources to write my suggestions. but i could not come to conclusion.
Please give me your suggesttions on how/what to come first and one by one.
Thanks in advance, Kumar V
First of all, if you are looking at Server performance, you should have a copy of the production environment that looks like the production environment. There is no point in evaluating server performance on your local PC. Otherwise a good place too start reading is http://www.sql-server-performance.com/
As Steinar said, ideally you need a hardware environment that is like the production server. However, before doing that, you can go through the database and make sure there is nothing obviously missing (e.g. tables without clustered indexes etc).
You probably also have an idea of the queries that need their performance improving - so you can make sure that the query plans for those on your local PC look roughly (and do note the emphasis on roughly) right, and possibly suggest indexing improvements, if there is anything that is obviously massively wrong.
Beware though. DO NOT attempt to do any fine tuning on your local PC whatsoever.
To know what to do with any server, you need to first, determine the settings of that server, memory, disk location & type, SQL Server configuration, on & on. Then, you need to gather metrics from the server, without a load, using perfmon (or similar tool) in order to determine it's baseline behavior. Store those metrics somewhere. Then, repeat the process with the server under load with special emphasis placed on observing and cataloging the wait states. Compare the loaded metrics with the unloaded metrics and check the wait states. You know have a set of measurements you can use to determine what needs to be done to improve performance.
That's how you do performance tuning on a server, or at least, that's the beginning of it.
(I tried to make this a comment but ran into the character limit)
I agree with everything Matt, Grant, and Steinar have said, but perhaps I can make 2 general points that will help.
First, remember that many performance tuning options come with trade offs. Adding an index will speed reads and slow writes, for instance. Often it is obviously worth taking that trade off, but some people entirely forget that they are making a trade off and that can lead to problems if they do not understand the actual work load they are optimizing for. Which of course leads back to Grants points about getting a good baseline idea before you start trying tweaks.
The second point is if you are looking at the hardware side of things, consider adding more RAM. For many SQL Server systems that will be the most cost-effective performance boost there is, and there are very few SQL Server Systems out there now that would not benefit from more RAM.
