George and I were thinking of adding a bunch of new checks/reports to SQLCop. SQLCop detects the following issues right now.

Code

• Procedures with SP_
• VarChar Size Problems
• Decimal Size Problem
• Undocumented Procedures
• Procedures without SET NOCOUNT ON
• Procedures with SET ROWCOUNT
• Procedures with @@Identity
• Procedures with dynamic sql
• Procedures using dynamic sql without sp_executesql

Column

• Column Name Problems
• Columns with float data type
• Columns with image data type
• Tables with text/ntext
• Collation Mismatch
• UniqueIdentifier with NewId

Table/Views

• Table Prefix
• Table Name Problems
• Missing Foreign Keys
• Wide Tables
• Tables without a primary key
• Empty Tables
• Views with order by
• Unnamed Constraints

Indexes

• Fragmented indexes
• Missing Foreign Key Indexes
• Forwarded Records

Configuration

• Database Collation
• Auto Close
• Auto Create
• Auto Shrink
• Auto Update
• Compatibility Level
• Login Language
• Old Backups
• Orphaned Users
• User Aliases
• Ad Hoc Distributed Queries
• CLR
• Database and log files on the same physical disk
• Database Mail
• Deprecated Features
• Instant File Initialization
• Max Degree of Parallelism
• OLE Automation Procedures
• Service Account
• SMO and DMO
• SQL Server Agent Service
• xp cmdshell

Health

• Buffer cache hit ratio
• Page life expectancy

I was thinking about adding an informational section to SQLCop and also some additional checks. Leave me a comment if you have other ideas or agree with the ones presented here

Would you like to know the last time DBCC ran successfully against your database?

This code would report that

CREATE TABLE #Test(ParentObject VARCHAR(500),Object VARCHAR(500),Field  VARCHAR(500),VALUE VARCHAR(500))

DECLARE @db VARCHAR(500)
SELECT @db =  db_name()

INSERT #Test
EXEC('DBCC DBINFO (''' + @db + ''') WITH TABLERESULTS')

SELECT DISTINCT Value FROM #Test
WHERE field = 'dbi_dbccLastKnownGood'

DROP TABLE #Test

If we add the configuration section would it be beneficial if we added the min and max values for the following

max worker threads

max text repl size (B)

max degree of parallelism

max server memory (MB)

max full-text crawl range

min memory per query (KB)

min server memory (MB)

For example

SELECT * FROM sys.configurations
WHERE name like( 'max%')

SELECT * FROM sys.configurations
WHERE name like( 'min%')

What about fillfactor?

SELECT * FROM sys.configurations
WHERE name  = 'fill factor (%)'

How about the 50 most used stored procedures

SELECT top 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,execution_count,s2.objectid,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
         THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1)) AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
and exists (Select 1 from sys.procedures s
where s.is_ms_shipped = 0
and s.name = x.ProcName )
ORDER BY execution_count DESC

How about stored procedures with the highest average CPU time

SELECT top 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,execution_count,s2.objectid,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
         THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1)) AS sql_statement,
       s1.total_worker_time/s1.execution_count as AverageCPUTime
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
and exists (Select 1 from sys.procedures s
where s.is_ms_shipped = 0
and s.name = x.ProcName )
ORDER BY AverageCPUTime DESC

Do you want to see all the different programs connected to your server?

SELECT COUNT(*) Count,program_name 
FROM sys.sysprocesses
WHERE status <> 'background'
GROUP BY program_name
ORDER BY 1 desc

This returns something like this on one of my boxes

42 Microsoft SQL Server Management Studio - Query                                                                                  
40  Microsoft Visual FoxPro                                                                                                         
29  Microsoft SQL Server Management Studio                                                                                          
19  .Net SqlClient Data Provider                                                                                                    
2   Microsoft SQL Server                                                                                                            
1   SQLAgent - Alert Engine                                                                                                         
1   SQLAgent - Generic Refresher                                                                                                    
1   SQLAgent - Job invocation engine

Do you want to know the file size, space used and free space for all of the files for your database?

select
    CONVERT(DATE,GETDATE()) AS [Date],
    a.FILEID,
    [FILE_SIZE_MB] =
        convert(decimal(12,2),round(a.size/128.000,2)),
    [SPACE_USED_MB] =
        convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
    [FREE_SPACE_MB] =
        convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
    NAME = left(a.NAME,35),
    FILENAME = left(a.FILENAME,60)
from
    dbo.sysfiles a

Here is what the output would look like

Date      FILEID  FILE_SIZE_MB  SPACE_USED  FREE_SPACE_MB  NAME    FILENAME
2012-04-09  1   1024.00   1.56       1022.44       tempdev  D:MSSQLDatatempdb.mdf
2012-04-09  2   2048.00   39.72      2008.28       templog  D:MSSQLDatatemplog.ldf
2012-04-09  3   1024.00   0.25       1023.75       tempdev1 D:MSSQLDatatempdb1.ndf
2012-04-09  4   1024.00   0.31       1023.69       tempdev2 D:MSSQLDatatempdb2.ndf
2012-04-09  5   1024.00   0.38       1023.63       tempdev3 D:MSSQLDatatempdb3.ndf
2012-04-09  6   1024.00   0.25       1023.75       tempdev4 D:MSSQLDatatempdb4.ndf
2012-04-09  7   1024.00   0.25       1023.75       tempdev5 D:MSSQLDatatempdb5.ndf
2012-04-09  8   1024.00   0.25       1023.75       tempdev6 D:MSSQLDatatempdb6.ndf
2012-04-09  9   1024.00   0.44       1023.56       tempdev7 D:MSSQLDatatempdb7.ndf

Leave me a comment if you know of anything that you would like to add or if some of this stuff I listed would be useful