Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««12345

Lots of Key Lookups vs. UniqueIdentifier Clustered Index Expand / Collapse
Posted Sunday, November 25, 2012 7:04 AM



Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 42,083, Visits: 39,479
opc.three (11/24/2012)
It's quite possible that leaving the FILLFACTOR at 100 would make the most sense. With the SSD in play, the page splits and ensuing fragmentation may be less of a concern than absorbing a 25% space-hit to the buffer pool for each index where the FILLFACTOR is lowered.

At first, and even though it goes against my inner data-troll, I thought that would be fine especially considering the blazing speed of SSDs. It might still be fine if you have a reorg going on a regular basis. My concern is that when you have a lot of pages of GUIDs, inserting a lot more GUIDs will cause page splits possibly on almost every page because of the extremely random distribution. In theory, if you have 10,000 pages of GUIDs where the CI is on the guid and you insert just 10,000 new GUIDs, it could cause a page split on each and every page. Now you suddenly have the space equivalent of a 50% FILL FACTOR on some relatively very expensive hardware.

Considerig the speed that a reorg or rebuild might happen on the SSD, that might still be the way to go, though.

--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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1388394
Posted Tuesday, November 27, 2012 11:08 AM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:03 PM
Points: 9,836, Visits: 11,913
Jeff Moden (11/24/2012)
opc.three (11/24/2012)

However the OP is talking about producing their own function to generate sequential GUIDs that would not be affected by a restart. The tradeoff of course is that all GUID-generation would require a roundtrip to the database which has its own set of baggage.

Considering that the OP has also identified that the existing GUIDs have been used in "thousands of lines of code", I'm hoping that he won't consider such a thing. It would even break GUI code unless they write a wad of "Instead Of" triggers to covert the UUIDs the GUI code is already passing.

I think I disagree, Jeff.

If id generation is one place, at most three triggers are needed; that's pehaps a bit less than a wad of triggers. Of course there's also a view (since there's no reasonable way to do partitioning between new GUIs and old GUIs within one table, it has to be two separate tables) and that's what is operated on. Maybe fewer than three triggers are needed (I can't remember how good SQL Server is at doing updates and deletions on a union view without trigers - certainly updates and deletions on unions have been done in other dbs without needing a trigger, so maybe only the insert trigger to trap duplicate keys is needed; and in fact a try catch could be used instead of that trigger if the insert statement used the new table instead of the union view, but I doubt that would be much cheaper in development, testing, or performance than inserting into the view and using the trigger to detect duplicates and direct to the new table). The overhead of the insertion trigger is pretty small too: just a check whether the generated value is already in the old-keys table, and if it is regenerate (doesn't need a new GUID, just adding one to the sequential parameter of the generator) and check again, and the check will hit a match only about one time in ten to the power thirty three even if there are as many as a billion rows in the old table, so the performance overhead is small).

I could be wrong here - I've never done this trick with GUIDs.

Post #1389324
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse