asked
dbcc commands
how can I speed up an query execution as a sql server administrator. I dont want to make any changes in the query, but I need to make it right by using dbcc commands. So please help to get the procedures to clear cache and other tips.. thanks in advance..
Clearing the cache will not speed up your query, it will most likely slow your entire system down. DBCC commands are not designed for direct performance tuning, rather to do troubleshooting. If you want your query to run faster, you need to look at the execution plan, system resource usage, table and index structures and the query itself.
I've learned something new today. DBCC = Database Console Command.
Dear William, I know both execution plans and data buffers are stored in cache and once we clear this cache it stars fresh. If SQL server continues its service for a long time with out stopping/restarting then it should increase the size of cache in memory, right?. I used the above idea from this situation and if we cleared this cache it takes more time for to execute first time only right?. Thats why I used this idea. Please correct me if I am wrong.. Thanks in advance..
Clearing the cache only frees up RAM that has to eventually be re-populated. This will requires actual disk access which is an order of magnitude slower than memory. This will result in slower queries until the cache is rebuilt. Also, if you haven't changed your query the same execution plan will most likely be generated and placed in cache. There are a myriad of things to check before you get to this. Start with updating your statistics and see if that helps your performance. Make sure you have the supporting indexes in place for the query. Examine the execution plan and figure out what the query is doing. What is your concern with working on the query itself? And use the "add new comment" feature to respond - not a new answer please.
