SQL Server 2008 timeout on insert I have an issue with insert time out. My table has a size of 12 gb and 12 million rows.

Sometimes I get timeout on insert. I have an insert query every 5 sec on average.

I suspect that I have lock escalations on the table when end user performs a select.

What is the best practice to avoid insert time out on large tables?

Is to create two tables one insert second for select ? replication ? SQL AVAILABILITY Groups ? table partitioning ? any other options?

(soon  we are going to upgrade to SQL Server 2016)

regards,
Pavel.
I would enable Read-Committed Snapshot Isolation, which does row versioning under the covers by making use of tempdb. Readers will not block writers, which is what you're looking for in this scenario.

This is a per-database level setting, which doesn't require a reboot. However, you must have tempdb configured correctly (appropriate fixed-size file growth, sufficient data files, etc.).

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;
GO


(Read more here: Choosing Row Versioning-based Isolation Levels.)