Find the logical and physical database source that is experiencing IO problem

Ekrem Önsoy

6 years ago

Interesting title, right?

We may want to see the processes that are currently running, especially when there are problems. In these cases you can use the sys.dm_exec_request DMV and the sys.sysprocesses Catalog View.

SELECT * FROM sys.dm_exec_requests WHERE session_id>50

1	SELECT * FROM sys.dm_exec_requests WHERE session_id>50

SELECT * FROM sys.sysprocesses WHERE spid>50 and status<>'sleeping'

1	SELECT * FROM sys.sysprocesses WHERE spid>50 and status<>'sleeping'

Suppose we have a very large table. Because a very important Index of this table is missing, this table is experiencing much PAGEIOLATCH_SH waiting when reading data with SELECT.

Using these two queries, you can find details of this object that is currently causing waiting due to the IO resource.

From the File and Page IDs, you can find the ID, hence the name, and the problematic records of this object.

I’ve run a code in a SQL Server Instance to capture a sample IO wait:

SELECT * FROM sys.dm_exec_requests WHERE session_id>50 AND wait_type LIKE 'PAGE%'

1	SELECT * FROM sys.dm_exec_requests WHERE session_id>50 AND wait_type LIKE 'PAGE%'

This query gave me the following result. I could also write a similar command using sys.sysprocesses. It would give me the same result in wait_resource.

Please don’t look at the small wait_time value here. As I said, I’m just showing as an example. This time will be much longer when there is a problem.

In summary, PAGEIOLATCH_SH tells us that the page in which the record is in query is not currently in RAM, is being read from the disk and transferred to RAM.

Of course, at this time, the relevant SPID is waiting for the read operation in the Suspended state on the Waiter List.

What I want you to focus here is the value in the wait_resource field. Let me explain the value “34:4:58919240” you see here.

34 is the database ID, you can find the name of the database from the database ID with a query like this:

SELECT DB_NAME(34)

1	SELECT DB_NAME(34)

4 is the ID of the database file, ie File ID. You can find detailed information about the file with the following query.

USE Related_Database
GO
SELECT * FROM sys.sysfiles WHERE fileid = 5

USE Related_Database

SELECT * FROM sys.sysfiles WHERE fileid = 5

58919240 is the Page ID that we expect to transfer from disk to RAM.

The DBCC PAGE is a command that has not been officially documented by Microsoft, but has been in SQL Server for many years. To use this command, you must enable Trace Flag 3604 in the related session as follows:

DBCC TRACEON(3604)

1	DBCC TRACEON(3604)

Then, with the DBCC PAGE command, you can access the details for that page:

DBCC PAGE(34, 4, 58919240, 3)

1	DBCC PAGE(34, 4, 58919240, 3)

After you run this command, you will see the following details:

In the “Metadata: ObjectId” section, you can see the ID of the corresponding table.

You can also find the name of this table with the following command:

SELECT OBJECT_NAME(58919240, 34)

1	SELECT OBJECT_NAME(58919240, 34)

You must have sysadmin privilege to run the DBCC page command.