{"id":6947,"date":"2018-12-27T17:42:40","date_gmt":"2018-12-27T17:42:40","guid":{"rendered":"https:\/\/dbtut.com\/?p=6947"},"modified":"2018-12-27T17:45:43","modified_gmt":"2018-12-27T17:45:43","slug":"find-the-logical-and-physical-database-source-that-is-experiencing-io-problem","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/","title":{"rendered":"Find the logical and physical database source that is experiencing IO problem"},"content":{"rendered":"

 <\/p>\n

Interesting title, right?<\/p>\n

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.<\/p>\n

SELECT * FROM sys.dm_exec_requests WHERE session_id>50<\/pre>\n
or<\/p>\n
SELECT * FROM sys.sysprocesses WHERE spid>50 and status<>'sleeping'<\/pre>\n
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.<\/p>\n
Using these two queries, you can find details of this object that is currently causing waiting due to the IO resource.<\/p>\n
From the File and Page IDs, you can find the ID, hence the name, and the problematic records of this object.<\/p>\n
I’ve run a code in a SQL Server Instance to capture a sample IO wait:<\/p>\n
SELECT * FROM sys.dm_exec_requests WHERE session_id>50 AND wait_type LIKE 'PAGE%'<\/pre>\n
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.<\/p>\n
<\/p>\n
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.<\/p>\n
In summary,\u00a0PAGEIOLATCH_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.<\/p>\n
Of course, at this time, the relevant SPID is waiting for the read operation in the Suspended state on the Waiter List.<\/p>\n
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.<\/p>\n
34 is the database ID, you can find the name of the database from the database ID with a query like this:<\/p>\n
SELECT DB_NAME(34)<\/pre>\n
4 is the ID of the database file, ie File ID. You can find detailed information about the file with the following query.<\/p>\n
USE Related_Database\r\nGO\r\nSELECT * FROM sys.sysfiles WHERE fileid = 5<\/pre>\n
58919240 is the Page ID that we expect to transfer from disk to RAM.<\/p>\n
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:<\/p>\n
DBCC TRACEON(3604)<\/pre>\n
Then, with the DBCC PAGE command, you can access the details for that page:<\/p>\n
DBCC PAGE(34, 4, 58919240, 3)<\/pre>\n
After you run this command, you will see the following details:<\/p>\n
<\/p>\n
In the “Metadata: ObjectId” section, you can see the ID of the corresponding table.<\/p>\n
You can also find the name of this table with the following command:<\/p>\n
SELECT OBJECT_NAME(58919240, 34)<\/pre>\n
<\/p>\n
You must have sysadmin privilege to run the DBCC page command.<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
  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 or SELECT * FROM sys.sysprocesses WHERE spid>50 and status<>’sleeping’ Suppose we have a very large …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":423,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[1585,1584,1583,1586],"class_list":["post-6947","post","type-post","status-publish","format-standard","","category-mssql","tag-dbcc-page","tag-dbcc-traceon3604","tag-metadata-objectid","tag-sysfiles"],"aioseo_notices":[],"yoast_head":"\nFind the logical and physical database source that is experiencing IO problem - Database Tutorials<\/title>\n<meta name=\"description\" content=\"Find the logical and physical database source that is experiencing IO problem\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Find the logical and physical database source that is experiencing IO problem - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Find the logical and physical database source that is experiencing IO problem\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-12-27T17:42:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-12-27T17:45:43+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/130.png\" \/>\n<meta name=\"author\" content=\"Ekrem \u00d6nsoy\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ekrem \u00d6nsoy\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/\"},\"author\":{\"name\":\"Ekrem \u00d6nsoy\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/42a3e874ed0bf8053fa123a3a1f6ee46\"},\"headline\":\"Find the logical and physical database source that is experiencing IO problem\",\"datePublished\":\"2018-12-27T17:42:40+00:00\",\"dateModified\":\"2018-12-27T17:45:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/\"},\"wordCount\":431,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/130.png\",\"keywords\":[\"DBCC PAGE\",\"DBCC TRACEON(3604)\",\"Metadata: ObjectId\",\"sysfiles\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/\",\"name\":\"Find the logical and physical database source that is experiencing IO problem - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/130.png\",\"datePublished\":\"2018-12-27T17:42:40+00:00\",\"dateModified\":\"2018-12-27T17:45:43+00:00\",\"description\":\"Find the logical and physical database source that is experiencing IO problem\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/130.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/130.png\",\"width\":303,\"height\":61},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Find the logical and physical database source that is experiencing IO problem\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/42a3e874ed0bf8053fa123a3a1f6ee46\",\"name\":\"Ekrem \u00d6nsoy\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/478b3dc5048390d7312367b30eee5be99c346dbd83706e82e0dd4e9678b77c94?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/478b3dc5048390d7312367b30eee5be99c346dbd83706e82e0dd4e9678b77c94?s=96&d=mm&r=g\",\"caption\":\"Ekrem \u00d6nsoy\"},\"description\":\"The original article was written in Turkish by Ekrem \u00d6nsoy and translated to English by dbtut with the consent of the author. The copyright of the article belongs to the author. The author shall not be liable in any way for any defect caused by translation.\",\"url\":\"https:\/\/dbtut.com\/index.php\/author\/ekremonsoy\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Find the logical and physical database source that is experiencing IO problem - Database Tutorials","description":"Find the logical and physical database source that is experiencing IO problem","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/","og_locale":"en_US","og_type":"article","og_title":"Find the logical and physical database source that is experiencing IO problem - Database Tutorials","og_description":"Find the logical and physical database source that is experiencing IO problem","og_url":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/","og_site_name":"Database Tutorials","article_published_time":"2018-12-27T17:42:40+00:00","article_modified_time":"2018-12-27T17:45:43+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/130.png","type":"","width":"","height":""}],"author":"Ekrem \u00d6nsoy","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Ekrem \u00d6nsoy","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/"},"author":{"name":"Ekrem \u00d6nsoy","@id":"https:\/\/dbtut.com\/#\/schema\/person\/42a3e874ed0bf8053fa123a3a1f6ee46"},"headline":"Find the logical and physical database source that is experiencing IO problem","datePublished":"2018-12-27T17:42:40+00:00","dateModified":"2018-12-27T17:45:43+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/"},"wordCount":431,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/130.png","keywords":["DBCC PAGE","DBCC TRACEON(3604)","Metadata: ObjectId","sysfiles"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/","url":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/","name":"Find the logical and physical database source that is experiencing IO problem - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/130.png","datePublished":"2018-12-27T17:42:40+00:00","dateModified":"2018-12-27T17:45:43+00:00","description":"Find the logical and physical database source that is experiencing IO problem","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/130.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/130.png","width":303,"height":61},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/27\/find-the-logical-and-physical-database-source-that-is-experiencing-io-problem\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Find the logical and physical database source that is experiencing IO problem"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/42a3e874ed0bf8053fa123a3a1f6ee46","name":"Ekrem \u00d6nsoy","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/478b3dc5048390d7312367b30eee5be99c346dbd83706e82e0dd4e9678b77c94?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/478b3dc5048390d7312367b30eee5be99c346dbd83706e82e0dd4e9678b77c94?s=96&d=mm&r=g","caption":"Ekrem \u00d6nsoy"},"description":"The original article was written in Turkish by Ekrem \u00d6nsoy and translated to English by dbtut with the consent of the author. The copyright of the article belongs to the author. The author shall not be liable in any way for any defect caused by translation.","url":"https:\/\/dbtut.com\/index.php\/author\/ekremonsoy\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/6947","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/423"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=6947"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/6947\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=6947"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=6947"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=6947"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}