One of the interesting if barely announced features of SQL 2005 was a change
to Profiler that lets you capture the number of rows returned by a statement.
Easy enough to use, but why should you? Let's take a quick look today at how it
works and how you might put it good use.
With Profiler open, go to the Trace Properties, then check the box for show
all columns (shown below). That will expand the number of possible columns that
you can select per event and just as with the standard columns, they return data
only if appropriate to that event.
If we scroll to the right we can see the RowCounts column, and you can see
below that I've circled both the column where I've checked it for a common set
of events, and circled the built in description of it a little further down.
Now let's run the trace and see what we get in a few different scenarios. For
my first test I did a 'Use Adventureworks' which returned zero rows, and for the
second test I used a Top 10 in my select and that shows a correct row count of
10. So far so good!
Here's a good test, what happens if you do a count? Does it return the rows
counted, or the rows returned? Below you can see it returns one row, good!
What about a union all? I ran two selects unioned to verify they returned 20
rows, and again - our results match.
To finish up I wanted to also see what a stored procedure did, so I
created one that returned all the rows from the Adventureworks Person.Contact
table:
So in at least all my test cases RowCount returned what I would consider the
expected result. Did I expect it not too? Not really, but it's useful to check
the behavior of a tool - especially one you measure with - before deciding if
and when to make use of it.
Which brings us to the final part of our discussion, what is it useful for?
I'm a proponent of proactive tuning, looking for items that have increased in
cost due to more data, more users, or just design changes in the application,
and I think RowCount fits into that process well. Consider profiling for a
couple hours each month while you capture rowcount, then analyze the data. For
example, in my examples above my last query returns almost 20,000 rows. Is that
bad? Well, it's not good! Not every query that returns a lot of rows needs to be
fixed or can be fixed, but in many cases it can lead you back to tightening up
the where clause and if nothing else, makes you pay a little more attention to a
query that can be doing a significant amount of work.
I blog frequently at
http://blogs.sqlservercentral.com/andy_warren/default.aspx, come visit!