0

Our production server has rather high CPU and I wondered if a possible cause is a high rate of compilations on the server.

There's about 5000 SQL compilations/sec (with a compilation to batch request percentage of about 30%). In contrast there's only about 5 recompiles a second at the most.

I thought maybe the proc cache had filled up but having cleared out some redundant single-use plans and since then the proc hasn't returned to it's old size (either in plan count or space used)

So to investigate further we did an extended events session for:

  • query_post_compilation_showplan events
  • sp_cache_miss events

There were far fewer query_post_compilation_showplan events than compilations - about 5-10 a second However there were about 5000 sp_cache_miss events/ sec

Almost all of them showed the following SQL text

SET DATEFORMAT YMD; SET DATEFIRST 1;SET ANSI_NULLS ON;SET ANSI_PADDING ON;SET ANSI_WARNINGS ON;SET ARITHABORT ON;SET NUMERIC_ROUNDABORT OFF;SET CONCAT_NULL_YIELDS_NULL ON;SET QUOTED_IDENTIFIER ON;

The dev team are checking but it looks like our web application sends this in every time it connects to SQL Server.

This thread https://social.msdn.microsoft.com/Forums/sqlserver/en-US/84860bdf-b0f2-41b1-be5f-165b4787667d/sql-profiler-trace-result?forum=sqldatabaseengine suggests that these SET statements commonly show up in audit events - which makes sense. But I am not sure why they are showing up as sp_cache_miss events -

here's a screenshot of the session

I suppose I have 3 questions:

  1. Why are these these "SET" statements showing up as sp_cache_miss events i.e. why doesn't SQL Server just ignore or maybe cache them?
  2. Do these explain our compilations/sec or does a cache miss event not always "count" as a compilation?
  3. Are these benign or could they explain our high CPU.

Sorry if these are daft questions - I am fairly inexperienced with all of this and I am definitely missing something - so any help would be gratefully received.

bluekangaroo
  • 1
  • 1
  • That looks bad, it's supposed to send that as markers in part of the first TDS packet, not as an actual batch. Was that the whole batch or just the beginning? IME high comps/sec is usually a lack of parameterization or `varchar` params with different lengths – Charlieface Mar 02 '21 at 10:24
  • Thanks for replying @Charlieface - to try and answer your question to see if the "SET" statement is part of a batch I ran another XE session on a non-production server with low activity. The only command in the same batch I saw was sp_reset_connection. Another test seemed to help answer a couple of questions: we ran the above SET command in SSMS a few thousand times whilst monitoring compilations/second & processor time in perfmon. Running this did seem to "count" towards compilations/sec, and there was a simultaneous spike in CPU too. So I think you're right - this isn't good. – bluekangaroo Mar 02 '21 at 22:41
  • `sp_reset_connection` is bogus, it definitely is not a batch with a compilation, it's just the connection pool passing a reset flag in the first TDS packet. – Charlieface Mar 02 '21 at 22:43
  • @Charlieface - thanks for your response and for pointing me in the right direction. I have heard back from our dev team and it seems that these options *are* added explicitly to each stored procedure call in some old code. Solution I am going to ask them to test will be to remove this and use the default connection options for most of these SET options and user language settings for the 2 date-related options. – bluekangaroo Mar 09 '21 at 09:22
  • The `SET` options can be set server-wide or database-wide also – Charlieface Mar 09 '21 at 09:30

0 Answers0