February 10, 2014 at 7:52 am
I've got a table that's using a varchar(9) as the PK / CI that keeps showing up as ~98+% fragmented. I'm wondering though, if it's going to be worth my and the devs time to potentially replace the CI at least with a surrogate (such as an INT)
So, a couple questions.
If I select the PK column only, then select again with an order by the PK column, both selects will return the same values in the same order? From a quick-and-dirty test, it seems so, but I'd like to confirm.
The values stored in this column *mostly* tend to be numeric (with leading 0s) but some have alpha characters. I'm presuming SQL would order them during an insert along the lines of 0-9, then A-Z?
There are no non-varchar columns in this table, and (currently) none of the columns are used as foreign keys in other tables. According to the "Index Usage Report" from SSMS, the majority of the use of this index is User Seeks, then almost even between Scans and Updates.
Thanks,
Jason
February 10, 2014 at 8:14 am
How big is the table?
If I select the PK column only, then select again with an order by the PK column, both selects will return the same values in the same order? From a quick-and-dirty test, it seems so, but I'd like to confirm
.
Not necessarily. Without an ORDER BY there is absolutely no guarantee of order. You get the data back in whatever order it is when the last query operator finishes with it.
The values stored in this column *mostly* tend to be numeric (with leading 0s) but some have alpha characters. I'm presuming SQL would order them during an insert along the lines of 0-9, then A-Z?
Depends on collation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2014 at 8:25 am
GilaMonster (2/10/2014)
How big is the table?If I select the PK column only, then select again with an order by the PK column, both selects will return the same values in the same order? From a quick-and-dirty test, it seems so, but I'd like to confirm
.
Not necessarily. Without an ORDER BY there is absolutely no guarantee of order. You get the data back in whatever order it is when the last query operator finishes with it.
The values stored in this column *mostly* tend to be numeric (with leading 0s) but some have alpha characters. I'm presuming SQL would order them during an insert along the lines of 0-9, then A-Z?
Depends on collation.
About 245K rows, really not all that huge.
Collation is SQL_Latin1_General_CP1_CI_AS
February 10, 2014 at 8:35 am
Number of pages?
Unless it's either a large table or you've pinpointed the page splits as being a problem, I'd probably leave it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2014 at 8:50 am
GilaMonster (2/10/2014)
Number of pages?Unless it's either a large table or you've pinpointed the page splits as being a problem, I'd probably leave it.
7474 pages currently, according to sys.dm_db_index_physical_stats. The last time I rebuilt this index, I set the fill factor to 80%, and told it to pad the index. That was about 2 weeks ago.
I've not pinpointed page splits (trying to track down a way to see how these look,) and that I'm aware of no one is complaining about the performance either.
So I'm likely going to let this go, for now, as not being worth the effort just to not have a 98+% fragmented index on a table. Use it as a way to determine *why* it's getting that fragmented so in the future I don't need to ask for help on it...
Thanks!
Jason A.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply