Performance Testing ( Turn compile cache off ? ) 
Author Message
 Performance Testing ( Turn compile cache off ? )

I have a sql statement that takes 4 minutes to return 89,000 rows. I want to
speed this up and am using the index tuning wizard to help me.

Problem is that if you run a command it takes say.. 10 seconds to return, if
you now run it again it will be much quicker, because the statement has been
compiled and cached. This of course doesn't help me as I do not know if I
have actually improved the indexing on the tables involved in the query or
its just the fact that the query is cached.

Is there any way of disabling the cache to force it to recompile the
statement every time, thus giving me a true indication of improvements ?

thanks

Mark



Sun, 08 Aug 2004 19:04:47 GMT
 Performance Testing ( Turn compile cache off ? )

Mark, try these undocumented commands:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Quote:
> I have a sql statement that takes 4 minutes to return 89,000 rows. I want
to
> speed this up and am using the index tuning wizard to help me.

> Problem is that if you run a command it takes say.. 10 seconds to return,
if
> you now run it again it will be much quicker, because the statement has
been
> compiled and cached. This of course doesn't help me as I do not know if I
> have actually improved the indexing on the tables involved in the query or
> its just the fact that the query is cached.

> Is there any way of disabling the cache to force it to recompile the
> statement every time, thus giving me a true indication of improvements ?

> thanks

> Mark



Sun, 08 Aug 2004 19:11:51 GMT
 Performance Testing ( Turn compile cache off ? )
Thanks, that does the trick..



Quote:
> Mark, try these undocumented commands:
> DBCC FREEPROCCACHE
> DBCC DROPCLEANBUFFERS
> --
> HTH,
> Vyas, MVP (SQL Server)

> http://vyaskn.tripod.com/



> > I have a sql statement that takes 4 minutes to return 89,000 rows. I
want
> to
> > speed this up and am using the index tuning wizard to help me.

> > Problem is that if you run a command it takes say.. 10 seconds to
return,
> if
> > you now run it again it will be much quicker, because the statement has
> been
> > compiled and cached. This of course doesn't help me as I do not know if
I
> > have actually improved the indexing on the tables involved in the query
or
> > its just the fact that the query is cached.

> > Is there any way of disabling the cache to force it to recompile the
> > statement every time, thus giving me a true indication of improvements ?

> > thanks

> > Mark



Sun, 08 Aug 2004 19:24:08 GMT
 Performance Testing ( Turn compile cache off ? )
Vyas,

I believe it was Dinesh who pointed it out to me that both of these are
documented in the SQL Server 2000 BOL     :-)

BPM



Quote:
> Mark, try these undocumented commands:
> DBCC FREEPROCCACHE
> DBCC DROPCLEANBUFFERS
> --
> HTH,
> Vyas, MVP (SQL Server)

> http://vyaskn.tripod.com/



> > I have a sql statement that takes 4 minutes to return 89,000 rows. I
want
> to
> > speed this up and am using the index tuning wizard to help me.

> > Problem is that if you run a command it takes say.. 10 seconds to
return,
> if
> > you now run it again it will be much quicker, because the statement has
> been
> > compiled and cached. This of course doesn't help me as I do not know if
I
> > have actually improved the indexing on the tables involved in the query
or
> > its just the fact that the query is cached.

> > Is there any way of disabling the cache to force it to recompile the
> > statement every time, thus giving me a true indication of improvements ?

> > thanks

> > Mark



Mon, 09 Aug 2004 09:38:32 GMT
 Performance Testing ( Turn compile cache off ? )
That's correct BP. Just verified it. They are undocumented only for 7.0 :-)
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Vyas,

I believe it was Dinesh who pointed it out to me that both of these are
documented in the SQL Server 2000 BOL     :-)

BPM



Quote:
> Mark, try these undocumented commands:
> DBCC FREEPROCCACHE
> DBCC DROPCLEANBUFFERS
> --
> HTH,
> Vyas, MVP (SQL Server)

> http://vyaskn.tripod.com/



> > I have a sql statement that takes 4 minutes to return 89,000 rows. I
want
> to
> > speed this up and am using the index tuning wizard to help me.

> > Problem is that if you run a command it takes say.. 10 seconds to
return,
> if
> > you now run it again it will be much quicker, because the statement has
> been
> > compiled and cached. This of course doesn't help me as I do not know if
I
> > have actually improved the indexing on the tables involved in the query
or
> > its just the fact that the query is cached.

> > Is there any way of disabling the cache to force it to recompile the
> > statement every time, thus giving me a true indication of improvements ?

> > thanks

> > Mark



Mon, 09 Aug 2004 15:35:40 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. SQL Server & turning RAID Cache Off

2. Possible to turn off file system cache?

3. Possible to turn off file system cache?

4. Can I turn OFF read cache?

5. Turning cache off on a Seagate drive under AIX - Progress/AIX/Seagate don't mix

6. Turning off cache for a single sql-query

7. Turning Off the PTS Cache

8. Possible to turn off file system cache?

9. turn write-to-cache off

10. How to: Turn OFF performance monitor logging

11. Turn it off, turn it on

12. 20% off OCP Exams, 50% off Self Test, 70% off Oracle8i Training, Oracle10G info and more


 
Powered by phpBB® Forum Software