Optimizer Warm-up!

Last Post 16 Apr 2002 04:52 AM by satya. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Irish
New Member
New Member

--
16 Apr 2002 04:41 AM
I'm developing a system which processes a large number of stored procedures. As I run the system, I comment out the stored procedures that work and move on - that way I'm not waiting ages to see if my latest additions work or not. For example, part A, part B, part C
all work, so when I come to develop part D, I comment out A, B & C, allowing SQL to go straight to part D.

What I've found is that if I run parts A, B & C prior to running part D, then D runs in (let's say) 5 mins. But when I comment out parts A, B & C, and go straight to part D, then it takes (let's say) 5 hours to run.

Parts A, B & C would have already created all the tables, indexes, etc, necessary for part D to execute, but why should it take so much longer when processed on it's own?

Does the optimizer need parts A, B & C to warm up?

Thanks,
David.
satya
New Member
New Member

--
16 Apr 2002 04:52 AM
Use SET FORCEPLAN which essentially overrides the logic used by the query optimizer to process a Transact-SQL SELECT statement.

The data returned by the SELECT statement is the same regardless of this setting. The only difference is the way SQL Server processes the tables to satisfy the query.

Query optimizer hints can also be used in queries to affect how SQL Server processes the SELECT statement. The setting of SET FORCEPLAN is set at execute or run time and not at parse time.

Refer to BOL for more information.
Irish
New Member
New Member

--
19 Apr 2002 01:09 AM
Thanks Peter,

I've performed STATS TIME and located the problem query, I've then performed STATS IO to view logical and physical reads, etc, but I'm unable to understand the numbers - can you help?

The table DL_0255_CIssue has 19.7 million rows in it.
The query effected 7.3m of those rows.

The IO results are...
Table DL_0255_CIssue. Scan count 1, logical reads 7424348, physical reads 3981521, read-ahead reads 104794
Table DL_0255_Products. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 5

I've been referring to Kalen Delaney's 'Inside Microsoft SQL Server 2000' book, chapter 16 - 'Query Tuning', which gives the meaning of these items - but how should they be interpreted? Is there a critical threshold value of some sort - Kalen makes reference to a 'Cache-Hit ratio' on page 890 - but what value of ratio is too high? The numbers for DL_0255_CIssue look big, but are they acceptable because of the size of the table?

Thanks in advance.
David.
You are not authorized to post a reply.

Acceptable Use Policy