We have a write-heavy SQL database (SQL Server 2008) and the insert speed decreases after some time. I read these similar questions: Sql insert speed up, SQL: What is slowing down INSERTs if not CPU or IO? and Speed up INSERTs.
Also, I read this post about how to analyze SQL Server performance which helped me to know how to find bottlenecks (for example, by querying
sys.dm_os_wait_stats), but still I have difficulties in interpreting the query results and fixing the problem.
First, I started with querying the
sys.dm_exec_requests which only returned one session id (select query) with status "running" (i.e., I found NO SUSPENDED SESSIONS). So, if nothing has blocked my insert, why does it become slow?
Next, I used
sys.dm_os_wait_stats to check statistics about all wait types. The result showed that LATCH_EX, CXPACKET AND PAGEIOLATCH_SH had the most wait_time (694379 ms, 310364 ms and 308335 ms respectively).
Then I used
sys.dm_os_latch_stats to find the most prevalent latch type which in my case was
- First of all, I don't know how to find the query related to each session_id in the results of querying
- Secondly, what numbers of wait-time (in
sys.dm_os_wait_stats) should be considered as high? And if the above mentioned numbers (results of sys.dm_os_wait_stats) are high, how can I decrease them?
- As far as I understand,
ACCESS_METHODS_DATASET_PARENTis related to parallelism and one of the solutions I've found was reducing the degree of parallelism. Is that right?
- In MySQL, there are some tunning settings which could be done right after installation (e.g., increasing innodb buffer pool size), is there something similar in SQL Server?
Some more info:
sys.dm_db_index_usage_stats, number of reads and writes are 80336 and 70672 respectively.
One of our most busy tables is
trans_all(showing all payments transactions) with NO TRIGGERS, NO CONSTRAINTS (this is the same for all tables), but it has a CLUSTERED INDEX, namely,
PK_trans_allconsist of the following columns: gs_id(smallint), pt_id(tinyint), fueling_time(datetime), purchase_type(tinyint).
Database size is 2.6 GB and the size of
trans_alltable is 155 MB.
ACCESS_METHODS_DATASET_PARENT, I tried the solution that I've found (i.e., changing the degree of parallelism. I changed it to 1 to make sure that query never go to parallelism), but it didn't fix the issue :| should I change it again? what is the default that I should use?
I just checked the size of transaction log file for my database which is 2.4 GB and %log space used is 98%. Could this be the reason to slow down my inserts? Should I increase the log file size?
Also, I used
sys.database_files to check the file size, and the amount of empty space for my database. Result shows that filesize is 195 MB and empty space is 0.187 MB.