Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Simple query maxing CPU Expand / Collapse
Author
Message
Posted Thursday, November 7, 2013 5:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:22 AM
Points: 43, Visits: 481
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.


===========================================
performance issue:
(1) Have you update the statistics on all tables of underling query?
(2) Are you using index well on the tables?
(3) Can you simplify your Query by re-writing it .
(4) are indexes are Defragmented well ?

Post #1512211
Posted Thursday, November 7, 2013 8:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1512288
Posted Thursday, November 7, 2013 8:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1512303
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse