How to find indexes and statics on a particular table in SQL Server

How to find indexes and statics on a particular table in SQL Server.

Generally we create indexes on any table in a database to increase data retrieval process. So when we create an index (Clustered Indexes or Non-Clustered indexes) we think that this is all we need to do.
However as data are inserted or deleted as time flies by, these indexes will be out of sync based on new set of data.

As a developer or dba, we should keep in mind that we should periodically check all the indexes on our database  and see when was last time it was updated. This can help a lot in performance improvement in production environment.

So let's get into some technical aspect of indexes.

How we create indexes?-- You can find many resources online
Clustered vs. Non-Clustered indexes.-- You can find many resources online

So after creating al these indexes, how do we go and check that what all indexes exists on a particular table and when was it last updated?

To begin with, we will have to look at the table called sys.indexes which hold all indexes information. Table name in sys.indexes is hidden in Object_ID values which we will have to convert to read.

So begin with wild select on this table.

Select * From Sys.Indexes;

Some important columns are following:
object_id = your table ID
name = Name of the index
index_id = IndexID
is_unique
is_primary_key = does it have a primary key as part of index
is_unique_constraint
fill_factor = default value is 80 percent
has_filter = does your index have filter or not


Now we want to know what all indexes exist on my table. Let's say I have a table 'dbo.Mytable'


To find this out let's run this query.


SELECT name AS index_name
 , STATS_DATE(OBJECT_ID, index_id)  AS StatsUpdated 
 FROM sys.indexes 
 WHERE OBJECT_ID = OBJECT_ID('dbo.Mytable');

 This query will return two columns Index_Name and statsUpdated (date when it was updated last time). Based on this, you can find out when was your indexes last built and if you need to rebuild them or not.

Update:

This sql will provide you the name of index on your table, type of index and percent of fragmentation.

SELECT  a.index_id, name, a.index_type_desc, a.page_count, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Schema.TableName'),
     NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;