February 3, 2017 at 1:07 pm
I'm really beginning to see this as a bug. We have skewed data where small banks may have a few thousands rows in key tables and large clients have millions. We update statistics with full scan religiously. Most of our poorly performing queries are caused by "good enough" plans being pulled from cache -- that plan having been created by a tiny client.
New indexes and filtered statistics may not even be used without option(recompile) because, surprise, surprise, sql parameter sniffed and pulled a horrible "good enough" plan from cache. Since are code is ORM based with virtually no stored procedure usage, development has to start adding option recompile to certain report queries.
I've recently added filtered statistics and ( until dev does it's thing ) a number of plan guides to add the hint. If you've ever seen ORM-generated sql though, you'll realize that plan guides are hit or miss when the same report may pull a column one time and call it clientID99 and the next time the same query runs, alias it as ClientID23. Freeproccachhe is now a scheduled job 3 nights a week.
🙂
Moving to 2016 fairly soon and I see it has a DB level option to turn off parameter sniffing.
February 3, 2017 at 1:32 pm
Indianrock - Friday, February 3, 2017 1:07 PMI'm really beginning to see this as a bug. We have skewed data where small banks may have a few thousands rows in key tables and large clients have millions. We update statistics with full scan religiously. Most of our poorly performing queries are caused by "good enough" plans being pulled from cache -- that plan having been created by a tiny client.New indexes and filtered statistics may not even be used without option(recompile) because, surprise, surprise, sql parameter sniffed and pulled a horrible "good enough" plan from cache. Since are code is ORM based with virtually no stored procedure usage, development has to start adding option recompile to certain report queries.
I've recently added filtered statistics and ( until dev does it's thing ) a number of plan guides to add the hint. If you've ever seen ORM-generated sql though, you'll realize that plan guides are hit or miss when the same report may pull a column one time and call it clientID99 and the next time the same query runs, alias it as ClientID23. Freeproccachhe is now a scheduled job 3 nights a week.
🙂
Moving to 2016 fairly soon and I see it has a DB level option to turn off parameter sniffing.
A) It is absolutely NOT a bug. If you have data value skew you simply MUST do things differently or you can't buy big enough hardware to deal with the horribly bad plans you will have cached (in both directions too: estimated rows small, actual bajillion and estimated rows bajillion, actual small).
B) There is a way you can have EF queries (if you are using such - nHibernate may have same capability) have a post-pended OPTION (RECOMPILE) on them, which as you have surmised is absolutely necessary (well, dynamic SQL is also a fix). But in EF it is a sledgehammer, affecting EVERY query out of the engine.
C) I have a client that back in the early 2005 days had to have the freeproccache sledgehammer run on it EVERY 15 MINUTES!! That was partly for the parameter sniffing problem but more for the plan cache bloat problem (which was CRITICAL prior to 2005 SP1 because the plan cache, by design, could eat up something like 80% of your buffer pool!!!)
D) As you stated, plan guides are essentially useless for ORM-generated code. Just one of UMPTEEN reasons why database applications should never be created using an ORM. 😀
E) Depending what else is on the server you could use a trace flag (4136 IIRC) to disable parameter sniffing on the entire server on some non-2016 versions.
HOWEVER!!!! Do you REALLY know what will happen when you disable parameter sniffing? The optimizer will use the AVERAGE distribution for all parameters . This will GUARANTEE that you will get bad plans some of the time, and depending on circumstances MOST of the time! I cannot possibly see that being a win for your scenario.
BTW, sounds like you might be the commenter on this blog post? 🙂
http://www.scarydba.com/2016/12/12/optimize-hints-parameter-sniffing-turned-off/
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2017 at 1:43 pm
Hi Kevin. Yes I get around and have received several very helpful responses from you, Grant F and Aaron B. 🙂
Well they did just add option(recompile) to one bad query and limited it to the large clients somehow. So I'm urging them to move forward with that. Our ORM is sort of open source Domain Objects but I do see some use of EF now.
So, no avoiding tinkering under the hood. Reminds me of trying to tune the carburetors on a 1960 Austin Healey 3000 I used to own.
The whole enchilada on SSD is going to be tested very soon, with Sql 2016 just around the corner. We just successfully tested putting the aspstate session tables on a separate 2016 server In Memory. No blocking although it's tough to generate a prod like load even with an unlicensed tool that limits you to 50 app users.
Fun times
February 3, 2017 at 2:08 pm
Indianrock - Friday, February 3, 2017 1:43 PMHi Kevin. Yes I get around and have received several very helpful responses from you, Grant F and Aaron B. 🙂Well they did just add option(recompile) to one bad query and limited it to the large clients somehow. So I'm urging them to move forward with that. Our ORM is sort of open source Domain Objects but I do see some use of EF now.
So, no avoiding tinkering under the hood. Reminds me of trying to tune the carburetors on a 1960 Austin Healey 3000 I used to own.
The whole enchilada on SSD is going to be tested very soon, with Sql 2016 just around the corner. We just successfully tested putting the aspstate session tables on a separate 2016 server In Memory. No blocking although it's tough to generate a prod like load even with an unlicensed tool that limits you to 50 app users.
Fun times
1969 Triumph Spitfire here. Dual carbs fun! 🙂
Personally my STRONG belief is that there are MUCH more appropriate - and WAY cheaper alternatives for ASP.NET session state than SQL Server!! I do note though that the entire reason we have Hekaton at all is BWIN, who have now benchmarked 1.2 MILLION ASP.NET session state activities PER SECOND on a SINGLE SQL SERVER!!! That is just stunning, especially given the blob!!
Are you already on SSDs?? If not BEWARE!! You run the risk of SIGNIFICANT deadlock issues when you take your IO stalls from NNms per to 1 or 2 ms per! I had a client that literally made their system unusable due to the blizzard of deadlocks when they opened up to the web. I note the change was done without me and WOW did I yell at them after I helped them recover (they no rollback plan, obviously, because faster hardware can't EVER be a problem, right?!?)!!! And I had been with them for years so they KNEW what I could do for them. Sigh ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2017 at 2:14 pm
They're looking at Ncache, but we needed something to stop the big money clients from complaining. SSDs will be testing in a prod-like QA environment first. These will be Netapp enterprise-grade SSDs. It has to work there, although, again,
there is that issue of producing a prod-like load
February 3, 2017 at 3:17 pm
The biggest performance factor overall is properly clustering the tables. Presumably you always query by client (if you have clients that see multiple clients, you might need another layer of key)?! Is the first/leading key of the clustering index the client number?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply