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 = 'whatever@whatever.com' ; " 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?
Best Answer
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.