March 28, 2008 at 6:34 am
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
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply