Queries that Fail and How to find Them

Queries will fail. That is as inevitable as death and taxes. Many times queries fail without you knowing. Other times, the failure is glorious and impossible to not notice. In the majority of cases, queries fail simply because they are flat out terrible. This article will explore some terrible query designs and how to find them.

In this article, I am not going to explore how to resolve failing queries. Rather, my objective is simply to illustrate an easy method to find these horrible beasts. I must provide advance warning, the queries I will share are based on real queries I have encountered in the wild. These are not contrived examples – despite being rewritten for a test environment.

This article will explore two specific query patterns that produce to very extreme errors. In addition, I will add an additional error that is less horrendous but still related to terrible code. (Wow, talk about being blunt right now!)

When all is said and done, this article will show three specific examples of bad queries. These examples have the multi-purpose of demonstrating both how to find these terrible queries but also to reveal that such terrible coding standards can cause serious problems. How do we find these queries though? The answer is incredibly simple – Extended Events! (Bet you didn’t see that coming!)

For the sake of posterity, I am also adding this to the MASSIVE collection of Extended Events articles.

Supremely Awful Queries

I cannot over exaggerate the beauty of the awfulness of these two query examples. These queries end up producing two terrible “kill everything” errors. Those errors are 8623 and 8632. Here are the messages that come with those errors.

Error: 8623, Severity: 16, State: 1 The query processor ran out of internal
resources and could not produce a query plan. This is a rare event and only
expected for extremely complex queries or queries that reference a very large
number of tables or partitions. Please simplify the query. If you believe you
have received this message in error, contact Customer Support Services for
more information.

When this error is thrown it is not uncommon to also see the following errors/messages.

Msg 701, Level 17, State 65, Line 1
There is insufficient system memory in resource pool ‘default’ to run this query.

(1 row(s) affected)
An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown.

And then we have the slightly more favorable error that still results from an ugly as sin query.

Msg 8632, Level 17, State 2, Line 2
Internal error: An expression services limit has been reached.
Please look for potentially complex expressions in your query, and try to simplify them.

How can these errors be thrown? Here is a sample script to Complex_Queries. Warning: these queries are awful and produce seriously undesirable effects. Use at your absolute own risk! I must also reiterate that very similar queries to these have been found in production environments (e.g. environments running WebSense).

Hunting Specific Queries

The easy way to find these terrible queries is through the power of XEvents. Personally, I prefer to hunt for the errors rather than fish. What I mean by that is that I prefer to seek for specific issues rather than cast a wide noisy net. I have some clients that have an XEvent session running that is capturing every error. To me, that is not supremely useful – it is 99% noise and turns into something that just gets ignored.

Instead of the wide net for every random error, I have the following specific session for these two errors.

-- Create the Event Session
IF EXISTS (
SELECT*
FROMsys.server_event_sessions
WHEREname = 'overly_complex_queries'
)
DROP EVENT SESSION overly_complex_queries 
ON SERVER;
GO
DECLARE @ServerMajorVersion DECIMAL(4, 2);
SELECT@ServerMajorVersion = CONVERT(DECIMAL(4, 2), PARSENAME(dt.fqn, 4) + '.' + PARSENAME(dt.fqn, 3))
FROM(
SELECTCONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion'))
) dt(fqn);
IF @ServerMajorVersion >= 11.0
BEGIN
CREATE EVENT SESSION overly_complex_queries
ON SERVER
ADD EVENT sqlserver.error_reported
(ACTION (
 package0.callstack
   , sqlserver.database_id
   , sqlserver.session_id
   , sqlserver.sql_text
   , sqlserver.username
   , sqlserver.nt_username
   , sqlserver.server_principal_name
   , sqlserver.client_hostname
   , package0.collect_system_time
   , package0.event_sequence
   , sqlserver.database_name
   , sqlserver.session_nt_username
   , sqlserver.client_app_name
   , sqlserver.context_info
   , sqlserver.client_connection_id
 )
 WHERE (
 (
   [severity] = 16
 OR [severity] = 17
   )
   AND(
   [error_number] = 8623
OR[error_number] = 8632
   )
 )
)
ADD TARGET package0.asynchronous_file_target --automatically changes to package0.event_file from 2012 on
(SET filename = 'C:DatabaseXEoverly_complex_queries.xel'
, metadatafile = 'C:DatabaseXEoverly_complex_queries.xem' --ignored >= 2012
, max_file_size = 10, max_rollover_files = 5
)
  , ADD TARGET package0.ring_buffer -- Store events in the ring buffer target
(SET max_memory = 4096, max_events_limit = 10)
WITH (
MAX_MEMORY = 4090KB
  , EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
  , MAX_DISPATCH_LATENCY = 4 SECONDS
  , MAX_EVENT_SIZE = 2048KB
  , MEMORY_PARTITION_MODE = PER_NODE
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = ON
);
END;
ELSE
BEGIN
--Create an extended event session
CREATE EVENT SESSION overly_complex_queries
ON SERVER
ADD EVENT sqlserver.error_reported
(ACTION (
-- package0.callstack, --new in 2012
    sqlserver.database_id
   , sqlserver.session_id
   , sqlserver.sql_text
   , sqlserver.username
   , sqlserver.nt_username
   --, sqlserver.server_principal_name --new in 2012
   , sqlserver.client_hostname
   , package0.collect_system_time
   , package0.event_sequence
   --, sqlserver.database_name --new in 2012
   , sqlserver.session_nt_username
   , sqlserver.client_app_name
   --, sqlserver.context_info --new in 2012
   --, sqlserver.client_connection_id --new in 2012
 )
 WHERE (
 (
   [severity] = 16
 OR [severity] = 17
   )
   AND(
   [error_number] = 8623
OR[error_number] = 8632
   )
 )
)
ADD TARGET package0.asynchronous_file_target
(SET filename = 'C:DatabaseXEoverly_complex_queries.xel'
, metadatafile = 'C:DatabaseXEoverly_complex_queries.xem'
, max_file_size = 10, max_rollover_files = 5)
  , ADD TARGET package0.ring_buffer -- Store events in the ring buffer target
(SET max_memory = 4096, max_events_limit = 10)
WITH (
MAX_MEMORY = 4090KB
  , EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
  , MAX_DISPATCH_LATENCY = 4 SECONDS
  , MAX_EVENT_SIZE = 2048KB
  , MEMORY_PARTITION_MODE = PER_NODE
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = ON
);
END;
-- Start the session
ALTER EVENT SESSION overly_complex_queries 
ON SERVER STATE = START;
GO

I have customized this script to account for some minor differences introduced with XEvents in SQL Server 2012. This session should help you capture the culprit most of the time with these errors. Occasionally, 8623 is so severe that it chokes out the error reporting so that it doesn’t get registered.

Arithmetic and Bad Queries

Now it is time for that bonus session. I have frequently used this session at clients because they have the same problem. They all frequently (or at least used to) run into Arithmetic errors. Once received they think SQL Server is broken and don’t realize they have a problem in the code.

Let’s start with a bit of a contrived example.

DECLARE @math1 DECIMAL (3,0) = 100
,@math2 DECIMAL (3,0) = 100
,@result DECIMAL (3,0)
SELECT @result = @math1 *@math2
SELECT @result

Running this sample code will result in the following error.

Msg 8115, Level 16, State 8, Line 5
Arithmetic overflow error converting numeric to data type numeric.

The error message seems pretty straight forward. In short, you have a math problem that does not work with your data types. This was illustrated in the example because 100*100 cannot fit into a DECIMAL(3,0) being that it is five digits in size. So let’s set an easy trap to capture these issues (along with the offending query).

IF EXISTS (
SELECT*
FROMsys.server_event_sessions
WHEREname = 'ArithmeticError'
)
DROP EVENT SESSION ArithmeticError ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION ArithmeticError
ON SERVER
ADD EVENT sqlos.exception_ring_buffer_recorded
(ACTION (
 sqlserver.tsql_stack
   , sqlserver.sql_text
 )
 WHERE (error_number = 8115)
)
ADD TARGET package0.event_file
( -- file target
SET filename = 'C:DatabaseXEArithmeticError.xel'
, metadatafile = 'C:DatabaseXEArithmeticError.xem'
)
WITH (
MAX_MEMORY = 4096KB
  , EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
  , MAX_DISPATCH_LATENCY = 10 SECONDS
  , MAX_EVENT_SIZE = 0KB
  , MEMORY_PARTITION_MODE = NONE
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = OFF
);
GO
ALTER EVENT SESSION ArithmeticError 
ON SERVER STATE = STOP;
GO

Here is a sample output of that session.

Now you are hunting. Better yet, you are a Rock Star DBA that is efficient in doing your task!

Put a bow on it

I am not a fan of capturing / tracing for everything. However, there is a lot of value for monitoring for specific conditions. When monitoring for specific conditions, there is great power and efficiency.

In this article, two Extended Event Sessions were shown that can help target very specific conditions. This helps the sessions become more efficient as well as the DBA in charge of the environment. Becoming more efficient at finding the real problems helps to free time for doing other things like corporate initiatives (or personal pet projects). I call that a win-win!

Interested in learning more deep technical information? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the fifth article in the 2021 “12 Days of Christmas” series. For the full list of articles, please visit this page.

The post Queries that Fail and How to find Them first appeared on SQL RNNR.