April 20, 2015 at 7:19 am
I have a 3rd party database which means no control over design, I can control indexes (create, alter modify). Database is about 1.4 terabytes, about 7K tables. Unfortunately a guid in every table (sometimes more than one, and these are the primary key in most, foreign keys in the others). Server is HP 48 core, 128 gig of RAM
MY problem
When I took over they used a combo of vendor supplied maintenance plan along with index/stat rebuild from Management Studio Wizard. Never ran consistenly, so I punted and put in Ola's script which had been running well until recently. We actually just moved from older 5300 EMC to new EMC San. The last 2 weekends the index rebuild has been taking over 25 hours (this is with almost no one on the system). I log the rebuild to a table, errors are not an issue, and my Idera monitoring shows the disk being very busy, but compared to normal use (about 600 active clients) not that bad. Other than log shipping backups nothing else going on. I have run Ola's script against larger databases at previous position and never had this kind of issue. Was going to run Crystal Disk Mark (ran last month, not bad not great) to see if I see something. No anti virus ran at this time before you ask. I do wonder if the engine is struggling with rebuilding indexes that contain that many guids. I am sure the engine logically would rather rebuild dates, numbers, and varchar data types, I am sure rebuilding an index with 2-3 guid's must be painful, of course this would be almost all your Sharepoint tables. My dilema is my window for the this maintenance is going to be shrinking and the database is always growing. I did use Ozar's and some other scripts to remove about 200 gig of duplicate or unused indexes so I have done all the cleanup on that approach as I can. Anyone have any other places to start looking?
Thanks
April 20, 2015 at 7:33 am
SQL's not going to struggle to rebuild indexes with GUIDs in them, uniqueidentifiers are no harder to work with than strings, and probably are easier. The problem is that GUIDs are random and hence cause massive fragmentation and so Ola's tool is probably rebuilding every single index on every single table because of the severe fragmentation that the random GUIDs cause.
Have you considered reducing the fill factor on your clustered indexes?
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
April 20, 2015 at 7:43 am
actually rebuilt less than 1000 indexes out of 30K. Going to look at fill factor on the 1000k that were rebuil
April 20, 2015 at 12:14 pm
Looking at that scale, it may not be practical to change the clustered indexes to use another key so Gail's recommendation should be the least painful.
That being said, just be sure that you are rebuilding indexes that are actually being used and not doing it just to keep the server room warm.
Changing fillfactor on an unused index is less than pointless so probably first get rid of dead weight, then assess fillfactor on the survivors.
So, I would see how many indexes go unused up till the day before the weekly rebuild and at then disable them.
Some procs or Jobs might look for them by name so disable might be safer than dropping.
Also, if you have dropped all the waste, then you can factorise the remaining indexes since you might have 10 indexes all on the same key but covering different columns on the same table.
oh, and dont disable anything with index_id = 1, i.e. clusteded indexes.
April 20, 2015 at 12:24 pm
Oh forgot to leave out that small factor, almost no clustered indexes (yes I know bad) but that is the one change if I make lose my warranty, trust me been a dba for 20 years I believe this company is developers gone wild). I did run cystal disk mark against 2 of my servers on the index drives (yes all my indexes are on own filegroup). One that the active/passive cluster that this database is on, and the other against a little newer server that is attached to the same SAN. Going by ozar's description for my heavy OLTP server the 4k QD32 is the most important.
for my problem server I get
22.8 MB sec read and 78.52 MB sec write
for my other server
29.62 MB sec read and 106.2 MB sec write
SOme difference but also guessing newer server also has a little faster controller.
Checking through the log table I found 5 indexes on 138 million row table took 4.8 hours to rebuild. In fact out of the 25 hours it took 30% of the indexes rebuilt accounted for 80% of the time
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply