Full Text Index 2

[nice95gle]

Hello everyone,

This is related to SQL Server 2005

I have a problem. I have a FTI in 3 environments, Dev, QA and Prod. I have two queries that run against my index. Now for some reason in Dev and QA they run in 0 secs but in prod they take 6 mins.

After looking at the execution plan an in both dev and QA, there's an Index Seek that's taking 90% of the exec time and a Remote Scan that takes about 10% give or take.

But in prod running the same query the Index Seek is taking about 10% and the Remote Scan is taking about 90%.

I moved the prod DB to the Dev and QA boxes to see if the problem still exists and yes it does. Because first thoughts were there might be a hardware issue. But after running this little test, I’ve come to the conclusion that the problem might be within the DB or the index and not hardware related.

I did a schema compare using one of the tools we have here at work and they are the same. Same scripts used to create all three FTI’s. The only difference is my Dev and QA Db’s are set to a Simple recovery model and Prod is set to Full.

Does anyone know why my prod database is using this funky plan?

Thanks

Well Done is better than well said
- Ben Franklin

 

[SQLSister]

Do you have approximately the same number of records in all three databases? Especially in the tables that the remote scan is running against.


"NOTHING is more important in a database than integrity." ESquared

 

[nice95gle]

No SQLSister...Dev and Prod has the same, QA has more.

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

For anyone that might be interested in the fix:

After restoring a Sql Server 2005 database with a Full Text Index from one environment to another, you may find that any queries that use your FTI running very slow.

To resolve this issue, detach and then re-attach your database.

I also detached with Update Statistics turned on.


Well Done is better than well said
- Ben Franklin

 

[mrdenny]

It's probably the UPDATE STATISTICS that actually fixed it, as all detaching the database would have done is flushed everything from cache and closed the file. UPDATE STATS would have told SQL Server how to generate a new execution plan.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[nice95gle]

Thanks Denny,

I ran a UPDATE STATISTICS yesterday (which was the first thing I thought of) to no avail. Not until I detached with UPDATE STATISTICS did the problem go away. But who knows.

Well Done is better than well said
- Ben Franklin