Search This Blog & Web

Tuesday, May 19, 2009

Finding missing relationship

-- Possible bad Indexes (writes > reads)

DECLARE @dbid int
SELECT @dbid = db_id()
SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id, 'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups, 'Difference' = user_updates - (user_seeks + user_scans + user_lookups)
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
WHERE objectproperty(s.object_id,'IsUserTable') = 1 AND s.database_id = @dbid AND user_updates > (user_seeks + user_scans + user_lookups)
ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC;

--- Index Read/Write stats for a single table
DECLARE @dbid int
SELECT @dbid = db_id()
SELECT objectname = object_name(s.object_id), indexname = i.name, i.index_id, reads = user_seeks + user_scans + user_lookups, writes = user_updates
FROM sys.dm_db_index_usage_stats AS s, sys.indexes AS i
WHERE objectproperty(s.object_id,'IsUserTable') = 1 AND s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id = @dbid AND object_name(s.object_id) IN( 'tablename')
ORDER BY object_name(s.object_id), writes DESC, reads DESC;

-- Show existing indexes for this table EXEC sp_HelpIndex 'tablename'

-- Missing Indexes SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',mid.equality_columns, mid.inequality_columns, mid.included_columns,migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)ON mig.index_handle = mid.index_handleORDER BY index_advantage DESC;

-- Missing indexes for a single tableSELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',mid.equality_columns, mid.inequality_columns, mid.included_columns,migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)ON mig.index_handle = mid.index_handleWHERE statement = '[databasename].[dbo].[tablename]' -- Specify one tableORDER BY index_advantage DESC;

-- Examine current indexesEXEC sp_HelpIndex 'dbo.tablename'

1 comment:

Shamas Saeed said...

Good link and another solution is this.

--Query to find Constraints which are existing
select Tables1.Name PKTable, Columns1.Name as PKColumn, Tables2.Name FKTable, Columns2.Name FKColumn,
case when FKs.constid is null then 0 else 1 end as HasFK

from
sysobjects Tables1 inner join
(select id, count(*) as ColNo
from sysindexkeys
where indid = 1
group by id
having count(*) = 1) PKColCount
on PKColCount.id = Tables1.ID
inner join syscolumns Columns1 on Columns1.id = Tables1.id and right(Columns1.Name, 2) = 'ID'-- not in ('Name', 'Status', 'UpdateDate', 'CreateDate')
inner join sysindexkeys PKColumns on PKColumns.id = Tables1.id and PKColumns.indid = 1 and PKColumns.colid = Columns1.colid
inner join syscolumns Columns2 on Columns1.Name = Columns2.Name and Columns1.id != Columns2.id
inner join sysobjects Tables2 on Columns2.id = Tables2.id
left join sysforeignkeys FKs on Columns1.colid = FKs.rkey and Columns2.colid = FKs.fkey
and Columns1.id = FKs.rkeyid and Columns2.id = FKs.fkeyid
where Tables1.xtype = 'U' and Tables2.xtype = 'U' and FKs.constid is null and
not
(Tables1.Name like 'sys%' or Tables2.Name like 'sys%' or Columns1.Name like 'CommonName'
--other combinations that are not relevant
)


--
-- SELECT f.name AS ForeignKey,
--OBJECT_NAME(f.parent_object_id) AS TableName,
--COL_NAME(fc.parent_object_id,
--fc.parent_column_id) AS ColumnName,
--OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
--COL_NAME(fc.referenced_object_id,
--fc.referenced_column_id) AS ReferenceColumnName
--FROM sys.foreign_keys AS f
--INNER JOIN sys.foreign_key_columns AS fc
--ON f.OBJECT_ID = fc.constraint_object_id


--select * from syscolumns a where right(a.Name, 2) like 'ID'