You are currently browsing the monthly archive for April 2008.
Simon Sabin posted about this a few weeks back but I hit the issue he described today so I wanted to write about it and what I had to do.
The issue I am talking about is related to the account Full Text host uses to perform out of process tasks. This account, in CTP6, is created when CTP6 is installed. However it will use whatever default expiry conditions you have within your infrastructure.
Now, all this was fine when we first did some testing. However, coming back to it today I needed to drop some full text indexes and create them again. I dropped them but when I tried to create them, the full crawl wasn’t working. After checking the error log, it was clear that root cause of this was the FDHost Account’s password had expired….
On our system, the user name was called FDH$MSSQLSERVER. As per Simon’s post, the first step is to change the password on your Server and set any expiry details you need to (e.g. not expire). Next you will need to sync this with SQL Server. There is Stored Procedure for this, which can be used as follows:
EXEC sp_fulltext_resetfdhostaccount
@username = ‘FDH$MSSQLSERVER’,
@password = ‘Katmai2008′
This worked nicely. However, to get the full text index to start to work you need to give it a little kick. You can RESUME the population of the index by running:
ALTER
FULLTEXT
INDEX
ON
[MyFullTextTable]
RESUME
POPULATION
If you are not sure of the indexes that haven’t been populated yet and want to resume all of them , you can run this simple query to generate the script needed. Then simple run the script that gets returned.
SELECT
‘ALTER FULLTEXT INDEX ON ['
+
OBJECT_NAME(object_id)
+
'] RESUME POPULATION ‘
FROM
sys.fulltext_indexes
WHERE has_crawl_completed = 0
By the way, this issue has been fixed in the RC0 (refresh) release due out in May. It will now ask you to specify an account to use for the FDHost process. Upgrading an existing install will also prompt you to change this. However, if anyone is using CTP6 currently you might have the same issue I experienced today.
This is direct from MSDN but I was asked about this specifically so I thought I’d post about it. If anyone has used full text search you will know that the default way it ranks results is pretty good. It uses a known method called Jaccard Coefficient. However, there are times when you might want something els.e. Sharepoint 2007 (and versions onwards I assume) uses Enterprise Search and supports the following ranking methods. I have no idea if or when this kind of thing will be supported in 2008 or future versions of SQL Server out of the box.
JACCARD COEFFICIENT (default if not specified)
Calculates ranking results from the relative proportion of matching terms, excluding any terms that are not matched.
DICE COEFFICIENT
Calculates ranking results from the frequency of multiple terms found together, compared with the probability that they are found in isolation.
INNER PRODUCT
Calculates ranking results by using the integral of the products of the ranks of the individual matching documents.
MINIMUM
Calculates ranking results from the lowest rank score from all the matching documents.
MAXIMUM
Calculates ranking results from the highest rank score from all the matching documents.
Now , if you want to specify the RANKMETHOD when you perform your ISABOUT query you can simply state the method you want to use.
ISABOUT ( <match_terms> RANKMETHOD <rank_method> )
An example of which is:
WHERE CONTAINS(Description, ‘ISABOUT(“computer”,”software”) RANKMETHOD INNER PRODUCT’)
I hope this helps clarify the different ranking methods there are.
I have just finished watching the Gadget show and wanted to blog about a neat personal, usb, linux-based firewall. You basically have to install the drive and plug in.. it works in XP/Vista (32bit I believe).
You can check out the details at the Yoggie site , http://www.yoggie.com/node/40.
If you are in the UK, Play.com seem to do the best deal at £49.99.
Ok, so not really a SQL Server post but security is important right? especially if you fly by the seat of your pants coding T-SQL on a laptop on the move..
Having not long arrived back in the UK and with good intentions to blog like mad during my trip to Seattle, my health whilst out visiting Microsoft unfortunately wasn’t permitting me to do much else after the work I was there to do.
I will be posting a more concise post later this week about the time I spent with iFTS. However, I would like to say it is certainly a version 1.0 product in it’s current CTP6 form.
We (as a company) will have issues replacing our Yukon (2005) boxes with 2008, partly due to the way we have improved performance in 2005; crazy, I know. So, if you use multiple ISABOUT terms, have laden your full text indexes with TOKENs to improve overall performance in 2005 you will have potentially a chunk of changes to your existing database(s) awaiting you.
It may be that we use full text in a way unlike any other company in the world… but this is unlikely.
I will also post some information about ISABOUT and WEIGHT terms, there isn’t too much on the internet about the usage of ISABOUT and how the WEIGHT affects the ranking so I will help to improve that.
The fantastic developers at Microsoft, specifically the full text search team , I am sure will be working like mad to resolve issues and improve the overall performance. There is a lot to be excited about in SQL Server 2008 and iFTS does have huge potential.
This may be only relevant to 2008 but if you find Fulltext index creation taking a long time to complete you may be affected by a potential bug in CTP6. If you are experiencing this problem you can try running the following statement prior to the creation of the index. This will disable the checking of the OFFICE filter signature.
sp_fulltext_service
‘verify_signature’, 0
On machines we have here, the signature does not appear to exist for the dll and therefore the crawl is taking a long time to complete.
Here is a summary of the new Full Text System Views in SQL 2008.
sys.fulltext_stoplists
A list of STOPLISTS in your database. STOPLISTS are database specific. STOPLISTS replace Noise Word Files.
sys.fulltext_stopwords
This will show you you’re STOPWORDS. You can filter this on the language and or stoplist of your choice. STOPWORDS are individual words that make up a STOPLIST and replace Noise Words in previous versions of SQL Server.
For more information on STOPLISTS, check out my SQL Agent’s post at:
http://sqlagents.wordpress.com/2008/04/20/noise-words-have-been-silenced/.
sys.fulltext_index_fragments
This is a great view; this will display a row for each full-text index fragment. I have posted before about this but basically if you see that you have between 30-50 fragments in a table you should be thinking of reorganizing (REORGANIZE) your fulltext index.
For a clear picture, use this query:
SELECT
OBJECT_NAME([table_id])
AS TableName,
COUNT([fragment_id])
AS Fragments
FROM
sys.fulltext_index_fragments
GROUP
BY
OBJECT_NAME([table_id])
HAVING
COUNT([fragment_id])
>=30
Well, this is the first of my blog posts about my trip to Seattle. This is not much more than an introductory hello from me in Seattle. I arrived with a stinker of a cold and due to sinus issues during the descent into Seattle I am currently, largely, deaf.
Not a great start to a week of intensive testing with iFTS and SQL Server 2008!.
As a company we rely on Full Text to delivery and with the advent of 2008 , it seems to work differently to how we expect. Therefore, the purpose of this visit is to ensure that 2008 can meet our businesses needs and what changes we should be thinking of implementing to make sure this happens.
In particular , I shall be testing the differences between the different CHANGE TRACKING methods of the full text catalog and the way in which joins to non-full text tables have been given a performance boost. We have made a lot of changes to our indexes (in SQL 2005) to ensure as much as possible is tokenized to negate the need for external joins. However, this does mean that our quieries use multiple ISABOUTS and WEIGHTing to bring back relevant results.
SQL 2008 is potentially a lot different. Future posts will be based on my work this week with Microsoft.
SQL Server 2005 or 2008 required for this.
I don’t pretend to be a fanboy of dynamic SQL but there are times when it is needed. I came across such a situation today where I needed to update an aging procedure which had lumps of hard coded T-SQL.
Consider this scenario. You have full text catalogs for each culture your system supports; in our environment this is only Dutch, British English and German (1043, 2057 and 1031). These have to be separate tables due to the nature of full text. However, for one of our processes we need to union join the results.
Now, in the original procedure this was achieved by hard-coding the table names. When a new culture is added to our system , we would have to change the procedure again. If this was the only procedure that needed changing I would almost consider doing this but we have 200+ procedures that have an element to them where the culture is used.
So, it was decided that we needed to change this and others so that the addition of a new culture is not an issue. Right, back to the original procedure. We already have a Culture table; this holds the CultureId, the Culture (as in en-GB, nl-NL) and the descriptive Culture (e.g. British English). For the purpose of dynamically building my query I need to produce a query that gives me a string that holds the Union T-SQL.
So you can see this in action, here is the T-SQL to create the Culture table and fill it with some data:
CREATE TABLE Culture (CultureId INT, Culture CHAR (5) , CultureLanguage VARCHAR (25)) INSERT INTO Culture (CultureId, Culture, CultureLanguage) VALUES (1043, 'nl-NL','') INSERT INTO Culture (CultureId, Culture, CultureLanguage) VALUES (2057, 'en-GB','') INSERT INTO Culture (CultureId, Culture, CultureLanguage) VALUES (1031, 'de-DE','')
Now you have a table with some culture values in, this is my solution to generating the Union T-SQL:
DECLARE @UnionSQL NVARCHAR (500) SET @UnionSQL = ( SELECT DISTINCT STUFF(( SELECT 'UNION ALL SELECT MyId FROM [Visits_' + T2.Culture + '] WITH (NOLOCK) ' FROM Culture T2 FOR XML PATH('')),1,9,'') AS Cultures FROM Culture T1 ) PRINT @UnionSQL
In our environment , all the culture based full text tables are named with the Culture extension so the select is really simple. The clever bit is the use of FOR XML PATH and STUFF. This produces the following output:
SELECT MyId FROM [Visits_nl-NL] WITH (NOLOCK) UNION ALL SELECT MyId FROM [Visits_en-GB] WITH (NOLOCK) UNION ALL SELECT MyId FROM [Visits_de-DE] WITH (NOLOCK)
For me, this is a really simple solution and it means that any new cultures added to the Culture table are automatically included in the procedure. I do have some additional checks prior to this to ensure the tables exist plus obviously a lot more code within the procedure itself.
This particular procedure runs once a day for a process we have , I am sure this may not be the best way to achieve the solution if you needed to run this 100 times a minute and I would welcome any comments on this scenario and alternative solutions. For instance, I know you can achieve the same result with CTEs.
Simon Sabin has found a bug in CTP6 of SQL Server 2008. If you use iFTS and have experienced issues as described by Simon please follow his suggestions. His post has been summarised here:
iFTS does some stuff out of process for security and reliability in CTP6 the run this process under a local user account FDH$<instance name> by default.
However this accounts password may expire or even may be set to change on next logon (depending on domain settings etc).
If you find iFTS is not working then go into Local Users and Groups. Select the FDH$ account and check the settings. If you need to change the password there is a stored procedure called sp_fulltext_resetfdhostaccount. You can use this to sync the password in SQL once you have changed it in the User manager.
You can read his full post here > http://sqlblogcasts.com/blogs/simons/archive/2008/04/11/sql-server-2008-breaking-bug.aspx
The script presented in this article is for SQL Server 2005 or SQL Server 2008.
Here’s the problem; SQL Server has no problem when you create a Stored Procedure that references a non-existing table. It has no problem when you use a column that is simply not there. It is not validated until it’s executed. All that is checked (parsed) is the syntax. This is made worse when the structure of your database changes or people within your organisation come and go and those one-off or test procedures are left in your database.
How much dead wood is left in your database? Who wants to remove a procedure, it may be used by some process somewhere but no one is actually that sure. Who is going to take the risk?.
Well, there are a number of ways to see procedures that are no longer in use – you can check the procedure cache and list out procedures that dont exist in the cache. This is quite a quick and simple way to see procedures that have not been used in a while. However, there might be procedures that are still valid – they still may work if run, whenever that may be. They still might be valid.
What we really want is a quick way to actually check the procedures to see if they still work against our current structure. We want to do this without actually executing them and potentially causing a 3 year old bug to appear or wipe out some table because a particular procedure was created to do just that, once , at some point in our application’s history.
As it turns out, it isn’t that straightforward but it can be done.
What I am going to show you here is a script which will obtain a list of your procedures, grab their parameters and then check each procedure , one at a time; if it turns out they are not valid they will be added to a temp table for listing at the end of the script.
First, I will outline the key steps. Then I will show the full script you can use. The first part of process is to list the procedures , their parameters and dummy (NULL) assignment. This will be used to execute the procedures.
SELECT [name]=OBJECT_NAME(SM.[object_id]), [type]=SO.[type], SO.[type_desc]
,[params] = (
SELECT
(
SELECT CONVERT(xml,(
SELECT STUFF((
SELECT ', '+[name]+'=NULL' AS [text()]
FROM sys.parameters
WHERE [object_id] = SM.[object_id]
FOR XML PATH('') ),1,1,'')))
FOR XML RAW, TYPE
).value( '/row[1]', 'varchar(max)' )
)
FROM sys.sql_modules SM JOIN sys.objects SO ON SO.[object_id] = SM.[object_id]
WHERE SO.[is_ms_shipped] = 0
AND SO.[type] = 'P'
Next, when it has the list of procedures it creates a test harness for the procudure. E.g.
CREATE PROCEDURE up_sqldev_SPCheck_exec
AS
EXEC up_TtDay_insert @Id=NULL;
SET FMTONLY ON;
EXEC up_TtDay_insert @Id=NULL; SET FMTONLY OFF
Then, finally it runs the test harness procudure; removes it and then moves onto the next procedure. Any errors are logged in a temporary table.
Use this script without warranty, we have found it very useful but please use wisely and in non-production environments until you are personally happy with it’s output:
SET NOCOUNT ON;
DECLARE @tblInvalid TABLE ( -- invalid objects
[type_desc] NVARCHAR(60), [name] sysname, [error_number] INT, [error_message] NVARCHAR(2048), [type] CHAR(2) );
DECLARE @type CHAR(2); -- object type
DECLARE @type_desc NVARCHAR(60); -- object type description
DECLARE @name sysname; -- object name
DECLARE @params NVARCHAR(MAX); -- parameters
DECLARE @sql NVARCHAR(MAX); -- dynamic sql
DECLARE curObjs CURSOR LOCAL FAST_FORWARD FOR
SELECT [name]=OBJECT_NAME(SM.[object_id]), [type]=SO.[type], SO.[type_desc]
,[params] = (
SELECT
(
SELECT CONVERT(xml,(
SELECT STUFF((
SELECT ', '+[name]+'=NULL' AS [text()]
FROM sys.parameters
WHERE [object_id] = SM.[object_id]
FOR XML PATH('') ),1,1,'')))
FOR XML RAW, TYPE
).value( '/row[1]', 'varchar(max)' )
)
FROM sys.sql_modules SM JOIN sys.objects SO ON SO.[object_id] = SM.[object_id]
WHERE SO.[is_ms_shipped] = 0
AND SO.[type] = 'P'
OPEN curObjs;
WHILE (1=1)
BEGIN
FETCH NEXT
FROM curObjs INTO @name, @type, @type_desc, @params;
IF (@@FETCH_STATUS != 0) BREAK;
IF OBJECT_ID('up_sqldev_SPCheck_exec',N'P') IS NOT NULL
DROP PROCEDURE up_compare_dbs_temp_invalid_sp_check;
SET @sql = N'
CREATE PROCEDURE up_sqldev_SPCheck_exec
AS
EXEC '+ @name +' '+ @params +';'
PRINT @sql
EXEC (@sql);
BEGIN TRY
BEGIN TRANSACTION;
SET @sql = N'SET FMTONLY ON; EXEC '+ @name +' '+ @params +'; SET FMTONLY OFF;';
PRINT @sql;
EXEC (@sql);
ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
IF ( ERROR_NUMBER() != 0 )
INSERT INTO @tblInvalid
SELECT @type_desc, @name, ERROR_NUMBER(), ERROR_MESSAGE(), @type;
END CATCH
IF OBJECT_ID('up_sqldev_SPCheck_exec',N'P') IS NOT NULL
DROP PROCEDURE up_sqldev_SPCheck_exec;
END;
CLOSE curObjs;
DEALLOCATE curObjs;
SET NOCOUNT OFF;
SELECT [type_desc], [name], [error_number], [error_message]
FROM @tblInvalid
ORDER BY CHARINDEX( [type], ' U V PK UQ F TR FN TF P SQ ' ), [name];
GO
I want to give most of the credit for this script to Hue Holleran, a personal friend of mine who regularly presents at SQL Server themed events. For those of you who caught his presentation on SQLCMD at the March 2008 SQLBits, he won presenter of the day. Myself and Hue bounced around various of versions of this until Hue came up with the winning script. This is also forming part of a bigger script Hue is working on , so stay tuned for an follow up post that I hope Hue does to explain this.
If you have any suggestions for improvement or have any general questions about this please comment on this blog entry.
