sp_Blitz<\/a><\/p>\n\r\nUSE master;\r\nGO\r\n\r\nIF OBJECT_ID('master.dbo.sp_Blitz') IS NOT NULL \r\n DROP PROC dbo.sp_Blitz;\r\nGO\r\n\r\nCREATE PROCEDURE [dbo].[sp_Blitz]\r\n @CheckUserDatabaseObjects TINYINT = 1 ,\r\n @CheckProcedureCache TINYINT = 0 ,\r\n @OutputType VARCHAR(20) = 'TABLE' ,\r\n @OutputProcedureCache TINYINT = 0 ,\r\n @CheckProcedureCacheFilter VARCHAR(10) = NULL ,\r\n @CheckServerInfo TINYINT = 0 ,\r\n @Version INT = NULL OUTPUT\r\nAS \r\n SET NOCOUNT ON;\r\n\/*\r\n sp_Blitz v16 - December 13, 2012\r\n \r\n (C) 2012, Brent Ozar Unlimited\r\n\r\nTo learn more, visit http:\/\/www.BrentOzar.com\/blitz where you can download\r\nnew versions for free, watch training videos on how it works, get more info on\r\nthe findings, and more. To contribute code and see your name in the change\r\nlog, email your improvements & checks to Help@BrentOzar.com.\r\n\r\nExplanation of priority levels:\r\n 1 - Critical risk of data loss. Fix this ASAP.\r\n 10 - Security risk.\r\n 20 - Security risk due to unusual configuration, but requires more research.\r\n 50 - Reliability risk.\r\n 60 - Reliability risk due to unusual configuration, but requires more research.\r\n100 - Performance risk.\r\n110 - Performance risk due to unusual configuration, but requires more research.\r\n200 - Informational.\r\n250 - Server info. Not warnings, just explaining data about the server.\r\n\r\nKnown limitations of this version:\r\n - No support for SQL Server 2000 or compatibility mode 80.\r\n - If a database name has a question mark in it, some tests will fail. Gotta\r\n love that unsupported sp_MSforeachdb.\r\n\r\nUnknown limitations of this version:\r\n - None. (If we knew them, they'd be known. Duh.)\r\n\r\nChanges in v16:\r\n - Chris Fradenburg @ChrisFradenburg http:\/\/www.fradensql.com:\r\n - Check 81 for non-active sp_configure options not yet taking effect.\r\n - Improved check 35 to not alert if Optimize for Ad Hoc is already enabled.\r\n - Rob Sullivan @DataChomp http:\/\/datachomp.com:\r\n - Suggested to add output variable @Version to manage server installations.\r\n - Vadim Mordkovich:\r\n - Added check 85 for database users with elevated database roles like\r\n db_owner, db_securityadmin, etc.\r\n - Vladimir Vissoultchev rewrote the DBCC CHECKDB check to work around a bug in\r\n SQL Server 2008 & R2 that report dbi_dbccLastKnownGood twice. For more info\r\n on the bug, check Connect ID 485869.\r\n - Added check 77 for database snapshots.\r\n - Added check 78 for stored procedures with WITH RECOMPILE in the source code.\r\n - Added check 79 for Agent jobs with SHRINKDATABASE or SHRINKFILE.\r\n - Added check 80 for databases with a max file size set.\r\n - Added @CheckServerInfo perameter default 0. Adds additional server inventory\r\n data in checks 83-85 for things like CPU, memory, service logins. None of\r\n these are problems, but if you're using sp_Blitz to assess a server you've\r\n never seen, you may want to know more about what you're working with. I do.\r\n - Tweaked check 75 for large log files so that it only alerts on files > 1GB.\r\n - Changed one of the two check 59's to be check 82. (Doh!)\r\n - Added WITH NO_INFOMSGS to the DBCC calls to ease life for automation folks.\r\n - Works with offline and restoring databases. (Just happened to test it in\r\n this version and it already worked - must have fixed this earlier.)\r\n\r\nChanges in v15:\r\n - Mikael Wedham caught bugs in a few checks that reported the wrong database name.\r\n - Bob Klimes fixed bugs in several checks where v14 broke case sensitivity.\r\n - Seth Washeck fixed bugs in the VLF checks so they include the number of VLFs.\r\n\r\nChanges in v14:\r\n - Lori Edwards @LoriEdwards http:\/\/sqlservertimes2.com\r\n - Did all the coding in this version! She did a killer job of integrating\r\n\t improvements and suggestions from all kinds of people, including:\r\n - Chris Fradenburg @ChrisFradenburg http:\/\/www.fradensql.com \r\n - Check 74 to identify globally enabled traceflags\r\n - Jeremy Lowell @DataRealized http:\/\/datarealized.com added:\r\n - Check 72 for non-aligned indexes on partitioned tables\r\n - Paul Anderton @Panders69 added check 69 to check for high VLF count\r\n - Ron van Moorsel added several changes\r\n\t - Added a change to check 6 to use sys.server_principals instead of syslogins\r\n\t - Added a change to check 25 to check whether tempdb was set to autogrow. \r\n\t - Added a change to check 49 to check for linked servers configured with the SA login\r\n - Shaun Stuart @shaunjstu http:\/\/shaunjstuart.com added several changes:\r\n\t - Added check 68 to check for the last successful DBCC CHECKDB\r\n\t - Updated check 1 to verify the backup came from the current \r\n\t - Added check 70 to verify that @@servername is not null\r\n - Typo in check 51 changing free to present thanks to Sabu Varghese\r\n - Check 73 to determine if a failsafe operator has been configured\r\n - Check 75 for transaction log files larger than data files suggested by Chris Adkin\r\n - Fixed a bunch of bugs for oddball database names (like apostrophes).\r\n\r\nChanges in v13:\r\n - Fixed typos in descriptions of checks 60 & 61 thanks to Mark Hions.\r\n - Improved check 14 to work with collations thanks to Greg Ackerland.\r\n - Improved several of the backup checks to exclude database snapshots and\r\n databases that are currently being restored thanks to Greg Ackerland.\r\n - Improved wording on check 51 thanks to Stephen Criddle.\r\n - Added top line introducing the reader to sp_Blitz and the version number.\r\n - Changed Brent Ozar PLF, LLC to Brent Ozar Unlimited. Great catch by\r\n Hondo Henriques, @SQLHondo.\r\n - If you've submitted code recently to sp_Blitz, hang in there! We're still\r\n building a big new version with lots of new checks. Just fixing bugs in\r\n this small release.\r\n\r\nChanges in v12:\r\n - Added plan cache (aka procedure cache) analysis. Examines top resource-using\r\n queries for common problems like implicit conversions, missing indexes, etc.\r\n - Added @CheckProcedureCacheFilter to focus plan cache analysis on\r\n CPU, Reads, Duration, or ExecCount. If null, we analyze all of them.\r\n - Added @OutputProcedureCache to include the queries we analyzed. Results are\r\n sorted using the @CheckProcedureCacheFilter parameter, otherwise by CPU.\r\n - Fixed case sensitive calls of sp_MSforeachdb reported by several users.\r\n\r\nChanges in v11:\r\n - Added check for optimize for ad hoc workloads in sys.configurations.\r\n - Added @OutputType parameter. Choices:\r\n \t- 'TABLE' - default of one result set table with all warnings.\r\n\t- 'COUNT' - Sesame Street's favorite character will tell you how many\r\n\t\t\t\tproblems sp_Blitz found. Useful if you want to use a\r\n\t\t\t\tmonitoring tool to alert you when something changed.\r\n\r\nChanges in v10:\r\n - Jeremiah Peschka added check 59 for file growths set to a percentage.\r\n - Ned Otter added check 62 for old compatibility levels.\r\n - Wayne Sheffield improved checks 38 & 39 by excluding more system tables.\r\n - Christopher Fradenburg improved check 30 (missing alerts) by making sure\r\n that alerts are set up for all of the severity levels involved, not just\r\n some of them.\r\n - James Siebengartner and others improved check 14 (page verification) by\r\n excluding TempDB, which can't be set to checksum in older versions.\r\n - Added check 60 for index fill factors <> 0, 100.\r\n - Added check 61 for unusual SQL Server editions (not Standard, Enterprise, or\r\n Developer)\r\n - Added limitations note to point out that compatibility mode 80 won't work.\r\n - Fixed a bug where changes in sp_configure weren't always reported.\r\n\r\nChanges in v9:\r\n - Alex Pixley fixed a spelling typo.\r\n - Steinar Anderson http:\/\/www.sqlservice.se fixed a date bug in checkid 2.\r\n That bug was reported by several users, but Steinar coded the fix.\r\n - Stephen Schissler added a filter for checkid 2 (missing log backups) to look\r\n only for databases where source_database_id is null because these are\r\n database snapshots, and you can't run transaction log backups on snapshots.\r\n - Mark Fleming @markflemingnl added checkid 62 looking for disabled alerts.\r\n - Checkid 17 typo changed from "disabled" to "enabled" - the check\r\n functionality was right, but it was warning that auto update stats async\r\n was "disabled". Disabled is actually the default, but the check was\r\n firing because it had been enabled. (This one was reported by many.)\r\n\r\nChanges in v8 May 10 2012:\r\n - Switched more-details URLs to be short. This way they'll render better\r\n when viewed in our SQL Server Management Studio reports.\r\n - Removed ?VersionNumber querystring parameter to shorten links in SSMS.\r\n - Eliminated duplicate check for startup stored procedures.\r\n\r\nChanges in v7 April 30 2012:\r\n - Thomas Rushton http:\/\/thelonedba.wordpress.com\/ @ThomasRushton added check\r\n 58 for database collations that don't match the server collation.\r\n - Rob Pellicaan caught a bug in check 13: it was only checking for plan guides\r\n in the master database rather than all user databases.\r\n - Michal Tinthofer http:\/\/www.woodler.eu improved check 2 to work across\r\n collations and fix a bug in the backup_finish_date check. (Several people\r\n reported this, but Michal contributed the most improvements to this check.)\r\n - Chris Fradenburg improved checks 38 and 39 by excluding heaps if they are\r\n marked is_ms_shipped, thereby excluding more system stuff.\r\n - Jack Whittaker fixed a bug in checkid 1. When checking for databases\r\n without a full backup, we were ignoring the model database, but some shops\r\n really do need to back up model because they put stuff in there to be\r\n copied into each new database, so let's alert on that too. Larry Silverman\r\n also noticed this bug.\r\n - Michael Burgess caught a bug in the untrusted key\/constraint checks that\r\n were not checking for is_disabled = 0.\r\n - Alex Friedman fixed a bug in check 44 which required a running trace.\r\n - New check for SQL Agent alerts configured without operator notifications.\r\n - Even if @CheckUserDatabaseObjects was set to 0, some user database object\r\n checks were being done.\r\n - Check 48 for untrusted foreign keys now just returns one line per database\r\n that has the issue rather than listing every foreign key individually. For\r\n the full list of untrusted keys, run the query in the finding's URL.\r\n\r\nChanges in v6 Dec 26 2011:\r\n - Jonathan Allen @FatherJack suggested tweaking sp_BlitzUpdate's error message\r\n about Ad Hoc Queries not being enabled so that it also includes\r\n instructions on how to disable them again after temporarily enabling\r\n it to update sp_Blitz. \r\n\r\nChanges in v5 Dec 18 2011:\r\n - John Miner suggested tweaking checkid 48 and 56, the untrusted constraints\r\n and keys, to look for is_not_for_replication = 0 too. This filters out\r\n constraints\/keys that are only used for replication and don't need to\r\n be trusted.\r\n - Ned Otter caught a bug in the URL for check 7, startup stored procs.\r\n - Scott (Anon) recommended using SUSER_SNAME(0x01) instead of 'sa' when\r\n checking for job ownership, database ownership, etc.\r\n - Martin Schmidt http:\/\/www.geniiius.com\/blog\/ caught a bug in checkid 1 and\r\n contributed code to catch databases that had never been backed up.\r\n - Added parameter for @CheckProcedureCache. When set to 0, we skip the checks\r\n that are typically the slowest on servers with lots of memory. I'm\r\n defaulting this to 0 so more users can get results back faster.\r\n\r\nChanges in v4 Nov 1 2011:\r\n - Andreas Schubert caught a typo in the explanations for checks 15-17.\r\n - K. Brian Kelley @kbriankelley added checkid 57 for SQL Agent jobs set to\r\n start automatically on startup.\r\n - Added parameter for @CheckUserDatabaseObjects. When set to 0, we skip the\r\n checks that are typically the slowest on large servers, the user\r\n database schema checks for things like triggers, hypothetical\r\n indexes, untrusted constraints, etc.\r\n\r\nChanges in v3 Oct 16 2011:\r\n - David Tolbert caught a bug in checkid 2. If some backups had failed or\r\n been aborted, we raised a false alarm about no transaction log backups.\r\n - Fixed more bugs in checking for SQL Server 2005. (I need more 2005 VMs!)\r\n\r\nChanges in v2 Oct 14 2011:\r\n - Ali Razeghi http:\/\/www.alirazeghi.com added checkid 55 looking for\r\n databases owned by <> SA.\r\n - Fixed bugs in checking for SQL Server 2005 (leading % signs)\r\n\r\n*\/\r\n\r\n IF OBJECT_ID('tempdb..#BlitzResults') IS NOT NULL \r\n DROP TABLE #BlitzResults;\r\n CREATE TABLE #BlitzResults\r\n (\r\n ID INT IDENTITY(1, 1) ,\r\n CheckID INT ,\r\n Priority TINYINT ,\r\n FindingsGroup VARCHAR(50) ,\r\n Finding VARCHAR(200) ,\r\n URL VARCHAR(200) ,\r\n Details NVARCHAR(4000) ,\r\n QueryPlan [XML] NULL ,\r\n QueryPlanFiltered [NVARCHAR](MAX) NULL\r\n );\r\n\r\n IF OBJECT_ID('tempdb..#ConfigurationDefaults') IS NOT NULL \r\n DROP TABLE #ConfigurationDefaults;\r\n CREATE TABLE #ConfigurationDefaults\r\n (\r\n name NVARCHAR(128) ,\r\n DefaultValue BIGINT\r\n );\r\n\r\n IF @CheckProcedureCache = 1 \r\n BEGIN\r\n IF OBJECT_ID('tempdb..#dm_exec_query_stats') IS NOT NULL \r\n DROP TABLE #dm_exec_query_stats;\r\n CREATE TABLE #dm_exec_query_stats\r\n (\r\n [id] [int] NOT NULL\r\n IDENTITY(1, 1) ,\r\n [sql_handle] [varbinary](64) NOT NULL ,\r\n [statement_start_offset] [int] NOT NULL ,\r\n [statement_end_offset] [int] NOT NULL ,\r\n [plan_generation_num] [bigint] NOT NULL ,\r\n [plan_handle] [varbinary](64) NOT NULL ,\r\n [creation_time] [datetime] NOT NULL ,\r\n [last_execution_time] [datetime] NOT NULL ,\r\n [execution_count] [bigint] NOT NULL ,\r\n [total_worker_time] [bigint] NOT NULL ,\r\n [last_worker_time] [bigint] NOT NULL ,\r\n [min_worker_time] [bigint] NOT NULL ,\r\n [max_worker_time] [bigint] NOT NULL ,\r\n [total_physical_reads] [bigint] NOT NULL ,\r\n [last_physical_reads] [bigint] NOT NULL ,\r\n [min_physical_reads] [bigint] NOT NULL ,\r\n [max_physical_reads] [bigint] NOT NULL ,\r\n [total_logical_writes] [bigint] NOT NULL ,\r\n [last_logical_writes] [bigint] NOT NULL ,\r\n [min_logical_writes] [bigint] NOT NULL ,\r\n [max_logical_writes] [bigint] NOT NULL ,\r\n [total_logical_reads] [bigint] NOT NULL ,\r\n [last_logical_reads] [bigint] NOT NULL ,\r\n [min_logical_reads] [bigint] NOT NULL ,\r\n [max_logical_reads] [bigint] NOT NULL ,\r\n [total_clr_time] [bigint] NOT NULL ,\r\n [last_clr_time] [bigint] NOT NULL ,\r\n [min_clr_time] [bigint] NOT NULL ,\r\n [max_clr_time] [bigint] NOT NULL ,\r\n [total_elapsed_time] [bigint] NOT NULL ,\r\n [last_elapsed_time] [bigint] NOT NULL ,\r\n [min_elapsed_time] [bigint] NOT NULL ,\r\n [max_elapsed_time] [bigint] NOT NULL ,\r\n [query_hash] [binary](8) NULL ,\r\n [query_plan_hash] [binary](8) NULL ,\r\n [query_plan] [xml] NULL ,\r\n [query_plan_filtered] [nvarchar](MAX) NULL ,\r\n [text] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS\r\n NULL ,\r\n [text_filtered] [nvarchar](MAX)\r\n COLLATE SQL_Latin1_General_CP1_CI_AS\r\n NULL\r\n )\r\n\t\r\n END\r\n\r\n DECLARE @StringToExecute NVARCHAR(4000);\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 1 AS CheckID ,\r\n 1 AS Priority ,\r\n 'Backup' AS FindingsGroup ,\r\n 'Backups Not Performed Recently' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/nobak' AS URL ,\r\n 'Database ' + d.Name + ' last backed up: '\r\n + CAST(COALESCE(MAX(b.backup_finish_date), ' never ') AS VARCHAR(200)) AS Details\r\n FROM master.sys.databases d\r\n LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name\r\n AND b.type = 'D'\r\n AND b.server_name = @@SERVERNAME \/*Backupset ran on current server *\/\r\n WHERE d.database_id <> 2 \/* Bonus points if you know what that means *\/\r\n AND d.state <> 1 \/* Not currently restoring, like log shipping databases *\/\r\n AND d.is_in_standby = 0 \/* Not a log shipping target database *\/\r\n AND d.source_database_id IS NULL \/* Excludes database snapshots *\/\r\n GROUP BY d.name\r\n HAVING MAX(b.backup_finish_date) <= DATEADD(dd, -7, GETDATE());\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n \r\n )\r\n SELECT 1 AS CheckID ,\r\n 1 AS Priority ,\r\n 'Backup' AS FindingsGroup ,\r\n 'Backups Not Performed Recently' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/nobak' AS URL ,\r\n ( 'Database ' + d.Name + ' never backed up.' ) AS Details\r\n FROM master.sys.databases d\r\n WHERE d.database_id <> 2 \/* Bonus points if you know what that means *\/\r\n AND d.state <> 1 \/* Not currently restoring, like log shipping databases *\/\r\n AND d.is_in_standby = 0 \/* Not a log shipping target database *\/\r\n AND d.source_database_id IS NULL \/* Excludes database snapshots *\/\r\n AND NOT EXISTS ( SELECT *\r\n FROM msdb.dbo.backupset b\r\n WHERE d.name = b.database_name\r\n AND b.type = 'D'\r\n AND b.server_name = @@SERVERNAME \/*Backupset ran on current server *\/)\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT DISTINCT\r\n 2 AS CheckID ,\r\n 1 AS Priority ,\r\n 'Backup' AS FindingsGroup ,\r\n 'Full Recovery Mode w\/o Log Backups' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/biglogs' AS URL ,\r\n ( 'Database ' + ( d.Name COLLATE database_default )\r\n + ' is in ' + d.recovery_model_desc\r\n + ' recovery mode but has not had a log backup in the last week.' ) AS Details\r\n FROM master.sys.databases d\r\n WHERE d.recovery_model IN ( 1, 2 )\r\n AND d.database_id NOT IN ( 2, 3 )\r\n AND d.source_database_id IS NULL\r\n AND d.state <> 1 \/* Not currently restoring, like log shipping databases *\/\r\n AND d.is_in_standby = 0 \/* Not a log shipping target database *\/\r\n AND d.source_database_id IS NULL \/* Excludes database snapshots *\/\r\n AND NOT EXISTS ( SELECT *\r\n FROM msdb.dbo.backupset b\r\n WHERE d.name = b.database_name\r\n AND b.type = 'L'\r\n AND b.backup_finish_date >= DATEADD(dd,\r\n -7, GETDATE()) );\r\n\r\n\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT TOP 1\r\n 3 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Backup' AS FindingsGroup ,\r\n 'MSDB Backup History Not Purged' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/history' AS URL ,\r\n ( 'Database backup history retained back to '\r\n + CAST(bs.backup_start_date AS VARCHAR(20)) ) AS Details\r\n FROM msdb.dbo.backupset bs\r\n WHERE bs.backup_start_date <= DATEADD(dd, -60, GETDATE())\r\n ORDER BY backup_set_id ASC;\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 4 AS CheckID ,\r\n 10 AS Priority ,\r\n 'Security' AS FindingsGroup ,\r\n 'Sysadmins' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/sa' AS URL ,\r\n ( 'Login [' + l.name\r\n + '] is a sysadmin - meaning they can do absolutely anything in SQL Server, including dropping databases or hiding their tracks.' ) AS Details\r\n FROM master.sys.syslogins l\r\n WHERE l.sysadmin = 1\r\n AND l.name <> SUSER_SNAME(0x01)\r\n AND l.denylogin = 0;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 5 AS CheckID ,\r\n 10 AS Priority ,\r\n 'Security' AS FindingsGroup ,\r\n 'Security Admins' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/sa' AS URL ,\r\n ( 'Login [' + l.name\r\n + '] is a security admin - meaning they can give themselves permission to do absolutely anything in SQL Server, including dropping databases or hiding their tracks.' ) AS Details\r\n FROM master.sys.syslogins l\r\n WHERE l.securityadmin = 1\r\n AND l.name <> SUSER_SNAME(0x01)\r\n AND l.denylogin = 0;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 6 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Security' AS FindingsGroup ,\r\n 'Jobs Owned By Users' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/owners' AS URL ,\r\n ( 'Job [' + j.name + '] is owned by [' + sl.name\r\n + '] - meaning if their login is disabled or not available due to Active Directory problems, the job will stop working.' ) AS Details\r\n FROM msdb.dbo.sysjobs j\r\n LEFT OUTER JOIN sys.server_principals sl ON j.owner_sid = sl.sid\r\n WHERE j.enabled = 1\r\n AND sl.name <> SUSER_SNAME(0x01);\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 7 AS CheckID ,\r\n 10 AS Priority ,\r\n 'Security' AS FindingsGroup ,\r\n 'Stored Procedure Runs at Startup' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/startup' AS URL ,\r\n ( 'Stored procedure [master].[' + r.SPECIFIC_SCHEMA\r\n + '].[' + r.SPECIFIC_NAME\r\n + '] runs automatically when SQL Server starts up. Make sure you know exactly what this stored procedure is doing, because it could pose a security risk.' ) AS Details\r\n FROM master.INFORMATION_SCHEMA.ROUTINES r\r\n WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'ExecIsStartup') = 1;\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'\r\n AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%' \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\nSELECT 8 AS CheckID, 150 AS Priority, ''Security'' AS FindingsGroup, ''Server Audits Running'' AS Finding, \r\n ''http:\/\/BrentOzar.com\/go\/audits'' AS URL,\r\n (''SQL Server built-in audit functionality is being used by server audit: '' + [name]) AS Details FROM sys.dm_server_audit_status'\r\n EXECUTE(@StringToExecute)\r\n END;\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%' \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\nSELECT 9 AS CheckID, 200 AS Priority, ''Surface Area'' AS FindingsGroup, ''Endpoints Configured'' AS Finding, \r\n ''http:\/\/BrentOzar.com\/go\/endpoints\/'' AS URL,\r\n (''SQL Server endpoints are configured. These can be used for database mirroring or Service Broker, but if you do not need them, avoid leaving them enabled. Endpoint name: '' + [name]) AS Details FROM sys.endpoints WHERE type <> 2'\r\n EXECUTE(@StringToExecute)\r\n END;\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'\r\n AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%' \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\nSELECT 10 AS CheckID, 100 AS Priority, ''Performance'' AS FindingsGroup, ''Resource Governor Enabled'' AS Finding, \r\n ''http:\/\/BrentOzar.com\/go\/rg'' AS URL,\r\n (''Resource Governor is enabled. Queries may be throttled. Make sure you understand how the Classifier Function is configured.'') AS Details FROM sys.resource_governor_configuration WHERE is_enabled = 1'\r\n EXECUTE(@StringToExecute)\r\n END;\r\n\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%' \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\nSELECT 11 AS CheckID, 100 AS Priority, ''Performance'' AS FindingsGroup, ''Server Triggers Enabled'' AS Finding, \r\n ''http:\/\/BrentOzar.com\/go\/logontriggers\/'' AS URL,\r\n (''Server Trigger ['' + [name] ++ ''] is enabled, so it runs every time someone logs in. Make sure you understand what that trigger is doing - the less work it does, the better.'') AS Details FROM sys.server_triggers WHERE is_disabled = 0 AND is_ms_shipped = 0'\r\n EXECUTE(@StringToExecute)\r\n END;\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 12 AS CheckID ,\r\n 10 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Auto-Close Enabled' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/autoclose' AS URL ,\r\n ( 'Database [' + [name]\r\n + '] has auto-close enabled. This setting can dramatically decrease performance.' ) AS Details\r\n FROM sys.databases\r\n WHERE is_auto_close_on = 1;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 12 AS CheckID ,\r\n 10 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Auto-Shrink Enabled' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/autoshrink' AS URL ,\r\n ( 'Database [' + [name]\r\n + '] has auto-shrink enabled. This setting can dramatically decrease performance.' ) AS Details\r\n FROM sys.databases\r\n WHERE is_auto_shrink_on = 1;\r\n\r\n\r\n IF @@VERSION LIKE '%Microsoft SQL Server 2000%' \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\nSELECT 14 AS CheckID, 50 AS Priority, ''Reliability'' AS FindingsGroup, ''Page Verification Not Optimal'' AS Finding, \r\n ''http:\/\/BrentOzar.com\/go\/torn'' AS URL,\r\n (''Database ['' + [name] + ''] has '' + [page_verify_option_desc] + '' for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.'') COLLATE database_default AS Details FROM sys.databases WHERE page_verify_option < 1 AND name <> ''tempdb'''\r\n EXECUTE(@StringToExecute)\r\n END;\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%' \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\nSELECT 14 AS CheckID, 50 AS Priority, ''Reliability'' AS FindingsGroup, ''Page Verification Not Optimal'' AS Finding, \r\n ''http:\/\/BrentOzar.com\/go\/torn'' AS URL,\r\n (''Database ['' + [name] + ''] has '' + [page_verify_option_desc] + '' for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.'') AS Details FROM sys.databases WHERE page_verify_option < 2 AND name <> ''tempdb'''\r\n EXECUTE(@StringToExecute)\r\n END;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 15 AS CheckID ,\r\n 110 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Auto-Create Stats Disabled' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/acs' AS URL ,\r\n ( 'Database [' + [name]\r\n + '] has auto-create-stats disabled. SQL Server uses statistics to build better execution plans, and without the ability to automatically create more, performance may suffer.' ) AS Details\r\n FROM sys.databases\r\n WHERE is_auto_create_stats_on = 0;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 16 AS CheckID ,\r\n 110 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Auto-Update Stats Disabled' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/aus' AS URL ,\r\n ( 'Database [' + [name]\r\n + '] has auto-update-stats disabled. SQL Server uses statistics to build better execution plans, and without the ability to automatically update them, performance may suffer.' ) AS Details\r\n FROM sys.databases\r\n WHERE is_auto_update_stats_on = 0;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 17 AS CheckID ,\r\n 110 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Stats Updated Asynchronously' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/asyncstats' AS URL ,\r\n ( 'Database [' + [name]\r\n + '] has auto-update-stats-async enabled. When SQL Server gets a query for a table with out-of-date statistics, it will run the query with the stats it has - while updating stats to make later queries better. The initial run of the query may suffer, though.' ) AS Details\r\n FROM sys.databases\r\n WHERE is_auto_update_stats_async_on = 1;\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 18 AS CheckID ,\r\n 110 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Forced Parameterization On' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/forced' AS URL ,\r\n ( 'Database [' + [name]\r\n + '] has forced parameterization enabled. SQL Server will aggressively reuse query execution plans even if the applications do not parameterize their queries. This can be a performance booster with some programming languages, or it may use universally bad execution plans when better alternatives are available for certain parameters.' ) AS Details\r\n FROM sys.databases\r\n WHERE is_parameterization_forced = 1;\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 19 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Informational' AS FindingsGroup ,\r\n 'Replication In Use' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/repl' AS URL ,\r\n ( 'Database [' + [name]\r\n + '] is a replication publisher, subscriber, or distributor.' ) AS Details\r\n FROM sys.databases\r\n WHERE is_published = 1\r\n OR is_subscribed = 1\r\n OR is_merge_published = 1\r\n OR is_distributor = 1;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 20 AS CheckID ,\r\n 110 AS Priority ,\r\n 'Informational' AS FindingsGroup ,\r\n 'Date Correlation On' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/corr' AS URL ,\r\n ( 'Database [' + [name]\r\n + '] has date correlation enabled. This is not a default setting, and it has some performance overhead. It tells SQL Server that date fields in two tables are related, and SQL Server maintains statistics showing that relation.' ) AS Details\r\n FROM sys.databases\r\n WHERE is_date_correlation_on = 1;\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'\r\n AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%' \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\nSELECT 21 AS CheckID, 20 AS Priority, ''Encryption'' AS FindingsGroup, ''Database Encrypted'' AS Finding, \r\n ''http:\/\/BrentOzar.com\/go\/tde'' AS URL,\r\n (''Database ['' + [name] + ''] has Transparent Data Encryption enabled. Make absolutely sure you have backed up the certificate and private key, or else you will not be able to restore this database.'') AS Details FROM sys.databases WHERE is_encrypted = 1'\r\n EXECUTE(@StringToExecute)\r\n END;\r\n\r\n\/* Compare sp_configure defaults *\/\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'Ad Hoc Distributed Queries', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'affinity I\/O mask', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'affinity mask', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'Agent XPs', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'allow updates', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'awe enabled', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'blocked process threshold', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'c2 audit mode', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'clr enabled', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'cost threshold for parallelism', 5 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'cross db ownership chaining', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'cursor threshold', -1 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'Database Mail XPs', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'default full-text language', 1033 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'default language', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'default trace enabled', 1 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'disallow results from triggers', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'fill factor (%)', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'ft crawl bandwidth (max)', 100 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'ft crawl bandwidth (min)', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'ft notify bandwidth (max)', 100 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'ft notify bandwidth (min)', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'index create memory (KB)', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'in-doubt xact resolution', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'lightweight pooling', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'locks', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'max degree of parallelism', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'max full-text crawl range', 4 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'max server memory (MB)', 2147483647 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'max text repl size (B)', 65536 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'max worker threads', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'media retention', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'min memory per query (KB)', 1024 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'min server memory (MB)', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'nested triggers', 1 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'network packet size (B)', 4096 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'Ole Automation Procedures', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'open objects', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'optimize for ad hoc workloads', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'PH timeout (s)', 60 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'precompute rank', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'priority boost', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'query governor cost limit', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'query wait (s)', -1 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'recovery interval (min)', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'remote access', 1 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'remote admin connections', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'remote login timeout (s)', 20 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'remote proc trans', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'remote query timeout (s)', 600 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'Replication XPs', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'RPC parameter data validation', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'scan for startup procs', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'server trigger recursion', 1 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'set working set size', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'show advanced options', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'SMO and DMO XPs', 1 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'SQL Mail XPs', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'transform noise words', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'two digit year cutoff', 2049 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'user connections', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'user options', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'Web Assistant Procedures', 0 );\r\n INSERT INTO #ConfigurationDefaults\r\n VALUES ( 'xp_cmdshell', 0 );\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 22 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Non-Default Server Config' AS FindingsGroup ,\r\n cd.name AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/conf' AS URL ,\r\n ( 'This sp_configure option has been changed. Its default value is '\r\n + CAST(cd.[DefaultValue] AS VARCHAR(100))\r\n + ' and it has been set to '\r\n + CAST(cr.value_in_use AS VARCHAR(100)) + '.' ) AS Details\r\n FROM #ConfigurationDefaults cd\r\n INNER JOIN sys.configurations cr ON cd.name = cr.name\r\n WHERE cd.DefaultValue <> cr.value_in_use;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT DISTINCT\r\n 24 AS CheckID ,\r\n 20 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'System Database on C Drive' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/drivec' AS URL ,\r\n ( 'The ' + DB_NAME(database_id)\r\n + ' database has a file on the C drive. Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.' ) AS Details\r\n FROM sys.master_files\r\n WHERE UPPER(LEFT(physical_name, 1)) = 'C'\r\n AND DB_NAME(database_id) IN ( 'master', 'model', 'msdb' );\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT TOP 1\r\n 25 AS CheckID ,\r\n 100 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'TempDB on C Drive' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/drivec' AS URL ,\r\n CASE WHEN growth > 0\r\n THEN ( 'The tempdb database has files on the C drive. TempDB frequently grows unpredictably, putting your server at risk of running out of C drive space and crashing hard. C is also often much slower than other drives, so performance may be suffering.' )\r\n ELSE ( 'The tempdb database has files on the C drive. TempDB is not set to Autogrow, hopefully it is big enough. C is also often much slower than other drives, so performance may be suffering.' )\r\n END AS Details\r\n FROM sys.master_files\r\n WHERE UPPER(LEFT(physical_name, 1)) = 'C'\r\n AND DB_NAME(database_id) = 'tempdb';\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT DISTINCT\r\n 26 AS CheckID ,\r\n 20 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'User Databases on C Drive' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/cdrive' AS URL ,\r\n ( 'The ' + DB_NAME(database_id)\r\n + ' database has a file on the C drive. Putting databases on the C drive runs the risk of crashing the server when it runs out of space.' ) AS Details\r\n FROM sys.master_files\r\n WHERE UPPER(LEFT(physical_name, 1)) = 'C'\r\n AND DB_NAME(database_id) NOT IN ( 'master', 'model',\r\n 'msdb', 'tempdb' );\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 27 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Informational' AS FindingsGroup ,\r\n 'Tables in the Master Database' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/mastuser' AS URL ,\r\n ( 'The ' + name\r\n + ' table in the master database was created by end users on '\r\n + CAST(create_date AS VARCHAR(20))\r\n + '. Tables in the master database may not be restored in the event of a disaster.' ) AS Details\r\n FROM master.sys.tables\r\n WHERE is_ms_shipped = 0;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 28 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Informational' AS FindingsGroup ,\r\n 'Tables in the MSDB Database' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/msdbuser' AS URL ,\r\n ( 'The ' + name\r\n + ' table in the msdb database was created by end users on '\r\n + CAST(create_date AS VARCHAR(20))\r\n + '. Tables in the msdb database may not be restored in the event of a disaster.' ) AS Details\r\n FROM msdb.sys.tables\r\n WHERE is_ms_shipped = 0;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 29 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Informational' AS FindingsGroup ,\r\n 'Tables in the Model Database' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/model' AS URL ,\r\n ( 'The ' + name\r\n + ' table in the model database was created by end users on '\r\n + CAST(create_date AS VARCHAR(20))\r\n + '. Tables in the model database are automatically copied into all new databases.' ) AS Details\r\n FROM model.sys.tables\r\n WHERE is_ms_shipped = 0;\r\n\r\n\r\n IF ( SELECT COUNT(*)\r\n FROM msdb.dbo.sysalerts\r\n WHERE severity BETWEEN 19 AND 25\r\n ) < 7 \r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 30 AS CheckID ,\r\n 50 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'Not All Alerts Configured' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/alert' AS URL ,\r\n ( 'Not all SQL Server Agent alerts have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.' ) AS Details;\r\n \r\n IF EXISTS ( SELECT *\r\n FROM msdb.dbo.sysalerts\r\n WHERE enabled = 1\r\n AND COALESCE(has_notification, 0) = 0\r\n AND job_id IS NULL ) \r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 59 AS CheckID ,\r\n 50 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'Alerts Configured without Follow Up' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/alert' AS URL ,\r\n ( 'SQL Server Agent alerts have been configured but they either do not notify anyone or else they do not take any action. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.' ) AS Details;\r\n\r\n IF NOT EXISTS ( SELECT *\r\n FROM msdb.dbo.sysalerts\r\n WHERE message_id IN ( 823, 824, 825 ) ) \r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 60 AS CheckID ,\r\n 50 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'No Alerts for Corruption' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/alert' AS URL ,\r\n ( 'SQL Server Agent alerts do not exist for errors 823, 824, and 825. These three errors can give you notification about early hardware failure. Enabling them can prevent you a lot of heartbreak.' ) AS Details;\r\n\r\n IF NOT EXISTS ( SELECT *\r\n FROM msdb.dbo.sysalerts\r\n WHERE severity BETWEEN 19 AND 25 ) \r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 61 AS CheckID ,\r\n 50 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'No Alerts for Sev 19-25' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/alert' AS URL ,\r\n ( 'SQL Server Agent alerts do not exist for severity levels 19 through 25. These are some very severe SQL Server errors. Knowing that these are happening may let you recover from errors faster.' ) AS Details;\r\n\r\n --check for disabled alerts\r\n IF EXISTS ( SELECT name\r\n FROM msdb.dbo.sysalerts\r\n WHERE enabled = 0 ) \r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n \r\n )\r\n SELECT 62 AS CheckID ,\r\n 50 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'Alerts Disabled' AS Finding ,\r\n 'http:\/\/www.BrentOzar.com\/go\/alerts\/' AS URL ,\r\n ( 'The following Alert is disabled, please review and enable if desired: '\r\n + name ) AS Details\r\n FROM msdb.dbo.sysalerts\r\n WHERE enabled = 0\r\n\r\n\r\n IF NOT EXISTS ( SELECT *\r\n FROM msdb.dbo.sysoperators\r\n WHERE enabled = 1 ) \r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 31 AS CheckID ,\r\n 50 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'No Operators Configured\/Enabled' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/op' AS URL ,\r\n ( 'No SQL Server Agent operators (emails) have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.' ) AS Details;\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'\r\n AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%' \r\n BEGIN\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 33, 200, ''Licensing'', ''Enterprise Edition Features In Use'', ''http:\/\/BrentOzar.com\/go\/ee'', (''The ['' + DB_NAME() + ''] database is using '' + feature_name + ''. If this database is restored onto a Standard Edition server, the restore will fail.'') FROM [?].sys.dm_db_persisted_sku_features';\r\n END;\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'\r\n AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%' \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\n SELECT TOP 1\r\n 34 AS CheckID ,\r\n 1 AS Priority ,\r\n ''Corruption'' AS FindingsGroup ,\r\n ''Database Corruption Detected'' AS Finding ,\r\n ''http:\/\/BrentOzar.com\/go\/repair'' AS URL ,\r\n ( ''Database mirroring has automatically repaired at least one corrupt page in the last 30 days. For more information, query the DMV sys.dm_db_mirroring_auto_page_repair.'' ) AS Details\r\n FROM sys.dm_db_mirroring_auto_page_repair\r\n WHERE modification_time >= DATEADD(dd, -30, GETDATE()) ;'\r\n EXECUTE(@StringToExecute)\r\n END;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT DISTINCT\r\n 36 AS CheckID ,\r\n 100 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Slow Storage Reads on Drive '\r\n + UPPER(LEFT(mf.physical_name, 1)) AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/slow' AS URL ,\r\n 'Reads are averaging longer than 100ms for at least one database on this drive. For specific database file speeds, run the query from the information link.' AS Details\r\n FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs\r\n INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id\r\n AND fs.[file_id] = mf.[file_id]\r\n WHERE ( io_stall_read_ms \/ ( 1.0 + num_of_reads ) ) > 100;\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT DISTINCT\r\n 37 AS CheckID ,\r\n 100 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Slow Storage Writes on Drive '\r\n + UPPER(LEFT(mf.physical_name, 1)) AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/slow' AS URL ,\r\n 'Writes are averaging longer than 20ms for at least one database on this drive. For specific database file speeds, run the query from the information link.' AS Details\r\n FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs\r\n INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id\r\n AND fs.[file_id] = mf.[file_id]\r\n WHERE ( io_stall_write_ms \/ ( 1.0 + num_of_writes ) ) > 20;\r\n\r\n\r\n IF ( SELECT COUNT(*)\r\n FROM tempdb.sys.database_files\r\n WHERE type_desc = 'ROWS'\r\n ) = 1 \r\n BEGIN\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n VALUES ( 40 ,\r\n 100 ,\r\n 'Performance' ,\r\n 'TempDB Only Has 1 Data File' ,\r\n 'http:\/\/BrentOzar.com\/go\/tempdb' ,\r\n 'TempDB is only configured with one data file. More data files are usually required to alleviate SGAM contention.'\r\n );\r\n END;\r\n\r\n EXEC dbo.sp_MSforeachdb 'use [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT 41, 100, ''Performance'', ''Multiple Log Files on One Drive'', ''http:\/\/BrentOzar.com\/go\/manylogs'', (''The ['' + DB_NAME() + ''] database has multiple log files on the '' + LEFT(physical_name, 1) + '' drive. This is not a performance booster because log file access is sequential, not parallel.'') FROM [?].sys.database_files WHERE type_desc = ''LOG'' AND ''?'' <> ''[tempdb]'' GROUP BY LEFT(physical_name, 1) HAVING COUNT(*) > 1';\r\n\r\n EXEC dbo.sp_MSforeachdb 'use [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 42, 100, ''Performance'', ''Uneven File Growth Settings in One Filegroup'', ''http:\/\/BrentOzar.com\/go\/grow'', (''The ['' + DB_NAME() + ''] database has multiple data files in one filegroup, but they are not all set up to grow in identical amounts. This can lead to uneven file activity inside the filegroup.'') FROM [?].sys.database_files WHERE type_desc = ''ROWS'' GROUP BY data_space_id HAVING COUNT(DISTINCT growth) > 1 OR COUNT(DISTINCT is_percent_growth) > 1';\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n \r\n )\r\n SELECT 44 AS CheckID ,\r\n 110 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Queries Forcing Order Hints' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/hints' AS URL ,\r\n CAST(occurrence AS VARCHAR(10))\r\n + ' instances of order hinting have been recorded since restart. This means queries are bossing the SQL Server optimizer around, and if they don''t know what they''re doing, this can cause more harm than good. This can also explain why DBA tuning efforts aren''t working.' AS Details\r\n FROM sys.dm_exec_query_optimizer_info\r\n WHERE counter = 'order hint'\r\n AND occurrence > 1\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 45 AS CheckID ,\r\n 110 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Queries Forcing Join Hints' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/hints' AS URL ,\r\n CAST(occurrence AS VARCHAR(10))\r\n + ' instances of join hinting have been recorded since restart. This means queries are bossing the SQL Server optimizer around, and if they don''t know what they''re doing, this can cause more harm than good. This can also explain why DBA tuning efforts aren''t working.' AS Details\r\n FROM sys.dm_exec_query_optimizer_info\r\n WHERE counter = 'join hint'\r\n AND occurrence > 1\r\n\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT DISTINCT\r\n 49 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Informational' AS FindingsGroup ,\r\n 'Linked Server Configured' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/link' AS URL ,\r\n +CASE WHEN l.remote_name = 'sa'\r\n THEN s.data_source\r\n + ' is configured as a linked server. Check its security configuration as it is connecting with sa, because any user who queries it will get admin-level permissions.'\r\n ELSE s.data_source\r\n + ' is configured as a linked server. Check its security configuration to make sure it isn''t connecting with SA or some other bone-headed administrative login, because any user who queries it might get admin-level permissions.'\r\n END AS Details\r\n FROM sys.servers s\r\n INNER JOIN sys.linked_logins l ON s.server_id = l.server_id\r\n WHERE s.is_linked = 1\r\n\r\n\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'\r\n AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%' \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\n SELECT 50 AS CheckID ,\r\n 100 AS Priority ,\r\n ''Performance'' AS FindingsGroup ,\r\n ''Max Memory Set Too High'' AS Finding ,\r\n ''http:\/\/BrentOzar.com\/go\/max'' AS URL ,\r\n ''SQL Server max memory is set to ''\r\n + CAST(c.value_in_use AS VARCHAR(20))\r\n + '' megabytes, but the server only has ''\r\n + CAST(( CAST(m.total_physical_memory_kb AS BIGINT) \/ 1024 ) AS VARCHAR(20))\r\n + '' megabytes. SQL Server may drain the system dry of memory, and under certain conditions, this can cause Windows to swap to disk.'' AS Details\r\n FROM sys.dm_os_sys_memory m\r\n INNER JOIN sys.configurations c ON c.name = ''max server memory (MB)''\r\n WHERE CAST(m.total_physical_memory_kb AS BIGINT) < ( CAST(c.value_in_use AS BIGINT) * 1024 )'\r\n EXECUTE(@StringToExecute)\r\n END;\r\n\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'\r\n AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%' \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\n SELECT 51 AS CheckID ,\r\n 1 AS Priority ,\r\n ''Performance'' AS FindingsGroup ,\r\n ''Memory Dangerously Low'' AS Finding ,\r\n ''http:\/\/BrentOzar.com\/go\/max'' AS URL ,\r\n ''Although available memory is ''\r\n + CAST(( CAST(m.available_physical_memory_kb AS BIGINT)\r\n \/ 1024 ) AS VARCHAR(20))\r\n + '' megabytes, only ''\r\n + CAST(( CAST(m.total_physical_memory_kb AS BIGINT) \/ 1024 ) AS VARCHAR(20))\r\n + ''megabytes of memory are present. As the server runs out of memory, there is danger of swapping to disk, which will kill performance.'' AS Details\r\n FROM sys.dm_os_sys_memory m\r\n WHERE CAST(m.available_physical_memory_kb AS BIGINT) < 262144'\r\n EXECUTE(@StringToExecute)\r\n END;\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT TOP 1\r\n 53 AS CheckID ,\r\n 200 AS Priority ,\r\n 'High Availability' AS FindingsGroup ,\r\n 'Cluster Node' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/node' AS URL ,\r\n 'This is a node in a cluster.' AS Details\r\n FROM sys.dm_os_cluster_nodes\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 55 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Security' AS FindingsGroup ,\r\n 'Database Owner <> SA' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/owndb' AS URL ,\r\n ( 'Database name: ' + name + ' ' + 'Owner name: '\r\n + SUSER_SNAME(owner_sid) ) AS Details\r\n FROM sys.databases\r\n WHERE SUSER_SNAME(owner_sid) <> SUSER_SNAME(0x01);\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 57 AS CheckID ,\r\n 10 AS Priority ,\r\n 'Security' AS FindingsGroup ,\r\n 'SQL Agent Job Runs at Startup' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/startup' AS URL ,\r\n ( 'Job ' + j.name\r\n + '] runs automatically when SQL Server Agent starts up. Make sure you know exactly what this job is doing, because it could pose a security risk.' ) AS Details\r\n FROM msdb.dbo.sysschedules sched\r\n JOIN msdb.dbo.sysjobschedules jsched ON sched.schedule_id = jsched.schedule_id\r\n JOIN msdb.dbo.sysjobs j ON jsched.job_id = j.job_id\r\n WHERE sched.freq_type = 64;\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 58 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'Database Collation Mismatch' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/collate' AS URL ,\r\n ( 'Database ' + d.NAME + ' has collation '\r\n + d.collation_name + '; Server collation is '\r\n + CONVERT(VARCHAR(100), SERVERPROPERTY('collation')) ) AS Details\r\n FROM master.sys.databases d\r\n WHERE d.collation_name <> SERVERPROPERTY('collation')\r\n\r\n EXEC sp_MSforeachdb 'use [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\nSELECT DISTINCT 82 AS CheckID, \r\n 100 AS Priority, \r\n ''Performance'' AS FindingsGroup, \r\n ''File growth set to percent'', \r\n ''http:\/\/brentozar.com\/go\/percentgrowth'' AS URL,\r\n ''The ['' + DB_NAME() + ''] database is using percent filegrowth settings. This can lead to out of control filegrowth.''\r\nFROM [?].sys.database_files \r\nWHERE is_percent_growth = 1 ';\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 61 AS CheckID ,\r\n 100 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Unusual SQL Server Edition' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/workgroup' AS URL ,\r\n ( 'This server is using '\r\n + CAST(SERVERPROPERTY('edition') AS VARCHAR(100))\r\n + ', which is capped at low amounts of CPU and memory.' ) AS Details\r\n WHERE CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Standard%'\r\n AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Enterprise%'\r\n AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Developer%'\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n\t \r\n )\r\n SELECT 62 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Old Compatibility Level' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/compatlevel' AS URL ,\r\n ( 'Database ' + name + ' is compatibility level '\r\n + CAST(compatibility_level AS VARCHAR(20))\r\n + ', which may cause unwanted results when trying to run queries that have newer T-SQL features.' ) AS Details\r\n FROM sys.databases\r\n WHERE compatibility_level <> ( SELECT compatibility_level\r\n FROM sys.databases\r\n WHERE name = 'model'\r\n )\r\n\t \r\n\t \r\n\t \r\n\r\n IF @CheckUserDatabaseObjects = 1 \r\n BEGIN\r\n\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 32, 110, ''Performance'', ''Triggers on Tables'', ''http:\/\/BrentOzar.com\/go\/trig'', (''The ['' + DB_NAME() + ''] database has triggers on the '' + s.name + ''.'' + o.name + '' table.'') FROM [?].sys.triggers t INNER JOIN [?].sys.objects o ON t.parent_id = o.object_id INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id WHERE t.is_ms_shipped = 0';\r\n\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 38, 110, ''Performance'', ''Active Tables Without Clustered Indexes'', ''http:\/\/BrentOzar.com\/go\/heaps'', (''The ['' + DB_NAME() + ''] database has heaps - tables without a clustered index - that are being actively queried.'') FROM [?].sys.indexes i INNER JOIN [?].sys.objects o ON i.object_id = o.object_id INNER JOIN [?].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.databases sd ON sd.name = ''?'' LEFT OUTER JOIN [?].sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = sd.database_id WHERE i.type_desc = ''HEAP'' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NOT NULL AND sd.name <> ''tempdb'' AND o.is_ms_shipped = 0 AND o.type <> ''S''';\r\n\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 39, 110, ''Performance'', ''Inactive Tables Without Clustered Indexes'', ''http:\/\/BrentOzar.com\/go\/heaps'', (''The ['' + DB_NAME() + ''] database has heaps - tables without a clustered index - that have not been queried since the last restart. These may be backup tables carelessly left behind.'') FROM [?].sys.indexes i INNER JOIN [?].sys.objects o ON i.object_id = o.object_id INNER JOIN [?].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.databases sd ON sd.name = ''?'' LEFT OUTER JOIN [?].sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = sd.database_id WHERE i.type_desc = ''HEAP'' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NULL AND sd.name <> ''tempdb'' AND o.is_ms_shipped = 0 AND o.type <> ''S''';\r\n\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT 46, 100, ''Performance'', ''Leftover Fake Indexes From Wizards'', ''http:\/\/BrentOzar.com\/go\/hypo'', (''The index ['' + DB_NAME() + ''].['' + s.name + ''].['' + o.name + ''].['' + i.name + ''] is a leftover hypothetical index from the Index Tuning Wizard or Database Tuning Advisor. This index is not actually helping performance and should be removed.'') from [?].sys.indexes i INNER JOIN [?].sys.objects o ON i.object_id = o.object_id INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_hypothetical = 1';\r\n\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT 47, 100, ''Performance'', ''Indexes Disabled'', ''http:\/\/BrentOzar.com\/go\/ixoff'', (''The index ['' + DB_NAME() + ''].['' + s.name + ''].['' + o.name + ''].['' + i.name + ''] is disabled. This index is not actually helping performance and should either be enabled or removed.'') from [?].sys.indexes i INNER JOIN [?].sys.objects o ON i.object_id = o.object_id INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_disabled = 1';\r\n\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 48, 100, ''Performance'', ''Foreign Keys Not Trusted'', ''http:\/\/BrentOzar.com\/go\/trust'', (''The ['' + DB_NAME() + ''] database has foreign keys that were probably disabled, data was changed, and then the key was enabled again. Simply enabling the key is not enough for the optimizer to use this key - we have to alter the table using the WITH CHECK CHECK CONSTRAINT parameter.'') from [?].sys.foreign_keys i INNER JOIN [?].sys.objects o ON i.parent_object_id = o.object_id INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0';\r\n\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT 56, 100, ''Performance'', ''Check Constraint Not Trusted'', ''http:\/\/BrentOzar.com\/go\/trust'', (''The check constraint ['' + DB_NAME() + ''].['' + s.name + ''].['' + o.name + ''].['' + i.name + ''] is not trusted - meaning, it was disabled, data was changed, and then the constraint was enabled again. Simply enabling the constraint is not enough for the optimizer to use this constraint - we have to alter the table using the WITH CHECK CHECK CONSTRAINT parameter.'') from [?].sys.check_constraints i INNER JOIN [?].sys.objects o ON i.parent_object_id = o.object_id INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0';\r\n\r\n IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'\r\n AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%' \r\n BEGIN\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT TOP 1 13 AS CheckID, 110 AS Priority, ''Performance'' AS FindingsGroup, ''Plan Guides Enabled'' AS Finding, ''http:\/\/BrentOzar.com\/go\/guides'' AS URL, (''Database ['' + DB_NAME() + ''] has query plan guides so a query will always get a specific execution plan. If you are having trouble getting query performance to improve, it might be due to a frozen plan. Review the DMV sys.plan_guides to learn more about the plan guides in place on this server.'') AS Details FROM [?].sys.plan_guides WHERE is_disabled = 0'\r\n END;\r\n\r\n EXEC sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\n\t\tSELECT DISTINCT 60 AS CheckID, \r\n\t\t 100 AS Priority, \r\n\t\t ''Performance'' AS FindingsGroup, \r\n\t\t ''Fill Factor Changed'', \r\n\t\t ''http:\/\/brentozar.com\/go\/fillfactor'' AS URL,\r\n\t\t ''The ['' + DB_NAME() + ''] database has objects with fill factor <> 0. This can cause memory and storage performance problems, but may also prevent page splits.''\r\n\t\tFROM [?].sys.indexes \r\n\t\tWHERE fill_factor <> 0 AND fill_factor <> 100 AND is_disabled = 0 AND is_hypothetical = 0';\r\n\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT 78, 100, ''Performance'', ''Stored Procedure WITH RECOMPILE'', ''http:\/\/BrentOzar.com\/go\/recompile'', (''['' + DB_NAME() + ''].['' + SPECIFIC_SCHEMA + ''].['' + SPECIFIC_NAME + ''] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.'') from [?].INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE N''%WITH RECOMPILE%''';\r\n\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 86, 20, ''Security'', ''Elevated Permissions on a Database'', ''http:\/\/BrentOzar.com\/go\/elevated'', (''In ['' + DB_NAME() + ''], user ['' + u.name + ''] has the role ['' + g.name + '']. This user can perform tasks beyond just reading and writing data.'') FROM [?].dbo.sysmembers m inner join [?].dbo.sysusers u on m.memberuid = u.uid inner join sysusers g on m.groupuid = g.uid where u.name <> ''dbo'' and g.name in (''db_owner'' , ''db_accessAdmin'' , ''db_securityadmin'' , ''db_ddladmin'')';\r\n\r\n\r\n\r\n END \/* IF @CheckUserDatabaseObjects = 1 *\/\r\n\r\n\r\n IF @CheckProcedureCache = 1 \r\n BEGIN\r\n\t\t\t\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n\t \r\n )\r\n SELECT 35 AS CheckID ,\r\n 100 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Single-Use Plans in Procedure Cache' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/single' AS URL ,\r\n ( CAST(COUNT(*) AS VARCHAR(10))\r\n + ' query plans are taking up memory in the procedure cache. This may be wasted memory if we cache plans for queries that never get called again. This may be a good use case for SQL Server 2008''s Optimize for Ad Hoc or for Forced Parameterization.' ) AS Details\r\n FROM sys.dm_exec_cached_plans AS cp\r\n WHERE cp.usecounts = 1\r\n AND cp.objtype = 'Adhoc'\r\n AND EXISTS ( SELECT 1\r\n FROM sys.configurations\r\n WHERE name = 'optimize for ad hoc workloads'\r\n AND value_in_use = 0 )\r\n HAVING COUNT(*) > 1;\r\n\r\n\r\n\t\t\t\t\/* Set up the cache tables. Different on 2005 since it doesn't support query_hash, query_plan_hash. *\/\r\n IF @@VERSION LIKE '%Microsoft SQL Server 2005%' \r\n BEGIN\r\n IF @CheckProcedureCacheFilter = 'CPU'\r\n OR @CheckProcedureCacheFilter IS NULL \r\n BEGIN\r\n SET @StringToExecute = 'WITH queries ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time])\r\n\t\t\t AS (SELECT TOP 20 qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time]\r\n\t\t\t\t\t\tFROM sys.dm_exec_query_stats qs\r\n\t\t\t\t\t\tORDER BY qs.total_worker_time DESC)\r\n\t\t\t\t\t\tINSERT INTO #dm_exec_query_stats ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time])\r\n\t\t\t\t\t\tSELECT qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time]\r\n\t\t\t\t\t\tFROM queries qs\r\n\t\t\t\t\t\tLEFT OUTER JOIN #dm_exec_query_stats qsCaught ON qs.sql_handle = qsCaught.sql_handle AND qs.plan_handle = qsCaught.plan_handle AND qs.statement_start_offset = qsCaught.statement_start_offset\r\n\t\t\t\t\t\tWHERE qsCaught.sql_handle IS NULL;'\r\n EXECUTE(@StringToExecute)\r\n END\r\n\r\n IF @CheckProcedureCacheFilter = 'Reads'\r\n OR @CheckProcedureCacheFilter IS NULL \r\n BEGIN\r\n SET @StringToExecute = 'WITH queries ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time])\r\n\t\t\t AS (SELECT TOP 20 qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time]\r\n\t\t\t\t\t\tFROM sys.dm_exec_query_stats qs\r\n\t\t\t\t\t\tORDER BY qs.total_logical_reads DESC)\r\n\t\t\t\t\t\tINSERT INTO #dm_exec_query_stats ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time])\r\n\t\t\t\t\t\tSELECT qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time]\r\n\t\t\t\t\t\tFROM queries qs\r\n\t\t\t\t\t\tLEFT OUTER JOIN #dm_exec_query_stats qsCaught ON qs.sql_handle = qsCaught.sql_handle AND qs.plan_handle = qsCaught.plan_handle AND qs.statement_start_offset = qsCaught.statement_start_offset\r\n\t\t\t\t\t\tWHERE qsCaught.sql_handle IS NULL;'\r\n END\r\n\r\n IF @CheckProcedureCacheFilter = 'ExecCount'\r\n OR @CheckProcedureCacheFilter IS NULL \r\n BEGIN\r\n SET @StringToExecute = 'WITH queries ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time])\r\n\t\t\t AS (SELECT TOP 20 qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time]\r\n\t\t\t\t\t\tFROM sys.dm_exec_query_stats qs\r\n\t\t\t\t\t\tORDER BY qs.execution_count DESC)\r\n\t\t\t\t\t\tINSERT INTO #dm_exec_query_stats ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time])\r\n\t\t\t\t\t\tSELECT qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time]\r\n\t\t\t\t\t\tFROM queries qs\r\n\t\t\t\t\t\tLEFT OUTER JOIN #dm_exec_query_stats qsCaught ON qs.sql_handle = qsCaught.sql_handle AND qs.plan_handle = qsCaught.plan_handle AND qs.statement_start_offset = qsCaught.statement_start_offset\r\n\t\t\t\t\t\tWHERE qsCaught.sql_handle IS NULL;'\r\n EXECUTE(@StringToExecute)\r\n END\r\n\r\n IF @CheckProcedureCacheFilter = 'Duration'\r\n OR @CheckProcedureCacheFilter IS NULL \r\n BEGIN\r\n SET @StringToExecute = 'WITH queries ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time])\r\n\t\t\t AS (SELECT TOP 20 qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time]\r\n\t\t\t\t\t\tFROM sys.dm_exec_query_stats qs\r\n\t\t\t\t\t\tORDER BY qs.total_elapsed_time DESC)\r\n\t\t\t\t\t\tINSERT INTO #dm_exec_query_stats ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time])\r\n\t\t\t\t\t\tSELECT qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time]\r\n\t\t\t\t\t\tFROM queries qs\r\n\t\t\t\t\t\tLEFT OUTER JOIN #dm_exec_query_stats qsCaught ON qs.sql_handle = qsCaught.sql_handle AND qs.plan_handle = qsCaught.plan_handle AND qs.statement_start_offset = qsCaught.statement_start_offset\r\n\t\t\t\t\t\tWHERE qsCaught.sql_handle IS NULL;'\r\n EXECUTE(@StringToExecute)\r\n END\r\n\r\n END;\r\n IF @@VERSION LIKE '%Microsoft SQL Server 2008%'\r\n OR @@VERSION LIKE '%Microsoft SQL Server 2012%' \r\n BEGIN\r\n IF @CheckProcedureCacheFilter = 'CPU'\r\n OR @CheckProcedureCacheFilter IS NULL \r\n BEGIN\r\n SET @StringToExecute = 'WITH queries ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time],[query_hash],[query_plan_hash])\r\n\t\t\t AS (SELECT TOP 20 qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time],qs.[query_hash],qs.[query_plan_hash]\r\n\t\t\t\t\t\tFROM sys.dm_exec_query_stats qs\r\n\t\t\t\t\t\tORDER BY qs.total_worker_time DESC)\r\n\t\t\t\t\t\tINSERT INTO #dm_exec_query_stats ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time],[query_hash],[query_plan_hash])\r\n\t\t\t\t\t\tSELECT qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time],qs.[query_hash],qs.[query_plan_hash]\r\n\t\t\t\t\t\tFROM queries qs\r\n\t\t\t\t\t\tLEFT OUTER JOIN #dm_exec_query_stats qsCaught ON qs.sql_handle = qsCaught.sql_handle AND qs.plan_handle = qsCaught.plan_handle AND qs.statement_start_offset = qsCaught.statement_start_offset\r\n\t\t\t\t\t\tWHERE qsCaught.sql_handle IS NULL;'\r\n EXECUTE(@StringToExecute)\r\n END\r\n\r\n IF @CheckProcedureCacheFilter = 'Reads'\r\n OR @CheckProcedureCacheFilter IS NULL \r\n BEGIN\r\n SET @StringToExecute = 'WITH queries ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time],[query_hash],[query_plan_hash])\r\n\t\t\t AS (SELECT TOP 20 qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time],qs.[query_hash],qs.[query_plan_hash]\r\n\t\t\t\t\t\tFROM sys.dm_exec_query_stats qs\r\n\t\t\t\t\t\tORDER BY qs.total_logical_reads DESC)\r\n\t\t\t\t\t\tINSERT INTO #dm_exec_query_stats ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time],[query_hash],[query_plan_hash])\r\n\t\t\t\t\t\tSELECT qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time],qs.[query_hash],qs.[query_plan_hash]\r\n\t\t\t\t\t\tFROM queries qs\r\n\t\t\t\t\t\tLEFT OUTER JOIN #dm_exec_query_stats qsCaught ON qs.sql_handle = qsCaught.sql_handle AND qs.plan_handle = qsCaught.plan_handle AND qs.statement_start_offset = qsCaught.statement_start_offset\r\n\t\t\t\t\t\tWHERE qsCaught.sql_handle IS NULL;'\r\n EXECUTE(@StringToExecute)\r\n END\r\n\t\r\n IF @CheckProcedureCacheFilter = 'ExecCount'\r\n OR @CheckProcedureCacheFilter IS NULL \r\n BEGIN\r\n SET @StringToExecute = 'WITH queries ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time],[query_hash],[query_plan_hash])\r\n\t\t\t AS (SELECT TOP 20 qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time],qs.[query_hash],qs.[query_plan_hash]\r\n\t\t\t\t\t\tFROM sys.dm_exec_query_stats qs\r\n\t\t\t\t\t\tORDER BY qs.execution_count DESC)\r\n\t\t\t\t\t\tINSERT INTO #dm_exec_query_stats ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time],[query_hash],[query_plan_hash])\r\n\t\t\t\t\t\tSELECT qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time],qs.[query_hash],qs.[query_plan_hash]\r\n\t\t\t\t\t\tFROM queries qs\r\n\t\t\t\t\t\tLEFT OUTER JOIN #dm_exec_query_stats qsCaught ON qs.sql_handle = qsCaught.sql_handle AND qs.plan_handle = qsCaught.plan_handle AND qs.statement_start_offset = qsCaught.statement_start_offset\r\n\t\t\t\t\t\tWHERE qsCaught.sql_handle IS NULL;'\r\n EXECUTE(@StringToExecute)\r\n END\r\n\r\n IF @CheckProcedureCacheFilter = 'Duration'\r\n OR @CheckProcedureCacheFilter IS NULL \r\n BEGIN\r\n SET @StringToExecute = 'WITH queries ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time],[query_hash],[query_plan_hash])\r\n\t\t\t AS (SELECT TOP 20 qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time],qs.[query_hash],qs.[query_plan_hash]\r\n\t\t\t\t\t\tFROM sys.dm_exec_query_stats qs\r\n\t\t\t\t\t\tORDER BY qs.total_elapsed_time DESC)\r\n\t\t\t\t\t\tINSERT INTO #dm_exec_query_stats ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time],[last_execution_time],[execution_count],[total_worker_time],[last_worker_time],[min_worker_time],[max_worker_time],[total_physical_reads],[last_physical_reads],[min_physical_reads],[max_physical_reads],[total_logical_writes],[last_logical_writes],[min_logical_writes],[max_logical_writes],[total_logical_reads],[last_logical_reads],[min_logical_reads],[max_logical_reads],[total_clr_time],[last_clr_time],[min_clr_time],[max_clr_time],[total_elapsed_time],[last_elapsed_time],[min_elapsed_time],[max_elapsed_time],[query_hash],[query_plan_hash])\r\n\t\t\t\t\t\tSELECT qs.[sql_handle],qs.[statement_start_offset],qs.[statement_end_offset],qs.[plan_generation_num],qs.[plan_handle],qs.[creation_time],qs.[last_execution_time],qs.[execution_count],qs.[total_worker_time],qs.[last_worker_time],qs.[min_worker_time],qs.[max_worker_time],qs.[total_physical_reads],qs.[last_physical_reads],qs.[min_physical_reads],qs.[max_physical_reads],qs.[total_logical_writes],qs.[last_logical_writes],qs.[min_logical_writes],qs.[max_logical_writes],qs.[total_logical_reads],qs.[last_logical_reads],qs.[min_logical_reads],qs.[max_logical_reads],qs.[total_clr_time],qs.[last_clr_time],qs.[min_clr_time],qs.[max_clr_time],qs.[total_elapsed_time],qs.[last_elapsed_time],qs.[min_elapsed_time],qs.[max_elapsed_time],qs.[query_hash],qs.[query_plan_hash]\r\n\t\t\t\t\t\tFROM queries qs\r\n\t\t\t\t\t\tLEFT OUTER JOIN #dm_exec_query_stats qsCaught ON qs.sql_handle = qsCaught.sql_handle AND qs.plan_handle = qsCaught.plan_handle AND qs.statement_start_offset = qsCaught.statement_start_offset\r\n\t\t\t\t\t\tWHERE qsCaught.sql_handle IS NULL;'\r\n EXECUTE(@StringToExecute)\r\n END\r\n\r\n\t\t\t\t\t\/* Populate the query_plan_filtered field. Only works in 2005SP2+, but we're just doing it in 2008 to be safe. *\/\r\n UPDATE #dm_exec_query_stats\r\n SET query_plan_filtered = qp.query_plan\r\n FROM #dm_exec_query_stats qs\r\n CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,\r\n qs.statement_start_offset,\r\n qs.statement_end_offset)\r\n AS qp \r\n\r\n END;\r\n\r\n\t\t\t\t\/* Populate the additional query_plan, text, and text_filtered fields *\/\r\n UPDATE #dm_exec_query_stats\r\n SET query_plan = qp.query_plan ,\r\n [text] = st.[text] ,\r\n text_filtered = SUBSTRING(st.text,\r\n ( qs.statement_start_offset \/ 2 )\r\n + 1,\r\n ( ( CASE qs.statement_end_offset\r\n WHEN -1\r\n THEN DATALENGTH(st.text)\r\n ELSE qs.statement_end_offset\r\n END\r\n - qs.statement_start_offset )\r\n \/ 2 ) + 1)\r\n FROM #dm_exec_query_stats qs\r\n CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st\r\n CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp\r\n\r\n\t\t\t\t\/* Dump instances of our own script. We're not trying to tune ourselves. *\/\r\n DELETE #dm_exec_query_stats\r\n WHERE text LIKE '%sp_Blitz%'\r\n OR text LIKE '%#BlitzResults%'\r\n\r\n\t\t\t\t\/* Look for implicit conversions *\/\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details ,\r\n QueryPlan ,\r\n QueryPlanFiltered\r\n\t\t\t\t\t\t \r\n )\r\n SELECT 63 AS CheckID ,\r\n 120 AS Priority ,\r\n 'Query Plans' AS FindingsGroup ,\r\n 'Implicit Conversion' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/implicit' AS URL ,\r\n ( 'One of the top resource-intensive queries is comparing two fields that are not the same datatype.' ) AS Details ,\r\n qs.query_plan ,\r\n qs.query_plan_filtered\r\n FROM #dm_exec_query_stats qs\r\n WHERE COALESCE(qs.query_plan_filtered,\r\n CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%CONVERT_IMPLICIT%'\r\n AND COALESCE(qs.query_plan_filtered,\r\n CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%PhysicalOp="Index Scan"%'\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details ,\r\n QueryPlan ,\r\n QueryPlanFiltered\r\n\t\t\t\t\t\t\t\t \r\n )\r\n SELECT 63 AS CheckID ,\r\n 120 AS Priority ,\r\n 'Query Plans' AS FindingsGroup ,\r\n 'Implicit Conversion Affecting Cardinality' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/implicit' AS URL ,\r\n ( 'One of the top resource-intensive queries has an implicit conversion that is affecting cardinality estimation.' ) AS Details ,\r\n qs.query_plan ,\r\n qs.query_plan_filtered\r\n FROM #dm_exec_query_stats qs\r\n WHERE COALESCE(qs.query_plan_filtered,\r\n CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT%'\r\n\r\n\r\n\t\t\t\t\/* Look for missing indexes *\/\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details ,\r\n QueryPlan ,\r\n QueryPlanFiltered\r\n\t\t\t\t\t\t \r\n )\r\n SELECT 65 AS CheckID ,\r\n 120 AS Priority ,\r\n 'Query Plans' AS FindingsGroup ,\r\n 'Missing Index' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/missingindex' AS URL ,\r\n ( 'One of the top resource-intensive queries may be dramatically improved by adding an index.' ) AS Details ,\r\n qs.query_plan ,\r\n qs.query_plan_filtered\r\n FROM #dm_exec_query_stats qs\r\n WHERE COALESCE(qs.query_plan_filtered,\r\n CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%MissingIndexGroup%'\r\n\t\t\t\t\r\n\t\t\t\t\/* Look for cursors *\/\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details ,\r\n QueryPlan ,\r\n QueryPlanFiltered\r\n\t\t\t\t\t\t \r\n )\r\n SELECT 66 AS CheckID ,\r\n 120 AS Priority ,\r\n 'Query Plans' AS FindingsGroup ,\r\n 'Cursor' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/cursor' AS URL ,\r\n ( 'One of the top resource-intensive queries is using a cursor.' ) AS Details ,\r\n qs.query_plan ,\r\n qs.query_plan_filtered\r\n FROM #dm_exec_query_stats qs\r\n WHERE COALESCE(qs.query_plan_filtered,\r\n CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%<StmtCursor%'\r\n\r\n\r\n\t\t\t\t\/* Look for scalar user-defined functions *\/\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details ,\r\n QueryPlan ,\r\n QueryPlanFiltered\r\n\t\t\t\t\t\t \r\n )\r\n SELECT 67 AS CheckID ,\r\n 120 AS Priority ,\r\n 'Query Plans' AS FindingsGroup ,\r\n 'Scalar UDFs' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/functions' AS URL ,\r\n ( 'One of the top resource-intensive queries is using a user-defined scalar function that may inhibit parallelism.' ) AS Details ,\r\n qs.query_plan ,\r\n qs.query_plan_filtered\r\n FROM #dm_exec_query_stats qs\r\n WHERE COALESCE(qs.query_plan_filtered,\r\n CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%<UserDefinedFunction%'\r\n\r\n END \/* IF @CheckProcedureCache = 1 *\/\r\n\r\n\t\/*Check for the last good DBCC CHECKDB date *\/\r\n CREATE TABLE #DBCCs\r\n (\r\n Id INT IDENTITY(1, 1)\r\n PRIMARY KEY ,\r\n ParentObject VARCHAR(255) ,\r\n Object VARCHAR(255) ,\r\n Field VARCHAR(255) ,\r\n Value VARCHAR(255) ,\r\n DbName SYSNAME NULL\r\n )\r\n EXEC sp_MSforeachdb N'USE [?];\r\n\t\t\t\t\t\t\tINSERT #DBCCs(ParentObject, Object, Field, Value)\r\n\t\t\t\t\t\t\tEXEC (''DBCC DBInfo() With TableResults, NO_INFOMSGS'');\r\n\t\t\t\t\t\t\tUPDATE #DBCCs SET DbName = N''?'' WHERE DbName IS NULL;';\r\n\r\n\r\n WITH DB2\r\n AS ( SELECT DISTINCT\r\n Field ,\r\n Value ,\r\n DbName\r\n FROM #DBCCs\r\n WHERE Field = 'dbi_dbccLastKnownGood'\r\n )\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n\t\t \r\n )\r\n SELECT 68 AS CheckID ,\r\n 50 AS PRIORITY ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'Last good DBCC CHECKDB over 2 weeks old' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/checkdb' AS URL ,\r\n 'Database [' + DB2.DbName + ']'\r\n + CASE DB2.Value\r\n WHEN '1900-01-01 00:00:00.000'\r\n THEN ' never had a successful DBCC CHECKDB.'\r\n ELSE ' last had a successful DBCC CHECKDB run on '\r\n + DB2.Value + '.'\r\n END\r\n + ' This check should be run regularly to catch any database corruption as soon as possible.'\r\n + ' Note: you can restore a backup of a busy production database to a test server and run DBCC CHECKDB '\r\n + ' against that to minimize impact. If you do that, you can ignore this warning.' AS Details\r\n FROM DB2\r\n WHERE CAST(DB2.Value AS DATETIME) < DATEADD(DD, -14,\r\n CURRENT_TIMESTAMP)\r\n\r\n\r\n\r\n\/*Check for high VLF count: this will omit any database snapshots*\/\r\n IF @@VERSION LIKE 'Microsoft SQL Server 2012%' \r\n BEGIN\r\n CREATE TABLE #LogInfo2012\r\n (\r\n recoveryunitid INT ,\r\n FileID SMALLINT ,\r\n FileSize BIGINT ,\r\n StartOffset BIGINT ,\r\n FSeqNo BIGINT ,\r\n [Status] TINYINT ,\r\n Parity TINYINT ,\r\n CreateLSN NUMERIC(38)\r\n );\r\n EXEC sp_MSforeachdb N'USE [?]; \r\n\tINSERT INTO #LogInfo2012 \r\n\tEXEC sp_executesql N''DBCC LogInfo() WITH NO_INFOMSGS''; \r\n\tIF @@ROWCOUNT > 50 \r\n\t\tBEGIN\r\n\t\t\tINSERT INTO #BlitzResults \r\n\t\t\t( CheckID \r\n\t\t\t,Priority \r\n\t\t\t,FindingsGroup \r\n\t\t\t,Finding \r\n\t\t\t,URL \r\n\t\t\t,Details) \r\n\t\t\tSELECT 69 \r\n\t\t\t,100 \r\n\t\t\t,''Performance'' \r\n\t\t\t,''High VLF Count'' \r\n\t\t\t,''http:\/\/BrentOzar.com\/go\/vlf '' \r\n\t\t\t,''The ['' + DB_NAME() + ''] database has '' + CAST(COUNT(*) as VARCHAR(20)) + '' virtual log files (VLFs). This may be slowing down startup, restores, and even inserts\/updates\/deletes.'' \r\n\t\t\tFROM #LogInfo2012\r\n\t\t\tWHERE EXISTS (SELECT name FROM master.sys.databases \r\n\t\t\t\t\t\t\tWHERE source_database_id is null) ; \r\n\t\t\tEND \r\n\t\t\tTRUNCATE TABLE #LogInfo2012;'\r\n DROP TABLE #LogInfo2012;\r\n END\r\n IF @@VERSION NOT LIKE 'Microsoft SQL Server 2012%' \r\n BEGIN\r\n CREATE TABLE #LogInfo\r\n (\r\n FileID SMALLINT ,\r\n FileSize BIGINT ,\r\n StartOffset BIGINT ,\r\n FSeqNo BIGINT ,\r\n [Status] TINYINT ,\r\n Parity TINYINT ,\r\n CreateLSN NUMERIC(38)\r\n );\r\n EXEC sp_MSforeachdb N'USE [?]; \r\n\tINSERT INTO #LogInfo \r\n\tEXEC sp_executesql N''DBCC LogInfo() WITH NO_INFOMSGS''; \r\n\tIF @@ROWCOUNT > 50 \r\n\t\tBEGIN\r\n\t\t\tINSERT INTO #BlitzResults \r\n\t\t\t( CheckID \r\n\t\t\t,Priority \r\n\t\t\t,FindingsGroup \r\n\t\t\t,Finding \r\n\t\t\t,URL \r\n\t\t\t,Details) \r\n\t\t\tSELECT 69 \r\n\t\t\t,100 \r\n\t\t\t,''Performance'' \r\n\t\t\t,''High VLF Count'' \r\n\t\t\t,''http:\/\/BrentOzar.com\/go\/vlf'' \r\n\t\t\t,''The ['' + DB_NAME() + ''] database has '' + CAST(COUNT(*) as VARCHAR(20)) + '' virtual log files (VLFs). This may be slowing down startup, restores, and even inserts\/updates\/deletes.'' \r\n\t\t\tFROM #LogInfo\r\n\t\t\tWHERE EXISTS (SELECT name FROM master.sys.databases \r\n\t\t\t\t\t\t\tWHERE source_database_id is null); \r\n\t\t\tEND \r\n\t\t\tTRUNCATE TABLE #LogInfo;'\r\n DROP TABLE #LogInfo;\r\n END\r\n\t\r\n\/*Verify that the servername is set *\/\r\n\t\r\n IF @@SERVERNAME IS NULL \r\n BEGIN\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 70 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Configuration' AS FindingsGroup ,\r\n '@@Servername not set' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/servername' AS URL ,\r\n '@@Servername variable is null. Correct by executing "sp_addserver ''<LocalServerName>'', local"' AS Details\r\n END;\r\n\r\n\r\n\/*Check for non-aligned indexes in partioned databases*\/\r\n CREATE TABLE #partdb\r\n (\r\n dbname VARCHAR(100) ,\r\n objectname VARCHAR(200) ,\r\n type_desc VARCHAR(50)\r\n )\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; insert into #partdb(dbname, objectname, type_desc)\r\nSELECT distinct db_name(database_id) as DBName,o.name Object_Name,\r\nds.type_desc\r\n FROM sys.objects AS o\r\n JOIN sys.indexes AS i\r\n ON o.object_id = i.object_id \r\nJOIN sys.data_spaces ds on ds.data_space_id = i.data_space_id\r\n LEFT OUTER JOIN \r\n sys.dm_db_index_usage_stats AS s \r\n ON i.object_id = s.object_id \r\n AND i.index_id = s.index_id\r\n WHERE o.type = ''u''\r\n -- Clustered and Non-Clustered indexes\r\n AND i.type IN (1, 2) \r\nAND o.name in \r\n\t(\r\nSELECT a.name from \r\n (SELECT ob.name, ds.type_desc from sys.objects ob JOIN sys.indexes ind on ind.object_id = ob.object_id join sys.data_spaces ds on ds.data_space_id = ind.data_space_id\r\n\t\tGROUP BY ob.name, ds.type_desc ) a group by a.name having COUNT (*) > 1\r\n\t)'\r\n\t\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT DISTINCT\r\n 72 AS CheckId ,\r\n 100 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'The partioned database ' + dbname\r\n + ' may have non-aligned indexes' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/aligned' AS URL ,\r\n 'Having non-aligned indexes on partitioned tables may cause inefficient query plans and CPU pressure' AS Details\r\n FROM #partdb\r\n WHERE dbname IS NOT\tNULL\r\n DROP TABLE #partdb\r\n\r\n\/*Check to see if a failsafe operator has been configured*\/\r\n\r\n DECLARE @AlertInfo TABLE\r\n (\r\n FailSafeOperator NVARCHAR(255) ,\r\n NotificationMethod INT ,\r\n ForwardingServer NVARCHAR(255) ,\r\n ForwardingSeverity INT ,\r\n PagerToTemplate NVARCHAR(255) ,\r\n PagerCCTemplate NVARCHAR(255) ,\r\n PagerSubjectTemplate NVARCHAR(255) ,\r\n PagerSendSubjectOnly NVARCHAR(255) ,\r\n ForwardAlways INT\r\n )\r\n\r\n INSERT INTO @AlertInfo\r\n EXEC [master].[dbo].[sp_MSgetalertinfo] @includeaddresses = 0\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n \r\n )\r\n SELECT 73 AS CheckID ,\r\n 50 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'No failsafe operator configured' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/failsafe' AS URL ,\r\n ( 'No failsafe operator is configured on this server. This is a good idea just in-case there are issues with the [msdb] database that prevents alerting.' ) AS Details\r\n FROM @AlertInfo\r\n WHERE FailSafeOperator IS NULL;\r\n\r\n\/*Identify globally enabled trace flags*\/\r\n IF OBJECT_ID('tempdb..#TraceStatus') IS NOT NULL \r\n DROP TABLE #TraceStatus;\r\n CREATE TABLE #TraceStatus\r\n (\r\n TraceFlag VARCHAR(10) ,\r\n status BIT ,\r\n Global BIT ,\r\n Session BIT\r\n );\r\n\r\n INSERT INTO #TraceStatus\r\n EXEC ( ' DBCC TRACESTATUS(-1) WITH NO_INFOMSGS'\r\n )\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 74 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Global Trace Flag' AS FindingsGroup ,\r\n 'TraceFlag On' AS Finding ,\r\n 'http:\/\/www.BrentOzar.com\/go\/traceflags\/' AS URL ,\r\n 'Trace flag ' + T.TraceFlag + ' is enabled globally.' ASDetails\r\n FROM #TraceStatus T\r\n\r\n\/*Check for transaction log file larger than data file *\/\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 75 AS CheckId ,\r\n 50 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'Transaction Log Larger than Data File' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/biglog' AS URL ,\r\n 'The database [' + DB_NAME(a.database_id)\r\n + '] has a transaction log file larger than a data file. This may indicate that transaction log backups are not being performed or not performed often enough.' AS Details\r\n FROM sys.master_files a\r\n WHERE a.type = 1\r\n AND a.size > 125000 \/* Size is measured in pages here, so this gets us log files over 1GB. *\/\r\n AND a.size > ( SELECT SUM(b.size)\r\n FROM sys.master_files b\r\n WHERE a.database_id = b.database_id\r\n AND b.type = 0\r\n )\r\n AND a.database_id IN ( SELECT database_id\r\n FROM sys.databases\r\n WHERE source_database_id IS NULL )\r\n\r\n\/*Check for collation conflicts between user databases and tempdb *\/\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 76 AS CheckId ,\r\n 50 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'Collation for ' + name\r\n + ' different than tempdb collation' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/collate' AS URL ,\r\n 'Collation differences between user databases and tempdb can cause conflicts especially when comparing string values' AS Details\r\n FROM sys.databases\r\n WHERE name NOT IN ( 'master', 'model', 'msdb' )\r\n AND collation_name <> ( SELECT collation_name\r\n FROM sys.databases\r\n WHERE name = 'tempdb'\r\n )\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 77 AS CheckId ,\r\n 50 AS Priority ,\r\n 'Reliability' AS FindingsGroup ,\r\n 'Database Snapshot Online' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/snapshot' AS URL ,\r\n 'Database [' + dSnap.[name] + '] is a snapshot of ['\r\n + dOriginal.[name]\r\n + ']. Make sure you have enough drive space to maintain the snapshot as the original database grows.' AS Details\r\n FROM sys.databases dSnap\r\n INNER JOIN sys.databases dOriginal ON dSnap.source_database_id = dOriginal.database_id\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 79 AS CheckId ,\r\n 100 AS Priority ,\r\n 'Performance' AS FindingsGroup ,\r\n 'Shrink Database Job' AS Finding ,\r\n 'http:\/\/BrentOzar.com\/go\/autoshrink' AS URL ,\r\n 'In the [' + j.[name] + '] job, step [' + step.[step_name]\r\n + '] has SHRINKDATABASE or SHRINKFILE, which may be causing database fragmentation.' AS Details\r\n FROM msdb.dbo.sysjobs j\r\n INNER JOIN msdb.dbo.sysjobsteps step ON j.job_id = step.job_id\r\n WHERE step.command LIKE N'%SHRINKDATABASE%'\r\n OR step.command LIKE N'%SHRINKFILE%'\r\n\r\n EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 80, 50, ''Reliability'', ''Max File Size Set'', ''http:\/\/BrentOzar.com\/go\/maxsize'', (''The ['' + DB_NAME() + ''] database file '' + name + '' has a max file size set to '' + CAST(CAST(max_size AS BIGINT) * 8 \/ 1024 AS VARCHAR(100)) + ''MB. If it runs out of space, the database will stop working even though there may be drive space available.'') FROM sys.database_files WHERE max_size <> 268435456 AND max_size <> -1';\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 81 AS CheckID ,\r\n 200 AS Priority ,\r\n 'Non-Active Server Config' AS FindingsGroup ,\r\n cr.name AS Finding ,\r\n 'http:\/\/www.BrentOzar.com\/blitz\/sp_configure\/' AS URL ,\r\n ( 'This sp_configure option isn''t running under its set value. Its set value is '\r\n + CAST(cr.[Value] AS VARCHAR(100))\r\n + ' and its running value is '\r\n + CAST(cr.value_in_use AS VARCHAR(100))\r\n + '. When someone does a RECONFIGURE or restarts the instance, this setting will start taking effect.' ) AS Details\r\n FROM sys.configurations cr\r\n WHERE cr.value <> cr.value_in_use;\r\n\r\n\r\n IF EXISTS ( SELECT *\r\n FROM sys.all_objects\r\n WHERE name = 'dm_server_services' ) \r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\n SELECT 83 AS CheckID ,\r\n 250 AS Priority ,\r\n ''Server Info'' AS FindingsGroup ,\r\n ''Services'' AS Finding ,\r\n '''' AS URL ,\r\n N''Service: '' + servicename + N'' runs under service account '' + service_account + N''. Last startup time: '' + COALESCE(CAST(CAST(last_startup_time AS DATETIME) AS VARCHAR(50)), ''not shown.'') + ''. Startup type: '' + startup_type_desc + N'', currently '' + status_desc + ''.'' \r\n FROM sys.dm_server_services;'\r\n EXECUTE(@StringToExecute);\r\n\r\n\r\n\/* Check 84 - SQL Server 2012 *\/\r\n IF EXISTS ( SELECT *\r\n FROM sys.all_objects o\r\n INNER JOIN sys.all_columns c ON o.object_id = c.object_id\r\n WHERE o.name = 'dm_os_sys_info'\r\n AND c.name = 'physical_memory_kb' ) \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\n SELECT 84 AS CheckID ,\r\n 250 AS Priority ,\r\n ''Server Info'' AS FindingsGroup ,\r\n ''Hardware'' AS Finding ,\r\n '''' AS URL ,\r\n ''Logical processors: '' + CAST(cpu_count AS VARCHAR(50)) + ''. Physical memory: '' + CAST( CAST(ROUND((physical_memory_kb \/ 1024.0 \/ 1024), 1) AS INT) AS VARCHAR(50)) + ''GB.''\r\n\t\tFROM sys.dm_os_sys_info';\r\n EXECUTE(@StringToExecute);\r\n END\r\n\r\n\/* Check 84 - SQL Server 2008 *\/\r\n IF EXISTS ( SELECT *\r\n FROM sys.all_objects o\r\n INNER JOIN sys.all_columns c ON o.object_id = c.object_id\r\n WHERE o.name = 'dm_os_sys_info'\r\n AND c.name = 'physical_memory_in_bytes' ) \r\n BEGIN\r\n SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)\r\n SELECT 84 AS CheckID ,\r\n 250 AS Priority ,\r\n ''Server Info'' AS FindingsGroup ,\r\n ''Hardware'' AS Finding ,\r\n '''' AS URL ,\r\n ''Logical processors: '' + CAST(cpu_count AS VARCHAR(50)) + ''. Physical memory: '' + CAST( CAST(ROUND((physical_memory_in_bytes \/ 1024.0 \/ 1024 \/ 1024), 1) AS INT) AS VARCHAR(50)) + ''GB.''\r\n\t\tFROM sys.dm_os_sys_info';\r\n EXECUTE(@StringToExecute);\r\n END\r\n\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n SELECT 85 AS CheckID ,\r\n 250 AS Priority ,\r\n 'Server Info' AS FindingsGroup ,\r\n 'SQL Server Service' AS Finding ,\r\n '' AS URL ,\r\n N'Version: '\r\n + CAST(SERVERPROPERTY('productversion') AS NVARCHAR(100))\r\n + N'. Patch Level: '\r\n + CAST(SERVERPROPERTY('productlevel') AS NVARCHAR(100))\r\n + N'. Edition: '\r\n + CAST(SERVERPROPERTY('edition') AS VARCHAR(100))\r\n + N'. AlwaysOn Enabled: '\r\n + CAST(COALESCE(SERVERPROPERTY('IsHadrEnabled'), 0) AS VARCHAR(100))\r\n + N'. AlwaysOn Mgr Status: '\r\n + CAST(COALESCE(SERVERPROPERTY('HadrManagerStatus'), 0) AS VARCHAR(100))\r\n\t\r\n\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n )\r\n VALUES ( -1 ,\r\n 255 ,\r\n 'Thanks!' ,\r\n 'From Brent Ozar Unlimited' ,\r\n 'http:\/\/www.BrentOzar.com\/blitz\/' ,\r\n 'Thanks from the Brent Ozar Unlimited team. We hope you found this tool useful, and if you need help relieving your SQL Server pains, email us at Help@BrentOzar.com.'\r\n );\r\n\r\n SET @Version = 16;\r\n INSERT INTO #BlitzResults\r\n ( CheckID ,\r\n Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n URL ,\r\n Details\r\n\t\t \r\n )\r\n VALUES ( -1 ,\r\n 0 ,\r\n 'sp_Blitz v16 Dec 13 2012' ,\r\n 'From Brent Ozar Unlimited' ,\r\n 'http:\/\/www.BrentOzar.com\/blitz\/' ,\r\n 'Thanks from the Brent Ozar Unlimited team. We hope you found this tool useful, and if you need help relieving your SQL Server pains, email us at Help@BrentOzar.com.'\r\n\t\t \r\n );\r\n\r\n\r\n\r\n IF @OutputType = 'COUNT' \r\n BEGIN\r\n SELECT COUNT(*) AS Warnings\r\n FROM #BlitzResults\r\n END\r\n ELSE \r\n BEGIN\r\n SELECT [Priority] ,\r\n [FindingsGroup] ,\r\n [Finding] ,\r\n [URL] ,\r\n [Details] ,\r\n [QueryPlan] ,\r\n [QueryPlanFiltered] ,\r\n CheckID\r\n FROM #BlitzResults\r\n ORDER BY Priority ,\r\n FindingsGroup ,\r\n Finding ,\r\n Details;\r\n END\r\n \r\n DROP TABLE #BlitzResults;\r\n\r\n\r\n IF @OutputProcedureCache = 1 \r\n SELECT TOP 20\r\n total_worker_time \/ execution_count AS AvgCPU ,\r\n total_worker_time AS TotalCPU ,\r\n CAST(ROUND(100.00 * total_worker_time\r\n \/ ( SELECT SUM(total_worker_time)\r\n FROM sys.dm_exec_query_stats\r\n ), 2) AS MONEY) AS PercentCPU ,\r\n total_elapsed_time \/ execution_count AS AvgDuration ,\r\n total_elapsed_time AS TotalDuration ,\r\n CAST(ROUND(100.00 * total_elapsed_time\r\n \/ ( SELECT SUM(total_elapsed_time)\r\n FROM sys.dm_exec_query_stats\r\n ), 2) AS MONEY) AS PercentDuration ,\r\n total_logical_reads \/ execution_count AS AvgReads ,\r\n total_logical_reads AS TotalReads ,\r\n CAST(ROUND(100.00 * total_logical_reads\r\n \/ ( SELECT SUM(total_logical_reads)\r\n FROM sys.dm_exec_query_stats\r\n ), 2) AS MONEY) AS PercentReads ,\r\n execution_count ,\r\n CAST(ROUND(100.00 * execution_count\r\n \/ ( SELECT SUM(execution_count)\r\n FROM sys.dm_exec_query_stats\r\n ), 2) AS MONEY) AS PercentExecutions ,\r\n CASE WHEN DATEDIFF(mi, creation_time, qs.last_execution_time) = 0\r\n THEN 0\r\n ELSE CAST(( 1.00 * execution_count \/ DATEDIFF(mi,\r\n creation_time,\r\n qs.last_execution_time) ) AS MONEY)\r\n END AS executions_per_minute ,\r\n qs.creation_time AS plan_creation_time ,\r\n qs.last_execution_time ,\r\n text ,\r\n text_filtered ,\r\n query_plan ,\r\n query_plan_filtered ,\r\n sql_handle ,\r\n query_hash ,\r\n plan_handle ,\r\n query_plan_hash\r\n FROM #dm_exec_query_stats qs\r\n ORDER BY CASE UPPER(@CheckProcedureCacheFilter)\r\n WHEN 'CPU' THEN total_worker_time\r\n WHEN 'READS' THEN total_logical_reads\r\n WHEN 'EXECCOUNT' THEN execution_count\r\n WHEN 'DURATION' THEN total_elapsed_time\r\n ELSE total_worker_time\r\n END DESC\r\n SET NOCOUNT OFF;\r\nGO\r\n\r\n\/*\r\nSample execution call with the most common parameters:\r\nEXEC [dbo].[sp_Blitz]\r\n @CheckUserDatabaseObjects = 1 ,\r\n @CheckProcedureCache = 1 ,\r\n @OutputType = 'TABLE' ,\r\n @OutputProcedureCache = 0 ,\r\n @CheckProcedureCacheFilter = NULL,\r\n\t@CheckServerInfo = 0\r\n\r\n*\/\r\n<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"\u68c0\u67e5\u662f\u5426\u5907\u4efd\uff0c\u5b89\u5168\u4e0a\u67e5\u8be2\u7ba1\u7406\u5458\u89d2\u8272\u7684\u662f\u54ea\u4e9b\u8d26\u53f7\uff0c\u7ef4\u62a4\u4e0aDBCC checkdb\u65f6\u95f4\u7b49\u3002 sp_Blitz US […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2372","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2372","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2372"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2372\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2372"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2372"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}