SQLTeam.com | Weblogs | Forums

Logical Scan Fragmentation is High

This is what I hate about MS documentation and such functionality. There are (from what I can make out in the documentation) large databases where any performance improvement of CHECKDB would be a help. Since they are large databases, they're also partitioned with one file/filegroup per historical month. According to both your and my interpretation of the trace flag you speak of, it's not going to work anywhere on the server because it's a GLOBAL trace flag.

Hmmm.. maybe I can just turn it on when I need it and off when I don't.

Still, even if every database had multiple files, their requirements for usage make doing tricks like my previous answer, which is extremely beneficial where I've done it on some really large CIs, either make it so you can't use the trace flag on that database or make it so you can't get the benefit of the method I posted.

I keep the benefit of what I posted. :smiley:

Thanks, @ScottPletcher . I'm curious... why do you change occurrences of the table name in index names with a "~"?

And, yeah... I usually do the same alpha sort on variables because it makes them a whole lot easier to find with the Mark I Mod I eyeball method. :smiley:

I use the table name in the index name. The table name can be rather long sometimes. So I substitute ~ for the table name in the index names to shorten the index names for display.

Also, I prefix index names with the table name rather than suffix with it. Partly because then a sorted list of index names will be in table name order as well.

And because it seemed a more logical naming method than MS's "PK_", etc. Everyone else just seems to copy that method but it's rather illogical to me. Typically I want to work at one time with all the indexes on one table, not with every PK_ index.

Specifically, I use this pattern for table-related object names. This pattern also allows the name to be accurately programmatically deconstructed:


Where TT = type of constraint / object == CK|CL|DF|IX|TR|...

The "&" between column names I picked up from another DBA. I ended up kinda liking it. Yeah, you have to always use [] around constraint names, but to me it's still clearer when reading it.

table1__TR_INSERT --an insert trigger on table1
table2__CK_column3 --a check constraint on table2, column3
table3__IX_column4&column5 --a nonclus index with keys on column4, columm5 (may or may not have INCLUDEd columns)

Similarly, in the output from the script above, the INCLUDEd columns for missing indexes are in sorted order (unlike the original MS output, which just lists them all higglety pigglety).

Btw, that script is very old, so I'm sure I'm need to rewrite it from scratch to make it cleaner, more efficient, etc., I just never find the time to do it.

I have a couple of scripts for various things in that very same condition. :smiley: I'll get to them someday.

I guess I've gotten complacent given today's error-checking physical drives and CHECKSUM accuracy in SQL Server.

If I don't see entries in the dbo.suspect_pages table, I don't run physical check on VLDBs very often, like just every 3-6 months. Are you all seeing errors w/o entries in the suspect_pages table?

I'm not. But I've also never seen any errors in any of the databases we have at work. I have a morning report that I built as a job that sends me the status of all jobs in the last 24 hours and the very top item has a count of the suspect pages table.

Also - if your VLDB is in an AG then a lot of integrity issues can be 'fixed' by SQL Server. If SQL Server encounters an issue with a page - it can request that page from the secondary.

Doesn't eliminate the need to perform an integrity check though.

Very often -- not always, of course -- you can just restore the damaged page(s) from a backup of the db, esp. if you monitor suspect_pages and respond quickly to the problem.

Thanks for the detailed replies, everyone.