Tests that your database design should pass

— Tables without primary key
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + ‘.’ + t.name
, type_desc = ‘PRIMARY_KEY’
FROM sys.tables t
LEFT OUTER JOIN (SELECT object_id FROM sys.indexes WHERE is_primary_key = 1) i ON t.object_id = i.object_id
WHERE i.object_id IS NULL

— Tables without clustered index
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + ‘.’ + t.name
, type_desc = ‘TABLE’
FROM sys.tables t
LEFT OUTER JOIN (SELECT object_id FROM sys.indexes WHERE type = 1) i ON t.object_id = i.object_id
WHERE i.object_id IS NULL
ORDER BY Found

— Missing foreign Keys
— If a column ends with id but is not a foreign key, it should be renamed
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + ‘.’ + t.name + ‘.’ + c.name
, type_desc = ‘FOREIGN_KEY_CONSTRAINT’
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
LEFT OUTER JOIN (SELECT fk.parent_object_id, fkc.parent_column_id
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
) fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
LEFT OUTER JOIN (SELECT ic.object_id, ic.column_id
FROM sys.index_columns ic
INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN (SELECT object_id
FROM sys.index_columns
GROUP BY object_id, index_id
HAVING COUNT(*) = 1) icu ON ic.object_id = icu.object_id
WHERE i.is_primary_key = 1
) ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE fkc.parent_column_id IS Null
AND ic.object_id IS NULL
AND t.name ‘sysdiagrams’
AND t.name NOT LIKE ‘%Log’
AND t.name NOT LIKE ‘Log%’
AND c.name Like ‘%Id’
ORDER BY FOUND

— Uniqueidentifier column used in clustered index as first column and have default value newid()
— check for default value is commented to show all clustered indexes with first column with uniqueidentifier type
— it is posible that having such first column and that to be fine if that is a foreign key column.
— But if you set a default values on all such columns to newid(), even so you set their values in insert procedure,
— then you can uncomment the last check, and with that remove the ones that are fine as described.
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + ‘.’ + t.name + ‘: ‘ + i.name + ‘ (‘ + c.name + ‘)’
, type_desc = ‘CLUSTERED_INDEX’
FROM sys.index_columns ic
INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.columns c ON t.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
WHERE i.type = 1
AND ic.column_id = 1
AND tp.name = ‘uniqueidentifier’
–AND c.default_object_id 0
ORDER BY Found

— Missing index for foreign key columns
— It is posible that creating indexes for all foreign keys is not required. So you should decide for which ones to create indexes.
SET NOCOUNT ON ;
CREATE TABLE #SmallTables (object_id int)
EXEC sp_msforeachtable ‘If Not Exists(SELECT * FROM (Select Rows = Count(*) From ?) R WHERE Rows < 100)
Insert Into #SmallTables Values(OBJECT_ID(''?''))'

SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + '.' + fkc.name + ' (' + c.name + ')'
, type_desc = 'INDEX'
, Resolution = 'CREATE NONCLUSTERED INDEX [IX_' + t.name + '_' + c.name + '] ON [' + OBJECT_SCHEMA_NAME(t.object_id) + '].[' + t.name + ']([' + c.name + '])'
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN (SELECT fk.parent_object_id, fkc.parent_column_id, fk.name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
) fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
LEFT OUTER JOIN (SELECT ic.object_id, ic.column_id
FROM sys.index_columns ic
INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.index_columns icu ON ic.object_id = icu.object_id
WHERE ic.index_column_id = 1
) ic ON fkc.parent_object_id = ic.object_id AND fkc.parent_column_id = ic.column_id
— INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
INNER JOIN #SmallTables st ON t.object_id = st.object_id
WHERE ic.object_id IS NULL
AND t.name ‘sysdiagrams’
AND t.name NOT LIKE ‘%Log’
AND t.name NOT LIKE ‘Log%’
–AND tp.name ‘uniqueidentifier’
ORDER BY FOUND

DROP TABLE #SmallTables

— VarChar missing size
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type IN (‘P’, ‘FN’, ‘TF’, ‘IF’, ‘AF’, ‘FT’, ‘IS’, ‘PC’, ‘FS’)
AND REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ‘ ‘, ”), ‘ ‘, ”), ‘]’, ”) LIKE ‘%varchar[^(]%’
AND o.name NOT LIKE ‘SqlQueryNotificationStoredProcedure-%’
ORDER BY Found

— Decimal missing size
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type IN (‘P’, ‘FN’, ‘TF’, ‘IF’, ‘AF’, ‘FT’, ‘IS’, ‘PC’, ‘FS’)
AND (REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ‘ ‘, ”), ‘ ‘, ”), ‘]’, ”) LIKE ‘%decimal[^(]%’
OR
REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ‘ ‘, ”), ‘ ‘, ”), ‘]’, ”) LIKE ‘%numeric[^(]%’
)
AND o.name NOT LIKE ‘SqlQueryNotificationStoredProcedure-%’
ORDER BY Found

— Procedures without SET NOCOUNT ON
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type = ‘P’
AND UPPER(OBJECT_DEFINITION(o.object_id)) NOT LIKE ‘%SET NOCOUNT ON%’
AND o.name NOT LIKE ‘SqlQueryNotificationStoredProcedure-%’
ORDER BY Found

— Procedures with SET ROWCOUNT
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type = ‘P’
AND UPPER(OBJECT_DEFINITION(o.object_id)) LIKE ‘%SET ROWCOUNT%’
AND o.name NOT LIKE ‘SqlQueryNotificationStoredProcedure-%’
ORDER BY Found

— Procedures with @@identity
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.type = ‘P’
AND o.name ‘sp_creatediagram’
AND LOWER(OBJECT_DEFINITION(o.object_id)) LIKE ‘%@@identity%’
AND o.name NOT LIKE ‘SqlQueryNotificationStoredProcedure-%’
ORDER BY Found

— Invalid characters in column or object names
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND LOWER(o.name) LIKE ‘%[^a-z0-9_.-]%’
AND o.name NOT LIKE ‘SqlQueryNotificationStoredProcedure-%’
UNION ALL
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name + ‘.’ + c.name
, type_desc = ‘COLUMN’
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.is_ms_shipped = 0
AND LOWER(c.name) LIKE ‘%[^a-z0-9_.-]%’
ORDER BY Found

— Using of float or real data types
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.name ‘sp_creatediagram’
AND o.name NOT LIKE ‘SqlQueryNotificationStoredProcedure-%’
AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE ‘%float%’
OR
LOWER(OBJECT_DEFINITION(o.object_id)) LIKE ‘%real%’)
UNION ALL
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name + ‘.’ + c.name
, type_desc = ‘COLUMN’
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.is_ms_shipped = 0
AND t.name IN (‘float’, ‘real’)
ORDER BY FOUND

— Using of text, ntext or image data types
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name
, o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND o.name ‘sp_creatediagram’
AND o.name NOT LIKE ‘SqlQueryNotificationStoredProcedure-%’
AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE ‘%text%’
OR
LOWER(OBJECT_DEFINITION(o.object_id)) LIKE ‘%ntext%’
OR
LOWER(OBJECT_DEFINITION(o.object_id)) LIKE ‘%image%’)
UNION ALL
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name + ‘.’ + c.name
, type_desc = ‘COLUMN’
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.is_ms_shipped = 0
AND t.name IN (‘text’, ‘ntext’, ‘image’)
ORDER BY FOUND

— Collation problem. Column’s collation is different from database collation
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + ‘.’ + o.name + ‘.’ + c.name
, type_desc = ‘COLUMN’
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.is_ms_shipped = 0
AND CAST(c.collation_name AS VARCHAR(50)) CAST(DATABASEPROPERTYEX(DB_NAME(), ‘Collation’) AS VARCHAR(50))

— Empty tables
SET NOCOUNT ON ;

CREATE TABLE #EmptyTables (Table_Name VARCHAR(100))

EXEC sp_msforeachtable ‘If Not Exists(Select * From ?)
Insert Into #EmptyTables Values(”?”)’

SELECT Table_Name
FROM #EmptyTables
ORDER BY Table_Name

DROP TABLE #EmptyTables

— Wide tables
SELECT Found = OBJECT_SCHEMA_NAME(t.object_id) + ‘.’ + OBJECT_NAME(t.object_id)
, type_desc = ‘TABLE’
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
GROUP BY t.object_id
HAVING SUM(ISNULL(NULLIF(CONVERT(BIGINT, tp.max_length), 8000), 0) + CASE WHEN c.collation_name IS NULL THEN 0 ELSE c.max_length END) > 8060

— Missing index
;WITH missing_index_impact AS (
SELECT dm_db_missing_index_groups.index_handle,
SUM(( dm_db_missing_index_group_stats.avg_total_user_cost
* dm_db_missing_index_group_stats.avg_user_impact
* ( dm_db_missing_index_group_stats.user_seeks
+ dm_db_missing_index_group_stats.user_scans ) )) AS “total_impact”,
( 100.00
* SUM(dm_db_missing_index_group_stats.avg_total_user_cost
* dm_db_missing_index_group_stats.avg_user_impact
* ( dm_db_missing_index_group_stats.user_seeks
+ dm_db_missing_index_group_stats.user_scans ))
/ SUM(SUM(dm_db_missing_index_group_stats.avg_total_user_cost
* dm_db_missing_index_group_stats.avg_user_impact
* ( dm_db_missing_index_group_stats.user_seeks
+ dm_db_missing_index_group_stats.user_scans ))) OVER ( ) ) AS “percent_impact”,
ROW_NUMBER() OVER ( ORDER BY SUM(avg_total_user_cost
* avg_user_impact
* ( user_seeks
+ user_scans )) DESC ) AS rn
FROM sys.dm_db_missing_index_groups AS dm_db_missing_index_groups
JOIN sys.dm_db_missing_index_group_stats AS dm_db_missing_index_group_stats ON dm_db_missing_index_groups.index_group_handle = dm_db_missing_index_group_stats.group_handle
GROUP BY dm_db_missing_index_groups.index_handle
),
agg_missing_index_impact AS (
SELECT missing_index_impact_1.index_handle,
missing_index_impact_1.total_impact,
SUM(missing_index_impact_2.total_impact) AS running_total_impact,
missing_index_impact_1.percent_impact,
SUM(missing_index_impact_2.percent_impact) AS running_total_percent,
missing_index_impact_1.rn
FROM missing_index_impact AS missing_index_impact_1
JOIN missing_index_impact AS missing_index_impact_2 ON missing_index_impact_1.rn <= missing_index_impact_2.rn
GROUP BY missing_index_impact_1.index_handle,
missing_index_impact_1.total_impact,
missing_index_impact_1.percent_impact,
missing_index_impact_1.rn
),
missing_index_details AS (
SELECT dm_db_missing_index_details.database_id,
dm_db_missing_index_details."object_id",
dm_db_missing_index_details.index_handle,
dm_db_missing_index_details."statement",
dm_db_missing_index_details.equality_columns,
dm_db_missing_index_details.inequality_columns,
dm_db_missing_index_details.included_columns
FROM sys.dm_db_missing_index_details AS dm_db_missing_index_details
)
SELECT agg_missing_index_impact.rn,
missing_index_details."statement",
missing_index_details.equality_columns,
missing_index_details.inequality_columns,
missing_index_details.included_columns,
agg_missing_index_impact.running_total_impact,
agg_missing_index_impact.total_impact,
agg_missing_index_impact.running_total_percent,
agg_missing_index_impact.percent_impact,
"key".index_key_column_count,
"key".index_key_column_bytes,
"all".index_all_column_count,
"all".index_all_column_bytes
FROM agg_missing_index_impact AS agg_missing_index_impact
JOIN missing_index_details AS missing_index_details ON agg_missing_index_impact.index_handle = missing_index_details.index_handle
JOIN ( SELECT missing_index_details1.index_handle,
COUNT(*) AS index_key_column_count,
SUM(COL_LENGTH(missing_index_details1."statement",
dm_db_missing_index_columns1.column_name)) AS index_key_column_bytes
FROM missing_index_details AS missing_index_details1
CROSS APPLY sys.dm_db_missing_index_columns(missing_index_details1.index_handle) AS dm_db_missing_index_columns1
WHERE dm_db_missing_index_columns1.column_usage = 'EQUALITY'
OR dm_db_missing_index_columns1.column_usage = 'INEQUALITY'
GROUP BY missing_index_details1.index_handle
) AS "key" ON missing_index_details.index_handle = "key".index_handle
JOIN ( SELECT missing_index_details2.index_handle,
COUNT(*) AS index_all_column_count,
SUM(COL_LENGTH(missing_index_details2."statement",
dm_db_missing_index_columns2.column_name)) AS index_all_column_bytes
FROM missing_index_details AS missing_index_details2
CROSS APPLY sys.dm_db_missing_index_columns(missing_index_details2.index_handle) AS dm_db_missing_index_columns2
GROUP BY missing_index_details2.index_handle
) AS "all" ON missing_index_details.index_handle = "all".index_handle
–WHERE missing_index_details.database_id = ?
ORDER BY agg_missing_index_impact.rn ASC ;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s