Sql-server – Stored procedure slows down after upgrade from SQL Server 2008 to SQL Server 2016

performancesql-server-2016stored-procedures

Post migration from SQL Server 2008 to SQL Server 2016, one of our stored procedure is showing performance degradation. It works fine few days, and then gets slow taking up to 5 minutes. Interestingly, at times SP gets back to working normal again.

On clearing the query cache of stored procedure (DBCC FREEPROCCACHE), it starts to work fine (but only for few days).

We have tried to look into execution plan, and it looks like when the performance is fine – the smaller table join happens first to filter the records, whereas while the stored procedure is slow, the smaller table join happens in the end resulting into slow performance. The clustered index seek shows up "Expected Number of Rows"= 4 and "Actual Number of Rows" ~ 60000000.

Appreciate any inputs.

Best Answer

Make sure your statistics are getting updated. Also check your index fragmentation and your plan for maintaining indexes.

Since you are on SQL Server 2016, I think you should look into the Query Store feature. You turn it on by running

ALTER DATABASE YOURDATABASE SET  QUERY_STORE = ON;

Once this is turned on, the Query Store will keep the execution plans. There are several searches you can use, one being "Regressed Queries". Once you find your query, you can look at the different execution plans and force the query to use the more efficient plan with the GUI tools.

To force your stored procedure to recompile it's plan the next time it is executed, you can run

 EXEC sp_recompile 'YOURSTOREDPROCEDURE';

If you would like SQL Server to recompile the procedure plan each time it is executed, you can run

 EXECUTE YOURSTOREDPROCEDURE WITH RECOMPILE;

Here are some links for further reading and understanding.

The SQL Server 2016 Query Store: Overview and Architecture

Monitoring Performance By Using the Query Store

Recompile a Stored Procedure

Related Solutions

Sql-server – Slow insert in SQL Server 2008

Why no clustered index? Why no primary key?

This is most likely your problem: you don't have any order to the table (in the sense of, say, an IDENTITY column) this you are inserting into a heap

See

Edit, after update.

Having a non-unique clustered index requires a uniquifier (DBA.se links) per duplicate gn_ObjectId entry. This is not a good clustering key, which is best when it is

numeric
monotonically increasing
narrow

I also assume you haven't tried a IDENTITY column then...? Try it, report back..

Edit 2, as @JNK says, an ID column is not an overhead in this situation because of how your writes will be managed on disk

Postgresql – GROUP BY very slow when returning many rows

Off the top of my head you can reduce the number of cycles by using a query wrapper and grouping on that. For example:

zabbix=> \copy (
select hostid,
    value,
    extractDay,
    extractMonth,
    intYear,
    DailyUsage
from
(
select 
    h.hostid, 
    sum(value) as value, 
    extract(day from to_timestamp(clock)) as extractDay, 
    extract(month from to_timestamp(clock)) as extractMonth, 
    2015 as intYear, 
   'DailyExemptUsage' as DailyUsage
from 
   hosts h, 
   items i, 
   history_uint hu
where 
   h.hostid = i.hostid
   and i.itemid = hu.itemid
   and i.name like 'Air%bound%Tot%'
   and clock between 1430463600  and 1432969200 
   and extract(hour from to_timestamp(clock)) between 1 and 5
   and h.name like '172.xxx.%'
) as source
group by 
   hostid, 
   extractDay,
   extractMonth
) 
to '/home/me/my_file' with csv;

Best Answer

Related Solutions

Sql-server – Slow insert in SQL Server 2008

Postgresql – GROUP BY very slow when returning many rows

Related Question