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