November 10, 2010 at 6:58 am
We have recently migrated our DB from SQL 2005 to SQL 2008.
One stored procedure is always cauing the issue in new sql server 2008.
When I run it first it is taking around 33 seconds to display the result but later attempts
it is taking less than 3 seconds.
After further investigation on the stored proc, i found the problem with full text catalog
For testing purpose, i am just selecting the data from full text catalog.
If I run the following sql statement on SQL Server 2005, it is returning the results very fast where
as in sql server 2008, it is taking around 33 seconds(only first time, second time it is very fast).
select * from containstable(tbl_name, fieldname,'1607')
In SQL 2008, when I cancel the above query, I am seeing following error message
Msg 30053, Level 16, State 102, Line 1
Word breaking timed out for the full-text query string. This can happen if the wordbreaker took a long time to process the full-text query string, or if a large number of queries are running on the server. Try running the query again under a lighter load.
Query was cancelled by user.
but I run the following sql statement on both SQL Server 2005 and 2008, it is taking same time
select * from containstable(tbl_name, fieldname,'(isabout("1607 *")) or (isabout ("1607*") )')
What is the issue? could anyone help me on this please?
November 10, 2010 at 8:02 am
In SQL 2005 the full text search engine was outside of sql server engine there for it was not able to take advantage of the sql server statistics.
The query optimizer used a best guess estimate when creating a query plan. if you take a look at the query plan you will see that FTS probably did a guess of 10,000 rows.
In 2008 the engine is inside of the SQL server and makes use of statistics.
The first query you are running is more then likely gathering statistics and creating a QRY plan..the second time around it is using the query plan that has been created there for it does not need to create a new query plan just a reuse of the old one.
November 10, 2010 at 8:08 am
Thank you for your response.
But I wonder why this is very fast even first time in sql 2008?
select * from containstable(tbl_name, fieldname,'(isabout("1607 *")) or (isabout ("1607*") )')
November 10, 2010 at 9:01 am
if you are asking why 2008 is faster then 2005. the query plan is much more efficient in 2008.
If you looked at both query plans you would probably fine that one is doing a index scan(2005 almost always did a scan,unless you forced it into a seek) and 2008 is doing an index seek.
this is again due to the fact SQL is making a query plan based of statistics instead of an educated guess as in 2005
November 10, 2010 at 9:04 am
looking back at the original question you said the first time it took 33 sec?
and the sec question you say the 2008 first time it was very fast....I am little confused on the exact question
November 10, 2010 at 9:09 am
Hi, I have provided two queries in my first post
one is
select * from containstable(tbl_name, fieldname,'1607')
and another one is
select * from containstable(tbl_name, fieldname,'(isabout("1607 *")) or (isabout ("1607*") )')
First one is taking 33 seconds when I run on SQL 2008 for first time but that is fine on SQL 2005
where as second query is fast on both SQL 2008 and 2005.
You have answered saying that "In 2008 the engine is inside of the SQL server and makes use of statistics The first query you are running is more then likely gathering statistics and creating a QRY plan..the second time around it is using the query plan that has been created there for it does not need to create a new query plan just a reuse of the old one."
I wondered why second query runs fast? is it clear now?
November 10, 2010 at 9:33 am
in order to test them both you need to go to your dev box clear the cache and memory and then run 2008, then clear out the cache and memory and rerun. DONT NOT RUN THESE ON PRODUCTION
DBCC FREEPROCCACHE
Removes all elements from the procedure cache.
DBCC DROPCLEANBUFFERS - Removes all clean buffers from the buffer pool. -- Memory cache
my guess the will be closer to the same, you should not see 33 sec no matter what, the optimizer should never take that long to come up with a qry plan since it does not go through every possible plan.
give that a try and let us know of the results....
if there is a big diff after running this test then you need to look at the qry plans to figure out more
November 10, 2010 at 9:48 am
Thank you for your response.
I have run the DBCC commands and run the query.
Now the query returns the result very fast 🙂
I will run the query tomorrow again without running DBCC commands and will see whether
the query returns results within few seconds or not
November 10, 2010 at 9:54 am
so if you run it tomorrow one can be slower then other depending on if the data is in cache.
since both SHOULD reuse the same qry plan you will not have an issue.
if you turn on follow the example below this will let you know what in cache and what is not, just replace the qry with yours and you will be good to go
USE AdventureWorks2008R2;
GO
SET STATISTICS IO ON;
GO
SELECT *
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET STATISTICS IO OFF;
GO
Table 'ProductCostHistory'. Scan count 1, logical reads 5, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply