interpreting "set statistics" results

i ran
"set statistics io on"
1)
on a proc execution. it ran in 11 minutes and gave 1000+ lines for the 'Messages' tab for the IO stats.; the results tab gave 100 records.
returned with approx 10 lines for each row of output in the messages tab for the IO)
2)
on another proc, it ran 2 minutes, but produced 35000+ lines  for the 'Messages' tab for the IO stats. the results tab gave around 95 records
from the proc

the below is typical in the IO readout for the proc that ran 11 minutes (relatively minimal IO readout in messages tab):
Table '#239FE47D'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CallCenter'. Scan count 0, logical reads 94, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

the below is typical in the IO readout for the proc that ran 2 minutes (more IO readout in messages tab):

Table '#MsgCtgy________________________________________________________________________________________________________0000000000F5'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CallCenter'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#33D64C46'. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

From the above, does it tell anything about the procs to you before further analysis?

seems like inspites of almost bringing the same # of records from both procs, only did too much IO and still finished faster than the other? is that a fair statement?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
is that a fair statement?

I don't believe so.  Looking at your statistics, I notice the second (faster) method uses a temp table, but the first does not seem as if it does.  In my experience, some queries can be significantly optimized with the use of a temp table, especially if it is indexed correctly.  However, since the temp table is loaded with a subset of data that may be filtered out later, it frequently leads to additional I/O.

Look at it this way.  In general, in SQL Server, you can optimize memory usage, speed, disk I/O, and readability.  You get to choose two to three of these in any complex case.  If you have too much memory usage, then speed tends to suffer.  If you have perfect readability, you are probably not optimized for speed.  If you have minimized disk I/O, then speed tends to suffer.

Notice a pattern here?  Optimize for ANYTHING but speed, and speed suffers. Optimizing for speed tends to make one or more of the other three suffer.  It is just how the SQL Server implementation of a relational database works (and if anyone ever finds a way to break this linkage in another DB, it could take over the database World!  (Insert evil laugh here...)
0
Scott PletcherSenior DBACommented:
You've got far too much output to rationally analyze.

You need to selective turn SET STATISTICS IO on and off before key statements only.  For example:

EXEC proc_name ...normal_parameters, @debug_options = 1 --1=SET STATS IO ON for key statements



CREATE PROC proc_name
    @param1 ...,
    @param2 ...,
    @debug_options int = 0
AS
SET NOCOUNT ON
DECLARE @local_var1 ...
...
SELECT @variable = lookup_value
FROM dbo.lookup_table
WHERE ... = @param1
...other miscellaneous SELECT(s)

IF @debug_options & 1 > 0
    SET STATISTICS IO ON
SELECT
FROM dbo.major_table
INNER JOIN dbo.another_major_table ON ...
WHERE ...
SET STATISTICS IO OFF

...miscellaneous (relatively) minor SELECT(s)

IF @debug_options & 1 > 0
    SET STATISTICS IO ON
SELECT
FROM dbo.big_table
INNER JOIN dbo.another_big_table ON ...
WHERE ...
SET STATISTICS IO OFF

...

etc.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
very helpful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.