I have an application that uses Microsoft SQL Server 2012 to store information and text extracted from documents amongst other things. A single table (let's call it "Docs") stores most of this information and has a "Status" field that determines where the records reside within the application. This Status field can have various values, but essentially determines which interface the documents appear in throughout the lifecycle of the application. The Docs table can grow quite dramatically depending upon the number of items written into it. We have various fields in the table that are included in a full-text index. We currently don't have any partitioning schemes setup for this table but would like to explore whether implementing partitioning will provide any performance benefits, mainly relating to queries performed by the various UI.
Let's take an example of a database that has 35 million records in the Docs table, which equates to a total database size of just under 2TB and a data size of 1.5TB for the Docs table. Initially, these have the same "status" value (let's say it is "1") and are only accessible for querying via UI 1. As users work with the records, the "status" field is updated to "2", making them available in UI 2.
We would like to improve the responsiveness of queries performed in UI 2, and are thinking of implementing a partitioning scheme based on the "status" field. When querying the database, UI 2 will always include a WHERE status = 2. My understanding of table partitioning is that if we partition the table based on the Status, the system will only have to query the items and retrieve data from the filgroup storing records of that status.
We have a scenario where a user has ingested 35 million records into the Docs table, but running queries on the full-text index is causing UI 2 to timeout. So, my question is, would table partitioning improve the responsiveness of queries performed in UI 2? Additionally, if we created the full-text indexes on the partitioned tables, would the full-text indexes be stored within the partitioned tables (i.e. separately, therefore improving full-text query responsiveness) or would SQL Server still have to query the entire full-text index (which in this case is 500GB)?
