How can I maintain Index-heavy application.

[Jerry Winston]

Hello


I've been tasked with supporting a newly implemented piece of off-the-shelf software that requires SQL 2000. A recent upgrade to the software radically changed the distributed design of the underlying database to a very, very index-heavy and centralized model (they dropped 80% of the PK's and added 1,000's of indexes). I've been casually keeping an eye on the performance stats coming from the server and wasn't concerned until I learned of plans to perform several large data imports. I'm concerned about index performance, file growth, and data file fragmentation. I have plans to expand the data and log files, but I'm wondering if there isn't something I should do with the indexes pre/post-import. What can I do to improve or prepare for this batch import?


SQL Server 8.0.760
DATA ~3 GB
LOG ~1 GB
Collation SQL_Latin1_General_CP1_CI_AS

Thanks in advance!

[Oralloy]

Jerry,

As you're aware, a primary key and a unique index are really moral equivalents.

I'm not certain how Microsoft treats them differently, though.

You might start by asking yourself what the incoming data bulk is to your existing data bulk. This will probably drive your decisions.

One trick we've occasionally used in the Oracle world is to turn off the inidices during the import (assuming you can do so), and re-build them afterwards.

Outside of that, how sanitary is the incoming data? Will it collide with your existing data and constraints, or will it simply fit in with little-to-no difficulty?

Good Luck!

[Jerry Winston]

Thanks for the response.

yes, I know they're equivalent but I'm a little concerned because all of the indexes are Non-Unique,Non-Clustered--I checked sysindexes.

According to my rough estimate the number of incoming rows should triple the size of the database on a 1:3 ratio where one row of incoming data produces one row of data in the database(destTable,propertyTable, LogTable).

I considered disabling indexes for the bulk import but I couldn't be sure what kind of down-time we'd be facing rebuilding the indexes.

The bulk data should come directly into the database without any trouble colliding with the existing data.

I might be making much ado about nothing, and if I am let me know, but I have a sneaking suspicion the DB is going to really balloon. I might be able to calculate the number of grow operations by knowing the growth rate is 10 percent.

[Oralloy]

Well, how long does a backup take? :))

Worst case is it all goes south at the end of the import. Then you have lost time and recovery downtime.

Is it possible for you to back up the DB and run an update against the backup first, just to see what happens?

[ck9663]

First on PK and unique index. They are the same, but different :) The most visible difference between the two is the treatment of NULLable columns. All columns in PK should not allow NULL. Unique index, on the other hand, accepts NULLable columns.

Depending on your definition of "volume", you may start observing the performance of your database. I typically re-create index if my data grew by 10-15%.

Non-clustered index does not update the physical table, just the leaf arrangements of the index.

Good Luck!!!

~~ CK

[Oralloy]

Yep, there are differences. Also, the implementation mechanism can be radically different depending on the DB's implementation.

Regardless, the cost of building (and maintaining) either index is expensive.

From what I see, Jerry has about 10M records in his database (assuming ~33% utilization and 100 byte/record).

My personal experience stress testing MySQL shows that it takes between 2 and 4 hours to insert 1M records into an otherwise quiet database on an x86 based server running linux. That's 139 records/second.

Assuming O(log(n)) insert performance, I'm guestimating that 10M records should take a minimum of 40-80 hours to insert while maintaining indices. (As we all know, O(n*log(n)) performance really is decent, but it's still painful for large n.) That's just to build up the original database from raw record data.

And then we get to tripple that for his new data. Ugh! Now we're talking another 120 to 250 hours or more.

So, how can we make this viable?

If we turn off indices, he can (nominally) insert the records in linear [O(n)] time. Three giga-bytes of data is very little, so I would expect a small number of hours.

So the question is - how quickly can the indices be rebuilt? We know the answer is O(n*log(n)), however the time multiplier may be much better, depending on the implementation.

That's why it could be very interesting (and useful) for him to dry-run this operation on a separate server.

I want to see how this works out.

[Jerry Winston]

Thanks for all the help everyone.


I always like to maintain a test environment for these very situations, when the math says you could be down for 1024 years. :P I can look like a genius on production implementations if I have a test implementation first!

Unfortunately, this project has "legs" and I only found out about it through a "BTW Jerry..." conversation.

This is a very important application so, of course, we purchased the custom consulting component and left the majority of our IT staff in the dark.

The implementation is very, very buggy, IMO, and would not support bringing up a test environment quickly (in our deployment schedule).

Luckily, the import will run in pieces so hopefully by starting with the smallest I can get some good numbers to figure out how long the next run will take.

In any event what do you all suggest I do during the import?

1. Get a cup of coffee. The contractors will take any heat.
2. Stand in a door way
3. Put fingers in ears and crouch under a table
4. Head between legs to kiss butt goodbye

Thanks again for all the advice

[Oralloy]

C-wrap Jerry, but you got handed a ball of snakes.

Good luck, it's good that you've go the contractors helping you out; but still, you have to make sure that all the infrastructure and logistics are kept working during the upgrade. I live the contracting world, and I've dropped a ball or two over the years. You would not believe just how valuable a right-honest backup can be at that moment.

Let us know how it works out. I'd like to know if my time estimates are anything better than the thumb at the end of my arm.

Good Luck!

[NeoPa]

I figured I wouldn't be in much of a position to help if you were asking the question Jerry, but I'm pleased to see some of the other experts in here helping out.

Best of luck anyway :)

[ck9663]

There are a number of factors that you might also want to consider. How often the BULK IMPORT will happen? How fast do you want your production database be updated?...Is this a simple BULK IMPORT or a full ETL (Extract-Transform-Load). I deal with ETL process on a monthly basis. Our files could range from a couple of thousand rows...to average of 7-10 million rows...to the extreme of 18 million. I managed to transform the 18 million rows in ~3hrs. I managed to load a 5M rows in ~2hrs. All index active and no re-index performed.

Do you have the necessary rights to bring back those PK instead of index? In theory, PK are faster than unique index due to the fact that PK is a constraint and not an index.

Keep us posted.

Good Luck!!!

~~ CK

[Jerry Winston]

@ck

Those are some impressive number for BULK IMPORTs, I'll definitely have to talk with you about ETL later.

However, I think I've been a little too cavalier with my language. The "bulk imports" are processed through a third-party application that's completely black-boxed.

The bulk upload will happen once, after all records are loaded new entries will be added as needed.

I'd love to bring back the PK's but there's no way I'd get the green light to perform any DDL-level tuning short of the server physically catching afire.