SQL Server Performance

Normal Steps for tuning of MSSQL2005

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by TimB83, Jan 8, 2009.

  1. TimB83 New Member

    I want to tune the database statements of my program. So I have read a lot about MSSQL2005 DTA and other things. Therefore I have the following question:
    Is this the right way?
    1) Start SQL Profiler
    2) Run own program (which selects, deletes and updates some tables and so one (tool is working very slow because of so much data)).
    3) After working with my own program I can find a big list of statements in my SQL Profiler. I save this file as test.trc
    4) Then I run the DTA (Database Engine Tuning Advisor), select the test.trc and the correct database. After some minutes it says the following to me:
    => many hints of creating indizes/index for the tables. The performance could be more than 50% better then actual performance - this sounds good!
    Now my question: I tried to execute this hints on database and it works fine. The system creates all these index/indizes and so one. Is this the right way to get a better performance? Or isn't it good to use this hints of DTA and execute them!? On the other way I have optimized the SQL-statements in my tool, this helps alos a little bit.
    Hope you can give me a hint.
    Thanks a lot, Tim
  2. ghemant Moderator

    to me this *depends* on the situation and if I were at you I would follow the typical scenario i.e. I'll check the execution plan of slow/poor performing queries and try to optimize it.
    The way you approach is also good except using hints with queries is not always good (IMO).
  3. gurucb New Member

    DTA is a good tool and very much enhanced in SQL Server 2005 there is no doubt about that.. But still both DTA and Missing Indexes DMV are just tools.. These tools go query by query and generate scripts for creating indexes and hints.
    For example there is a queries like below
    Select EmployeeID, EmployeeName from Employee where EmployeeID = 1
    Select EmployeeID, EmployeeName, ManagerName, Address from Employee where EmployeeID = 1 and EmployeeName = 'guru'
    There are high chances that DTA and Missing Indexes DMV would suggest 2 indexes but here in index would suffice for both queries. So while applying the hints / suggestion directly I would manually go over each suggestion and then only apply them.
    For performance Tuning as a general, it would be only 5 - 10 queries that are consuming 70% or more of system resources. The challange is to indentify those queries and fine tune only those queries.
    With SQL Server 2005 there is a kewl tool called SQLDIAG.exe that automates the process of collecting performance related data (Perfmon, Profiler etc).
    As Step 1: Run this SQLDiag and collect that information. (I can help if you need)
    There is also a other tool to analyze top resource consuming queries.. called SQLNexus.
    Download it from www.codeplex.com it will need a SQL for its own analysis and .net framework.
    As Step 2. Input data collected from Step 1 to this tool and let it run and analyze.
    As Step 3: Take top resource consumers and then run DTA on those queries or analyze them manually and implement solution.
  4. TimB83 New Member

    At first thank you very much for your answers and this great ideas! I will have a look for each of your hints and will try the other tools in the next days.
    A last question:
    At first I searched for the slow queries and changed them (in this case I changed things like WHERE... IN(...) to JOIN...ON....). Do you know a website/ blog where I can find more hints for optimizing sql-server queries?
    After this changings of queries I tried the DTA Yesterday. This tool told me very often to create a statistic. I never had heard this before so I searched for statistics in google. I think the statistics would bring a much better performance and I will create them. But therefore my question: Is there any disadvantage of creating/using statistics?
    Best regards,
  5. gurucb New Member

Share This Page