SQL Server Performance Forum – Threads Archive
LIKE query slower in SQL2005 than in SQL2000
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,
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.
FullText services are stopped on both servers.
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.
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%
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.
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).
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.
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.
]]>