Showing posts with label trace. Show all posts
Showing posts with label trace. Show all posts

Monday, February 13, 2012

All zero Reads in profiler trace

I'm looking at a trace of 100k rows over two hours for a
production server. The database isn't huge, it might all
be in cache for all I know, but I didn't expect all Read
values to come back as zero!
Profiler seems to suggest that these are *logical* reads,
so even if it were all in cache, would that explain the
numbers, or is there something else wedged?
(Just to be clear, the column is present, but all values
are zero).
Thanks for any wisdom or advice.
J.Which events are you trapping in the profiler trace output? Not all profiler
events trap all data columns.
For example, the TSQL\SQL:StmtCompleted event captures the reads data
column, but the TSQL\SQL:StmtStarting does not.
You need to check the event class / data column matrix in the documentation
to see which event classes capture which data columns.
The TSQL class is documented (with the other classes) here:
http://msdn.microsoft.com/library/en-us/adminsql/ad_mon_perf_7zll.asp
HTH
Regards,
Greg Linwood
SQL Server MVP
"jxstern" <jxstern@.wherever.iam> wrote in message
news:0b3001c38233$cc5c9b70$a301280a@.phx.gbl...
> I'm looking at a trace of 100k rows over two hours for a
> production server. The database isn't huge, it might all
> be in cache for all I know, but I didn't expect all Read
> values to come back as zero!
> Profiler seems to suggest that these are *logical* reads,
> so even if it were all in cache, would that explain the
> numbers, or is there something else wedged?
> (Just to be clear, the column is present, but all values
> are zero).
> Thanks for any wisdom or advice.
> J.
>|||On Wed, 24 Sep 2003 11:00:17 +1000, "Greg Linwood"
<g_linwood@.hotmail.com> wrote:
>Which events are you trapping in the profiler trace output? Not all profiler
>events trap all data columns.
>For example, the TSQL\SQL:StmtCompleted event captures the reads data
>column, but the TSQL\SQL:StmtStarting does not.
It's all the eventclass 12's that usually capture reads reliably, as I
recall that's BatchCompleted.
J.
>You need to check the event class / data column matrix in the documentation
>to see which event classes capture which data columns.
>The TSQL class is documented (with the other classes) here:
>http://msdn.microsoft.com/library/en-us/adminsql/ad_mon_perf_7zll.asp
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"jxstern" <jxstern@.wherever.iam> wrote in message
>news:0b3001c38233$cc5c9b70$a301280a@.phx.gbl...
>> I'm looking at a trace of 100k rows over two hours for a
>> production server. The database isn't huge, it might all
>> be in cache for all I know, but I didn't expect all Read
>> values to come back as zero!
>> Profiler seems to suggest that these are *logical* reads,
>> so even if it were all in cache, would that explain the
>> numbers, or is there something else wedged?
>> (Just to be clear, the column is present, but all values
>> are zero).
>> Thanks for any wisdom or advice.
>> J.
>|||When you say:
<snip> It's all the eventclass 12's that usually capture reads reliably, as
I recall that's BatchCompleted.</snip>
Are you saying that BatchCompleted is the event that is returning read
values reliably (non zero), or is it some other event that's the returning
zeros?
Regards,
Greg Linwood
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:c272nvc8c461ibc0mrgvh9l3vmg08p69sa@.4ax.com...
> On Wed, 24 Sep 2003 11:00:17 +1000, "Greg Linwood"
> <g_linwood@.hotmail.com> wrote:
> >Which events are you trapping in the profiler trace output? Not all
profiler
> >events trap all data columns.
> >
> >For example, the TSQL\SQL:StmtCompleted event captures the reads data
> >column, but the TSQL\SQL:StmtStarting does not.
> It's all the eventclass 12's that usually capture reads reliably, as I
> recall that's BatchCompleted.
> J.
>
> >
> >You need to check the event class / data column matrix in the
documentation
> >to see which event classes capture which data columns.
> >
> >The TSQL class is documented (with the other classes) here:
> >http://msdn.microsoft.com/library/en-us/adminsql/ad_mon_perf_7zll.asp
> >
> >HTH
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"jxstern" <jxstern@.wherever.iam> wrote in message
> >news:0b3001c38233$cc5c9b70$a301280a@.phx.gbl...
> >> I'm looking at a trace of 100k rows over two hours for a
> >> production server. The database isn't huge, it might all
> >> be in cache for all I know, but I didn't expect all Read
> >> values to come back as zero!
> >>
> >> Profiler seems to suggest that these are *logical* reads,
> >> so even if it were all in cache, would that explain the
> >> numbers, or is there something else wedged?
> >>
> >> (Just to be clear, the column is present, but all values
> >> are zero).
> >>
> >> Thanks for any wisdom or advice.
> >>
> >> J.
> >>
> >
>|||On Wed, 24 Sep 2003 14:54:16 +1000, "Greg Linwood"
<g_linwood@.hotmail.com> wrote:
>When you say:
><snip> It's all the eventclass 12's that usually capture reads reliably, as
>I recall that's BatchCompleted.</snip>
>Are you saying that BatchCompleted is the event that is returning read
>values reliably (non zero), or is it some other event that's the returning
>zeros?
I generally look only at the 12's, and all BatchCompleted events are
coming up zero in this trace.
In fact, *all* standard events are coming up straight zeros.
J.

Thursday, February 9, 2012

All Inserts/Updates halted for a 1 minute time period. Looking for a reason why.

We are running SQL 2000 SP3. In looking at a SQL Trace, I noticed that for
a 1 minute time period - there were SQL statements that all finished within
about 10 milliseconds of each other. I immediately suspected blocking as
the culprit but I am 100% sure that blocking was not the cause (I had a
"blocker script" running continuously looking for blocking and nothing
showed up).
When I looked a little closer - I noticed that ALL of these 8 SQL statements
were either Inserting or Updating. All 8 SQL statements were each touching
different tables and were in very small units of work. In looking at the
READS, WRITES and CPU for each of these statements they were all very low.
Again - I have to emphasize there was no database blocking involved.
Furthermore - during this time period I was able to determine that of all
the ~ 6200 SQL statements that were started, these 8 SQL statements were the
only ones updating. All other SL statements were reading only.
This means that for a one-minute time period all Update/Insert activity was
"halted" (without database blocking occurring). Does anyone have an
explanation as to why this could have occurred? Could it be a problem with
cache or transaction log backup?
Also - wanted to indicate that the transaction log backup was not
interfering with this time period. I checked msdb.backupset and I can see
that this questionable time period did not coincide with any t-log backup.
Looking for some advice... Maybe there are some pertainant Perfmon
counters that I could enable?
Thanks in advance!Hi
If you were using sp_blocker_pss80 you may want to decrease the time in your
delay between calls.
You may also want to trace lock escalations, timeouts and deadlocks in
profiler along with events in the audit category such as backup/restore and
DBCC events. Also check out any file expansions in performance monitor.
John
"TJT" wrote:
> We are running SQL 2000 SP3. In looking at a SQL Trace, I noticed that for
> a 1 minute time period - there were SQL statements that all finished within
> about 10 milliseconds of each other. I immediately suspected blocking as
> the culprit but I am 100% sure that blocking was not the cause (I had a
> "blocker script" running continuously looking for blocking and nothing
> showed up).
> When I looked a little closer - I noticed that ALL of these 8 SQL statements
> were either Inserting or Updating. All 8 SQL statements were each touching
> different tables and were in very small units of work. In looking at the
> READS, WRITES and CPU for each of these statements they were all very low.
> Again - I have to emphasize there was no database blocking involved.
> Furthermore - during this time period I was able to determine that of all
> the ~ 6200 SQL statements that were started, these 8 SQL statements were the
> only ones updating. All other SL statements were reading only.
> This means that for a one-minute time period all Update/Insert activity was
> "halted" (without database blocking occurring). Does anyone have an
> explanation as to why this could have occurred? Could it be a problem with
> cache or transaction log backup?
> Also - wanted to indicate that the transaction log backup was not
> interfering with this time period. I checked msdb.backupset and I can see
> that this questionable time period did not coincide with any t-log backup.
> Looking for some advice... Maybe there are some pertainant Perfmon
> counters that I could enable?
> Thanks in advance!
>
>