This table contains millions of records and we need to fetch the record by DLRID which is received from several sources from outside the application so we cannot control the format and order. Most send this field as guid or some alphanumeric id .
the main issue is whenever we create a non clustered index on dlrid field within minutes the fragmentation goes to 99% because the data received is not in any order.
Is there any solution for this thanks.
I realize that this post is a couple of months old and I might be trying to close the barn door after the horse already got out but we'll see.
Jeffrey Williams has a darned good suggestion about this if you don't want to take Scott's advise about building a separate lookup table (which wouldn't be needed in most OLTP/single row lookup environments).
There are a couple of keys here, though. If you could answer a couple of questions, please, we might be able to easily make it so that you can quite literally go for months with <1% fragmentation on the DLRID column because it IS random. Here are the questions...
1. What is the average row size of this table? Yeah, I could figure it out from what you posted but it's easier if you just run sys.dm_db_index_physical_stats and let me know.
2. How many rows do you actually have in this table? Saying "Millions of rows" is not an adequate answer. How many millions?
3. How many rows per day are generally inserted into the table? Again, "Thousands of rows" or "Millions of rows" is not adequate. I need to know how many thousands or millions of rows.
Just to give you a clue that I might just know what I'm talking about, I've done a shedload of testing with this kind of stuff. One of the tests I did inserts 100,000 rows per day in a 1005 RBAR fashion into a 123 byte/row Clustered Index for 58 straight days with < 1% fragmentation, almost zero page spits, and no index maintenance during the 58 day period. That's 5.8 MILLION random GUID rows inserted with < 1% fragmentation. The table contained ~29.8 Million 123 byte rows at the beginning of day 299, which was day 1 of the 58 day period I'm talking about. That was with a 70% Fill Factor and the other Fill Factors also work a treat.
Here's the graph from the test results... the "flats" are where data is being added with almost no page splits (no even "good" ones) and nearly zero fragmentation.
And, no... the page density of the pages doesn't stay at 70%. They're constant filling as shown in the following chart of a similar 70% Fill Factor run of the same technique. This chart was for 10,000 rows per day ending at 3.65 million rows.
The "technique" is simple no matter how many rows per day you have.
- Set the desired Fill Factor. I recommend 71, 81, or 91. Why the 1? To make it easy to identify these types of "Low Threshold Rebuild" indexes just by reading the Fill Factor. No special tables required.
- Rebuild when logical fragmentation hits goes over just 1%. This is important because ALL of the pages fill at almost the same rate and as soon as you get 1% fragmentation, it means there's no practical amount of room left above the fill factor and the index is going to start doing MASSIVE page splits across the board starting the next day or so.
- When you do index maintenance at or above 1%, only do REBUILDs! NEVER EVER USE REORGANIZE ON RANDOM GUID INDEXES because it doesn't actually work the way you think it does. Like a bad drug, REORGANIZE actually compresses the pages without clearing the area above the fill factor and guess what that does? IT PERPETUATES MASSIVE PAGE SPLITS ALL DAY EVERY DAY and the more you use it, the more you need to use it. The "Best Practice" index maintenance that everyone in the whole bloody world uses are actually a WORST PRACTICE for most indexes that fragment. There are only two different scenarios out of many where REORGANIZE will actually do the job and then it'll still cost you an arm and a leg in log file space and number of entries in one of those while it perpetuates the problem in the other (you need to fix the other index).
Here's what the index looks like (this is for an 80% fill factor but looks almost identical at other fill factors) if you make the mistake of using REORGANIZE. Like I said... DO NOT USE REORGANIZE ON RANDOM GUID INDEXES! (Exception... use Reorganize if you need to compress LOBs but then follow that with a REBUILD).