-1

We decided to migrate a MS SQL Server 2014 a DB into a different server that runs 2016. Now the PHP application that uses this DB has slowed down its performance significantly (queries have jumped from 1 or 2 seconds to 20), although the DB is identical in terms of schema, structure and data. The new server has higher CPU, disk and memory params and the workload is just normal, so this decrease in performance had no sense in the beginning.

Then I analyzed some queries with the Profiler and I was surprised to see that the query plan is different when it runs in either Management Studio (fast, 1 to 3 seconds) or the application front-end (slow, 15 to 25 seconds).

I found this link, and this other too, but after reading a lot and trying different things I can't fix the issue. Has anyone dealt with this before? Any hints about quick things to check before diving deeply?

Thanks!

============

EDIT 1

The query returns just few records, depending on the values of the filters, but something 10 and 1000 (never more than that because it is topped 1000). The main table being queried contains 1 million of records, not much, and whole DB weights around 4 GB. Indexes were migrated successfully, then rebuild and statistics updated.

EDIT 2

AMong the things I tried is rebuild all DB indexes and update all DB tables statistics. I forced RECOMPILE several times too (from Management Studio only). I used index hints to force some indexes to be used (from Management Studio only). None of these helped me fix the issue. Regarding the query plans, they are very very difficult to analyze because my queries are joining about 20 tables and have several subqueries.

EDIT 3

Just to clarify, the query that produces different execution plans is identical even with the parameters. I mean, I run the query from the front-end while a profiler trace is active, the query is captured (takes around 20 seconds) and I copy the text and paste it in management studio, run the same query and get the results in just 2 seconds, plus a different execution plan. Could this kind of issue still be related with parameter sniffing?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hauri
  • 275
  • 1
  • 7
  • 22
  • uh, how does the data looked like (how much? is it hundreds? thousands? billions?), how did you do the query..? and what is your query obviously, if you retrieved tons of data and then process them in php, then yeah, its slow. – Bagus Tesa May 10 '17 at 09:28
  • If you're querying for lots of rows, you should try **In-Memory OLTP**, which returns billions of rows in very few seconds. https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-In-memory-OLTP – Nirav Madariya May 10 '17 at 09:47
  • What are the different things you tried ? – GuidoG May 10 '17 at 11:01
  • check if all the indexes were also copied.Query to get all cache plan.http://stackoverflow.com/questions/43738587/almost-empty-plan-cache/43739599?noredirect=1#comment74525381_43739599 – KumarHarsh May 10 '17 at 11:17
  • Check the sniffed parameters in both plans, not the parameters that you see while executing the query – sepupic May 11 '17 at 10:45
  • What tool would you use for doing that? My execution plan has around 200 icons and the XML version more than 4000 lines... – Hauri May 11 '17 at 11:20

2 Answers2

0

That might be because the ArithAbort property on your database is off. See also http://www.sommarskog.se/query-plan-mysteries.html

You can check it and correct it with this small script

declare @value sql_variant 
select @value = SESSIONPROPERTY('ARITHABORT') 
if @value <> 1 
begin 
    USE master 
    ALTER DATABASE [your_database] SET ARITHABORT ON WITH NO_WAIT
    use your_database
end
GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • OK. I had the same problem once and with me this solved it. Sorry this does not helps you – GuidoG May 10 '17 at 14:57
  • Actually I tried with other configuration params like ANSI_NULLS and other similars and got the same CPU times :S Thanks anyway – Hauri May 10 '17 at 15:37
  • When you change your session options to match with another session that had a better plan, you reuse that good plan, but until what? Any server restart, making db offline or a simple change to you code will invalidate that plan or make it out of plan cache, so changing session options does not resolve the problem. In order to resolve it you should compare the bad plan with the good one and understand the cause of genereting another plan, and make sure that on every recompile you get a good plan – sepupic May 10 '17 at 16:38
0

You need to extract both execution plans: the fast and the slow ones and compare them, you should check the sniffed parameters for which these plans were built; if you find different plans for same queries with different parameters, you may use option(recomplile) or consider rewriting the code as described in the Sommarskog's article. You should not change atirhabort or other session options because the cause of different plans in this case are different parameters for which the query was optimized; different session options just cause the server to sniff different parameters because the new plan was build

sepupic
  • 8,409
  • 1
  • 9
  • 20
  • Hi, thanks for your answer. I know how to force recompile on Management Studio, but I don't know if I should (and how) do this from PHP. Is this what you meant? – Hauri May 10 '17 at 15:39
  • Why can't you add option(recompile) in your php code? – sepupic May 10 '17 at 16:29
  • I editted my question to clarify something that might reject parameter sniffing from being the root cause. What do you think, @sepupic? – Hauri May 11 '17 at 10:33