Home / server / Questions / 408875
In Process
Keeno
Asked: 2012-07-19 07:15:39 +0800 CST

Why is SQL Server limiting speed to/from one database from IIS

  • 772

I am having troubles with SQL server (or IIS, but I am pretty sure its SQL)

I have posted before with a more vague question, however its still happening and I have some more detailed information.

Previous Post

The problem: SQL Server 2008r2 to Website (iis 7) appears to be limiting data transfer speed when the IIS website tries to access it. The speed appears to get limited to 1Mb per second.

This fixes itself if I restart the database service, or take the DB offline, but I would rather not have to do this every week.

Some trouble shooting:

  • File transfers from the same machines are fine, so I don't think its the network.
  • I can connect from my local SQL Management studio, and query the SAME database fine
  • Other websites on the same IIS server to the SQL server do not suffer talking to other DBs
  • Pointing our dev server to the live troublesome database has the same issues
  • Taking a copy of the database and putting that on another SQL doesn't have the issue

So, I think I have whittled it down to one Database within SQL seems to be having intermittent troubles transferring data to IIS.

  • Any ideas what would be causing this?
  • Why would it happen after a few days of working normally?
  • Where can I start trouble shooting?

EDIT 1: NEW INFO I have done some more tests, it seems that it is one specific Stored Procedure that, only sometimes, is only using 10% of the network speed.

When I was fiddling around with the SP, doing nothing other than "tweaking it" seemed to break the bottleneck and thus it started working normally again.

Help, this is driving me mad!

Thank you

sql-server networking iis

2 Answers

  1. Pointing our dev server to the live troublesome database has the same issues

    That indicates that it is an issue with that particular web app, since you say other web apps don't have this problem. Unless those other web apps use radically different database data, performance should be at least comparable.

    What you want to do is run a server-side trace on the database, and capture only the duration and rowset size for each SQL call from that server - and possibly from another server for comparison.

    You can then plot this against perfmon captured data for SQL server and the IIS machine - yes, SQL Server Profiler fully supports this scenario :)

    This will yield zoom-, scroll-, and clickable graphs for all the data you put in; refer to SQL Server Profiler help (BOL) for details on how to set this up.

    • 1

  2. If it's a single SP that seems to be the problem, and 'tweaking' it appeared to resolve the issue, the problem is probably down to the compiled execution plan - it may have been compiled for a set of parameters that cause it to run slower for other, more common sets of parameters.

    Next time you have the problem, try running a dbcc freeproccache and see if that resolves it. If it does, you could add a WITH RECOMPILE hint to the SP itself to stop the plan being cached.

    • 1