June 21, 2016 at 2:54 am
Does anybody know how the row count in the execution logs is worked out?
I have a report that is made up of a series of datasets obtained from a collection of stored procs. Most of the datasets are only likely to produce row counts in the low single figures and at most the low double figures. One of them is much more open ended but in normal usage shouldn't produce more than 150 rows. These datasets are pulled together into document that comprises various tables and text boxes that are sent to the police as required. The whole point is for concision and clarity so we don't want to provide huge amounts of raw data.
I've been monitoring our report server to try and track an intermittent slowdown and last night there was spike in CXPACKET waits so I had a look at my report usage report(!) and the report that looked like it caused the spike allegedly returned over 600000 rows. This didn't look right so I ran all the individual procs using the same parameters as the report was run with and none of them returned more than 100 rows. The only explanation I can come up with is that the row count on the execution log is calculated by something like Dataset1 x Dataset2 x Dataset3 etc. Can anybody shed any light on this please?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 21, 2016 at 6:50 am
If you have 3 datasets embedded in your report and they each return 100 rows then the row count in the execution log should display 300. If the datasets are shared then you will see them as separate entries in the execution log with their own row counts.
Remember the datasets could return 6000 rows but the report only display 1 row due to filters and groupings. I would double check the stored procedures to ensure there is nothing that could return more rows than expected.
June 21, 2016 at 7:18 am
FridayNightGiant (6/21/2016)
If you have 3 datasets embedded in your report and they each return 100 rows then the row count in the execution log should display 300. If the datasets are shared then you will see them as separate entries in the execution log with their own row counts.Remember the datasets could return 6000 rows but the report only display 1 row due to filters and groupings. I would double check the stored procedures to ensure there is nothing that could return more rows than expected.
The first thing I did was go through the procs to make sure they were all returning what they should be and they were all behaving. There is a bit of witchcraft done to remove duplicates and create legible sentences but nothing that should hide 600000 rows.
I see what you mean about the row count showing the sum of the datatsets but even allowing for that it seems extremely high. It looks more like a Cartesian product of the row counts. I can see how that could get high quickly, particularly if there's a couple of datasets returning 100 + rows.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply