I am a programmer, not a dba. We are having some very poor response from one of our forms. The code was writen by an outside vendor. I wanted to know how to tell if the problem is in the client code or on the server.
Can I look at the server with taskman to see if the cpu or network are maxed out? Would that mean the problem is there? If neither of those resources are straining then does that mean the client's code is poorly written (which is what I believe)?
The form takes between 1 and 2 minutes to load and it doesn't show a lot of data. 1 record with 1 or more child records with 0 or more grandchild records.
I can type the query in SSMS and it comes back in 5 seconds (and I believe there is no data cached because subsequent runs bring the data back in 1 second).
The primary key is a guid but the data is looked up by a human readable number. I created an index on this field but it had no discernable effect.
We do have a raid on the sql server, I do not remember which kind.
Is there any way I can isolate the bottleneck? Any good links for recommended reading?
The fact that you can run the query from SSMS and get the results substantially faster than when run from the client application indicates to me that the issue is with the client application (or perhaps the network, if the amount of data transferred is large). If you don't have the source code for the client app (or even if you have the source code), ask the vendor for help if possible.
You can run SQL Profiler and watch the queries that come into the server and measure how much time is taken by those queries to reconfirm that hypothesis.
You may also want to examine whether the database is maintained properly. Mainly whether indexes are fragmented and whether statistics are updated (google for how to do those).
Perfmon has a number of counters associated with SQL Server that will allow you to measure various performance parameters of the Server. But before you do all that, do the things I listed above.