July 13, 2010 at 6:07 pm
Jeff Moden (7/13/2010)
Gianluca Sartori (7/12/2010)
I think yesterday it was Emperor Paulpatine's birthday.Happy birthday, Paul!
Heh... does it mean more Vuvuzelas?
It was indeed - well remembered, Gianluca, and thanks!
I am pleased to confirm that vuvuzelas were banned that evening.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 13, 2010 at 6:15 pm
CirquedeSQLeil (7/13/2010)
Here's another one in the same doc.As a rule of thumb, once a table has more than five indexes, updates to that table may be adversely affected since the system must also update all of the indexes.
I think that is an odd rule of thumb.
It's very odd. Depending on the table and index structure, it's quite possible that most updates will hit only one or two of the indexes even if there are 20 or more of them. Of course inserts will be expensive, but not necessarily updates (with no clustered index, updates which cause page splits will cause many index updates - but with a clustered index, or where few updates cause page splits, this isn't a problem).
Tom
July 13, 2010 at 6:18 pm
Stefan Krzywicki (7/13/2010)
Jeff Moden (7/13/2010)
CirquedeSQLeil (7/13/2010)
Here's another one in the same doc.As a rule of thumb, once a table has more than five indexes, updates to that table may be adversely affected since the system must also update all of the indexes.
I think that is an odd rule of thumb.
Me, too, especially since it only takes one really bad one to make things go totally haywire.
And yet I've heard it more than once from more than one source.
I had never seen this recommendation before and so it struck me as odd.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2010 at 6:19 pm
CirquedeSQLeil (7/13/2010)
I have a problem with the word "NEVER."
So do I. Many of my scripts begin with a USE statement. I do have scripts that have to run on different databases at different times, and they don't have USE statements, but these are rarer than scripts that always run against the same database.
The NEVER statement is almost as bizarre as claiming that a script should never contain a 3-part table identifier, since that too specifies the database to be used.
Tom
July 13, 2010 at 6:20 pm
CirquedeSQLeil (7/13/2010)
I have a problem with the word "NEVER."
Me too. My only rule of thumb is that there are no good rules of thumb.
And 'never' means 'try to avoid...' or 'prepare to be asked to justify...' at best.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 13, 2010 at 6:23 pm
Paul White NZ (7/13/2010)
CirquedeSQLeil (7/13/2010)
I have a problem with the word "NEVER."Me too. My only rule of thumb is that there are no good rules of thumb.
And 'never' means 'try to avoid...' or 'prepare to be asked to justify...' at best.
Agreed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2010 at 6:30 pm
Tom.Thomson (7/13/2010)
Depending on the table and index structure, it's quite possible that most updates will hit only one or two of the indexes even if there are 20 or more of them.
True. A little extra work is involved in determining whether to update each non-clustered index or not. The number of indexes might also affect the optimiser's choice of update strategy: per-index, or per-row.
Of course inserts will be expensive, but not necessarily updates (with no clustered index, updates which cause page splits will cause many index updates - but with a clustered index, or where few updates cause page splits, this isn't a problem).
Page splits in a non-clustered index occur regardless of the presence of a clustered index. Data page splits are only possible on a clustered table - heap data does not split, though forwarding pointers might be created, which are arguably worse.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 13, 2010 at 6:31 pm
I usually say "never do x.... until you have to"
In the v6/6.5 days, temp tables where an issue. So we told all developers to "never" use temp tables.
Until we had no other solution, or it was a large performance boost.
July 13, 2010 at 6:41 pm
I can see some reason for evangelizing "never" in the database world to developers. However, if the developer is always told to never do something, and then you do something contrary to what you have preached - they will see that as a "fold" on your part. Or at least that has happened to me in the past. Once they see that chink, then it is tougher to win the battles in the future. By changing the terminology just so slightly, a chink never appears and the developer is edumacated.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2010 at 6:47 pm
Jeff Moden (7/13/2010)
Stefan Krzywicki (7/13/2010)
Jeff Moden (7/13/2010)
CirquedeSQLeil (7/13/2010)
Here's another one in the same doc.As a rule of thumb, once a table has more than five indexes, updates to that table may be adversely affected since the system must also update all of the indexes.
I think that is an odd rule of thumb.
Me, too, especially since it only takes one really bad one to make things go totally haywire.
And yet I've heard it more than once from more than one source.
I'm not sure where or how these rules of thumb are actually developed but I try to keep them from spreading especially when there is no proof offered with the related hearsay.
Just like the tired old "more than three tables in a join leads to performance problems." I think I have an involuntary spitting problem when someone rolls that one out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2010 at 6:55 pm
CirquedeSQLeil (7/13/2010)
Stefan Krzywicki (7/13/2010)
Jeff Moden (7/13/2010)
CirquedeSQLeil (7/13/2010)
Here's another one in the same doc.As a rule of thumb, once a table has more than five indexes, updates to that table may be adversely affected since the system must also update all of the indexes.
I think that is an odd rule of thumb.
Me, too, especially since it only takes one really bad one to make things go totally haywire.
And yet I've heard it more than once from more than one source.
I had never seen this recommendation before and so it struck me as odd.
When I first heard it, I thought it was likely a warning to newer developers that if they start having many indexes, they should look again at what the indexes are doing. I figured it was phrased that way to emphasise it and make it memorable. I filed it away as "make sure the indexes you have are doing what you actually want them to do". Reviewing your indexes is a good idea, maybe this phrasing gets people to check them more often? Or more likely, the issuers of this advice think this is the case.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 13, 2010 at 7:01 pm
Grant Fritchey (7/13/2010)
Jeff Moden (7/13/2010)
Stefan Krzywicki (7/13/2010)
Jeff Moden (7/13/2010)
CirquedeSQLeil (7/13/2010)
Here's another one in the same doc.As a rule of thumb, once a table has more than five indexes, updates to that table may be adversely affected since the system must also update all of the indexes.
I think that is an odd rule of thumb.
Me, too, especially since it only takes one really bad one to make things go totally haywire.
And yet I've heard it more than once from more than one source.
I'm not sure where or how these rules of thumb are actually developed but I try to keep them from spreading especially when there is no proof offered with the related hearsay.
Just like the tired old "more than three tables in a join leads to performance problems." I think I have an involuntary spitting problem when someone rolls that one out.
I actually have something similar to that in this doc. I already removed it. The premise being that the query is too complex if it has multiple joins - yak yak.
If people think that three joins is nuts, they should evaluate some of what MS CRM does in its views and procs - the yakking and convulsing will last for days.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2010 at 7:07 pm
Anybody have a good source concerning the "don't index tables with less than 1000 rows" optimization technique?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2010 at 7:33 pm
CirquedeSQLeil (7/13/2010)
Anybody have a good source concerning the "don't index tables with less than 1000 rows" optimization technique?
It's not 1000 rows, it's a 1000 pages and, it's a made up number[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2010 at 7:41 pm
CirquedeSQLeil (7/13/2010)
Anybody have a good source concerning the "don't index tables with less than 1000 rows" optimization technique?
Read Paul Randall's blog on this subject.
http://www.sqlskills.com/BLOGS/PAUL/category/Indexes-From-Every-Angle.aspx#p4
Basically, he made them up...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 16,336 through 16,350 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply