SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mar
Starting Member

45 Posts

Posted - 01/15/2013 :  08:42:20  Show Profile  Reply with Quote
Hello

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?

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/15/2013 :  19:02:54  Show Profile  Reply with Quote
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.
Go to Top of Page

Mar
Starting Member

45 Posts

Posted - 01/16/2013 :  11:09:40  Show Profile  Reply with Quote
Thanks for your reply.

The profiler was able to help me. Here's a good (and quick) tutorial for using the profiler.
http://thefirstsql.com/2011/01/07/sql-server-profiler-a-tutorial/

Thanks again
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/16/2013 :  12:03:29  Show Profile  Reply with Quote
Thank you for the link, Mar. I am glad you were able to figure out the issue.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000