Showing posts from July, 2011

Improving Query performance in SQL Server

Recently started learning more about performance related stuff in SQL server. Man oh man!!! so many things to learn and so many things to blog about.... Let us start with the basics:
Writing a SQL query you need to measure a couple of things:
1. Query Cost
2. CPU Utilization
3. Time taken for the Query to execute
4. Table scans, Index scans and Index seeks.
Let us start by focusing on Query cost--->
Write the command- SET SHOWPLAN ON;
and then execute a SQL for example-->
SELECT E.EmployeeID,E.EmployeeStatus FROM Employee E where E.AddressID = 112345 ORDER BY E.EmployeeID
This command will basically give us the Query analysis built into the SQL server. The first aspect you will notice is the Query cost... In this case if there are indexes on the underlying table then we can state that the query cost might be less provided the index is being used else the query cost is going to increase significantly. Also we can minimize query cost by inclusing more filters in our SQL query provided that…