Full Text Index and database structure First forgive me, I am not a DBA and our company really doesnt have one. I am a developer and Googling for answers is just not helping any more. Thanks in advance.

My question is on optimal structure for our database. We have a website that allows users to search and find PDFs easily. Mostly its just meta data searching but we do allow full text searching (All words, Any Words, Exact Phrase). 

There are ~3 million PDFs (growing daily) at about 2.3 TB in size. These are stored in tables corrisponding to date (File1980, File1990, File2000, etc) using VARBINARY(MAX). We didnt make it a single table because our system admin was having trouble with backups. Each of the file tables (File1990,etc) has it's own MDF file.

Our website is using Microsoft Dynamic Data and Entity framework. We search across the PDFs using one of the table value functions below that joins with the rest of the LINQ query.

ALTER FUNCTION [dbo].[udf_ContainsSearch]   (@keywords nvarchar(4000))
RETURNS @containsTable TABLE
(
    [key] [uniqueidentifier] NOT NULL, --PRIMARY KEY NONCLUSTERED,
    [rank] [int] NOT NULL
    PRIMARY KEY NONCLUSTERED ([key], [rank])

)

AS
BEGIN
    insert into @containsTable 

    select [key],[rank] from ContainsTable(File2010,Content,@keywords)
    union  
    select [key],[rank] from  ContainsTable(File2000,Content,@keywords)
    union    
    select [key],[rank] from  ContainsTable(File1990,Content,@keywords)
    union    
    select [key],[rank] from  ContainsTable(File1980,Content,@keywords)
    union    
    select [key],[rank] from  ContainsTable(File1970,Content,@keywords)

    RETURN
END

ALTER FUNCTION [dbo].[udf_FreetextSearch]   (@keywords nvarchar(4000))
RETURNS @containsTable TABLE
(
    [key] [uniqueidentifier]  NOT NULL, --PRIMARY KEY NONCLUSTERED,
    [rank] [int] NOT NULL
    PRIMARY KEY NONCLUSTERED ([key], [rank]) 
)

AS
BEGIN
    insert into @containsTable 

        select [key],[rank] from FreetextTable(File2010,Content,@keywords)
        union  
        select [key],[rank] from  FreetextTable(File2000,Content,@keywords)
        union    
        select [key],[rank] from  FreetextTable(File1990,Content,@keywords)
        union    
        select [key],[rank] from  FreetextTable(File1980,Content,@keywords)
        union    
        select [key],[rank] from  FreetextTable(File1970,Content,@keywords)

    RETURN
END


We have been getting timeout issues on the web site and have been looking into performance. Running the database tuner and adding some indexes on a weeks worth of queries helped but not enough.

Now the questions:

Is there a better way to structure the data that would give better performance? 

We use professional SQL Server 2008, would Enterprise help us in anyway?

Is there a better way for the full text search portion?
Did you try to replace the insert..union statements with separate insert statements?

For example:

insert into @containsTable select [key],[rank] from ContainsTable(File2010,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File2000,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1990,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1980,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1970,Content,@keywords)   


It could be as simple as the increased processing cost of union plus the increased memory usage from having 5 processes open at the same time to use them on that insert, slowing down your UDF.

(Also, since you're querying from - supposedly - unrelated data tables, the 'distinct' part of union should be unnecessary.)