SQL Query Running Slow !

Most of the time beginners sql developer  comes with query performance issue it could be that one of report having SQL query is running slow even earlier it was running fine. I have faced this issue several time, my observation have included the following points which degrade query performance.

Select * from <Table Name>   Here * is commonly used by new developers and they really get surprised when they see query taking more time and do the complaint with DBA and says "simple query I have written is taking unexpected time" :-).

So friends specially developers who are beginners, just keep few things in your mind that  used  of  * with select statement is not recommended at all, it can lead to poor sql query performance because indexes which exists in table may not selected by query optimizer due to *  used in select query. It could be big topic if we think about the reasons that why indexes are not being used by query optimizer, I will discuss the same in my next blog.

Cluster index should placed   Another important thing, cluster index which should be placed on table.
When creating primary key on table, itself it create cluster index on table and this way initially query performance definitely can be boost.

Missing non cluster indexes   Other than cluster index, non cluster indexes can be  used and placed on table to enhance further query performance. Following script can suggest the indexes which is required by query optimizer however before implement missing indexes, necessary impact analysis should be performed by developer/DBA.

SELECT '------MISSING INDEXES--------------'
SELECT  DB_NAME(D.DATABASE_ID) DBNAME,
        OBJECT_NAME(D.OBJECT_ID) TABLENAME,
        RowC.row_count,
        D.INDEX_HANDLE,
        D.EQUALITY_COLUMNS,
        D.INEQUALITY_COLUMNS,
        D.INCLUDED_COLUMNS,
        D.STATEMENT AS FULLY_QUALIFIED_OBJECT,
        GS.*
FROM    SYS.DM_DB_MISSING_INDEX_GROUPS G
        JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS GS ON GS.GROUP_HANDLE = G.INDEX_GROUP_HANDLE
        JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON G.INDEX_HANDLE = D.INDEX_HANDLE
        Join ( SELECT   OBJECT_NAME(OBJECT_ID) TableName,
                        st.row_count
               FROM     sys.dm_db_partition_stats st
               WHERE    index_id < 2
             ) As RowC on RowC.tablename = OBJECT_NAME(D.OBJECT_ID)
WHERE   D.DATABASE_ID = D.DATABASE_ID
        AND D.OBJECT_ID = D.OBJECT_ID
        AND D.DATABASE_ID = db_id()
  --and OBJECT_NAME(D.OBJECT_ID)='TableName'-- Table Name
Order by last_user_seek desc

As discussed, these are few steps which definitely improves the performance of sql query.
I will elaborate many things in my next blog which are missed here.

Rajnish Awasthi
Sr. Consultant
MCTS