0

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.

enter image description here

Is there a setting in SQL Server that would affect this?

Matt Dawdy
  • 429
  • 1
  • 9
  • 19
  • You reference the RawLead table which seems to be very large. Are queries to other tables affected as well? Are you using tracing? Can you run the SQL Server Profiler against the database? – the-wabbit Feb 09 '12 at 17:24
  • I'm not 100% sure if any other are. I'll check. Yeah, this table has 12mil rows, so it's pretty big. – Matt Dawdy Feb 09 '12 at 21:45
  • could you add the output of `EXEC sp_lock` executed while running your huge-result query? – the-wabbit Feb 09 '12 at 23:22
  • @syneticon-dj I did that but it's a lot to paste into my question. Should I look for something specific in that? Also, the result set shouldn't be all that big (maybe a couple hundred hits at most). – Matt Dawdy Feb 10 '12 at 19:57
  • Basically table locks of any kind. BTW, if the information is just too much to paste into the question, you could use a service like http://pastebin.com – the-wabbit Feb 10 '12 at 21:09

2 Answers2

1

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.

Greg Askew
  • 35,880
  • 5
  • 54
  • 82
  • Yet, it would not put an exclusive lock on the whole table. And especially not block other read requests. – the-wabbit Feb 09 '12 at 23:22
  • What's so bad about it? Assuming WITH(NOLOCK) is added, what's actually wrong with the query? Not specifying the column names? – Matt Dawdy Feb 10 '12 at 15:23
  • Why is this expected behavior? Why would 1 query (even a terribly designed query) bring the network traffic down to almost 0? Why would 1 query stop SQL Server from responding to other queries? Why would SQL Server allow itself to be used like this? – Matt Dawdy Feb 10 '12 at 16:13
  • @Matt - The query could be optimized to operate on the set in chunks. Also, if the database is as badly designed as the query, that would make it worse. An example would be an inappropriate data type for the clustered index. When doing range scans, SQL leverages a clustered index that uses sequential numbers. A horrible clustered index would be a uniqueIdentifer column. A good clustered index is a bigint identity column. Try running SQL Profiler during the activity to determine what is happening. You could start with the TSQL_Locks template. – Greg Askew Feb 10 '12 at 16:31
1

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.

Grant Fritchey
  • 121
  • 3