{"id":417,"date":"2012-12-15T12:00:32","date_gmt":"2012-12-15T19:00:32","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=417"},"modified":"2012-12-06T17:27:04","modified_gmt":"2012-12-07T00:27:04","slug":"parse-and-sort-set-statistics-io-output-with-excel","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2012\/12\/parse-and-sort-set-statistics-io-output-with-excel\/","title":{"rendered":"Parse and sort SET STATISTICS IO output with Excel"},"content":{"rendered":"

I wish I could say this was my idea. Somebody posted it on twitter awhile back and I did the classic \"facepalm\". I have no idea how many times I manually picked apart the results of SQL Server's SET STATISTICS IO ON without considering creating a formula to handle it for me. So, somebody came up with the brilliant idea to use a \"computer\" to complete a repetitive, mundane task. That person was not me. I only managed to remember to google how to do it (or as Matt Roche<\/a> was fond of saying at the PASS Summit: \"google it on Bing\").<\/p>\n

A brief aside: am I the only one who feels like they have to say \"SET STATISTICS IO ON\" in a robot voice? Which then leads my stream of consciousness thinking to Short Circuit<\/a> which of course leads me to mutter \"Hey laser lips! Your mama was a snow blower!\" This then completes my coworkers impression that I am completely unstable. And now back to our regular programming...<\/p>\n

As far as I know all the credit for the stroke of brilliance to parse and sort SQL's SET STATISTICS IO in Excel goes to Vicky Harp. You can find her post on how to do it over at her vickyharp.com<\/a>.<\/p>\n

Unfortunately, there were some minor typos with her original code that resulted in me just creating my own post so I could quickly find and use the formula's myself:<\/p>\n

Object Name\r\n=IFERROR(MID(A2,FIND(\"'\",A2,1)+1,FIND(\"'\",A2,FIND(\"'\",A2,1)+1)-FIND(\"'\",A2,1)-1),\"\")\r\n\r\nScan Count\r\n=IF(LEN(A2)&gt;0,IFERROR(MID(A2,FIND(\"Scan count\",A2,1)+10,FIND(\",\",A2,1)-FIND(\"Scan count\",A2,1)-10)+0,\"\"),\"\")\r\n\r\nLogical Reads \r\n=IF(LEN(A2)>0,IFERROR(MID(A2,FIND(\"logical reads\",A2,1)+13,FIND(\",\",A2,FIND(\"logical reads\",A2,1))-FIND(\"logical reads\",A2,1)-13)+0,\"\"),\"\")\r\n\r\nPhysical Reads \r\n=IF(LEN(A2)>0,IFERROR(MID(A2,FIND(\"physical reads\",A2,1)+14,FIND(\",\",A2,FIND(\"physical reads\",A2,1))-FIND(\"physical reads\",A2,1)-14)+0,\"\"),\"\")\r\n\r\nRead-ahead Reads\r\n=IF(LEN(A2)>0,IFERROR(MID(A2,FIND(\"read-ahead reads\",A2,1)+16,FIND(\",\",A2,FIND(\"read-ahead reads\",A2,1))-FIND(\"read-ahead reads\",A2,1)-16)+0,\"\"),\"\")\r\n\r\nLOB Logical Reads\r\n=IF(LEN(A2)>0,IFERROR(MID(A2,FIND(\"lob logical reads\",A2,1)+17,FIND(\",\",A2,FIND(\"lob logical reads\",A2,1))-FIND(\"lob logical reads\",A2,1)-17)+0,\"\"),\"\")\r\n\r\nLOB Physical Reads\r\n=IF(LEN(A2)>0,IFERROR(MID(A2,FIND(\"lob physical reads\",A2,1)+18,FIND(\",\",A2,FIND(\"lob physical reads\",A2,1))-FIND(\"lob physical reads\",A2,1)-18)+0,\"\"),\"\")\r\n\r\nLOB Read-ahead Reads\r\n=IF(LEN(A2)>0,IFERROR(MID(A2,FIND(\"lob read-ahead reads\",A2,1)+20,FIND(\".\",A2,FIND(\"lob read-ahead reads\",A2,1))-FIND(\"lob read-ahead reads\",A2,1)-20)+0,\"\"),\"\")<\/pre>\n
I've also attached an excel worksheet with the formulas already in it so if you want to be really lazy just download that from here: Statistics IO Parse Excel Sheet<\/a><\/p>\n
\"\"<\/a>
What do you mean I don't have to parse fifty lines SET STATISTICS IO output by hand?!?<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"
I wish I could say this was my idea. Somebody posted it on twitter awhile back and I did the classic \"facepalm\". I have no idea how many times I manually picked apart the results of SQL Server's SET STATISTICS IO ON without considering creating a formula to handle it for me. So, somebody came […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,86,43,8,3,57],"tags":[170,10,4,99,9,12,11,54,100,173,5],"class_list":["post-417","post","type-post","status-publish","format-standard","hentry","category-data-architect","category-dba","category-microsoft-sql-server","category-performance-tuning","category-scripts","category-t-sql","tag-excel","tag-ms-sql","tag-performance","tag-short-circuit","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-ssms","tag-statistics","tag-t-sql","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/417","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=417"}],"version-history":[{"count":6,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/417\/revisions"}],"predecessor-version":[{"id":423,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/417\/revisions\/423"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=417"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=417"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}