CODEWITHSUNDEEP
sqlasp.netsql-serverdatabase
user111222333
user111222333

Reputation: 99

Sequential GUID to bigint

I'm currently overseeing a database that has (sequential) GUID's all over the place. This database will grow in size significantly in the short term. It wouldn't be too much work to convert the whole shebang over to use bigint's. I'm wondering, is it worth it?

The clustered indexes are going to fall apart as it grows in size, SQL page sizes will increase, I'm expecting all sorts of hell if I continue on the path using sequential GUID's. Fragmented pages, horrendous indexing.. (especially in the event of a server reboot, which resets the sequential GUID creation)

Is there a world in which I could keep the GUID's around for some reason and use bigint for indexing? All SQL statements could easily be converted to use a bigint column for SELECT clauses.

What's my best approach? Any reason to keep the GUID's around? Or should I just convert everything into bigint and run from there?

Upvotes: 2

Views: 856

Answers (2)

Kasper Brandenburg
Kasper Brandenburg

Reputation: 212

@simon_j_dm: Sequential GUIDs are not a ordered as BIGINT... Seq. GUID gives you 1000 item long sequences of ordered GUIDs... but between those sequences... you still see fragmentation.

BIGINT is the most ordered key type you can have.

Does this mean you should change? not necessarily, BIGINT are smaller, so you have less memory pressure, and the dont cause fragmentation like GUIDs does. But depending on the load, you could see latch congestion will using BIGINT, that you wont see on GUIDs as they by nature spread the load over more pages.

You can reduce the fragmentation with GUIDs by lowereing the fill-factor. That however causes the database to bloat in MB size, and you dont fill up the data-pages right away. And you will still need to deal with fragmentation at some point.

So my point is... you need to do what is right for your situation. There is no golden way to do this. Do it the Brent Ozar way:

  1. Figure out what you want to change.
  2. Test your change in a controlled environment
  3. Messure if the change had the wanted affect.

Upvotes: 1

simon_dmorias
simon_dmorias

Reputation: 2473

If you are using sequential guid then they are ordered just as a bigint is. Changing to a bigint will not affect fragmentation at all. Moving to a bigint would however reduce the storage space by 50% on those columns, which in turn would also reduce memory usage and general query performance as memory grants can be smaller and tempdb usage lowered. If it's not too much pain I would change it as smaller data types are always preferred.

Upvotes: 0

Related Questions