SQL Server Performance

LIKE query slower in SQL2005 than in SQL2000

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by pinformaticien, Apr 11, 2007.

  1. pinformaticien New Member

    Hi,

    I had a lot of performance issues when migrating from SQL 2000 (32 bits) to SQL 2005 (64 bits), so I tried to setup a testing environnement to achieve some tests :
    - Server1 : Win2003 x64, with SQL 2000 32-bits (8.0.2039)
    - Server2 : Win2003 x64, with SQL 2005 x64 (9.0.3054)

    Server1 et Server2 are exactly the same : dual-Xeon 3.6 Ghz, 3G RAM, 2x 73go 15k SCSI disks (RAID mirroring). They had exactly the same performance when configured with the same SQL versions.

    For that really simple test, there is only 1 database, and 1 table, named "Products", that contains only two columns. The database and table have been created from scratch with SQL Management Studio for SQL2005, and Enterprise Manager for SQL2000, so with default options, last compatibility levels, etc. Details for columns :
    - ProductID: Primary key, auto-increment, with a clustered index
    - NSearch: NvarChar(2000), collation Latin1_General_BIN, Not null, default ('')

    The data loaded into the Products table consists of 2,550,000 rows, only in low case, and without any accent. The indexes have been rebuilt on both servers after data insertion. Now here is a sample test query:

    SELECT COUNT(*) FROM dbo.Products
    WHERE (NSearch LIKE '%test%' OR NSearch LIKE '%te st%' OR NSearch LIKE '%te st%')
    AND NOT (NSearch LIKE '%a%test%' OR NSearch LIKE '%b%test%' OR NSearch LIKE '%c%test%')

    Here is the time to execute the query:
    - Server1 : 4 seconds
    - Server2 : 7 seconds

    Estimated subtree costs in Execution plans are almost the same in both case (40.49 vs 40.66), but the performance difference is quite huge... I expected SQL2005 would at least do as good as SQL2000, but it's not the case. Has anybody already heard about a such issue ? Can it be related to the 32 / 64 bits version ?

    Thanks,
  2. satya Moderator

    Do you have any FULLTEXT enabled or started on these 2 servers?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. pinformaticien New Member

    FullText services are stopped on both servers.
  4. satya Moderator

    Are the indexes optimized and SPs recompiled once the database have been deployed?

    See these links for any help or reference:
    http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
    http://support.microsoft.com/kb/298475

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. pinformaticien New Member

    The SQL installations are "brand new", tables too. In my test, there is no user SP on the Databases, and only one clustered index, with no fragmentation (rebuilt after data insertion)

    DBCC SHOWCONTIG scanning 'Products' table...
    Table: 'Products' (1733581214); index ID: 1, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 44551
    - Extents Scanned..............................: 5569
    - Extent Switches..............................: 5568
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 100.00% [5569:5569]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 0.16%
    - Avg. Bytes Free per Page.....................: 87.6
    - Avg. Page Density (full).....................: 98.92%
  6. MohammedU New Member

    Check the table/index statistics on both servers...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  7. pinformaticien New Member

    Updating the statistics doesn't change the results at all (as they where pretty up to date, considering that the data was loaded in one time, and did not change after). Same result with forced statistics (sampled at 100%), and still the same without statistics on the NSearch column (and temporarily disabling the "Auto Create Statistics" option for the DB).
  8. MohammedU New Member

    Also try creating the procedure with your select statement and run it...
    Is db compatibility is 90 on 2005 server?


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. satya Moderator

    Just another thought is to check the TEMPDB contention and difference between the both boxes.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page