What you need to know before starting to make query performance improvements in SQL Server?

PROBLEM:
What you need to know before starting to make query performance improvements in SQL Server?

SOLUTION:
To perform actions for performance improvements in SQL Server you need to understand the use of the following 3 statements and their scope before you are using them:
1. Use CHECKPOINT to forces all dirty pages for the current database to be written to disk and clean the buffers. The database engine performs modifications to database pages in memory (in the buffer cache) and does not write these pages to disk after every change. Rather, the database engine periodically issues a CHECKPOINT on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.
2. Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the cache remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.
3. Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed. To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache.

CHECKPOINT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

Scope - The CHECKPOINT is database-specific, the FREEPROCCACHE  and DROPCLEANBUFFERS drops every single clean page from the entire database data cache. By executing them all sessions of all users in the database are effected, so if you perform it in a shared "performance database" it affects the other users that are currently connected.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!