Finding Missing Index

The first thing I usually do when optimizing queries is to check if there is at least one index on all tables referred in the query. However, having the right index is important as well. I found this query on the net that finds what index is missing on most frequently used queries. I would thank them but I can't remember

SELECT  
Avg_Estimated_Impact = dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans),
TableName = OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id),
Create_Statement = 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
   + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
   CASE
      WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
      ELSE ''
   END
   + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
   + ']'
   + ' ON ' + dm_mid.statement
   + ' (' + ISNULL (dm_mid.equality_columns,'')
   + CASE 
         WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' 
         ELSE ''
      END
   + ISNULL (dm_mid.inequality_columns, '')
   + ')'
   + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '')
FROM sys.dm_db_missing_index_groups dm_mig
   INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY last_user_seek DESC

Here's the result set:

Avg_Estimated_Impact        TableName    Create_Statement
-------------------------   ------------ ------------------------------------------------
2052.6                      MyTable      CREATE INDEX [IX_MyTable_Col1_Col2] ON [DBName].[dbo].[MyTable] ([Col1],[Col2]) INCLUDE ([Col3], [Col4])

2066.24                     MyOtherTable CREATE INDEX [IX_MyOtherTable_Col3_Col5] ON [DBName].[dbo].[MyOtherTable] ([Col3],[Col5]) INCLUDE ([Col4])

Comment?


~~ CK

Getting Size of All Tables in The Database

Here's a quick way of getting the size of all tables in the database. This will display the size in pages, bytes and the number of rows.

select 
   o.name,
   reservedpages = sum(a.total_pages),
   usedpages = sum(a.used_pages),
   pages = sum(case 
                  when a.type <> 1 then a.used_pages
                  when p.index_id < 2 then a.data_pages 
                  else 0 
               end),
   bytes = SUM(a.used_pages)*8096,
   rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)
from sys.objects o
   inner join sys.partitions p on p.object_id = o.object_id
   inner  join sys.allocation_units a on p.partition_id = a.container_id
where o.type = 'U' 
group by o.name
order by 3 desc 

Here's the result set:

name           reservedpages  usedpages  pages   bytes  rows
-------------- -------------- ---------- ------- ------ ------
MyTable        4              4          2       32384  176
MyOtherTable   2              2          1       16192  30

Here's another way of doing it.

SELECT
   TableName = QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name), 
   Rows = SUM(p.Rows) 
FROM sys.objects AS o
   INNER JOIN sys.partitions AS p ON o.object_id = p.object_id
WHERE o.type = 'U'
      AND o.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered      
GROUP BY o.schema_id, o.name
ORDER BY [TableName]

Here's the result set:

TableName                            Rows
------------------------------------ ------
[dbo].[Table1]                          176
[dbo].[Table2]                           30

And to find the size of a specific table

SELECT
   TableName = QUOTENAME(o.name), 
   Rows = SUM(p.Rows) 
FROM tempdb.sys.objects AS o
   INNER JOIN tempdb.sys.partitions AS p ON o.object_id = p.object_id and
                                    o.object_id = object_id('[TableName]')
WHERE o.type = 'U'
      AND o.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered      
GROUP BY o.schema_id, o.name

As always, comments are welcome.


~~ CK