SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INSERT running slow in SQL 2016


INSERT running slow in SQL 2016

Author
Message
coolchaitu
coolchaitu
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9459 Visits: 1644

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


Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172626 Visits: 24494
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!
Cool
Server specs, wait types etc.

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)

Group: General Forum Members
Points: 375388 Visits: 34638
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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
AlexSQLForums
AlexSQLForums
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13398 Visits: 3032
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
Grant Fritchey
SSC Guru
SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)SSC Guru (375K reputation)

Group: General Forum Members
Points: 375388 Visits: 34638
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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
AlexSQLForums
AlexSQLForums
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13398 Visits: 3032
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
coolchaitu
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9459 Visits: 1644
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
coolchaitu
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9459 Visits: 1644
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
Lynn Pettis
SSC Guru
SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)

Group: General Forum Members
Points: 417989 Visits: 43943
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)

Group: General Forum Members
Points: 936357 Visits: 49080
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search