100 Percent CPU Race condition

GW 2014-05-13 14:36:49

My 4-core Win2012/SQL2012 64Gig server goes to 100% CPU when more than 5-6 people run this at the same time. Only DBCC Freeproccache solves the problem, invalidating everything in cache. No joy. Query is created by Entity Framework 6.X

@SQLTrooper 2014-05-13 14:45:09
Can you share your PLE (page life expectancy)? Do you have hyperthreading enabled? I have enjoyed a great performance boost in terms of CPU when enabling hyperthreading on newer processors. That would get you four additional virtual cores for a total of eight – could make a world of difference.

But, the 100% CPU condition sounds like there are some wait/spinlock issues – can you run the following query from Paul Randal's blog and share the results?

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

GW 2014-05-13 14:47:49
PLE is fine.
3600 to 45000
HT = no
running as VMWARE guest 1 proc, 4 cores

Waits are CXPACKET, SOS_SCHEDULER_YIELD AND PAGELATCHIO_EX

App runs fine for 7-10 days, then 100% CPU happens

@SQLTrooper 2014-05-13 14:59:52
First, your query got truncated so that makes it a little tough to make suggestions at this point.

Well, CXPACKET isn't always bad, but that could point to inefficient parallelism. Do you have max degree of parallelism set to the default of 0 still? I would go with 2 personally if you have four cores. I would also adjust Cost Threshold while you are in there to 50 from the default of 5.

Speaking of cores, SOS_SCHEDULER_YIELD is an indication of CPU pressure (obviously). You need more processing horsepower. PAGELATCHIO_EX is I/O pressure, but we have to take this one step at a time. Perhaps a more efficient query would take pressure of all these resources.

SQLkiwi 2014-05-13 15:26:28
If you are simply looking to ensure this query always produces a serial execution plan, the main route open to you when using Entity Framework is to use a plan guide. The link (and related pages) explains the process, but the idea is to obtain the parameterized form of the query, then use the plan guide to add a MAXDOP (1) hint.

That said, hints and plan guides ought to be a last resort. There are two separate root causes here.

First, the query plan appears to be sensitive to sniffed parameter values. When the query is compiled with one sniffed parameter value, the query optimizer chooses a serial execution plan based on an index-intersection strategy. This is the serial plan you originally posted.

When the query replan later recompiles (perhaps due to a change in statistics, as the database changes over time) the sniffed parameter value is different, causing SQL Server to choose a parallel execution plan based on an index scan instead. This plan is cached and reused for subsequent executions, quickly overwhelming your server with parallel queries.

The more serious second problem is that you are using scalar functions extensively, and filtering on the results. This is a major anti-pattern, because it can result in a separate function execution for each row of the index scan, and it might also severely limit the optimizer's ability to use indexes. The function calls are shown below:

Function calls

I cannot over-emphasise how inefficient this is. For each of the 31,716,200 rows from the RemitItem table, five separate data-accessing function queries are executed. That is to say 158,581,000 separate evaluations before the filtering can be applied.

Notice also the implicit conversions, meaning your function inputs expect Unicode strings but MRNO is stored as something else (possibly not even a character type).

Forcing a serial plan or messing about with parallelism thresholds might buy you some time, but ultimately, a serious design and implementation rethink is needed here.

@SQLTrooper 2014-05-13 15:39:15
Great points, Paul. My suggestions weren't really focused at this query (since we couldn't see it all); rather a baseline/best practice. I like to work on a solid foundation/configuration, and drive on from there. But you've pointed out some obvious specific issues – hope he gets it ironed out!
GW 2014-05-13 15:45:15
I could send the original text, but it needs to stay anon.
Yes this it what happens when you use Microsoft's Entity Framework. Plus the issue of using a CLR UDF to compute columns. Paul blogged about the issue of QO running the function instead of using the existing on-disk values here: http://stackoverflow.com/questions/5998217/why-does-the-execution-plan-include-a-user-defined-function-call-for-a-computed
SQLkiwi 2014-05-13 15:47:04
@SQLTrooper I understand, and yes the key is a 'more efficient query' as you say in one of your comments. Unfortunately, T-SQL scalar functions (especially ones that access data!) aren't a 'solid foundation' for anything good to happen ๐Ÿ™‚
GW 2014-05-13 16:44:07
What am I asking? What makes this query drive the CPU to 100%? Not that EF is much of an issue, as you would think an ad-hoc query would suffer this way. 31M rows. I guess thats SOP for an ORM.
GW 2014-05-13 17:32:42
"This is a major anti-pattern"
Agreed.

We are doing the C# string split found in the .NET CLR UDF in the import of the ETL instead. System dev'd before I got there by non-dba folks. Then will re-eval performance.

Thanks for the pointers.

Found this site by using SQL Sentry "post your plan" feature.

SQLkiwi 2014-05-13 17:53:40
@GW So you're saying the functions listed are string-splits with the resulting values stored in persisted computed columns? Do you have an index on those columns that would be useful for the query? The table DDL would be most useful, even if you can't share the query text. Unless you're saying you're happy moving the whole idea into the ETL, in which case I guess we're done for the moment ๐Ÿ™‚
GW 2014-05-13 18:56:25
"Do you have an index on those columns that would be useful for the query"
Yes

But sometimes they are used, sometimes not.

Yes, sorry, prob done for the moment.

CLR is NOT the choice for string splits in UDFs for this reason

@SQLTrooper 2014-05-13 19:06:15
Well, something is causing the optimizer to make those choices at compile/recompile time. Whether indexes are used or not is largely based on statistics. How often are you doing maintenance on your indexes/stats? Do you have auto-update stats on/off for that database? How many rows are in the largest table, and is it updated often? There is a trace flag that will update the statistics more often for larger tables versus waiting for 20% of the data to be updated before triggering an auto-update. The trace flag is 2371.

My take is that if it's running "okay" sometimes, and not others, we have to find out why. Unless of course you plan on rewriting the thing – you'll have to make that determination.

GW 2014-05-13 20:05:50
Updated stats and defragged indexes didnt help.

Reading a few of Pauls parallelism posts like
http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx

the hard part is the system runs fine for 7-10 days, then goes 100% CPU. Its parallelism related, as it never happens at maxdop 1.

But since MS put out this ORM, and devs actually use it, "not concerned about the SQL under the sheets", you cant spec things like hints, options, flags, dbcc commands to help mr optimizer out in EF.

@SQLTrooper 2014-05-13 20:23:03
FWIW, enabling the trace flag has nothing to do with EF, and some go so far as to say not using it is the exception (Grant F., for example). It may not help the OP at all in this particular case, but given the larger data sets it could have a positive impact in other situations.
SQLkiwi 2014-05-13 20:08:01
@GW "CLR is NOT the choice for string splits in UDFs for this reason".

My advice is to avoid functions in computed columns full stop. If you can split the strings before storing them, that's definitely the way to go.

SQLkiwi 2014-05-13 20:12:09
@SQLTrooper Read my stack overflow answer linked to above. The issues are rather more complex than the usual ones. CLR functions have to be persisted to be a key in an index, so there's no way to avoid the possibility in general.
SQLkiwi 2014-05-13 20:13:47
@GW "you cant spec things like hints, options, flags, dbcc commands to help mr optimizer out in EF"

Hence my remark about plan guides, which can enable those things when you can't change the query text. Still, removing the functions from the computed columns is hugely preferable.