There are various reasons affection performance of inserts. 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 can reduce this by breaking huge transaction into small batched or using bulk inserts.
2. Too many indexes on table:
If my table is having too many indexes that means for every 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 can 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 will 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
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 can reduce this by breaking huge transaction into small batched or using bulk inserts.
2. Too many indexes on table:
If my table is having too many indexes that means for every 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 can 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 will 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
No comments:
Post a Comment