Post reply

How to get all activities on SQL server without using Profiler.

  • September 18, 2008 at 10:15 am

    #873753

    David NienDuy Nguyen (9/18/2008)

    Hi All,

    I ran into the same situation. I was assigned to identify and remedy poor performing queries on an extremely heavy use production server. First I was using SQL Profiler in combination with Database Tuning Advisor using Tuning predefinied template to monitor bad SP, T-SQL etc... CPU, read, write trying to get the hint toward a solution. But my boss and the IT team ordered me to stop that because it adds more overhead, performance penalty to already heavy-use production server, degrading the performance more... I know I can use DMV, DMF ...but as far as I know, SQL profiler is the best tool to diagnose performance....

    Now, I'm not allowed to use SQL profiler, anyone knows a tool, another way to substitute SQL profiler without incurring more overhead on the production server.

    Thanks.

    David N Nguyen

    Server Side tracing is the way to go although some of the DMV's can give you the information you may need. Send the link Andrew posted to your boss and IT team. Also ask them if they know that SQL Server 2005 has a trace running all the time already, unless they explicitly stopped it.

    sys.dm_exec_query_stats could allow you to start to identify queries that should be optimized, if you can't run a server side trace.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing post 16 (of 15 total)

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