Avatar of marrowyung
marrowyung
 asked on

insert to a large table the SQL server slow.

hi,

we have a SQL 2017 with CU23 and I know the CU is 27 now.


we have a performance problem that when we insert a record to a very large table, 155880134 rows, even a select count(*) seems return slowly and when it is running the wait type is (1ms)HADR_SYNC_COMMIT.


usually other than 

1) change sync type from sync to async.

2) change that table to use table partitioning.

3) make the tempdb growth rate less frequently.


what else we can do.

SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
Pavel Celba

Do you need exact row count? If not then you may add NOLOCK hint.
marrowyung

ASKER
Do you need exact row count?

it just a test on if the SQL server is slow and we don't do it for business. just want to know if this cause that what we can do to solve it.

Pavel Celba

If you have wait type HADR_SYNC_COMMIT which in your opinion causes the slowness then it could mean your HADR solution is slow...
Inserts to a large table also depends on a number of indexes, triggers, and reads from that table during the insert operation.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
marrowyung

ASKER
then it could mean your HADR solution is slow...

then what should I do to solve it ?
marrowyung

ASKER
that table has very few triggers and indexes:

Pavel Celba

I would not say this means "very few"...  Just imagine one insert means to update 8 indexes and it also fires 3 triggers which may update additional data and indexes...
Disable all these triggers and indexes (except the PK) and test the insert. (Don't forget to delete the inserted rows, reenable disabled triggers and indexes, and reindex afterwards.)

Do you need to keep all the data in this possibly frequently updated table? If the most of rows is there for some reporting or auditing purposes then you may move such rows into archive regularly during the night and redirect the reporting to the archive. Etc.

The HADR speed you have to discuss with your hardware specialists.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
If the most of rows is there for some reporting or auditing purposes then you may move such rows into archive regularly during the night and redirect the reporting to the archive. Etc.

this means live and none live DB/table to make the insert to live table faster?


ASKER CERTIFIED SOLUTION
Pavel Celba

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
marrowyung

ASKER
tks.