As a DBAs we get lot of request to tune queries and we normally suggest to create Index or Update Statistics but tuning also needs rewriting of actual query and the normally performance Issue is caused by lot of Nested SQL Server Subquery.

So here is a demo what I want to write, soon ill update some more tips related to rewrite your actual query to improve performance.

This is the demo script which I need to tune as a performance tuning DBA

If you will look into below screenshot


Scan count for EmployeeDepartmentHistory is 2,although Logical reads are 8 only as it is very small table .Scanning table two times can impact your performance when table would be of the size of 400-500 Gb

Scan count is 2 because above mentioned table is getting used 2 times in the query,    1st in outer query and then in sub query

We can use Concept of Analytical Function here   which will reduce the scan count to 1


I have reduced scan count to 1 but as you can see Worktable which is temp table actually got created so it increased number of logical reads so it would be useful when you will  have tables of large size ,in this scenario also I am comparing cost of above two query. I will inform you about avoiding usage of temp table in my future blogs so here I will not focus on Worktable .

Cost for first Query


Cost for 2nd query


You would be surprised to see even I have more logical reads in my 2nd query still cost is less, because cost is mainly dependent on Physical IO operations or the data present in Physical disk, once you have fetched data from disk it would be easy for Query optimizer or relational Engine to manipulate is as required on memory, that’s why we prefer hash and Merge algorithm for joining two tables instead of nested loops

Ill update more tuning tips in my next blog, till then keep reading



Akash Gautam

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook