How to check actual performance of SQL Query

Guys it is very common that we generate query execution plan to check the performance of our SQL queries, but you know when you generate execution plan it itself take some time to prepare the plan so you don’t know exactly how much is your query time and how much is for execution plan preparation time.

So to check actual query statistics follow few simple steps which will give you actual statistics of your sql query

1.    In SSMS you need to do some setting, go to Tools >> Options  >> Query Execution >> SQL Server >> Advance

It will display several check box option, you need to check SET STATISTICS TIME and SET STATISTICS IO check box, hit OK

2.    Now open a new query window to get first step setting affected

3.    Clear cache before the execution plan, run below script

-- Command to flush the proc out of database

DBCC FLUSHPROCINDB (db_id)

--here db_id you can find by using following query in your database

Select db_id() -- this will return int number that number you can pass as db_id

--2. Clear Cache

DBCC FREEPROCCACHE

--3. Clear Buffers

DBCC DROPCLEANBUFFERS

Check this link Clear cache before generating the execution plan in SQL Server

Now in new query window execute your query and generate execution plan, now it will give you actual IO statistics of your query.

Cheers!!!