Do you load .csv files into excel from perfmon (blg files)? I took Brent Ozar's suggestion to add the min, max, and average functions. Thanks Brent! However, I wanted to know when (time of day) the counter hit the min and max values. For example, what is the excel cell address of the absolute value of the max function? In Figure B below the counter "\SQLServer:SQL Statistics\Batch Requests/sec" occurred at 10:30PM. No, it's not a screamer.
The steps I took:
- Capture perfmon data into a blg file (can also be created in a csv file format)
- Convert it to a csv file with relog.exe (a post for another day, but a very handy tool)
- Open the csv file in excel
- Insert 5 rows at the top of the spreadsheet
- Label the rows using the text in the heading column below or your own
- Paste the formula in the cell address into B1, B2, etc...
- Change the data range in cells B1 and B2 to match your csv file. My data range was B7:B788. I excluded row 7 because there was no data in the file.
Be careful when copying cell contents and using relative or absolute cell addresses. e.g. B1 vs $B1 vs B$1 vs $B$1
Sample excel function to return cell address of min and max values:
| cell address | heading | formula result | formula |
|---|---|---|---|
| B1 | min cell address | $B$36 | =CELL("address",OFFSET(B6,MATCH(ABS(B3),B8:B788,0)*1,0)) |
| B2 | max cell address | $B$208 | =CELL("address",OFFSET(B6,MATCH(ABS(B4),B8:B788,0)*1,0)) |
| B3 | min function | 1,089.16 | =MIN(B$8:B$788) |
| B4 | max function | 115,378.09 | =MAX(B$8:B$788) |
| B5 | average function | 14,531.68 | =AVERAGE(B$8:B$788) |
| B6 | perfmon counter title | ||
| B7 | first row of data | ||
| B788 | last row of data |
Figure A. Excel layout manual
Excel graphic of perfmon data. Note the data range's don't match. Pretend data range B8:B85496 is really B8:B788 and it's all good again.
You can also get min and max values and time time of day by sorting / filtering. I found this to be too much clicking around. Yet another way to do this is to load it into a MSSQL table. So many ways and so little time. There are some really great authors that I've learned tons from. However, I cut my teeth with SQL Server Central.
Cheers! I will drink to that.
r
