SQL server 2005 multicore problem with 'where column like' sqlcommand
in [Server Configuration]
|
Prev: Deploy a DDL trigger to prevent dropping tables for mulitple datab
Next: Deploy a DDL trigger to prevent dropping tables for mulitpledatab
From: Simon H on 5 May 2010 15:28 Good job on the book Erland - I have it sitting on my desk right now :-) I didn't realise the royalties went to charity - even better job ;-) S Erland Sommarskog wrote: > Motobit (info(a)pstruh.cz) writes: >> We have two systems: >> - new one dual procesor quad core 2,5 GHz W 2008 64, 24GB RAM + SQL >> 2005 >> - old dual dual core 2Ghz 4GB RAM Win 2003 + SQL 2005 >> >> The new one has 3-4 times better performance, good working, almost >> without problems. Except one simple command: >> >> select keycolumn from table where text like '%keyword%' >> >> from table with 1GB data, 100000 rows. >> >> The query runs the same time - 40 seconds on both new and old >> servers. And processor is 100% percent during the operation on both >> old server (2 cores) and on new (8 cores) also. So the command takes 4 >> times more processor time on the new server and the running time is >> the same. > > What collations and data types do you have? This operation is a lot > faster with a binary collation, and also with an SQL collation if > you use the varchar data type. Typicall the difference is in a factor > of 7 to 10. > > With a non-binary Windows collation, SQL Server will have to apply the > complex Unicode for many characters in the search string. > > Last year, I contributed to a book together with 52 other SQL Server MVPs. > My chapter deals with this kind of search, and I suggest some methods to > make them faster. Our royalties of this books goes to War Child > International; we did not make this book to make us some money. More > information here: http://www.sommarskog.se/yourownindex.html. > |