Reliable SQL Server 2012 Hosting Tips: There are various reasons affection performance of inserts in SQL Server. Couple of them are
1. Are you getting wait type as WRITELOG :
The reason here is number of log cache flushes is high causing sql sever to wait while writing in log cache. you’ll cut back this by breaking large transaction into little batched or using bulk inserts.
2. Too many indexes on table:
If my table is having too many indexes which means for each insert sql server have to update all indexes on table.
3. Disk is slow:
If I/O is slow , its slowest resource of any server, you’ll face inserts are slow. this can be reduced by sharing table in multiple data files on multiple disks.
4. If we get wait type PAGELATCH_EX:
If IDENTITY column is a Clustered Index. this may cause all new inserts to go against the same page – at the end of the clustered index because the data is ordered. so this page will be a natural hotspot with threads waiting for PAGELATCH_EX
5. Table with no clustered index :
SQL Server will spend more time searching for available space than when a clustered index is used.
6. Trigger on Table:
If there are triggers on table, then definitely it gona slow down inserts
7. Page splits :
Page splits is one of the biggest reason of slow inserts, Once can avoid same by setting appropriate fillfactor
If you want a reliable web host, there is nobody else who can do a better job than HostForLIFEASP.NET. Why? It is not only about their 99.9% uptime. They are much more than that. You should learn about it from our hosting review.