Tuning Tidbits

1. can avoid sort by in exectuion plan ,by including that column in index,so that it will get sorted in index itself so no need to sort again

Sql Queries

To list the index of the table

SELECT 

     TableName = t.name,

     IndexName = ind.name,

     IndexId = ind.index_id,

     ColumnId = ic.index_column_id,

     ColumnName = col.name,

     is_included_column,

     ind.*,

     ic.*,

     col.* 

FROM 

     sys.indexes ind 

INNER JOIN 

     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 

INNER JOIN 

     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 

INNER JOIN 

     sys.tables t ON ind.object_id = t.object_id 

WHERE 

     ind.is_primary_key = 0 

     AND ind.is_unique = 0 

     AND ind.is_unique_constraint = 0 

     AND t.is_ms_shipped = 0 

     and t.name = 'lcp_productdetail'

ORDER BY 

     t.name, ind.name, ind.index_id, ic.index_column_id

To fetch the last executed SP

SELECT DB_Name(SqlTxt.[dbid]) AS [Database]     , 

Object_Name(SqlTxt.objectid, SqlTxt.[dbid]) AS [Stored Procedure]     , 

Max(stat.last_execution_time) As [Last Executed Time] FROM sys.dm_exec_query_stats AS stat     

CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) AS SqlTxt     INNER JOIN sys.procedures SP         

ON SP.object_id = SqlTxt.objectid     

where db_name(SqlTxt.[dbid])     = 'MMP_Generator'

and stat.last_execution_time >'2014-08-11 09:08:50.860'

AND SqlTxt.[dbid] IS NOT NULL GROUP BY 

db_name(SqlTxt.[dbid])     , 

Object_Name(SqlTxt.objectid, SqlTxt.[dbid]) ORDER BY 1, 2   

To change the identity value of a table

DBCC CHECKIDENT (yourtable, reseed, 34)

This will insert the new row with the identity value as 35.
Note:Even if already there is value 35 in the table,it will get repeated,so have to be careful if it is considered as the column for the uniqueness of the table.(go through the below link)

Useful Links

1. Identity reseed