sp_Blitz, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, and other SQL Server scripts for health checks and performance tuning.
Home Page: http://FirstResponderKit.org
License: Other
Created by George Stocker ([email protected]) at 2014/07/28 16:49:14 +0000:
Currently, the SQL Server Setup guide has a date it's been changed, but not a summary of changes since that last version.
The summary of changes is helpful for the following reasons:
1 . Tells us if there's any out of date or incorrect information(!)
2. Tells us what's changed, so we don't need to reread the whole document or try to retain multiple versions.
Please add a changelog to the guide that details the changes between each version
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6225318-add-a-changelog-for-sql-server-setup-guide
Created by Michael Bluett ([email protected]) at 2014/07/04 13:35:02 +0000:
It looks like there's a logical error here (the duplicate lines):
IF @output_database_name IS NOT NULL
AND @output_schema_name IS NOT NULL
AND @output_schema_name IS NOT NULL
Should it be:
IF @output_database_name IS NOT NULL
AND @output_schema_name IS NOT NULL
AND @output_table_name IS NOT NULL
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6134458-slight-logical-error-in-sp-blitzcache-sql-v2-3
For the change log, new version date, version number.
This repro is against the StackOverflow DB. Can be Repro'd against SQL14 in the lab.
use StackOverflow;
GO
DBCC FREEPROCCACHE;
GO
SELECT
Score,
Count(*) AS CommentCount
FROM dbo.Comments
WHERE UserId = 557499
GROUP BY Score
ORDER BY Score DESC;
GO
SELECT
Score,
Count(*) AS CommentCount
FROM dbo.Comments
WHERE UserId = 26837
GROUP BY Score
ORDER BY Score DESC;
GO
SELECT
Score,
Count(*) AS CommentCount
FROM dbo.Comments
WHERE UserId = 22656
GROUP BY Score
ORDER BY Score DESC;
GO
exec sp_BlitzCache @results='Expert';
expected result: one line showing 1 query with 3 executions
Actual result: three lines with 1 execution each, all with Query Hash 0x4046BC7C48BEC501
Fixing this will be mildly complicated because there can be different plans-- so you'll have to just pick one and warn on that. In the example here one throws " Missing Indexes (1), Parallel" whereas the others don't.
They all warn "Multiple Plans" and they all have "# Plans" = 3 and "Distinct Plans"=1, with Executions=1.
The # of plans is right, but the Distinct Plans is not.
First Name: Hendrik
Last Name: Meiffert
Your Email: [email protected]
Company: Leo Burnett
Phone:
Your Comments: Hey All,
thank you for everything you give to us newbies out there. I really enjoy watching your movies. Sometimes the fun is lowered because of bad microphone quality but the information you give us, is enourmous. Would have taken me AGES to come to that point.
I am just trying sp_blitzcache for the first time and wanted to report a dead link :
http://brentozar.com/blitzcache/unparameterized-queries
It seemed to be moved for whatever reason, but sp_blitzcache still refers to it.
thank you,
kind regards,
Hendrik
Your Comments: this one is also dead :
http://brentozar.com/blitzcache/compile-timeout/
Newsletter subscription: Yes
Created by Anonymous ([email protected]) at 2014/07/11 20:39:47 +0000:
I have only one stored procedure in my database that uses the WITH RECOMPILE option, and that is sp_BlitzCache. Could the sp_Blitz ignore all the sp_BlitzWHATEVER stored procedures for this alert?
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6163217-have-sp-blitz-ignore-sp-blitzcache
sp_BlitzRS has inconsistencies in object name case, i.e., "SharedDatasets" and "SharedDataSets".
This could show space saved by removing duplicates and unused indexes and the space that would be taken up by recommended indexes.
First Name: Chris
Last Name: Hurford
Your Email: [email protected]
Company:
Phone:
Your Comments: [dbo].[sp_AskBrent] was not returning perfMon information on my named instance until I updated the insert into #PerfmonCounters section
INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES ('SQLServer:Access Methods','Forwarded Records/sec', NULL)
with
DECLARE @instance SYSNAME
SET @instance = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer'
ELSE 'MSSQL$' + @@SERVICENAME
END
INSERT INTO #PerfmonCounters ([object_name],[counter_name],[instance_name]) VALUES (@ServiceName + ':Access Methods','Forwarded Records/sec', NULL)
Great tool though, has helped me out of more than one bind.
Thanks,
Chris
This would be useful for demos, but I've also had some client situations where it would be useful as well.
In https://github.com/BrentOzarULTD/PublicTools/blob/develop/sp_Blitz/sp_Blitz.sql#L370 there's no predicate that would eliminate offline databases.
The unparameterized query warning QueryPlan.exist('//p:StmtSimple[@StatementOptmLevel[.="FULL"]]/*/p:RelOp/descendant::p:ScalarOperator/p:Identifier/p:ColumnReference[contains(@Column, "@")]') = 1 will incorrectly identify functions acting on parameters as an unparameterized query.
by default have sp_blitztrace start the trace, wait 60 seconds, query results, stop the trace, delete the trace.
if users want to just start it or stop it, they have to use a special parameter combo. (I want to leave that in there because it's nice for demo purposes.)
catch errors, try to clean up the xevents session if it hits the catch block.
Or bolth!
Suggested by @LitKnd
Full Name
Michael James Bluett
Email
[email protected]
Your Title
Database Developer
By checking this box, I agree to the Brent Ozar Unlimited Code Contributor Licensing Agreement.
I agree!
Contributing to
sp_Blitz®
Subject
Fix for case sensitivity issues in the query on http://www.brentozar.com/blitz/partitioned-tables-with-non-aligned-indexes/
Contribution Details
(this is not a fix for sp_Blitz itself, but the query on a page that is linked to from the script)
There are case sensitivity issues in the query on the page http://www.brentozar.com/blitz/partitioned-tables-with-non-aligned-indexes/ (I have fixed DS and OBJECT_NAME which had case issues).
Here is the corrected query:
SELECT
ISNULL(db_name(s.database_id),db_name()) AS DBName
,OBJECT_SCHEMA_NAME(i.object_id,DB_ID()) AS SchemaName
,o.name AS [Object_Name]
,i.name AS Index_name
,i.Type_Desc AS Type_Desc
,ds.name AS DataSpaceName
,ds.type_desc AS DataSpaceTypeDesc
,s.user_seeks
,s.user_scans
,s.user_lookups
,s.user_updates
,s.last_user_seek
,s.last_user_update
FROM sys.objects AS o
JOIN sys.indexes AS i ON o.object_id = i.object_id
JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
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()
WHERE o.type = 'u'
AND i.type IN (1, 2)
AND o.object_id in
(
SELECT a.object_id from
(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
GROUP BY ob.object_id, ds.type_desc ) a group by a.object_id having COUNT (*) > 1
)
ORDER BY [Object_Name] DESC
Created by Paulo A. Nascimento ([email protected]) at 2014/08/15 15:46:48 +0000:
Hello guys,
Hope you're doing just fine.
In respect to the little cute query in http://www.brentozar.com/blitz/agent-jobs-starting-simultaneously/
I proposed adding "AND j.enabled = 1" to not show false-positives. Just an idea.
At the end the query should look something like this:
SELECT j.name, j.description, a.start_execution_date
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobactivity a ON j.job_id = a.job_id
WHERE a.start_execution_date > DATEADD(dd, -14, GETDATE()) AND j.enabled = 1
AND a.start_execution_date IN (SELECT start_execution_date
FROM msdb.dbo.sysjobactivity
WHERE start_execution_date > DATEADD(dd, -14, GETDATE())
GROUP BY start_execution_date HAVING COUNT(*) > 1)
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6300768-hello-guys-hope-you-re-doing-just-fine-in-respe
sp_BlitzCache should provide more information about a query hash. This would make showing details about a single plan (see #4) much easier.
Created by Andrew Notarian ([email protected]) at 2014/07/28 17:39:00 +0000:
Because we probably can't do much to tune the internal workings of these.
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6225540-have-sp-blitzcache-ignore-reporting-services-rep
Created by Michael Bluett ([email protected]) at 2014/06/27 13:52:26 +0000:
One of the links to brentozar.com is broken:
Line 1768
'http://brentozar.com/blitzcache/compilaltion-timeout/',
Should be:
'http://brentozar.com/blitzcache/compilation-timeout/',
In this script:
http://www.brentozar.com/first-aid/protected/?scripts/sp_BlitzCache.txt
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6106907-typo-in-sp-blitzcache-sql-v2-3-2014-06-07
Created by Andrew Notarian ([email protected]) at 2014/07/28 17:36:38 +0000:
For SQL 2012+ CLR RAM is included in the general SQL Engine memory space so a separate allocation/reserve is no longer necessary. Second bullet under 11.7. See KB 2663912.
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6225518-add-a-note-to-the-max-memory-section-of-the-sql-se
sp_BlitzCache could look for NOLOCK hints or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in procedure text.
This isn't perfect because the nolock hints could be hidden in views, or the app could be setting the iso level in a statement (not a proc). But hey, it's something!
Extra points for detecting if RCSI / Snapshot are enabled in a user database and this might be underminding that setting.
From Rebecca Lewis, SQLfingers:
Sweet! Thanks a bunch, Brent. Enjoy the rest of your holiday. I will leave you be now... until tomorrow. :-)
On Mon, May 26, 2014 at 6:02 PM, Brent Ozar Unlimited [email protected] wrote:
And by the way, this is driving me crazy because I had a check for this! Now I gotta find out why it didn't alert you on that, heh. I'll get you a new build tomorrow that better alert you on it and might fix the DT stuff too. Thanks for sending me that output! It really helps.
Tiny glass keyboard
Typos flowing like rivers
As winter snow thaws
On May 26, 2014, at 6:56 PM, Brent Ozar Unlimited [email protected] wrote:
Yeah, there we go. It got renamed in the past but nobody told SQL Server. Here's how to fix it:
http://msdn.microsoft.com/en-us/library/ms143799.aspx
Tiny glass keyboard
Typos flowing like rivers
As winter snow thaws
On May 26, 2014, at 6:47 PM, "[email protected]" [email protected] wrote:
This is the log, yet the @@ServerName is RLG-FL-SQLMR\SCALE.
Message
Server name is 'RLG-NY-SQL-DR\SCALE'. This is an informational message only. No user action is required.
On Mon, May 26, 2014 at 5:43 PM, [email protected] [email protected] wrote:
SOB! Look at that ---
SELECT @@ServerName -- Gives me 'RLG-FL-SQLMR\SCALE'
But that is not what I got with SERVERPROPERTY. See here:
DECLARE @props TABLE (propertyname sysname PRIMARY KEY)
INSERT INTO @props(propertyname)
SELECT 'ComputerNamePhysicalNetBIOS'
UNION
SELECT 'InstanceName'
UNION
SELECT 'MachineName'
UNION
SELECT 'ServerName'
SELECT propertyname, SERVERPROPERTY(propertyname) FROM @props
My results:
ComputerNamePhysicalNetBIOS RLG-NY-SQL-DR
InstanceName SCALE
MachineName RLG-NY-SQL-DR
ServerName RLG-NY-SQL-DR\SCALE
On Mon, May 26, 2014 at 5:38 PM, [email protected] [email protected] wrote:
I pull the SQLServerName, MachineName, InstanceName and netbiosName from SERVERPROPERTY. They are the values below, respectively. I will check again real quick. But they have some crazy DT setup. I have to stop the DT job first, then bring up SQL.
RLG-NY-SQL-DR\SCALE,
RLG-NY-SQL-DR
SCALE
RLG-NY-SQL-DR
On Mon, May 26, 2014 at 5:27 PM, Brent Ozar Unlimited [email protected] wrote:
Ah, I bet the server name is wrong - check @@ServerName versus the setver's actual name. That causes all kinds of problems.
Tiny glass keyboard
Typos flowing like rivers
As winter snow thaws
<SQLServerHealthCheck.xlsx>
Created by Anonymous ([email protected]) at 2014/08/01 14:38:46 +0000:
For check 76, I wouldn’t use
'Collation for ' + name
I would use
'Collation for database is'
This keeps the Finding column clean. The database name is available in DatabaseName
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6242767-sp-blitz-v35-improve-check-76
To avoid plan cache bloat. Below is from contribution form:
Full Name
Paolo Piponi
Your Title
Systems Architect
By checking this box, I agree to the Brent Ozar Unlimited Code Contributor Licensing Agreement.
I agree!
Contributing to
sp_AskBrent®
Subject
Better paramterization for Query Stats inserts
Contribution Details
Obviously, this code won't run as it is but the changes should be clear. I've simply replaced the dynamic evaluation with parameters. We have a schedule for AskBrent and this avoids the plan cache filing up unnecessarily.
/* Populate #QueryStats. SQL 2005 doesn't have query hash or query plan hash. */
IF @@Version LIKE 'Microsoft SQL Server 2005%'
SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
SELECT [sql_handle], 2 AS Pass, GETDATE(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, NULL AS query_hash, NULL AS query_plan_hash, 0
FROM sys.dm_exec_query_stats qs
WHERE qs.last_execution_time >= @StartSampleTime ';
ELSE
SET @StringToExecute = N'INSERT INTO #QueryStats ([sql_handle], Pass, SampleTime, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, Points)
SELECT [sql_handle], 2 AS Pass, GETDATE(), statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, execution_count, total_worker_time, total_physical_reads, total_logical_writes, total_logical_reads, total_clr_time, total_elapsed_time, creation_time, query_hash, query_plan_hash, 0
FROM sys.dm_exec_query_stats qs
WHERE qs.last_execution_time >= @StartSampleTime ';
EXECUTE sys.sp_executesql
@Statement=@StringToExecute,
@params=N'@StartSampleTime datetime',
@StartSampleTime=@StartSampleTime;
I have surely done something silly in that query that's easy to fix. 60 second sample took 4 minutes to parse.
2014-09-03T17:55:19.717- Creating and starting trace.
Create trace dynamic sql
CREATE EVENT SESSION sp_BlitzTrace ON SERVER
ADD EVENT sqlserver.degree_of_parallelism (
ACTION(sqlserver.context_info)
WHERE ([sqlserver].[session_id]=(51))),
ADD EVENT sqlserver.object_created (
ACTION(sqlserver.context_info)
WHERE ([sqlserver].[session_id]=(51))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.context_info)
WHERE ([sqlserver].[session_id]=(51))),
ADD EVENT sqlserver.rpc_completed (
ACTION(sqlserver.context_info)
WHERE ([sqlserver].[session_id]=(51))),
ADD EVENT sqlserver.sql_statement_recompile (
ACTION(sqlserver.context_info)
WHERE ([sqlserver].[session_id]=(51)))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY = 128 MB,
EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 1 SECONDS,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=OFF)
2014-09-03T17:55:19.723- Waiting for the sample time to complete...
2014-09-03T17:56:19.727- Reporting, stopping, and deleting trace.
2014-09-03T17:56:19.727- Populating #sp_BlitzTraceEvents...
2014-09-03T18:00:23.510- Querying sql_batch_completed, rpc_completed...
2014-09-03T18:00:23.520- Querying parallelism and memory grant...
2014-09-03T18:00:23.523- Querying object_created ...
Stopping and deleting trace.
Resetting context and we're outta here.
This is a database level check. It only works in SQL Server 2008+. (Plan guides apparently exist in 2005 but they were even suckier then.)
Low pri, this feature doesn't get used much, it just might be easy to add and could be a quick win in a rare situation.
If the following query returns > 0, you have a plan guide in the database that's hosed-- and it could be making queries silently fail.
SELECT
COUNT(*)
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id);
GO
For the documentation. To get details on the error, a user can run:
SELECT
plan_guide_id, msgnum, severity, state, message,
name, create_date, is_disabled, query_text, scope_type_desc, scope_batch, parameters, hints
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id);
GO
Created by Erik ([email protected]) at 2014/09/11 19:28:54 +0000:
sp_BlitzCache will identify the conversion from INT to BIGINT from using COUNT() instead of COUNT_BIG() as implicit conversion in your query plan.
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6425079-disregard-implicit-conversion-from-using-count
These are sleep loops, are showing up in some samples i'm trying to take on sql 2014 and messin' up my screenshots :)
'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
I don't think this is super high priority. Or even high priority. But it looks like it'd be easy.
In an execution plan just look for the presence of "PlanGuideName"
The following repro works on SQL14 for StackOverflow. If you needed a repro for adventureworks2012 I could put something together.
DBCC FREEPROCCACHE
GO
use StackOverflow;
GO
IF OBJECT_ID('dbo.AcceptedAnswersByUser') is null
exec ('create procedure dbo.AcceptedAnswersByUser as return 0')
GO
ALTER PROCEDURE dbo.AcceptedAnswersByUser
@UserId INT
AS
SELECT
(CAST(Count(a.Id) AS float) / (SELECT Count(*) FROM Posts WHERE OwnerUserId = @UserId AND PostTypeId = 2) * 100) AS AcceptedPercentage
FROM dbo.Posts q
JOIN dbo.Posts a ON q.AcceptedAnswerId = a.Id
WHERE
a.OwnerUserId = @UserId
AND
a.PostTypeId = 2
GO
EXEC sp_create_plan_guide
@name = N'AcceptedAnswersByUser-Optimize For Value',
@stmt = N'SELECT
(CAST(Count(a.Id) AS float) / (SELECT Count(*) FROM Posts WHERE OwnerUserId = @UserId AND PostTypeId = 2) * 100) AS AcceptedPercentage
FROM dbo.Posts q
JOIN dbo.Posts a ON q.AcceptedAnswerId = a.Id
WHERE
a.OwnerUserId = @UserId
AND
a.PostTypeId = 2',
@type = N'OBJECT',
@module_or_batch = N'[dbo].[AcceptedAnswersByUser]',
@hints = N'OPTION (OPTIMIZE FOR (@UserId=557499))'
GO
exec dbo.AcceptedAnswersByUser 22656;
GO
EXEC sp_control_plan_guide 'Drop', 'AcceptedAnswersByUser-Optimize For Value';
GO
Created by Darko Martinovic ([email protected]) at 2014/09/05 12:12:55 +0000:
One instance and many databases, from many software companies. I'm interested to view cache only for one database. It means, it will be good to add new parametar e.g. @filterByDatabaseName. I've made some modification to my own, but glad to see how author will handle this.
Second,maybe is not bad idea, if you set default for @output_schema_name to 'dbo'
And finally - On the line 1416, why not display full table name
RAISERROR('Writing results to table.', 0, 1) WITH NOWAIT; -- why not put full table name e.g. RAISERROR(N'Writing results to table: %s' , 0, 1, @fulltableName) WITH NOWAIT
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6397628-filter-cache-by-database-name
So we can respond to people:
Created by Kendra Little ([email protected]) at 2014/09/12 12:36:03 +0000:
It would be awesome to see these plan attributes! http://technet.microsoft.com/en-us/library/ms175088(v=sql.105).aspx
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6428154-please-display-user-set-options-in-expert-mode-in
First Name: Richard
Last Name: Douglas
Your Email: [email protected]
Company: Dell Software
Phone: no
Your Comments: Hi Guys,
Just wanted to add something to sp_blitz. Did a quick check of the code and I don't think you are looking for this currently.
William Durkin wrote a blog about a log reuse problem in simple recovery with 2012. To check if your instance is suffering from that problem there's a simple check:
SELECT name,
recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases
WHERE recovery_model_desc = 'Simple'
AND log_reuse_wait_desc = 'LOG_BACKUP';
William's blog about it is here - http://williamdurkin.com/2014/06/simple-recovery-log_backup/
Regards,
Rich
Submitted by Andrew Notarian [email protected]
The XPM option does not work for me, SQL 2012 SP1. Not sure if this is something that only works on 2014.
/*------------------------
EXEC sp_BlitzCache @sort_order='executions per minute'
------------------------*/
Setting up temporary tables for sp_BlitzCache
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Adding SQL to collect trigger stats.
Collecting execution plan information.
Msg 207, Level 16, State 1, Line 190
Invalid column name 'creation_time'.
Msg 207, Level 16, State 1, Line 190
Invalid column name 'creation_time'.
Msg 207, Level 16, State 1, Line 191
Invalid column name 'creation_time'.
Msg 207, Level 16, State 1, Line 301
Invalid column name 'execution_count'.
Msg 207, Level 16, State 1, Line 302
Invalid column name 'age_minutes'.
Msg 4104, Level 16, State 1, Line 302
The multi-part identifier "qs.creation_time" could not be bound.
Msg 4104, Level 16, State 1, Line 302
The multi-part identifier "qs.last_execution_time" could not be bound.
Msg 207, Level 16, State 1, Line 302
Invalid column name 'age_minutes'.
Msg 4104, Level 16, State 1, Line 302
The multi-part identifier "qs.creation_time" could not be bound.
Msg 4104, Level 16, State 1, Line 302
The multi-part identifier "qs.last_execution_time" could not be bound.
Msg 207, Level 16, State 1, Line 303
Invalid column name 'execution_count'.
Msg 207, Level 16, State 1, Line 303
Invalid column name 'age_minutes'.
Msg 207, Level 16, State 1, Line 303
Invalid column name 'age_minutes'.
Msg 4104, Level 16, State 1, Line 303
The multi-part identifier "qs.creation_time" could not be bound.
Msg 4104, Level 16, State 1, Line 303
The multi-part identifier "qs.last_execution_time" could not be bound.
Computing CPU, duration, read, and write metrics
Checking for query level SQL Server issues.
Scanning individual plan nodes for query issues.
Checking for plan compilation timeouts.
Checking for forced parameterization and cursors.
Populating Warnings column
Building query plan summary data.
Displaying analysis of plan cache.
I love how sp_Blitz is warning about the corruption bug for 2012. Similarly would be nice to warn about this security bulletin.
There's two components that this fixes, and one is a TSQL vulnerability. "A local attacker could exploit this vulnerability by creating a specially crafted T-SQL statement that causes the Microsoft SQL Server to stop responding.")
"This security update is rated Important for supported editions of Microsoft SQL Server 2008 Service Pack 3, Microsoft SQL Server 2008 R2 Service Pack 2, and Microsoft SQL Server 2012 Service Pack 1; it is also rated Important for Microsoft SQL Server 2014 for x64-based Systems. "
Good news for one group: SQL Server 2012 users should just go to SQL 2012 SP2 (patched for the corruption bug), that isn't impacted.
https://technet.microsoft.com/en-us/library/security/MS14-044
Created by Steve Hood ([email protected]) at 2014/06/16 17:12:08 +0000:
Based on the following query, check to see what's in cache in the current database and compare that to the size of those indexes. This is a great way to find recent large index scans that are causing more IO on the server, and could help find the offending code in the proc cache or recent code in EE/Trace data.
Although this is just a current snapshot, some large scans happen frequently. If an index looks out of place here then you have the opportunity to look into it more and justify the use of your memory. A single run won't give you everything, but it will give you a start.
SELECT cached_MB
, ObjName = name
, index_id
, index_name
, Pct_Of_Cache = cast((cached_mb * 100) / cast(SUM(cached_mb) over () as DEC(20,4)) as DEC(5,2))
, Pct_InRow_Data_In_Cache = cast((100.0 * cached_MB) / (1.0 * Used_InRow_MB) as DEC(5,2))
, Used_MB
, Used_InRow_MB
, Used_LOB_MB
FROM (
SELECT count(1)/128 AS cached_MB
, obj.name
, i.index_id
, index_name = i.name
, i.Used_MB
, i.Used_InRow_MB
, i.Used_LOB_MB
FROM sys.dm_os_buffer_descriptors AS bd with (NOLOCK)
INNER JOIN
(
SELECT name = OBJECT_SCHEMA_NAME(object_id) + '.' + object_name(object_id)
, object_id
, index_id
, allocation_unit_id
FROM sys.allocation_units AS au with (NOLOCK)
INNER JOIN sys.partitions AS p with (NOLOCK)
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT name = OBJECT_SCHEMA_NAME(object_id) + '.' + object_name(object_id)
, object_id
, index_id
, allocation_unit_id
FROM sys.allocation_units AS au with (NOLOCK)
INNER JOIN sys.partitions AS p with (NOLOCK)
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
INNER JOIN (
SELECT Name = OBJECT_NAME(PS.Object_ID)
, PS.Object_ID
, PS.Index_ID
, Used_MB = SUM(PS.used_page_count) / 128
, Used_InRow_MB = SUM(PS.in_row_used_page_count) / 128
, Used_LOB_MB = SUM(PS.lob_used_page_count) / 128
, Reserved_MB = SUM(PS.reserved_page_count) / 128
, row_count = SUM(Row_Count)
FROM sys.dm_db_partition_stats PS
GROUP BY PS.OBJECT_ID
, PS.Index_ID
) i ON obj.object_id = i.object_id AND obj.index_id = i.index_id
WHERE database_id = db_id()
GROUP BY obj.name
, i.index_id
, i.name
, i.Used_MB
, i.Used_InRow_MB
, i.Used_LOB_MB
HAVING Count(*) > 128
) x
ORDER BY 1 DESC;
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6061048-create-sp-blitzbuffercache
Created by George Stocker ([email protected]) at 2014/08/05 13:28:58 +0000:
Currently, sp_blitzcache triggers a 'multiple plans' warning for Trivial Plans in the cache (SELECT B FROM TABLE where A=1 and C=2 in my case).
Since these are trivial plans; they're not really useful to show up in sp_blitzcache (since they're not going to be cached anyway); and in multi-tenant situations, these can literally fill up the results for sp_blitzcache.
I've tested a fix, and I believe this works, but if you change line 1289 in v2.3 from:
plan_warnings = CASE WHEN QueryPlan.value('count(//p:Warnings)', 'int') > 0 THEN 1 END,
To:
plan_warnings = CASE WHEN QueryPlan.value('count(//p:Warnings)', 'int') > 0 AND QueryPlan.exist('//p:StmtSimple[@StatementOptmLevel[.="TRIVIAL"]]') = 0 THEN 1 END,
That keeps those trivial statements from triggering a warning in sp_blitzcache.
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6258775-disregard-trivial-plans-with-multiple-plans-warn
Created by Anonymous ([email protected]) at 2014/08/01 14:37:24 +0000:
Comments in sp_blitz v35 indicate
... To contribute code and see your name in the change
log, email your improvements & checks to [email protected].
But if I email that address, I get told to go to
Uservoice URL: http://brentozarultd.uservoice.com/forums/250742-general/suggestions/6242763-update-link-for-suggestions-in-comments-at-head-of
Liar! Liar! Pants on fire!
Op Stats column is not reporting updates correctly. It says 0 when I look at the DMV and see 731 for the NC index under leaf_update_count.
Hi Brent,
for some config values there are no records in #ConfigurationDefaults.
When running below script on my SQL Server 2014 I get 6 configs that
are not in #ConfigurationDefaults (blocked process threshold (s),
common criteria compliance enabled, EKM provider enabled, backup
compression default, filestream access level, backup checksum
default).
For backup compression default there is another check.
Maybe all the others a new with SQL Server 2014?
select sc.*
from #ConfigurationDefaults cd
right join sys.configurations sc
on cd.name = sc.name
where cd.name is null;
best regards
Tobias Ortmann [email protected]
Not counting this as required to ship V1, but certainly nice for later versions.
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.