SQL Server – LOGMGR and how to reduce it

SQL Server Books online defines LOGMGR as “Occurs when a task is waiting for any outstanding log I/Os to finish before shutting down the log while closing the database “

Depending on what other waits are occurring – this could indicate an IO bottleneck. For example , if ASYNCH_IO_COMPLETION,IO_COMPLETION,WRITELOG,PAGEIOLATCH_x  are involved this is a strong indicator of the IO bottleneck.

If you want to doublecheck – collect Current Disk Queue Length  , which will indicate that the IO subsystem is struggling to complete throughput

Any SQL Server task waits for IO completion – this is completely normal part of SQL Server processing. But when the wait for IO completion is slow there is a problem

A few  issues to consider and approached to minimizing LOGMGR

1)Maintain Random access \ Sequential access files  on different drives . Particuarly focus on isolating the transaction log file . Work with you storage administrator – in identifying the optimal configuration to assist in throughput

2)Are you experiencing IO delay messages in the error logs. This is a strong indicator , as it reports on IO delays – outside of the SQL Server process space.

Click here for a in depth explanation

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server – IO_COMPLETION and how to reduce it

SQL Server Books online defines IO_COMPLETION as “Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits”

As with  ASYNC_IO_COMPLETION  it is a symptom of an  IO bottleneck  . Cross check by identifying other wait stat indicators such as : ASYNC_IO_COMPLETION  ,WRITELOG,PAGEIOLATCH_X. The appearance of at least one of these along with IO_COMPLETION – near the top of the wait stats indicates a serious IO bottleneck

It’s useful to differentiate between WRITELOG – which occurs when waiting for a log flush to occur – and IO_COMPLETION. IO_COMPLETION reports on non-data pages and essentially is a SQL Server thread awaiting  the kernel to return the IO request status.

The complexity in diagnosing the source of the problem , is to understand the OS , SQL server , IO configurations which could include drive profile, controllers etc.Tools such as SQL Profiler & Perfmon assist

Any SQL Server task waits for IO completion – this is completely normal part of SQL Server processing. But when the wait for IO completion is slow there is a problem

A few  issues to consider and approached to minimising IO_COMPLETION.

1)Maintain Random access \ Sequential access files  on different drives . Create an io profile of the database server and balance drive appropriately .

2)Commit some analysis of poorly running queries. Is excessive table and index scanning occurring ? This could be causing many round-trips to  the disk

 3)End to end analysis – for example , is IO being throttled ?

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server Async_io_completion and how to reduce it

SQL Server Books online defines Async_io_completion as “Occurs when a task is waiting for I/Os to finish “

Mainly a symptom of an  IO bottleneck. Cross check by identifying other wait stat indicators such as :IO_COMPLETION ,WRITELOG,PAGEIOLATCH_X. The appearance of at least one of these along with ASYNC_IO_COMPLETION – near the top of the wait stats indicates a serious IO bottleneck

Any SQL Server task waits for IO completion – this is completely normal part of SQL Server processing. But when the wait for IO completion is slow there is a problem

A few  issues to consider and approached to minimizing Async_io_completion.

1)Maintain Random access \ Sequential access files  on different drives .

2)Are you experiencing IO delay messages in the error logs. This is a strong indicator , as it reports on IO delays – outside of the SQL Server process space.

the article : IO Requests taking longer than 15 seconds to complete gives an in depth explanation

3)Analyse the SQL Server Performance Stack

 CPU Cores

Windows

SQL Server

Cache

FC HBA

Fibre Channel Switch

Storage Controller

Storage Controller Cache

Disk

LUN

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server profiler Scan Event

Normally , if I’m doing some general health checks – I’ll issue a trace with some designated conditions- depending on the application. For example , it may be looking at Duration of queries on a specific database.  This will give me some hints at what to analyse.

 Once identifying the relevant queries  - I would investigate Execution Plans , statistics, fragmentation . All fairly straightforward stuff.

 Recently I implemented  a Trace ,  using the Scan Event. This is part of the Scans Event Category which produces event classes related to scanning tables and indexes.

The Scans Event category has two Event classes. Started Event Class and Stopped Event Class.

As the name suggests the Started Event Class is triggered when a scan of a table o index starts and the Stopped Event Class is triggered when the Scan stops. It is possible to combine the Scan event with the  statements captured in one of the TSQL Event Category classes.

 I found the Transaction ID column -  ID of the transaction if the statement was run within a transaction – useful as I was able to run one trace focused on the Scan events and cross reference with output from the TSQL events.

 Scans of tables aren’t always bad. In fact , sometimes the Query Optimizer will suggest to do a scan, if it decides that there aren’t sufficient unique values in a column. The cost-based optimizer will calculate resources required - and make the relevant decision.

 But , if the DBA has designed indices based on potential queries – then Scans may suggest a number of problems such as : index design, statistics out of date, data sets have changed since the original design. The Scan Event is a useful tool to use .

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server – read ahead

On the SQL Server performance stack one of the potential performance bottlenecks is the read-ahead rate at the Cache level.

 The relational engine manages the method of access  and the buffer manager. The methods of access relates to table scans &  index  scans . The buffer manager accesses and updates database pages.

 This is a very useful process. But the performance is dependant on the organisation of the data being prefetched. If the data is contigious – the Read Ahead manager can read large chunks of data at a single time.  On the other hand if the data is mixed i.e scattered across the extents and mixed in with other objects, then this places a greater cost on retrieving the data.

In those situations , aim to make the data as contigious as possible. Decrease fragmentation levels.

 Read-ahead is a database engine optimisation method. The theory is that the database engine will anticipate the requirement for data pages and index pages within a query, the manager will fetch the data and place it in the cache –in preparation for use.  

 While CPU is able to manage computational aspects of the query, the read ahead will read from disk. A read- ahead read will use a scatter – gather approach to placing in the buffer cache. Scatter – gather is ideal as it attempts to  deal with moving data in and out of non- contigious memory areas. If the particular page already exists in the buffer cache , it will be discarded. As mentioned above, it is ideal to decrease fragmentation levels.

 Read – ahead comes in two flavours : data page reads & index page reads

 Reading data pages involves building a list of disk addresses via the IAM.Reading index pages for non clustered indexes  uses  prefetching . As the leaf pages are being scanned – the database engine will begin to retrieve rows that have already been scanned.

Source:Jack Vamvas (http://www.sqlserver-dba.com)