In today’s installment of the Quest Pain of the Week webcasts, Jason Hall and I will be discussing a reader’s question about finding missing clustered indexes, their impact on performance, and how to be proactive about monitoring performance to find those types of design issues.

MSSQLTips.com has a great post called “Finding primary keys and missing primary keys in SQL Server.”  That post includes two snippets of code, one for each of the popular SQL versions:

Finding missing primary keys in SQL 2005:

SELECT c.name, b.name
FROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = ‘U’
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = ‘PK’ )

Finding missing primary keys in SQL 2000:

SELECT c.name, a.name
FROM sysobjects a
INNER JOIN sysusers c ON a.uid = c.uid
WHERE xtype = ‘U’
AND NOT EXISTS
(SELECT b.name
FROM sysindexes b
WHERE a.id = b.id
AND (b.status & 2048)<>0)

You can read their post for similar queries on finding tables WITH primary keys too.

Also in today’s webcast, I’ll be showing a few screenshots from Microsoft’s SQL Server Performance Dashboard Reports.  They’re a free set of SSRS reports that are integrated into SQL Server Management Studio.  The reports I’ll be showing in the webcast are somewhat limited in that they only show data that’s currently in the plan cache.  If your server is under memory pressure, it may not retain this data for too long, and you would have to revisit these reports pretty often to get a good picture of what’s going on under the hood.

For more information about the dashboard and how to use it, check out Kevin Kline’s Tool Time article “SQL Server 2005 Dashboard Reports” in SQL Server Magazine’s August 2007 issue.