Sql-server – Identify Query Waiting on resources

How can I check whether the process is actually doing something?

For the session 65, process is suspended because its waiting on a process to complete. That wait info itself is highlighted there as PAGEIOLATCH_SH..

This means from MSDN

Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode.

These waits are commonly associated with disk I/O bottlenecks, though the root cause of the problem may be, and commonly is, a poorly performing query that is consuming excessive amounts of memory in the server. PAGEIOLATCH_* waits are specifically associated with delays in being able to read or write data from the database files.

The first thing you need to analyse is how frequent is this wait. Compare this with you're baseline data and see if its actually a problem.

May be it comes for a fraction of second and then just vanishes. Should not be that big a concern for you in case you saying its not blocking. But again it depends how you have baseline the data.

Referring from this very helpful article Knee-Jerk Wait Statistics : PAGEIOLATCH_SH

Here are some possible root causes (not an exhaustive list):

• External Windows memory pressure on SQL Server causing the memory manager to reduce the buffer pool size

• Plan cache bloat causing extra memory to be borrowed from the buffer pool

• A query plan doing a table/clustered index scan (instead of an index seek) because of:

  1. a workload volume increase

  2. a parameter sniffing

  3. problem a required nonclustered index that was dropped or changed

  4. An implicit conversion

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.