I've got a server hosted at Rackspace that is a pretty beefy machine (dual hex core, 48 GB of RAM) and our database isn't all that big (100GB or so). SQL Server 2008 R2 Standard 64 bit.
Normally, this machine runs perfectly. Very little pegging of any CPU cores, query response times are great, etc.
However, I can kill this machine's response times by running a query of "SELECT * FROM RawLead WHERE emailAddress = '[email protected]' ; " EDIT: And no, emailAddress is not indexed. Yes, I know that would help this specific query, but the original problem would still remain -- how 1 query can bring the entire server to a halt.
When I do this, SQL Server stops responding to any other queries until this result set is returned.
I just tried this, and as you can see from this screen capture from the Resource Monitor Disk activity changed, CPU didn't really, but the network traffic dropped to basically zero.
Is there a setting in SQL Server that would affect this?
Sounds like expected behavior. If someone actually used a query this hideously bad, I would hope they would at least have the common courtesy to combine it with the "WITH (NOLOCK)", which is the same as specifying an Isolation Level of Read Uncommitted.
If this is a large table, and there are no indexes, then you're going to get a table scan. Further, if the optimizer thinks the cost of the query is high enough, you may be seeing parallel execution. Between a table scan and parallel execution, yeah, you could be spiking the CPU. Then you can toss in blocking, resource contention, multiple users, and it all just gets worse.