Hello,
We have recently released a new version of our gallery function where we
give the users the possibility to upload images, videos and sound files. We
moved about 15 million objects from the earlier version of the application.
Each day about 150- to 200 thousand objects are uploaded and 100 thousand
objects are deleted.
One of the features in the new versions is the search functionality. The
users can mark their content with tags that are searchable via a full text
index. Each day about 1.5 million queries are executed against the full text
index and about the double against the table that the index is based on.
The full text query uses CONTAINSTABLE and gets 100 rows from the index.
These rows is joined with the table that the index is based on via a INNER
JOIN and four other tables that is needed to present all information about
the object (one INNER JOIN and three LEFT OUTER JOIN).
The problem we have come over is that queries take extremely long time to
execute (up to five minutes). Under a beta period we ran the application with
the same amount of data but with less queries against the tables, under this
period we don’t discovered any problems. After the release the msftesql.exe
consumed 100 % CPU. From the start we were using INFLECTION, after removing
that the process isn’t constantly using 100% CPU but is still peaking at full
CPU usage.
We are using Sql Server 2005. The machine running the Sql Server has two AMD
Opteron 64 bit Dual Core 2.4 GHz with 16 GB of RAM.
My question is if anyone has some input of how to proceed with the
identification of the problem? What limitations of the amount of queries
(both selects and changes of the content) have a full text index? Dose anyone
has some documentation/literature about this subject to recommend?
Thanks in advance!
Regards,
Karl
When you remove full-text from the query what is your performance like. I
strongly suspect that your problem is that you are running into a lazy spool
which will make your SQL FTS performance extremely slow. You will need to
re-write your queries to remove lazy spool.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"karl_a" <karl_a@.discussions.microsoft.com> wrote in message
news:F02AAEAF-0F3E-4847-8210-2D36EF61EC9A@.microsoft.com...
> Hello,
> We have recently released a new version of our gallery function where we
> give the users the possibility to upload images, videos and sound files.
> We
> moved about 15 million objects from the earlier version of the
> application.
> Each day about 150- to 200 thousand objects are uploaded and 100 thousand
> objects are deleted.
> One of the features in the new versions is the search functionality. The
> users can mark their content with tags that are searchable via a full text
> index. Each day about 1.5 million queries are executed against the full
> text
> index and about the double against the table that the index is based on.
> The full text query uses CONTAINSTABLE and gets 100 rows from the index.
> These rows is joined with the table that the index is based on via a INNER
> JOIN and four other tables that is needed to present all information about
> the object (one INNER JOIN and three LEFT OUTER JOIN).
> The problem we have come over is that queries take extremely long time to
> execute (up to five minutes). Under a beta period we ran the application
> with
> the same amount of data but with less queries against the tables, under
> this
> period we don't discovered any problems. After the release the
> msftesql.exe
> consumed 100 % CPU. From the start we were using INFLECTION, after
> removing
> that the process isn't constantly using 100% CPU but is still peaking at
> full
> CPU usage.
> We are using Sql Server 2005. The machine running the Sql Server has two
> AMD
> Opteron 64 bit Dual Core 2.4 GHz with 16 GB of RAM.
> My question is if anyone has some input of how to proceed with the
> identification of the problem? What limitations of the amount of queries
> (both selects and changes of the content) have a full text index? Dose
> anyone
> has some documentation/literature about this subject to recommend?
> Thanks in advance!
> Regards,
> Karl
|||Laxy spool might indicate the wrong join algorithm selection.
You might consider the following workarounds:
1) insert the results of containstable into a temptable (e.g. clustered on
ftkey) and then join the temptable with the rest of your query.
2) You mentioned CONTAINS returns 100 rows. Is that by virtue of data
distribution or due to top_n_by_rank parameter? If you're not using the
latter, I strongly recommend doing so.
3) If there's a specific query plan that you find optimal (e.g. the one you
observed in testing), and the one you're getting now is different, you can
consider using the OPTIMIZE FOR hint to hint the CONTAINSTABLE parameter
value that produced the optimal plan.
Hop this helps,
Regards,
-Denis.
"Hilary Cotter" wrote:
> When you remove full-text from the query what is your performance like. I
> strongly suspect that your problem is that you are running into a lazy spool
> which will make your SQL FTS performance extremely slow. You will need to
> re-write your queries to remove lazy spool.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "karl_a" <karl_a@.discussions.microsoft.com> wrote in message
> news:F02AAEAF-0F3E-4847-8210-2D36EF61EC9A@.microsoft.com...
>
>
Tuesday, March 20, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment