For the past week my CRM application has been misbehaving. Users are complaining that it is slow. We’ve not done anything lately so we’re not sure what is going on.
In the let’s waste some time (and money) department our BA decided that sitting in the call center watching the users and using a stopwatch would be a good idea. Other than providing no objective information about what the performance is, sure. Watching the users told me that they aren’t always doing what they are trained to do. But it doesn’t tell me anything really useful that will address this problem. Unfortunately I’m involved in 4 projects all at once so I didn’t really have time to address this.
Anyway, IIS logs every interaction with the web browsers. It also tells me how long it took to process a requests. Perfect. Only problem is that the log files are usually about three hundred and fifty meg! Not a job for Excel or notepad. I looked around at some of the tools available, but I don’t have budget and the free ones didn’t really do it for me.
What’s that? Blast the data in to SQL Server and use a Pivot Table in Excel? Brilliant! How do I do that?
Go here: How To Use SQL Server to Analyze Web Logs
Basically they tell you how to create a table to hold the data in the log file and then how to use BULK INSERT to read the log file. In my case my log file didn’t have the same columns as the example so I had to change the table around a bit. I also had to chop off the first few lines of the log file to get to the real data. But once that was done I was able to pull the data in without any trouble.
The result: I have real objective data and know that occasionally the system is slow. In one case it took nearly 1 minute to respond! However, a quick query told me that it was very rare for the user to get a response that took more than 3 seconds (less than .5%) but unfortunately sense you are in the application all day – you are going to get a slow response eventually.
The fun part now begins. I gotta correlate the slow responses to other things happening in the system. Go look at the EventLog, SQL Server, turn on tracing in CRM, etc. Somewhere something is happening to cause this.