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.
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.