Performance Issue Between SQL and IIS on Seperate Machines

Hi there,

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

From serverfault Chris Laythorpe

• 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.

  From Dave Holland