SQL Server Performance

How to find out what querry is a bottle neck, what is taking most time and resources?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by greg.balajewicz, Aug 24, 2009.

  1. greg.balajewicz New Member

    Hello everyone!
    I got an OLTP system (for www.realmofempires.com) which I am trying to analize.
    It appear the SQL server takes the bulk of CPU usage, and althought it could be just load, I wonder if there are tools that I could use to see what (what querry perhaps) taking most time/resources.
    I have a log of a running times, from client application perspective, of every single call to the database and I am unable to spot some long running querries this way but I optimized almost everything there and I am not sure how to move on.
    It is possible that without some app redesign i cannot squeeze any more from the server but I doubt it. Likely I have some inneficiencies that I can spot simply by looking at the running time of some querries but I am really not sure how to move on, what to look for.
    Can anyone suggest any tools, articles, books etc to help me shed some light on this?
    Many thanks!!
  2. imSQrLy New Member

    Start a trace with the sql server profiler, set a filter for 5 seconds on duration. see what pops in. If 5 seconds is to short go to 10, 15 etc just to get the top set of queries taking a long time. Once the statements are identified you can look at them to determine any modifications. You can also use dmv's but thats a more complex topic.
  3. RDW2 New Member

    You might want to take a look at Confio's Ignite product. I've used it some and it provides excellent WaitState analysis of bottlenecks and poorly performing queries.
  4. I782 New Member

  5. Greg Larsen New Member

    In addition to those other great suggestions already mentioned there are a number of SQL Server DMV's that you can use to find statistics about the worst performing T-SQL statements and stored procedures. Here are a couple of articles I wrote on the subject that might help.
    http://www.sql-server-performance.com/articles/per/Query_Execution_Statistics_p1.aspx
    http://www.sql-server-performance.com/articles/per/Monitor_Stored_Procedure_Performance_p1.aspx
    http://www.databasejournal.com/feat...erforming-T-SQL-Statements-on-an-Instance.htm

Share This Page