July 18, 2012 at 2:26 am
Hi All,
I'm stuck on a problem that seems simple enough, but is evidently beyond the scope of my manual. I have a table full of around 600,000 e-mails, which is queried via a basic web interface. It has a full text index on a few fields and an integer clustered primary key; pretty standard stuff.
After upgrading from SQL Server 2005 to 2008R2 the query has started to time out. I have rebuilt all the indexes and updating all the statistics, with no improvement. It seems that 2008 has an odd idea on how best to plan the execution. Hopefully the following will help explain the problem:
Select single record using PK:
SELECT e.* FROM dbo.tEmail AS e
WHERE (e.fRecId = 574501)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'tEmail'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 9, lob physical reads 0, lob read-ahead reads 1.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Select 150 records using FT:
SELECT e.* FROM dbo.tEmail AS e
WHERE CONTAINS(e.fSubject, '89249-1')
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 4 ms, elapsed time = 4 ms.
Informational: The full-text search condition contained noise word(s).
(150 row(s) affected)
Table 'tEmail'. Scan count 0, logical reads 468, physical reads 0, read-ahead reads 0, lob logical reads 1555, lob physical reads 0, lob read-ahead reads 146.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 100 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
All perfectly acceptable performance. However, if you combine the two it all starts going wrong:
SELECT e.* FROM dbo.tEmail AS e
WHERE CONTAINS(e.fSubject, '89249-1') OR (e.fRecId = 574501)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Informational: The full-text search condition contained noise word(s).
(150 row(s) affected)
Table 'tEmail'. Scan count 1, logical reads 15212, physical reads 0, read-ahead reads 0, lob logical reads 1555, lob physical reads 0, lob read-ahead reads 146.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 54843 ms, elapsed time = 58298 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
That's nearly a minute to do a job that should take a few milliseconds, which was working fine on SQL Server 2005.
I'm pretty stumped, does anyone have an ideas please? Or perhaps you could point me in the direction of some documentation that might help?
I have attached the actual execution plan so you can see why it might be going about it the wrong way.
Many thanks, Richard.
July 18, 2012 at 2:38 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2012 at 2:42 am
Ooops, sorry school boy error; forgot the attachments! Cut-n-paste fail.
July 18, 2012 at 4:00 am
Hi, more a workaround than a solution, but could you change the query to union the two selects together?
Should help performance while you're looking for something more permanent.
When you said you rebuilt the indexes, I assume you meant the FT indexes too - I know that's generally a step you want to take after an upgrade.
Cheers
July 18, 2012 at 4:11 am
Hi Gaz,
Good idea, like the lateral thinking. Yes, rebuilt the FT index too (~500Mb), the FT query on it's own performs perfectly. Such strange behaviour for such a simple query!
R.
July 18, 2012 at 6:43 am
Thanks, hope it helps. Agree it's a strange one!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply