Some size concerns with old 7.x databases in Gold

[AbleMods]

I've been moving sites for the past two weeks to my new-and-improved hosting platform. During the course of the move, I noticed a few of the larger sites had large SQL databases, like 7gb-10gb in size.

At first I dismissed it thinking "they're old, they've got a ton of orders." But as I thought about it more, it still bothered me. So I dug a little deeper (yea, dangerous I know)...

In every case I found a full-text search catalog ac_SearchCatalog in the database and it's a massive file. For one client, the total database size was 7.2 gb. The search catalog was 6 gb of it.

By clicking the Rebuild Index in the admin->website->indexes, the search catalog immediately dropped to 108 mb about 5 seconds later. Now the database size is just shy of 1 gb after I did a database resize in SQL Management Studio.

That's a whopping difference in database size. And it brings to mind a few questions right away:

A) Why isn't that being done automatically? Seems awfully simple to slap that rebuild-index into the maintenance routine.

B) How on earth did the full-text search catalog keep growing and growing and growing? How is Able utilizing it, or is it simply a feature managed by SQL itself?

C) Shouldn't there be some sort of monitor on that puppy if it's got the potential to grow to such massive proportions? At least some sort of dashboard warning would be very helpful.

D) Finally, what are the implications here? Are searches going to suck for a while? Will they be slower? Will they actually improve because the accuracy is somehow better?

[mazhar]

A) Why isn't that being done automatically? Seems awfully simple to slap that rebuild-index into the maintenance routine.

Well for this first we need to see if its correct to rebuild FTS index regularly? I wonder if index needs some reorganization or rebuild takes away some useful indexed information. This needs some research before we make any decision.

B) How on earth did the full-text search catalog keep growing and growing and growing? How is Able utilizing it, or is it simply a feature managed by SQL itself?

SQL server FTS is manged by SQL Server itself. We simply create a catalog by telling SQLServer what columns and tables are manged by FTS. Then its up to FTS to mange search indexes.

C) Shouldn't there be some sort of monitor on that puppy if it's got the potential to grow to such massive proportions? At least some sort of dashboard warning would be very helpful.

Yes that sounds like a good idea. As I mentioned in response to point (A) that first we need to do bit of research to fully understand whats happening. Then we can come up with plan to handle the case.

D) Finally, what are the implications here? Are searches going to suck for a while? Will they be slower? Will they actually improve because the accuracy is somehow better?

Rebuild will start to reinitialize the indexing process. Depending upon how long it takes to complete the rebuild process searches may not be as accurate. I will do some research and see if I can find more details.

[AbleMods]

It has to be some sort of weird SQL index fragmentation issue. I've just never seen it happen to this extreme.

I learned a few more things last night:

Just deleting the catalog in SQL will break store searches in 7.x. Easy fix is to go into admin, disable full text search (it's a checkbox), then re-enable it. This recreates the missing catalog.

In another site, I used this technique to see if that would clear the monster catalog and it worked. So it seems something as simple as a disable/enable will bring things back to normal. While it doesn't shrink the database files afterwards, at least shrinking will now have an impact.

Always a good idea to rebuild/reorganize indexes in your SQL database from time to time. Definitely improves site performance for larger sites with a high volume of traffic. In my hosting servers, I have a maintenance plan configured to automate this process easily.

[mazhar]

Always a good idea to rebuild/reorganize indexes in your SQL database from time to time. Definitely improves site performance for larger sites with a high volume of traffic. In my hosting servers, I have a maintenance plan configured to automate this process easily.

Right, I guess then most probably all third party hosting providers may have such maintenance plans configured on their SQL Servers? It would make sense if this happens on a self hosted server without any such maintenance plan.

[AbleMods]

I guess then most probably all third party hosting providers may have such maintenance plans configured on their SQL Servers?

I've found the opposite in most cases. A lot of hosting providers don't seem to staff knowledgeable SQL database administrators. They know enough to host SQL databases, but they know little about SQL troubleshooting or optimization. I've had several instances personally where I was hired by the "database administrator" to teach them SQL Profiler, Database Tuning Advisor, monitor index fragmentation and activating maintenance plans. They had been using SQL for years but didn't know how to do those tasks.

SQL administration is becoming a lost art. SQL installs so well and runs so well, it doesn't require the hand-holding like it used to I think.

[mazhar]

I see, I will do some research on this. Please share if you find something useful while working on this.