INSERT running slow in SQL 2016

Question

INSERT running slow in SQL 2016

coolchaitu

SSChampion

Points: 10332
More actions
January 24, 2018 at 11:24 pm

#335388

Good Morning Experts,
We had a database on SQL Server 2012. INSERTS were running fine. We migrated this database to 2016 and noticed same INSERTS taking very very long time. Could it be due to different settings on SQL 2016? Please help

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

coolchaitu - Wednesday, January 24, 2018 11:24 PM
Good Morning Experts,
We had a database on SQL Server 2012. INSERTS were running fine. We migrated this database to 2016 and noticed same INSERTS taking very very long time. Could it be due to different settings on SQL 2016? Please help

More details please!
😎
Server specs, wait types etc.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

coolchaitu - Wednesday, January 24, 2018 11:24 PM
Good Morning Experts,
We had a database on SQL Server 2012. INSERTS were running fine. We migrated this database to 2016 and noticed same INSERTS taking very very long time. Could it be due to different settings on SQL 2016? Please help

Lots more details. Just an INSERT or an INSERT with a SELECT? Statistics? Execution Plan? Structures?

There is no way in the world for someone to give you enough information to begin to troubleshoot your problem when you define the problem as "taking a very long time." It could be anything. You have to provide some information for us to even start to make educated guesses.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

AlexSQLForums SSChampion Points: 14277 More actions · Answer 3

coolchaitu - Wednesday, January 24, 2018 11:24 PM
Good Morning Experts,
We had a database on SQL Server 2012. INSERTS were running fine. We migrated this database to 2016 and noticed same INSERTS taking very very long time. Could it be due to different settings on SQL 2016? Please help

Did you do the following after migration
Use DB;
DBCC UPDATEUSAGE(DB);
GO
EXEC sp_updatestats;
GO

Alex S

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

AlexSQLForums - Thursday, January 25, 2018 10:10 AM
Did you do the following after migration
Use DB;
DBCC UPDATEUSAGE(DB);
GO
EXEC sp_updatestats;
GO

Why would you immediately run sp_updatestats on slow execution of a query? Maybe UPDATE STATISTICS WITH FULLSCAN has already been run. For any table with a single row or more modified, you'll now get a sampled update of the statistics. Without knowing a lot more about the situation, this is actually poor advice.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

AlexSQLForums SSChampion Points: 14277 More actions · Answer 5

Grant Fritchey - Thursday, January 25, 2018 12:11 PM
AlexSQLForums - Thursday, January 25, 2018 10:10 AM
Grant Fritchey - Thursday, January 25, 2018 12:11 PM
AlexSQLForums - Thursday, January 25, 2018 10:10 AM
Did you do the following after migration
Use DB;
DBCC UPDATEUSAGE(DB);
GO
EXEC sp_updatestats;
GO
Why would you immediately run sp_updatestats on slow execution of a query? Maybe UPDATE STATISTICS WITH FULLSCAN has already been run. For any table with a single row or more modified, you'll now get a sampled update of the statistics. Without knowing a lot more about the situation, this is actually poor advice.
Did you do the following after migration
Use DB;
DBCC UPDATEUSAGE(DB);
GO
EXEC sp_updatestats;
GO
Why would you immediately run sp_updatestats on slow execution of a query? Maybe UPDATE STATISTICS WITH FULLSCAN has already been run. For any table with a single row or more modified, you'll now get a sampled update of the statistics. Without knowing a lot more about the situation, this is actually poor advice.

I asked if sp_updatestats were executed after the migration and not on slow execution of the query.

Alex S

coolchaitu SSChampion Points: 10332 More actions · Answer 6

Grant Fritchey - Thursday, January 25, 2018 8:58 AM
coolchaitu - Wednesday, January 24, 2018 11:24 PM
Good Morning Experts,
We had a database on SQL Server 2012. INSERTS were running fine. We migrated this database to 2016 and noticed same INSERTS taking very very long time. Could it be due to different settings on SQL 2016? Please help
Lots more details. Just an INSERT or an INSERT with a SELECT? Statistics? Execution Plan? Structures?
There is no way in the world for someone to give you enough information to begin to troubleshoot your problem when you define the problem as "taking a very long time." It could be anything. You have to provide some information for us to even start to make educated guesses.

Only INSERT

coolchaitu SSChampion Points: 10332 More actions · Answer 7

AlexSQLForums - Thursday, January 25, 2018 1:36 PM
Grant Fritchey - Thursday, January 25, 2018 12:11 PM
AlexSQLForums - Thursday, January 25, 2018 10:10 AM
Grant Fritchey - Thursday, January 25, 2018 12:11 PM
AlexSQLForums - Thursday, January 25, 2018 10:10 AM
Did you do the following after migration
Use DB;
DBCC UPDATEUSAGE(DB);
GO
EXEC sp_updatestats;
GO
Why would you immediately run sp_updatestats on slow execution of a query? Maybe UPDATE STATISTICS WITH FULLSCAN has already been run. For any table with a single row or more modified, you'll now get a sampled update of the statistics. Without knowing a lot more about the situation, this is actually poor advice.
Did you do the following after migration
Use DB;
DBCC UPDATEUSAGE(DB);
GO
EXEC sp_updatestats;
GO
Why would you immediately run sp_updatestats on slow execution of a query? Maybe UPDATE STATISTICS WITH FULLSCAN has already been run. For any table with a single row or more modified, you'll now get a sampled update of the statistics. Without knowing a lot more about the situation, this is actually poor advice.
I asked if sp_updatestats were executed after the migration and not on slow execution of the query.

Yes sp_updatestats was executed

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

coolchaitu - Monday, January 29, 2018 9:24 AM
Grant Fritchey - Thursday, January 25, 2018 8:58 AM
coolchaitu - Wednesday, January 24, 2018 11:24 PM
Good Morning Experts,
We had a database on SQL Server 2012. INSERTS were running fine. We migrated this database to 2016 and noticed same INSERTS taking very very long time. Could it be due to different settings on SQL 2016? Please help
Lots more details. Just an INSERT or an INSERT with a SELECT? Statistics? Execution Plan? Structures?
There is no way in the world for someone to give you enough information to begin to troubleshoot your problem when you define the problem as "taking a very long time." It could be anything. You have to provide some information for us to even start to make educated guesses.
Only INSERT

That's good, but still doesn't provide us with the information needed to answer your question.
We need DDL including indexes defined, the execution plan for the slow execution.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

coolchaitu - Wednesday, January 24, 2018 11:24 PM
Good Morning Experts,
We had a database on SQL Server 2012. INSERTS were running fine. We migrated this database to 2016 and noticed same INSERTS taking very very long time. Could it be due to different settings on SQL 2016? Please help

We had the same problem with many stored procedures that did multi-row inserts. It was all thanks to the "improvements" they made in the optimizer (Cardinality Estimator specifically, IIRC). We "patched" the problem by using the Trace Flag that says to use the old CE. Unfortunately, that also means that our "get out of the woods patch" has become the fix because people don't understand that we actually do need to tweek,n,peak the inserts and won't even try to fix them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

P.S. The Trace Flag that I'm talking about is 9481. Do Google it before you use it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Merkwürdigliebe SSC Journeyman Points: 91 More actions · Answer 11

Jeff Moden - Monday, January 29, 2018 2:55 PM
P.S. The Trace Flag that I'm talking about is 9481. Do Google it before you use it.

I know not a lot to go on but the first thing I thought of was CE too. It had to be done but the CE changes are causing some real issues for folks.
"it worked fine on the old server, are you sure it was setup correctly" I've learned a lot about explain plans and stats over the last 6 months,
Job security I guess...

Arsh SSCertifiable Points: 6345 More actions · Answer 12

coolchaitu - Wednesday, January 24, 2018 11:24 PM
Good Morning Experts,
We had a database on SQL Server 2012. INSERTS were running fine. We migrated this database to 2016 and noticed same INSERTS taking very very long time. Could it be due to different settings on SQL 2016? Please help

And how about the effects on other DMLs? If they too have become slow , then you should also consider architectural (DB file distribution etc) and infrastructure differences too.