7

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_exec_requests and 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 ACCESS_METHODS_DATASET_PARENT.

  • First of all, I don't know how to find the query related to each session_id in the results of querying sys.dm_exec_requests.
  • 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_PARENT is 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:

  • Using 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_all consist 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_all table is 155 MB.

UPDATE_1

Regarding the 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?

UPDATE_2

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.

migrated from stackoverflow.com Nov 21 '16 at 10:58

This question came from our site for professional and enthusiast programmers.

  • I've always found sp_WhoIsActive sqlblog.com/files/folders/release/tags/who+is+active/… to be excellent for a concise snapshot of whats going on without having to dig through DMV's. Where do you observe 'insert speed decreases'? Do you have a front end app? Or a data logger? If a data logger how does it report a decrease in insert speed? From your description I guess there are many frequent small record insertions from many clients rather than lots of large bulk data insertions? – Nick.McDermaid Nov 21 '16 at 10:36
  • 1
    My guess would be that your clustered index on many columns will be slowing things down, as you won't be inserting in the free and clear space at the end of the table, but instead inserting in already-busy pages in the middle. I would probably recommend changing the clustered index on that table to a simple incrementing ID and moving your existing index to a simple nonclustered one if necessary. But dba would be a better place to ask, I'd say... – Matt Gibson Nov 21 '16 at 10:58
  • @MattGibson the inserts are at the end of tables – monamona Nov 21 '16 at 11:18
  • 1
    Is that the exact order that your clustered index is defined in (gs_id, pt_id, fueling_time, purchase_type)? If so, I expect the inserts are not at the end of the tables as you have said, and you are experiencing a large number of page splits during your inserts. – GarethD Nov 21 '16 at 11:43
  • Measure the fragmentation. Is insert faster after you defragment? – paparazzo Nov 21 '16 at 14:43
2

There are many factors that could be causing this slow down of inserts as you have described.

Something that I do not see in your information is the Database / Table size that the inserts are going into. As the table grows, the longer your inserts will take due to table size, fragmentation and writing the same data to your indexes.

The DM queries you are running have given you some good information. I would also recommend using Brent Ozar's toolkit. Link

I would suggest you use SP_BlitzIndex and SP_BlitzCache. These will give you another view of what is actually happening in the database and what the insert queries you are running are doing at that time.

I would also check when the last time a DBCC CHECKDB was run and also when you last rebuilt your indexes and statistics.

Thanks for the updates, see below:

Some more info:

Not a huge amount of read/writes so I'm not too concerned.

Do you know when the last time the trans_all index was rebuilt? If you only have one covering index and many inserts, it will take longer each time when inserting data to it if it is out of date. Below is a query to check fragmentation on your database tables:

Use <Database>
GO

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

UPDATE 1:

MAX DOP settings can affect your query run time, but I can't be sure it is doing so without seeing the query you are using and then looking at the estimated plan results to see what SQL server is using. There are two statistics to use to find out how the query is affected when adjusting the MAX DOP.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

Here is some information by Kendra Little over at Brent Ozar on using these to give you guidelines.

tsql-measure-performance-improvements

and

q-can-high-maxdop-make-a-query-slower

UPDATE 2:

I would recommend increasing your Log size if it is 98% full. Either add another trans log or increase it. You might also look at TEMP_DB and see what it is doing because SQL using that as a dumping ground for many things.

Empty space isn't really an issue unless you are out of room to grow. It is just telling you how much room is left until the next auto grow.

  • Thanks for your reply. So, do you think the problem was not related to degree of parallelism? (since I've seen ACCESS_METHODS_DATASET_PARENT as the result of sys.dm_os_latch_stats? I mean, if it was not the problem why I had to use dmv? – monamona Nov 23 '16 at 9:30
0

If your database is being log-shipped then what could be happening is that the log is filling up with Transactions, then waiting until the next log backup before cleaning them out and making the space for new transactions to continue processing. Try reducing the gap between Log-shipping periods, or increasing the size of the log file. There's other stuff you can do like checking index useage stats and removing unused indexes, their maintenance will create extra demand on the log.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.