SQL Server Performance

Odd FT + PK Query Behaviour After 2008 Upgrade

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trick, Jul 17, 2012.

  1. Trick New Member

    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:
    Code:
    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:
    Code:
    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:
    Code:
    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.

    Attached Files:

  2. Shehap MVP, MCTS, MCITP SQL Server

    Fundamentally , Query execution plans might differ from SQL Server version to SQL Server Version even if after update statistics and index rebuild due to the difference in query execution plans estimated by Query Analyzer of each one..

    But this is explicit between SQL Server 2000 and SQL Server 2005 and onwards due to big distance between indexing techniques ..

    Therefore our case here expresses the same issue which the below index might address it :

    createnonclusteredindexEmails_Index1 (fRecId) on Emails

    include (HTMLBody,fBody,fBodyFormat,fImportance,fReceivedTime,fSentOn,fSubject,fSenderEmail,fSenderName,fEntryId)

    with (fillfactor=80,data_compression=page)

    Kindly work out it and let me know your feedback
  3. Trick New Member

    Hi Shehap,

    Thanks for the suggestion, however the additional index has no impact on the performance I'm afraid. The database already have more than enough indexes to go by, the primary key and the full text index.

    Think I may have to rebuild the database in 2008 to being with and if that performs properly, see how it differs from the original.

    Regards,

    Richard.
  4. Shehap MVP, MCTS, MCITP SQL Server

    I am not sure about the other indexes if they fully fit with that expensive query evidenced much more that you worked out the assumptions of rebuild indexes and statistics update but still no stuck at performance…

    For the impact of this index on production , it can impact adversely in any of the cases below:

    · Your system has intensive OLTP transactions that need to minimize clustered index IO insert /update cost within query execution plan by reducing index sizes

    · Your index has tangible IO or CPU cost on DB that can impact potentially on OLAP transactions

    Hence, to verify these 2 points on another testing or staging environment prior to production , you can use either :

    · Query execution plan of relevant queries (OLTP and OLAP )

    · You can conduct stress test for your relevant OLTP & OLAP queries after applying index ( This is cane be dome using different tools like SQLQueryTest)

    Kindly let me know if any further help is needed

Share This Page