Post reply

How to trace long running Queries without SQL Profiler(Frequncy)

  • August 9, 2011 at 4:35 am

    #241442

    Hi All,

    I have SQL Server 2008 Ent edition(R2).My server is having Performance issue.report generation is slow due to Some of the queries which are using HIGH CPU.

    I want to prepare Performance report for that i want to gather queries which are taking high CPU along with their frequency.Previously i use to run trace to gather required data.

    Bt my ORAG is not allowing me to run trace becoz task is resource consuming.

    Can any one suggest another way to gather these queires with number of occurances??So that i can start with analyzing?

    Thanks in advance

    Akshay

  • August 9, 2011 at 4:49 am

    #1365743

    There should be some good tools available in codeplex site.

    Have you tried searching there?

    M&M

  • August 9, 2011 at 5:57 am

    #1365804

    A server-side trace is the best mechanism you have for doing this. They are not very resource intensive at all. In fact, it has a very light foot print as long as you don't collect very many events or columns. The default trace in Profiler is very benign (but use it as a model, don't run the Profiler gui against the server, that is problematic). I'd push back and get the server-side trace going.

    The other option you have is to use the DMVs such sys.dm_exec_query_stats. This is an aggregate of the performance of all queries that are in cache. That might get you what you need, assuming the query is in cache. As soon as the query leaves the cache, the statistics are lost as well. You can combine this DMV with others to put together meaningful information pretty quickly. If you have access to the last two years at the PASS Summit, I've done a presentation on exactly that. I'm doing it again this year. Otherwise, pick up a copy of Louis Davidson & Tim Ford's book.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 9, 2011 at 7:14 am

    #1365875

    HI All,

    Thanks for your replies.I am going through Gail's article on server side trace.here is link.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    And i am also going through DMV's.

    Thanks,

    Akshay

  • August 22, 2011 at 11:41 pm

    #1371770

    I agree server-side trace is a good approach. Another low-intrusive method is to tune the queries against the execution plans.

    Jack Vamvas
    sqlserver-dba.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply