PASS Summit 2022 - Pre-Cons Were Amazing!

Other PASS Summit 2022 Days - Day 1 |  Day 2 | Day 3

--

It's that time again...TUESDAY!

https://www.mememaker.net/static/images/memes/4791280.jpg

I mean...NOVEMBER!

https://tinyurl.com/33hezsje

OK fine...SUMMIT!

https://img.memegenerator.net/images/10802365.jpg

I have very much missed in-person events and I am glad that Redgate (@Redgate) has managed to continue the @PASSDataSummit framework - virtual presentations are better than absolutely nothing, but it will never (*NEVER*) be the same as being together to not only share comments and questions but also for all of the other networking and social opportunities that in-person events bring.

(Along with that, I think having "hybrid" events like our current Summit dilutes the impact of the in-person event, although that is a conversation for another place, and as always is my $.02)

Now that I am here (AWESOME!) I wanted to share the highlights of my first two days here at Summit 2022.

On Monday I participated in the "Securing the Data Platform" pre-con given by one of my SQLfriends from Iowa, Ed Leighton-Dick (@eleightondick/blog).  Over the last eight years, Ed has grown his company Kingfisher Technologies into a great database security resource.

Ed's presentation followed the same style as many other security talks I have seen - he spent the first half of his talk scaring the bejeezus out of everyone to demonstrate the importance of adopting the principles covered in the second half of the talk!  Ed covered some well known hacks from the last ten years as well as several that I had never heard of at major organizations, and discussed the specific gaps that allowed the incidents to occur

https://s3.memeshappen.com/memes/still-scared--you-should-be-meme-31789.jpg

Ed have some great tips on encryption, including encrypting connection strings, proper ways to set up service accounts to prevent inappropriate escalation of privilege, and general security implementation best practices.

Tuesday brought the pre-con I was most excited for (sorry Ed) - the SQL Server 2022 Workshop presented by Bob Ward (@bobwardms) and an all-star team of Microsoft team members.  Bob's presentations are always "brain melting" and today's did not disappoint.

The main focus of the workshop is what Bob called the SQL 2022 "Wheel of Power" - five grouping of cool new features and improvements in the new product:

courtesy Bob Ward

The presentation ran through a wide variety of items, including:

• Linking Azure SQL Managed Instance to your traditional SQL Server instance for DR or to migrate to MI
• Parameter Sensitive Plan Optimization with multiple query plans for a single stored procedure
• Cardinality Estimation Feedback that leverages Query Store to determine which Cardinality Estimator is better for your high impact queries
• Degree of Parallelism Feedback which similarly uses Query Store to discern if a lower DoP would be "good enough" for your query at lesser CPU cost
• Contained Availability Groups to duplicate instance level objects (including SQL Agent jobs!) as part of the AG
• Ledger to track data changes to document attempts to fraudulently change your data
• Improved T-SQL
• Major Intelligent Query Processing enhancements

...and so much more!

https://i.imgflip.com/4710a3.jpg

The evening closed with the Welcome Reception, which as always was a fun opportunity to reconnect with #sqlfamily that I haven't seen since before the pandemic started.

I am looking forward to the rest of the week - it's going to be great!

--

An aside - this is my first blog post in over a year; I haven't written much since COVID and have not presented at all - I hope to correct both of these going forward, although opportunities for in-person speaking are still greatly diminished after the demise of PASS SQL Saturdays; there are the "new" SQL Saturdays underwritten by the new covering organization, but there simply aren't that many events yet.

Thanks for reading everyone and hope to see you soon!

Disabling Non-Clustered Indexes For Fun and Profit

One of the frequently quoted ETL best practices is to disable your non-clustered indexes during a data load.  Basically the conventional wisdom is that you disable the non-clustered indexes, do your load, and then enable the non-clustered indexes, which functionally rebuilds them with the newly loaded data (a step you should be performing after a load of any size even if you *don't* disable your indexes - rebuild those newly fragmented indexes!)

So why doesn't anybody seem to do this?

https://memegenerator.net/img/instances/53039992/ponder-we-must.jpg

--

IMPORTANT NOTE <cue scary music> - do NOT disable the clustered index when doing data loads or at any other time you want the table to be accessible.  Disabling the clustered index makes the object (table or view) inaccessible.

If you disable the clustered index you are functionally disabling the object, and any attempts to access that object will result in an Error 8655:

Msg 8655, Level 16, State 1, Line 23

The query processor is unable to produce a plan because the index ‘PK_Person_BusinessEntityID’ on table or view ‘Person’ is disabled.

https://memegenerator.net/img/instances/62333367/no-please-dont-do-that.jpg

-- 

I recently had a situation with a client where they have a load process that altogether runs over 24 hours - not one day-long query but rather a string of processes that usually takes 24+ hours to complete.  The situation has worsened to the point where other things are impacted because the process is taking so long, and they asked me to check it out.

I started with all of the basics, and right away Page Life Expectancy during the relevant period jumped out:

The server has 128GB of RAM...so a PLE consistently below 1,000 is pretty low.

I used XEvents to gather queries with large memory grants, and it brought to light an issue that the client has previously considered but not handled - disabling indexes during the load.

** SIDEBAR**

The code for the XEvents session to pull large memory grant queries I used is as follows - it collects any memory grant over 8MB (which you can modify in the WHERE clause) and its calling query:

CREATE EVENT SESSION [Ntirety_MemoryGrantUsage] ON SERVER 

ADD EVENT sqlserver.query_memory_grant_usage

(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id

,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text)

WHERE ([package0].[greater_than_uint64]([granted_memory_kb],(8192))))

ADD TARGET package0.event_file(SET filename=N'Ntirety_MemoryGrantUsage',max_file_size=(256)),

ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(1048576))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON

)

The query to then retrieve the data is this - it dynamically pulls your default error log path and then queries the XEL output file from the event_file target in the session at that path:

USE master

GO

DECLARE @ErrorLogPath nvarchar(400), @XELPath nvarchar(500)

SET @ErrorLogPath = (

SELECT LEFT(cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(400)),LEN(cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(400)))-8)

)

SET @XELPath = @ErrorLogPath+'Ntirety_MemoryGrantUsage*.xel'

SELECT DISTINCT *

FROM 

(

SELECT 

  DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_data.value(N'(event/@timestamp)[1]', N'datetime'))as Event_time

, CAST(n.value('(data[@name="granted_memory_kb"]/value)[1]', 'bigint')/1024.0 as DECIMAL(30,2)) AS granted_memory_mb

, CAST(n.value('(data[@name="used_memory_kb"]/value)[1]', 'bigint')/1024.0 as DECIMAL(30,2)) AS used_memory_mb

, n.value('(data[@name="usage_percent"]/value)[1]', 'int') AS usage_percent

, n.value ('(action[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name

, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name

, n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS client_hostname

, n.value ('(action[@name="server_principal_name"]/value)[1]','nvarchar(500)') AS [server_principal_name]

, n.value('(@name)[1]', 'varchar(50)') AS event_type

, n.value ('(action[@name="sql_text"]/value)[1]', 'nvarchar(4000)') AS sql_text

FROM 

(

SELECT CAST(event_data AS XML) AS event_data

FROM sys.fn_xe_file_target_read_file(

@XELPath

, NULL

, NULL

, NULL)

) 

AS Event_Data_Table

CROSS APPLY event_data.nodes('event') AS q(n)

) MemoryGrantUsage

ORDER BY event_time desc

** END SIDEBAR **

Looking at the queries that were the top memory consumers over the time period led to some pretty basic queries:

INSERT INTO "dbo"."Junk" 

SELECT "UID"

, "ContainerId"

, "CategoryCode"

, "CategoryName"

, "CategoryDataValue"

, "CharacteristicCode"

, "CharacteristicName"

, "CharacteristicDataValue"

, "DimensionCode"

, "DimensionName"

, "DimensionDataValue"

, "OptionId"

, "NumberOfItems"

, "IsStandard"

, "LanguageId"

, "Market"

, "VehicleType"

, "YearMonth"

, "BatchRefreshDate"

, "CreatedBy"

, "CreatedTimestamp"

, "ModifiedBy"

, "ModifiedTimestamp"

, "BatchId" 

FROM "dbo"."Junk_Stg" 

where "Market" = 'XYZ'       

  

https://media.makeameme.org/created/so-simple-right.jpg

The immediate problem to me was that memory grants usually come from SORT and HASH operations (check out Erik Darling's great description at https://www.erikdarlingdata.com/starting-sql/starting-sql-memory-grants-in-execution-plans/) and this query doesn't obviously do any of that - it is a very straightforward INSERT...SELECT from one table into another, but the query plan looks like this:

 

Pretty crazy right?  With all of those SORT's, no wonder SQL Server wants to give this query a giant memory grant!

But why are there six different SORT operations on this one basic INSERT...SELECT?

Then I realized this is why:

Six SORT's...for SIX non-clustered indexes!

Inserting rows into a table with enabled non-clustered indexes requires a SORT to add rows to that index - the INSERT can't simply dump the rows on to the end of the index, but has to sort the input to match the indexes sort.

As a test I created a copy of the target table that I could tweak the indexes on, and a second copy without the indexes in place, and my INSERT...SELECT query plan against the No Index copy looked a little bit better:

As I test I copy-pasted the two INSERT...SELECT's into a single query window and pulled the plans for each so that I could compare them:

The Query Cost numbers in Management Studio always have to be taken with a grain of salt, but for comparison's sake consider - the six-way insert costs almost four times as much as the no-index query!

To test further I disabled three of the indexes on my copy table and ran the plan again:

Bingo - six streams now down to three for the three remaining enabled indexes.

I disabled the rest of the non-clustered indexes and:

https://memegenerator.net/img/instances/63334945/booyah.jpg

For one final comparison I ran the double query against  my "Indexes All Newly Disabled" copy and my "No Index" copy together:

Spot on!

--

As I mentioned above the cost to this is that at the end of the process you have to have time to enable the indexes again, which does a rebuild of that index.

As always Your Mileage May Vary, as the direct impact will relate to the size of the table, the number of non-clustered indexes in place, and multiple other items - but check it out, especially if your process runs longer than you want - this may be the problem!

Hope this helps!

Things I Learned at Summit v20 - Trace Flag 4199

Part of a series of posts on cool stuff I learned at PASS Summit v20 (2018) - in this first post we'll look at a trace flag I had never even heard of, let alone deployed - 4199.

https://i.imgflip.com/15frvt.jpg

What is a Trace Flag?  According to Microsoft:

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. 

At the most basic, you can set or unset a trace flag in order to modify how SQL Server works.  This can be done globally (instance-wide) or for a particular session.

There are a LOT of trace flags, and several are widely used, such as 1204 and 1222 (Deadlock Info) and 1118 (Disable Single Page Allocations), but I haven't heard a Microsoft employee tell me I should "almost always" turn any trace flag on - until now.

--

On Tuesday of Summit week, I was in the pre-conference session put on by the Microsoft MSSQL Tiger Team - the Senior Product Managers of SQL Server Engineering (blog/@mssqltiger).

https://fico.i.lithium.com/t5/image/serverpage/image-id/31298i0D30CC780E2784AD?v=1.0

The session was titled "Modernize Your SQL Server with Bob Ward, the Tiger Team, and CSS Escalation Engineers" and it...was...awesome!

One of the presenters was Pedro Lopes (blog/@SQLPedro), a Senior PM for the Relational Engine.  In his part of the day he talked about several features of the engine and the optimizer, but the "What...what did he say?" moment for me was when he talked about trace flag 4199...and how we should have it turned on pretty much everywhere.

Wait...what?

https://vignette.wikia.nocookie.net/halofanon/images/d/de/BEAN-say-what.jpg/revision/latest?cb=20130302200428

Trace Flag 4199 enables query optimizer fixes that are included in CUs (and the CU breakpoint releases formerly known as "service packs") after RTM.

What does this mean?  If you aren't running with TF 4199, then your Query Optimizer (QO) is running the same as RTM, regardless of what CU level you have applied to your engine.

For example, if you are running SQL Server 2012 SP4 (11.0.7001.0) but you aren't running with TF 4199, then your QO is running the same as someone whose instance is running 2012 RTM (11.0.2100.60).  There are plenty of other things changed by applying the CU, but Query Optimizer function isn't one of them...without 4199.

That melted my mind a little, and I could tell the same was true for the attendees sitting near me - we can religiously apply CUs and fixes but unless we're running this TF (which none of us had ever even *heard* of) then you aren't fixing (improving) your Query Optimizer.

https://i.chzbgr.com/full/3519512832/h0B54C8A3/

Query Optimizer fixes are shipped as OFF by default in order to maintain compatibility and performance when CUs are deployed (so there aren't plan regressions).  Originally there was a new trace flag for each fix, so to turn on multiple fixes you would have to enable multiple trace flags.

As described at https://support.microsoft.com/en-us/help/974006/sql-server-query-optimizer-hotfix-trace-flag-4199-servicing-model, starting way back in SQL2005-SP3-CU6, SQL2008-SP1-CU7, and SQL2008R2 RTM, the ability to enable query optimizer fixes was rolled into one switch, TF 4199. 

While reading the article I tripped over one sentence:

Because trace flag 4199 is recommended only for customers who are seeing specific performance issues, customers are advised to remove trace flag 4199 after they migrate their databases to the latest compatibility level because trace flag 4199 will be reused for future fixes that may not apply to your application and could cause unexpected plan performance changes on a production system

This was interesting to see, because it ran contrary to the advice from this session and several other places I found references to enabling 4199, but this sentence reads like standard lawyerly boilerplate which is enough to make me treat it with healthy distrust.

(For this blog post I took a step I never have for a blog post before - I reached out to Pedro directly to verify my interpretation of his message to make sure I wasn't misrepresenting his content.  He graciously responded with a few comments, including the fact that he is trying to get the above highlighted sentence updated to reflect changes in how TF 4199 works under SQL 2016+ (see below)).

--

At the pre-conference session, Pedro's comments were basically that while there was a time when we all "waited for SP1" to let everyone else test, that isn't how the modern SQL Server development model works.  With the extensive testing that occurs these days as well as the "Cloud First" model that has everything live in production in Azure long before it gets to the on-premises product, there simply isn't that much risk to deploying RTM software or to deploying CUs as they come out.

IMPORTANT NOTE - There is still always (*always*) due diligence - patch DEV before PROD (we all do *that* at least, right?) - but by the time we get a new CU for an on-prem SQL Server it has already been used by thousands (hundreds of thousands) of users in Microsoft testing and on Azure.

In effect, we are already waiting for other people to test it, even without waiting at all.

https://ih0.redbubble.net/image.400523286.2984/flat,550x550,075,f.u3.jpg

While researching this topic I found another well-put opinion in one of the forum answers here by SQL MVP Gianluca Sartori (blog/@spaghettidba):

--

Personally, whenever I build a new server for a new project I always enable TF4199 globally. The same applies when I upgrade existing instances to newer versions.

The TF enables new fixes that would affect the behaviour of the application, but for new projects the risk of regression is not an issue. For instances upgraded from previous versions, the differences between old and new version are a concern on their own and having to deal with plan regression is expected anyway, so I prefer fighting with it with TF4199 enabled. <emphasis mine>

AS far as existing databases is concerned, there is only one way to know: test it. You can capture a workload on the existing setup and replay it after enabling the flag. RML Utilities can help you automate the process, as described in this answer.

Obviously, the flag affects the whole instance, so you'll have to test all the databases sitting there.

--

Another piece to the TF 4199 story is how it relates to database compatibility level.  Prior to SQL 2016, 4199 was an all or nothing - if you were running SQL 2014 SP1 with 4199 enabled, you got the Query Optimizer fixes included in 2014 SP1, regardless of the DB Compat level.

Starting in SQL 2016, DB Compat Level is relevant with 4199 enabled:

https://sqlbits.com/Downloads/550/SQLBits%20-%20Enhancements%20that%20will%20make%20your%20SQL%20database%20engine%20Part%202.pdf

On a SQL Server 2016 server, running in 130 compatibility automatically enables fixes from previous compatibility levels, even without running 4199; the purpose of 4199 in SQL 2016 is to enables fixes *after 2016 RTM* for databases in compatibility 130 - fixes before 2016 RTM are already enabled in compatibility 130.

This model is continued in 2017+ as described here.

This is consistent with another piece of info I learned at Summit v20 from the Tiger Team - start certifying for Compatibility Level, not SQL Server version.  When developing and testing, verify your code for DBCompat 130, *not* SQL Server 2016. (I may write more about that later.)

--

One other item Pedro highlighted in his email response to me was that there are multiple trace flags that Microsoft recommends for various systems, especially high-traffic/high-performance systems.  These still aren't "turn on everywhere" but are frequently useful.  

They are laid out in a pair of MS support articles:

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads

Recommended updates and configuration options for SQL Server 2017 and 2016 with high-performance workloads

Many of these items and other related high-performance configurations are detailed at:

http://aka.ms/sqlperfcenter

Thanks Pedro for your response - and thanks Tiger Team for your amazing pre-con!

https://sayingimages.com/wp-content/uploads/dont-try-to-deny-it-awesome-meme.jpg

--

Hope this helps!

When Good Query Plans Go Bad

Today's story starts again with a page escalation:

--

We are seeing very high CPU usage on DistServer01.  Could you please take a look and do a health check on this server?  Please let us know if there is anything we can do to help.

--

I connnected to DistServer01 and, sure enough, there was consistently 99% CPU in Windows and 90%+ of it was from SQL Server.

https://i.imgflip.com/1mqb58.jpg

As you may have guessed, DistServer01 was the Distributor in a very large transactional replication topology.

I checked the active processes looking for something that was abnormally long-running - no good.

I checked the processes again looking for something using high CPU.  The top thing I found was a Distribution Agent for one of the many publications being routed through DistServer01.  I stopped the SQL Agent job for the Distribution Agent - no good, so I resumed the job.

Next place to turn was that warm comfy place...the Glenn Berry DMV scripts!

http://s.quickmeme.com/img/50/50aefd57e50f2014dda55437e3637e9c9efd9c6b2f84a1ad7f0fa3937129794e.jpg

I have heaped praise on Glenn (blog/@GlennAlanBerry) and his scripts before, and they are definitely my go-to when I dive into troubleshooting.

For high CPU an especially useful query is the "top worker time queries" query.  This is the 2008R2 version (but it works on later versions as well):

--

-- Get top total worker time queries for entire instance (Query 33) (Top Worker Time Queries)
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], t.[text] AS [Query Text],
qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
qs.max_worker_time AS [Max Worker Time], qs.execution_count AS [Execution Count],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.total_physical_reads/qs.execution_count AS [Avg Physical Reads], qs.creation_time AS [Creation Time]
, qp.query_plan AS [Query Plan] -- comment out this column if copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

--

The (truncated) results come out like this:

Database Name	Query Text	Total Worker Time	Min Worker Time	Avg Worker Time	Max Worker Time	Execution Count	Creation Time
NULL	CREATE PROCEDURE sp_MSadd_replcmds( @para	4224360546	0	3920	475776	1077642	05/12/2018 22:55:25
NULL	--  -- Name:   -- fn_repldecryptver4  --   -- Descrip	1724660996	0	89	372022	19285577	05/12/2018 22:55:25
NULL	--  -- Name:  --  fn_MSrepl_isdistdb  --  -- Desc	1066486938	0	53	287015	19817134	05/12/2018 22:55:23
NULL	create procedure sys.sp_MSispeertopeeragent  (	845048463	0	43	203009	19276558	05/12/2018 22:55:26
NULL	CREATE PROCEDURE sys.sp_MSadd_distribution_h	796880096	0	490	368106	1623313	05/13/2018 03:50:07
NULL	CREATE PROCEDURE sys.sp_MSget_subscription_gui	527028755	0	27	379022	19260376	05/12/2018 22:55:27
NULL	create procedure sys.sp_MSispeertopeeragent  (	500954674	0	25	240012	19276558	05/12/2018 22:55:26
distribution	CREATE PROCEDURE sp_MSdelete_publisherdb_trans	434227603	0	204054	5019298	2128	05/12/2018 23:05:04
master	SELECT target_data           FROM sys.dm_xe_session_	342630497	55006	228116	963058	1502	05/13/2018 00:00:08
msdb	CREATE PROCEDURE sp_sqlagent_has_server_access	267846345	30999	763095	21321226	351	05/12/2018 22:55:18

As you can see here, the internal replication process sp_MSadd_replcmds was by far the largest total consumer of CPU, but its average CPU time was still relatively small - it was just called over a million times since the server was last started!

Just to be sure I checked the Missing/Recommended Indexes to make sure there wasn't a glaringly obvious recommendation (sometimes new indexes drastically help CPU) but there was not.

The next place to turn was the query plan...

https://imgflip.com/i/2ajq6z

Query plans can be very complicated subjects.  At the most basic level, query plans are the layout of how a query operates.  It is what we see represented when we look at a graphical execution plan in Management Studio:

Query plans are created on first execution and then are stored in the plan cache on either the first or second execution of the query (depending on whether you have Optimize for Ad Hoc Workloads enabled).

This is beneficial a majority (a vast majority) of the time because generating the query plan takes much more resources that simply looking it up in cache.  The catch is that not all similar queries need the same plan.

The simplest example to me relates to indexing (and is actually one of the reasons my first instinct was to look for missing indexes) - when performing a filtered query (meaning one with a WHERE clause) the amount of data being returned help shape the query plan.  If the estimator expects a large portion of the rows in the table to be returned, it will often ignore nonclustered indexes and simply perform a table scan, checking each row to see if it meets the filter criteria and returning the appropriate rows.  If the estimator expects a more narrow subset and there is a relevant nonclustered index in place, it generates a plan that performs a scan or seek of the nonclustered index and then performs lookups to return any columns not already included in the index.

The problem comes up when the plan that gets cached is built for one of these two cases that is the less frequent of the two.  Using our example above let's say the first run of the query is the large resultset/table scan, which caches that plan.  The next one million runs of the query are the narrow resultset that could have used a nice, small nonclustered index - except there is already a table scan query plan stored in the cache, so SQL Server helpfully does what it's supposed to do any uses that plan, performing a million table scans for our million queries!

http://cdn-webimages.wimages.net/05123d70d127ca613706fc58c0084f07caa772-wm.jpg?v=3

The way to deal with a bad query plan is to get rid of it (Simple, right?)  Many people think you need to dump the full procedure and/or system caches (or even worse, restart SQL Server) to clear this situation (and *DON'T* do that in production!)

What you can do instead is zap a single offending query plan, impacting only that specific query!

As Glenn describes here, you can look up the individual plan handle and then pass it to DBCC FREEPROCCACHE to remove that single plan from the cache.

To look up the plan handle, you can use this query:

--

SELECT cp.plan_handle, cp.objtype, cp.usecounts,
DB_NAME(st.dbid) AS [DatabaseName]
FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE OBJECT_NAME (st.objectid)
LIKE N'%sp_MSadd_replcmds%' OPTION (RECOMPILE);

--

plan_handle	objtype	usecounts	DatabaseName
0x0500FF7FB38EE9D6A03A9D310501000001000000000000000000000000000000000000000000000000000000	Proc	1077642	NULL

--

The next step is to pass this plan_handle to DBCC FREEPROCCACHE:

--

DBCC FREEPROCCACHE(0x0500FF7FB38EE9D6A03A9D310501000001000000000000000000000000000000000000000000000000000000)

--

...in my case - BOOM!  Instantaneous CPU drop from 90%+ to just 5%-10%.

https://revdrbrian.files.wordpress.com/2016/03/and-there-was-much-rejoicing.jpg

Another useful query came from a blog post Chris Skorlinski of Microsoft https://blogs.msdn.microsoft.com/chrissk/2009/05/25/transactional-replication-conversations/

The post is relatively old (2009) but it still is relevant as it talks about troubleshooting replication (which what led me to it, even though my problem ended up not being a replication problem) but the query is useful to find both the plan handles and other relevant information about the top CPU-consuming queries.  It is a useful expansion on the Glenn query above:

--

SELECT TOP 25
st.text,
qp.query_plan,
(qs.total_logical_reads/qs.execution_count) as avg_logical_reads,
(qs.total_logical_writes/qs.execution_count) as avg_logical_writes,
(qs.total_physical_reads/qs.execution_count) as avg_phys_reads,
qs.*
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
--WHERE st.text like 'CREATE PROCEDURE sp_MSadd_replcmds%'
ORDER BY qs.total_worker_time DESC

--

I want to repeat that - even though this is a distribution server in a complicated replication topology, this turned out to *not* be a replication problem.

Always review the evidence - error logs, query results, etc. - and follow the trail - never assume.

--

Hope this helps!

Toolbox - Which Clerk Is Busy?

A few years ago I wrote what would turn out to be my most-hit blog post (so far) titled "Error 701 - Insufficient System Memory - Now what?"

In that post I talk about a troubleshooting scenario that finally led to using DBCC MEMORYSTATUS to find one of the memory clerks (MEMORYCLERK_XE) consuming far too much memory on the instance.  (In that case there was a runaway XEvents session burning lots of RAM).

Bottom line, you should troubleshoot which clerk was the busiest to find the source of the overall problem.

https://dominicantoday.com/wp-content/uploads/2017/09/a-4.jpg

The 701 error (Error 701: “There is insufficient system memory to run this query”) just came up this morning again in our team Skype, and my colleague mentioned that the problem was happening at a time when no one wanted to be on the server watching.

I told him about my previous situation and how looking at DBCC MEMORYSTATUS had led to my smoking gun, and it led to thinking about some way to collect and persist the MEMORYSTATUS data without someone watching live on the system.

Google led me to several variations of a #YouCanDoAnythingWithPowershell script, such as this one from Microsoft Premier Field Engineer (PFE) Tim Chapman (blog/@chapmandew) but I really wanted a Transact-SQL script I could play with myself.

More Google finally led me to a blog post and T-SQL script from the SQL Server Photographer Slava Murygin (blog/@slavasql) (follow his blog for scripts and great photos he takes at all types of SQL events)!

Slava's script translates one of the Powershell scripts into T-SQL, storing the output from DBCC MEMORYSTATUS in a temporary table for output.  The one limitation of this for my needs is that it is an adhoc execution - great to run interactively but not useful to store over time.

It took a few steps to turn Slava's temp table implementation into one that stores the data in a permanent table so that it can be queried after the fact and over time.

A few notes: Slava's script relies on xp_cmdshell, and my modification still does.   My modification stores data in a permanent table, which means it needs to reside in a permanent database.  My script uses the "DBADatabase" including code to create it if it doesn't exist, but it is an easy find-replace to change that name if you'd like:

--

/*

Track DBCC MEMORYSTATUS data over time

Guts of query to parse DBCC results into a temp table from 
http://slavasql.blogspot.com/2016/08/parsing-dbcc-memorystatus-without-using.html

Modified to store those results in a permanent table over time
Intended for use in a scheduled Agent Job but could be run manually as needed

Also modified to run on SQL 2005 (syntax changes)

Relies on xp_cmdshell

Stores data in the "DBADatabase" including creating the database if it doesn't pre-exist

If you wish to use an existing database or a different database 
name simply Find-Replace for the string DBADatabase

*/

SET NOCOUNT ON
GO

IF DB_ID('DBADatabase') IS NULL  /* Check if DBADatabase database exists - if not, create it */
BEGIN
EXECUTE ('CREATE DATABASE DBADatabase')

    ALTER DATABASE DBADatabase SET RECOVERY SIMPLE;

    ALTER AUTHORIZATION ON DATABASE::DBADatabase TO sa;

/* Read the current SQL Server default backup location */  
DECLARE @BackupDirectory NVARCHAR(100)   
EXEC master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',  
@key = 'Software\Microsoft\MSSQLServer\MSSQLServer',  
    @value_name = 'BackupDirectory', @BackupDirectory = @BackupDirectory OUTPUT ;  
EXECUTE ('BACKUP DATABASE DBADatabase to DISK = '''+@BackupDirectory+'\DBADatabase.bak'' WITH INIT')
PRINT 'CREATED DATABASE'
    RAISERROR('Ensure that you add the DBADatabase database to backup / maintenance jobs/plans', 10, 1) WITH NOWAIT;
END;
GO

/* If Holding Table doesn't exist, create it */
IF OBJECT_ID('DBADatabase.dbo.DBCCMemoryStatus') IS NULL
CREATE TABLE [DBADatabase].[dbo].[DBCCMemoryStatus](
[Datestamp] [datetime] NOT NULL,
[DataSet] [varchar](100) NULL,
[Measure] [varchar](20) NULL,
[Counter] [varchar](100) NULL,
[Value] [money] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

IF OBJECT_ID('tempdb..#tbl_MemoryStatusDump') IS NOT NULL
DROP TABLE #tbl_MemoryStatusDump;
GO
IF OBJECT_ID('tempdb..#tbl_MemoryStatus') IS NOT NULL
DROP TABLE #tbl_MemoryStatus;
GO
CREATE TABLE #tbl_MemoryStatusDump(
 ID INT IDENTITY(1,1) PRIMARY KEY
 , [Dump] VARCHAR(100));
GO
CREATE TABLE #tbl_MemoryStatus(
 ID INT IDENTITY(1,1), 
 [DataSet] VARCHAR(100), 
 [Measure] VARCHAR(20), 
 [Counter] VARCHAR(100), 
 [Value] MONEY);
GO
INSERT INTO #tbl_MemoryStatusDump([Dump])
EXEC ('xp_cmdshell ''sqlcmd -E -S localhost -Q "DBCC MEMORYSTATUS" ''');
GO
DECLARE @f BIT
 , @i SMALLINT
 , @m SMALLINT 
 , @CurSet VARCHAR(100)
 , @CurMeasure VARCHAR(20)
 , @Divider TINYINT
 , @CurCounter VARCHAR(100)
 , @CurValue VARCHAR(20);

SET @f=1
SET @m = (SELECT MAX(ID) FROM #tbl_MemoryStatusDump)
set @i = 1

WHILE @i < @m
BEGIN
 SELECT @Divider = PATINDEX('% %',REVERSE(RTRIM([Dump])))
  , @CurCounter = LEFT([Dump], LEN([Dump]) - @Divider)
  , @CurValue = RIGHT(RTRIM([Dump]), @Divider - 1)
 FROM #tbl_MemoryStatusDump WHERE ID = @i;

 IF @f = 1 
  SELECT @CurSet = @CurCounter, @CurMeasure = @CurValue, @f = 0 
  FROM #tbl_MemoryStatusDump WHERE ID = @i;
 ELSE IF LEFT(@CurCounter,1) = '(' SET @f = 1;
 ELSE IF @CurCounter != 'NULL' and LEFT(@CurCounter,1) != '-'
  INSERT INTO #tbl_MemoryStatus([DataSet], [Measure], [Counter], [Value])
  SELECT @CurSet, @CurMeasure, @CurCounter, CAST(@CurValue as MONEY)
  FROM #tbl_MemoryStatusDump WHERE ID = @i;
 SET @i = @i + 1;
END
GO

/*Send data from temp table to permanent table */
INSERT INTO DBADatabase.dbo.DBCCMemoryStatus
SELECT 
GETDATE() as Datestamp
, DataSet
, Measure
, Counter
, Value 
FROM #tbl_MemoryStatus

/* Purge rows older than 96 hours to manage table size */
DELETE FROM DBADatabase.dbo.DBCCMemoryStatus
WHERE DATEDIFF(hh,DateStamp, GETDATE())>96

/*
SELECT *
FROM DBADatabase.dbo.DBCCMemoryStatus
WHERE counter = 'VM Reserved'
ORDER BY DateStamp DESC
*/


--

Running this statement interactively doesn't return any data - it just loads the data into DBADatabase.dbo.DBCCMemoryStatus.  Running the commented-out SELECT at the bottom of the script as written will query that table for all rows of counter VM Reserved (virtual memory reserved) but there is much more data than that available if you modify the SELECT.

This query can be dropped into a SQL Agent job step as is and it will run - just like the interactive run it will create the database and permanent table if they don't exist and then store those nuggets of data into the permanent table for later use - you never know when you may need them!

https://memegenerator.net/img/instances/400x/57081575/winter-is-coming.jpg

--

Hope this helps!