jump to navigation

SQL 2008 Fulltext Query Performance issue using FREETEXT and CONTAINS June 6, 2010

Posted by Chirag in Full Text Search, SQL 2005, SQL 2008, SQL 2008 R2, SQL Server 2008.
Tags: , , , ,
12 comments

Of all the things that have improved in SQL 2008 Fulltext seems to have taken a step backwards. We were helping one of our clients upgrade from SQL 2005 to SQL 2008. They are a 24×7 shop with a framework that supports various websites for their customers. Fulltext is heavily used for text based searches on the websites. We had set up a test rig for them on SQL 2008 to test their applications. The testing process revealed sever performance issues with the text based searches. We investigated and identified the cause of the performance degradation to be the FREETEXT clause used in a multiple OR condition.

Issue:
A fulltext query using the FREETEXT predicate in a multiple OR combination works magnitudes slower in SQL 2008 than in SQL 2005 like for like vanilla installs. We tested this in SQL 2008/R2 and the same issue persists so it pointed to an engine issue in the way fulltext query plans were generated and handled in SQL 2008.

Eg.

SELECT A.Col1,  A.Col2, B.Col1, B.Col1….
FROM TableA AS A LEFT JOIN TableB AS B ON A.ID = B.A_ID
WHERE FREETEXT((A.Name, A.Description), ‘SomeSearchText’)
OR FREETEXT((B.Name, B.Description),’SomeSearchText’)

The fulltext catalog encapsulates multiple tables which each have a single fulltext index.

Workaround:
After thorough investigation we identified a workaround for this issue, it is to separate the query using a UNION clause which has extremely good performance on SQL 2008/R2.

Eg.

SELECT A.Col1,  A.Col2, B.Col1, B.Col1….
FROM TableA AS A LEFT JOIN TableB AS B ON A.ID = B.A_ID
WHERE FREETEXT((A.Name, A.Description), ‘SomeSearchText’)

UNION

SELECT A.Col1,  A.Col2, B.Col1, B.Col1….
FROM TableA AS A LEFT JOIN TableB AS B ON A.ID = B.A_ID
WHERE FREETEXT((B.Name, B.Description),’SomeSearchText’)

Unfortunately, this leads to code bloat and may not be the best approach to resolve this issue as it could mean many man hours to update the related code which could be hundreds of stored procedures as was in our clients case.

We raised a PSS case with Microsoft to identify the root cause and possibly provide a solution for the same.

I have attached the repro scripts I provided Microsoft to help them recreate the issue:

Instructions to Reproduce the issue(Run in SQL 2005 and SQL 2008 instances preferably similar hardware/software settings):

  • Run the FREETEXT_Issue_CreateSchema.sql Script => this will create the schema and populate the tables and the fulltext catatlog
  • Wait for the fulltext catalog to finish population
  • In a separate window run the FREETEXT_Issue_Query.sql script, switch to the messages tab and compare the CPU times in the output.

Microsoft confirmed that this was a BUG in the SQL 2008/R2 and infact affected the CONATINS clause too. They confirmed that it would take a substantial design change to fix this issue. After much persistence they have confirmed that a fix will be released as a Cumulative update “soon”. I will update this blog with the appropriate link for the Cumulative update as soon as it is released.

UPDATE: (24th August 2010)
Microsoft has released a patch which resolves this issue in the SQL 2008 SP1 CU9 rollout. You can obtain it from here: http://support.microsoft.com/kb/2083921. I am still awaiting confirmation for SQL 2008 R2. Will update as soon as I have more news.

UPDATE: (27th January 2011)
Microsoft has released a patch which resolves this issue in SQL 2008 R2 CU4 rollout. You can obtain it from here: http://support.microsoft.com/kb/2264562. Although many readers have informed me that the workaround still works faster. Test it and let me know.

Multithreaded DBCC Checkdb when MAXDOP = 1 March 4, 2010

Posted by Chirag in Database Maintenance Plans, SQL 2005, SQL 2008, SQL Server 2008, T-SQL.
Tags: , , , , ,
1 comment so far

I know I am posting after a long time, so have just decided to wipe of the dust off my blogging keyboard, bring out my rusty creative writing skills and start posting again.

So here goes again… with a post to request your support and vote on a connect item if you feel my pain.

We have a customer who has several servers with many VLDB’s hosted on each of them. We run a nightly maintenance plan for general housekeeping and this tends to take a very long time to finish. An average run of CHECKDB with the PHYSICAL_ONLY option on weekdays takes over 4 hours and full CHECKDB on Sunday’s can take up to 6-8 hours to finish. This causes the job to overshoot the nightly maintenance window and in turn translates to performance problems for query response times as it is a 24×7 shop.

Besides all the necessary checks that CHECKDB does to check the integrity of the database it can run single threaded or multithreaded if more  processors are available. For reasons of query response and runtime predictability requirements the client has MAXDOP = 1 configured at the SQL Server instance level. This causes CHECKDB to run single threaded and contributes to extending the overall runtime of CHECKDB.

If like me you feel that there should be a setting to allow us to force CHECKDB to run multi threaded even if MAXDOP = 1 then please vote on this connect item: https://connect.microsoft.com/SQLServer/feedback/details/538754/introduce-setting-to-force-dbcc-checkdb-to-run-multi-threaded-when-maxdop-1

I have requested this to be implemented as:

- Trace Flag to allow global setting

- Switch on the DBCC CHECKDB command

Many thanks in advance if you vote and hopefully Microsoft introduce this soon!

SQL 2008 Service Pack 1 Released… April 8, 2009

Posted by Chirag in SQL 2008.
Tags: ,
add a comment

… and available for download from

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19

Failed:(0) Alter failed for server ‘ComputerName\\InstanceName’ February 9, 2009

Posted by Chirag in Database Maintenance Plans, SQL 2005.
Tags: , ,
3 comments

I had just setup a Maintenance Plan with the first step being “Database Integrity Checks”. I tried to test it and the Job failed. The job step was set to log the output of the step into a file. When I looked at the step execution log file, it had this vague error

Failed: (0) Alter failed for server ‘ComputerName\\InstanceName’

I looked in the SQL Error log and didn’t find anything relating to this.

So, I started a profiler trace on the server and executed the Maintenance Plan job again. I saw that it tried to run the following configuration option

EXEC sys.sp_configure N’user options’, 0

RECONFIGURE

and the job failed with the same error in the step execution log.

Why?… well we all know the strange ways in which some things work with SQL Server sometimes…. I then dug up a KB article on googling which mentioned the same issue… KB945067

This was not of much help as when I checked the settings the “user options” was already set to 0. I was totally confused. Then said lets give the KB suggestion a try. So I manually ran this

sp_configure ‘allow updates’, 0

reconfigure with override

which failed with the following error:

“Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.”

And voila… it seems I hit the jackpot. Now this was a 64-bit SQL Server Standard Edition Box and one of the other DBA’s had at some point tried to set the “awe enabled” option to 1(don’t ask me why… but someone got a ticking off). The “Lock Pages in Memory” was not set at the OS level causing the “Reconfigure” statement to fail.

Now this caused the Maintenance Plan Job to fail as it tried to unsuccessfully run a Reconfigure statement as it also tried to commit the pending AWE setting.

I reset the “awe enabled” setting to 0 and executed

reconfigure with override

commiting the correct settings. And the day was saved as the Maintenance Plan ran smoothly and I was off to fight another battle.

Microsoft Beta Exam Vouchers September 14, 2008

Posted by Chirag in Certification, SQL 2005, SQL 2008.
Tags: , , , ,
2 comments

Was just surfing through SQL stuff and came across this blog from Microsoft where they announce and frequently releaseBeta Exam Vouchercodes. These voucher codes give you a free shot at the new beta exams with Prometric Testing centres only. Bear in mind you have to rush for it and register ASAP as the voucher codes have limited availability. So hurry.

Also, once you complete the exam the results won’t be displayed immediately and can take upto 6-8 weeks for the result to show up on your transcript if you pass.

Well end of the day it’s free and if you pass it counts toward the final certification!

Clean up dropped Subscriptions September 14, 2008

Posted by Chirag in Replication, SQL 2005, SQL 2008, T-SQL.
Tags: , , , , , , ,
add a comment

If you are working in an environment utilising replication, you may find orphaned subscriptions show up on subscribers long after they have been dropped at the publisher. Well, why they aren’t cleared up when one drops a subscription or subsequently the publication itself.. you may ask… that’s a different question all together.

You can run a not so widely known command at the subscriber to clear out any orphaned subscriptions still listed on the subscribers. In fact, should I dare suggest it would actually be a great idea to implement this within your drop subscription process.

The command you can use is “sp_subscription_cleanup”. It has the following syntax:

sp_subscription_cleanup

[ @publisher = ] publisher ,

[ @publisher_db = ] publisher_db

[ , [ @publication = ] publication]
where,

@publisher => is the name of the Publisher

@publisher_db => is the name of the Publisher database

@publication => is the name of the publication, with a default of NULL. If NULL, subscriptions using a shared agent publication in the publishing database will be deleted

Execute this at the Subscriber on the subscription database to remove replication meta data from it.

Service Broker queue not being processed September 14, 2008

Posted by Chirag in Service Broker, SQL 2005, SQL 2008, T-SQL.
Tags: , , ,
add a comment

We were troubleshooting a problem with Service broker, and realised the queue was being populated but wasn’t being processed.

Further investigation revealed that since our database had been migrated from SQL 2000 to SQL 2005 the “is_broker_enabled” was set to “off” and hence the queue wasn’t being processed.

To enable it we ran the following command

ALTER DATABASE [databasename] SET ENABLE_BROKER

Now, this command when executed requires the session to have exclusive access to the database, so make sure you either close all other sessions active as well as inactive; or run the command with the termination options:

ROLLBACK AFTER integer [SECONDS] – Specifies whether to roll back after the specified number of seconds.

ROLLBACK IMMEDIATE – Specifies whether to roll back immediately.

NO_WAIT – Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

The SQL journey begins… September 14, 2008

Posted by Chirag in Uncategorized.
add a comment

I will try to make this a regular rant on my varied experiences within the world of SQL Server. I consider this to be my way of giving back to the SQL community as I myself have gained a lot from the vast resources of knowledge and personal experiences of SQL guru’s and novices alike. So let the quest begin…..

Follow

Get every new post delivered to your Inbox.