|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DROPCLEANBUFFERS/FREEPROCCACHE
I've been doing some stored proc tuning and I want to ensure that after the
changes I make to the query the execution times are accurate. So basically, if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other users, am I clearing ALL buffers/proccache that SQL is using, or just the ones tied to my connection? Everything for the server.
Show quoteHide quote "mrdj" <m***@discussions.microsoft.com> wrote in message news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@microsoft.com... > I've been doing some stored proc tuning and I want to ensure that after the > changes I make to the query the execution times are accurate. So basically, > if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other > users, am I clearing ALL buffers/proccache that SQL is using, or just the > ones tied to my connection? There is no need to issue FREEPROCCACHE as each time you edit the sp it will
invalidate the plan already in cache anyway. DropCleanBuffers is data related and has nothing to do with the sp execution plan chosen. -- Show quoteHide quoteAndrew J. Kelly SQL MVP "mrdj" <m***@discussions.microsoft.com> wrote in message news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@microsoft.com... > I've been doing some stored proc tuning and I want to ensure that after > the > changes I make to the query the execution times are accurate. So > basically, > if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other > users, am I clearing ALL buffers/proccache that SQL is using, or just the > ones tied to my connection? well, i thought so about DROPCLEANBUFFERS but the proc ran quicker (without
change) every time I ran it after the first time. So SQL must have been acessing the data from the buffer? Thanks for your quick response. Show quoteHide quote "Andrew J. Kelly" wrote: > There is no need to issue FREEPROCCACHE as each time you edit the sp it will > invalidate the plan already in cache anyway. DropCleanBuffers is data > related and has nothing to do with the sp execution plan chosen. > > -- > Andrew J. Kelly SQL MVP > > > "mrdj" <m***@discussions.microsoft.com> wrote in message > news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@microsoft.com... > > I've been doing some stored proc tuning and I want to ensure that after > > the > > changes I make to the query the execution times are accurate. So > > basically, > > if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other > > users, am I clearing ALL buffers/proccache that SQL is using, or just the > > ones tied to my connection? > > > "mrdj" <m***@discussions.microsoft.com> wrote in message Andrew may not agree with the method, but I personally always do anews:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@microsoft.com... > well, i thought so about DROPCLEANBUFFERS but the proc ran quicker (without > change) every time I ran it after the first time. So SQL must have been > acessing the data from the buffer? Thanks for your quick response. CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing stored procedure changes for single-run performance -- I want to work with a totally clean slate. Of course, a single-run doesn't tell you much; you might instead want to try running it 10 or 20 times and taking the average. I've found this gives much more interesting results. Sometimes a single run can be skewed by something else on the server running (or not running, thereby making you think you've done better than you really have). If you run it several times and take an average, that's less likely to affect your results. It is not that I don't agree with doing that but it is important to
understand what happens when you do that and how it may or may not affect your results. But it's rare any more that I do a single test like that since it rarely mimics real life conditions on busy systems. I can usually tell more from the query plan than anything else. But That does not mean this can not be useful information. -- Show quoteHide quoteAndrew J. Kelly SQL MVP "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:uC9JouXMFHA.2252@TK2MSFTNGP15.phx.gbl... > "mrdj" <m***@discussions.microsoft.com> wrote in message > news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@microsoft.com... >> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker > (without >> change) every time I ran it after the first time. So SQL must have been >> acessing the data from the buffer? Thanks for your quick response. > > Andrew may not agree with the method, but I personally always do a > CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing > stored procedure changes for single-run performance -- I want to work with > a > totally clean slate. > > Of course, a single-run doesn't tell you much; you might instead want > to > try running it 10 or 20 times and taking the average. I've found this > gives > much more interesting results. Sometimes a single run can be skewed by > something else on the server running (or not running, thereby making you > think you've done better than you really have). If you run it several > times > and take an average, that's less likely to affect your results. > > > -- > Adam Machanic > SQL Server MVP > http://www.datamanipulation.net > -- > >  
Other interesting topics
Need help on Effective Reindexing Stragtegy.
Primary File Group Full? SAN issues, please help Select performance is one table, but not another Managing TB size of data Determine cause of Timeouts TRXN LOG BKP time takes so much longer than Full BKP??? Data Migration Recommendations Needed Debug SP not stopping on Breakpoint Help needed with OpenQuery |
|||||||||||||||||||||||
