Query to obtain indexes information - why does it produce duplicated values? SELECT  OBJECT_NAME(S.OBJECT_ID)                                  AS 'TABLE NAME'
       ,I.NAME                                                    AS 'INDEX NAME'
       ,I.INDEX_ID                                                AS 'INDEX_ID'
       ,USER_UPDATES                                              AS 'TOTAL WRITES'
       ,USER_SEEKS + USER_SCANS + USER_LOOKUPS                    AS 'TOTAL READS'
       ,USER_UPDATES - ( USER_SEEKS + USER_SCANS + USER_LOOKUPS ) AS 'DIFFERENCE'
       ,SUM(PS.[USED_PAGE_COUNT]) * 8                             AS 'INDEXSIZEKB'
       ,IXS.PAGE_COUNT                                            AS 'PAGECOUNT'
       ,IXS.AVG_FRAGMENTATION_IN_PERCENT                          AS 'FRAG'

FROM       SYS.DM_DB_INDEX_USAGE_STATS     AS S  
INNER JOIN SYS.INDEXES                     AS I ON S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID
INNER JOIN SYS.DM_DB_PARTITION_STATS       AS PS ON PS.[OBJECT_ID] = I.[OBJECT_ID]  AND PS.[INDEX_ID] = I.[INDEX_ID]
INNER JOIN SYS.TABLES                      AS T ON T.OBJECT_ID=S.OBJECT_ID
INNER JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS  (DB_ID(), NULL, NULL, NULL, NULL) AS IXS ON IXS.OBJECT_ID=T.OBJECT_ID
WHERE OBJECTPROPERTY(S.OBJECT_ID, 'ISUSERTABLE') = 1
      AND S.DATABASE_ID = DB_ID()
      AND USER_UPDATES &gt; ( USER_SEEKS + USER_SCANS + USER_LOOKUPS )
   -- AND I.INDEX_ID &gt; 1
GROUP BY S.OBJECT_ID ,I.NAME ,I.INDEX_ID ,S.USER_UPDATES, S.USER_SEEKS, S.USER_UPDATES, S.USER_SCANS, S.USER_LOOKUPS, IXS.PAGE_COUNT, IXS.AVG_FRAGMENTATION_IN_PERCENT
ORDER BY 'DIFFERENCE' DESC, 'TOTAL WRITES' DESC, 'TOTAL READS' ASC 


I made this query to list index id, writes, reads and etc, but all values are duplicated only with pagecount and fragmentation different. what am I missing?
If you check where the different column values come from (PAGE_COUNT and AVG_FRAGMENTATION_IN_PERCENT), you will see they are from the system function SYS.DM_DB_INDEX_PHYSICAL_STATS. 

Just before joining with SYS.DM_DB_INDEX_PHYSICAL_STATS you have joined SYS.INDEXES with SYS.DM_DB_PARTITION_STATS, so your row is detailed by object_id, index_id, partition_number. Try joining by these 3 against SYS.DM_DB_INDEX_PHYSICAL_STATS (since the function result is actually detailed by these 3):

INNER JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS IXS ON 
    IXS.OBJECT_ID = T.OBJECT_ID AND
    IXS.index_id = I.index_id AND
    IXS.partition_number = PS.partition_number