how to view association among File Groups, System Logical and Physical Files and Tables

You can use this code to view file group, logical filename and physical filename associated to a table. It can be beneficial if you have large number of database files and want to see objects (tables) in a file or to view the file associated to a table.

To see whole list of tables in each data file
A table can have only one value at a time in index table (0 or 1), 1 for clustered index and 0 for heap, so you can use both values, if a table has clustered index it will not have heap associated value in index table and vice versa.

SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SI.INDEX_ID IN (0,1)

To see whole list of tables in a specific data file
You can get file id or file name or logical file name from below query or you can use FILE_ID() function if you know logical file name.

SELECT FILEID, FILENAME, NAME FROM SYS.SYSFILES

SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SF.FILEID = FILE_ID('Logical File Name')

To see filename for a table
SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SI.OBJECT_ID = OBJECT_ID('Table Name')

Table's rows count without using COUNT() function

You can get table's total number of rows as:

SELECT SUM(ROWS) AS Total_Rows
FROM SYS.SYSINDEXES
WHERE ID=OBJECT_ID('Table1')
AND INDID IN (0,1)

OBJECT_ID('Table1'):
This function will return the object id for table "Table1" or any other specified table.

INDID:
It can be 0 for heap and 1 for clustered index, and it will have only one value at a time 0 or 1, greater than 1 values are for non-clustered indexes.

Determining Index fragmentation / when to rebuild / when to reorganize

Index fragmentation:
When data is inserted, updated or deleted in a table, if clustered or non-clustered indexes exist on that table, they must be maintained to reflect the changes. The maintenance of these indexes eventually will cause the indexes less efficient. This inefficiency leads to index fragmentation.

We have two types of index fragmentation:
Internal Fragmentation
External Fragmentation

In result of DELETE operation, spaces occurs in the underlying index page, so when index page is not full as it should be, it leads to internal fragmentation.

Sometimes when INSERT or UPDATE operation occurs, database engine creates additional index page to accommodate this operation, a page split occurs and a new page created by maintaining the logical order, as it does not maintain the physical order of index pages. So this variation in logical and physical ordering of index pages called external fragmentation.

SQL Server maintains the counters or values to keep track indexes internal and external fragmentation, so you can evaluate by using these indicators, when to rebuild and when to reorganize the indexes to increase its efficiency.

You can get these stats from a dynamic management function that is replacement of DBCC SHOCONTIG statement:

SYS.DM_DB_INDEX_PHYSICAL_STATS(database_id/Null, object_id/Null, index_id/Null/0, partition_number/Null, mode/Null/Defualt)

database_id/Null: ID of the database, you can use DB_ID(‘database_name’) or DB_ID() to get the current database id. If you specify Null then it will return the information for all databases, and you must supply object_id, index_id and partition_number with Null values.
object_id/Null: ID of base table or view. You can use Object_ID(‘table/view name’) to get object id. If you specify Null, it will return the information of all table/view of current database, you must supply index_id and partition_number with Null values.
index_id/Null/0: ID of index. You can specify Null, it will return the information of all indexes for specified base table or index, you must supply partition_number with Null value. 0 index_id is for heap tables.
partition_number/Null: partition number of index/heap or Null to return the information for all partitions.
mode/Null/Default: mode specifies the scan level that is used to obtain statistics. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED or DETAILED. The default is LIMITED.


Now you have to decide when to rebuild (recreating the current indexes) the indexes and when to reorganize (de-fragmentation of the current indexes) them, you have to observe the values of avg_fragmentation_in_percent and avg_page_space_used_in_percent columns returned by sys.dm_db_index_physical_stats DMF.


If avg_fragmentation_in_percent greater than 15 OR avg_fragmentation_in_percent less than 60 then you have to rebuild or recreate the indexes as:

ALTER INDEX ALL ON Table_Name REBUILD


If avg_page_space_used_in_percent between 60 and 75 Or avg_fragmentation_in_percent between 10 and 15 then you have to reorganize the indexes as:

ALTER INDEX ALL ON Table_Name REORGANIZE

Example:

SELECT
DB_NAME(DATABASE_ID)
,OBJECT_NAME(OBJECT_ID)
,INDEX_ID
,INDEX_TYPE_DESC
,AVG_FRAGMENTATION_IN_PERCENT
,AVG_PAGE_SPACE_USED_IN_PERCENT
,CASE WHEN AVG_FRAGMENTATION_IN_PERCENT >15 OR AVG_PAGE_SPACE_USED_IN_PERCENT<60 THEN 'REBUILD INDEX' END AS INDEX_REBUILD_STATE
,CASE WHEN (AVG_FRAGMENTATION_IN_PERCENT >10 AND AVG_FRAGMENTATION_IN_PERCENT<15) OR
(AVG_PAGE_SPACE_USED_IN_PERCENT>60 AND AVG_PAGE_SPACE_USED_IN_PERCENT<75) THEN 'RE-ORGANIZE INDEX' END AS INDEX_REORGANIZE_STATE
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), OBJECT_ID('TEST'), NULL, NULL, 'DETAILED')
WHERE INDEX_ID<>0

Determining indexes utilization Or unused indexes

You can use following DMV to check index utilization or to see unused indexes.

SYS.DM_DB_INDEX_USAGE_STATS

By determining the stats, you can see what indexes are mostly used by the queries and what indexes are unused and overhead for the system from maintenance point of view. These stats/counters will be initialized to empty whenever SQL Server restarts, database is de-attached, or database is shutdown.

When you perform SELECT, INSERT, UPDATE or DELETE operations, then this DMV keeps track all of your performed operations in each separate counter as:

USER_SCANS: selection performed on whole table without specifying any criteria. (SELECT * operation)

USER_SEEKS: selection performed by specifying any criteria. (Either looking up a single or doing range scan)

USER_LOOKUPS: large selection performed with specified criteria by using non-clustered indexes.

USER_UPDATES: level of maintenance when INSERT, UPDATE or DELETE operation performed.

Above counters have associated dates, these are updated when above any counter incremented. This means this date will reflect about the last date of action performed or last time when index was accessed.

LAST_USER_SCAN
LAST_USER_SEEK
LAST_USER_LOOKUP
LAST_USER_UPDATE

You can use the following query to get index name and its associated user stats.

SELECT SI.INDEX_ID,SI.NAME,USER_SCANS,USER_SEEKS,USER_LOOKUPS, USER_UPDATES, LAST_USER_SCAN, LAST_USER_SEEK, LAST_USER_LOOKUP,LAST_USER_UPDATE
FROM SYS.DM_DB_INDEX_USAGE_STATS DMI
INNER JOIN SYS.INDEXES SI ON SI.INDEX_ID=DMI.INDEX_ID AND DMI.OBJECT_ID=SI.OBJECT_ID
WHERE DMI.OBJECT_ID=OBJECT_ID('TEST')

Example:

1) Create test table
CREATE TABLE TEST
(VAL1 INT IDENTITY(1,1)
,VAL2 VARCHAR(2)
,VAL3 VARCHAR(3)
)

2) Create Indexes
CREATE CLUSTERED INDEX VAL1_INDEX ON TEST (VAL1)
CREATE NONCLUSTERED INDEX VAL2_INDEX ON TEST (VAL2)
CREATE NONCLUSTERED INDEX VAL3_INDEX ON TEST (VAL3)

3) Insert test data

DECLARE @I INT
SET @I=1
WHILE (@I<100000)
BEGIN
INSERT INTO TEST VALUES('C','B')
SET @I=@I+1
END

4) Check USER_UPDATES and LAST_USER_UPDATE values

5) Update 50% data with new values

UPDATE TEST SET VAL2='D',VAL3='F' WHERE VAL1>50000

6) Check values for USER_UPDATES, LAST_USER_UPDATE, USER_SEEKS, LAST_USER_SEEK

7) SELECT * FROM TEST

8) Check values for USER_SCANS and LAST_USER_SCAN

9) SELECT * FROM TEST WHERE VAL1=40

10) Check values for USER_SEEKS and LAST_USER_SEEK

11) SELECT * FROM TEST WHERE VAL2='F'

12) Check values for USER_SEEKS, LAST_USER_SEEK, USER_LOOKUPS and LAST_USER_LOOKUP