SQL SERVER – 2008 – Find If Index is Being Used in Database

 It is very often I get query that how to find if any index is being used in database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If number of index is higher it reduces the INSERT / UPDATE / DELETE operation but increase the SELECT operation. It is recommended to drop any unused indexes from table to improve the performance.

Before dropping the index it is important to check if index is being used or not. I have wrote quick script which can find out quickly if index is used or not. SQL Server 2005 and later editions have Dynamic Management Views (DMV) which can queried to retrieve necessary information.

We will run SELECT on Employee table of AdventureWorks database and check it uses Indexes or not. All the information about Index usage is stored in DMV – sys.dm_db_index_usage_stats. Let us run following query first and save our results.

USE AdventureWorks
GO
SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('AdventureWorks') AND sis.OBJECT_ID = OBJECT_ID('HumanResources.Employee');
GO

The query retrieving hits on index of a particular table

So, whenever the query is made on the particular table the table the

sys.dm_db_index_usage_stats and increment column user_seeks is automatically updated based on the particular index. The field which concerns us is the user_seeks which gets automatically incremeted when a particular index is beeing used.

Above whole process explains that any index usage is stored in the sys.dm_db_index_usage_stats. DMV sys.dm_db_index_usage_stats stores all the usage since SQL Server is restarted. Once SQL Server service is restarted sys.dm_db_index_usage_stats is reset to zero but over the period of the time it updates the values in the columns. 

If SQL Server services are not restarted in reasonable amount of time and if any index usage is not found, the index should be dropped. Again, make sure you have test your performance after dropping the index. If it gets worst put that index back and continue exercise.

 

How to Override Inline Styles with Style Sheet

Often we think of inline styles as a way to override styles we set up in the CSS. 99% of the time, this is the case, and it's very handy. But there are some circumstances where you need to do it the other way around. As in, there are inline styles on some markup that you absolutely can't remove, but you need to override what those styles are. This could be markup that is being inserted onto the page from foreign JavaScript or perhaps generated from the bowels of a CMS that you cannot control easily.


Thank our lucky stars, we CAN override inline styles directly from the stylesheet. Take this example markup:


Eg 1:

<div style="background: red;">
    The inline styles for this div should make it red.</div>

div[style] {
   background: yellow !important;
}

Eg 2:
<div class="block">
 <span style="font-weight: bold; color: red;">Hello World</span>
</div>

.block span[style]{
    font-weight: normal !important;
    color: #000 !important;
}

Unfortunately the down side of this is technique is that it will not
work on IE6 and below, but it does work in IE7, IE8, Fire Fox, Safari,
and Opera.

Dropping multiple stored procedure in SQL SERVER

DROP PROCEDURE uspGetAddress, uspInsertAddress, uspDeleteAddress

How to disable copy,cut, paste, autocomplete in textbox

<asp:TextBox ID="txtAnswer" runat="server" CssClass="required" MaxLength="100" Width="200px"  AutoCompleteType="None"  oncopy="return false" onpaste="return false" autocomplete="off"
                                    oncut="return false"></asp:TextBox>