4

We are using Ola Hallengren scripts, both to backup our databases and for index maintenance. We have a FileTable in our database and from time to time, REBUILDING its PK index takes close to three (3) hours, the rest of the time it is very fast. Questions : is it a good practice to reindex a FileTable ? which is abnormal, taking only a few seconds or taking a few hours ? I have done a few searches about reindexing a filetable but have not really found anything useful.

1
  • 2
    Can you post the arguments you are using with the IndexOptimize script?
    – Jacob H
    Jun 18, 2018 at 14:42

2 Answers 2

2

(converting my comment to answer)

FileTable uses Filestream technology. From Paul Randal's blog - Defrag the NTFS volume if needed before setting up FILESTREAM, and periodically to maintain good scan performance.

Instead of rebuilding, you should defrag the ntfs volume as suggested.

Also, highly suggest to read - Best Practices on FILESTREAM implementations - by Pedro Lopes (Senior PM at Microsoft Database Systems)

1

The length of time it takes to rebuild an index will depend largely on local factors

Blocking

Both online and offline index rebuilds require locks, though offline requires more. If user queries are hitting the table during maintenance, this could be the issue. There's generally no good way to figure out if this is the problem without directly monitoring it.

Server Load

If other things are going on on the server, even in other databases, hardware may be strained. Backups, CHECKDB, ETL loads, etc. may starve the server of resources.

Environmental Load

A good example of this is when you have VMs (or even physical servers) all on a SAN. I'm not calling your SAN slow (though it may be!), but the network path from the server to the SAN may be saturated. Especially when there are multiple SQL Servers in the environment, DBAs tend to schedule all their maintenance tasks to run at the same time. Depending on what overlaps, this can cause significant drops in throughput. You can check your error log for 15 second I/O warnings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.