Home » 2013 » October

Monthly Archives: October 2013

DMV-15 : Pending I/O requests……..sys.dm_io_pending_io_requests


sys.dm_io_pending_io_requests DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms188762.aspx

Returns a row for each pending I/O request in SQL Server.

It’s a very simple DMV used to see all pending I/O requests & there description.

You can combine this DMV with DMF – sys.dm_io_virtual_file_stats to see I/O pending requests with database files. You should run this query multiple times to check if the same files or drive letters consistently coming up on the top. If this is the case that means you facing I/O bottlenecks for that file or drive letter.

Query 1 : Details of I/O pending requests against each DB file

SELECT

DB_NAME(MF.DATABASE_ID) AS [DATABASE],

MF.PHYSICAL_NAME,

IPIR.IO_TYPE,

SUM(IPIR.IO_PENDING) TOTAL_PENDING_IO,

SUM(IPIR.IO_PENDING_MS_TICKS) TOTAL_PENDING_MS_TICKS,

SUM(VFS.NUM_OF_READS) TOTAL_READS,

SUM(VFS.NUM_OF_WRITES) TOTAL_WRITES

FROM

SYS.DM_IO_PENDING_IO_REQUESTS AS IPIR

INNER JOIN

SYS.DM_IO_VIRTUAL_FILE_STATS(NULL,NULL) AS VFS

ON IPIR.IO_HANDLE = VFS.FILE_HANDLE

INNER JOIN

SYS.MASTER_FILES AS MF

ON VFS.DATABASE_ID = MF.DATABASE_ID

AND VFS.FILE_ID = MF.FILE_ID

GROUP BY MF.DATABASE_ID, MF.PHYSICAL_NAME, IPIR.IO_TYPE

ORDER BY SUM(IPIR.IO_PENDING)

Remarks

1. To use this DMV, User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Advertisements

Cumulative Update – 13 for SQL Server 2008 SP3 Is Now Available !


The 13th cumulative update release for SQL Server 2008 Service Pack 3 is now available. Cumulative Update 13 contains all the hotfixes released since the initial release of SQL Server 2008 SP3.

Those who are facing severe issues with their environment, they can plan to test CU13 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP4 final release to deploy on your production environment, to have consolidate build.

KB Article For CU13 of SQL Server 2008 SP3

§ CU#13 KB Article: http://support.microsoft.com/kb/2880350

Previous Cumulative Update KB Articles of SQL Server 2008 SP3:

§ CU#12 KB Article: http://support.microsoft.com/kb/2863205

§ CU#11 KB Article: http://support.microsoft.com/kb/2834048

§ CU#10 KB Article: http://support.microsoft.com/kb/2814783

§ CU#9 KB Article: http://support.microsoft.com/kb/2799883

§ CU#8 KB Article: http://support.microsoft.com/kb/2771833

§ CU#7 KB Article: http://support.microsoft.com/kb/2738350

§ CU#6 KB Article: http://support.microsoft.com/kb/2715953

§ CU#5 KB Article: http://support.microsoft.com/kb/2696626

§ CU#4 KB Article: http://support.microsoft.com/kb/2673383

§ CU#3 KB Article: http://support.microsoft.com/kb/2648098

§ CU#2 KB Article: http://support.microsoft.com/kb/2633143

§ CU#1 KB Article: http://support.microsoft.com/kb/2617146

If you liked this post, do like on facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)