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 is what is most required, for example-->
SELECT E.EmployeeID,E.EmployeeStatus FROM Employee E where E.AddressID = 112345 and E.EmployeeID > 443521 and E.JoiningDate=09-17-2010 ORDER BY E.EmployeeID
The query cost will defintely reduce depending upon the size of the rows being extracted from the page.
Also other tips like replacing Outer joins with Inner Joins, Specifying hints, ensuring that there are indexes in place will lead to better Query Cost (Lesser the query cost the better your query has been written). Also ensure that your stats are up to date by using the "sp_updatestatistics".

Now let us consider CPU utilization:-
This is quite an interesting topic and I am currently looking at using stuff like Memcached and Appfabric to enable better CPU utilization. The general concept is that the larger your physical memory the more the query results are cached. And the more the query results cached the faster your results will be displayed and the CPU utilization is fairly less. You can monitor the CPU utilization during execution of your queries by using the following command-->
SET STATISTICS IO ON
Another thing to look at is a distributed database architecture for large OLTP systems where the primary lookups will be hashed(Key pair values). Working on a mini project related to this aspect and am looking forward to people collaborating with me in this respect.

Then we can look at the time -->
ha ha ha..... always time is vital especially when you have customers who do not like taking small breaks in the workplace. No worries. Just follow the same steps as the query cost and we should be in good shape (tyres???).

Table Scans, Index Scans and Index Seeks -->
Table Scans > Index Scans > Index Seeks
So when we take a look @ the query plan we must try and make sure that the table scans are reduced to Index scans and the Index scans are reduced to Index seeks. How?? Add an index for Index Scan. Add a filtered index for Index Seeks or use hints/statistics.

Another cool thing I am working on right now is SECRET.
But until next time, I think this article will not let me forget this kind of basic stuff.

Comments

Popular posts from this blog

System.ConfigurationSettings.AppSettings is Obsolete

Error while trying to run Report Builder 3.0 in Sharepoint 2010

SQL Server 2012 Power View Installation with Sharepoint 2010