SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple query maxing CPU


Simple query maxing CPU

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215476 Visits: 41979
SscLover (11/7/2013)
hi Jeff,

adding an index is actually the worst thing you could do even for SELECTs.


could you please enlighten us with some test samples or links would be appreciated.


The paraphrasing you did on what I said makes it sound very dangerous. Here's what I actually said... (I've bolded the part you left out)...

There are quite a few places where adding an index is actually the worst thing you could do even for SELECTs.



I don't have a demonstrable example mostly because I never thought of building one but there are many examples that you could Google. For example, one example has to do with Table Scans (Clustered Index Scan in most cases) v.s. Index Seeks. I've seen many a query at work where someone added a new index to get rid of a CI Scan to get an Index Seek and it was absolutely the worst thing that could be done because the CI Scan was about 40 times more efficient than the 100,000 Index Seeks that occurred after the new index was added.

My point is that I don't want anyone to think that "INdexes is definitely a MUST thing here or everywhere" is the solution in all or even in most cases although I do agree that the presence of a proper Clustered Index is usually (but not always) a given. "It Depends" and someone must sit down and do some serious analysis and testing. Just adding a bunch of indexes to "solve" problems increases INSERT/UPDATE/DELETE latency (can actually produce timeouts for the front-end), can cause the optimizer to have to work harder to figure out which index is the most appropriate, can cause severe bloating of the database and the resulting backups (I have an audit table with 8GB of data and 14GB of indexes that I'm currently working on), nightly maintenance times can increase drastically (most NCI's fragment a lot because they are not in temporal order), and the unnecessary bloat also slows down restores in the even of a DR situation.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215476 Visits: 41979
Ah! THERE it is! I remembered that I had an article out there somewhere where I had documented that the example code actually ran twice as slow in the presence of what seemed like the right index to use and I found it. It's in the article at the following link. You can certainly setup the test data from the article and give it a try yourself with the understanding that index requirements can and do vary from machine to machine if you consider available memory, number of processors, MAXDOP settings, etc.

http://www.sqlservercentral.com/articles/T-SQL/94570/

The quote from the article is...

Also notice that we didn’t add any indexes. It turns out that adding an index to the EmployeeID column would actually make the upcoming code run about twice as slow even though we’d get a MERGE JOIN out of it. Do NOT add an index to this interim table! If you intend to keep this table with the Nested Sets calculations (the Left and Right Bowers, etc) in it, add the indexes you'll need AFTER we get done with the rest of the steps below.



Like I said, "It Depends" and no one should ever take on the attitude that "INdexes is definitely a MUST thing here or everywhere." Adding indexes without analysis and testing can be the worst thing to do.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search