Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-03-14 : 16:13:00
|
| Scott writes "I have a fairly complicated SQL stored procedure which is returning a set of around 20 rows which have to meet a fairly complex criteria (the rows refer to some detail records and certain items must be in the detail records for the item to get picked from the main table. There's also an ordering constraint. When I run the query in Query Analyzer (using "set showplan_all on" and setting the IO Statistics checkbox), I get a total of 305 logical reads. The most complicated part of the stored procedure returns the following:Table '#44AD693B'. Scan count 0, logical reads 17, physical reads 0, read-ahead reads 0.Table '#42C520C9'. Scan count 16, logical reads 16, physical reads 0, read-ahead reads 0.Table '#43B94502'. Scan count 91, logical reads 91, physical reads 0, read-ahead reads 0.Table 'myDetailRecordTable'. Scan count 20, logical reads 60, physical reads 0, read-ahead reads 0.Table 'myMainRecordTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.Given what it has to do, this is probably reasonable. However, when I'm accessing this same query from my application, I get a line in SQL Profiler that says for the event "RPC:Completed" (on the execution of the same sproc), the CPU was 628 milliseconds and the logical reads were 23181!! I should point out that the way I generated the statistics from Query analyzer was to copy the statement text from the SQL Profiler line, add the showplan_all and then execute it with IO Statistics also checked.It looks to me like everything runs fine, but there is a huge descrepancy between the number of logical reads from Query Analyzer and what seems to be on the "live" test machine.Is this caused because the stored procedures need to be recompiled (does Query Analyzer have it's own copy of the compilation and so is not propogated to the normal cache)? Do I need to force statistics to be updated on the tables?Anyone have any explanation or suggestion?" |
|
|
TheTao
Starting Member
1 Post |
Posted - 2006-03-14 : 16:53:07
|
| I misspoke above. I said that I'm doing "set showplan_all". I meant to say "set statistics profile on". The difference is that the latter actually executes the stored procedure |
 |
|
|
|
|
|
|
|
Query Analyzer vs SQL Profile descrepancy