· 10 years ago · Aug 31, 2016, 02:44 PM
1IF OBJECT_ID('dbo.sp_Blitz') IS NULL
2 EXEC ('CREATE PROCEDURE dbo.sp_Blitz AS RETURN 0;')
3GO
4
5ALTER PROCEDURE [dbo].[sp_Blitz]
6 @Help TINYINT = 0 ,
7 @CheckUserDatabaseObjects TINYINT = 1 ,
8 @CheckProcedureCache TINYINT = 0 ,
9 @OutputType VARCHAR(20) = 'TABLE' ,
10 @OutputProcedureCache TINYINT = 0 ,
11 @CheckProcedureCacheFilter VARCHAR(10) = NULL ,
12 @CheckServerInfo TINYINT = 0 ,
13 @SkipChecksServer NVARCHAR(256) = NULL ,
14 @SkipChecksDatabase NVARCHAR(256) = NULL ,
15 @SkipChecksSchema NVARCHAR(256) = NULL ,
16 @SkipChecksTable NVARCHAR(256) = NULL ,
17 @IgnorePrioritiesBelow INT = NULL ,
18 @IgnorePrioritiesAbove INT = NULL ,
19 @OutputServerName NVARCHAR(256) = NULL ,
20 @OutputDatabaseName NVARCHAR(256) = NULL ,
21 @OutputSchemaName NVARCHAR(256) = NULL ,
22 @OutputTableName NVARCHAR(256) = NULL ,
23 @OutputXMLasNVARCHAR TINYINT = 0 ,
24 @EmailRecipients VARCHAR(MAX) = NULL ,
25 @EmailProfile sysname = NULL ,
26 @SummaryMode TINYINT = 0 ,
27 @BringThePain TINYINT = 0 ,
28 @VersionDate DATETIME = NULL OUTPUT
29AS
30 SET NOCOUNT ON;
31 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
32 SET @VersionDate = '20160715'
33
34 IF @Help = 1 PRINT '
35 /*
36 sp_Blitz from http://FirstResponderKit.org
37
38 This script checks the health of your SQL Server and gives you a prioritized
39 to-do list of the most urgent things you should consider fixing.
40
41 To learn more, visit http://FirstResponderKit.org where you can download new
42 versions for free, watch training videos on how it works, get more info on
43 the findings, contribute your own code, and more.
44
45 Known limitations of this version:
46 - Only Microsoft-supported versions of SQL Server. Sorry, 2005 and 2000.
47 - If a database name has a question mark in it, some tests will fail. Gotta
48 love that unsupported sp_MSforeachdb.
49 - If you have offline databases, sp_Blitz fails the first time you run it,
50 but does work the second time. (Hoo, boy, this will be fun to debug.)
51 - @OutputServerName is not functional yet.
52
53 Unknown limitations of this version:
54 - None. (If we knew them, they would be known. Duh.)
55
56 Changes in v53.1 - 2016/07/15
57 - Warn about 2016 Query Store cleanup bug in Standard, Evaluation, Express:
58 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/352
59 - Updating list of supported SQL Server versions:
60 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/344
61 - Fixing bug in wait stats percentages:
62 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/324
63 - For the full list of improvements and fixes in this version, see:
64 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/milestone/3?closed=1
65
66
67 Changes in v53 - 2016/06/26
68 - BREAKING CHANGE: Standardized input & output parameters to be
69 consistent across the entire First Responder Kit. This also means the old
70 old output parameter @Version is no more, because we are switching to
71 semantic versioning.
72 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/284
73 - BREAKING CHANGE: The CheckDate field datatype is now DATETIMEOFFSET. This
74 makes it easier to combine results from multiple servers into one table even
75 when servers are in different data centers, different time zones. More info:
76 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/288
77
78
79 Parameter explanations:
80
81 @CheckUserDatabaseObjects 1=review user databases for triggers, heaps, etc. Takes more time for more databases and objects.
82 @CheckServerInfo 1=show server info like CPUs, memory, virtualization
83 @CheckProcedureCache 1=top 20-50 resource-intensive cache plans and analyze them for common performance issues.
84 @OutputProcedureCache 1=output the top 20-50 resource-intensive plans even if they did not trigger an alarm
85 @CheckProcedureCacheFilter ''CPU'' | ''Reads'' | ''Duration'' | ''ExecCount''
86 @OutputType ''TABLE''=table | ''COUNT''=row with number found | ''SCHEMA''=version and field list | ''NONE'' = none
87 @IgnorePrioritiesBelow 50=ignore priorities below 50
88 @IgnorePrioritiesAbove 50=ignore priorities above 50
89 For the rest of the parameters, see http://www.brentozar.com/blitz/documentation for details.
90
91 MIT License
92
93 Copyright (c) 2016 Brent Ozar Unlimited
94
95 Permission is hereby granted, free of charge, to any person obtaining a copy
96 of this software and associated documentation files (the "Software"), to deal
97 in the Software without restriction, including without limitation the rights
98 to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
99 copies of the Software, and to permit persons to whom the Software is
100 furnished to do so, subject to the following conditions:
101
102 The above copyright notice and this permission notice shall be included in all
103 copies or substantial portions of the Software.
104
105 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
106 IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
107 FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
108 AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
109 LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
110 OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
111 SOFTWARE.
112
113
114 */'
115 ELSE IF @OutputType = 'SCHEMA'
116 BEGIN
117 SELECT FieldList = '[Priority] TINYINT, [FindingsGroup] VARCHAR(50), [Finding] VARCHAR(200), [DatabaseName] NVARCHAR(128), [URL] VARCHAR(200), [Details] NVARCHAR(4000), [QueryPlan] NVARCHAR(MAX), [QueryPlanFiltered] NVARCHAR(MAX), [CheckID] INT'
118
119 END
120 ELSE /* IF @OutputType = 'SCHEMA' */
121 BEGIN
122
123 /*
124 We start by creating #BlitzResults. It's a temp table that will store all of
125 the results from our checks. Throughout the rest of this stored procedure,
126 we're running a series of checks looking for dangerous things inside the SQL
127 Server. When we find a problem, we insert rows into #BlitzResults. At the
128 end, we return these results to the end user.
129
130 #BlitzResults has a CheckID field, but there's no Check table. As we do
131 checks, we insert data into this table, and we manually put in the CheckID.
132 For a list of checks, visit http://FirstResponderKit.org.
133 */
134 DECLARE @StringToExecute NVARCHAR(4000)
135 ,@curr_tracefilename NVARCHAR(500)
136 ,@base_tracefilename NVARCHAR(500)
137 ,@indx int
138 ,@query_result_separator CHAR(1)
139 ,@EmailSubject NVARCHAR(255)
140 ,@EmailBody NVARCHAR(MAX)
141 ,@EmailAttachmentFilename NVARCHAR(255)
142 ,@ProductVersion NVARCHAR(128)
143 ,@ProductVersionMajor DECIMAL(10,2)
144 ,@ProductVersionMinor DECIMAL(10,2)
145 ,@CurrentName NVARCHAR(128)
146 ,@CurrentDefaultValue NVARCHAR(200)
147 ,@CurrentCheckID INT
148 ,@CurrentPriority INT
149 ,@CurrentFinding VARCHAR(200)
150 ,@CurrentURL VARCHAR(200)
151 ,@CurrentDetails NVARCHAR(4000)
152 ,@MSSinceStartup DECIMAL(38,0)
153 ,@CPUMSsinceStartup DECIMAL(38,0);
154
155 IF OBJECT_ID('tempdb..#BlitzResults') IS NOT NULL
156 DROP TABLE #BlitzResults;
157 CREATE TABLE #BlitzResults
158 (
159 ID INT IDENTITY(1, 1) ,
160 CheckID INT ,
161 DatabaseName NVARCHAR(128) ,
162 Priority TINYINT ,
163 FindingsGroup VARCHAR(50) ,
164 Finding VARCHAR(200) ,
165 URL VARCHAR(200) ,
166 Details NVARCHAR(4000) ,
167 QueryPlan [XML] NULL ,
168 QueryPlanFiltered [NVARCHAR](MAX) NULL
169 );
170
171 /*
172 You can build your own table with a list of checks to skip. For example, you
173 might have some databases that you don't care about, or some checks you don't
174 want to run. Then, when you run sp_Blitz, you can specify these parameters:
175 @SkipChecksDatabase = 'DBAtools',
176 @SkipChecksSchema = 'dbo',
177 @SkipChecksTable = 'BlitzChecksToSkip'
178 Pass in the database, schema, and table that contains the list of checks you
179 want to skip. This part of the code checks those parameters, gets the list,
180 and then saves those in a temp table. As we run each check, we'll see if we
181 need to skip it.
182
183 Really anal-retentive users will note that the @SkipChecksServer parameter is
184 not used. YET. We added that parameter in so that we could avoid changing the
185 stored proc's surface area (interface) later.
186 */
187 IF OBJECT_ID('tempdb..#SkipChecks') IS NOT NULL
188 DROP TABLE #SkipChecks;
189 CREATE TABLE #SkipChecks
190 (
191 DatabaseName NVARCHAR(128) ,
192 CheckID INT ,
193 ServerName NVARCHAR(128)
194 );
195 CREATE CLUSTERED INDEX IX_CheckID_DatabaseName ON #SkipChecks(CheckID, DatabaseName);
196
197 IF @SkipChecksTable IS NOT NULL
198 AND @SkipChecksSchema IS NOT NULL
199 AND @SkipChecksDatabase IS NOT NULL
200 BEGIN
201 SET @StringToExecute = 'INSERT INTO #SkipChecks(DatabaseName, CheckID, ServerName )
202 SELECT DISTINCT DatabaseName, CheckID, ServerName
203 FROM ' + QUOTENAME(@SkipChecksDatabase) + '.' + QUOTENAME(@SkipChecksSchema) + '.' + QUOTENAME(@SkipChecksTable)
204 + ' WHERE ServerName IS NULL OR ServerName = SERVERPROPERTY(''ServerName'');'
205 EXEC(@StringToExecute)
206 END
207
208 IF NOT EXISTS ( SELECT 1
209 FROM #SkipChecks
210 WHERE DatabaseName IS NULL AND CheckID = 106 )
211 AND (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1
212 BEGIN
213 select @curr_tracefilename = [path] from sys.traces where is_default = 1 ;
214 set @curr_tracefilename = reverse(@curr_tracefilename);
215 select @indx = patindex('%\%', @curr_tracefilename) ;
216 set @curr_tracefilename = reverse(@curr_tracefilename) ;
217 set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;
218 END
219
220 /* If the server has any databases on Antiques Roadshow, skip the checks that would break due to CTEs. */
221 IF @CheckUserDatabaseObjects = 1 AND EXISTS(SELECT * FROM sys.databases WHERE compatibility_level < 90)
222 BEGIN
223 SET @CheckUserDatabaseObjects = 0;
224 PRINT 'Databases with compatibility level < 90 found, so setting @CheckUserDatabaseObjects = 0.';
225 PRINT 'The database-level checks rely on CTEs, which are not supported in SQL 2000 compat level databases.';
226 PRINT 'Get with the cool kids and switch to a current compatibility level, Grandpa. To find the problems, run:';
227 PRINT 'SELECT * FROM sys.databases WHERE compatibility_level < 90;';
228 END
229
230
231 /* If the server is Amazon RDS, skip checks that it doesn't allow */
232 IF LEFT(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(8000)), 8) = 'EC2AMAZ-'
233 AND LEFT(CAST(SERVERPROPERTY('MachineName') AS VARCHAR(8000)), 8) = 'EC2AMAZ-'
234 AND LEFT(CAST(SERVERPROPERTY('ServerName') AS VARCHAR(8000)), 8) = 'EC2AMAZ-'
235 BEGIN
236 INSERT INTO #SkipChecks (CheckID) VALUES (6);
237 INSERT INTO #SkipChecks (CheckID) VALUES (29);
238 INSERT INTO #SkipChecks (CheckID) VALUES (30);
239 INSERT INTO #SkipChecks (CheckID) VALUES (31);
240 INSERT INTO #SkipChecks (CheckID) VALUES (40); /* TempDB only has one data file */
241 INSERT INTO #SkipChecks (CheckID) VALUES (57);
242 INSERT INTO #SkipChecks (CheckID) VALUES (59);
243 INSERT INTO #SkipChecks (CheckID) VALUES (61);
244 INSERT INTO #SkipChecks (CheckID) VALUES (62);
245 INSERT INTO #SkipChecks (CheckID) VALUES (68);
246 INSERT INTO #SkipChecks (CheckID) VALUES (69);
247 INSERT INTO #SkipChecks (CheckID) VALUES (73);
248 INSERT INTO #SkipChecks (CheckID) VALUES (79);
249 INSERT INTO #SkipChecks (CheckID) VALUES (92);
250 INSERT INTO #SkipChecks (CheckID) VALUES (94);
251 INSERT INTO #SkipChecks (CheckID) VALUES (96);
252 INSERT INTO #SkipChecks (CheckID) VALUES (98);
253 INSERT INTO #SkipChecks (CheckID) VALUES (100); /* Remote DAC disabled */
254 INSERT INTO #SkipChecks (CheckID) VALUES (123);
255 INSERT INTO #SkipChecks (CheckID) VALUES (177);
256 END /* Amazon RDS skipped checks */
257
258
259
260 /*
261 That's the end of the SkipChecks stuff.
262 The next several tables are used by various checks later.
263 */
264 IF OBJECT_ID('tempdb..#ConfigurationDefaults') IS NOT NULL
265 DROP TABLE #ConfigurationDefaults;
266 CREATE TABLE #ConfigurationDefaults
267 (
268 name NVARCHAR(128) ,
269 DefaultValue BIGINT,
270 CheckID INT
271 );
272
273 IF OBJECT_ID ('tempdb..#Recompile') IS NOT NULL
274 DROP TABLE #Recompile;
275 CREATE TABLE #Recompile(
276 DBName varchar(200),
277 ProcName varchar(300),
278 RecompileFlag varchar(1),
279 SPSchema varchar(50)
280 );
281
282 IF OBJECT_ID('tempdb..#DatabaseDefaults') IS NOT NULL
283 DROP TABLE #DatabaseDefaults;
284 CREATE TABLE #DatabaseDefaults
285 (
286 name NVARCHAR(128) ,
287 DefaultValue NVARCHAR(200),
288 CheckID INT,
289 Priority INT,
290 Finding VARCHAR(200),
291 URL VARCHAR(200),
292 Details NVARCHAR(4000)
293 );
294
295
296
297 IF OBJECT_ID('tempdb..#DBCCs') IS NOT NULL
298 DROP TABLE #DBCCs;
299 CREATE TABLE #DBCCs
300 (
301 ID INT IDENTITY(1, 1)
302 PRIMARY KEY ,
303 ParentObject VARCHAR(255) ,
304 Object VARCHAR(255) ,
305 Field VARCHAR(255) ,
306 Value VARCHAR(255) ,
307 DbName NVARCHAR(128) NULL
308 )
309
310
311 IF OBJECT_ID('tempdb..#LogInfo2012') IS NOT NULL
312 DROP TABLE #LogInfo2012;
313 CREATE TABLE #LogInfo2012
314 (
315 recoveryunitid INT ,
316 FileID SMALLINT ,
317 FileSize BIGINT ,
318 StartOffset BIGINT ,
319 FSeqNo BIGINT ,
320 [Status] TINYINT ,
321 Parity TINYINT ,
322 CreateLSN NUMERIC(38)
323 );
324
325 IF OBJECT_ID('tempdb..#LogInfo') IS NOT NULL
326 DROP TABLE #LogInfo;
327 CREATE TABLE #LogInfo
328 (
329 FileID SMALLINT ,
330 FileSize BIGINT ,
331 StartOffset BIGINT ,
332 FSeqNo BIGINT ,
333 [Status] TINYINT ,
334 Parity TINYINT ,
335 CreateLSN NUMERIC(38)
336 );
337
338 IF OBJECT_ID('tempdb..#partdb') IS NOT NULL
339 DROP TABLE #partdb;
340 CREATE TABLE #partdb
341 (
342 dbname NVARCHAR(128) ,
343 objectname NVARCHAR(200) ,
344 type_desc NVARCHAR(128)
345 )
346
347 IF OBJECT_ID('tempdb..#TraceStatus') IS NOT NULL
348 DROP TABLE #TraceStatus;
349 CREATE TABLE #TraceStatus
350 (
351 TraceFlag VARCHAR(10) ,
352 status BIT ,
353 Global BIT ,
354 Session BIT
355 );
356
357 IF OBJECT_ID('tempdb..#driveInfo') IS NOT NULL
358 DROP TABLE #driveInfo;
359 CREATE TABLE #driveInfo
360 (
361 drive NVARCHAR ,
362 SIZE DECIMAL(18, 2)
363 )
364
365
366 IF OBJECT_ID('tempdb..#dm_exec_query_stats') IS NOT NULL
367 DROP TABLE #dm_exec_query_stats;
368 CREATE TABLE #dm_exec_query_stats
369 (
370 [id] [int] NOT NULL
371 IDENTITY(1, 1) ,
372 [sql_handle] [varbinary](64) NOT NULL ,
373 [statement_start_offset] [int] NOT NULL ,
374 [statement_end_offset] [int] NOT NULL ,
375 [plan_generation_num] [bigint] NOT NULL ,
376 [plan_handle] [varbinary](64) NOT NULL ,
377 [creation_time] [datetime] NOT NULL ,
378 [last_execution_time] [datetime] NOT NULL ,
379 [execution_count] [bigint] NOT NULL ,
380 [total_worker_time] [bigint] NOT NULL ,
381 [last_worker_time] [bigint] NOT NULL ,
382 [min_worker_time] [bigint] NOT NULL ,
383 [max_worker_time] [bigint] NOT NULL ,
384 [total_physical_reads] [bigint] NOT NULL ,
385 [last_physical_reads] [bigint] NOT NULL ,
386 [min_physical_reads] [bigint] NOT NULL ,
387 [max_physical_reads] [bigint] NOT NULL ,
388 [total_logical_writes] [bigint] NOT NULL ,
389 [last_logical_writes] [bigint] NOT NULL ,
390 [min_logical_writes] [bigint] NOT NULL ,
391 [max_logical_writes] [bigint] NOT NULL ,
392 [total_logical_reads] [bigint] NOT NULL ,
393 [last_logical_reads] [bigint] NOT NULL ,
394 [min_logical_reads] [bigint] NOT NULL ,
395 [max_logical_reads] [bigint] NOT NULL ,
396 [total_clr_time] [bigint] NOT NULL ,
397 [last_clr_time] [bigint] NOT NULL ,
398 [min_clr_time] [bigint] NOT NULL ,
399 [max_clr_time] [bigint] NOT NULL ,
400 [total_elapsed_time] [bigint] NOT NULL ,
401 [last_elapsed_time] [bigint] NOT NULL ,
402 [min_elapsed_time] [bigint] NOT NULL ,
403 [max_elapsed_time] [bigint] NOT NULL ,
404 [query_hash] [binary](8) NULL ,
405 [query_plan_hash] [binary](8) NULL ,
406 [query_plan] [xml] NULL ,
407 [query_plan_filtered] [nvarchar](MAX) NULL ,
408 [text] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS
409 NULL ,
410 [text_filtered] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS
411 NULL
412 )
413
414 /* Used for the default trace checks. */
415 DECLARE @TracePath NVARCHAR(256);
416 SELECT @TracePath=CAST(value as NVARCHAR(256))
417 FROM sys.fn_trace_getinfo(1)
418 WHERE traceid=1 AND property=2;
419
420 SELECT @MSSinceStartup = DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP)
421 FROM sys.databases
422 WHERE name='tempdb';
423
424 SET @MSSinceStartup = @MSSinceStartup * 60000;
425
426 SELECT @CPUMSsinceStartup = @MSSinceStartup * cpu_count
427 FROM sys.dm_os_sys_info;
428
429
430 /* If we're outputting CSV, don't bother checking the plan cache because we cannot export plans. */
431 IF @OutputType = 'CSV'
432 SET @CheckProcedureCache = 0;
433
434 /* Only run CheckUserDatabaseObjects if there are less than 50 databases. */
435 IF @BringThePain = 0 AND 50 <= (SELECT COUNT(*) FROM sys.databases) AND @CheckUserDatabaseObjects = 1
436 BEGIN
437 SET @CheckUserDatabaseObjects = 0;
438 PRINT 'Running sp_Blitz @CheckUserDatabaseObjects = 1 on a server with 50+ databases may cause temporary insanity for the server and/or user.';
439 PRINT 'If you''re sure you want to do this, run again with the parameter @BringThePain = 1.';
440 END
441
442 /* Sanitize our inputs */
443 SELECT
444 @OutputDatabaseName = QUOTENAME(@OutputDatabaseName),
445 @OutputSchemaName = QUOTENAME(@OutputSchemaName),
446 @OutputTableName = QUOTENAME(@OutputTableName)
447
448 /* Get the major and minor build numbers */
449 SET @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
450 SELECT @ProductVersionMajor = SUBSTRING(@ProductVersion, 1,CHARINDEX('.', @ProductVersion) + 1 ),
451 @ProductVersionMinor = PARSENAME(CONVERT(varchar(32), @ProductVersion), 2)
452
453
454 /*
455 Whew! we're finally done with the setup, and we can start doing checks.
456 First, let's make sure we're actually supposed to do checks on this server.
457 The user could have passed in a SkipChecks table that specified to skip ALL
458 checks on this server, so let's check for that:
459 */
460 IF ( ( SERVERPROPERTY('ServerName') NOT IN ( SELECT ServerName
461 FROM #SkipChecks
462 WHERE DatabaseName IS NULL
463 AND CheckID IS NULL ) )
464 OR ( @SkipChecksTable IS NULL )
465 )
466 BEGIN
467
468 /*
469 Our very first check! We'll put more comments in this one just to
470 explain exactly how it works. First, we check to see if we're
471 supposed to skip CheckID 1 (that's the check we're working on.)
472 */
473 IF NOT EXISTS ( SELECT 1
474 FROM #SkipChecks
475 WHERE DatabaseName IS NULL AND CheckID = 1 )
476 BEGIN
477
478 /*
479 Below, we check master.sys.databases looking for databases
480 that haven't had a backup in the last week. If we find any,
481 we insert them into #BlitzResults, the temp table that
482 tracks our server's problems. Note that if the check does
483 NOT find any problems, we don't save that. We're only
484 saving the problems, not the successful checks.
485 */
486 INSERT INTO #BlitzResults
487 ( CheckID ,
488 DatabaseName ,
489 Priority ,
490 FindingsGroup ,
491 Finding ,
492 URL ,
493 Details
494 )
495 SELECT 1 AS CheckID ,
496 d.[name] AS DatabaseName ,
497 1 AS Priority ,
498 'Backup' AS FindingsGroup ,
499 'Backups Not Performed Recently' AS Finding ,
500 'http://BrentOzar.com/go/nobak' AS URL ,
501 'Database ' + d.name + ' last backed up: '
502 + COALESCE(CAST(MAX(b.backup_finish_date) AS VARCHAR(25)),'never') AS Details
503 FROM master.sys.databases d
504 LEFT OUTER JOIN msdb.dbo.backupset b ON d.name COLLATE SQL_Latin1_General_CP1_CI_AS = b.database_name COLLATE SQL_Latin1_General_CP1_CI_AS
505 AND b.type = 'D'
506 AND b.server_name = SERVERPROPERTY('ServerName') /*Backupset ran on current server */
507 WHERE d.database_id <> 2 /* Bonus points if you know what that means */
508 AND d.state NOT IN(1, 6, 10) /* Not currently offline or restoring, like log shipping databases */
509 AND d.is_in_standby = 0 /* Not a log shipping target database */
510 AND d.source_database_id IS NULL /* Excludes database snapshots */
511 AND d.name NOT IN ( SELECT DISTINCT
512 DatabaseName
513 FROM #SkipChecks
514 WHERE CheckID IS NULL )
515 /*
516 The above NOT IN filters out the databases we're not supposed to check.
517 */
518 GROUP BY d.name
519 HAVING MAX(b.backup_finish_date) <= DATEADD(dd,
520 -7, GETDATE())
521 OR MAX(b.backup_finish_date) IS NULL;
522 /*
523 And there you have it. The rest of this stored procedure works the same
524 way: it asks:
525 - Should I skip this check?
526 - If not, do I find problems?
527 - Insert the results into #BlitzResults
528 */
529
530 END
531
532 /*
533 And that's the end of CheckID #1.
534
535 CheckID #2 is a little simpler because it only involves one query, and it's
536 more typical for queries that people contribute. But keep reading, because
537 the next check gets more complex again.
538 */
539
540 IF NOT EXISTS ( SELECT 1
541 FROM #SkipChecks
542 WHERE DatabaseName IS NULL AND CheckID = 2 )
543 BEGIN
544 INSERT INTO #BlitzResults
545 ( CheckID ,
546 DatabaseName ,
547 Priority ,
548 FindingsGroup ,
549 Finding ,
550 URL ,
551 Details
552 )
553 SELECT DISTINCT
554 2 AS CheckID ,
555 d.name AS DatabaseName ,
556 1 AS Priority ,
557 'Backup' AS FindingsGroup ,
558 'Full Recovery Mode w/o Log Backups' AS Finding ,
559 'http://BrentOzar.com/go/biglogs' AS URL ,
560 ( 'The ' + CAST(CAST((SELECT ((SUM([mf].[size]) * 8.) / 1024.) FROM sys.[master_files] AS [mf] WHERE [mf].[database_id] = d.[database_id] AND [mf].[type_desc] = 'LOG') AS DECIMAL(18,2)) AS VARCHAR) + 'MB log file has not been backed up in the last week.' ) AS Details
561 FROM master.sys.databases d
562 WHERE d.recovery_model IN ( 1, 2 )
563 AND d.database_id NOT IN ( 2, 3 )
564 AND d.source_database_id IS NULL
565 AND d.state <> 1 /* Not currently restoring, like log shipping databases */
566 AND d.is_in_standby = 0 /* Not a log shipping target database */
567 AND d.source_database_id IS NULL /* Excludes database snapshots */
568 AND d.name NOT IN ( SELECT DISTINCT
569 DatabaseName
570 FROM #SkipChecks
571 WHERE CheckID IS NULL )
572 AND NOT EXISTS ( SELECT *
573 FROM msdb.dbo.backupset b
574 WHERE d.name COLLATE SQL_Latin1_General_CP1_CI_AS = b.database_name COLLATE SQL_Latin1_General_CP1_CI_AS
575 AND b.type = 'L'
576 AND b.backup_finish_date >= DATEADD(dd,
577 -7, GETDATE()) );
578 END
579
580
581 /*
582 Next up, we've got CheckID 8. (These don't have to go in order.) This one
583 won't work on SQL Server 2005 because it relies on a new DMV that didn't
584 exist prior to SQL Server 2008. This means we have to check the SQL Server
585 version first, then build a dynamic string with the query we want to run:
586 */
587
588 IF NOT EXISTS ( SELECT 1
589 FROM #SkipChecks
590 WHERE DatabaseName IS NULL AND CheckID = 8 )
591 BEGIN
592 IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
593 AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
594 BEGIN
595 SET @StringToExecute = 'INSERT INTO #BlitzResults
596 (CheckID, Priority,
597 FindingsGroup,
598 Finding, URL,
599 Details)
600 SELECT 8 AS CheckID,
601 230 AS Priority,
602 ''Security'' AS FindingsGroup,
603 ''Server Audits Running'' AS Finding,
604 ''http://BrentOzar.com/go/audits'' AS URL,
605 (''SQL Server built-in audit functionality is being used by server audit: '' + [name]) AS Details FROM sys.dm_server_audit_status'
606 EXECUTE(@StringToExecute)
607 END;
608 END
609
610 /*
611 But what if you need to run a query in every individual database?
612 Hop down to the @CheckUserDatabaseObjects section.
613
614 And that's the basic idea! You can read through the rest of the
615 checks if you like - some more exciting stuff happens closer to the
616 end of the stored proc, where we start doing things like checking
617 the plan cache, but those aren't as cleanly commented.
618
619 If you'd like to contribute your own check, use one of the check
620 formats shown above and email it to Help@BrentOzar.com. You don't
621 have to pick a CheckID or a link - we'll take care of that when we
622 test and publish the code. Thanks!
623 */
624
625
626 IF NOT EXISTS ( SELECT 1
627 FROM #SkipChecks
628 WHERE DatabaseName IS NULL AND CheckID = 93 )
629 BEGIN
630 INSERT INTO #BlitzResults
631 ( CheckID ,
632 Priority ,
633 FindingsGroup ,
634 Finding ,
635 URL ,
636 Details
637 )
638 SELECT
639 93 AS CheckID ,
640 1 AS Priority ,
641 'Backup' AS FindingsGroup ,
642 'Backing Up to Same Drive Where Databases Reside' AS Finding ,
643 'http://BrentOzar.com/go/backup' AS URL ,
644 CAST(COUNT(1) AS VARCHAR(50)) + ' backups done on drive '
645 + UPPER(LEFT(bmf.physical_device_name, 3))
646 + ' in the last two weeks, where database files also live. This represents a serious risk if that array fails.' Details
647 FROM msdb.dbo.backupmediafamily AS bmf
648 INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id
649 AND bs.backup_start_date >= ( DATEADD(dd,
650 -14, GETDATE()) )
651 WHERE UPPER(LEFT(bmf.physical_device_name COLLATE SQL_Latin1_General_CP1_CI_AS, 3)) IN (
652 SELECT DISTINCT
653 UPPER(LEFT(mf.physical_name COLLATE SQL_Latin1_General_CP1_CI_AS, 3))
654 FROM sys.master_files AS mf )
655 GROUP BY UPPER(LEFT(bmf.physical_device_name, 3))
656 END
657
658
659 IF NOT EXISTS ( SELECT 1
660 FROM #SkipChecks
661 WHERE DatabaseName IS NULL AND CheckID = 119 )
662 AND EXISTS ( SELECT *
663 FROM sys.all_objects o
664 WHERE o.name = 'dm_database_encryption_keys' )
665 BEGIN
666 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, DatabaseName, URL, Details)
667 SELECT 119 AS CheckID,
668 1 AS Priority,
669 ''Backup'' AS FindingsGroup,
670 ''TDE Certificate Not Backed Up Recently'' AS Finding,
671 db_name(dek.database_id) AS DatabaseName,
672 ''http://BrentOzar.com/go/tde'' AS URL,
673 ''The certificate '' + c.name + '' is used to encrypt database '' + db_name(dek.database_id) + ''. Last backup date: '' + COALESCE(CAST(c.pvt_key_last_backup_date AS VARCHAR(100)), ''Never'') AS Details
674 FROM sys.certificates c INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint
675 WHERE pvt_key_last_backup_date IS NULL OR pvt_key_last_backup_date <= DATEADD(dd, -30, GETDATE())';
676 EXECUTE(@StringToExecute);
677 END
678
679
680 IF NOT EXISTS ( SELECT 1
681 FROM #SkipChecks
682 WHERE DatabaseName IS NULL AND CheckID = 3 )
683 BEGIN
684 INSERT INTO #BlitzResults
685 ( CheckID ,
686 DatabaseName ,
687 Priority ,
688 FindingsGroup ,
689 Finding ,
690 URL ,
691 Details
692 )
693 SELECT TOP 1
694 3 AS CheckID ,
695 'msdb' ,
696 200 AS Priority ,
697 'Backup' AS FindingsGroup ,
698 'MSDB Backup History Not Purged' AS Finding ,
699 'http://BrentOzar.com/go/history' AS URL ,
700 ( 'Database backup history retained back to '
701 + CAST(bs.backup_start_date AS VARCHAR(20)) ) AS Details
702 FROM msdb.dbo.backupset bs
703 WHERE bs.backup_start_date <= DATEADD(dd, -60,
704 GETDATE())
705 ORDER BY backup_set_id ASC;
706 END
707
708 IF NOT EXISTS ( SELECT 1
709 FROM #SkipChecks
710 WHERE DatabaseName IS NULL AND CheckID = 178 )
711 AND EXISTS (SELECT *
712 FROM msdb.dbo.backupset bs
713 WHERE bs.type = 'D'
714 AND bs.compressed_backup_size >= 50000000000 /* At least 50GB */
715 AND DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) <= 60 /* Backup took less than 60 seconds */
716 AND bs.backup_finish_date >= DATEADD(DAY, -14, GETDATE()) /* In the last 2 weeks */)
717 BEGIN
718 INSERT INTO #BlitzResults
719 ( CheckID ,
720 Priority ,
721 FindingsGroup ,
722 Finding ,
723 URL ,
724 Details
725 )
726 SELECT 178 AS CheckID ,
727 200 AS Priority ,
728 'Performance' AS FindingsGroup ,
729 'Snapshot Backups Occurring' AS Finding ,
730 'http://BrentOzar.com/go/snaps' AS URL ,
731 ( CAST(COUNT(*) AS VARCHAR(20)) + ' snapshot-looking backups have occurred in the last two weeks, indicating that IO may be freezing up.') AS Details
732 FROM msdb.dbo.backupset bs
733 WHERE bs.type = 'D'
734 AND bs.compressed_backup_size >= 50000000000 /* At least 50GB */
735 AND DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) <= 60 /* Backup took less than 60 seconds */
736 AND bs.backup_finish_date >= DATEADD(DAY, -14, GETDATE()) /* In the last 2 weeks */
737 END
738
739 IF NOT EXISTS ( SELECT 1
740 FROM #SkipChecks
741 WHERE DatabaseName IS NULL AND CheckID = 4 )
742 BEGIN
743 INSERT INTO #BlitzResults
744 ( CheckID ,
745 Priority ,
746 FindingsGroup ,
747 Finding ,
748 URL ,
749 Details
750 )
751 SELECT 4 AS CheckID ,
752 230 AS Priority ,
753 'Security' AS FindingsGroup ,
754 'Sysadmins' AS Finding ,
755 'http://BrentOzar.com/go/sa' AS URL ,
756 ( 'Login [' + l.name
757 + '] is a sysadmin - meaning they can do absolutely anything in SQL Server, including dropping databases or hiding their tracks.' ) AS Details
758 FROM master.sys.syslogins l
759 WHERE l.sysadmin = 1
760 AND l.name <> SUSER_SNAME(0x01)
761 AND l.denylogin = 0
762 AND l.name NOT LIKE 'NT SERVICE\%'
763 AND l.name <> 'l_certSignSmDetach'; /* Added in SQL 2016 */
764 END
765
766 IF NOT EXISTS ( SELECT 1
767 FROM #SkipChecks
768 WHERE DatabaseName IS NULL AND CheckID = 5 )
769 BEGIN
770 INSERT INTO #BlitzResults
771 ( CheckID ,
772 Priority ,
773 FindingsGroup ,
774 Finding ,
775 URL ,
776 Details
777 )
778 SELECT 5 AS CheckID ,
779 230 AS Priority ,
780 'Security' AS FindingsGroup ,
781 'Security Admins' AS Finding ,
782 'http://BrentOzar.com/go/sa' AS URL ,
783 ( 'Login [' + l.name
784 + '] 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
785 FROM master.sys.syslogins l
786 WHERE l.securityadmin = 1
787 AND l.name <> SUSER_SNAME(0x01)
788 AND l.denylogin = 0;
789 END
790
791 IF NOT EXISTS ( SELECT 1
792 FROM #SkipChecks
793 WHERE DatabaseName IS NULL AND CheckID = 104 )
794 BEGIN
795 INSERT INTO #BlitzResults
796 ( [CheckID] ,
797 [Priority] ,
798 [FindingsGroup] ,
799 [Finding] ,
800 [URL] ,
801 [Details]
802 )
803 SELECT 104 AS [CheckID] ,
804 230 AS [Priority] ,
805 'Security' AS [FindingsGroup] ,
806 'Login Can Control Server' AS [Finding] ,
807 'http://BrentOzar.com/go/sa' AS [URL] ,
808 'Login [' + pri.[name]
809 + '] has the CONTROL SERVER permission - meaning they can do absolutely anything in SQL Server, including dropping databases or hiding their tracks.' AS [Details]
810 FROM sys.server_principals AS pri
811 WHERE pri.[principal_id] IN (
812 SELECT p.[grantee_principal_id]
813 FROM sys.server_permissions AS p
814 WHERE p.[state] IN ( 'G', 'W' )
815 AND p.[class] = 100
816 AND p.[type] = 'CL' )
817 AND pri.[name] NOT LIKE '##%##'
818 END
819
820 IF NOT EXISTS ( SELECT 1
821 FROM #SkipChecks
822 WHERE DatabaseName IS NULL AND CheckID = 6 )
823 BEGIN
824 INSERT INTO #BlitzResults
825 ( CheckID ,
826 Priority ,
827 FindingsGroup ,
828 Finding ,
829 URL ,
830 Details
831 )
832 SELECT 6 AS CheckID ,
833 230 AS Priority ,
834 'Security' AS FindingsGroup ,
835 'Jobs Owned By Users' AS Finding ,
836 'http://BrentOzar.com/go/owners' AS URL ,
837 ( 'Job [' + j.name + '] is owned by ['
838 + SUSER_SNAME(j.owner_sid)
839 + '] - meaning if their login is disabled or not available due to Active Directory problems, the job will stop working.' ) AS Details
840 FROM msdb.dbo.sysjobs j
841 WHERE j.enabled = 1
842 AND SUSER_SNAME(j.owner_sid) <> SUSER_SNAME(0x01);
843 END
844
845
846 IF NOT EXISTS ( SELECT 1
847 FROM #SkipChecks
848 WHERE DatabaseName IS NULL AND CheckID = 7 )
849 BEGIN
850 INSERT INTO #BlitzResults
851 ( CheckID ,
852 Priority ,
853 FindingsGroup ,
854 Finding ,
855 URL ,
856 Details
857 )
858 SELECT 7 AS CheckID ,
859 230 AS Priority ,
860 'Security' AS FindingsGroup ,
861 'Stored Procedure Runs at Startup' AS Finding ,
862 'http://BrentOzar.com/go/startup' AS URL ,
863 ( 'Stored procedure [master].['
864 + r.SPECIFIC_SCHEMA + '].['
865 + r.SPECIFIC_NAME
866 + '] 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
867 FROM master.INFORMATION_SCHEMA.ROUTINES r
868 WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),
869 'ExecIsStartup') = 1;
870 END
871
872
873 IF NOT EXISTS ( SELECT 1
874 FROM #SkipChecks
875 WHERE DatabaseName IS NULL AND CheckID = 10 )
876 BEGIN
877 IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
878 AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
879 BEGIN
880 SET @StringToExecute = 'INSERT INTO #BlitzResults
881 (CheckID,
882 Priority,
883 FindingsGroup,
884 Finding,
885 URL,
886 Details)
887 SELECT 10 AS CheckID,
888 100 AS Priority,
889 ''Performance'' AS FindingsGroup,
890 ''Resource Governor Enabled'' AS Finding,
891 ''http://BrentOzar.com/go/rg'' AS URL,
892 (''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'
893 EXECUTE(@StringToExecute)
894 END;
895 END
896
897
898 IF NOT EXISTS ( SELECT 1
899 FROM #SkipChecks
900 WHERE DatabaseName IS NULL AND CheckID = 11 )
901 BEGIN
902 IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
903 BEGIN
904 SET @StringToExecute = 'INSERT INTO #BlitzResults
905 (CheckID,
906 Priority,
907 FindingsGroup,
908 Finding,
909 URL,
910 Details)
911 SELECT 11 AS CheckID,
912 100 AS Priority,
913 ''Performance'' AS FindingsGroup,
914 ''Server Triggers Enabled'' AS Finding,
915 ''http://BrentOzar.com/go/logontriggers/'' AS URL,
916 (''Server Trigger ['' + [name] ++ ''] is enabled. 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'
917 EXECUTE(@StringToExecute)
918 END;
919 END
920
921
922 IF NOT EXISTS ( SELECT 1
923 FROM #SkipChecks
924 WHERE DatabaseName IS NULL AND CheckID = 12 )
925 BEGIN
926 INSERT INTO #BlitzResults
927 ( CheckID ,
928 DatabaseName ,
929 Priority ,
930 FindingsGroup ,
931 Finding ,
932 URL ,
933 Details
934 )
935 SELECT 12 AS CheckID ,
936 [name] AS DatabaseName ,
937 10 AS Priority ,
938 'Performance' AS FindingsGroup ,
939 'Auto-Close Enabled' AS Finding ,
940 'http://BrentOzar.com/go/autoclose' AS URL ,
941 ( 'Database [' + [name]
942 + '] has auto-close enabled. This setting can dramatically decrease performance.' ) AS Details
943 FROM sys.databases
944 WHERE is_auto_close_on = 1
945 AND name NOT IN ( SELECT DISTINCT
946 DatabaseName
947 FROM #SkipChecks
948 WHERE CheckID IS NULL)
949 END
950
951
952 IF NOT EXISTS ( SELECT 1
953 FROM #SkipChecks
954 WHERE DatabaseName IS NULL AND CheckID = 13 )
955 BEGIN
956 INSERT INTO #BlitzResults
957 ( CheckID ,
958 DatabaseName ,
959 Priority ,
960 FindingsGroup ,
961 Finding ,
962 URL ,
963 Details
964 )
965 SELECT 13 AS CheckID ,
966 [name] AS DatabaseName ,
967 10 AS Priority ,
968 'Performance' AS FindingsGroup ,
969 'Auto-Shrink Enabled' AS Finding ,
970 'http://BrentOzar.com/go/autoshrink' AS URL ,
971 ( 'Database [' + [name]
972 + '] has auto-shrink enabled. This setting can dramatically decrease performance.' ) AS Details
973 FROM sys.databases
974 WHERE is_auto_shrink_on = 1
975 AND name NOT IN ( SELECT DISTINCT
976 DatabaseName
977 FROM #SkipChecks
978 WHERE CheckID IS NULL);
979 END
980
981
982 IF NOT EXISTS ( SELECT 1
983 FROM #SkipChecks
984 WHERE DatabaseName IS NULL AND CheckID = 14 )
985 BEGIN
986 IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
987 BEGIN
988 SET @StringToExecute = 'INSERT INTO #BlitzResults
989 (CheckID,
990 DatabaseName,
991 Priority,
992 FindingsGroup,
993 Finding,
994 URL,
995 Details)
996 SELECT 14 AS CheckID,
997 [name] as DatabaseName,
998 50 AS Priority,
999 ''Reliability'' AS FindingsGroup,
1000 ''Page Verification Not Optimal'' AS Finding,
1001 ''http://BrentOzar.com/go/torn'' AS URL,
1002 (''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
1003 FROM sys.databases
1004 WHERE page_verify_option < 2
1005 AND name <> ''tempdb''
1006 and name not in (select distinct DatabaseName from #SkipChecks)'
1007 EXECUTE(@StringToExecute)
1008 END;
1009 END
1010
1011
1012 IF NOT EXISTS ( SELECT 1
1013 FROM #SkipChecks
1014 WHERE DatabaseName IS NULL AND CheckID = 15 )
1015 BEGIN
1016 INSERT INTO #BlitzResults
1017 ( CheckID ,
1018 DatabaseName ,
1019 Priority ,
1020 FindingsGroup ,
1021 Finding ,
1022 URL ,
1023 Details
1024 )
1025 SELECT 15 AS CheckID ,
1026 [name] AS DatabaseName ,
1027 110 AS Priority ,
1028 'Performance' AS FindingsGroup ,
1029 'Auto-Create Stats Disabled' AS Finding ,
1030 'http://BrentOzar.com/go/acs' AS URL ,
1031 ( 'Database [' + [name]
1032 + '] 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
1033 FROM sys.databases
1034 WHERE is_auto_create_stats_on = 0
1035 AND name NOT IN ( SELECT DISTINCT
1036 DatabaseName
1037 FROM #SkipChecks
1038 WHERE CheckID IS NULL)
1039 END
1040
1041 IF NOT EXISTS ( SELECT 1
1042 FROM #SkipChecks
1043 WHERE DatabaseName IS NULL AND CheckID = 16 )
1044 BEGIN
1045 INSERT INTO #BlitzResults
1046 ( CheckID ,
1047 DatabaseName ,
1048 Priority ,
1049 FindingsGroup ,
1050 Finding ,
1051 URL ,
1052 Details
1053 )
1054 SELECT 16 AS CheckID ,
1055 [name] AS DatabaseName ,
1056 110 AS Priority ,
1057 'Performance' AS FindingsGroup ,
1058 'Auto-Update Stats Disabled' AS Finding ,
1059 'http://BrentOzar.com/go/aus' AS URL ,
1060 ( 'Database [' + [name]
1061 + '] 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
1062 FROM sys.databases
1063 WHERE is_auto_update_stats_on = 0
1064 AND name NOT IN ( SELECT DISTINCT
1065 DatabaseName
1066 FROM #SkipChecks
1067 WHERE CheckID IS NULL)
1068 END
1069
1070
1071 IF NOT EXISTS ( SELECT 1
1072 FROM #SkipChecks
1073 WHERE DatabaseName IS NULL AND CheckID = 17 )
1074 BEGIN
1075 INSERT INTO #BlitzResults
1076 ( CheckID ,
1077 DatabaseName ,
1078 Priority ,
1079 FindingsGroup ,
1080 Finding ,
1081 URL ,
1082 Details
1083 )
1084 SELECT 17 AS CheckID ,
1085 [name] AS DatabaseName ,
1086 150 AS Priority ,
1087 'Performance' AS FindingsGroup ,
1088 'Stats Updated Asynchronously' AS Finding ,
1089 'http://BrentOzar.com/go/asyncstats' AS URL ,
1090 ( 'Database [' + [name]
1091 + '] 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
1092 FROM sys.databases
1093 WHERE is_auto_update_stats_async_on = 1
1094 AND name NOT IN ( SELECT DISTINCT
1095 DatabaseName
1096 FROM #SkipChecks
1097 WHERE CheckID IS NULL)
1098 END
1099
1100
1101 IF NOT EXISTS ( SELECT 1
1102 FROM #SkipChecks
1103 WHERE DatabaseName IS NULL AND CheckID = 18 )
1104 BEGIN
1105 INSERT INTO #BlitzResults
1106 ( CheckID ,
1107 DatabaseName ,
1108 Priority ,
1109 FindingsGroup ,
1110 Finding ,
1111 URL ,
1112 Details
1113 )
1114 SELECT 18 AS CheckID ,
1115 [name] AS DatabaseName ,
1116 150 AS Priority ,
1117 'Performance' AS FindingsGroup ,
1118 'Forced Parameterization On' AS Finding ,
1119 'http://BrentOzar.com/go/forced' AS URL ,
1120 ( 'Database [' + [name]
1121 + '] 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
1122 FROM sys.databases
1123 WHERE is_parameterization_forced = 1
1124 AND name NOT IN ( SELECT DatabaseName
1125 FROM #SkipChecks
1126 WHERE CheckID IS NULL)
1127 END
1128
1129
1130 IF NOT EXISTS ( SELECT 1
1131 FROM #SkipChecks
1132 WHERE DatabaseName IS NULL AND CheckID = 20 )
1133 BEGIN
1134 INSERT INTO #BlitzResults
1135 ( CheckID ,
1136 DatabaseName ,
1137 Priority ,
1138 FindingsGroup ,
1139 Finding ,
1140 URL ,
1141 Details
1142 )
1143 SELECT 20 AS CheckID ,
1144 [name] AS DatabaseName ,
1145 200 AS Priority ,
1146 'Informational' AS FindingsGroup ,
1147 'Date Correlation On' AS Finding ,
1148 'http://BrentOzar.com/go/corr' AS URL ,
1149 ( 'Database [' + [name]
1150 + '] 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
1151 FROM sys.databases
1152 WHERE is_date_correlation_on = 1
1153 AND name NOT IN ( SELECT DISTINCT
1154 DatabaseName
1155 FROM #SkipChecks
1156 WHERE CheckID IS NULL)
1157 END
1158
1159
1160 IF NOT EXISTS ( SELECT 1
1161 FROM #SkipChecks
1162 WHERE DatabaseName IS NULL AND CheckID = 21 )
1163 BEGIN
1164 IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
1165 AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
1166 BEGIN
1167 SET @StringToExecute = 'INSERT INTO #BlitzResults
1168 (CheckID,
1169 DatabaseName,
1170 Priority,
1171 FindingsGroup,
1172 Finding,
1173 URL,
1174 Details)
1175 SELECT 21 AS CheckID,
1176 [name] as DatabaseName,
1177 200 AS Priority,
1178 ''Informational'' AS FindingsGroup,
1179 ''Database Encrypted'' AS Finding,
1180 ''http://BrentOzar.com/go/tde'' AS URL,
1181 (''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
1182 FROM sys.databases
1183 WHERE is_encrypted = 1
1184 and name not in (select distinct DatabaseName from #SkipChecks)'
1185 EXECUTE(@StringToExecute)
1186 END;
1187 END
1188
1189 /*
1190 Believe it or not, SQL Server doesn't track the default values
1191 for sp_configure options! We'll make our own list here.
1192 */
1193 INSERT INTO #ConfigurationDefaults
1194 VALUES ( 'access check cache bucket count', 0, 1001 );
1195 INSERT INTO #ConfigurationDefaults
1196 VALUES ( 'access check cache quota', 0, 1002 );
1197 INSERT INTO #ConfigurationDefaults
1198 VALUES ( 'Ad Hoc Distributed Queries', 0, 1003 );
1199 INSERT INTO #ConfigurationDefaults
1200 VALUES ( 'affinity I/O mask', 0, 1004 );
1201 INSERT INTO #ConfigurationDefaults
1202 VALUES ( 'affinity mask', 0, 1005 );
1203 INSERT INTO #ConfigurationDefaults
1204 VALUES ( 'affinity64 mask', 0, 1066 );
1205 INSERT INTO #ConfigurationDefaults
1206 VALUES ( 'affinity64 I/O mask', 0, 1067 );
1207 INSERT INTO #ConfigurationDefaults
1208 VALUES ( 'Agent XPs', 0, 1071 );
1209 INSERT INTO #ConfigurationDefaults
1210 VALUES ( 'allow updates', 0, 1007 );
1211 INSERT INTO #ConfigurationDefaults
1212 VALUES ( 'awe enabled', 0, 1008 );
1213 INSERT INTO #ConfigurationDefaults
1214 VALUES ( 'backup checksum default', 0, 1070 );
1215 INSERT INTO #ConfigurationDefaults
1216 VALUES ( 'backup compression default', 0, 1073 );
1217 INSERT INTO #ConfigurationDefaults
1218 VALUES ( 'blocked process threshold', 0, 1009 );
1219 INSERT INTO #ConfigurationDefaults
1220 VALUES ( 'blocked process threshold (s)', 0, 1009 );
1221 INSERT INTO #ConfigurationDefaults
1222 VALUES ( 'c2 audit mode', 0, 1010 );
1223 INSERT INTO #ConfigurationDefaults
1224 VALUES ( 'clr enabled', 0, 1011 );
1225 INSERT INTO #ConfigurationDefaults
1226 VALUES ( 'common criteria compliance enabled', 0, 1074 );
1227 INSERT INTO #ConfigurationDefaults
1228 VALUES ( 'contained database authentication', 0, 1068 );
1229 INSERT INTO #ConfigurationDefaults
1230 VALUES ( 'cost threshold for parallelism', 5, 1012 );
1231 INSERT INTO #ConfigurationDefaults
1232 VALUES ( 'cross db ownership chaining', 0, 1013 );
1233 INSERT INTO #ConfigurationDefaults
1234 VALUES ( 'cursor threshold', -1, 1014 );
1235 INSERT INTO #ConfigurationDefaults
1236 VALUES ( 'Database Mail XPs', 0, 1072 );
1237 INSERT INTO #ConfigurationDefaults
1238 VALUES ( 'default full-text language', 1033, 1016 );
1239 INSERT INTO #ConfigurationDefaults
1240 VALUES ( 'default language', 0, 1017 );
1241 INSERT INTO #ConfigurationDefaults
1242 VALUES ( 'default trace enabled', 1, 1018 );
1243 INSERT INTO #ConfigurationDefaults
1244 VALUES ( 'disallow results from triggers', 0, 1019 );
1245 INSERT INTO #ConfigurationDefaults
1246 VALUES ( 'EKM provider enabled', 0, 1075 );
1247 INSERT INTO #ConfigurationDefaults
1248 VALUES ( 'filestream access level', 0, 1076 );
1249 INSERT INTO #ConfigurationDefaults
1250 VALUES ( 'fill factor (%)', 0, 1020 );
1251 INSERT INTO #ConfigurationDefaults
1252 VALUES ( 'ft crawl bandwidth (max)', 100, 1021 );
1253 INSERT INTO #ConfigurationDefaults
1254 VALUES ( 'ft crawl bandwidth (min)', 0, 1022 );
1255 INSERT INTO #ConfigurationDefaults
1256 VALUES ( 'ft notify bandwidth (max)', 100, 1023 );
1257 INSERT INTO #ConfigurationDefaults
1258 VALUES ( 'ft notify bandwidth (min)', 0, 1024 );
1259 INSERT INTO #ConfigurationDefaults
1260 VALUES ( 'index create memory (KB)', 0, 1025 );
1261 INSERT INTO #ConfigurationDefaults
1262 VALUES ( 'in-doubt xact resolution', 0, 1026 );
1263 INSERT INTO #ConfigurationDefaults
1264 VALUES ( 'lightweight pooling', 0, 1027 );
1265 INSERT INTO #ConfigurationDefaults
1266 VALUES ( 'locks', 0, 1028 );
1267 INSERT INTO #ConfigurationDefaults
1268 VALUES ( 'max degree of parallelism', 0, 1029 );
1269 INSERT INTO #ConfigurationDefaults
1270 VALUES ( 'max full-text crawl range', 4, 1030 );
1271 INSERT INTO #ConfigurationDefaults
1272 VALUES ( 'max server memory (MB)', 2147483647, 1031 );
1273 INSERT INTO #ConfigurationDefaults
1274 VALUES ( 'max text repl size (B)', 65536, 1032 );
1275 INSERT INTO #ConfigurationDefaults
1276 VALUES ( 'max worker threads', 0, 1033 );
1277 INSERT INTO #ConfigurationDefaults
1278 VALUES ( 'media retention', 0, 1034 );
1279 INSERT INTO #ConfigurationDefaults
1280 VALUES ( 'min memory per query (KB)', 1024, 1035 );
1281 /* Accepting both 0 and 16 below because both have been seen in the wild as defaults. */
1282 IF EXISTS ( SELECT *
1283 FROM sys.configurations
1284 WHERE name = 'min server memory (MB)'
1285 AND value_in_use IN ( 0, 16 ) )
1286 INSERT INTO #ConfigurationDefaults
1287 SELECT 'min server memory (MB)' ,
1288 CAST(value_in_use AS BIGINT), 1036
1289 FROM sys.configurations
1290 WHERE name = 'min server memory (MB)'
1291 ELSE
1292 INSERT INTO #ConfigurationDefaults
1293 VALUES ( 'min server memory (MB)', 0, 1036 );
1294 INSERT INTO #ConfigurationDefaults
1295 VALUES ( 'nested triggers', 1, 1037 );
1296 INSERT INTO #ConfigurationDefaults
1297 VALUES ( 'network packet size (B)', 4096, 1038 );
1298 INSERT INTO #ConfigurationDefaults
1299 VALUES ( 'Ole Automation Procedures', 0, 1039 );
1300 INSERT INTO #ConfigurationDefaults
1301 VALUES ( 'open objects', 0, 1040 );
1302 INSERT INTO #ConfigurationDefaults
1303 VALUES ( 'optimize for ad hoc workloads', 0, 1041 );
1304 INSERT INTO #ConfigurationDefaults
1305 VALUES ( 'PH timeout (s)', 60, 1042 );
1306 INSERT INTO #ConfigurationDefaults
1307 VALUES ( 'precompute rank', 0, 1043 );
1308 INSERT INTO #ConfigurationDefaults
1309 VALUES ( 'priority boost', 0, 1044 );
1310 INSERT INTO #ConfigurationDefaults
1311 VALUES ( 'query governor cost limit', 0, 1045 );
1312 INSERT INTO #ConfigurationDefaults
1313 VALUES ( 'query wait (s)', -1, 1046 );
1314 INSERT INTO #ConfigurationDefaults
1315 VALUES ( 'recovery interval (min)', 0, 1047 );
1316 INSERT INTO #ConfigurationDefaults
1317 VALUES ( 'remote access', 1, 1048 );
1318 INSERT INTO #ConfigurationDefaults
1319 VALUES ( 'remote admin connections', 0, 1049 );
1320 /* SQL Server 2012 changes a configuration default */
1321 IF @@VERSION LIKE '%Microsoft SQL Server 2005%'
1322 OR @@VERSION LIKE '%Microsoft SQL Server 2008%'
1323 BEGIN
1324 INSERT INTO #ConfigurationDefaults
1325 VALUES ( 'remote login timeout (s)', 20, 1069 );
1326 END
1327 ELSE
1328 BEGIN
1329 INSERT INTO #ConfigurationDefaults
1330 VALUES ( 'remote login timeout (s)', 10, 1069 );
1331 END
1332 INSERT INTO #ConfigurationDefaults
1333 VALUES ( 'remote proc trans', 0, 1050 );
1334 INSERT INTO #ConfigurationDefaults
1335 VALUES ( 'remote query timeout (s)', 600, 1051 );
1336 INSERT INTO #ConfigurationDefaults
1337 VALUES ( 'Replication XPs', 0, 1052 );
1338 INSERT INTO #ConfigurationDefaults
1339 VALUES ( 'RPC parameter data validation', 0, 1053 );
1340 INSERT INTO #ConfigurationDefaults
1341 VALUES ( 'scan for startup procs', 0, 1054 );
1342 INSERT INTO #ConfigurationDefaults
1343 VALUES ( 'server trigger recursion', 1, 1055 );
1344 INSERT INTO #ConfigurationDefaults
1345 VALUES ( 'set working set size', 0, 1056 );
1346 INSERT INTO #ConfigurationDefaults
1347 VALUES ( 'show advanced options', 0, 1057 );
1348 INSERT INTO #ConfigurationDefaults
1349 VALUES ( 'SMO and DMO XPs', 1, 1058 );
1350 INSERT INTO #ConfigurationDefaults
1351 VALUES ( 'SQL Mail XPs', 0, 1059 );
1352 INSERT INTO #ConfigurationDefaults
1353 VALUES ( 'transform noise words', 0, 1060 );
1354 INSERT INTO #ConfigurationDefaults
1355 VALUES ( 'two digit year cutoff', 2049, 1061 );
1356 INSERT INTO #ConfigurationDefaults
1357 VALUES ( 'user connections', 0, 1062 );
1358 INSERT INTO #ConfigurationDefaults
1359 VALUES ( 'user options', 0, 1063 );
1360 INSERT INTO #ConfigurationDefaults
1361 VALUES ( 'Web Assistant Procedures', 0, 1064 );
1362 INSERT INTO #ConfigurationDefaults
1363 VALUES ( 'xp_cmdshell', 0, 1065 );
1364
1365
1366 IF NOT EXISTS ( SELECT 1
1367 FROM #SkipChecks
1368 WHERE DatabaseName IS NULL AND CheckID = 22 )
1369 BEGIN
1370 INSERT INTO #BlitzResults
1371 ( CheckID ,
1372 Priority ,
1373 FindingsGroup ,
1374 Finding ,
1375 URL ,
1376 Details
1377 )
1378 SELECT cd.CheckID ,
1379 200 AS Priority ,
1380 'Non-Default Server Config' AS FindingsGroup ,
1381 cr.name AS Finding ,
1382 'http://BrentOzar.com/go/conf' AS URL ,
1383 ( 'This sp_configure option has been changed. Its default value is '
1384 + COALESCE(CAST(cd.[DefaultValue] AS VARCHAR(100)),
1385 '(unknown)')
1386 + ' and it has been set to '
1387 + CAST(cr.value_in_use AS VARCHAR(100))
1388 + '.' ) AS Details
1389 FROM sys.configurations cr
1390 INNER JOIN #ConfigurationDefaults cd ON cd.name = cr.name
1391 LEFT OUTER JOIN #ConfigurationDefaults cdUsed ON cdUsed.name = cr.name
1392 AND cdUsed.DefaultValue = cr.value_in_use
1393 WHERE cdUsed.name IS NULL;
1394 END
1395
1396
1397 IF NOT EXISTS ( SELECT 1
1398 FROM #SkipChecks
1399 WHERE DatabaseName IS NULL AND CheckID = 24 )
1400 BEGIN
1401 INSERT INTO #BlitzResults
1402 ( CheckID ,
1403 DatabaseName ,
1404 Priority ,
1405 FindingsGroup ,
1406 Finding ,
1407 URL ,
1408 Details
1409 )
1410 SELECT DISTINCT
1411 24 AS CheckID ,
1412 DB_NAME(database_id) AS DatabaseName ,
1413 170 AS Priority ,
1414 'File Configuration' AS FindingsGroup ,
1415 'System Database on C Drive' AS Finding ,
1416 'http://BrentOzar.com/go/cdrive' AS URL ,
1417 ( 'The ' + DB_NAME(database_id)
1418 + ' 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
1419 FROM sys.master_files
1420 WHERE UPPER(LEFT(physical_name, 1)) = 'C'
1421 AND DB_NAME(database_id) IN ( 'master',
1422 'model', 'msdb' );
1423 END
1424
1425
1426 IF NOT EXISTS ( SELECT 1
1427 FROM #SkipChecks
1428 WHERE DatabaseName IS NULL AND CheckID = 25 )
1429 BEGIN
1430 INSERT INTO #BlitzResults
1431 ( CheckID ,
1432 DatabaseName ,
1433 Priority ,
1434 FindingsGroup ,
1435 Finding ,
1436 URL ,
1437 Details
1438 )
1439 SELECT TOP 1
1440 25 AS CheckID ,
1441 'tempdb' ,
1442 170 AS Priority ,
1443 'File Configuration' AS FindingsGroup ,
1444 'TempDB on C Drive' AS Finding ,
1445 'http://BrentOzar.com/go/cdrive' AS URL ,
1446 CASE WHEN growth > 0
1447 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.' )
1448 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.' )
1449 END AS Details
1450 FROM sys.master_files
1451 WHERE UPPER(LEFT(physical_name, 1)) = 'C'
1452 AND DB_NAME(database_id) = 'tempdb';
1453 END
1454
1455
1456 IF NOT EXISTS ( SELECT 1
1457 FROM #SkipChecks
1458 WHERE DatabaseName IS NULL AND CheckID = 26 )
1459 BEGIN
1460 INSERT INTO #BlitzResults
1461 ( CheckID ,
1462 DatabaseName ,
1463 Priority ,
1464 FindingsGroup ,
1465 Finding ,
1466 URL ,
1467 Details
1468 )
1469 SELECT DISTINCT
1470 26 AS CheckID ,
1471 DB_NAME(database_id) AS DatabaseName ,
1472 20 AS Priority ,
1473 'Reliability' AS FindingsGroup ,
1474 'User Databases on C Drive' AS Finding ,
1475 'http://BrentOzar.com/go/cdrive' AS URL ,
1476 ( 'The ' + DB_NAME(database_id)
1477 + ' 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
1478 FROM sys.master_files
1479 WHERE UPPER(LEFT(physical_name, 1)) = 'C'
1480 AND DB_NAME(database_id) NOT IN ( 'master',
1481 'model', 'msdb',
1482 'tempdb' )
1483 AND DB_NAME(database_id) NOT IN (
1484 SELECT DISTINCT
1485 DatabaseName
1486 FROM #SkipChecks )
1487 END
1488
1489
1490 IF NOT EXISTS ( SELECT 1
1491 FROM #SkipChecks
1492 WHERE DatabaseName IS NULL AND CheckID = 27 )
1493 BEGIN
1494 INSERT INTO #BlitzResults
1495 ( CheckID ,
1496 DatabaseName ,
1497 Priority ,
1498 FindingsGroup ,
1499 Finding ,
1500 URL ,
1501 Details
1502 )
1503 SELECT 27 AS CheckID ,
1504 'master' AS DatabaseName ,
1505 200 AS Priority ,
1506 'Informational' AS FindingsGroup ,
1507 'Tables in the Master Database' AS Finding ,
1508 'http://BrentOzar.com/go/mastuser' AS URL ,
1509 ( 'The ' + name
1510 + ' table in the master database was created by end users on '
1511 + CAST(create_date AS VARCHAR(20))
1512 + '. Tables in the master database may not be restored in the event of a disaster.' ) AS Details
1513 FROM master.sys.tables
1514 WHERE is_ms_shipped = 0;
1515 END
1516
1517
1518 IF NOT EXISTS ( SELECT 1
1519 FROM #SkipChecks
1520 WHERE DatabaseName IS NULL AND CheckID = 28 )
1521 BEGIN
1522 INSERT INTO #BlitzResults
1523 ( CheckID ,
1524 Priority ,
1525 FindingsGroup ,
1526 Finding ,
1527 URL ,
1528 Details
1529 )
1530 SELECT 28 AS CheckID ,
1531 200 AS Priority ,
1532 'Informational' AS FindingsGroup ,
1533 'Tables in the MSDB Database' AS Finding ,
1534 'http://BrentOzar.com/go/msdbuser' AS URL ,
1535 ( 'The ' + name
1536 + ' table in the msdb database was created by end users on '
1537 + CAST(create_date AS VARCHAR(20))
1538 + '. Tables in the msdb database may not be restored in the event of a disaster.' ) AS Details
1539 FROM msdb.sys.tables
1540 WHERE is_ms_shipped = 0 AND name NOT LIKE '%DTA_%';
1541 END
1542
1543
1544 IF NOT EXISTS ( SELECT 1
1545 FROM #SkipChecks
1546 WHERE DatabaseName IS NULL AND CheckID = 29 )
1547 BEGIN
1548 INSERT INTO #BlitzResults
1549 ( CheckID ,
1550 Priority ,
1551 FindingsGroup ,
1552 Finding ,
1553 URL ,
1554 Details
1555 )
1556 SELECT 29 AS CheckID ,
1557 200 AS Priority ,
1558 'Informational' AS FindingsGroup ,
1559 'Tables in the Model Database' AS Finding ,
1560 'http://BrentOzar.com/go/model' AS URL ,
1561 ( 'The ' + name
1562 + ' table in the model database was created by end users on '
1563 + CAST(create_date AS VARCHAR(20))
1564 + '. Tables in the model database are automatically copied into all new databases.' ) AS Details
1565 FROM model.sys.tables
1566 WHERE is_ms_shipped = 0;
1567 END
1568
1569
1570 IF NOT EXISTS ( SELECT 1
1571 FROM #SkipChecks
1572 WHERE DatabaseName IS NULL AND CheckID = 30 )
1573 BEGIN
1574 IF ( SELECT COUNT(*)
1575 FROM msdb.dbo.sysalerts
1576 WHERE severity BETWEEN 19 AND 25
1577 ) < 7
1578 INSERT INTO #BlitzResults
1579 ( CheckID ,
1580 Priority ,
1581 FindingsGroup ,
1582 Finding ,
1583 URL ,
1584 Details
1585 )
1586 SELECT 30 AS CheckID ,
1587 200 AS Priority ,
1588 'Monitoring' AS FindingsGroup ,
1589 'Not All Alerts Configured' AS Finding ,
1590 'http://BrentOzar.com/go/alert' AS URL ,
1591 ( '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;
1592 END
1593
1594
1595
1596 IF NOT EXISTS ( SELECT 1
1597 FROM #SkipChecks
1598 WHERE DatabaseName IS NULL AND CheckID = 59 )
1599 BEGIN
1600 IF EXISTS ( SELECT *
1601 FROM msdb.dbo.sysalerts
1602 WHERE enabled = 1
1603 AND COALESCE(has_notification, 0) = 0
1604 AND (job_id IS NULL OR job_id = 0x))
1605 INSERT INTO #BlitzResults
1606 ( CheckID ,
1607 Priority ,
1608 FindingsGroup ,
1609 Finding ,
1610 URL ,
1611 Details
1612 )
1613 SELECT 59 AS CheckID ,
1614 200 AS Priority ,
1615 'Monitoring' AS FindingsGroup ,
1616 'Alerts Configured without Follow Up' AS Finding ,
1617 'http://BrentOzar.com/go/alert' AS URL ,
1618 ( '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;
1619 END
1620
1621 IF NOT EXISTS ( SELECT 1
1622 FROM #SkipChecks
1623 WHERE DatabaseName IS NULL AND CheckID = 96 )
1624 BEGIN
1625 IF NOT EXISTS ( SELECT *
1626 FROM msdb.dbo.sysalerts
1627 WHERE message_id IN ( 823, 824, 825 ) )
1628 INSERT INTO #BlitzResults
1629 ( CheckID ,
1630 Priority ,
1631 FindingsGroup ,
1632 Finding ,
1633 URL ,
1634 Details
1635 )
1636 SELECT 96 AS CheckID ,
1637 200 AS Priority ,
1638 'Monitoring' AS FindingsGroup ,
1639 'No Alerts for Corruption' AS Finding ,
1640 'http://BrentOzar.com/go/alert' AS URL ,
1641 ( '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;
1642 END
1643
1644
1645 IF NOT EXISTS ( SELECT 1
1646 FROM #SkipChecks
1647 WHERE DatabaseName IS NULL AND CheckID = 61 )
1648 BEGIN
1649 IF NOT EXISTS ( SELECT *
1650 FROM msdb.dbo.sysalerts
1651 WHERE severity BETWEEN 19 AND 25 )
1652 INSERT INTO #BlitzResults
1653 ( CheckID ,
1654 Priority ,
1655 FindingsGroup ,
1656 Finding ,
1657 URL ,
1658 Details
1659 )
1660 SELECT 61 AS CheckID ,
1661 200 AS Priority ,
1662 'Monitoring' AS FindingsGroup ,
1663 'No Alerts for Sev 19-25' AS Finding ,
1664 'http://BrentOzar.com/go/alert' AS URL ,
1665 ( '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;
1666 END
1667
1668 --check for disabled alerts
1669 IF NOT EXISTS ( SELECT 1
1670 FROM #SkipChecks
1671 WHERE DatabaseName IS NULL AND CheckID = 98 )
1672 BEGIN
1673 IF EXISTS ( SELECT name
1674 FROM msdb.dbo.sysalerts
1675 WHERE enabled = 0 )
1676 INSERT INTO #BlitzResults
1677 ( CheckID ,
1678 Priority ,
1679 FindingsGroup ,
1680 Finding ,
1681 URL ,
1682 Details
1683 )
1684 SELECT 98 AS CheckID ,
1685 200 AS Priority ,
1686 'Monitoring' AS FindingsGroup ,
1687 'Alerts Disabled' AS Finding ,
1688 'http://www.BrentOzar.com/go/alerts/' AS URL ,
1689 ( 'The following Alert is disabled, please review and enable if desired: '
1690 + name ) AS Details
1691 FROM msdb.dbo.sysalerts
1692 WHERE enabled = 0
1693 END
1694
1695
1696 IF NOT EXISTS ( SELECT 1
1697 FROM #SkipChecks
1698 WHERE DatabaseName IS NULL AND CheckID = 31 )
1699 BEGIN
1700 IF NOT EXISTS ( SELECT *
1701 FROM msdb.dbo.sysoperators
1702 WHERE enabled = 1 )
1703 INSERT INTO #BlitzResults
1704 ( CheckID ,
1705 Priority ,
1706 FindingsGroup ,
1707 Finding ,
1708 URL ,
1709 Details
1710 )
1711 SELECT 31 AS CheckID ,
1712 200 AS Priority ,
1713 'Monitoring' AS FindingsGroup ,
1714 'No Operators Configured/Enabled' AS Finding ,
1715 'http://BrentOzar.com/go/op' AS URL ,
1716 ( '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;
1717 END
1718
1719
1720
1721 IF NOT EXISTS ( SELECT 1
1722 FROM #SkipChecks
1723 WHERE DatabaseName IS NULL AND CheckID = 34 )
1724 BEGIN
1725 IF EXISTS ( SELECT *
1726 FROM sys.all_objects
1727 WHERE name = 'dm_db_mirroring_auto_page_repair' )
1728 BEGIN
1729 SET @StringToExecute = 'INSERT INTO #BlitzResults
1730 (CheckID,
1731 DatabaseName,
1732 Priority,
1733 FindingsGroup,
1734 Finding,
1735 URL,
1736 Details)
1737 SELECT DISTINCT
1738 34 AS CheckID ,
1739 db.name ,
1740 1 AS Priority ,
1741 ''Corruption'' AS FindingsGroup ,
1742 ''Database Corruption Detected'' AS Finding ,
1743 ''http://BrentOzar.com/go/repair'' AS URL ,
1744 ( ''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
1745 FROM (SELECT rp2.database_id, rp2.modification_time
1746 FROM sys.dm_db_mirroring_auto_page_repair rp2
1747 WHERE rp2.[database_id] not in (
1748 SELECT db2.[database_id]
1749 FROM sys.databases as db2
1750 WHERE db2.[state] = 1
1751 ) ) as rp
1752 INNER JOIN master.sys.databases db ON rp.database_id = db.database_id
1753 WHERE rp.modification_time >= DATEADD(dd, -30, GETDATE()) ;'
1754 EXECUTE(@StringToExecute)
1755 END;
1756 END
1757
1758 IF NOT EXISTS ( SELECT 1
1759 FROM #SkipChecks
1760 WHERE DatabaseName IS NULL AND CheckID = 89 )
1761 BEGIN
1762 IF EXISTS ( SELECT *
1763 FROM sys.all_objects
1764 WHERE name = 'dm_hadr_auto_page_repair' )
1765 BEGIN
1766 SET @StringToExecute = 'INSERT INTO #BlitzResults
1767 (CheckID,
1768 DatabaseName,
1769 Priority,
1770 FindingsGroup,
1771 Finding,
1772 URL,
1773 Details)
1774 SELECT DISTINCT
1775 89 AS CheckID ,
1776 db.name ,
1777 1 AS Priority ,
1778 ''Corruption'' AS FindingsGroup ,
1779 ''Database Corruption Detected'' AS Finding ,
1780 ''http://BrentOzar.com/go/repair'' AS URL ,
1781 ( ''AlwaysOn has automatically repaired at least one corrupt page in the last 30 days. For more information, query the DMV sys.dm_hadr_auto_page_repair.'' ) AS Details
1782 FROM sys.dm_hadr_auto_page_repair rp
1783 INNER JOIN master.sys.databases db ON rp.database_id = db.database_id
1784 WHERE rp.modification_time >= DATEADD(dd, -30, GETDATE()) ;'
1785 EXECUTE(@StringToExecute)
1786 END;
1787 END
1788
1789
1790 IF NOT EXISTS ( SELECT 1
1791 FROM #SkipChecks
1792 WHERE DatabaseName IS NULL AND CheckID = 90 )
1793 BEGIN
1794 IF EXISTS ( SELECT *
1795 FROM msdb.sys.all_objects
1796 WHERE name = 'suspect_pages' )
1797 BEGIN
1798 SET @StringToExecute = 'INSERT INTO #BlitzResults
1799 (CheckID,
1800 DatabaseName,
1801 Priority,
1802 FindingsGroup,
1803 Finding,
1804 URL,
1805 Details)
1806 SELECT DISTINCT
1807 90 AS CheckID ,
1808 db.name ,
1809 1 AS Priority ,
1810 ''Corruption'' AS FindingsGroup ,
1811 ''Database Corruption Detected'' AS Finding ,
1812 ''http://BrentOzar.com/go/repair'' AS URL ,
1813 ( ''SQL Server has detected at least one corrupt page in the last 30 days. For more information, query the system table msdb.dbo.suspect_pages.'' ) AS Details
1814 FROM msdb.dbo.suspect_pages sp
1815 INNER JOIN master.sys.databases db ON sp.database_id = db.database_id
1816 WHERE sp.last_update_date >= DATEADD(dd, -30, GETDATE()) ;'
1817 EXECUTE(@StringToExecute)
1818 END;
1819 END
1820
1821
1822 IF NOT EXISTS ( SELECT 1
1823 FROM #SkipChecks
1824 WHERE DatabaseName IS NULL AND CheckID = 36 )
1825 BEGIN
1826 INSERT INTO #BlitzResults
1827 ( CheckID ,
1828 Priority ,
1829 FindingsGroup ,
1830 Finding ,
1831 URL ,
1832 Details
1833 )
1834 SELECT DISTINCT
1835 36 AS CheckID ,
1836 150 AS Priority ,
1837 'Performance' AS FindingsGroup ,
1838 'Slow Storage Reads on Drive '
1839 + UPPER(LEFT(mf.physical_name, 1)) AS Finding ,
1840 'http://BrentOzar.com/go/slow' AS URL ,
1841 'Reads are averaging longer than 200ms for at least one database on this drive. For specific database file speeds, run the query from the information link.' AS Details
1842 FROM sys.dm_io_virtual_file_stats(NULL, NULL)
1843 AS fs
1844 INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
1845 AND fs.[file_id] = mf.[file_id]
1846 WHERE ( io_stall_read_ms / ( 1.0 + num_of_reads ) ) > 200
1847 AND num_of_reads > 100000;
1848 END
1849
1850 IF NOT EXISTS ( SELECT 1
1851 FROM #SkipChecks
1852 WHERE DatabaseName IS NULL AND CheckID = 37 )
1853 BEGIN
1854 INSERT INTO #BlitzResults
1855 ( CheckID ,
1856 Priority ,
1857 FindingsGroup ,
1858 Finding ,
1859 URL ,
1860 Details
1861 )
1862 SELECT DISTINCT
1863 37 AS CheckID ,
1864 150 AS Priority ,
1865 'Performance' AS FindingsGroup ,
1866 'Slow Storage Writes on Drive '
1867 + UPPER(LEFT(mf.physical_name, 1)) AS Finding ,
1868 'http://BrentOzar.com/go/slow' AS URL ,
1869 'Writes 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
1870 FROM sys.dm_io_virtual_file_stats(NULL, NULL)
1871 AS fs
1872 INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
1873 AND fs.[file_id] = mf.[file_id]
1874 WHERE ( io_stall_write_ms / ( 1.0
1875 + num_of_writes ) ) > 100
1876 AND num_of_writes > 100000;
1877 END
1878
1879 IF NOT EXISTS ( SELECT 1
1880 FROM #SkipChecks
1881 WHERE DatabaseName IS NULL AND CheckID = 40 )
1882 BEGIN
1883 IF ( SELECT COUNT(*)
1884 FROM tempdb.sys.database_files
1885 WHERE type_desc = 'ROWS'
1886 ) = 1
1887 BEGIN
1888 INSERT INTO #BlitzResults
1889 ( CheckID ,
1890 DatabaseName ,
1891 Priority ,
1892 FindingsGroup ,
1893 Finding ,
1894 URL ,
1895 Details
1896 )
1897 VALUES ( 40 ,
1898 'tempdb' ,
1899 170 ,
1900 'File Configuration' ,
1901 'TempDB Only Has 1 Data File' ,
1902 'http://BrentOzar.com/go/tempdb' ,
1903 'TempDB is only configured with one data file. More data files are usually required to alleviate SGAM contention.'
1904 );
1905 END;
1906 END
1907
1908
1909 IF NOT EXISTS ( SELECT 1
1910 FROM #SkipChecks
1911 WHERE DatabaseName IS NULL AND CheckID = 44 )
1912 BEGIN
1913 INSERT INTO #BlitzResults
1914 ( CheckID ,
1915 Priority ,
1916 FindingsGroup ,
1917 Finding ,
1918 URL ,
1919 Details
1920 )
1921 SELECT 44 AS CheckID ,
1922 150 AS Priority ,
1923 'Performance' AS FindingsGroup ,
1924 'Queries Forcing Order Hints' AS Finding ,
1925 'http://BrentOzar.com/go/hints' AS URL ,
1926 CAST(occurrence AS VARCHAR(10))
1927 + ' 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
1928 FROM sys.dm_exec_query_optimizer_info
1929 WHERE counter = 'order hint'
1930 AND occurrence > 1000
1931 END
1932
1933 IF NOT EXISTS ( SELECT 1
1934 FROM #SkipChecks
1935 WHERE DatabaseName IS NULL AND CheckID = 45 )
1936 BEGIN
1937 INSERT INTO #BlitzResults
1938 ( CheckID ,
1939 Priority ,
1940 FindingsGroup ,
1941 Finding ,
1942 URL ,
1943 Details
1944 )
1945 SELECT 45 AS CheckID ,
1946 150 AS Priority ,
1947 'Performance' AS FindingsGroup ,
1948 'Queries Forcing Join Hints' AS Finding ,
1949 'http://BrentOzar.com/go/hints' AS URL ,
1950 CAST(occurrence AS VARCHAR(10))
1951 + ' 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
1952 FROM sys.dm_exec_query_optimizer_info
1953 WHERE counter = 'join hint'
1954 AND occurrence > 1000
1955 END
1956
1957 IF NOT EXISTS ( SELECT 1
1958 FROM #SkipChecks
1959 WHERE DatabaseName IS NULL AND CheckID = 49 )
1960 BEGIN
1961 INSERT INTO #BlitzResults
1962 ( CheckID ,
1963 Priority ,
1964 FindingsGroup ,
1965 Finding ,
1966 URL ,
1967 Details
1968 )
1969 SELECT DISTINCT
1970 49 AS CheckID ,
1971 200 AS Priority ,
1972 'Informational' AS FindingsGroup ,
1973 'Linked Server Configured' AS Finding ,
1974 'http://BrentOzar.com/go/link' AS URL ,
1975 +CASE WHEN l.remote_name = 'sa'
1976 THEN s.data_source
1977 + ' 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.'
1978 ELSE s.data_source
1979 + ' 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.'
1980 END AS Details
1981 FROM sys.servers s
1982 INNER JOIN sys.linked_logins l ON s.server_id = l.server_id
1983 WHERE s.is_linked = 1
1984 END
1985
1986 IF NOT EXISTS ( SELECT 1
1987 FROM #SkipChecks
1988 WHERE DatabaseName IS NULL AND CheckID = 50 )
1989 BEGIN
1990 IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
1991 AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
1992 BEGIN
1993 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
1994 SELECT 50 AS CheckID ,
1995 100 AS Priority ,
1996 ''Performance'' AS FindingsGroup ,
1997 ''Max Memory Set Too High'' AS Finding ,
1998 ''http://BrentOzar.com/go/max'' AS URL ,
1999 ''SQL Server max memory is set to ''
2000 + CAST(c.value_in_use AS VARCHAR(20))
2001 + '' megabytes, but the server only has ''
2002 + CAST(( CAST(m.total_physical_memory_kb AS BIGINT) / 1024 ) AS VARCHAR(20))
2003 + '' megabytes. SQL Server may drain the system dry of memory, and under certain conditions, this can cause Windows to swap to disk.'' AS Details
2004 FROM sys.dm_os_sys_memory m
2005 INNER JOIN sys.configurations c ON c.name = ''max server memory (MB)''
2006 WHERE CAST(m.total_physical_memory_kb AS BIGINT) < ( CAST(c.value_in_use AS BIGINT) * 1024 )'
2007 EXECUTE(@StringToExecute)
2008 END;
2009 END
2010
2011 IF NOT EXISTS ( SELECT 1
2012 FROM #SkipChecks
2013 WHERE DatabaseName IS NULL AND CheckID = 51 )
2014 BEGIN
2015 IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
2016 AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
2017 BEGIN
2018 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
2019 SELECT 51 AS CheckID ,
2020 1 AS Priority ,
2021 ''Performance'' AS FindingsGroup ,
2022 ''Memory Dangerously Low'' AS Finding ,
2023 ''http://BrentOzar.com/go/max'' AS URL ,
2024 ''The server has '' + CAST(( CAST(m.total_physical_memory_kb AS BIGINT) / 1024 ) AS VARCHAR(20)) + '' megabytes of physical memory, but only '' + CAST(( CAST(m.available_physical_memory_kb AS BIGINT) / 1024 ) AS VARCHAR(20))
2025 + '' megabytes are available. As the server runs out of memory, there is danger of swapping to disk, which will kill performance.'' AS Details
2026 FROM sys.dm_os_sys_memory m
2027 WHERE CAST(m.available_physical_memory_kb AS BIGINT) < 262144'
2028 EXECUTE(@StringToExecute)
2029 END;
2030 END
2031
2032 IF NOT EXISTS ( SELECT 1
2033 FROM #SkipChecks
2034 WHERE DatabaseName IS NULL AND CheckID = 159 )
2035 BEGIN
2036 IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
2037 AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
2038 BEGIN
2039 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
2040 SELECT DISTINCT 159 AS CheckID ,
2041 1 AS Priority ,
2042 ''Performance'' AS FindingsGroup ,
2043 ''Memory Dangerously Low in NUMA Nodes'' AS Finding ,
2044 ''http://BrentOzar.com/go/max'' AS URL ,
2045 ''At least one NUMA node is reporting THREAD_RESOURCES_LOW in sys.dm_os_nodes and can no longer create threads.'' AS Details
2046 FROM sys.dm_os_nodes m
2047 WHERE node_state_desc LIKE ''%THREAD_RESOURCES_LOW%'''
2048 EXECUTE(@StringToExecute)
2049 END;
2050 END
2051
2052 IF NOT EXISTS ( SELECT 1
2053 FROM #SkipChecks
2054 WHERE DatabaseName IS NULL AND CheckID = 53 )
2055 BEGIN
2056 INSERT INTO #BlitzResults
2057 ( CheckID ,
2058 Priority ,
2059 FindingsGroup ,
2060 Finding ,
2061 URL ,
2062 Details
2063 )
2064 SELECT TOP 1
2065 53 AS CheckID ,
2066 200 AS Priority ,
2067 'Informational' AS FindingsGroup ,
2068 'Cluster Node' AS Finding ,
2069 'http://BrentOzar.com/go/node' AS URL ,
2070 'This is a node in a cluster.' AS Details
2071 FROM sys.dm_os_cluster_nodes
2072 END
2073
2074 IF NOT EXISTS ( SELECT 1
2075 FROM #SkipChecks
2076 WHERE DatabaseName IS NULL AND CheckID = 55 )
2077 BEGIN
2078 INSERT INTO #BlitzResults
2079 ( CheckID ,
2080 DatabaseName ,
2081 Priority ,
2082 FindingsGroup ,
2083 Finding ,
2084 URL ,
2085 Details
2086 )
2087 SELECT 55 AS CheckID ,
2088 [name] AS DatabaseName ,
2089 230 AS Priority ,
2090 'Security' AS FindingsGroup ,
2091 'Database Owner <> SA' AS Finding ,
2092 'http://BrentOzar.com/go/owndb' AS URL ,
2093 ( 'Database name: ' + [name] + ' '
2094 + 'Owner name: ' + SUSER_SNAME(owner_sid) ) AS Details
2095 FROM sys.databases
2096 WHERE SUSER_SNAME(owner_sid) <> SUSER_SNAME(0x01)
2097 AND name NOT IN ( SELECT DISTINCT
2098 DatabaseName
2099 FROM #SkipChecks
2100 WHERE CheckID IS NULL);
2101 END
2102
2103 IF NOT EXISTS ( SELECT 1
2104 FROM #SkipChecks
2105 WHERE DatabaseName IS NULL AND CheckID = 57 )
2106 BEGIN
2107 INSERT INTO #BlitzResults
2108 ( CheckID ,
2109 Priority ,
2110 FindingsGroup ,
2111 Finding ,
2112 URL ,
2113 Details
2114 )
2115 SELECT 57 AS CheckID ,
2116 230 AS Priority ,
2117 'Security' AS FindingsGroup ,
2118 'SQL Agent Job Runs at Startup' AS Finding ,
2119 'http://BrentOzar.com/go/startup' AS URL ,
2120 ( 'Job [' + j.name
2121 + '] 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
2122 FROM msdb.dbo.sysschedules sched
2123 JOIN msdb.dbo.sysjobschedules jsched ON sched.schedule_id = jsched.schedule_id
2124 JOIN msdb.dbo.sysjobs j ON jsched.job_id = j.job_id
2125 WHERE sched.freq_type = 64
2126 AND sched.enabled = 1;
2127 END
2128
2129
2130
2131 IF NOT EXISTS ( SELECT 1
2132 FROM #SkipChecks
2133 WHERE DatabaseName IS NULL AND CheckID = 97 )
2134 BEGIN
2135 INSERT INTO #BlitzResults
2136 ( CheckID ,
2137 Priority ,
2138 FindingsGroup ,
2139 Finding ,
2140 URL ,
2141 Details
2142 )
2143 SELECT 97 AS CheckID ,
2144 100 AS Priority ,
2145 'Performance' AS FindingsGroup ,
2146 'Unusual SQL Server Edition' AS Finding ,
2147 'http://BrentOzar.com/go/workgroup' AS URL ,
2148 ( 'This server is using '
2149 + CAST(SERVERPROPERTY('edition') AS VARCHAR(100))
2150 + ', which is capped at low amounts of CPU and memory.' ) AS Details
2151 WHERE CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Standard%'
2152 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Enterprise%'
2153 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Data Center%'
2154 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Developer%'
2155 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Business Intelligence%'
2156 END
2157
2158 IF NOT EXISTS ( SELECT 1
2159 FROM #SkipChecks
2160 WHERE DatabaseName IS NULL AND CheckID = 154 )
2161 BEGIN
2162 INSERT INTO #BlitzResults
2163 ( CheckID ,
2164 Priority ,
2165 FindingsGroup ,
2166 Finding ,
2167 URL ,
2168 Details
2169 )
2170 SELECT 154 AS CheckID ,
2171 10 AS Priority ,
2172 'Performance' AS FindingsGroup ,
2173 '32-bit SQL Server Installed' AS Finding ,
2174 'http://BrentOzar.com/go/32bit' AS URL ,
2175 ( 'This server uses the 32-bit x86 binaries for SQL Server instead of the 64-bit x64 binaries. The amount of memory available for query workspace and execution plans is heavily limited.' ) AS Details
2176 WHERE CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%64%'
2177 END
2178
2179 IF NOT EXISTS ( SELECT 1
2180 FROM #SkipChecks
2181 WHERE DatabaseName IS NULL AND CheckID = 62 )
2182 BEGIN
2183 INSERT INTO #BlitzResults
2184 ( CheckID ,
2185 DatabaseName ,
2186 Priority ,
2187 FindingsGroup ,
2188 Finding ,
2189 URL ,
2190 Details
2191 )
2192 SELECT 62 AS CheckID ,
2193 [name] AS DatabaseName ,
2194 200 AS Priority ,
2195 'Performance' AS FindingsGroup ,
2196 'Old Compatibility Level' AS Finding ,
2197 'http://BrentOzar.com/go/compatlevel' AS URL ,
2198 ( 'Database ' + [name]
2199 + ' is compatibility level '
2200 + CAST(compatibility_level AS VARCHAR(20))
2201 + ', which may cause unwanted results when trying to run queries that have newer T-SQL features.' ) AS Details
2202 FROM sys.databases
2203 WHERE name NOT IN ( SELECT DISTINCT
2204 DatabaseName
2205 FROM #SkipChecks
2206 WHERE CheckID IS NULL)
2207 AND compatibility_level <= 90
2208 END
2209
2210 IF NOT EXISTS ( SELECT 1
2211 FROM #SkipChecks
2212 WHERE DatabaseName IS NULL AND CheckID = 94 )
2213 BEGIN
2214 INSERT INTO #BlitzResults
2215 ( CheckID ,
2216 Priority ,
2217 FindingsGroup ,
2218 Finding ,
2219 URL ,
2220 Details
2221 )
2222 SELECT 94 AS CheckID ,
2223 200 AS [Priority] ,
2224 'Monitoring' AS FindingsGroup ,
2225 'Agent Jobs Without Failure Emails' AS Finding ,
2226 'http://BrentOzar.com/go/alerts' AS URL ,
2227 'The job ' + [name]
2228 + ' has not been set up to notify an operator if it fails.' AS Details
2229 FROM msdb.[dbo].[sysjobs] j
2230 INNER JOIN ( SELECT DISTINCT
2231 [job_id]
2232 FROM [msdb].[dbo].[sysjobschedules]
2233 WHERE next_run_date > 0
2234 ) s ON j.job_id = s.job_id
2235 WHERE j.enabled = 1
2236 AND j.notify_email_operator_id = 0
2237 AND j.notify_netsend_operator_id = 0
2238 AND j.notify_page_operator_id = 0
2239 AND j.category_id <> 100 /* Exclude SSRS category */
2240 END
2241
2242
2243 IF EXISTS ( SELECT 1
2244 FROM sys.configurations
2245 WHERE name = 'remote admin connections'
2246 AND value_in_use = 0 )
2247 AND NOT EXISTS ( SELECT 1
2248 FROM #SkipChecks
2249 WHERE DatabaseName IS NULL AND CheckID = 100 )
2250 BEGIN
2251 INSERT INTO #BlitzResults
2252 ( CheckID ,
2253 Priority ,
2254 FindingsGroup ,
2255 Finding ,
2256 URL ,
2257 Details
2258 )
2259 SELECT 100 AS CheckID ,
2260 50 AS Priority ,
2261 'Reliability' AS FindingGroup ,
2262 'Remote DAC Disabled' AS Finding ,
2263 'http://BrentOzar.com/go/dac' AS URL ,
2264 'Remote access to the Dedicated Admin Connection (DAC) is not enabled. The DAC can make remote troubleshooting much easier when SQL Server is unresponsive.'
2265 END
2266
2267
2268 IF EXISTS ( SELECT *
2269 FROM sys.dm_os_schedulers
2270 WHERE is_online = 0 )
2271 AND NOT EXISTS ( SELECT 1
2272 FROM #SkipChecks
2273 WHERE DatabaseName IS NULL AND CheckID = 101 )
2274 BEGIN
2275 INSERT INTO #BlitzResults
2276 ( CheckID ,
2277 Priority ,
2278 FindingsGroup ,
2279 Finding ,
2280 URL ,
2281 Details
2282 )
2283 SELECT 101 AS CheckID ,
2284 50 AS Priority ,
2285 'Performance' AS FindingGroup ,
2286 'CPU Schedulers Offline' AS Finding ,
2287 'http://BrentOzar.com/go/schedulers' AS URL ,
2288 'Some CPU cores are not accessible to SQL Server due to affinity masking or licensing problems.'
2289 END
2290
2291
2292 IF NOT EXISTS ( SELECT 1
2293 FROM #SkipChecks
2294 WHERE DatabaseName IS NULL AND CheckID = 110 )
2295 AND EXISTS (SELECT * FROM master.sys.all_objects WHERE name = 'dm_os_memory_nodes')
2296 BEGIN
2297 SET @StringToExecute = 'IF EXISTS (SELECT *
2298 FROM sys.dm_os_nodes n
2299 INNER JOIN sys.dm_os_memory_nodes m ON n.memory_node_id = m.memory_node_id
2300 WHERE n.node_state_desc = ''OFFLINE'')
2301 INSERT INTO #BlitzResults
2302 ( CheckID ,
2303 Priority ,
2304 FindingsGroup ,
2305 Finding ,
2306 URL ,
2307 Details
2308 )
2309 SELECT 110 AS CheckID ,
2310 50 AS Priority ,
2311 ''Performance'' AS FindingGroup ,
2312 ''Memory Nodes Offline'' AS Finding ,
2313 ''http://BrentOzar.com/go/schedulers'' AS URL ,
2314 ''Due to affinity masking or licensing problems, some of the memory may not be available.''';
2315 EXECUTE(@StringToExecute);
2316 END
2317
2318
2319 IF EXISTS ( SELECT *
2320 FROM sys.databases
2321 WHERE state > 1 )
2322 AND NOT EXISTS ( SELECT 1
2323 FROM #SkipChecks
2324 WHERE DatabaseName IS NULL AND CheckID = 102 )
2325 BEGIN
2326 INSERT INTO #BlitzResults
2327 ( CheckID ,
2328 DatabaseName ,
2329 Priority ,
2330 FindingsGroup ,
2331 Finding ,
2332 URL ,
2333 Details
2334 )
2335 SELECT 102 AS CheckID ,
2336 [name] ,
2337 20 AS Priority ,
2338 'Reliability' AS FindingGroup ,
2339 'Unusual Database State: ' + [state_desc] AS Finding ,
2340 'http://BrentOzar.com/go/repair' AS URL ,
2341 'This database may not be online.'
2342 FROM sys.databases
2343 WHERE state > 1
2344 END
2345
2346 IF EXISTS ( SELECT *
2347 FROM master.sys.extended_procedures )
2348 AND NOT EXISTS ( SELECT 1
2349 FROM #SkipChecks
2350 WHERE DatabaseName IS NULL AND CheckID = 105 )
2351 BEGIN
2352 INSERT INTO #BlitzResults
2353 ( CheckID ,
2354 DatabaseName ,
2355 Priority ,
2356 FindingsGroup ,
2357 Finding ,
2358 URL ,
2359 Details
2360 )
2361 SELECT 105 AS CheckID ,
2362 'master' ,
2363 200 AS Priority ,
2364 'Reliability' AS FindingGroup ,
2365 'Extended Stored Procedures in Master' AS Finding ,
2366 'http://BrentOzar.com/go/clr' AS URL ,
2367 'The [' + name
2368 + '] extended stored procedure is in the master database. CLR may be in use, and the master database now needs to be part of your backup/recovery planning.'
2369 FROM master.sys.extended_procedures
2370 END
2371
2372
2373
2374 IF NOT EXISTS ( SELECT 1
2375 FROM #SkipChecks
2376 WHERE DatabaseName IS NULL AND CheckID = 107 )
2377 BEGIN
2378 INSERT INTO #BlitzResults
2379 ( CheckID ,
2380 Priority ,
2381 FindingsGroup ,
2382 Finding ,
2383 URL ,
2384 Details
2385 )
2386 SELECT 107 AS CheckID ,
2387 50 AS Priority ,
2388 'Performance' AS FindingGroup ,
2389 'Poison Wait Detected: THREADPOOL' AS Finding ,
2390 'http://BrentOzar.com/go/poison' AS URL ,
2391 CAST(SUM([wait_time_ms]) / 10000 / 1000 / 60 / 60 / 24 AS VARCHAR) + CAST(CONVERT(TIME, DATEADD(ms, SUM([wait_time_ms] / 10000 % 1000), DATEADD(ss, SUM([wait_time_ms] / 10000000), 0))) AS VARCHAR) + ' of this wait have been recorded. This wait often indicates killer performance problems.'
2392 FROM sys.[dm_os_wait_stats]
2393 WHERE wait_type = 'THREADPOOL'
2394 GROUP BY wait_type
2395 HAVING SUM([wait_time_ms]) > (SELECT 5000 * datediff(HH,create_date,CURRENT_TIMESTAMP) AS hours_since_startup FROM sys.databases WHERE name='tempdb')
2396 AND SUM([wait_time_ms]) > 60000
2397 END
2398
2399 IF NOT EXISTS ( SELECT 1
2400 FROM #SkipChecks
2401 WHERE DatabaseName IS NULL AND CheckID = 108 )
2402 BEGIN
2403 INSERT INTO #BlitzResults
2404 ( CheckID ,
2405 Priority ,
2406 FindingsGroup ,
2407 Finding ,
2408 URL ,
2409 Details
2410 )
2411 SELECT 108 AS CheckID ,
2412 50 AS Priority ,
2413 'Performance' AS FindingGroup ,
2414 'Poison Wait Detected: RESOURCE_SEMAPHORE' AS Finding ,
2415 'http://BrentOzar.com/go/poison' AS URL ,
2416 CONVERT(VARCHAR(10), (SUM([wait_time_ms]) / 1000) / 86400) + ':' + CONVERT(VARCHAR(20), DATEADD(s, (SUM([wait_time_ms]) / 1000), 0), 108) + ' of this wait have been recorded. This wait often indicates killer performance problems.'
2417 FROM sys.[dm_os_wait_stats]
2418 WHERE wait_type = 'RESOURCE_SEMAPHORE'
2419 GROUP BY wait_type
2420 HAVING SUM([wait_time_ms]) > (SELECT 5000 * datediff(HH,create_date,CURRENT_TIMESTAMP) AS hours_since_startup FROM sys.databases WHERE name='tempdb')
2421 AND SUM([wait_time_ms]) > 60000
2422 END
2423
2424
2425 IF NOT EXISTS ( SELECT 1
2426 FROM #SkipChecks
2427 WHERE DatabaseName IS NULL AND CheckID = 109 )
2428 BEGIN
2429 INSERT INTO #BlitzResults
2430 ( CheckID ,
2431 Priority ,
2432 FindingsGroup ,
2433 Finding ,
2434 URL ,
2435 Details
2436 )
2437 SELECT 109 AS CheckID ,
2438 50 AS Priority ,
2439 'Performance' AS FindingGroup ,
2440 'Poison Wait Detected: RESOURCE_SEMAPHORE_QUERY_COMPILE' AS Finding ,
2441 'http://BrentOzar.com/go/poison' AS URL ,
2442 CAST(SUM([wait_time_ms]) / 10000 / 1000 / 60 / 60 / 24 AS VARCHAR) + CAST(CONVERT(TIME, DATEADD(ms, SUM([wait_time_ms] / 10000 % 1000), DATEADD(ss, SUM([wait_time_ms] / 10000000), 0))) AS VARCHAR) + ' of this wait have been recorded. This wait often indicates killer performance problems.'
2443 FROM sys.[dm_os_wait_stats]
2444 WHERE wait_type = 'RESOURCE_SEMAPHORE_QUERY_COMPILE'
2445 GROUP BY wait_type
2446 HAVING SUM([wait_time_ms]) > (SELECT 5000 * datediff(HH,create_date,CURRENT_TIMESTAMP) AS hours_since_startup FROM sys.databases WHERE name='tempdb')
2447 AND SUM([wait_time_ms]) > 60000
2448 END
2449
2450
2451 IF NOT EXISTS ( SELECT 1
2452 FROM #SkipChecks
2453 WHERE DatabaseName IS NULL AND CheckID = 121 )
2454 BEGIN
2455 INSERT INTO #BlitzResults
2456 ( CheckID ,
2457 Priority ,
2458 FindingsGroup ,
2459 Finding ,
2460 URL ,
2461 Details
2462 )
2463 SELECT 121 AS CheckID ,
2464 50 AS Priority ,
2465 'Performance' AS FindingGroup ,
2466 'Poison Wait Detected: Serializable Locking' AS Finding ,
2467 'http://BrentOzar.com/go/serializable' AS URL ,
2468 CAST(SUM([wait_time_ms]) / 10000 / 1000 / 60 / 60 / 24 AS VARCHAR) + CAST(CONVERT(TIME, DATEADD(ms, SUM([wait_time_ms] / 10000 % 1000), DATEADD(ss, SUM([wait_time_ms] / 10000000), 0))) AS VARCHAR) + ' of LCK_R% waits have been recorded. This wait often indicates killer performance problems.'
2469 FROM sys.[dm_os_wait_stats]
2470 WHERE wait_type LIKE '%LCK%R%'
2471 HAVING SUM([wait_time_ms]) > (SELECT 5000 * datediff(HH,create_date,CURRENT_TIMESTAMP) AS hours_since_startup FROM sys.databases WHERE name='tempdb')
2472 AND SUM([wait_time_ms]) > 60000
2473 END
2474
2475
2476
2477
2478 IF @ProductVersionMajor >= 11 AND NOT EXISTS ( SELECT 1
2479 FROM #SkipChecks
2480 WHERE DatabaseName IS NULL AND CheckID = 162 )
2481 BEGIN
2482 INSERT INTO #BlitzResults
2483 ( CheckID ,
2484 Priority ,
2485 FindingsGroup ,
2486 Finding ,
2487 URL ,
2488 Details
2489 )
2490 SELECT 162 AS CheckID ,
2491 50 AS Priority ,
2492 'Performance' AS FindingGroup ,
2493 'Poison Wait Detected: CMEMTHREAD & NUMA' AS Finding ,
2494 'http://BrentOzar.com/go/poison' AS URL ,
2495 CAST(SUM([wait_time_ms]) / 10000 / 1000 / 60 / 60 / 24 AS VARCHAR) + CAST(CONVERT(TIME, DATEADD(ms, SUM([wait_time_ms] / 10000 % 1000), DATEADD(ss, SUM([wait_time_ms] / 10000000), 0))) AS VARCHAR) + ' of this wait have been recorded. In servers with over 8 cores per NUMA node, when CMEMTHREAD waits are a bottleneck, trace flag 8048 may be needed.'
2496 FROM sys.dm_os_nodes n
2497 INNER JOIN sys.[dm_os_wait_stats] w ON w.wait_type = 'CMEMTHREAD'
2498 WHERE n.node_id = 0 AND n.online_scheduler_count >= 8
2499 GROUP BY w.wait_type
2500 HAVING SUM([wait_time_ms]) > (SELECT 5000 * datediff(HH,create_date,CURRENT_TIMESTAMP) AS hours_since_startup FROM sys.databases WHERE name='tempdb')
2501 AND SUM([wait_time_ms]) > 60000;
2502 END
2503
2504
2505
2506
2507 IF NOT EXISTS ( SELECT 1
2508 FROM #SkipChecks
2509 WHERE DatabaseName IS NULL AND CheckID = 111 )
2510 BEGIN
2511 INSERT INTO #BlitzResults
2512 ( CheckID ,
2513 Priority ,
2514 FindingsGroup ,
2515 Finding ,
2516 DatabaseName ,
2517 URL ,
2518 Details
2519 )
2520 SELECT 111 AS CheckID ,
2521 50 AS Priority ,
2522 'Reliability' AS FindingGroup ,
2523 'Possibly Broken Log Shipping' AS Finding ,
2524 d.[name] ,
2525 'http://BrentOzar.com/go/shipping' AS URL ,
2526 d.[name] + ' is in a restoring state, but has not had a backup applied in the last two days. This is a possible indication of a broken transaction log shipping setup.'
2527 FROM [master].sys.databases d
2528 INNER JOIN [master].sys.database_mirroring dm ON d.database_id = dm.database_id
2529 AND dm.mirroring_role IS NULL
2530 WHERE ( d.[state] = 1
2531 OR (d.[state] = 0 AND d.[is_in_standby] = 1) )
2532 AND NOT EXISTS(SELECT * FROM msdb.dbo.restorehistory rh
2533 INNER JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
2534 WHERE d.[name] COLLATE SQL_Latin1_General_CP1_CI_AS = rh.destination_database_name COLLATE SQL_Latin1_General_CP1_CI_AS
2535 AND rh.restore_date >= DATEADD(dd, -2, GETDATE()))
2536
2537 END
2538
2539
2540 IF NOT EXISTS ( SELECT 1
2541 FROM #SkipChecks
2542 WHERE DatabaseName IS NULL AND CheckID = 112 )
2543 AND EXISTS (SELECT * FROM master.sys.all_objects WHERE name = 'change_tracking_databases')
2544 BEGIN
2545 SET @StringToExecute = 'INSERT INTO #BlitzResults
2546 (CheckID,
2547 Priority,
2548 FindingsGroup,
2549 Finding,
2550 URL,
2551 Details)
2552 SELECT 112 AS CheckID,
2553 100 AS Priority,
2554 ''Performance'' AS FindingsGroup,
2555 ''Change Tracking Enabled'' AS Finding,
2556 ''http://BrentOzar.com/go/tracking'' AS URL,
2557 ( d.[name] + '' has change tracking enabled. This is not a default setting, and it has some performance overhead. It keeps track of changes to rows in tables that have change tracking turned on.'' ) AS Details FROM sys.change_tracking_databases AS ctd INNER JOIN sys.databases AS d ON ctd.database_id = d.database_id';
2558 EXECUTE(@StringToExecute);
2559 END
2560
2561
2562 IF NOT EXISTS ( SELECT 1
2563 FROM #SkipChecks
2564 WHERE DatabaseName IS NULL AND CheckID = 116 )
2565 AND EXISTS (SELECT * FROM msdb.sys.all_columns WHERE name = 'compressed_backup_size')
2566 BEGIN
2567 SET @StringToExecute = 'INSERT INTO #BlitzResults
2568 ( CheckID ,
2569 Priority ,
2570 FindingsGroup ,
2571 Finding ,
2572 URL ,
2573 Details
2574 )
2575 SELECT 116 AS CheckID ,
2576 200 AS Priority ,
2577 ''Informational'' AS FindingGroup ,
2578 ''Backup Compression Default Off'' AS Finding ,
2579 ''http://BrentOzar.com/go/backup'' AS URL ,
2580 ''Uncompressed full backups have happened recently, and backup compression is not turned on at the server level. Backup compression is included with SQL Server 2008R2 & newer, even in Standard Edition. We recommend turning backup compression on by default so that ad-hoc backups will get compressed.''
2581 FROM sys.configurations
2582 WHERE configuration_id = 1579 AND CAST(value_in_use AS INT) = 0
2583 AND EXISTS (SELECT * FROM msdb.dbo.backupset WHERE backup_size = compressed_backup_size AND type = ''D'' AND backup_finish_date >= DATEADD(DD, -14, GETDATE()));'
2584 EXECUTE(@StringToExecute);
2585 END
2586
2587 IF NOT EXISTS ( SELECT 1
2588 FROM #SkipChecks
2589 WHERE DatabaseName IS NULL AND CheckID = 117 )
2590 AND EXISTS (SELECT * FROM master.sys.all_objects WHERE name = 'dm_exec_query_resource_semaphores')
2591 BEGIN
2592 SET @StringToExecute = 'IF 0 < (SELECT SUM([forced_grant_count]) FROM sys.dm_exec_query_resource_semaphores WHERE [forced_grant_count] IS NOT NULL)
2593 INSERT INTO #BlitzResults
2594 (CheckID,
2595 Priority,
2596 FindingsGroup,
2597 Finding,
2598 URL,
2599 Details)
2600 SELECT 117 AS CheckID,
2601 100 AS Priority,
2602 ''Performance'' AS FindingsGroup,
2603 ''Memory Pressure Affecting Queries'' AS Finding,
2604 ''http://BrentOzar.com/go/grants'' AS URL,
2605 CAST(SUM(forced_grant_count) AS NVARCHAR(100)) + '' forced grants reported in the DMV sys.dm_exec_query_resource_semaphores, indicating memory pressure has affected query runtimes.''
2606 FROM sys.dm_exec_query_resource_semaphores WHERE [forced_grant_count] IS NOT NULL;'
2607 EXECUTE(@StringToExecute);
2608 END
2609
2610
2611
2612 IF NOT EXISTS ( SELECT 1
2613 FROM #SkipChecks
2614 WHERE DatabaseName IS NULL AND CheckID = 124 )
2615 BEGIN
2616 INSERT INTO #BlitzResults
2617 (CheckID,
2618 Priority,
2619 FindingsGroup,
2620 Finding,
2621 URL,
2622 Details)
2623 SELECT 124, 150, 'Performance', 'Deadlocks Happening Daily', 'http://BrentOzar.com/go/deadlocks',
2624 CAST(p.cntr_value AS NVARCHAR(100)) + ' deadlocks have been recorded since startup.' AS Details
2625 FROM sys.dm_os_performance_counters p
2626 INNER JOIN sys.databases d ON d.name = 'tempdb'
2627 WHERE RTRIM(p.counter_name) = 'Number of Deadlocks/sec'
2628 AND RTRIM(p.instance_name) = '_Total'
2629 AND p.cntr_value > 0
2630 AND (1.0 * p.cntr_value / NULLIF(datediff(DD,create_date,CURRENT_TIMESTAMP),0)) > 10;
2631 END
2632
2633
2634 IF DATEADD(mi, -15, GETDATE()) < (SELECT TOP 1 creation_time FROM sys.dm_exec_query_stats ORDER BY creation_time)
2635 BEGIN
2636 INSERT INTO #BlitzResults
2637 (CheckID,
2638 Priority,
2639 FindingsGroup,
2640 Finding,
2641 URL,
2642 Details)
2643 SELECT TOP 1 125, 10, 'Performance', 'Plan Cache Erased Recently', 'http://BrentOzar.com/askbrent/plan-cache-erased-recently/',
2644 'The oldest query in the plan cache was created at ' + CAST(creation_time AS NVARCHAR(50)) + '. Someone ran DBCC FREEPROCCACHE, restarted SQL Server, or it is under horrific memory pressure.'
2645 FROM sys.dm_exec_query_stats WITH (NOLOCK)
2646 ORDER BY creation_time
2647 END;
2648
2649 IF EXISTS (SELECT * FROM sys.configurations WHERE name = 'priority boost' AND (value = 1 OR value_in_use = 1))
2650 BEGIN
2651 INSERT INTO #BlitzResults
2652 (CheckID,
2653 Priority,
2654 FindingsGroup,
2655 Finding,
2656 URL,
2657 Details)
2658 VALUES(126, 5, 'Reliability', 'Priority Boost Enabled', 'http://BrentOzar.com/go/priorityboost/',
2659 'Priority Boost sounds awesome, but it can actually cause your SQL Server to crash.')
2660 END;
2661
2662 IF NOT EXISTS ( SELECT 1
2663 FROM #SkipChecks
2664 WHERE DatabaseName IS NULL AND CheckID = 128 )
2665 BEGIN
2666
2667 IF (@ProductVersionMajor = 12 AND @ProductVersionMinor < 2000) OR
2668 (@ProductVersionMajor = 11 AND @ProductVersionMinor < 3000) OR
2669 (@ProductVersionMajor = 10.5 AND @ProductVersionMinor < 6000) OR
2670 (@ProductVersionMajor = 10 AND @ProductVersionMinor < 6000) OR
2671 (@ProductVersionMajor = 9 /*AND @ProductVersionMinor <= 5000*/)
2672 BEGIN
2673 INSERT INTO #BlitzResults(CheckID, Priority, FindingsGroup, Finding, URL, Details)
2674 VALUES(128, 20, 'Reliability', 'Unsupported Build of SQL Server', 'http://BrentOzar.com/go/unsupported',
2675 'Version ' + CAST(@ProductVersionMajor AS VARCHAR(100)) + '.' +
2676 CASE WHEN @ProductVersionMajor > 9 THEN
2677 CAST(@ProductVersionMinor AS VARCHAR(100)) + ' is no longer supported by Microsoft. You need to apply a service pack.'
2678 ELSE ' is no longer support by Microsoft. You should be making plans to upgrade to a modern version of SQL Server.' END);
2679 END;
2680
2681 END;
2682
2683 /* Reliability - Dangerous Build of SQL Server (Corruption) */
2684 IF NOT EXISTS ( SELECT 1
2685 FROM #SkipChecks
2686 WHERE DatabaseName IS NULL AND CheckID = 129 )
2687 BEGIN
2688 IF (@ProductVersionMajor = 11 AND @ProductVersionMinor >= 3000 AND @ProductVersionMinor <= 3436) OR
2689 (@ProductVersionMajor = 11 AND @ProductVersionMinor = 5058) OR
2690 (@ProductVersionMajor = 12 AND @ProductVersionMinor >= 2000 AND @ProductVersionMinor <= 2342)
2691 BEGIN
2692 INSERT INTO #BlitzResults(CheckID, Priority, FindingsGroup, Finding, URL, Details)
2693 VALUES(129, 20, 'Reliability', 'Dangerous Build of SQL Server (Corruption)', 'http://sqlperformance.com/2014/06/sql-indexes/hotfix-sql-2012-rebuilds',
2694 'There are dangerous known bugs with version ' + CAST(@ProductVersionMajor AS VARCHAR(100)) + '.' + CAST(@ProductVersionMinor AS VARCHAR(100)) + '. Check the URL for details and apply the right service pack or hotfix.');
2695 END;
2696
2697 END;
2698
2699 /* Reliability - Dangerous Build of SQL Server (Security) */
2700 IF NOT EXISTS ( SELECT 1
2701 FROM #SkipChecks
2702 WHERE DatabaseName IS NULL AND CheckID = 157 )
2703 BEGIN
2704 IF (@ProductVersionMajor = 10 AND @ProductVersionMinor >= 5500 AND @ProductVersionMinor <= 5512) OR
2705 (@ProductVersionMajor = 10 AND @ProductVersionMinor >= 5750 AND @ProductVersionMinor <= 5867) OR
2706 (@ProductVersionMajor = 10.5 AND @ProductVersionMinor >= 4000 AND @ProductVersionMinor <= 4017) OR
2707 (@ProductVersionMajor = 10.5 AND @ProductVersionMinor >= 4251 AND @ProductVersionMinor <= 4319) OR
2708 (@ProductVersionMajor = 11 AND @ProductVersionMinor >= 3000 AND @ProductVersionMinor <= 3129) OR
2709 (@ProductVersionMajor = 11 AND @ProductVersionMinor >= 3300 AND @ProductVersionMinor <= 3447) OR
2710 (@ProductVersionMajor = 12 AND @ProductVersionMinor >= 2000 AND @ProductVersionMinor <= 2253) OR
2711 (@ProductVersionMajor = 12 AND @ProductVersionMinor >= 2300 AND @ProductVersionMinor <= 2370)
2712 BEGIN
2713 INSERT INTO #BlitzResults(CheckID, Priority, FindingsGroup, Finding, URL, Details)
2714 VALUES(157, 20, 'Reliability', 'Dangerous Build of SQL Server (Security)', 'https://technet.microsoft.com/en-us/library/security/MS14-044',
2715 'There are dangerous known bugs with version ' + CAST(@ProductVersionMajor AS VARCHAR(100)) + '.' + CAST(@ProductVersionMinor AS VARCHAR(100)) + '. Check the URL for details and apply the right service pack or hotfix.');
2716 END;
2717
2718 END;
2719
2720
2721 /* Performance - High Memory Use for In-Memory OLTP (Hekaton) */
2722 IF NOT EXISTS ( SELECT 1
2723 FROM #SkipChecks
2724 WHERE DatabaseName IS NULL AND CheckID = 145 )
2725 AND EXISTS ( SELECT *
2726 FROM sys.all_objects o
2727 WHERE o.name = 'dm_db_xtp_table_memory_stats' )
2728 BEGIN
2729 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
2730 SELECT 145 AS CheckID,
2731 10 AS Priority,
2732 ''Performance'' AS FindingsGroup,
2733 ''High Memory Use for In-Memory OLTP (Hekaton)'' AS Finding,
2734 ''http://BrentOzar.com/go/hekaton'' AS URL,
2735 CAST(CAST((SUM(mem.pages_kb / 1024.0) / CAST(value_in_use AS INT) * 100) AS INT) AS NVARCHAR(100)) + ''% of your '' + CAST(CAST((CAST(value_in_use AS DECIMAL(38,1)) / 1024) AS MONEY) AS NVARCHAR(100)) + ''GB of your max server memory is being used for in-memory OLTP tables (Hekaton). Microsoft recommends having 2X your Hekaton table space available in memory just for Hekaton, with a max of 250GB of in-memory data regardless of your server memory capacity.'' AS Details
2736 FROM sys.configurations c INNER JOIN sys.dm_os_memory_clerks mem ON mem.type = ''MEMORYCLERK_XTP''
2737 WHERE c.name = ''max server memory (MB)''
2738 GROUP BY c.value_in_use
2739 HAVING CAST(value_in_use AS DECIMAL(38,2)) * .25 < SUM(mem.pages_kb / 1024.0)
2740 OR SUM(mem.pages_kb / 1024.0) > 250000';
2741 EXECUTE(@StringToExecute);
2742 END
2743
2744
2745 /* Performance - In-Memory OLTP (Hekaton) In Use */
2746 IF NOT EXISTS ( SELECT 1
2747 FROM #SkipChecks
2748 WHERE DatabaseName IS NULL AND CheckID = 146 )
2749 AND EXISTS ( SELECT *
2750 FROM sys.all_objects o
2751 WHERE o.name = 'dm_db_xtp_table_memory_stats' )
2752 BEGIN
2753 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
2754 SELECT 146 AS CheckID,
2755 200 AS Priority,
2756 ''Performance'' AS FindingsGroup,
2757 ''In-Memory OLTP (Hekaton) In Use'' AS Finding,
2758 ''http://BrentOzar.com/go/hekaton'' AS URL,
2759 CAST(CAST((SUM(mem.pages_kb / 1024.0) / CAST(value_in_use AS INT) * 100) AS INT) AS NVARCHAR(100)) + ''% of your '' + CAST(CAST((CAST(value_in_use AS DECIMAL(38,1)) / 1024) AS MONEY) AS NVARCHAR(100)) + ''GB of your max server memory is being used for in-memory OLTP tables (Hekaton).'' AS Details
2760 FROM sys.configurations c INNER JOIN sys.dm_os_memory_clerks mem ON mem.type = ''MEMORYCLERK_XTP''
2761 WHERE c.name = ''max server memory (MB)''
2762 GROUP BY c.value_in_use
2763 HAVING SUM(mem.pages_kb / 1024.0) > 10';
2764 EXECUTE(@StringToExecute);
2765 END
2766
2767 /* In-Memory OLTP (Hekaton) - Transaction Errors */
2768 IF NOT EXISTS ( SELECT 1
2769 FROM #SkipChecks
2770 WHERE DatabaseName IS NULL AND CheckID = 147 )
2771 AND EXISTS ( SELECT *
2772 FROM sys.all_objects o
2773 WHERE o.name = 'dm_xtp_transaction_stats' )
2774 BEGIN
2775 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
2776 SELECT 147 AS CheckID,
2777 100 AS Priority,
2778 ''In-Memory OLTP (Hekaton)'' AS FindingsGroup,
2779 ''Transaction Errors'' AS Finding,
2780 ''http://BrentOzar.com/go/hekaton'' AS URL,
2781 ''Since restart: '' + CAST(validation_failures AS NVARCHAR(100)) + '' validation failures, '' + CAST(dependencies_failed AS NVARCHAR(100)) + '' dependency failures, '' + CAST(write_conflicts AS NVARCHAR(100)) + '' write conflicts, '' + CAST(unique_constraint_violations AS NVARCHAR(100)) + '' unique constraint violations.'' AS Details
2782 FROM sys.dm_xtp_transaction_stats
2783 WHERE validation_failures <> 0
2784 OR dependencies_failed <> 0
2785 OR write_conflicts <> 0
2786 OR unique_constraint_violations <> 0;'
2787 EXECUTE(@StringToExecute);
2788 END
2789
2790
2791
2792 /* Reliability - Database Files on Network File Shares */
2793 IF NOT EXISTS ( SELECT 1
2794 FROM #SkipChecks
2795 WHERE DatabaseName IS NULL AND CheckID = 148 )
2796 BEGIN
2797 INSERT INTO #BlitzResults
2798 ( CheckID ,
2799 DatabaseName ,
2800 Priority ,
2801 FindingsGroup ,
2802 Finding ,
2803 URL ,
2804 Details
2805 )
2806 SELECT DISTINCT 148 AS CheckID ,
2807 d.[name] AS DatabaseName ,
2808 170 AS Priority ,
2809 'Reliability' AS FindingsGroup ,
2810 'Database Files on Network File Shares' AS Finding ,
2811 'http://BrentOzar.com/go/nas' AS URL ,
2812 ( 'Files for this database are on: ' + LEFT(mf.physical_name, 30)) AS Details
2813 FROM sys.databases d
2814 INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
2815 WHERE mf.physical_name LIKE '\\%'
2816 AND d.name NOT IN ( SELECT DISTINCT
2817 DatabaseName
2818 FROM #SkipChecks
2819 WHERE CheckID IS NULL)
2820 END
2821
2822 /* Reliability - Database Files Stored in Azure */
2823 IF NOT EXISTS ( SELECT 1
2824 FROM #SkipChecks
2825 WHERE DatabaseName IS NULL AND CheckID = 149 )
2826 BEGIN
2827 INSERT INTO #BlitzResults
2828 ( CheckID ,
2829 DatabaseName ,
2830 Priority ,
2831 FindingsGroup ,
2832 Finding ,
2833 URL ,
2834 Details
2835 )
2836 SELECT DISTINCT 149 AS CheckID ,
2837 d.[name] AS DatabaseName ,
2838 170 AS Priority ,
2839 'Reliability' AS FindingsGroup ,
2840 'Database Files Stored in Azure' AS Finding ,
2841 'http://BrentOzar.com/go/azurefiles' AS URL ,
2842 ( 'Files for this database are on: ' + LEFT(mf.physical_name, 30)) AS Details
2843 FROM sys.databases d
2844 INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
2845 WHERE mf.physical_name LIKE 'http://%'
2846 AND d.name NOT IN ( SELECT DISTINCT
2847 DatabaseName
2848 FROM #SkipChecks
2849 WHERE CheckID IS NULL)
2850 END
2851
2852
2853 /* Reliability - Errors Logged Recently in the Default Trace */
2854 IF NOT EXISTS ( SELECT 1
2855 FROM #SkipChecks
2856 WHERE DatabaseName IS NULL AND CheckID = 150 )
2857 AND @TracePath IS NOT NULL
2858 BEGIN
2859
2860 INSERT INTO #BlitzResults
2861 ( CheckID ,
2862 DatabaseName ,
2863 Priority ,
2864 FindingsGroup ,
2865 Finding ,
2866 URL ,
2867 Details
2868 )
2869 SELECT DISTINCT 150 AS CheckID ,
2870 t.DatabaseName,
2871 50 AS Priority ,
2872 'Reliability' AS FindingsGroup ,
2873 'Errors Logged Recently in the Default Trace' AS Finding ,
2874 'http://BrentOzar.com/go/defaulttrace' AS URL ,
2875 CAST(t.TextData AS NVARCHAR(4000)) AS Details
2876 FROM sys.fn_trace_gettable(@TracePath, DEFAULT) t
2877 WHERE t.EventClass = 22
2878 AND t.Severity >= 17
2879 AND t.StartTime > DATEADD(dd, -30, GETDATE())
2880 END
2881
2882
2883 /* Performance - Log File Growths Slow */
2884 IF NOT EXISTS ( SELECT 1
2885 FROM #SkipChecks
2886 WHERE DatabaseName IS NULL AND CheckID = 151 )
2887 AND @TracePath IS NOT NULL
2888 BEGIN
2889 INSERT INTO #BlitzResults
2890 ( CheckID ,
2891 DatabaseName ,
2892 Priority ,
2893 FindingsGroup ,
2894 Finding ,
2895 URL ,
2896 Details
2897 )
2898 SELECT DISTINCT 151 AS CheckID ,
2899 t.DatabaseName,
2900 50 AS Priority ,
2901 'Performance' AS FindingsGroup ,
2902 'Log File Growths Slow' AS Finding ,
2903 'http://BrentOzar.com/go/filegrowth' AS URL ,
2904 CAST(COUNT(*) AS NVARCHAR(100)) + ' growths took more than 15 seconds each. Consider setting log file autogrowth to a smaller increment.' AS Details
2905 FROM sys.fn_trace_gettable(@TracePath, DEFAULT) t
2906 WHERE t.EventClass = 93
2907 AND t.StartTime > DATEADD(dd, -30, GETDATE())
2908 AND t.Duration > 15000000
2909 GROUP BY t.DatabaseName
2910 HAVING COUNT(*) > 1
2911 END
2912
2913
2914 /* Performance - Many Plans for One Query */
2915 IF NOT EXISTS ( SELECT 1
2916 FROM #SkipChecks
2917 WHERE DatabaseName IS NULL AND CheckID = 160 )
2918 AND EXISTS (SELECT * FROM sys.all_columns WHERE name = 'query_hash')
2919 BEGIN
2920 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
2921 SELECT TOP 1 160 AS CheckID,
2922 100 AS Priority,
2923 ''Performance'' AS FindingsGroup,
2924 ''Many Plans for One Query'' AS Finding,
2925 ''http://BrentOzar.com/go/parameterization'' AS URL,
2926 CAST(COUNT(DISTINCT plan_handle) AS NVARCHAR(50)) + '' plans are present for a single query in the plan cache - meaning we probably have parameterization issues.'' AS Details
2927 FROM sys.dm_exec_query_stats qs
2928 CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
2929 WHERE pa.attribute = ''dbid''
2930 GROUP BY qs.query_hash, pa.value
2931 HAVING COUNT(DISTINCT plan_handle) > 50
2932 ORDER BY COUNT(DISTINCT plan_handle) DESC;';
2933 EXECUTE(@StringToExecute);
2934 END
2935
2936
2937 /* Performance - High Number of Cached Plans */
2938 IF NOT EXISTS ( SELECT 1
2939 FROM #SkipChecks
2940 WHERE DatabaseName IS NULL AND CheckID = 161 )
2941 BEGIN
2942 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
2943 SELECT TOP 1 161 AS CheckID,
2944 100 AS Priority,
2945 ''Performance'' AS FindingsGroup,
2946 ''High Number of Cached Plans'' AS Finding,
2947 ''http://BrentOzar.com/go/planlimits'' AS URL,
2948 ''Your server configuration is limited to '' + CAST(ht.buckets_count * 4 AS VARCHAR(20)) + '' '' + ht.name + '', and you are currently caching '' + CAST(cc.entries_count AS VARCHAR(20)) + ''.'' AS Details
2949 FROM sys.dm_os_memory_cache_hash_tables ht
2950 INNER JOIN sys.dm_os_memory_cache_counters cc ON ht.name = cc.name AND ht.type = cc.type
2951 where ht.name IN ( ''SQL Plans'' , ''Object Plans'' , ''Bound Trees'' )
2952 AND cc.entries_count >= (3 * ht.buckets_count)';
2953 EXECUTE(@StringToExecute);
2954 END
2955
2956
2957 /* Performance - Too Much Free Memory */
2958 IF NOT EXISTS ( SELECT 1
2959 FROM #SkipChecks
2960 WHERE DatabaseName IS NULL AND CheckID = 165 )
2961 BEGIN
2962 INSERT INTO #BlitzResults
2963 (CheckID,
2964 Priority,
2965 FindingsGroup,
2966 Finding,
2967 URL,
2968 Details)
2969 SELECT 165, 50, 'Performance', 'Too Much Free Memory', 'http://BrentOzar.com/go/freememory',
2970 CAST((CAST(cFree.cntr_value AS BIGINT) / 1024 / 1024 ) AS NVARCHAR(100)) + N'GB of free memory inside SQL Server''s buffer pool, which is ' + CAST((CAST(cTotal.cntr_value AS BIGINT) / 1024 / 1024) AS NVARCHAR(100)) + N'GB. You would think lots of free memory would be good, but check out the URL for more information.' AS Details
2971 FROM sys.dm_os_performance_counters cFree
2972 INNER JOIN sys.dm_os_performance_counters cTotal ON cTotal.object_name LIKE N'%Memory Manager%'
2973 AND cTotal.counter_name = N'Total Server Memory (KB) '
2974 WHERE cFree.object_name LIKE N'%Memory Manager%'
2975 AND cFree.counter_name = N'Free Memory (KB) '
2976 AND CAST(cTotal.cntr_value AS BIGINT) * .3 <= CAST(cFree.cntr_value AS BIGINT)
2977 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Standard%'
2978
2979 END
2980
2981
2982 /* Outdated sp_Blitz - sp_Blitz is Over 6 Months Old */
2983 IF NOT EXISTS ( SELECT 1
2984 FROM #SkipChecks
2985 WHERE DatabaseName IS NULL AND CheckID = 155 )
2986 AND DATEDIFF(MM, @VersionDate, GETDATE()) > 6
2987 BEGIN
2988 INSERT INTO #BlitzResults
2989 ( CheckID ,
2990 Priority ,
2991 FindingsGroup ,
2992 Finding ,
2993 URL ,
2994 Details
2995 )
2996 SELECT 155 AS CheckID ,
2997 0 AS Priority ,
2998 'Outdated sp_Blitz' AS FindingsGroup ,
2999 'sp_Blitz is Over 6 Months Old' AS Finding ,
3000 'http://www.BrentOzar.com/blitz/' AS URL ,
3001 'Some things get better with age, like fine wine and your T-SQL. However, sp_Blitz is not one of those things - time to go download the current one.' AS Details
3002 END
3003
3004
3005 /* Populate a list of database defaults. I'm doing this kind of oddly -
3006 it reads like a lot of work, but this way it compiles & runs on all
3007 versions of SQL Server.
3008 */
3009 INSERT INTO #DatabaseDefaults
3010 SELECT 'is_supplemental_logging_enabled', 0, 131, 210, 'Supplemental Logging Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3011 FROM sys.all_columns
3012 WHERE name = 'is_supplemental_logging_enabled' AND object_id = OBJECT_ID('sys.databases');
3013 INSERT INTO #DatabaseDefaults
3014 SELECT 'snapshot_isolation_state', 0, 132, 210, 'Snapshot Isolation Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3015 FROM sys.all_columns
3016 WHERE name = 'snapshot_isolation_state' AND object_id = OBJECT_ID('sys.databases');
3017 INSERT INTO #DatabaseDefaults
3018 SELECT 'is_read_committed_snapshot_on', 0, 133, 210, 'Read Committed Snapshot Isolation Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3019 FROM sys.all_columns
3020 WHERE name = 'is_read_committed_snapshot_on' AND object_id = OBJECT_ID('sys.databases');
3021 INSERT INTO #DatabaseDefaults
3022 SELECT 'is_auto_create_stats_incremental_on', 0, 134, 210, 'Auto Create Stats Incremental Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3023 FROM sys.all_columns
3024 WHERE name = 'is_auto_create_stats_incremental_on' AND object_id = OBJECT_ID('sys.databases');
3025 INSERT INTO #DatabaseDefaults
3026 SELECT 'is_ansi_null_default_on', 0, 135, 210, 'ANSI NULL Default Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3027 FROM sys.all_columns
3028 WHERE name = 'is_ansi_null_default_on' AND object_id = OBJECT_ID('sys.databases');
3029 INSERT INTO #DatabaseDefaults
3030 SELECT 'is_recursive_triggers_on', 0, 136, 210, 'Recursive Triggers Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3031 FROM sys.all_columns
3032 WHERE name = 'is_recursive_triggers_on' AND object_id = OBJECT_ID('sys.databases');
3033 INSERT INTO #DatabaseDefaults
3034 SELECT 'is_trustworthy_on', 0, 137, 210, 'Trustworthy Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3035 FROM sys.all_columns
3036 WHERE name = 'is_trustworthy_on' AND object_id = OBJECT_ID('sys.databases');
3037 INSERT INTO #DatabaseDefaults
3038 SELECT 'is_parameterization_forced', 0, 138, 210, 'Forced Parameterization Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3039 FROM sys.all_columns
3040 WHERE name = 'is_parameterization_forced' AND object_id = OBJECT_ID('sys.databases');
3041 /* Not alerting for this since we actually want it and we have a separate check for it:
3042 INSERT INTO #DatabaseDefaults
3043 SELECT 'is_query_store_on', 0, 139, 210, 'Query Store Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3044 FROM sys.all_columns
3045 WHERE name = 'is_query_store_on' AND object_id = OBJECT_ID('sys.databases');
3046 */
3047 INSERT INTO #DatabaseDefaults
3048 SELECT 'is_cdc_enabled', 0, 140, 210, 'Change Data Capture Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3049 FROM sys.all_columns
3050 WHERE name = 'is_cdc_enabled' AND object_id = OBJECT_ID('sys.databases');
3051 INSERT INTO #DatabaseDefaults
3052 SELECT 'containment', 0, 141, 210, 'Containment Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3053 FROM sys.all_columns
3054 WHERE name = 'containment' AND object_id = OBJECT_ID('sys.databases');
3055 INSERT INTO #DatabaseDefaults
3056 SELECT 'target_recovery_time_in_seconds', 0, 142, 210, 'Target Recovery Time Changed', 'http://BrentOzar.com/go/dbdefaults', NULL
3057 FROM sys.all_columns
3058 WHERE name = 'target_recovery_time_in_seconds' AND object_id = OBJECT_ID('sys.databases');
3059 INSERT INTO #DatabaseDefaults
3060 SELECT 'delayed_durability', 0, 143, 210, 'Delayed Durability Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3061 FROM sys.all_columns
3062 WHERE name = 'delayed_durability' AND object_id = OBJECT_ID('sys.databases');
3063 INSERT INTO #DatabaseDefaults
3064 SELECT 'is_memory_optimized_elevate_to_snapshot_on', 0, 144, 210, 'Memory Optimized Enabled', 'http://BrentOzar.com/go/dbdefaults', NULL
3065 FROM sys.all_columns
3066 WHERE name = 'is_memory_optimized_elevate_to_snapshot_on' AND object_id = OBJECT_ID('sys.databases');
3067
3068 DECLARE DatabaseDefaultsLoop CURSOR FOR
3069 SELECT name, DefaultValue, CheckID, Priority, Finding, URL, Details
3070 FROM #DatabaseDefaults
3071
3072 OPEN DatabaseDefaultsLoop
3073 FETCH NEXT FROM DatabaseDefaultsLoop into @CurrentName, @CurrentDefaultValue, @CurrentCheckID, @CurrentPriority, @CurrentFinding, @CurrentURL, @CurrentDetails
3074 WHILE @@FETCH_STATUS = 0
3075 BEGIN
3076
3077 /* DW* databases ship with Target Recovery Time (142) set to a non-default number */
3078 IF @CurrentCheckID = 142
3079 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details)
3080 SELECT ' + CAST(@CurrentCheckID AS NVARCHAR(200)) + ', d.[name], ' + CAST(@CurrentPriority AS NVARCHAR(200)) + ', ''Non-Default Database Config'', ''' + @CurrentFinding + ''',''' + @CurrentURL + ''',''' + COALESCE(@CurrentDetails, 'This database setting is not the default.') + '''
3081 FROM sys.databases d
3082 WHERE d.database_id > 4 AND d.[name] NOT IN (''DWConfiguration'', ''DWDiagnostics'', ''DWQueue'') AND (d.[' + @CurrentName + '] <> ' + @CurrentDefaultValue + ' OR d.[' + @CurrentName + '] IS NULL);';
3083 ELSE
3084 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details)
3085 SELECT ' + CAST(@CurrentCheckID AS NVARCHAR(200)) + ', d.[name], ' + CAST(@CurrentPriority AS NVARCHAR(200)) + ', ''Non-Default Database Config'', ''' + @CurrentFinding + ''',''' + @CurrentURL + ''',''' + COALESCE(@CurrentDetails, 'This database setting is not the default.') + '''
3086 FROM sys.databases d
3087 WHERE d.database_id > 4 AND (d.[' + @CurrentName + '] <> ' + @CurrentDefaultValue + ' OR d.[' + @CurrentName + '] IS NULL);';
3088 EXEC (@StringToExecute);
3089
3090 FETCH NEXT FROM DatabaseDefaultsLoop into @CurrentName, @CurrentDefaultValue, @CurrentCheckID, @CurrentPriority, @CurrentFinding, @CurrentURL, @CurrentDetails
3091 END
3092
3093 CLOSE DatabaseDefaultsLoop
3094 DEALLOCATE DatabaseDefaultsLoop;
3095
3096
3097/*This checks to see if Agent is Offline*/
3098IF @ProductVersionMajor >= 10 AND @ProductVersionMinor >= 50
3099 AND NOT EXISTS ( SELECT 1
3100 FROM #SkipChecks
3101 WHERE DatabaseName IS NULL AND CheckID = 167 )
3102 BEGIN
3103 IF EXISTS ( SELECT 1
3104 FROM sys.all_objects
3105 WHERE name = 'dm_server_services' )
3106 BEGIN
3107 INSERT INTO [#BlitzResults]
3108 ( [CheckID] ,
3109 [Priority] ,
3110 [FindingsGroup] ,
3111 [Finding] ,
3112 [URL] ,
3113 [Details] )
3114
3115 SELECT
3116 167 AS [CheckID] ,
3117 250 AS [Priority] ,
3118 'Server Info' AS [FindingsGroup] ,
3119 'Agent is Currently Offline' AS [Finding] ,
3120 '' AS [URL] ,
3121 ( 'Oops! It looks like the ' + [servicename] + ' service is ' + [status_desc] + '. The startup type is ' + [startup_type_desc] + '.'
3122 ) AS [Details]
3123 FROM
3124 [sys].[dm_server_services]
3125 WHERE [status_desc] <> 'Running'
3126 AND [servicename] LIKE 'SQL Server Agent%'
3127
3128 END;
3129 END;
3130
3131/*This checks to see if the Full Text thingy is offline*/
3132IF @ProductVersionMajor >= 10 AND @ProductVersionMinor >= 50
3133 AND NOT EXISTS ( SELECT 1
3134 FROM #SkipChecks
3135 WHERE DatabaseName IS NULL AND CheckID = 168 )
3136 BEGIN
3137 IF EXISTS ( SELECT 1
3138 FROM sys.all_objects
3139 WHERE name = 'dm_server_services' )
3140 BEGIN
3141 INSERT INTO [#BlitzResults]
3142 ( [CheckID] ,
3143 [Priority] ,
3144 [FindingsGroup] ,
3145 [Finding] ,
3146 [URL] ,
3147 [Details] )
3148
3149 SELECT
3150 168 AS [CheckID] ,
3151 250 AS [Priority] ,
3152 'Server Info' AS [FindingsGroup] ,
3153 'Full-text Filter Daemon Launcher is Currently Offline' AS [Finding] ,
3154 '' AS [URL] ,
3155 ( 'Oops! It looks like the ' + [servicename] + ' service is ' + [status_desc] + '. The startup type is ' + [startup_type_desc] + '.'
3156 ) AS [Details]
3157 FROM
3158 [sys].[dm_server_services]
3159 WHERE [status_desc] <> 'Running'
3160 AND [servicename] LIKE 'SQL Full-text Filter Daemon Launcher%'
3161
3162 END;
3163 END;
3164
3165/*This checks which service account SQL Server is running as.*/
3166IF @ProductVersionMajor >= 10 AND @ProductVersionMinor >= 50
3167 AND NOT EXISTS ( SELECT 1
3168 FROM #SkipChecks
3169 WHERE DatabaseName IS NULL AND CheckID = 169 )
3170
3171 BEGIN
3172 IF EXISTS ( SELECT 1
3173 FROM sys.all_objects
3174 WHERE name = 'dm_server_services' )
3175 BEGIN
3176 INSERT INTO [#BlitzResults]
3177 ( [CheckID] ,
3178 [Priority] ,
3179 [FindingsGroup] ,
3180 [Finding] ,
3181 [URL] ,
3182 [Details] )
3183
3184 SELECT
3185 169 AS [CheckID] ,
3186 250 AS [Priority] ,
3187 'Informational' AS [FindingsGroup] ,
3188 'SQL Server is running under an NT Service account' AS [Finding] ,
3189 'http://BrentOzar.com/go/setup' AS [URL] ,
3190 ( 'I''m running as ' + [service_account] + '. I wish I had an Active Directory service account instead.'
3191 ) AS [Details]
3192 FROM
3193 [sys].[dm_server_services]
3194 WHERE [service_account] LIKE 'NT Service%'
3195 AND [servicename] LIKE 'SQL Server%'
3196 AND [servicename] NOT LIKE 'SQL Server Agent%'
3197
3198 END;
3199 END;
3200
3201/*This checks which service account SQL Agent is running as.*/
3202IF @ProductVersionMajor >= 10 AND @ProductVersionMinor >= 50
3203 AND NOT EXISTS ( SELECT 1
3204 FROM #SkipChecks
3205 WHERE DatabaseName IS NULL AND CheckID = 170 )
3206
3207 BEGIN
3208 IF EXISTS ( SELECT 1
3209 FROM sys.all_objects
3210 WHERE name = 'dm_server_services' )
3211 BEGIN
3212 INSERT INTO [#BlitzResults]
3213 ( [CheckID] ,
3214 [Priority] ,
3215 [FindingsGroup] ,
3216 [Finding] ,
3217 [URL] ,
3218 [Details] )
3219
3220 SELECT
3221 170 AS [CheckID] ,
3222 250 AS [Priority] ,
3223 'Informational' AS [FindingsGroup] ,
3224 'SQL Server Agent is running under an NT Service account' AS [Finding] ,
3225 'http://BrentOzar.com/go/setup' AS [URL] ,
3226 ( 'I''m running as ' + [service_account] + '. I wish I had an Active Directory service account instead.'
3227 ) AS [Details]
3228 FROM
3229 [sys].[dm_server_services]
3230 WHERE [service_account] LIKE 'NT Service%'
3231 AND [servicename] LIKE 'SQL Server Agent%'
3232
3233 END;
3234 END;
3235
3236/*This counts memory dumps and gives min and max date of in view*/
3237IF @ProductVersionMajor >= 10 AND @ProductVersionMinor >= 50
3238 AND NOT EXISTS ( SELECT 1
3239 FROM #SkipChecks
3240 WHERE DatabaseName IS NULL AND CheckID = 171 )
3241 BEGIN
3242 IF EXISTS ( SELECT 1
3243 FROM sys.all_objects
3244 WHERE name = 'dm_server_memory_dumps' )
3245 BEGIN
3246 IF 5 <= (SELECT COUNT(*) FROM [sys].[dm_server_memory_dumps] WHERE [creation_time] >= DATEADD(year, -1, GETDATE()))
3247 INSERT INTO [#BlitzResults]
3248 ( [CheckID] ,
3249 [Priority] ,
3250 [FindingsGroup] ,
3251 [Finding] ,
3252 [URL] ,
3253 [Details] )
3254
3255 SELECT
3256 171 AS [CheckID] ,
3257 20 AS [Priority] ,
3258 'Reliability' AS [FindingsGroup] ,
3259 'Memory Dumps Have Occurred' AS [Finding] ,
3260 'http://BrentOzar.com/go/dump' AS [URL] ,
3261 ( 'That ain''t good. I''ve had ' +
3262 CAST(COUNT(*) AS VARCHAR(100)) + ' memory dumps between ' +
3263 CAST(CAST(MIN([creation_time]) AS DATETIME) AS VARCHAR(100)) +
3264 ' and ' +
3265 CAST(CAST(MAX([creation_time]) AS DATETIME) AS VARCHAR(100)) +
3266 '!'
3267 ) AS [Details]
3268 FROM
3269 [sys].[dm_server_memory_dumps]
3270 WHERE [creation_time] >= DATEADD(year, -1, GETDATE());
3271
3272 END;
3273 END;
3274
3275/*Checks to see if you're on Developer or Evaluation*/
3276 IF NOT EXISTS ( SELECT 1
3277 FROM #SkipChecks
3278 WHERE DatabaseName IS NULL AND CheckID = 173 )
3279 BEGIN
3280 INSERT INTO [#BlitzResults]
3281 ( [CheckID] ,
3282 [Priority] ,
3283 [FindingsGroup] ,
3284 [Finding] ,
3285 [URL] ,
3286 [Details] )
3287
3288 SELECT
3289 173 AS [CheckID] ,
3290 200 AS [Priority] ,
3291 'Licensing' AS [FindingsGroup] ,
3292 'Non-Production License' AS [Finding] ,
3293 'http://BrentOzar.com/go/licensing' AS [URL] ,
3294 ( 'We''re not the licensing police, but if this is supposed to be a production server, and you''re running ' +
3295 CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) +
3296 ' the good folks at Microsoft might get upset with you. Better start counting those cores.'
3297 ) AS [Details]
3298 WHERE CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) LIKE '%Developer%'
3299 OR CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) LIKE '%Evaluation%'
3300
3301 END
3302
3303/*Checks to see if Buffer Pool Extensions are in use*/
3304 IF @ProductVersionMajor >= 12
3305 AND NOT EXISTS ( SELECT 1
3306 FROM #SkipChecks
3307 WHERE DatabaseName IS NULL AND CheckID = 174 )
3308 BEGIN
3309 INSERT INTO [#BlitzResults]
3310 ( [CheckID] ,
3311 [Priority] ,
3312 [FindingsGroup] ,
3313 [Finding] ,
3314 [URL] ,
3315 [Details] )
3316
3317 SELECT
3318 174 AS [CheckID] ,
3319 200 AS [Priority] ,
3320 'Performance' AS [FindingsGroup] ,
3321 'Buffer Pool Extensions Enabled' AS [Finding] ,
3322 'http://BrentOzar.com/go/bpe' AS [URL] ,
3323 ( 'You have Buffer Pool Extensions enabled, and one lives here: ' +
3324 [path] +
3325 '. It''s currently ' +
3326 CASE WHEN [current_size_in_kb] / 1024. / 1024. > 0
3327 THEN CAST([current_size_in_kb] / 1024. / 1024. AS VARCHAR(100))
3328 + ' GB'
3329 ELSE CAST([current_size_in_kb] / 1024. AS VARCHAR(100))
3330 + ' MB'
3331 END +
3332 '. Did you know that BPEs only provide single threaded access 8 bytes at a time?'
3333 ) AS [Details]
3334 FROM sys.dm_os_buffer_pool_extension_configuration
3335 WHERE [state_description] <> 'BUFFER POOL EXTENSION DISABLED'
3336
3337 END
3338
3339/*Check for too many tempdb files*/
3340 IF NOT EXISTS ( SELECT 1
3341 FROM #SkipChecks
3342 WHERE DatabaseName IS NULL AND CheckID = 175 )
3343 BEGIN
3344 INSERT INTO #BlitzResults
3345 ( CheckID ,
3346 DatabaseName ,
3347 Priority ,
3348 FindingsGroup ,
3349 Finding ,
3350 URL ,
3351 Details
3352 )
3353 SELECT DISTINCT
3354 175 AS CheckID ,
3355 'TempDB' AS DatabaseName ,
3356 170 AS Priority ,
3357 'File Configuration' AS FindingsGroup ,
3358 'TempDB Has >16 Data Files' AS Finding ,
3359 'http://BrentOzar.com/go/tempdb' AS URL ,
3360 'Woah, Nelly! TempDB has ' + CAST(COUNT_BIG(*) AS VARCHAR) + '. Did you forget to terminate a loop somewhere?' AS Details
3361 FROM sys.[master_files] AS [mf]
3362 WHERE [mf].[database_id] = 2 AND [mf].[type] = 0
3363 HAVING COUNT_BIG(*) > 16;
3364 END
3365
3366 IF NOT EXISTS ( SELECT 1
3367 FROM #SkipChecks
3368 WHERE DatabaseName IS NULL AND CheckID = 176 )
3369 IF EXISTS ( SELECT 1
3370 FROM sys.all_objects
3371 WHERE name = 'dm_xe_sessions' )
3372 BEGIN
3373 BEGIN
3374 INSERT INTO #BlitzResults
3375 ( CheckID ,
3376 DatabaseName ,
3377 Priority ,
3378 FindingsGroup ,
3379 Finding ,
3380 URL ,
3381 Details
3382 )
3383 SELECT DISTINCT
3384 176 AS CheckID ,
3385 '' AS DatabaseName ,
3386 200 AS Priority ,
3387 'Monitoring' AS FindingsGroup ,
3388 'Extended Events Hyperextension' AS Finding ,
3389 'http://BrentOzar.com/go/xe' AS URL ,
3390 'Hey big spender, you have ' + CAST(COUNT_BIG(*) AS VARCHAR) + ' Extended Events sessions running. You sure you meant to do that?' AS Details
3391 FROM sys.dm_xe_sessions
3392 WHERE [name] NOT IN
3393 ('system_health', 'sp_server_diagnostics session', 'hkenginexesession'
3394 )
3395 HAVING COUNT_BIG(*) >= 2;
3396 END
3397 END
3398
3399 /*Harmful startup parameter*/
3400 IF NOT EXISTS ( SELECT 1
3401 FROM #SkipChecks
3402 WHERE DatabaseName IS NULL AND CheckID = 177 )
3403 BEGIN
3404 IF EXISTS ( SELECT 1
3405 FROM sys.all_objects
3406 WHERE name = 'dm_server_registry' )
3407
3408 BEGIN
3409 INSERT INTO #BlitzResults
3410 ( CheckID ,
3411 DatabaseName ,
3412 Priority ,
3413 FindingsGroup ,
3414 Finding ,
3415 URL ,
3416 Details
3417 )
3418 SELECT DISTINCT
3419 177 AS CheckID ,
3420 '' AS DatabaseName ,
3421 5 AS Priority ,
3422 'Monitoring' AS FindingsGroup ,
3423 'Disabled Internal Monitoring Features' AS Finding ,
3424 'https://msdn.microsoft.com/en-us/library/ms190737.aspx' AS URL ,
3425 'You have -x as a startup parameter. You should head to the URL and read more about what it does to your system.' AS Details
3426 FROM
3427 [sys].[dm_server_registry] AS [dsr]
3428 WHERE
3429 [dsr].[registry_key] LIKE N'%MSSQLServer\Parameters'
3430 AND [dsr].[value_data] = '-x';;
3431 END
3432 END
3433
3434
3435 /* Reliability - Dangerous Third Party Modules - 179 */
3436 IF NOT EXISTS ( SELECT 1
3437 FROM #SkipChecks
3438 WHERE DatabaseName IS NULL AND CheckID = 179 )
3439 BEGIN
3440 INSERT INTO [#BlitzResults]
3441 ( [CheckID] ,
3442 [Priority] ,
3443 [FindingsGroup] ,
3444 [Finding] ,
3445 [URL] ,
3446 [Details] )
3447
3448 SELECT
3449 179 AS [CheckID] ,
3450 5 AS [Priority] ,
3451 'Reliability' AS [FindingsGroup] ,
3452 'Dangerous Third Party Modules' AS [Finding] ,
3453 'https://support.microsoft.com/en-us/kb/2033238' AS [URL] ,
3454 ( COALESCE(company, '') + ' - ' + COALESCE(description, '') + ' - ' + COALESCE(name, '') + ' - suspected dangerous third party module is installed.') AS [Details]
3455 FROM sys.dm_os_loaded_modules
3456 WHERE UPPER(name) LIKE UPPER('%\ENTAPI.DLL') /* McAfee VirusScan Enterprise */
3457 OR UPPER(name) LIKE UPPER('%\HIPI.DLL') OR UPPER(name) LIKE UPPER('%\HcSQL.dll') OR UPPER(name) LIKE UPPER('%\HcApi.dll') OR UPPER(name) LIKE UPPER('%\HcThe.dll') /* McAfee Host Intrusion */
3458 OR UPPER(name) LIKE UPPER('%\SOPHOS_DETOURED.DLL') OR UPPER(name) LIKE UPPER('%\SOPHOS_DETOURED_x64.DLL') OR UPPER(name) LIKE UPPER('%\SWI_IFSLSP_64.dll') /* Sophos AV */
3459 OR UPPER(name) LIKE UPPER('%\PIOLEDB.DLL') OR UPPER(name) LIKE UPPER('%\PISDK.DLL') /* OSISoft PI data access */
3460
3461 END
3462
3463 /*Find shrink database tasks*/
3464
3465 IF NOT EXISTS ( SELECT 1
3466 FROM #SkipChecks
3467 WHERE DatabaseName IS NULL AND CheckID = 180 )
3468 BEGIN
3469 ;
3470 WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS [dts])
3471 ,[maintenance_plan_steps] AS (
3472 SELECT [name]
3473 , CAST(CAST([packagedata] AS VARBINARY(MAX)) AS XML) AS [maintenance_plan_xml]
3474 FROM [msdb].[dbo].[sysssispackages]
3475 WHERE [packagetype] = 6
3476 )
3477 INSERT INTO [#BlitzResults]
3478 ( [CheckID] ,
3479 [Priority] ,
3480 [FindingsGroup] ,
3481 [Finding] ,
3482 [URL] ,
3483 [Details] )
3484 SELECT
3485 180 AS [CheckID] ,
3486 100 AS [Priority] ,
3487 'Performance' AS [FindingsGroup] ,
3488 'Shrink Database Step In Maintenance Plan' AS [Finding] ,
3489 'http://BrentOzar.com/go/autoshrink' AS [URL] ,
3490 'The maintenance plan ' + [mps].[name] + ' has a step to shrink databases in it. Shrinking databases is as outdated as maintenance plans.' AS [Details]
3491 FROM [maintenance_plan_steps] [mps]
3492 CROSS APPLY [maintenance_plan_xml].[nodes]('//dts:Executables/dts:Executable') [t]([c])
3493 WHERE [c].[value]('(@dts:ObjectName)', 'VARCHAR(128)') = 'Shrink Database Task'
3494
3495 END
3496
3497
3498 /*Find repetitive maintenance tasks*/
3499 IF NOT EXISTS ( SELECT 1
3500 FROM #SkipChecks
3501 WHERE DatabaseName IS NULL AND CheckID = 181 )
3502 BEGIN
3503 ;
3504 WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS [dts])
3505 ,[maintenance_plan_steps] AS (
3506 SELECT [name]
3507 , CAST(CAST([packagedata] AS VARBINARY(MAX)) AS XML) AS [maintenance_plan_xml]
3508 FROM [msdb].[dbo].[sysssispackages]
3509 WHERE [packagetype] = 6
3510 ), [maintenance_plan_table] AS (
3511 SELECT [mps].[name]
3512 ,[c].[value]('(@dts:ObjectName)', 'NVARCHAR(128)') AS [step_name]
3513 FROM [maintenance_plan_steps] [mps]
3514 CROSS APPLY [maintenance_plan_xml].[nodes]('//dts:Executables/dts:Executable') [t]([c])
3515 ), [mp_steps_pretty] AS (SELECT DISTINCT [m1].[name] ,
3516 STUFF((SELECT N', ' + [m2].[step_name] FROM [maintenance_plan_table] AS [m2] WHERE [m1].[name] = [m2].[name]
3517 FOR XML PATH(N'')), 1, 2, N'') AS [maintenance_plan_steps]
3518 FROM [maintenance_plan_table] AS [m1])
3519
3520 INSERT INTO [#BlitzResults]
3521 ( [CheckID] ,
3522 [Priority] ,
3523 [FindingsGroup] ,
3524 [Finding] ,
3525 [URL] ,
3526 [Details] )
3527
3528 SELECT
3529 181 AS [CheckID] ,
3530 100 AS [Priority] ,
3531 'Performance' AS [FindingsGroup] ,
3532 'Repetitive Steps In Maintenance Plans' AS [Finding] ,
3533 'https://ola.hallengren.com/' AS [URL] ,
3534 'The maintenance plan ' + [m].[name] + ' is doing repetitive work on indexes and statistics. Perhaps it''s time to try something more modern?' AS [Details]
3535 FROM [mp_steps_pretty] m
3536 WHERE m.[maintenance_plan_steps] LIKE '%Rebuild%Reorganize%'
3537 OR m.[maintenance_plan_steps] LIKE '%Rebuild%Update%'
3538
3539 END
3540
3541
3542 IF @CheckUserDatabaseObjects = 1
3543 BEGIN
3544
3545 /*
3546 But what if you need to run a query in every individual database?
3547 Check out CheckID 99 below. Yes, it uses sp_MSforeachdb, and no,
3548 we're not happy about that. sp_MSforeachdb is known to have a lot
3549 of issues, like skipping databases sometimes. However, this is the
3550 only built-in option that we have. If you're writing your own code
3551 for database maintenance, consider Aaron Bertrand's alternative:
3552 http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
3553 We don't include that as part of sp_Blitz, of course, because
3554 copying and distributing copyrighted code from others without their
3555 written permission isn't a good idea.
3556 */
3557 IF NOT EXISTS ( SELECT 1
3558 FROM #SkipChecks
3559 WHERE DatabaseName IS NULL AND CheckID = 99 )
3560 BEGIN
3561 EXEC dbo.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.tables WITH (NOLOCK) WHERE name = ''sysmergepublications'' ) IF EXISTS ( SELECT * FROM sysmergepublications WITH (NOLOCK) WHERE retention = 0) INSERT INTO #BlitzResults (CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 99, DB_NAME(), 110, ''Performance'', ''Infinite merge replication metadata retention period'', ''http://BrentOzar.com/go/merge'', (''The ['' + DB_NAME() + ''] database has merge replication metadata retention period set to infinite - this can be the case of significant performance issues.'')';
3562 END
3563 /*
3564 Note that by using sp_MSforeachdb, we're running the query in all
3565 databases. We're not checking #SkipChecks here for each database to
3566 see if we should run the check in this database. That means we may
3567 still run a skipped check if it involves sp_MSforeachdb. We just
3568 don't output those results in the last step.
3569 */
3570
3571
3572 IF NOT EXISTS ( SELECT 1
3573 FROM #SkipChecks
3574 WHERE DatabaseName IS NULL AND CheckID = 163 )
3575 AND EXISTS(SELECT * FROM sys.all_objects WHERE name = 'database_query_store_options')
3576 BEGIN
3577 EXEC dbo.sp_MSforeachdb 'USE [?];
3578 INSERT INTO #BlitzResults
3579 (CheckID,
3580 DatabaseName,
3581 Priority,
3582 FindingsGroup,
3583 Finding,
3584 URL,
3585 Details)
3586 SELECT TOP 1 163,
3587 ''?'',
3588 10,
3589 ''Performance'',
3590 ''Query Store Disabled'',
3591 ''http://BrentOzar.com/go/querystore'',
3592 (''The new SQL Server 2016 Query Store feature has not been enabled on this database.'')
3593 FROM [?].sys.database_query_store_options WHERE desired_state = 0 AND ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''DWConfiguration'', ''DWDiagnostics'', ''DWQueue'', ''ReportServer'', ''ReportServerTempDB'')';
3594 END
3595
3596 IF NOT EXISTS ( SELECT 1
3597 FROM #SkipChecks
3598 WHERE DatabaseName IS NULL AND CheckID = 182 )
3599 AND EXISTS(SELECT * FROM sys.all_objects WHERE name = 'database_query_store_options')
3600 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Enterprise%'
3601 AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Developer%'
3602 BEGIN
3603 EXEC dbo.sp_MSforeachdb 'USE [?];
3604 INSERT INTO #BlitzResults
3605 (CheckID,
3606 DatabaseName,
3607 Priority,
3608 FindingsGroup,
3609 Finding,
3610 URL,
3611 Details)
3612 SELECT TOP 1 182,
3613 ''?'',
3614 20,
3615 ''Reliability'',
3616 ''Query Store Cleanup Disabled'',
3617 ''http://BrentOzar.com/go/cleanup'',
3618 (''SQL 2016 RTM has a bug involving dumps that happen every time Query Store cleanup jobs run.'')
3619 FROM [?].sys.database_query_store_options WHERE desired_state <> 0 AND ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''DWConfiguration'', ''DWDiagnostics'', ''DWQueue'', ''ReportServer'', ''ReportServerTempDB'')';
3620 END
3621
3622
3623 IF NOT EXISTS ( SELECT 1
3624 FROM #SkipChecks
3625 WHERE DatabaseName IS NULL AND CheckID = 41 )
3626 BEGIN
3627 EXEC dbo.sp_MSforeachdb 'use [?];
3628 INSERT INTO #BlitzResults
3629 (CheckID,
3630 DatabaseName,
3631 Priority,
3632 FindingsGroup,
3633 Finding,
3634 URL,
3635 Details)
3636 SELECT 41,
3637 ''?'',
3638 170,
3639 ''File Configuration'',
3640 ''Multiple Log Files on One Drive'',
3641 ''http://BrentOzar.com/go/manylogs'',
3642 (''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.'')
3643 FROM [?].sys.database_files WHERE type_desc = ''LOG''
3644 AND ''?'' <> ''[tempdb]''
3645 GROUP BY LEFT(physical_name, 1)
3646 HAVING COUNT(*) > 1';
3647 END
3648
3649 IF NOT EXISTS ( SELECT 1
3650 FROM #SkipChecks
3651 WHERE DatabaseName IS NULL AND CheckID = 42 )
3652 BEGIN
3653 EXEC dbo.sp_MSforeachdb 'use [?];
3654 INSERT INTO #BlitzResults
3655 (CheckID,
3656 DatabaseName,
3657 Priority,
3658 FindingsGroup,
3659 Finding,
3660 URL,
3661 Details)
3662 SELECT DISTINCT 42,
3663 ''?'',
3664 170,
3665 ''File Configuration'',
3666 ''Uneven File Growth Settings in One Filegroup'',
3667 ''http://BrentOzar.com/go/grow'',
3668 (''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.'')
3669 FROM [?].sys.database_files
3670 WHERE type_desc = ''ROWS''
3671 GROUP BY data_space_id
3672 HAVING COUNT(DISTINCT growth) > 1 OR COUNT(DISTINCT is_percent_growth) > 1';
3673 END
3674
3675
3676 IF NOT EXISTS ( SELECT 1
3677 FROM #SkipChecks
3678 WHERE DatabaseName IS NULL AND CheckID = 82 )
3679 BEGIN
3680 EXEC sp_MSforeachdb 'use [?];
3681 INSERT INTO #BlitzResults
3682 (CheckID,
3683 DatabaseName,
3684 Priority,
3685 FindingsGroup,
3686 Finding,
3687 URL, Details)
3688 SELECT DISTINCT 82 AS CheckID,
3689 ''?'' as DatabaseName,
3690 170 AS Priority,
3691 ''File Configuration'' AS FindingsGroup,
3692 ''File growth set to percent'',
3693 ''http://brentozar.com/go/percentgrowth'' AS URL,
3694 ''The ['' + DB_NAME() + ''] database file '' + f.physical_name + '' has grown to '' + CAST((f.size * 8 / 1000000) AS NVARCHAR(10)) + '' GB, and is using percent filegrowth settings. This can lead to slow performance during growths if Instant File Initialization is not enabled.''
3695 FROM [?].sys.database_files f
3696 WHERE is_percent_growth = 1 and size > 128000 ';
3697 END
3698
3699
3700
3701 /* addition by Henrik Staun Poulsen, Stovi Software */
3702 IF NOT EXISTS ( SELECT 1
3703 FROM #SkipChecks
3704 WHERE DatabaseName IS NULL AND CheckID = 158 )
3705 BEGIN
3706 EXEC sp_MSforeachdb 'use [?];
3707 INSERT INTO #BlitzResults
3708 (CheckID,
3709 DatabaseName,
3710 Priority,
3711 FindingsGroup,
3712 Finding,
3713 URL, Details)
3714 SELECT DISTINCT 158 AS CheckID,
3715 ''?'' as DatabaseName,
3716 170 AS Priority,
3717 ''File Configuration'' AS FindingsGroup,
3718 ''File growth set to 1MB'',
3719 ''http://brentozar.com/go/percentgrowth'' AS URL,
3720 ''The ['' + DB_NAME() + ''] database file '' + f.physical_name + '' is using 1MB filegrowth settings, but it has grown to '' + CAST((f.size * 8 / 1000000) AS NVARCHAR(10)) + '' GB. Time to up the growth amount.''
3721 FROM [?].sys.database_files f
3722 WHERE is_percent_growth = 0 and growth=128 and size > 128000 ';
3723 END
3724
3725
3726
3727 IF NOT EXISTS ( SELECT 1
3728 FROM #SkipChecks
3729 WHERE DatabaseName IS NULL AND CheckID = 33 )
3730 BEGIN
3731 IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
3732 AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
3733 BEGIN
3734 EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults
3735 (CheckID,
3736 DatabaseName,
3737 Priority,
3738 FindingsGroup,
3739 Finding,
3740 URL,
3741 Details)
3742 SELECT DISTINCT 33,
3743 db_name(),
3744 200,
3745 ''Licensing'',
3746 ''Enterprise Edition Features In Use'',
3747 ''http://BrentOzar.com/go/ee'',
3748 (''The ['' + DB_NAME() + ''] database is using '' + feature_name + ''. If this database is restored onto a Standard Edition server, the restore will fail.'')
3749 FROM [?].sys.dm_db_persisted_sku_features';
3750 END;
3751 END
3752
3753
3754 IF NOT EXISTS ( SELECT 1
3755 FROM #SkipChecks
3756 WHERE DatabaseName IS NULL AND CheckID = 19 )
3757 BEGIN
3758 /* Method 1: Check sys.databases parameters */
3759 INSERT INTO #BlitzResults
3760 ( CheckID ,
3761 DatabaseName ,
3762 Priority ,
3763 FindingsGroup ,
3764 Finding ,
3765 URL ,
3766 Details
3767 )
3768
3769 SELECT 19 AS CheckID ,
3770 [name] AS DatabaseName ,
3771 200 AS Priority ,
3772 'Informational' AS FindingsGroup ,
3773 'Replication In Use' AS Finding ,
3774 'http://BrentOzar.com/go/repl' AS URL ,
3775 ( 'Database [' + [name]
3776 + '] is a replication publisher, subscriber, or distributor.' ) AS Details
3777 FROM sys.databases
3778 WHERE name NOT IN ( SELECT DISTINCT
3779 DatabaseName
3780 FROM #SkipChecks
3781 WHERE CheckID IS NULL)
3782 AND is_published = 1
3783 OR is_subscribed = 1
3784 OR is_merge_published = 1
3785 OR is_distributor = 1;
3786
3787 /* Method B: check subscribers for MSreplication_objects tables */
3788 EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults
3789 (CheckID,
3790 DatabaseName,
3791 Priority,
3792 FindingsGroup,
3793 Finding,
3794 URL,
3795 Details)
3796 SELECT DISTINCT 19,
3797 db_name(),
3798 200,
3799 ''Informational'',
3800 ''Replication In Use'',
3801 ''http://BrentOzar.com/go/repl'',
3802 (''['' + DB_NAME() + ''] has MSreplication_objects tables in it, indicating it is a replication subscriber.'')
3803 FROM [?].sys.tables
3804 WHERE name = ''dbo.MSreplication_objects'' AND ''?'' <> ''master''';
3805
3806 END
3807
3808
3809
3810 IF NOT EXISTS ( SELECT 1
3811 FROM #SkipChecks
3812 WHERE DatabaseName IS NULL AND CheckID = 32 )
3813 BEGIN
3814 EXEC dbo.sp_MSforeachdb 'USE [?];
3815 INSERT INTO #BlitzResults
3816 (CheckID,
3817 DatabaseName,
3818 Priority,
3819 FindingsGroup,
3820 Finding,
3821 URL,
3822 Details)
3823 SELECT 32,
3824 ''?'',
3825 150,
3826 ''Performance'',
3827 ''Triggers on Tables'',
3828 ''http://BrentOzar.com/go/trig'',
3829 (''The ['' + DB_NAME() + ''] database has '' + CAST(SUM(1) AS NVARCHAR(50)) + '' triggers.'')
3830 FROM [?].sys.triggers t INNER JOIN [?].sys.objects o ON t.parent_id = o.object_id
3831 INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id WHERE t.is_ms_shipped = 0 AND DB_NAME() != ''ReportServer''
3832 HAVING SUM(1) > 0';
3833 END
3834
3835 IF NOT EXISTS ( SELECT 1
3836 FROM #SkipChecks
3837 WHERE DatabaseName IS NULL AND CheckID = 38 )
3838 BEGIN
3839 EXEC dbo.sp_MSforeachdb 'USE [?];
3840 INSERT INTO #BlitzResults
3841 (CheckID,
3842 DatabaseName,
3843 Priority,
3844 FindingsGroup,
3845 Finding,
3846 URL,
3847 Details)
3848 SELECT DISTINCT 38,
3849 ''?'',
3850 110,
3851 ''Performance'',
3852 ''Active Tables Without Clustered Indexes'',
3853 ''http://BrentOzar.com/go/heaps'',
3854 (''The ['' + DB_NAME() + ''] database has heaps - tables without a clustered index - that are being actively queried.'')
3855 FROM [?].sys.indexes i INNER JOIN [?].sys.objects o ON i.object_id = o.object_id
3856 INNER JOIN [?].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
3857 INNER JOIN sys.databases sd ON sd.name = ''?''
3858 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
3859 WHERE i.type_desc = ''HEAP'' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NOT NULL
3860 AND sd.name <> ''tempdb'' AND sd.name <> ''DWDiagnostics'' AND o.is_ms_shipped = 0 AND o.type <> ''S''';
3861 END
3862
3863 IF NOT EXISTS ( SELECT 1
3864 FROM #SkipChecks
3865 WHERE DatabaseName IS NULL AND CheckID = 164 )
3866 AND EXISTS(SELECT * FROM sys.all_objects WHERE name = 'fn_validate_plan_guide')
3867 BEGIN
3868 EXEC dbo.sp_MSforeachdb 'USE [?];
3869 INSERT INTO #BlitzResults
3870 (CheckID,
3871 DatabaseName,
3872 Priority,
3873 FindingsGroup,
3874 Finding,
3875 URL,
3876 Details)
3877 SELECT DISTINCT 164,
3878 ''?'',
3879 20,
3880 ''Reliability'',
3881 ''Plan Guides Failing'',
3882 ''http://BrentOzar.com/go/misguided'',
3883 (''The ['' + DB_NAME() + ''] database has plan guides that are no longer valid, so the queries involved may be failing silently.'')
3884 FROM [?].sys.plan_guides g CROSS APPLY fn_validate_plan_guide(g.plan_guide_id)';
3885 END
3886
3887 IF NOT EXISTS ( SELECT 1
3888 FROM #SkipChecks
3889 WHERE DatabaseName IS NULL AND CheckID = 39 )
3890 BEGIN
3891 EXEC dbo.sp_MSforeachdb 'USE [?];
3892 INSERT INTO #BlitzResults
3893 (CheckID,
3894 DatabaseName,
3895 Priority,
3896 FindingsGroup,
3897 Finding,
3898 URL,
3899 Details)
3900 SELECT DISTINCT 39,
3901 ''?'',
3902 150,
3903 ''Performance'',
3904 ''Inactive Tables Without Clustered Indexes'',
3905 ''http://BrentOzar.com/go/heaps'',
3906 (''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.'')
3907 FROM [?].sys.indexes i INNER JOIN [?].sys.objects o ON i.object_id = o.object_id
3908 INNER JOIN [?].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
3909 INNER JOIN sys.databases sd ON sd.name = ''?''
3910 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
3911 WHERE i.type_desc = ''HEAP'' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NULL
3912 AND sd.name <> ''tempdb'' AND sd.name <> ''DWDiagnostics'' AND o.is_ms_shipped = 0 AND o.type <> ''S''';
3913 END
3914
3915 IF NOT EXISTS ( SELECT 1
3916 FROM #SkipChecks
3917 WHERE DatabaseName IS NULL AND CheckID = 46 )
3918 BEGIN
3919 EXEC dbo.sp_MSforeachdb 'USE [?];
3920 INSERT INTO #BlitzResults
3921 (CheckID,
3922 DatabaseName,
3923 Priority,
3924 FindingsGroup,
3925 Finding,
3926 URL,
3927 Details)
3928 SELECT 46,
3929 ''?'',
3930 150,
3931 ''Performance'',
3932 ''Leftover Fake Indexes From Wizards'',
3933 ''http://BrentOzar.com/go/hypo'',
3934 (''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.'')
3935 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
3936 WHERE i.is_hypothetical = 1';
3937 END
3938
3939 IF NOT EXISTS ( SELECT 1
3940 FROM #SkipChecks
3941 WHERE DatabaseName IS NULL AND CheckID = 47 )
3942 BEGIN
3943 EXEC dbo.sp_MSforeachdb 'USE [?];
3944 INSERT INTO #BlitzResults
3945 (CheckID,
3946 DatabaseName,
3947 Priority,
3948 FindingsGroup,
3949 Finding,
3950 URL,
3951 Details)
3952 SELECT 47,
3953 ''?'',
3954 100,
3955 ''Performance'',
3956 ''Indexes Disabled'',
3957 ''http://BrentOzar.com/go/ixoff'',
3958 (''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.'')
3959 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
3960 WHERE i.is_disabled = 1';
3961 END
3962
3963
3964 IF NOT EXISTS ( SELECT 1
3965 FROM #SkipChecks
3966 WHERE DatabaseName IS NULL AND CheckID = 48 )
3967 BEGIN
3968 EXEC dbo.sp_MSforeachdb 'USE [?];
3969 INSERT INTO #BlitzResults
3970 (CheckID,
3971 DatabaseName,
3972 Priority,
3973 FindingsGroup,
3974 Finding,
3975 URL,
3976 Details)
3977 SELECT DISTINCT 48,
3978 ''?'',
3979 150,
3980 ''Performance'',
3981 ''Foreign Keys Not Trusted'',
3982 ''http://BrentOzar.com/go/trust'',
3983 (''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.'')
3984 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
3985 WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0 AND ''?'' NOT IN (''master'', ''model'', ''msdb'', ''ReportServer'', ''ReportServerTempDB'')';
3986 END
3987
3988 IF NOT EXISTS ( SELECT 1
3989 FROM #SkipChecks
3990 WHERE DatabaseName IS NULL AND CheckID = 56 )
3991 BEGIN
3992 EXEC dbo.sp_MSforeachdb 'USE [?];
3993 INSERT INTO #BlitzResults
3994 (CheckID,
3995 DatabaseName,
3996 Priority,
3997 FindingsGroup,
3998 Finding,
3999 URL,
4000 Details)
4001 SELECT 56,
4002 ''?'',
4003 150,
4004 ''Performance'',
4005 ''Check Constraint Not Trusted'',
4006 ''http://BrentOzar.com/go/trust'',
4007 (''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.'')
4008 from [?].sys.check_constraints i INNER JOIN [?].sys.objects o ON i.parent_object_id = o.object_id
4009 INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id
4010 WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0';
4011 END
4012
4013 IF NOT EXISTS ( SELECT 1
4014 FROM #SkipChecks
4015 WHERE DatabaseName IS NULL AND CheckID = 95 )
4016 BEGIN
4017 IF @@VERSION NOT LIKE '%Microsoft SQL Server 2000%'
4018 AND @@VERSION NOT LIKE '%Microsoft SQL Server 2005%'
4019 BEGIN
4020 EXEC dbo.sp_MSforeachdb 'USE [?];
4021 INSERT INTO #BlitzResults
4022 (CheckID,
4023 DatabaseName,
4024 Priority,
4025 FindingsGroup,
4026 Finding,
4027 URL,
4028 Details)
4029 SELECT TOP 1 95 AS CheckID,
4030 ''?'' as DatabaseName,
4031 110 AS Priority,
4032 ''Performance'' AS FindingsGroup,
4033 ''Plan Guides Enabled'' AS Finding,
4034 ''http://BrentOzar.com/go/guides'' AS URL,
4035 (''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
4036 FROM [?].sys.plan_guides WHERE is_disabled = 0'
4037 END;
4038 END
4039
4040 IF NOT EXISTS ( SELECT 1
4041 FROM #SkipChecks
4042 WHERE DatabaseName IS NULL AND CheckID = 60 )
4043 BEGIN
4044 EXEC sp_MSforeachdb 'USE [?];
4045 INSERT INTO #BlitzResults
4046 (CheckID,
4047 DatabaseName,
4048 Priority,
4049 FindingsGroup,
4050 Finding,
4051 URL,
4052 Details)
4053 SELECT DISTINCT 60 AS CheckID,
4054 ''?'' as DatabaseName,
4055 100 AS Priority,
4056 ''Performance'' AS FindingsGroup,
4057 ''Fill Factor Changed'',
4058 ''http://brentozar.com/go/fillfactor'' AS URL,
4059 ''The ['' + DB_NAME() + ''] database has objects with fill factor < 80%. This can cause memory and storage performance problems, but may also prevent page splits.''
4060 FROM [?].sys.indexes
4061 WHERE fill_factor <> 0 AND fill_factor < 80 AND is_disabled = 0 AND is_hypothetical = 0';
4062 END
4063
4064
4065
4066 IF NOT EXISTS ( SELECT 1
4067 FROM #SkipChecks
4068 WHERE DatabaseName IS NULL AND CheckID = 78 )
4069 BEGIN
4070 EXECUTE master.sys.sp_MSforeachdb 'USE [?];
4071 INSERT INTO #Recompile
4072 SELECT DBName = DB_Name(), SPName = SO.name, SM.is_recompiled, ISR.SPECIFIC_SCHEMA
4073 FROM sys.sql_modules AS SM
4074 LEFT OUTER JOIN master.sys.databases AS sDB ON SM.object_id = DB_id()
4075 LEFT OUTER JOIN dbo.sysobjects AS SO ON SM.object_id = SO.id and type = ''P''
4076 LEFT OUTER JOIN INFORMATION_SCHEMA.ROUTINES AS ISR on ISR.Routine_Name = SO.name AND ISR.SPECIFIC_CATALOG = DB_Name()
4077 WHERE SM.is_recompiled=1
4078 '
4079 INSERT INTO #BlitzResults
4080 (Priority,
4081 FindingsGroup,
4082 Finding,
4083 DatabaseName,
4084 URL,
4085 Details,
4086 CheckID)
4087 SELECT [Priority] = '100',
4088 FindingsGroup = 'Performance',
4089 Finding = 'Stored Procedure WITH RECOMPILE',
4090 DatabaseName = DBName,
4091 URL = 'http://BrentOzar.com/go/recompile',
4092 Details = '[' + DBName + '].[' + SPSchema + '].[' + ProcName + '] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.',
4093 CheckID = '78'
4094 FROM #Recompile AS TR WHERE ProcName NOT LIKE 'sp_AskBrent%' AND ProcName NOT LIKE 'sp_Blitz%'
4095 DROP TABLE #Recompile;
4096 END
4097
4098
4099
4100 IF NOT EXISTS ( SELECT 1
4101 FROM #SkipChecks
4102 WHERE DatabaseName IS NULL AND CheckID = 86 )
4103 BEGIN
4104 EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 86, DB_NAME(), 230, ''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'')';
4105 END
4106
4107
4108 /*Check for non-aligned indexes in partioned databases*/
4109
4110 IF NOT EXISTS ( SELECT 1
4111 FROM #SkipChecks
4112 WHERE DatabaseName IS NULL AND CheckID = 72 )
4113 BEGIN
4114 EXEC dbo.sp_MSforeachdb 'USE [?];
4115 insert into #partdb(dbname, objectname, type_desc)
4116 SELECT distinct db_name(DB_ID()) as DBName,o.name Object_Name,ds.type_desc
4117 FROM sys.objects AS o JOIN sys.indexes AS i ON o.object_id = i.object_id
4118 JOIN sys.data_spaces ds on ds.data_space_id = i.data_space_id
4119 LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s ON i.object_id = s.object_id AND i.index_id = s.index_id AND s.database_id = DB_ID()
4120 WHERE o.type = ''u''
4121 -- Clustered and Non-Clustered indexes
4122 AND i.type IN (1, 2)
4123 AND o.object_id in
4124 (
4125 SELECT a.object_id from
4126 (SELECT ob.object_id, 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
4127 GROUP BY ob.object_id, ds.type_desc ) a group by a.object_id having COUNT (*) > 1
4128 )'
4129 INSERT INTO #BlitzResults
4130 ( CheckID ,
4131 DatabaseName ,
4132 Priority ,
4133 FindingsGroup ,
4134 Finding ,
4135 URL ,
4136 Details
4137 )
4138 SELECT DISTINCT
4139 72 AS CheckID ,
4140 dbname AS DatabaseName ,
4141 100 AS Priority ,
4142 'Performance' AS FindingsGroup ,
4143 'The partitioned database ' + dbname
4144 + ' may have non-aligned indexes' AS Finding ,
4145 'http://BrentOzar.com/go/aligned' AS URL ,
4146 'Having non-aligned indexes on partitioned tables may cause inefficient query plans and CPU pressure' AS Details
4147 FROM #partdb
4148 WHERE dbname IS NOT NULL
4149 AND dbname NOT IN ( SELECT DISTINCT
4150 DatabaseName
4151 FROM #SkipChecks
4152 WHERE CheckID IS NULL)
4153 DROP TABLE #partdb
4154 END
4155
4156
4157 IF NOT EXISTS ( SELECT 1
4158 FROM #SkipChecks
4159 WHERE DatabaseName IS NULL AND CheckID = 113 )
4160 BEGIN
4161 EXEC dbo.sp_MSforeachdb 'USE [?];
4162 INSERT INTO #BlitzResults
4163 (CheckID,
4164 DatabaseName,
4165 Priority,
4166 FindingsGroup,
4167 Finding,
4168 URL,
4169 Details)
4170 SELECT DISTINCT 113,
4171 ''?'',
4172 50,
4173 ''Reliability'',
4174 ''Full Text Indexes Not Updating'',
4175 ''http://BrentOzar.com/go/fulltext'',
4176 (''At least one full text index in this database has not been crawled in the last week.'')
4177 from [?].sys.fulltext_indexes i WHERE change_tracking_state_desc <> ''AUTO'' AND i.is_enabled = 1 AND i.crawl_end_date < DATEADD(dd, -7, GETDATE())';
4178 END
4179
4180 IF NOT EXISTS ( SELECT 1
4181 FROM #SkipChecks
4182 WHERE DatabaseName IS NULL AND CheckID = 115 )
4183 BEGIN
4184 EXEC dbo.sp_MSforeachdb 'USE [?];
4185 INSERT INTO #BlitzResults
4186 (CheckID,
4187 DatabaseName,
4188 Priority,
4189 FindingsGroup,
4190 Finding,
4191 URL,
4192 Details)
4193 SELECT 115,
4194 ''?'',
4195 110,
4196 ''Performance'',
4197 ''Parallelism Rocket Surgery'',
4198 ''http://BrentOzar.com/go/makeparallel'',
4199 (''['' + DB_NAME() + ''] has a make_parallel function, indicating that an advanced developer may be manhandling SQL Server into forcing queries to go parallel.'')
4200 from [?].INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ''make_parallel'' AND ROUTINE_TYPE = ''FUNCTION''';
4201 END
4202
4203
4204 IF NOT EXISTS ( SELECT 1
4205 FROM #SkipChecks
4206 WHERE DatabaseName IS NULL AND CheckID = 122 )
4207 BEGIN
4208 /* SQL Server 2012 and newer uses temporary stats for AlwaysOn Availability Groups, and those show up as user-created */
4209 IF EXISTS (SELECT *
4210 FROM sys.all_columns c
4211 INNER JOIN sys.all_objects o ON c.object_id = o.object_id
4212 WHERE c.name = 'is_temporary' AND o.name = 'stats')
4213
4214 EXEC dbo.sp_MSforeachdb 'USE [?];
4215 INSERT INTO #BlitzResults
4216 (CheckID,
4217 DatabaseName,
4218 Priority,
4219 FindingsGroup,
4220 Finding,
4221 URL,
4222 Details)
4223 SELECT TOP 1 122,
4224 ''?'',
4225 200,
4226 ''Performance'',
4227 ''User-Created Statistics In Place'',
4228 ''http://BrentOzar.com/go/userstats'',
4229 (''['' + DB_NAME() + ''] has '' + CAST(SUM(1) AS NVARCHAR(10)) + '' user-created statistics. This indicates that someone is being a rocket scientist with the stats, and might actually be slowing things down, especially during stats updates.'')
4230 from [?].sys.stats WHERE user_created = 1 AND is_temporary = 0
4231 HAVING SUM(1) > 0;';
4232
4233 ELSE
4234 EXEC dbo.sp_MSforeachdb 'USE [?];
4235 INSERT INTO #BlitzResults
4236 (CheckID,
4237 DatabaseName,
4238 Priority,
4239 FindingsGroup,
4240 Finding,
4241 URL,
4242 Details)
4243 SELECT 122,
4244 ''?'',
4245 200,
4246 ''Performance'',
4247 ''User-Created Statistics In Place'',
4248 ''http://BrentOzar.com/go/userstats'',
4249 (''['' + DB_NAME() + ''] has '' + CAST(SUM(1) AS NVARCHAR(10)) + '' user-created statistics. This indicates that someone is being a rocket scientist with the stats, and might actually be slowing things down, especially during stats updates.'')
4250 from [?].sys.stats WHERE user_created = 1
4251 HAVING SUM(1) > 0;';
4252
4253
4254 END /* IF NOT EXISTS ( SELECT 1 */
4255
4256
4257 /*Check for high VLF count: this will omit any database snapshots*/
4258
4259 IF NOT EXISTS ( SELECT 1
4260 FROM #SkipChecks
4261 WHERE DatabaseName IS NULL AND CheckID = 69 )
4262 BEGIN
4263 IF @ProductVersionMajor >= 11
4264
4265 BEGIN
4266 EXEC sp_MSforeachdb N'USE [?];
4267 INSERT INTO #LogInfo2012
4268 EXEC sp_executesql N''DBCC LogInfo() WITH NO_INFOMSGS'';
4269 IF @@ROWCOUNT > 999
4270 BEGIN
4271 INSERT INTO #BlitzResults
4272 ( CheckID
4273 ,DatabaseName
4274 ,Priority
4275 ,FindingsGroup
4276 ,Finding
4277 ,URL
4278 ,Details)
4279 SELECT 69
4280 ,DB_NAME()
4281 ,170
4282 ,''File Configuration''
4283 ,''High VLF Count''
4284 ,''http://BrentOzar.com/go/vlf''
4285 ,''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.''
4286 FROM #LogInfo2012
4287 WHERE EXISTS (SELECT name FROM master.sys.databases
4288 WHERE source_database_id is null) ;
4289 END
4290 TRUNCATE TABLE #LogInfo2012;'
4291 DROP TABLE #LogInfo2012;
4292 END
4293 ELSE
4294 BEGIN
4295 EXEC sp_MSforeachdb N'USE [?];
4296 INSERT INTO #LogInfo
4297 EXEC sp_executesql N''DBCC LogInfo() WITH NO_INFOMSGS'';
4298 IF @@ROWCOUNT > 999
4299 BEGIN
4300 INSERT INTO #BlitzResults
4301 ( CheckID
4302 ,DatabaseName
4303 ,Priority
4304 ,FindingsGroup
4305 ,Finding
4306 ,URL
4307 ,Details)
4308 SELECT 69
4309 ,DB_NAME()
4310 ,170
4311 ,''File Configuration''
4312 ,''High VLF Count''
4313 ,''http://BrentOzar.com/go/vlf''
4314 ,''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.''
4315 FROM #LogInfo
4316 WHERE EXISTS (SELECT name FROM master.sys.databases
4317 WHERE source_database_id is null);
4318 END
4319 TRUNCATE TABLE #LogInfo;'
4320 DROP TABLE #LogInfo;
4321 END
4322 END
4323
4324
4325 IF NOT EXISTS ( SELECT 1
4326 FROM #SkipChecks
4327 WHERE DatabaseName IS NULL AND CheckID = 80 )
4328 BEGIN
4329 EXEC dbo.sp_MSforeachdb 'USE [?]; INSERT INTO #BlitzResults (CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details) SELECT DISTINCT 80, DB_NAME(), 170, ''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 AND type <> 2 AND name <> ''DWDiagnostics'' ';
4330 END
4331
4332 END /* IF @CheckUserDatabaseObjects = 1 */
4333
4334 IF @CheckProcedureCache = 1
4335 BEGIN
4336
4337 IF NOT EXISTS ( SELECT 1
4338 FROM #SkipChecks
4339 WHERE DatabaseName IS NULL AND CheckID = 35 )
4340 BEGIN
4341 INSERT INTO #BlitzResults
4342 ( CheckID ,
4343 Priority ,
4344 FindingsGroup ,
4345 Finding ,
4346 URL ,
4347 Details
4348 )
4349 SELECT 35 AS CheckID ,
4350 100 AS Priority ,
4351 'Performance' AS FindingsGroup ,
4352 'Single-Use Plans in Procedure Cache' AS Finding ,
4353 'http://BrentOzar.com/go/single' AS URL ,
4354 ( CAST(COUNT(*) AS VARCHAR(10))
4355 + ' 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
4356 FROM sys.dm_exec_cached_plans AS cp
4357 WHERE cp.usecounts = 1
4358 AND cp.objtype = 'Adhoc'
4359 AND EXISTS ( SELECT
4360 1
4361 FROM sys.configurations
4362 WHERE
4363 name = 'optimize for ad hoc workloads'
4364 AND value_in_use = 0 )
4365 HAVING COUNT(*) > 1;
4366 END
4367
4368
4369 /* Set up the cache tables. Different on 2005 since it doesn't support query_hash, query_plan_hash. */
4370 IF @@VERSION LIKE '%Microsoft SQL Server 2005%'
4371 BEGIN
4372 IF @CheckProcedureCacheFilter = 'CPU'
4373 OR @CheckProcedureCacheFilter IS NULL
4374 BEGIN
4375 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])
4376 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]
4377 FROM sys.dm_exec_query_stats qs
4378 ORDER BY qs.total_worker_time DESC)
4379 INSERT 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])
4380 SELECT 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]
4381 FROM queries qs
4382 LEFT 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
4383 WHERE qsCaught.sql_handle IS NULL;'
4384 EXECUTE(@StringToExecute)
4385 END
4386
4387 IF @CheckProcedureCacheFilter = 'Reads'
4388 OR @CheckProcedureCacheFilter IS NULL
4389 BEGIN
4390 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])
4391 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]
4392 FROM sys.dm_exec_query_stats qs
4393 ORDER BY qs.total_logical_reads DESC)
4394 INSERT 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])
4395 SELECT 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]
4396 FROM queries qs
4397 LEFT 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
4398 WHERE qsCaught.sql_handle IS NULL;'
4399 EXECUTE(@StringToExecute)
4400 END
4401
4402 IF @CheckProcedureCacheFilter = 'ExecCount'
4403 OR @CheckProcedureCacheFilter IS NULL
4404 BEGIN
4405 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])
4406 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]
4407 FROM sys.dm_exec_query_stats qs
4408 ORDER BY qs.execution_count DESC)
4409 INSERT 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])
4410 SELECT 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]
4411 FROM queries qs
4412 LEFT 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
4413 WHERE qsCaught.sql_handle IS NULL;'
4414 EXECUTE(@StringToExecute)
4415 END
4416
4417 IF @CheckProcedureCacheFilter = 'Duration'
4418 OR @CheckProcedureCacheFilter IS NULL
4419 BEGIN
4420 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])
4421 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]
4422 FROM sys.dm_exec_query_stats qs
4423 ORDER BY qs.total_elapsed_time DESC)
4424 INSERT 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])
4425 SELECT 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]
4426 FROM queries qs
4427 LEFT 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
4428 WHERE qsCaught.sql_handle IS NULL;'
4429 EXECUTE(@StringToExecute)
4430 END
4431
4432 END;
4433 IF @ProductVersionMajor >= 10
4434 BEGIN
4435 IF @CheckProcedureCacheFilter = 'CPU'
4436 OR @CheckProcedureCacheFilter IS NULL
4437 BEGIN
4438 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])
4439 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]
4440 FROM sys.dm_exec_query_stats qs
4441 ORDER BY qs.total_worker_time DESC)
4442 INSERT 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])
4443 SELECT 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]
4444 FROM queries qs
4445 LEFT 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
4446 WHERE qsCaught.sql_handle IS NULL;'
4447 EXECUTE(@StringToExecute)
4448 END
4449
4450 IF @CheckProcedureCacheFilter = 'Reads'
4451 OR @CheckProcedureCacheFilter IS NULL
4452 BEGIN
4453 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])
4454 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]
4455 FROM sys.dm_exec_query_stats qs
4456 ORDER BY qs.total_logical_reads DESC)
4457 INSERT 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])
4458 SELECT 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]
4459 FROM queries qs
4460 LEFT 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
4461 WHERE qsCaught.sql_handle IS NULL;'
4462 EXECUTE(@StringToExecute)
4463 END
4464
4465 IF @CheckProcedureCacheFilter = 'ExecCount'
4466 OR @CheckProcedureCacheFilter IS NULL
4467 BEGIN
4468 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])
4469 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]
4470 FROM sys.dm_exec_query_stats qs
4471 ORDER BY qs.execution_count DESC)
4472 INSERT 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])
4473 SELECT 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]
4474 FROM queries qs
4475 LEFT 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
4476 WHERE qsCaught.sql_handle IS NULL;'
4477 EXECUTE(@StringToExecute)
4478 END
4479
4480 IF @CheckProcedureCacheFilter = 'Duration'
4481 OR @CheckProcedureCacheFilter IS NULL
4482 BEGIN
4483 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])
4484 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]
4485 FROM sys.dm_exec_query_stats qs
4486 ORDER BY qs.total_elapsed_time DESC)
4487 INSERT 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])
4488 SELECT 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]
4489 FROM queries qs
4490 LEFT 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
4491 WHERE qsCaught.sql_handle IS NULL;'
4492 EXECUTE(@StringToExecute)
4493 END
4494
4495 /* Populate the query_plan_filtered field. Only works in 2005SP2+, but we're just doing it in 2008 to be safe. */
4496 UPDATE #dm_exec_query_stats
4497 SET query_plan_filtered = qp.query_plan
4498 FROM #dm_exec_query_stats qs
4499 CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
4500 qs.statement_start_offset,
4501 qs.statement_end_offset)
4502 AS qp
4503
4504 END;
4505
4506 /* Populate the additional query_plan, text, and text_filtered fields */
4507 UPDATE #dm_exec_query_stats
4508 SET query_plan = qp.query_plan ,
4509 [text] = st.[text] ,
4510 text_filtered = SUBSTRING(st.text,
4511 ( qs.statement_start_offset
4512 / 2 ) + 1,
4513 ( ( CASE qs.statement_end_offset
4514 WHEN -1
4515 THEN DATALENGTH(st.text)
4516 ELSE qs.statement_end_offset
4517 END
4518 - qs.statement_start_offset )
4519 / 2 ) + 1)
4520 FROM #dm_exec_query_stats qs
4521 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
4522 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
4523 AS qp
4524
4525 /* Dump instances of our own script. We're not trying to tune ourselves. */
4526 DELETE #dm_exec_query_stats
4527 WHERE text LIKE '%sp_Blitz%'
4528 OR text LIKE '%#BlitzResults%'
4529
4530 /* Look for implicit conversions */
4531
4532 IF NOT EXISTS ( SELECT 1
4533 FROM #SkipChecks
4534 WHERE DatabaseName IS NULL AND CheckID = 63 )
4535 BEGIN
4536 INSERT INTO #BlitzResults
4537 ( CheckID ,
4538 Priority ,
4539 FindingsGroup ,
4540 Finding ,
4541 URL ,
4542 Details ,
4543 QueryPlan ,
4544 QueryPlanFiltered
4545 )
4546 SELECT 63 AS CheckID ,
4547 120 AS Priority ,
4548 'Query Plans' AS FindingsGroup ,
4549 'Implicit Conversion' AS Finding ,
4550 'http://BrentOzar.com/go/implicit' AS URL ,
4551 ( 'One of the top resource-intensive queries is comparing two fields that are not the same datatype.' ) AS Details ,
4552 qs.query_plan ,
4553 qs.query_plan_filtered
4554 FROM #dm_exec_query_stats qs
4555 WHERE COALESCE(qs.query_plan_filtered,
4556 CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%CONVERT_IMPLICIT%'
4557 AND COALESCE(qs.query_plan_filtered,
4558 CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%PhysicalOp="Index Scan"%'
4559 END
4560
4561 IF NOT EXISTS ( SELECT 1
4562 FROM #SkipChecks
4563 WHERE DatabaseName IS NULL AND CheckID = 64 )
4564 BEGIN
4565 INSERT INTO #BlitzResults
4566 ( CheckID ,
4567 Priority ,
4568 FindingsGroup ,
4569 Finding ,
4570 URL ,
4571 Details ,
4572 QueryPlan ,
4573 QueryPlanFiltered
4574 )
4575 SELECT 64 AS CheckID ,
4576 120 AS Priority ,
4577 'Query Plans' AS FindingsGroup ,
4578 'Implicit Conversion Affecting Cardinality' AS Finding ,
4579 'http://BrentOzar.com/go/implicit' AS URL ,
4580 ( 'One of the top resource-intensive queries has an implicit conversion that is affecting cardinality estimation.' ) AS Details ,
4581 qs.query_plan ,
4582 qs.query_plan_filtered
4583 FROM #dm_exec_query_stats qs
4584 WHERE COALESCE(qs.query_plan_filtered,
4585 CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT%'
4586 END
4587
4588 /* @cms4j, 29.11.2013: Look for RID or Key Lookups */
4589 IF NOT EXISTS ( SELECT 1
4590 FROM #SkipChecks
4591 WHERE DatabaseName IS NULL AND CheckID = 118 )
4592 BEGIN
4593 INSERT INTO #BlitzResults
4594 ( CheckID ,
4595 Priority ,
4596 FindingsGroup ,
4597 Finding ,
4598 URL ,
4599 Details ,
4600 QueryPlan ,
4601 QueryPlanFiltered
4602 )
4603 SELECT 118 AS CheckID ,
4604 120 AS Priority ,
4605 'Query Plans' AS FindingsGroup ,
4606 'RID or Key Lookups' AS Finding ,
4607 'http://BrentOzar.com/go/lookup' AS URL ,
4608 'One of the top resource-intensive queries contains RID or Key Lookups. Try to avoid them by creating covering indexes.' AS Details ,
4609 qs.query_plan ,
4610 qs.query_plan_filtered
4611 FROM #dm_exec_query_stats qs
4612 WHERE COALESCE(qs.query_plan_filtered,
4613 CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%Lookup="1"%'
4614 END /* @cms4j, 29.11.2013: Look for RID or Key Lookups */
4615
4616
4617 /* Look for missing indexes */
4618 IF NOT EXISTS ( SELECT 1
4619 FROM #SkipChecks
4620 WHERE DatabaseName IS NULL AND CheckID = 65 )
4621 BEGIN
4622 INSERT INTO #BlitzResults
4623 ( CheckID ,
4624 Priority ,
4625 FindingsGroup ,
4626 Finding ,
4627 URL ,
4628 Details ,
4629 QueryPlan ,
4630 QueryPlanFiltered
4631 )
4632 SELECT 65 AS CheckID ,
4633 120 AS Priority ,
4634 'Query Plans' AS FindingsGroup ,
4635 'Missing Index' AS Finding ,
4636 'http://BrentOzar.com/go/missingindex' AS URL ,
4637 ( 'One of the top resource-intensive queries may be dramatically improved by adding an index.' ) AS Details ,
4638 qs.query_plan ,
4639 qs.query_plan_filtered
4640 FROM #dm_exec_query_stats qs
4641 WHERE COALESCE(qs.query_plan_filtered,
4642 CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%MissingIndexGroup%'
4643 END
4644
4645 /* Look for cursors */
4646 IF NOT EXISTS ( SELECT 1
4647 FROM #SkipChecks
4648 WHERE DatabaseName IS NULL AND CheckID = 66 )
4649 BEGIN
4650 INSERT INTO #BlitzResults
4651 ( CheckID ,
4652 Priority ,
4653 FindingsGroup ,
4654 Finding ,
4655 URL ,
4656 Details ,
4657 QueryPlan ,
4658 QueryPlanFiltered
4659 )
4660 SELECT 66 AS CheckID ,
4661 120 AS Priority ,
4662 'Query Plans' AS FindingsGroup ,
4663 'Cursor' AS Finding ,
4664 'http://BrentOzar.com/go/cursor' AS URL ,
4665 ( 'One of the top resource-intensive queries is using a cursor.' ) AS Details ,
4666 qs.query_plan ,
4667 qs.query_plan_filtered
4668 FROM #dm_exec_query_stats qs
4669 WHERE COALESCE(qs.query_plan_filtered,
4670 CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%<StmtCursor%'
4671 END
4672
4673 /* Look for scalar user-defined functions */
4674
4675 IF NOT EXISTS ( SELECT 1
4676 FROM #SkipChecks
4677 WHERE DatabaseName IS NULL AND CheckID = 67 )
4678 BEGIN
4679 INSERT INTO #BlitzResults
4680 ( CheckID ,
4681 Priority ,
4682 FindingsGroup ,
4683 Finding ,
4684 URL ,
4685 Details ,
4686 QueryPlan ,
4687 QueryPlanFiltered
4688 )
4689 SELECT 67 AS CheckID ,
4690 120 AS Priority ,
4691 'Query Plans' AS FindingsGroup ,
4692 'Scalar UDFs' AS Finding ,
4693 'http://BrentOzar.com/go/functions' AS URL ,
4694 ( 'One of the top resource-intensive queries is using a user-defined scalar function that may inhibit parallelism.' ) AS Details ,
4695 qs.query_plan ,
4696 qs.query_plan_filtered
4697 FROM #dm_exec_query_stats qs
4698 WHERE COALESCE(qs.query_plan_filtered,
4699 CAST(qs.query_plan AS NVARCHAR(MAX))) LIKE '%<UserDefinedFunction%'
4700 END
4701
4702 END /* IF @CheckProcedureCache = 1 */
4703
4704 /*Check for the last good DBCC CHECKDB date */
4705 IF NOT EXISTS ( SELECT 1
4706 FROM #SkipChecks
4707 WHERE DatabaseName IS NULL AND CheckID = 68 )
4708 BEGIN
4709 EXEC sp_MSforeachdb N'USE [?];
4710 INSERT #DBCCs
4711 (ParentObject,
4712 Object,
4713 Field,
4714 Value)
4715 EXEC (''DBCC DBInfo() With TableResults, NO_INFOMSGS'');
4716 UPDATE #DBCCs SET DbName = N''?'' WHERE DbName IS NULL;';
4717
4718 WITH DB2
4719 AS ( SELECT DISTINCT
4720 Field ,
4721 Value ,
4722 DbName
4723 FROM #DBCCs
4724 WHERE Field = 'dbi_dbccLastKnownGood'
4725 )
4726 INSERT INTO #BlitzResults
4727 ( CheckID ,
4728 DatabaseName ,
4729 Priority ,
4730 FindingsGroup ,
4731 Finding ,
4732 URL ,
4733 Details
4734 )
4735 SELECT 68 AS CheckID ,
4736 DB2.DbName AS DatabaseName ,
4737 1 AS PRIORITY ,
4738 'Reliability' AS FindingsGroup ,
4739 'Last good DBCC CHECKDB over 2 weeks old' AS Finding ,
4740 'http://BrentOzar.com/go/checkdb' AS URL ,
4741 'Database [' + DB2.DbName + ']'
4742 + CASE DB2.Value
4743 WHEN '1900-01-01 00:00:00.000'
4744 THEN ' never had a successful DBCC CHECKDB.'
4745 ELSE ' last had a successful DBCC CHECKDB run on '
4746 + DB2.Value + '.'
4747 END
4748 + ' This check should be run regularly to catch any database corruption as soon as possible.'
4749 + ' Note: you can restore a backup of a busy production database to a test server and run DBCC CHECKDB '
4750 + ' against that to minimize impact. If you do that, you can ignore this warning.' AS Details
4751 FROM DB2
4752 WHERE DB2.DbName <> 'tempdb'
4753 AND DB2.DbName NOT IN ( SELECT DISTINCT
4754 DatabaseName
4755 FROM
4756 #SkipChecks
4757 WHERE CheckID IS NULL)
4758 AND CONVERT(DATETIME, DB2.Value, 121) < DATEADD(DD,
4759 -14,
4760 CURRENT_TIMESTAMP)
4761 END
4762
4763
4764
4765
4766 /*Verify that the servername is set */
4767 IF NOT EXISTS ( SELECT 1
4768 FROM #SkipChecks
4769 WHERE DatabaseName IS NULL AND CheckID = 70 )
4770 BEGIN
4771 IF @@SERVERNAME IS NULL
4772 BEGIN
4773 INSERT INTO #BlitzResults
4774 ( CheckID ,
4775 Priority ,
4776 FindingsGroup ,
4777 Finding ,
4778 URL ,
4779 Details
4780 )
4781 SELECT 70 AS CheckID ,
4782 200 AS Priority ,
4783 'Informational' AS FindingsGroup ,
4784 '@@Servername Not Set' AS Finding ,
4785 'http://BrentOzar.com/go/servername' AS URL ,
4786 '@@Servername variable is null. You can fix it by executing: "sp_addserver ''<LocalServerName>'', local"' AS Details
4787 END;
4788
4789 IF /* @@SERVERNAME IS set */
4790 (@@SERVERNAME IS NOT NULL
4791 AND
4792 /* not a named instance */
4793 CHARINDEX('\',CAST(SERVERPROPERTY('ServerName') AS NVARCHAR)) = 0
4794 AND
4795 /* not clustered, when computername may be different than the servername */
4796 SERVERPROPERTY('IsClustered') = 0
4797 AND
4798 /* @@SERVERNAME is different than the computer name */
4799 @@SERVERNAME <> CAST(ISNULL(SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),@@SERVERNAME) AS NVARCHAR) )
4800 BEGIN
4801 INSERT INTO #BlitzResults
4802 ( CheckID ,
4803 Priority ,
4804 FindingsGroup ,
4805 Finding ,
4806 URL ,
4807 Details
4808 )
4809 SELECT 70 AS CheckID ,
4810 200 AS Priority ,
4811 'Configuration' AS FindingsGroup ,
4812 '@@Servername Not Correct' AS Finding ,
4813 'http://BrentOzar.com/go/servername' AS URL ,
4814 'The @@Servername is different than the computer name, which may trigger certificate errors.' AS Details
4815 END;
4816
4817 END
4818 /*Check to see if a failsafe operator has been configured*/
4819 IF NOT EXISTS ( SELECT 1
4820 FROM #SkipChecks
4821 WHERE DatabaseName IS NULL AND CheckID = 73 )
4822 BEGIN
4823
4824 DECLARE @AlertInfo TABLE
4825 (
4826 FailSafeOperator NVARCHAR(255) ,
4827 NotificationMethod INT ,
4828 ForwardingServer NVARCHAR(255) ,
4829 ForwardingSeverity INT ,
4830 PagerToTemplate NVARCHAR(255) ,
4831 PagerCCTemplate NVARCHAR(255) ,
4832 PagerSubjectTemplate NVARCHAR(255) ,
4833 PagerSendSubjectOnly NVARCHAR(255) ,
4834 ForwardAlways INT
4835 )
4836 INSERT INTO @AlertInfo
4837 EXEC [master].[dbo].[sp_MSgetalertinfo] @includeaddresses = 0
4838 INSERT INTO #BlitzResults
4839 ( CheckID ,
4840 Priority ,
4841 FindingsGroup ,
4842 Finding ,
4843 URL ,
4844 Details
4845 )
4846 SELECT 73 AS CheckID ,
4847 200 AS Priority ,
4848 'Monitoring' AS FindingsGroup ,
4849 'No failsafe operator configured' AS Finding ,
4850 'http://BrentOzar.com/go/failsafe' AS URL ,
4851 ( '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
4852 FROM @AlertInfo
4853 WHERE FailSafeOperator IS NULL;
4854 END
4855
4856/*Identify globally enabled trace flags*/
4857 IF NOT EXISTS ( SELECT 1
4858 FROM #SkipChecks
4859 WHERE DatabaseName IS NULL AND CheckID = 74 )
4860 BEGIN
4861 INSERT INTO #TraceStatus
4862 EXEC ( ' DBCC TRACESTATUS(-1) WITH NO_INFOMSGS'
4863 )
4864 INSERT INTO #BlitzResults
4865 ( CheckID ,
4866 Priority ,
4867 FindingsGroup ,
4868 Finding ,
4869 URL ,
4870 Details
4871 )
4872 SELECT 74 AS CheckID ,
4873 200 AS Priority ,
4874 'Informational' AS FindingsGroup ,
4875 'TraceFlag On' AS Finding ,
4876 'http://www.BrentOzar.com/go/traceflags/' AS URL ,
4877 'Trace flag ' +
4878 CASE WHEN [T].[TraceFlag] = '2330' THEN ' 2330 enabled globally. Using this trace Flag disables missing index requests'
4879 WHEN [T].[TraceFlag] = '1211' THEN ' 1211 enabled globally. Using this Trace Flag disables lock escalation when you least expect it. No Bueno!'
4880 WHEN [T].[TraceFlag] = '1224' THEN ' 1224 enabled globally. Using this Trace Flag disables lock escalation based on the number of locks being taken. You shouldn''t have done that, Dave.'
4881 WHEN [T].[TraceFlag] = '652' THEN ' 652 enabled globally. Using this Trace Flag disables pre-fetching during index scans. If you hate slow queries, you should turn that off.'
4882 WHEN [T].[TraceFlag] = '661' THEN ' 661 enabled globally. Using this Trace Flag disables ghost record removal. Who you gonna call? No one, turn that thing off.'
4883 WHEN [T].[TraceFlag] = '1806' THEN ' 1806 enabled globally. Using this Trace Flag disables instant file initialization. I question your sanity.'
4884 WHEN [T].[TraceFlag] = '3505' THEN ' 3505 enabled globally. Using this Trace Flag disables Checkpoints. Probably not the wisest idea.'
4885 WHEN [T].[TraceFlag] = '8649' THEN ' 8649 enabled globally. Using this Trace Flag drops cost thresholf for parallelism down to 0. I hope this is a dev server.'
4886 ELSE [T].[TraceFlag] + ' is enabled globally.' END
4887 AS Details
4888 FROM #TraceStatus T
4889 END
4890
4891 /*Check for transaction log file larger than data file */
4892 IF NOT EXISTS ( SELECT 1
4893 FROM #SkipChecks
4894 WHERE DatabaseName IS NULL AND CheckID = 75 )
4895 BEGIN
4896 INSERT INTO #BlitzResults
4897 ( CheckID ,
4898 DatabaseName ,
4899 Priority ,
4900 FindingsGroup ,
4901 Finding ,
4902 URL ,
4903 Details
4904 )
4905 SELECT 75 AS CheckID ,
4906 DB_NAME(a.database_id) ,
4907 50 AS Priority ,
4908 'Reliability' AS FindingsGroup ,
4909 'Transaction Log Larger than Data File' AS Finding ,
4910 'http://BrentOzar.com/go/biglog' AS URL ,
4911 'The database [' + DB_NAME(a.database_id)
4912 + '] has a ' + CAST((CAST(a.size AS BIGINT) * 8 / 1000000) AS NVARCHAR(20)) + ' GB transaction log file, larger than the total data file sizes. This may indicate that transaction log backups are not being performed or not performed often enough.' AS Details
4913 FROM sys.master_files a
4914 WHERE a.type = 1
4915 AND DB_NAME(a.database_id) NOT IN (
4916 SELECT DISTINCT
4917 DatabaseName
4918 FROM #SkipChecks )
4919 AND a.size > 125000 /* Size is measured in pages here, so this gets us log files over 1GB. */
4920 AND a.size > ( SELECT SUM(CAST(b.size AS BIGINT))
4921 FROM sys.master_files b
4922 WHERE a.database_id = b.database_id
4923 AND b.type = 0
4924 )
4925 AND a.database_id IN (
4926 SELECT database_id
4927 FROM sys.databases
4928 WHERE source_database_id IS NULL )
4929 END
4930
4931 /*Check for collation conflicts between user databases and tempdb */
4932 IF NOT EXISTS ( SELECT 1
4933 FROM #SkipChecks
4934 WHERE DatabaseName IS NULL AND CheckID = 76 )
4935 BEGIN
4936 INSERT INTO #BlitzResults
4937 ( CheckID ,
4938 DatabaseName ,
4939 Priority ,
4940 FindingsGroup ,
4941 Finding ,
4942 URL ,
4943 Details
4944 )
4945 SELECT 76 AS CheckID ,
4946 name AS DatabaseName ,
4947 200 AS Priority ,
4948 'Informational' AS FindingsGroup ,
4949 'Collation is ' + collation_name AS Finding ,
4950 'http://BrentOzar.com/go/collate' AS URL ,
4951 'Collation differences between user databases and tempdb can cause conflicts especially when comparing string values' AS Details
4952 FROM sys.databases
4953 WHERE name NOT IN ( 'master', 'model', 'msdb')
4954 AND name NOT LIKE 'ReportServer%'
4955 AND name NOT IN ( SELECT DISTINCT
4956 DatabaseName
4957 FROM #SkipChecks
4958 WHERE CheckID IS NULL)
4959 AND collation_name <> ( SELECT
4960 collation_name
4961 FROM
4962 sys.databases
4963 WHERE
4964 name = 'tempdb'
4965 )
4966 END
4967
4968 IF NOT EXISTS ( SELECT 1
4969 FROM #SkipChecks
4970 WHERE DatabaseName IS NULL AND CheckID = 77 )
4971 BEGIN
4972 INSERT INTO #BlitzResults
4973 ( CheckID ,
4974 DatabaseName ,
4975 Priority ,
4976 FindingsGroup ,
4977 Finding ,
4978 URL ,
4979 Details
4980 )
4981 SELECT 77 AS CheckID ,
4982 dSnap.[name] AS DatabaseName ,
4983 50 AS Priority ,
4984 'Reliability' AS FindingsGroup ,
4985 'Database Snapshot Online' AS Finding ,
4986 'http://BrentOzar.com/go/snapshot' AS URL ,
4987 'Database [' + dSnap.[name]
4988 + '] is a snapshot of ['
4989 + dOriginal.[name]
4990 + ']. Make sure you have enough drive space to maintain the snapshot as the original database grows.' AS Details
4991 FROM sys.databases dSnap
4992 INNER JOIN sys.databases dOriginal ON dSnap.source_database_id = dOriginal.database_id
4993 AND dSnap.name NOT IN (
4994 SELECT DISTINCT
4995 DatabaseName
4996 FROM
4997 #SkipChecks )
4998 END
4999
5000 IF NOT EXISTS ( SELECT 1
5001 FROM #SkipChecks
5002 WHERE DatabaseName IS NULL AND CheckID = 79 )
5003 BEGIN
5004 INSERT INTO #BlitzResults
5005 ( CheckID ,
5006 Priority ,
5007 FindingsGroup ,
5008 Finding ,
5009 URL ,
5010 Details
5011 )
5012 SELECT 79 AS CheckID ,
5013 100 AS Priority ,
5014 'Performance' AS FindingsGroup ,
5015 'Shrink Database Job' AS Finding ,
5016 'http://BrentOzar.com/go/autoshrink' AS URL ,
5017 'In the [' + j.[name] + '] job, step ['
5018 + step.[step_name]
5019 + '] has SHRINKDATABASE or SHRINKFILE, which may be causing database fragmentation.' AS Details
5020 FROM msdb.dbo.sysjobs j
5021 INNER JOIN msdb.dbo.sysjobsteps step ON j.job_id = step.job_id
5022 WHERE step.command LIKE N'%SHRINKDATABASE%'
5023 OR step.command LIKE N'%SHRINKFILE%'
5024 END
5025
5026 IF NOT EXISTS ( SELECT 1
5027 FROM #SkipChecks
5028 WHERE DatabaseName IS NULL AND CheckID = 81 )
5029 BEGIN
5030 INSERT INTO #BlitzResults
5031 ( CheckID ,
5032 Priority ,
5033 FindingsGroup ,
5034 Finding ,
5035 URL ,
5036 Details
5037 )
5038 SELECT 81 AS CheckID ,
5039 200 AS Priority ,
5040 'Non-Active Server Config' AS FindingsGroup ,
5041 cr.name AS Finding ,
5042 'http://www.BrentOzar.com/blitz/sp_configure/' AS URL ,
5043 ( 'This sp_configure option isn''t running under its set value. Its set value is '
5044 + CAST(cr.[value] AS VARCHAR(100))
5045 + ' and its running value is '
5046 + CAST(cr.value_in_use AS VARCHAR(100))
5047 + '. When someone does a RECONFIGURE or restarts the instance, this setting will start taking effect.' ) AS Details
5048 FROM sys.configurations cr
5049 WHERE cr.value <> cr.value_in_use
5050 AND NOT (cr.name = 'min server memory (MB)' AND cr.value IN (0,16) AND cr.value_in_use IN (0,16));
5051 END
5052
5053 IF NOT EXISTS ( SELECT 1
5054 FROM #SkipChecks
5055 WHERE DatabaseName IS NULL AND CheckID = 123 )
5056 BEGIN
5057 INSERT INTO #BlitzResults
5058 ( CheckID ,
5059 Priority ,
5060 FindingsGroup ,
5061 Finding ,
5062 URL ,
5063 Details
5064 )
5065 SELECT TOP 1 123 AS CheckID ,
5066 200 AS Priority ,
5067 'Informational' AS FindingsGroup ,
5068 'Agent Jobs Starting Simultaneously' AS Finding ,
5069 'http://BrentOzar.com/go/busyagent/' AS URL ,
5070 ( 'Multiple SQL Server Agent jobs are configured to start simultaneously. For detailed schedule listings, see the query in the URL.' ) AS Details
5071 FROM msdb.dbo.sysjobactivity
5072 WHERE start_execution_date > DATEADD(dd, -14, GETDATE())
5073 GROUP BY start_execution_date HAVING COUNT(*) > 1;
5074 END
5075
5076
5077 IF @CheckServerInfo = 1
5078 BEGIN
5079
5080/*This checks Windows version. It would be better if Microsoft gave everything a separate build number, but whatever.*/
5081IF @ProductVersionMajor >= 10 AND @ProductVersionMinor >= 50
5082 AND NOT EXISTS ( SELECT 1
5083 FROM #SkipChecks
5084 WHERE DatabaseName IS NULL AND CheckID = 172 )
5085 BEGIN
5086 IF EXISTS ( SELECT 1
5087 FROM sys.all_objects
5088 WHERE name = 'dm_os_windows_info' )
5089
5090 BEGIN
5091 INSERT INTO [#BlitzResults]
5092 ( [CheckID] ,
5093 [Priority] ,
5094 [FindingsGroup] ,
5095 [Finding] ,
5096 [URL] ,
5097 [Details] )
5098
5099 SELECT
5100 172 AS [CheckID] ,
5101 250 AS [Priority] ,
5102 'Server Info' AS [FindingsGroup] ,
5103 'Windows Version' AS [Finding] ,
5104 'http://BrentOzar.com/go/' AS [URL] ,
5105 ( CASE
5106 WHEN [owi].[windows_release] = '5' THEN 'You''re running a really old version: Windows 2000, version ' + CAST([owi].[windows_release] AS VARCHAR(5))
5107 WHEN [owi].[windows_release] > '5' AND [owi].[windows_release] < '6' THEN 'You''re running a really old version: Windows Server 2003/2003R2 era, version ' + CAST([owi].[windows_release] AS VARCHAR(5))
5108 WHEN [owi].[windows_release] >= '6' AND [owi].[windows_release] <= '6.1' THEN 'You''re running a pretty old version: Windows: Server 2008/2008R2 era, version ' + CAST([owi].[windows_release] AS VARCHAR(5))
5109 WHEN [owi].[windows_release] = '6.2' THEN 'You''re running a rather modern version of Windows: Server 2012 era, version ' + CAST([owi].[windows_release] AS VARCHAR(5))
5110 WHEN [owi].[windows_release] = '6.3' THEN 'You''re running a pretty modern version of Windows: Server 2012R2 era, version ' + CAST([owi].[windows_release] AS VARCHAR(5))
5111 WHEN [owi].[windows_release] > '6.3' THEN 'Hot dog! You''re living in the future! You''re running version ' + CAST([owi].[windows_release] AS VARCHAR(5))
5112 ELSE 'I have no idea which version of Windows you''re on. Sorry.'
5113 END
5114 ) AS [Details]
5115 FROM [sys].[dm_os_windows_info] [owi]
5116
5117 END;
5118 END;
5119
5120/*
5121This check hits the dm_os_process_memory system view
5122to see if locked_page_allocations_kb is > 0,
5123which could indicate that locked pages in memory is enabled.
5124*/
5125IF @ProductVersionMajor >= 10 AND NOT EXISTS ( SELECT 1
5126 FROM #SkipChecks
5127 WHERE DatabaseName IS NULL AND CheckID = 166 )
5128 BEGIN
5129 INSERT INTO [#BlitzResults]
5130 ( [CheckID] ,
5131 [Priority] ,
5132 [FindingsGroup] ,
5133 [Finding] ,
5134 [URL] ,
5135 [Details] )
5136 SELECT
5137 166 AS [CheckID] ,
5138 250 AS [Priority] ,
5139 'Server Info' AS [FindingsGroup] ,
5140 'Locked Pages In Memory Enabled' AS [Finding] ,
5141 'http://BrentOzar.com/go/lpim' AS [URL] ,
5142 ( 'You currently have '
5143 + CASE WHEN [dopm].[locked_page_allocations_kb] / 1024. / 1024. > 0
5144 THEN CAST([dopm].[locked_page_allocations_kb] / 1024. / 1024. AS VARCHAR(100))
5145 + ' GB'
5146 ELSE CAST([dopm].[locked_page_allocations_kb] / 1024. AS VARCHAR(100))
5147 + ' MB'
5148 END + ' of pages locked in memory.' ) AS [Details]
5149 FROM
5150 [sys].[dm_os_process_memory] AS [dopm]
5151 WHERE
5152 [dopm].[locked_page_allocations_kb] > 0;
5153 END;
5154
5155
5156 IF NOT EXISTS ( SELECT 1
5157 FROM #SkipChecks
5158 WHERE DatabaseName IS NULL AND CheckID = 130 )
5159 BEGIN
5160 INSERT INTO #BlitzResults
5161 ( CheckID ,
5162 Priority ,
5163 FindingsGroup ,
5164 Finding ,
5165 URL ,
5166 Details
5167 )
5168 SELECT 130 AS CheckID ,
5169 250 AS Priority ,
5170 'Server Info' AS FindingsGroup ,
5171 'Server Name' AS Finding ,
5172 'http://BrentOzar.com/go/servername' AS URL ,
5173 @@SERVERNAME AS Details
5174 WHERE @@SERVERNAME IS NOT NULL;
5175 END;
5176
5177
5178
5179 IF NOT EXISTS ( SELECT 1
5180 FROM #SkipChecks
5181 WHERE DatabaseName IS NULL AND CheckID = 83 )
5182 BEGIN
5183 IF EXISTS ( SELECT *
5184 FROM sys.all_objects
5185 WHERE name = 'dm_server_services' )
5186 BEGIN
5187 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
5188 SELECT 83 AS CheckID ,
5189 250 AS Priority ,
5190 ''Server Info'' AS FindingsGroup ,
5191 ''Services'' AS Finding ,
5192 '''' AS URL ,
5193 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 + ''.''
5194 FROM sys.dm_server_services;'
5195 EXECUTE(@StringToExecute);
5196 END
5197 END
5198
5199 /* Check 84 - SQL Server 2012 */
5200 IF NOT EXISTS ( SELECT 1
5201 FROM #SkipChecks
5202 WHERE DatabaseName IS NULL AND CheckID = 84 )
5203 BEGIN
5204 IF EXISTS ( SELECT *
5205 FROM sys.all_objects o
5206 INNER JOIN sys.all_columns c ON o.object_id = c.object_id
5207 WHERE o.name = 'dm_os_sys_info'
5208 AND c.name = 'physical_memory_kb' )
5209 BEGIN
5210 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
5211 SELECT 84 AS CheckID ,
5212 250 AS Priority ,
5213 ''Server Info'' AS FindingsGroup ,
5214 ''Hardware'' AS Finding ,
5215 '''' AS URL ,
5216 ''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.''
5217 FROM sys.dm_os_sys_info';
5218 EXECUTE(@StringToExecute);
5219 END
5220
5221 /* Check 84 - SQL Server 2008 */
5222 IF EXISTS ( SELECT *
5223 FROM sys.all_objects o
5224 INNER JOIN sys.all_columns c ON o.object_id = c.object_id
5225 WHERE o.name = 'dm_os_sys_info'
5226 AND c.name = 'physical_memory_in_bytes' )
5227 BEGIN
5228 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
5229 SELECT 84 AS CheckID ,
5230 250 AS Priority ,
5231 ''Server Info'' AS FindingsGroup ,
5232 ''Hardware'' AS Finding ,
5233 '''' AS URL ,
5234 ''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.''
5235 FROM sys.dm_os_sys_info';
5236 EXECUTE(@StringToExecute);
5237 END
5238 END
5239
5240
5241 IF NOT EXISTS ( SELECT 1
5242 FROM #SkipChecks
5243 WHERE DatabaseName IS NULL AND CheckID = 85 )
5244 BEGIN
5245 INSERT INTO #BlitzResults
5246 ( CheckID ,
5247 Priority ,
5248 FindingsGroup ,
5249 Finding ,
5250 URL ,
5251 Details
5252 )
5253 SELECT 85 AS CheckID ,
5254 250 AS Priority ,
5255 'Server Info' AS FindingsGroup ,
5256 'SQL Server Service' AS Finding ,
5257 '' AS URL ,
5258 N'Version: '
5259 + CAST(SERVERPROPERTY('productversion') AS NVARCHAR(100))
5260 + N'. Patch Level: '
5261 + CAST(SERVERPROPERTY('productlevel') AS NVARCHAR(100))
5262 + N'. Edition: '
5263 + CAST(SERVERPROPERTY('edition') AS VARCHAR(100))
5264 + N'. AlwaysOn Enabled: '
5265 + CAST(COALESCE(SERVERPROPERTY('IsHadrEnabled'),
5266 0) AS VARCHAR(100))
5267 + N'. AlwaysOn Mgr Status: '
5268 + CAST(COALESCE(SERVERPROPERTY('HadrManagerStatus'),
5269 0) AS VARCHAR(100))
5270 END
5271
5272
5273 IF NOT EXISTS ( SELECT 1
5274 FROM #SkipChecks
5275 WHERE DatabaseName IS NULL AND CheckID = 88 )
5276 BEGIN
5277 INSERT INTO #BlitzResults
5278 ( CheckID ,
5279 Priority ,
5280 FindingsGroup ,
5281 Finding ,
5282 URL ,
5283 Details
5284 )
5285 SELECT 88 AS CheckID ,
5286 250 AS Priority ,
5287 'Server Info' AS FindingsGroup ,
5288 'SQL Server Last Restart' AS Finding ,
5289 '' AS URL ,
5290 CAST(create_date AS VARCHAR(100))
5291 FROM sys.databases
5292 WHERE database_id = 2
5293 END
5294
5295 IF NOT EXISTS ( SELECT 1
5296 FROM #SkipChecks
5297 WHERE DatabaseName IS NULL AND CheckID = 92 )
5298 BEGIN
5299 INSERT INTO #driveInfo
5300 ( drive, SIZE )
5301 EXEC master..xp_fixeddrives
5302
5303 INSERT INTO #BlitzResults
5304 ( CheckID ,
5305 Priority ,
5306 FindingsGroup ,
5307 Finding ,
5308 URL ,
5309 Details
5310 )
5311 SELECT 92 AS CheckID ,
5312 250 AS Priority ,
5313 'Server Info' AS FindingsGroup ,
5314 'Drive ' + i.drive + ' Space' AS Finding ,
5315 '' AS URL ,
5316 CAST(i.SIZE AS VARCHAR)
5317 + 'MB free on ' + i.drive
5318 + ' drive' AS Details
5319 FROM #driveInfo AS i
5320 DROP TABLE #driveInfo
5321 END
5322
5323
5324 IF NOT EXISTS ( SELECT 1
5325 FROM #SkipChecks
5326 WHERE DatabaseName IS NULL AND CheckID = 103 )
5327 AND EXISTS ( SELECT *
5328 FROM sys.all_objects o
5329 INNER JOIN sys.all_columns c ON o.object_id = c.object_id
5330 WHERE o.name = 'dm_os_sys_info'
5331 AND c.name = 'virtual_machine_type_desc' )
5332 BEGIN
5333 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
5334 SELECT 103 AS CheckID,
5335 250 AS Priority,
5336 ''Server Info'' AS FindingsGroup,
5337 ''Virtual Server'' AS Finding,
5338 ''http://BrentOzar.com/go/virtual'' AS URL,
5339 ''Type: ('' + virtual_machine_type_desc + '')'' AS Details
5340 FROM sys.dm_os_sys_info
5341 WHERE virtual_machine_type <> 0';
5342 EXECUTE(@StringToExecute);
5343 END
5344
5345 IF NOT EXISTS ( SELECT 1
5346 FROM #SkipChecks
5347 WHERE DatabaseName IS NULL AND CheckID = 114 )
5348 AND EXISTS ( SELECT *
5349 FROM sys.all_objects o
5350 WHERE o.name = 'dm_os_memory_nodes' )
5351 AND EXISTS ( SELECT *
5352 FROM sys.all_objects o
5353 INNER JOIN sys.all_columns c ON o.object_id = c.object_id
5354 WHERE o.name = 'dm_os_nodes'
5355 AND c.name = 'processor_group' )
5356 BEGIN
5357 SET @StringToExecute = 'INSERT INTO #BlitzResults (CheckID, Priority, FindingsGroup, Finding, URL, Details)
5358 SELECT 114 AS CheckID ,
5359 250 AS Priority ,
5360 ''Server Info'' AS FindingsGroup ,
5361 ''Hardware - NUMA Config'' AS Finding ,
5362 '''' AS URL ,
5363 ''Node: '' + CAST(n.node_id AS NVARCHAR(10)) + '' State: '' + node_state_desc
5364 + '' Online schedulers: '' + CAST(n.online_scheduler_count AS NVARCHAR(10)) + '' Processor Group: '' + CAST(n.processor_group AS NVARCHAR(10))
5365 + '' Memory node: '' + CAST(n.memory_node_id AS NVARCHAR(10)) + '' Memory VAS Reserved GB: '' + CAST(CAST((m.virtual_address_space_reserved_kb / 1024.0 / 1024) AS INT) AS NVARCHAR(100))
5366 FROM sys.dm_os_nodes n
5367 INNER JOIN sys.dm_os_memory_nodes m ON n.memory_node_id = m.memory_node_id
5368 WHERE n.node_state_desc NOT LIKE ''%DAC%''
5369 ORDER BY n.node_id'
5370 EXECUTE(@StringToExecute);
5371 END
5372
5373
5374 IF NOT EXISTS ( SELECT 1
5375 FROM #SkipChecks
5376 WHERE DatabaseName IS NULL AND CheckID = 106 )
5377 AND (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1
5378 AND DATALENGTH( COALESCE( @base_tracefilename, '' ) ) > DATALENGTH('.TRC')
5379 BEGIN
5380
5381 INSERT INTO #BlitzResults
5382 ( CheckID ,
5383 Priority ,
5384 FindingsGroup ,
5385 Finding ,
5386 URL ,
5387 Details
5388 )
5389 SELECT
5390 106 AS CheckID
5391 ,250 AS Priority
5392 ,'Server Info' AS FindingsGroup
5393 ,'Default Trace Contents' AS Finding
5394 ,'http://BrentOzar.com/go/trace' AS URL
5395 ,'The default trace holds '+cast(DATEDIFF(hour,MIN(StartTime),GETDATE())as varchar)+' hours of data'
5396 +' between '+cast(Min(StartTime) as varchar)+' and '+cast(GETDATE()as varchar)
5397 +('. The default trace files are located in: '+left( @curr_tracefilename,len(@curr_tracefilename) - @indx)
5398 ) as Details
5399 FROM ::fn_trace_gettable( @base_tracefilename, default )
5400 WHERE EventClass BETWEEN 65500 and 65600
5401 END /* CheckID 106 */
5402
5403
5404 IF NOT EXISTS ( SELECT 1
5405 FROM #SkipChecks
5406 WHERE DatabaseName IS NULL AND CheckID = 152 )
5407 BEGIN
5408 IF EXISTS (SELECT * FROM sys.dm_os_wait_stats WHERE wait_time_ms > .1 * @CPUMSsinceStartup AND waiting_tasks_count > 0
5409 AND wait_type NOT IN ('REQUEST_FOR_DEADLOCK_SEARCH',
5410 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
5411 'SQLTRACE_BUFFER_FLUSH',
5412 'LAZYWRITER_SLEEP',
5413 'XE_TIMER_EVENT',
5414 'XE_DISPATCHER_WAIT',
5415 'FT_IFTS_SCHEDULER_IDLE_WAIT',
5416 'LOGMGR_QUEUE',
5417 'CHECKPOINT_QUEUE',
5418 'BROKER_TO_FLUSH',
5419 'BROKER_TASK_STOP',
5420 'BROKER_EVENTHANDLER',
5421 'SLEEP_TASK',
5422 'WAITFOR',
5423 'DBMIRROR_DBM_MUTEX',
5424 'DBMIRROR_EVENTS_QUEUE',
5425 'DBMIRRORING_CMD',
5426 'DISPATCHER_QUEUE_SEMAPHORE',
5427 'BROKER_RECEIVE_WAITFOR',
5428 'CLR_AUTO_EVENT',
5429 'DIRTY_PAGE_POLL',
5430 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
5431 'ONDEMAND_TASK_QUEUE',
5432 'FT_IFTSHC_MUTEX',
5433 'CLR_MANUAL_EVENT',
5434 'CLR_SEMAPHORE',
5435 'DBMIRROR_WORKER_QUEUE',
5436 'DBMIRROR_DBM_EVENT',
5437 'SP_SERVER_DIAGNOSTICS_SLEEP',
5438 'HADR_CLUSAPI_CALL',
5439 'HADR_LOGCAPTURE_WAIT',
5440 'HADR_NOTIFICATION_DEQUEUE',
5441 'HADR_TIMER_TASK',
5442 'HADR_WORK_QUEUE',
5443 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
5444 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
5445 'REDO_THREAD_PENDING_WORK',
5446 'UCS_SESSION_REGISTRATION',
5447 'BROKER_TRANSMITTER',
5448 'QDS_ASYNC_QUEUE'))
5449 BEGIN
5450 /* Check for waits that have had more than 10% of the server's wait time */
5451 WITH os(wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms)
5452 AS
5453 (SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
5454 FROM sys.dm_os_wait_stats
5455 WHERE wait_type NOT IN ('REQUEST_FOR_DEADLOCK_SEARCH',
5456 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
5457 'SQLTRACE_BUFFER_FLUSH',
5458 'LAZYWRITER_SLEEP',
5459 'XE_TIMER_EVENT',
5460 'XE_DISPATCHER_WAIT',
5461 'FT_IFTS_SCHEDULER_IDLE_WAIT',
5462 'LOGMGR_QUEUE',
5463 'CHECKPOINT_QUEUE',
5464 'BROKER_TO_FLUSH',
5465 'BROKER_TASK_STOP',
5466 'BROKER_EVENTHANDLER',
5467 'SLEEP_TASK',
5468 'WAITFOR',
5469 'DBMIRROR_DBM_MUTEX',
5470 'DBMIRROR_EVENTS_QUEUE',
5471 'DBMIRRORING_CMD',
5472 'DISPATCHER_QUEUE_SEMAPHORE',
5473 'BROKER_RECEIVE_WAITFOR',
5474 'CLR_AUTO_EVENT',
5475 'DIRTY_PAGE_POLL',
5476 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
5477 'ONDEMAND_TASK_QUEUE',
5478 'FT_IFTSHC_MUTEX',
5479 'CLR_MANUAL_EVENT',
5480 'CLR_SEMAPHORE',
5481 'DBMIRROR_WORKER_QUEUE',
5482 'DBMIRROR_DBM_EVENT',
5483 'SP_SERVER_DIAGNOSTICS_SLEEP',
5484 'HADR_CLUSAPI_CALL',
5485 'HADR_LOGCAPTURE_WAIT',
5486 'HADR_NOTIFICATION_DEQUEUE',
5487 'HADR_TIMER_TASK',
5488 'HADR_WORK_QUEUE',
5489 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
5490 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
5491 'REDO_THREAD_PENDING_WORK',
5492 'UCS_SESSION_REGISTRATION',
5493 'BROKER_TRANSMITTER',
5494 'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
5495 'PREEMPTIVE_HADR_LEASE_MECHANISM',
5496 'SLEEP_SYSTEMTASK',
5497 'QDS_SHUTDOWN_QUEUE',
5498 'XE_LIVE_TARGET_TVF')
5499 AND wait_time_ms > .1 * @CPUMSsinceStartup
5500 AND waiting_tasks_count > 0)
5501 INSERT INTO #BlitzResults
5502 ( CheckID ,
5503 Priority ,
5504 FindingsGroup ,
5505 Finding ,
5506 URL ,
5507 Details
5508 )
5509 SELECT TOP 9
5510 152 AS CheckID
5511 ,240 AS Priority
5512 ,'Wait Stats' AS FindingsGroup
5513 , CAST(ROW_NUMBER() OVER(ORDER BY os.wait_time_ms DESC) AS NVARCHAR(10)) + N' - ' + os.wait_type AS Finding
5514 ,'http://BrentOzar.com/go/waits' AS URL
5515 , Details = CAST(CAST(SUM(os.wait_time_ms / 1000.0 / 60 / 60) OVER (PARTITION BY os.wait_type) AS NUMERIC(18,1)) AS NVARCHAR(20)) + N' hours of waits, ' +
5516 CAST(CAST((SUM(60.0 * os.wait_time_ms) OVER (PARTITION BY os.wait_type) ) / @MSSinceStartup AS NUMERIC(18,1)) AS NVARCHAR(20)) + N' minutes average wait time per hour, ' +
5517 /* CAST(CAST(
5518 100.* SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type)
5519 / (1. * SUM(os.wait_time_ms) OVER () )
5520 AS NUMERIC(18,1)) AS NVARCHAR(40)) + N'% of waits, ' + */
5521 CAST(CAST(
5522 100. * SUM(os.signal_wait_time_ms) OVER (PARTITION BY os.wait_type)
5523 / (1. * SUM(os.wait_time_ms) OVER ())
5524 AS NUMERIC(18,1)) AS NVARCHAR(40)) + N'% signal wait, ' +
5525 CAST(SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS NVARCHAR(40)) + N' waiting tasks, ' +
5526 CAST(CASE WHEN SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) > 0
5527 THEN
5528 CAST(
5529 SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type)
5530 / (1. * SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type))
5531 AS NUMERIC(18,1))
5532 ELSE 0 END AS NVARCHAR(40)) + N' ms average wait time.'
5533 FROM os
5534 ORDER BY SUM(os.wait_time_ms / 1000.0 / 60 / 60) OVER (PARTITION BY os.wait_type) DESC;
5535 END /* IF EXISTS (SELECT * FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0 AND waiting_tasks_count > 0) */
5536
5537 /* If no waits were found, add a note about that */
5538 IF NOT EXISTS (SELECT * FROM #BlitzResults WHERE CheckID IN (107, 108, 109, 121, 152, 162))
5539 BEGIN
5540 INSERT INTO #BlitzResults
5541 ( CheckID ,
5542 Priority ,
5543 FindingsGroup ,
5544 Finding ,
5545 URL ,
5546 Details
5547 )
5548 VALUES (153, 240, 'Wait Stats', 'No Significant Waits Detected', 'http://BrentOzar.com/go/waits', 'This server might be just sitting around idle, or someone may have cleared wait stats recently.');
5549 END
5550 END /* CheckID 152 */
5551
5552 END /* IF @CheckServerInfo = 1 */
5553 END /* IF ( ( SERVERPROPERTY('ServerName') NOT IN ( SELECT ServerName */
5554
5555
5556 /* Delete priorites they wanted to skip. */
5557 IF @IgnorePrioritiesAbove IS NOT NULL
5558 DELETE #BlitzResults
5559 WHERE [Priority] > @IgnorePrioritiesAbove AND CheckID <> -1;
5560
5561 IF @IgnorePrioritiesBelow IS NOT NULL
5562 DELETE #BlitzResults
5563 WHERE [Priority] < @IgnorePrioritiesBelow AND CheckID <> -1;
5564
5565 /* Delete checks they wanted to skip. */
5566 IF @SkipChecksTable IS NOT NULL
5567 BEGIN
5568 DELETE FROM #BlitzResults
5569 WHERE DatabaseName IN ( SELECT DatabaseName
5570 FROM #SkipChecks
5571 WHERE CheckID IS NULL
5572 AND (ServerName IS NULL OR ServerName = SERVERPROPERTY('ServerName')));
5573 DELETE FROM #BlitzResults
5574 WHERE CheckID IN ( SELECT CheckID
5575 FROM #SkipChecks
5576 WHERE DatabaseName IS NULL
5577 AND (ServerName IS NULL OR ServerName = SERVERPROPERTY('ServerName')));
5578 DELETE r FROM #BlitzResults r
5579 INNER JOIN #SkipChecks c ON r.DatabaseName = c.DatabaseName and r.CheckID = c.CheckID
5580 AND (ServerName IS NULL OR ServerName = SERVERPROPERTY('ServerName'));
5581 END
5582
5583 /* Add summary mode */
5584 IF @SummaryMode > 0
5585 BEGIN
5586 UPDATE #BlitzResults
5587 SET Finding = br.Finding + ' (' + CAST(brTotals.recs AS NVARCHAR(20)) + ')'
5588 FROM #BlitzResults br
5589 INNER JOIN (SELECT FindingsGroup, Finding, Priority, COUNT(*) AS recs FROM #BlitzResults GROUP BY FindingsGroup, Finding, Priority) brTotals ON br.FindingsGroup = brTotals.FindingsGroup AND br.Finding = brTotals.Finding AND br.Priority = brTotals.Priority
5590 WHERE brTotals.recs > 1;
5591
5592 DELETE br
5593 FROM #BlitzResults br
5594 WHERE EXISTS (SELECT * FROM #BlitzResults brLower WHERE br.FindingsGroup = brLower.FindingsGroup AND br.Finding = brLower.Finding AND br.Priority = brLower.Priority AND br.ID > brLower.ID);
5595
5596 END
5597
5598 /* Add credits for the nice folks who put so much time into building and maintaining this for free: */
5599 INSERT INTO #BlitzResults
5600 ( CheckID ,
5601 Priority ,
5602 FindingsGroup ,
5603 Finding ,
5604 URL ,
5605 Details
5606 )
5607 VALUES ( -1 ,
5608 255 ,
5609 'Thanks!' ,
5610 'From Your Community Volunteers' ,
5611 'http://FirstResponderKit.org' ,
5612 'We hope you found this tool useful.'
5613 );
5614
5615 INSERT INTO #BlitzResults
5616 ( CheckID ,
5617 Priority ,
5618 FindingsGroup ,
5619 Finding ,
5620 URL ,
5621 Details
5622
5623 )
5624 VALUES ( -1 ,
5625 0 ,
5626 'sp_Blitz ' + CAST(CONVERT(DATETIME, @VersionDate, 102) AS VARCHAR(100)),
5627 'SQL Server First Responder Kit' ,
5628 'http://FirstResponderKit.org/' ,
5629 'To get help or add your own contributions, join us at http://FirstResponderKit.org.'
5630
5631 );
5632
5633 INSERT INTO #BlitzResults
5634 ( CheckID ,
5635 Priority ,
5636 FindingsGroup ,
5637 Finding ,
5638 URL ,
5639 Details
5640
5641 )
5642 SELECT 156 ,
5643 254 ,
5644 'Rundate' ,
5645 GETDATE() ,
5646 'http://FirstResponderKit.org/' ,
5647 'Captain''s log: stardate something and something...';
5648
5649 IF @EmailRecipients IS NOT NULL
5650 BEGIN
5651 /* Database mail won't work off a local temp table. I'm not happy about this hacky workaround either. */
5652 IF (OBJECT_ID('tempdb..##BlitzResults', 'U') IS NOT NULL) DROP TABLE ##BlitzResults;
5653 SELECT * INTO ##BlitzResults FROM #BlitzResults;
5654 SET @query_result_separator = char(9);
5655 SET @StringToExecute = 'SET NOCOUNT ON;SELECT [Priority] , [FindingsGroup] , [Finding] , [DatabaseName] , [URL] , [Details] , CheckID FROM ##BlitzResults ORDER BY Priority , FindingsGroup, Finding, Details; SET NOCOUNT OFF;';
5656 SET @EmailSubject = 'sp_Blitz Results for ' + @@SERVERNAME;
5657 SET @EmailBody = 'sp_Blitz ' + CAST(CONVERT(DATETIME, @VersionDate, 102) AS VARCHAR(100)) + '. http://FirstResponderKit.org';
5658 IF @EmailProfile IS NULL
5659 EXEC msdb.dbo.sp_send_dbmail
5660 @recipients = @EmailRecipients,
5661 @subject = @EmailSubject,
5662 @body = @EmailBody,
5663 @query_attachment_filename = 'sp_Blitz-Results.csv',
5664 @attach_query_result_as_file = 1,
5665 @query_result_header = 1,
5666 @query_result_width = 32767,
5667 @append_query_error = 1,
5668 @query_result_no_padding = 1,
5669 @query_result_separator = @query_result_separator,
5670 @query = @StringToExecute;
5671 ELSE
5672 EXEC msdb.dbo.sp_send_dbmail
5673 @profile_name = @EmailProfile,
5674 @recipients = @EmailRecipients,
5675 @subject = @EmailSubject,
5676 @body = @EmailBody,
5677 @query_attachment_filename = 'sp_Blitz-Results.csv',
5678 @attach_query_result_as_file = 1,
5679 @query_result_header = 1,
5680 @query_result_width = 32767,
5681 @append_query_error = 1,
5682 @query_result_no_padding = 1,
5683 @query_result_separator = @query_result_separator,
5684 @query = @StringToExecute;
5685 IF (OBJECT_ID('tempdb..##BlitzResults', 'U') IS NOT NULL) DROP TABLE ##BlitzResults;
5686 END
5687
5688
5689 /* @OutputTableName lets us export the results to a permanent table */
5690 IF @OutputDatabaseName IS NOT NULL
5691 AND @OutputSchemaName IS NOT NULL
5692 AND @OutputTableName IS NOT NULL
5693 AND EXISTS ( SELECT *
5694 FROM sys.databases
5695 WHERE QUOTENAME([name]) = @OutputDatabaseName)
5696 BEGIN
5697 SET @StringToExecute = 'USE '
5698 + @OutputDatabaseName
5699 + '; IF EXISTS(SELECT * FROM '
5700 + @OutputDatabaseName
5701 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
5702 + @OutputSchemaName
5703 + ''') AND NOT EXISTS (SELECT * FROM '
5704 + @OutputDatabaseName
5705 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
5706 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
5707 + @OutputTableName + ''') CREATE TABLE '
5708 + @OutputSchemaName + '.'
5709 + @OutputTableName
5710 + ' (ID INT IDENTITY(1,1) NOT NULL,
5711 ServerName NVARCHAR(128),
5712 CheckDate DATETIMEOFFSET,
5713 Priority TINYINT ,
5714 FindingsGroup VARCHAR(50) ,
5715 Finding VARCHAR(200) ,
5716 DatabaseName NVARCHAR(128),
5717 URL VARCHAR(200) ,
5718 Details NVARCHAR(4000) ,
5719 QueryPlan [XML] NULL ,
5720 QueryPlanFiltered [NVARCHAR](MAX) NULL,
5721 CheckID INT ,
5722 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
5723 EXEC(@StringToExecute);
5724 SET @StringToExecute = N' IF EXISTS(SELECT * FROM '
5725 + @OutputDatabaseName
5726 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
5727 + @OutputSchemaName + ''') INSERT '
5728 + @OutputDatabaseName + '.'
5729 + @OutputSchemaName + '.'
5730 + @OutputTableName
5731 + ' (ServerName, CheckDate, CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details, QueryPlan, QueryPlanFiltered) SELECT '''
5732 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
5733 + ''', SYSDATETIMEOFFSET(), CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details, QueryPlan, QueryPlanFiltered FROM #BlitzResults ORDER BY Priority , FindingsGroup , Finding , Details';
5734 EXEC(@StringToExecute);
5735 END
5736 ELSE IF (SUBSTRING(@OutputTableName, 2, 2) = '##')
5737 BEGIN
5738 SET @StringToExecute = N' IF (OBJECT_ID(''tempdb..'
5739 + @OutputTableName
5740 + ''') IS NOT NULL) DROP TABLE ' + @OutputTableName + ';'
5741 + 'CREATE TABLE '
5742 + @OutputTableName
5743 + ' (ID INT IDENTITY(1,1) NOT NULL,
5744 ServerName NVARCHAR(128),
5745 CheckDate DATETIMEOFFSET,
5746 Priority TINYINT ,
5747 FindingsGroup VARCHAR(50) ,
5748 Finding VARCHAR(200) ,
5749 DatabaseName NVARCHAR(128),
5750 URL VARCHAR(200) ,
5751 Details NVARCHAR(4000) ,
5752 QueryPlan [XML] NULL ,
5753 QueryPlanFiltered [NVARCHAR](MAX) NULL,
5754 CheckID INT ,
5755 CONSTRAINT [PK_' + CAST(NEWID() AS CHAR(36)) + '] PRIMARY KEY CLUSTERED (ID ASC));'
5756 + ' INSERT '
5757 + @OutputTableName
5758 + ' (ServerName, CheckDate, CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details, QueryPlan, QueryPlanFiltered) SELECT '''
5759 + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
5760 + ''', SYSDATETIMEOFFSET(), CheckID, DatabaseName, Priority, FindingsGroup, Finding, URL, Details, QueryPlan, QueryPlanFiltered FROM #BlitzResults ORDER BY Priority , FindingsGroup , Finding , Details';
5761 EXEC(@StringToExecute);
5762 END
5763 ELSE IF (SUBSTRING(@OutputTableName, 2, 1) = '#')
5764 BEGIN
5765 RAISERROR('Due to the nature of Dymamic SQL, only global (i.e. double pound (##)) temp tables are supported for @OutputTableName', 16, 0)
5766 END
5767
5768
5769 DECLARE @separator AS VARCHAR(1);
5770 IF @OutputType = 'RSV'
5771 SET @separator = CHAR(31);
5772 ELSE
5773 SET @separator = ',';
5774
5775 IF @OutputType = 'COUNT'
5776 BEGIN
5777 SELECT COUNT(*) AS Warnings
5778 FROM #BlitzResults
5779 END
5780 ELSE
5781 IF @OutputType IN ( 'CSV', 'RSV' )
5782 BEGIN
5783
5784 SELECT Result = CAST([Priority] AS NVARCHAR(100))
5785 + @separator + CAST(CheckID AS NVARCHAR(100))
5786 + @separator + COALESCE([FindingsGroup],
5787 '(N/A)') + @separator
5788 + COALESCE([Finding], '(N/A)') + @separator
5789 + COALESCE(DatabaseName, '(N/A)') + @separator
5790 + COALESCE([URL], '(N/A)') + @separator
5791 + COALESCE([Details], '(N/A)')
5792 FROM #BlitzResults
5793 ORDER BY Priority ,
5794 FindingsGroup ,
5795 Finding ,
5796 Details;
5797 END
5798 ELSE IF @OutputXMLasNVARCHAR = 1 AND @OutputType <> 'NONE'
5799 BEGIN
5800 SELECT [Priority] ,
5801 [FindingsGroup] ,
5802 [Finding] ,
5803 [DatabaseName] ,
5804 [URL] ,
5805 [Details] ,
5806 CAST([QueryPlan] AS NVARCHAR(MAX)) AS QueryPlan,
5807 [QueryPlanFiltered] ,
5808 CheckID
5809 FROM #BlitzResults
5810 ORDER BY Priority ,
5811 FindingsGroup ,
5812 Finding ,
5813 Details;
5814 END
5815 ELSE IF @OutputType <> 'NONE'
5816 BEGIN
5817 SELECT [Priority] ,
5818 [FindingsGroup] ,
5819 [Finding] ,
5820 [DatabaseName] ,
5821 [URL] ,
5822 [Details] ,
5823 [QueryPlan] ,
5824 [QueryPlanFiltered] ,
5825 CheckID
5826 FROM #BlitzResults
5827 ORDER BY Priority ,
5828 FindingsGroup ,
5829 Finding ,
5830 Details;
5831 END
5832
5833 DROP TABLE #BlitzResults;
5834
5835 IF @OutputProcedureCache = 1
5836 AND @CheckProcedureCache = 1
5837 SELECT TOP 20
5838 total_worker_time / execution_count AS AvgCPU ,
5839 total_worker_time AS TotalCPU ,
5840 CAST(ROUND(100.00 * total_worker_time
5841 / ( SELECT SUM(total_worker_time)
5842 FROM sys.dm_exec_query_stats
5843 ), 2) AS MONEY) AS PercentCPU ,
5844 total_elapsed_time / execution_count AS AvgDuration ,
5845 total_elapsed_time AS TotalDuration ,
5846 CAST(ROUND(100.00 * total_elapsed_time
5847 / ( SELECT SUM(total_elapsed_time)
5848 FROM sys.dm_exec_query_stats
5849 ), 2) AS MONEY) AS PercentDuration ,
5850 total_logical_reads / execution_count AS AvgReads ,
5851 total_logical_reads AS TotalReads ,
5852 CAST(ROUND(100.00 * total_logical_reads
5853 / ( SELECT SUM(total_logical_reads)
5854 FROM sys.dm_exec_query_stats
5855 ), 2) AS MONEY) AS PercentReads ,
5856 execution_count ,
5857 CAST(ROUND(100.00 * execution_count
5858 / ( SELECT SUM(execution_count)
5859 FROM sys.dm_exec_query_stats
5860 ), 2) AS MONEY) AS PercentExecutions ,
5861 CASE WHEN DATEDIFF(mi, creation_time,
5862 qs.last_execution_time) = 0 THEN 0
5863 ELSE CAST(( 1.00 * execution_count / DATEDIFF(mi,
5864 creation_time,
5865 qs.last_execution_time) ) AS MONEY)
5866 END AS executions_per_minute ,
5867 qs.creation_time AS plan_creation_time ,
5868 qs.last_execution_time ,
5869 text ,
5870 text_filtered ,
5871 query_plan ,
5872 query_plan_filtered ,
5873 sql_handle ,
5874 query_hash ,
5875 plan_handle ,
5876 query_plan_hash
5877 FROM #dm_exec_query_stats qs
5878 ORDER BY CASE UPPER(@CheckProcedureCacheFilter)
5879 WHEN 'CPU' THEN total_worker_time
5880 WHEN 'READS' THEN total_logical_reads
5881 WHEN 'EXECCOUNT' THEN execution_count
5882 WHEN 'DURATION' THEN total_elapsed_time
5883 ELSE total_worker_time
5884 END DESC
5885
5886 END /* ELSE -- IF @OutputType = 'SCHEMA' */
5887
5888 SET NOCOUNT OFF;
5889GO
5890
5891/*
5892--Sample execution call with the most common parameters:
5893EXEC [dbo].[sp_Blitz]
5894 @CheckUserDatabaseObjects = 1 ,
5895 @CheckProcedureCache = 0 ,
5896 @OutputType = 'TABLE' ,
5897 @OutputProcedureCache = 0 ,
5898 @CheckProcedureCacheFilter = NULL,
5899 @CheckServerInfo = 1
5900*/