Hello! Hows it going? Alright the guy who has to go to the restroom, go now, make some popcorn and come back. Good now that were all cozy i’m going to explain this series.

I’m going to in a series of posts walk you through the initial steps of what I like to call Performance Refactoring. I refer to it as such because I am not going to concentrate on just the code, just the app, just the web server etc. We’re going to look at it all. This is necessary with today’s technologies for a lot of reasons, but for enterprise level applications to not do performance checking can be deadly.

So on that ominous note!
Here is a break down of the parts we’ll be going over in this series.

1. Gathering Metrics
2. Interpreting Metrics (This one comes with the bonus feature “WMI and You”)  Of course since everyone wants real world examples I will also show you how to use gathered data to figure out your drives load capacity, and theoretical saturation point.
3. Aggregating Metrics (Or The Cleanup)
4. Building a display module to graph gathered metrics.
5. Using metrics to identify bottle necks.
6. Hardware and code or love hate relationship.
7. Refactoring SQL
8. Analyzing Query execution plans.
9. Understanding cardinality, cost based execution and your app.
10. Code Optimization.

Whew! We got a lot to cover in the next few weeks so might as well get started.
So the first step is going to be gathering metrics. Now for a lot of people I am going to applogize now, these series are going to use a lot of windows technologies. I would recommend that if your using Linux or Solaris that you investigate Nagios, although the methodologies I lay forward should still apply.

One of the common tools used in Performance evaluation on Windows Servers (and in fact is available on XP and Vista) is called perfmon. If you’ve never used go to START > RUN or press WIN + R on the keyboard and type perfmon.

Perfmon is a graphical manager that ties into the Win32 Raw Performance classes. It pulls all the counter data it collects and “cooks” it for you displaying it in a nice easy to see graph. Feel free to Right click and look at all the nice counters available to you.

Now for this exercise I am going to focus on only 3: Processor::Total Time, LogicalDisk::TransferPerSec and PhysicalDisk::TransferPerSec. These are not the only important ones but they will do for now.

So seeing this information is extremely helpful to diagnose system problems. However, what if you want to test the system and record the results? Well you can save it to a log I suppose. However, lets go further. What if you want to persist those results and save them for comparrison to see if your changes are positive, negative, or somewhere in between? Heck what if you want to make it viewable and in a web application where you could log on and compare different builds or scripts? Now were talking!

All right so the secret to getting this whole thing to work is about picking the right ODBC provider.  You want a system DSN and a SQL Server client, not a native SQL server.  Also for future reference you can setup the ODBC connection to point to a different server so you can have multiple remote systems sending to one core server, granted of course that you have security rights.

All right, so here is the chain of events to follow to get this whole thing setup. now these instructions are for Windows 2003 Server, 2008 and vista are a little different in Perfmon but the core logic stays the same:

1. Create Empty Database in SQL
2. Start > Control Panel > Administrative Tools > Data Sources
3. System DSN
4. Add
5. SQL Server (Not Native Client)
6. Name / Desc what you want
7. Select your server > Next
8. Client Config No Dynamic. Set 1433
9. Ok > Next
10. Change Default DB to empty database you made
11. Next > Test data Source > Ok
12. Run OR WIN + R > Enter Perfmon
13. Counter Logs > Right Click “New Log Settings”
14. Type Name
15. Add Objects (Processor, Memory, Physical Disk)
16. Run As (Administrator and set password)
17. Log Files Tab > Log File Type = SQL Database > Configure
18. System DSN select the ODBC connection you configured.
19. Click Ok
20. If New Log doesn’t turn green left click and hit Run
21. Database Schema
    1. a. CounterDetails = Counter Names / Types (CounterID)
    2. b. counterData = Counter Values / Time (CounterID)

All right, thats it. You now have a database that is collecting server metrics for you.  This means you could go off and load test that server and have recorded hard numerical value to use when your boss says “Hey when I have 2000 users on my site is it slowing down??” or my personal favorite “Can we support Project X with  antiquated hardware Y?”

Now if you got this setup you can start to answer that question.  We’ll cover more of the basics in the next part!