Performance Tuning : SQL Server Part -1

Performance Tuning : SQL Server Part -1

I always prefer to create index many does not agree but I believe this is the step where you don’t need to build no deployment and no code change , so why should we not try ?

There are two type of Index basic index and one more hybrid

           1)      Cluster Index

           2)      Non Cluster index

               *      covering indexes (may be New but interesting)

So first look into each and every table and create Primary Key, because primary key will automatically create cluster index and “select “execution will become fast

Create non-clustered indexes on columns which are:

• ·         Used to join other tables
• ·         Frequently used in the search criteria
• ·         Used in the ORDER BY clause
• ·         Used as foreign key fields
• ·         Of having high selectivity (column which returns a low percentage (0-5%) of rows from a total number of rows on a particular value)
• ·         Of type XML (primary and secondary indexes need to be created; more on this in the coming articles)

Now what if you want to search employee with his name and designation + LocationFortunately, there is a way to implement this feature. This is what is called "covered index". You create "covered indexes" in table columns to specify what additional column values the index page should store along with the clustered index key values (primary keys). Following is an example of creating a covered index

CREATE INDEX NCLIX_Employer_EmpID--Index name

ON dbo.EmpMaster(EmpName)--Column on which index is to be created

INCLUDE(EmpDes, EmpLoc)--Additional column values to include

Now you created Index but what if it gives you performance in Test environment not in production ?

You will thought is it possible ? Yes it is possible because Sql engine generate different query plan based on

• ·         Volume of Data
• ·         Index variance
• ·         Parameter Value in SQL SP
• ·         Load

So is there any way to create same environment as production???

Yes…  

That’s call Database Tuning Advisor’s Help with Profiler.

1.       Use SQL Profiler to capture traces in the production server. Use the Tuning template (I know, it is advised not to use SQL Profiler in a production database, but sometimes you have to use it while diagnosing performance problems in production). If you are not familiar with this tool, or if you need to learn more about profiling and tracing using SQL Profiler, read http://msdn.microsoft.com/en-us/library/ms181091.aspx.

2.       Use the trace file generated in the previous step to create a similar load in the test database server using the Database Tuning Advisor. Ask the Tuning Advisor to give some advice (index creation advice in most cases). You are most likely to get good realistic (index creation) advice from the Tuning Advisor (because the Tuning Advisor loads the test database with the trace generated from the production database and then tried to generate the best possible indexing suggestion). Using the Tuning Advisor tool, you can also create the indexes that it suggests. If you are not familiar with the Tuning Advisor tool, or if you need to learn more about using the Tuning Advisor, read http://msdn.microsoft.com/en-us/library/ms166575.aspx.

R   Reference : One article in Tech general and by some R & D

y   

      you can write feedback on viralpala@gmail.com.