Search code examples
sql-serverperformanceexecution

Why is my Sql Query is Faster the Second Time it Runs?


Every time I am getting same execution time while executing the SQL query. is there any chance to get same execution time for all the time if SQL query runs multiple times?


Solution

  • When you run your query for the first time and the data is not in cache, the server read the data from disk. It is time-comsuming. The second time you execute the same query data is already in cache so it requires less time.

    is there any chance to get same execution time for all the time if SQL query runs multiple times ?

    If you want to test your queries with the cold cache (with no data cached, every time) you can use DBCC DROPCLEANBUFFERS before your query execution:

    Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. To drop clean buffers from the buffer pool and columnstore objects from the columnstore object pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

    Of course this is not to be used in production environment.

    If conversely you want to have your data always in cache, you should increase RAM for your server and don't restart it as long as possible.