Post reply

Profiler - simple commands take ages

  • March 28, 2008 at 6:34 am

    #185671

    Running a profiler trace on one of our servers (SQL2005 SP2) and filtering for all Duration >=2500 I see lots of incidents of trivial statements taking several seconds to complete.

    For example:

    SP:StmtCompleted -

    TextData = "exec sp_reset_connection"

    StartTime = 11:06:19

    EndTime = 11:06:27

    CPU = 0

    READS = 0

    WRITES = 0

    DURATION = 8703

    SP:StmtCompleted -

    TextData = "select convert(sysname, serverproperty(N'servername'))"

    StartTime = 11:06:21

    EndTime = 11:06:27

    CPU = 0

    READS = 0

    WRITES = 0

    DURATION = 5517

    SP:StmtCompleted -

    TextData = "if @info_filter = 1"

    StartTime = 11:06:28

    EndTime = 11:06:34

    CPU = 0

    READS = 0

    WRITES = 0

    DURATION = 5476

    Any thoughts on why Profiler might report these statements as taking so long to complete?

    Do I trust it?

    From a general performance point of view, there windows server has plenty of CPU and memory in reserve, and there appears to be no significant blocking taking place.

    We are running merge replication on this server, replicating to 3 subscribers, each of which is running local pull subscriptions.

  • March 28, 2008 at 6:59 am

    #795136

    One change in SQL 2005 is that profiler records durations in microseconds, not milliseconds like 2000 did.

    By default the gui still shows in milliseconds, but behind the scenes (for filters and when importing into a SQL table) the microseconds are shown.

    If those are milliseconds (and the start/end times seem to indicate that), then check blocking/waiting. I have before seen reset_connection take ages. Not sure why.

    (sys.dm_os_waiting_tasks is a good view to start with)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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