Nates Stuff: SQL Server

個人檔案Nates Stuff相片部落格清單更多

工具

說明

部落格

摘要

排列方式：

<< 第一頁 < 上一頁下一頁 > 最後一頁 >>

3月26日

Gigantic Bug in SQL Server 2005 Full Text Query

You know you've done something wrong when it takes 30+ seconds to run a full-text query. The most annoying part of this bug is the fact that it is something very small and inconsequential that "triggers" the bug. It's a lot like an murder investigation where the killer turns out to be a Nun.

Here is the setup---

I have a full text index on a text field in my database. This table happens to be our Phrase table (for data localization)
I do a very simple full text search on that field in a SQL Server proc
I want to test my proc so I add some fixed input
Setting a value after the fact causes my full text to be *very* slow!!!

Here is the code

ALTER PROCEDURE [dbo].[AdvancedTrackSearch_TEST] ( @TrackTitleCrit xml ,@TrackDescCrit xml ,@RecordLabelCrit xml ,@CategoryCrit xml ,@ComposersCrit xml ,@TrackDuration int ,@TrackDurationOperator int ,@LangID int ,@PageSize int ,@PageNumber int ,@UserID int ) AS BEGIN   -- Temporary input DECLARE @TrackTitleStr nvarchar(2000) SET @TrackTitleStr = '"booty poppin"'   -- *********************************** -- * THIS LINE CAUSES THE PROBLEMS * -- * BY SIMPLY REMOVING THIS LINE * -- * THE QUERY TIME WILL GO FROM 30+ * -- * SECONDS TO LESS THAN 1 SECOND! * -- *********************************** SET @LangID = 66     DECLARE @TitlePhrases TABLE ( PhraseID int, DictionaryID int )   INSERT INTO @TitlePhrases SELECT PhraseID, DictionaryID FROM [dbo].[Phrase] WHERE --PhraseID NOT IN (SELECT PhraseID FROM @TitlePhrases) CONTAINS([TEXT] , @TrackTitleStr) --[Text] like @Phrase AND LanguageID = @LangID   -- Check the output SELECT * FROM @TitlePhrases   END   -- ****************************** -- NOW WE TRY TO EXECUTE THE PROC -- ****************************** DECLARE @return_value int   EXEC @return_value = [dbo].[AdvancedTrackSearch_TEST] @TrackTitleCrit = NULL, @TrackDescCrit = NULL, @RecordLabelCrit = NULL, @CategoryCrit = NULL, @ComposersCrit = NULL, @TrackDuration = NULL, @TrackDurationOperator = NULL, @LangID = NULL, @PageSize = NULL, @PageNumber = NULL, @UserID = NULL   GO

It seems so simple and stupid but setting the LangID (even if we pass null into the actual query) causes the query to take a substantially longer time doing the full text search.

I hope someone finds an explanation!

--Nathan Zaugg

UPDATE:

As a matter of fate, me and my friend Phil Gilmore stumbled on the answer. The trick is to "SET ARITHABORD ON" for one of the first things that you do in the query. This is usually linked to arithmetic exceptions and overflows, but for some reason with out it there is little chance your query will perform. If you look at the difference between the execution plans before setting that variable vs. after you can see that the execution plan changes a lot! After ARITHABORT is ON the execution plans are again identical! Check out my post on MSDN forums.

-- Nathan Zaugg

1月23日

User already exists in current database SQL Server 2005 Error 15023

I posted a blog entry a while back about how to change the owner of a database. What that still does not solve is the problem you have when there is a database user in the database you restored and there is a database user already in your system, each with the same name. While it seems intuitive that it should just work, it doesn't! The user in the database is essentially an orphan.

To find such orphans in your newly-restored database we run this command:

EXEC sp_change_users_login 'Report'

This simply gives us a list of these orphan logins. Generally you want to map the two users back to each other and we can do that with this command:

EXEC sp_change_users_login 'update_one', 'WebUser', 'WebUser'

You can run the first command again to see if it worked. In my case I had to try to login a couple of times before the I could get it to login. If that fails to work there is another, more powerful command you can run:

EXEC sp_change_users_login 'Auto_Fix', 'WebUser', NULL, 'MyWebUserPassword'

If you are too lazy to even do that, there are a few stored procedures on this blog entry that you can try running:

http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

I hope this helps someone besides just me! Drop me a line if this blog entry was useful.

--Nathan Zaugg