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

Code

Column

Table/Views

Indexes

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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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
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

T-SQL
1
2
3
4
5
SELECT * FROM sys.configurations
WHERE name like( 'max%')
 
SELECT * FROM sys.configurations
WHERE name like( 'min%')
SELECT * FROM sys.configurations
WHERE name like( 'max%')

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

What about fillfactor?

T-SQL
1
2
SELECT * FROM sys.configurations
WHERE name  = 'fill factor (%)'
SELECT * FROM sys.configurations
WHERE name  = 'fill factor (%)'

How about the 50 most used stored procedures

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
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?

T-SQL
1
2
3
4
5
SELECT COUNT(*) Count,program_name 
FROM sys.sysprocesses
WHERE status <> 'background'
GROUP BY program_name
ORDER BY 1 desc
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?

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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
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