Sql-server – SQL database performance optimization

performanceperformance-tuningsql serversql-server-2008

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.

Best Answer

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.

Related Question