July 29, 2008 at 12:23 pm
Hi,
My concept is (correct me if i m wrong)
When we run a query
1- sql server generates its Execution Plan
2- Brings the data in memory
When we run the same query again just immediately
1- sql server will use the already built execution plan
2- sql server will use the data already present in the memory
3- this will result in less cpu, read, write & duration as compared to first time run of the query
If my concept about reusing of Plan & Data is correct than tell me how can i force sql server to always generate a plan and always go to disk for data?
I want this to happen because i want to compare performance of the query before & after some tunning modifications (e.g separting data & log files on different disk etc etc)
Thanks,
usman
July 29, 2008 at 12:32 pm
For testing, and for testing only, don't do this in Production, I'll run DBCC FREEPROCCACHE to clean out all the procedures and force recompiles. Then try running CHECKPOINT followed by DBCC DROPCLEANBUFFERS. I generally don't bother with the last one unless I'm trying to test disk I/O. Usually all I care about is compile time and execution time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2008 at 12:37 pm
Thanks, yes i have to do it on test machines only.
As you said that you usually are concerend with compile & execution time, so Is ignoring the read/writes a good habbit? Don't we ALWAYS have to consider the disk IO along with the duration?
This is jus for my curiosity.
Thanks,
July 29, 2008 at 12:51 pm
It really depends on the system you're working on. Most of the systems I have experience with have a pretty common set of data in cache the majority of the time. Certainly not all the time, but enough that sweating the occasional I/O isn't worth worrying about. That said, reads and scans really matter. Just because memory is faster doesn't mean that 50,000 reads is a good thing. So you still want to look at the I/O returned from a query, especially while you're tuning it, but the cost of loading data to cache, on most systems, isn't the principal point of performance problems (although it certainly can be).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply