{"id":409,"date":"2014-04-08T10:48:30","date_gmt":"2014-04-08T15:48:30","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=409"},"modified":"2014-04-08T10:48:30","modified_gmt":"2014-04-08T15:48:30","slug":"creating-sql-server-performance-based-reports-using-excel","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/creating-sql-server-performance-based-reports-using-excel\/","title":{"rendered":"Creating SQL Server performance based reports using Excel"},"content":{"rendered":"

I\u2019m not a big fan of Performance Monitor but in this post we will review the steps in how to export the results so they can be read more easily in Excel.\u00a0 This post will not discuss how to setup a data collector set and will assume the reader already has results saved to the file system in a .blg file format.<\/p>\n

If you\u2019ve ever used PerfMon you\u2019ve probably noticed .blg files. These are the files that hold all of our performance data that we have collected over the past week. When you open this file in PerfMon it can be tedious work to get the data in a presentable form. Actually, I don\u2019t know if there\u2019s a good way to get this data in a presentable form using the PerfMon\u2019s GUI.<\/p>\n

\"SQL<\/a><\/p>\n

First thing we need to do is to convert the .blg to .csv so we can open it in Excel. To convert this file open command prompt and navigate to the directory the file is located and type:<\/p>\n

Relog SQL_BASELINE_20140128.blg \u2013f CSV \u2013o NewFile.csv<\/i><\/p>\n

\"SQL
\n<\/a>Once the command completes successfully, you should see the new file in the same directory:<\/p>\n

\"SQL<\/a><\/p>\n

Open NewFile.csv in Excel:<\/p>\n

\"SQL<\/a><\/p>\n

Not pretty huh? First thing we need to do is format the first column\u2026.which is the Date column. Change the text in the A1 cell from PDH-CSV 4.0\u2026. to DateTime:<\/p>\n

\"SQL<\/a><\/p>\n

Next, remove Row 2. We do not need this data. Also, highlight column A and right click anywhere in the column and choose Format Cells:<\/p>\n

\"SQL<\/a><\/p>\n

On the number tab, click the Data Category and select 3\/14\/2012 1:30PM:<\/p>\n

\"SQL<\/a><\/p>\n

Press Ctrl+Home to select the A1 cell.<\/p>\n

Create Pivot Chart<\/b><\/p>\n

Click on the Insert tab and select Pivot Chart:<\/p>\n

\"SQL<\/a><\/p>\n

The Pivot Chart dialog box should automatically select the correct cells to analyze. If not, make sure all of the cells are selected:<\/p>\n

\"SQL<\/a><\/p>\n

Once the new worksheet opens, drag DateTime to the Axis Fields pane:<\/p>\n

\"SQL<\/a><\/p>\n

Now, depending on what counter(s) you want to analyze, drag it down to the Values pane. For this example, I\u2019ll analyze Processor(_Total)\\% Processor Time:<\/p>\n

\"SQL<\/a><\/p>\n

Ta da! You now have a graph that displays your Processor %.<\/p>\n

\"SQL<\/a><\/p>\n

To make this presentable, simply format the graph to your liking and you\u2019re done.<\/p>\n","protected":false},"excerpt":{"rendered":"

I\u2019m not a big fan of Performance Monitor but in this post we will review the steps in how to export the results so they can be read more easily in Excel.\u00a0 This post will not discuss how to setup a data collector set and will assume the reader already has results saved to the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[66,9,97],"class_list":["post-409","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","tag-excel","tag-perfmon","tag-performance-tuning"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/409","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=409"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/409\/revisions"}],"predecessor-version":[{"id":422,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/409\/revisions\/422"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=409"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=409"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=409"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}