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