{"id":6351,"date":"2019-03-10T17:30:11","date_gmt":"2019-03-10T17:30:11","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6351"},"modified":"2019-11-28T07:04:14","modified_gmt":"2019-11-28T07:04:14","slug":"sql-server-performance-file-io-statistics","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-performance-file-io-statistics\/","title":{"rendered":"SQL Server Performance File IO Statistics"},"content":{"rendered":"<p>Hi,<\/p>\n<p>Disk IO Statistics are very crucial for SQL Server Performance criteria.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5125\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/troubleshoot.png\" alt=\"\" width=\"800\" height=\"400\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/troubleshoot.png 800w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/troubleshoot-300x150.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/troubleshoot-768x384.png 768w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/p>\n<p><!--more--><\/p>\n\n<p>Every transaction in SQL Server makes IO to bring data from physical disk to the buffer and just the opposite when Transaction is completed, all related data will transfer from buffer to the physical disk.<\/p>\n<p>So Disk speed and IO stats are very important for Performance criteria.<\/p>\n<p>To see IO stats and following information you can execute below script.<\/p>\n<ul>\n<li>Machine name<\/li>\n<li>Instance name<\/li>\n<li>SQL Server name<\/li>\n<li>Database name<\/li>\n<li>Logical name<\/li>\n<li>Physical name<\/li>\n<li>Disk drive<\/li>\n<li>File type<\/li>\n<li>State<\/li>\n<li>Size on disk<\/li>\n<li>Growth<\/li>\n<li>Number of reads<\/li>\n<li>Number of writes<\/li>\n<li>IO_stall_read_ms<\/li>\n<li>IO_stall_write_ms<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n\n<pre>select \r\nserverproperty('MachineName') 'machine_name'\r\n,isnull(serverproperty('InstanceName'),'mssqlserver') 'instance_name'\r\n,@@SERVERNAME 'sql_server_name'\r\n,DB_NAME(mf.database_id) 'database_name'\r\n,mf.name 'logical_name'\r\n,mf.physical_name 'physical_name'\r\n,left(mf.physical_name,1) 'disk_drive'\r\n,mf.type_desc 'file_type'\r\n,mf.state_desc 'state'\r\n,case mf.is_read_only\r\nwhen 0 then 'no'\r\nwhen 1 then 'yes'\r\nend 'read_only'\r\n,convert(numeric(18,2),convert(numeric,mf.size)*8\/1024) 'size_mb'\r\n,divfs.size_on_disk_bytes\/1024\/1024 'size_on_disk_mb'\r\n,case mf.is_percent_growth\r\nwhen 0 then cast(convert(int,convert(numeric,mf.growth)*8\/1024) as varchar) + ' MB'\r\nwhen 1 then cast(mf.growth as varchar) + '%'\r\nend 'growth'\r\n,case mf.is_percent_growth\r\nwhen 0 then convert(numeric(18,2),convert(numeric,mf.growth)*8\/1024)\r\nwhen 1 then convert(numeric(18,2),(convert(numeric,mf.size)*mf.growth\/100)*8\/1024)\r\nend 'next_growth_mb'\r\n,case mf.max_size\r\nwhen 0 then 'NO-growth'\r\nwhen -1 then (case mf.growth when 0 then 'NO-growth' else 'unlimited' end)\r\nelse cast(convert(int,convert(numeric,mf.max_size)*8\/1024) as varchar)+' MB'\r\nend 'max_size'\r\n\r\n,divfs.num_of_reads\r\n,divfs.num_of_bytes_read\/1024\/1024 'read_mb'\r\n,divfs.io_stall_read_ms\r\n\r\n,divfs.num_of_writes\r\n,divfs.num_of_bytes_written\/1024\/1024 'write_mb'\r\n,divfs.io_stall_write_ms\r\n\r\nfrom sys.master_files as mf\r\nleft outer join sys.dm_io_virtual_file_stats(null,null) as divfs\r\non mf.database_id=divfs.database_id and mf.file_id=divfs.file_id;\r\n\r\n\r\n\r\n<\/pre>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n<p><a href=\"https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/\">https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, Disk IO Statistics are very crucial for SQL Server Performance criteria.<\/p>\n","protected":false},"author":1,"featured_media":5125,"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,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3828,2227,3827],"tags":[1028,1031,1600,1610,2231,2349,2366,2367,2364,2365,2230,2229],"class_list":["post-6351","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-scripts","category-sql-server-2","category-sql-server-dba-scripts","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-sql-server-2012","tag-sql-server-2014","tag-sql-server-2019","tag-sql-server-databases-cpu-usage-stats","tag-sql-server-file-io-statistics","tag-sql-server-io-statistics","tag-sql-server-performance-file-io-statistics","tag-sql-server-performance-io-statistics","tag-sql-server-useful-query","tag-sql-server-useful-script"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/troubleshoot.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6351","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=6351"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6351\/revisions"}],"predecessor-version":[{"id":12963,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6351\/revisions\/12963"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/5125"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6351"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6351"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6351"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}