0

Scenario: We have a simple select query

Declare P@

SELECT TOP(1) USERID
FROM table
WHERE non_clusteredindex_column = (@P) ORDER BY PK_column DESC

It usually executes with in 0.12sec since 1 year. But Yesterday suddenly exactly after mid night it started consuming all my CPU and taking 150 sec to execute. I checked SP_who2 and found no dead locks and nothing except this one query consuming all CPU. I decided to reboot the server to get rid of any Parameter sniffing issue or to kill any stale connections.I took a SLQ profiler Trace for 1 min before restarting the server for future Root Cause Analysis. After reboot, everything is back to normal. I was surprised and curiously started reviewing the Execution plan in profiler that I took and comparing to the current execution plan of the SAME query. I found both are different.

Execution plan before problematic Night is same as the execution plan after the Reboot. (Doing perfect Index seeks)

But the execution plan in Problematic Night SQL profiler is doing full Index Scan which is taking all CPU and taking 150 sec to execute.

Quesion:

I can say the execution plan was suddenly recompiled or query started using new execution plan(full scan) after yesterday midnight and after I rebooted, again it started using the old and good execution plan( Index SEEK).

Q1. What made SQL server to use new EXECUTION plan all of a sudden? Q2. What made SQL server use the old & good execution plan after Reboot? Q3. Anything related to Parameter Sniffing as I am passing Parameter. But technically, it shouldn't be as The parameter column is well organized with evenly distributed Data.

Damodara Lanka
  • 51
  • 2
  • 9
  • What there a large amount of data being loaded (insert/update) simultaneously to when the query used the poorer execution plan? – Sam M Mar 06 '19 at 03:20
  • Also, were there any other tasks being run? That timing sounds suspicious, what housekeeping tasks are run at midnight? – DancingFool Mar 06 '19 at 05:06
  • There is no Unusual Load (inserts/deletes) happend and Ofcourse the timing sounds suspicious. But No House keeping tasks running. Even though if we have any house keeping task, How would that effects the query to pick new and bad execution plan?, All that I want know is "IS there possibility that SQL server will recompile and use new execution plan for the same exact old query all of a sudden? If Yes, WHY?" – Damodara Lanka Mar 06 '19 at 14:17
  • 1
    Yes, it may recompile by itself, but triggered by something. For some reasons see https://www.mssqltips.com/sqlservertip/5308/understanding-sql-server-recompilations/ And it most likely was a parameter sniffing issue, possibly caused by bad or missing statistics, which is why I wondered about what housekeeping tasks might be going on. A server restart, something updating some of the queries or flushing the plan cache, things like that. – DancingFool Mar 07 '19 at 00:16
  • Does that mean, SQL Restart will flush the Plan cache? Is that mean SQl server need to work on recompiling all Plans for all queries for every restart? – Damodara Lanka Mar 07 '19 at 14:38
  • @Damodara Lanka, yes restarting will flush the plan cache and cause all plans to recompile. It also clears out other caches for the DMV stats, indexes, cached data pages in memory, etc. It's generally not recommended to restart the server to fix parameter sniffing issues. A lot of things will become temporarily slower and there's always a small risk of the server starting up in recovery mode, depending on what it was doing when you shut it down. To at least temporarily clear out a parameter sniffing issue you can use the OPTION RECOMPILE keywords, and even better is to update statistics. – J.D. Oct 31 '20 at 02:54

2 Answers2

0

It sounds like you are having a parameter sniffing issue. I can't see your data but often we found these crop up even in simple query scenarios when either many rows match the parameter result and it flipped to a scan even when it shouldn't or there was some other problem with the data such as many values are unique but they decided under some scenario that column should have a 0 in a large portion of the table throwing everything for a loop. If the query from code is running slow but you can do a test procedure execution from ssms this is a pretty big red flag that something along this line is your issue.

You are correct that SQL restart flushes all the plan cache or you can manually flush all the plans out but you absolutely do not want to use this method to fix the plan of a single procedure. A quick fix is you can execute a EXEC sp_recompile 'dbo.procname'; to force it to flush just a single procedure execution plan and make a new one. Redoing all your plans especially in a busy database can cause significant performance concerns of other procs and restart of course has some downtime. This only temporarily fixes the problem when it crops up though if you have identified a parameter causing issues I would consider looking into addition an optimize for unknown hint specifically designed for parameter sniffing issues that have been identified. But also maybe make sure some good index maintenance is going on the regular in your environment in case that is causing bad plans not the sql engine.

N. Rhoades
  • 1
  • 2
0

In your case, you can do the following :

-- Activate the query store option in you database setting . Set Operation Mode To On.

enter image description here

-- This will start capturing the query plan for each request.

-- You can start tracking the query that consumes a lot of resources

-- Finally you can force an execution plan to be used for this query

enter image description here

Hicham
  • 29
  • 1