caching problem

Hi all gurus
I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
ram , intel xeon 8 processor machine.
We have 2 files file1 with 15,000 query and file2 with 60,000 query.
if run file2 alone it takes first 6 mins and if i run it second time
it takes 2 mins.
so this time it uses caches and returns result in 2 mins
So its taking benefit of cache.
file2 first 5 min
file2 second 2 min
But when i run file1 it takes 2 minutes and file2 takes 6 mins.
After then again if i run file1 again it takes again 2 mins and file2
takes 5 mins.
memory goes to 10 GB from 16 GB.
file1 first 2 min
file2 first 5 min
file1 second 2 min
file2 second 5 min
here second time when i run this files it doesn't take benefit of
cache and take same time
so why when i run both files sequentially 2 times 2nd time its not
taking benefit of caching even 6 GB of memory available.If there is 6
GB memory available then it should keep all data in cache and give
fast results second time.
Is there any limitation for caching or query plan? running 2 different
files will drop cache?What exactly do you mean by files and 15,000 query? And if sql server is
using all 16GB then you have a problem with your configuration. You need to
set a MAX Memory setting of no more than 14GB to leave room for the OS to do
it's job.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> Hi all gurus
> I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> ram , intel xeon 8 processor machine.
> We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>
> if run file2 alone it takes first 6 mins and if i run it second time
> it takes 2 mins.
> so this time it uses caches and returns result in 2 mins
> So its taking benefit of cache.
> file2 first 5 min
> file2 second 2 min
> But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> After then again if i run file1 again it takes again 2 mins and file2
> takes 5 mins.
> memory goes to 10 GB from 16 GB.
> file1 first 2 min
> file2 first 5 min
> file1 second 2 min
> file2 second 5 min
> here second time when i run this files it doesn't take benefit of
> cache and take same time
> so why when i run both files sequentially 2 times 2nd time its not
> taking benefit of caching even 6 GB of memory available.If there is 6
> GB memory available then it should keep all data in cache and give
> fast results second time.
> Is there any limitation for caching or query plan? running 2 different
> files will drop cache?|||On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> What exactly do you mean by files and 15,000 query? And if sql server is
> using all 16GB then you have a problem with your configuration. You need to
> set a MAX Memory setting of no more than 14GB to leave room for the OS to do
> it's job.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <shahdha...@.gmail.com> wrote in message
> news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> > Hi all gurus
> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> > ram , intel xeon 8 processor machine.
> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
> > if run file2 alone it takes first 6 mins and if i run it second time
> > it takes 2 mins.
> > so this time it uses caches and returns result in 2 mins
> > So its taking benefit of cache.
> > file2 first 5 min
> > file2 second 2 min
> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> > After then again if i run file1 again it takes again 2 mins and file2
> > takes 5 mins.
> > memory goes to 10 GB from 16 GB.
> > file1 first 2 min
> > file2 first 5 min
> > file1 second 2 min
> > file2 second 5 min
> > here second time when i run this files it doesn't take benefit of
> > cache and take same time
> > so why when i run both files sequentially 2 times 2nd time its not
> > taking benefit of caching even 6 GB of memory available.If there is 6
> > GB memory available then it should keep all data in cache and give
> > fast results second time.
> > Is there any limitation for caching or query plan? running 2 different
> > files will drop cache?
Hi Andrew
there are about 15000 select queries in first file and 60000 select
queries in second file. And MS is not using more than 10 GB.
Problem is that if i run only one file 2 times then at second time is
uses cache, but if I run file sequentially 2 time then its not using
cache.
why its doing so?|||60K selects? Wow that is one heck of a report. You said initially that
the memory went from 10GB to 16GB and now you are saying it is not using
more than 10GB. How are you determining this? What I said memory wise is
still true regardless and should be changed if not that way now. The
scenario you use is in no way proof that the cache is not being used. There
are many things that can cause this behavior. Poorly executed parallel plans
or blocking for instance can easily account for these numbers. Did you check
for blocking? Did you look at the file stats to see how much physical I/O
you are doing? And how large is the database itself?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
> On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
>> What exactly do you mean by files and 15,000 query? And if sql server
>> is
>> using all 16GB then you have a problem with your configuration. You need
>> to
>> set a MAX Memory setting of no more than 14GB to leave room for the OS to
>> do
>> it's job.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>> <shahdha...@.gmail.com> wrote in message
>> news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
>> > Hi all gurus
>> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
>> > ram , intel xeon 8 processor machine.
>> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>> > if run file2 alone it takes first 6 mins and if i run it second time
>> > it takes 2 mins.
>> > so this time it uses caches and returns result in 2 mins
>> > So its taking benefit of cache.
>> > file2 first 5 min
>> > file2 second 2 min
>> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
>> > After then again if i run file1 again it takes again 2 mins and file2
>> > takes 5 mins.
>> > memory goes to 10 GB from 16 GB.
>> > file1 first 2 min
>> > file2 first 5 min
>> > file1 second 2 min
>> > file2 second 5 min
>> > here second time when i run this files it doesn't take benefit of
>> > cache and take same time
>> > so why when i run both files sequentially 2 times 2nd time its not
>> > taking benefit of caching even 6 GB of memory available.If there is 6
>> > GB memory available then it should keep all data in cache and give
>> > fast results second time.
>> > Is there any limitation for caching or query plan? running 2 different
>> > files will drop cache?
> Hi Andrew
> there are about 15000 select queries in first file and 60000 select
> queries in second file. And MS is not using more than 10 GB.
> Problem is that if i run only one file 2 times then at second time is
> uses cache, but if I run file sequentially 2 time then its not using
> cache.
> why its doing so?|||On Jan 11, 12:22 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> 60K selects? Wow that is one heck of a report. You said initially that
> the memory went from 10GB to 16GB and now you are saying it is not using
> more than 10GB. How are you determining this? What I said memory wise is
> still true regardless and should be changed if not that way now. The
> scenario you use is in no way proof that the cache is not being used. There
> are many things that can cause this behavior. Poorly executed parallel plans
> or blocking for instance can easily account for these numbers. Did you check
> for blocking? Did you look at the file stats to see how much physical I/O
> you are doing? And how large is the database itself?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <shahdha...@.gmail.com> wrote in message
> news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
> > On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> > wrote:
> >> What exactly do you mean by files and 15,000 query? And if sql server
> >> is
> >> using all 16GB then you have a problem with your configuration. You need
> >> to
> >> set a MAX Memory setting of no more than 14GB to leave room for the OS to
> >> do
> >> it's job.
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >> <shahdha...@.gmail.com> wrote in message
> >>news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> >> > Hi all gurus
> >> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> >> > ram , intel xeon 8 processor machine.
> >> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
> >> > if run file2 alone it takes first 6 mins and if i run it second time
> >> > it takes 2 mins.
> >> > so this time it uses caches and returns result in 2 mins
> >> > So its taking benefit of cache.
> >> > file2 first 5 min
> >> > file2 second 2 min
> >> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> >> > After then again if i run file1 again it takes again 2 mins and file2
> >> > takes 5 mins.
> >> > memory goes to 10 GB from 16 GB.
> >> > file1 first 2 min
> >> > file2 first 5 min
> >> > file1 second 2 min
> >> > file2 second 5 min
> >> > here second time when i run this files it doesn't take benefit of
> >> > cache and take same time
> >> > so why when i run both files sequentially 2 times 2nd time its not
> >> > taking benefit of caching even 6 GB of memory available.If there is 6
> >> > GB memory available then it should keep all data in cache and give
> >> > fast results second time.
> >> > Is there any limitation for caching or query plan? running 2 different
> >> > files will drop cache?
> > Hi Andrew
> > there are about 15000 select queries in first file and 60000 select
> > queries in second file. And MS is not using more than 10 GB.
> > Problem is that if i run only one file 2 times then at second time is
> > uses cache, but if I run file sequentially 2 time then its not using
> > cache.
> > why its doing so?
sorry there is a typo. from 16 gb memory 10 gb is used. so i think if
6 gb is available , I got this info from task manager it showes 10 gb
uses, then in second case when 2 files are sequentially run 2 times
then why its not using cache 2nd times?|||Task manager does not tell you how much memory SQL Server is really using
you should use the Perfmon counters for that. What is the Min and Max
memory set to in SQL Server? And regardless that does not tell you if the
data is cached or not. There are too many other factors that can be
involved. Until you get the actual metrics that I mentioned you are just
guessing and probably barking up the wrong tree.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:6b8e2894-1ccf-4b5c-9a3c-e6ed7c3208c7@.j78g2000hsd.googlegroups.com...
> On Jan 11, 12:22 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
>> 60K selects? Wow that is one heck of a report. You said initially that
>> the memory went from 10GB to 16GB and now you are saying it is not using
>> more than 10GB. How are you determining this? What I said memory wise
>> is
>> still true regardless and should be changed if not that way now. The
>> scenario you use is in no way proof that the cache is not being used.
>> There
>> are many things that can cause this behavior. Poorly executed parallel
>> plans
>> or blocking for instance can easily account for these numbers. Did you
>> check
>> for blocking? Did you look at the file stats to see how much physical
>> I/O
>> you are doing? And how large is the database itself?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>> <shahdha...@.gmail.com> wrote in message
>> news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
>> > On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
>> > wrote:
>> >> What exactly do you mean by files and 15,000 query? And if sql
>> >> server
>> >> is
>> >> using all 16GB then you have a problem with your configuration. You
>> >> need
>> >> to
>> >> set a MAX Memory setting of no more than 14GB to leave room for the OS
>> >> to
>> >> do
>> >> it's job.
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >> <shahdha...@.gmail.com> wrote in message
>> >>news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
>> >> > Hi all gurus
>> >> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
>> >> > ram , intel xeon 8 processor machine.
>> >> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>> >> > if run file2 alone it takes first 6 mins and if i run it second time
>> >> > it takes 2 mins.
>> >> > so this time it uses caches and returns result in 2 mins
>> >> > So its taking benefit of cache.
>> >> > file2 first 5 min
>> >> > file2 second 2 min
>> >> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
>> >> > After then again if i run file1 again it takes again 2 mins and
>> >> > file2
>> >> > takes 5 mins.
>> >> > memory goes to 10 GB from 16 GB.
>> >> > file1 first 2 min
>> >> > file2 first 5 min
>> >> > file1 second 2 min
>> >> > file2 second 5 min
>> >> > here second time when i run this files it doesn't take benefit of
>> >> > cache and take same time
>> >> > so why when i run both files sequentially 2 times 2nd time its not
>> >> > taking benefit of caching even 6 GB of memory available.If there is
>> >> > 6
>> >> > GB memory available then it should keep all data in cache and give
>> >> > fast results second time.
>> >> > Is there any limitation for caching or query plan? running 2
>> >> > different
>> >> > files will drop cache?
>> > Hi Andrew
>> > there are about 15000 select queries in first file and 60000 select
>> > queries in second file. And MS is not using more than 10 GB.
>> > Problem is that if i run only one file 2 times then at second time is
>> > uses cache, but if I run file sequentially 2 time then its not using
>> > cache.
>> > why its doing so?
> sorry there is a typo. from 16 gb memory 10 gb is used. so i think if
> 6 gb is available , I got this info from task manager it showes 10 gb
> uses, then in second case when 2 files are sequentially run 2 times
> then why its not using cache 2nd times?
>