Timing requests to backend?

biko

Scenario is one web server with one app server and one DB behind that.
Now I want to time this somehow to see if code changes or SQL changes/stored procs etc makes the process (return results from DB onto webpage) faster?
Any good tools for this stuff?

amen

Well this interesting because you have a few variables at play.

1: Time to get/read the data on the database. There tools available in MS SQL and Oracle(not sure about MySql) that lets you see how the long the query took and how it searched for the data ie scanning a table (inefficient) instead of index seek ( very efficient). You need to google Execution Plans for what ever DB you are using. Big gains can be made in speed if your database is correctly tunned. Ohh you may have an efficient query but slow disks for the DB so you may also need to look at disk I/O. (Actually tunning a DB and the associated disks, data location etc can take weeks on a big system)

2: Time to get the data from the DB to the Server. Whats the network like between the DB and the server making the request ? Once you know the time to get the data from the DB (Point 1 above) its fairly simple to add some timing code to your call to the DB to figure out how long the data takes to get form the db to your server. If you are on the same box not a problem but your Application maybe taking valuable CPU/Memory from the DB making the DB slow!

3: How long does it take you application to process the data ? You might have a fast 1 and 2 but then performing some inefficient data processing on the server maybe concatinating 10,000 strings, inefficient looping or complex calculations etc. Again you can add timing to your own code.

4: Client side code. Are you doing some complex logic in javascript or creating a million dynamic controls and the page is slow to load? Again you can monitor your self. If you using client side script/controls you should monitor memory/cpu usage on the client and ensure there are no memory leaks. You should do this for all browsers that you support.

5: Network to your domain. Is the network to your domain sufficient? No point in your server having a 2MB connection if you are youtube

Re the timing in the code I suggest writing to a log file and have it configurable so you can turn it on/off. You should keep a baseline and review periodically.

Some good tools, the Tools that ship with MS SQL/Oracle, Fiddler, FireBug, WireShark, the sysinternals tools from MS etc

If you have specific elements you want to improve post a few of them.

amen

Just thought you also need to know whats running on your servers for instance Anti Virus software is generally a bad idea on your DB Server

biko

Thanks, what I'm after is something like ttfb I suppose. The time from I press send (HTTP request) until the page is fully loaded.
At the moment the performance is good but what I want to do is take some measurements now and then update html, stored procs and similar things over time and see if performance increases or decreases.

I have firebug already and like it, was thinking if there is anything else similar. Will look into Fiddler and Wireshark, heard about them before but never got around to use them yet.

kayos

AOP can be handy for things like this. Write an aspect that gets fired on the method entry and exits and write the times out to a file/event log/performance counter etc. Just make sure you logging code is async so you don't cause any performance issues due to concurrency in writing the logs.

Between the above and a trace/profile on the DB should should have all the stats you need on the server side of the divide.

Giblet

amen wrote: »

Just thought you also need to know whats running on your servers for instance Anti Virus software is generally a bad idea on your DB Server

PCI compliance auditors will look for anti virus on servers... Of course, the spec is for it to be installed, not actually running

OP: I use SQL Server Profiler built into Management studio. Also, you can analyse query plans generated for stored procedures.