Task Manager Graffiti

Brent Ozar has time to kill and a 72-core box on which to kill it:

For example, when I run it with 10,000 joins:

  1. Msg 8631, Level 17, State 1, Line 1
  2. Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

When I drop it down to a much more realistic 5,000 joins:

  1. Msg 4074, Level 16, State 1, Line 1
  2. Client drivers do not accept result sets that have more than 65,535 columns.

Ah! Okay, that’s fair. (That’s also two error messages I’ve never seen before. Probably a good thing.) Alright, let’s take out the SELECT * and replace it with SELECT p1.* and see what happens:

This is a fun read.

Related Posts

Join Elimination

Lukas Eder has a nice post explaining different forms of automatic join elimination: We intended to fetch all customers and their addresses. But observe: We project only columns from the CUSTOMER table and we don’t have any predicates at all, specifically not predicates using the ADDRESS table. So, we’re completely ignoring any contributions from the ADDRESS table. We never really needed […]

Read More

The Pain Of Multi-Statement TVFs

Andy Mallon walks through a multi-statement table-valued function in Microsoft Dynamics CRM: Look at all those table-valued function calls! Followed immediately by a really expensive hash match. My Spidey Sense started to tingle. What is fn_GetMaxPrivilegeDepthMask, and why is it being called 30 times? I bet this is a problem. When you see “Table-valued function” as an operator […]

Read More

Categories

August 2016
MTWTFSS
« Jul Sep »
1234567
891011121314
15161718192021
22232425262728
293031