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
set statistics io on select a.[LoginID],a.BirthDate from [HumanResources].[Employee]as a,[HumanResources].[EmployeeDepartmentHistory] as b where a.BusinessEntityID=b.BusinessEntityID and b.ModifiedDate= (select Max(ModifiedDate) from [HumanResources].[EmployeeDepartmentHistory] as c where a.BusinessEntityID=c.BusinessEntityID)
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