up vote 0 down vote favorite
share [g+] share [fb]

First time OP on serverfault, but an avid user of stackoverflow. We have two high-specification boxes, both running Windows Server 2008 R2 Standard.

On one we have IIS 7.5, the other SQL Server 2008 R2. We're running a typical web-based setup running older classic ASP sites and newer ASP.NET 2.5 and 3.5 sites (about 50 sites in total). I've been asked to investigate the slowness of the sites (primarily ASP, but sometimes ASP.NET).

I have checked SQL Server and the configuration and done some basic profile tests and direct page tests on the SQL server (running an IIS based page on the database server directly) to find that I really don't think it is SQL. I turned my attention to IIS and it seems to be fine also.

My thoughts are:

  1. Latency between the servers. THey are in same datacentre, though I can't see either server within their respective networks when I RDP in.
  2. Authentication or security between the servers?

Has anyone any pointers as to where to look/start and/or my theories the right path to follow?

Help appreciated.

Chris

link|improve this question
Presume slowness defined as time between click/enter and page returning. Servers on same sub-net in datacenter? Assume you've done a tracert between server to see # of hops. – jl. Sep 1 '10 at 14:57
Hi jl, thanks for the response. They are on same sub-net in datacentre I assume - only 1 hop on the tracert - to a switch and on I assume? Very fast <30ms, but pages take upto 6-8 seconds to load on some occasions... – Chris Laythorpe Sep 1 '10 at 14:59
Have you run the ASP.NET pages in a debugger to see what line (or lines) are causing the problem within the .NET code? – mrdenny♦ Sep 1 '10 at 18:26
Thanks for the reply mrdenny. The problem isn't the .NET code, I am fairly sure of that. It follows Patterns & Practices and when run locally on the SQL Server, is lightening fast. When run locally on the IIS server (with localhost DB connection) it is also lightening fast. So I can be sure to rule out the .NET, leaving the issues listed in the OP... Any clues? – Chris Laythorpe Sep 2 '10 at 10:22
Can you explain how you ruled out the SQL instance from the equation. The symptoms point to some bottleneck that is related to load. Is there data being updated/inserted/deleted during the time that the latency is experienced? If you have not defined a SQL trace logging to a file local to the SQL Instance then that should be one of your first steps. You should log this way to minimize impact of the trace. Then look for slow response times in the trace. – doug_w Sep 2 '10 at 13:38
feedback

1 Answer

up vote 0 down vote

I know that you say you don't think it's the SQL server - but try something for me to rule that out. During a busy part of your day run this:

USE master
select text,wait_time,blocking_session_id AS "Block",
percent_complete, * from sys.dm_exec_requests 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  AS s2 order by start_time asc

That query will show you everything that is actively running (queries, sprocs, etc) on the SQL box right that second. If you notice an abundance of a single query, or a query with long running times it could point you in the direction of the problem. The resolution to such a problem could be indexes, reindexing, re-writing poorly designed queries, etc. If you find nothing worth mentioning then start looking on the other places. I have a question, what did you mean by this:

THey are in same datacentre, though I can't see either server within their respective networks when I RDP in.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.