MS SQL Server, Performance Testing ( Turn compile cache off ? )

Performance Testing ( Turn compile cache off ? )

Author	Message
Mark Craf #1 / 5	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

Narayana Vyas Kondredd #2 / 5	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

Mark Craf #3 / 5	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

BP Margoli #4 / 5	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

Narayana Vyas Kondredd #5 / 5	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

Page 1 of 1

[ 5 post ]