You are currently browsing the category archive for the 'Article' category.
This is a great article on adding dynamic styling to reports, if I dont say so myself.
http://www.simple-talk.com/sql/sql-tools/reporting-services-with-style/
I was lucky enough to visit Microsoft during the CTP period and was testing out integrated full text search in 2008. An issue we experienced was that full text can be slow when there is a high number of updates to the index and is caused by blocking on the docidfilter internal table.
It appears to happen if:
- You have AUTO tracking on your full text indexes; although we changed it to manual and still had this issue.
- You experience Full-text queries taking a long time to execute; normally when updates are happening at the same time so you might only see this in production.
- One or more of your queries are complicated or take some time to complete.
You can check to see if your system is slow due to this by:
- SELECT * FROM sys.dm_os_wait_stats statement , it shows very high wait times some of the locks.
- Running Sp_who2; it should consistently show that the full-text gather is blocking full-text queries and, in turn, is being blocked by the queries.
The current work around for this issue is to use a global trace flag microsoft kindly enabled on the RTM build. To use this, type the following Transact-SQL statement:
DBCC TRACEON (7646, -1)
This has a minor side-effect.. which may or may not be important in your organisation. Certainly in ours, it is not. Your full text result may be ever so slightly out of date – e.g. you might return or not return a particular document from the index. However, this issue relates to the dirtyness of an index in terms of 10’s of milliseconds. Important? Unlikely….
I believe there is a knowledge base article on this.
Some great 2008 material here:
http://sqlserver2008jumpstart.microsofttraining.com/content/info.asp?CcpSubsiteID=69&infoid=27
We have an issue currently with the Word Breaker in Full Text Search. Using SQL 2008 , we were able to identify what was actually being parsed using the new sys.dm_fts_parser function.
In German they have compound words. Using the German Word Breaker it should split these compound words out. If you use the following query in SQL 2008 you will see this works for the word used in the example:
SELECT * FROM sys.dm_fts_parser
(‘wirtschaftsprüfung’, 1031,0,0)
However, not all german compound words are split. If you use the same function with ‘Installationsingeneur’ :
SELECT * FROM sys.dm_fts_parser
(‘Installationsingeneur’, 1031,0,0)
It does not split the word at all.
I want to ask anyone out there if there is an answer to this.
This is an interesting article on an issue with LINQ to SQL and what SQL Server does with procedure caching coming from LINQ.
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/linq-to-sql-queries-involving-strings-ca
In summary, each where condition depending on the size of the value will cause a different plan to be created – e.g if the value passed into a WHERE clause was ‘This Value’ and another was ‘Another Value’ – two entries will be kept in the procedure cache… one a NVARCHAR (10) and another NVARCHAR (13)..
I have no checked SP1 to see if this is resolved. If anyone knows, please let me know.
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.
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.
I attended the SQLBits conference on the 1st March and at this event a “developer” (not a SQL Developer I will add) did a quick overview presentation of LINQ; this wasn’t the full session on LINQ that was also presented that day but one of the open mic sessions. He was a very animated chap with a loud Hawaiian shirt but what concerned me (apart from the shirt) was what he said and his view on LINQ.
He presented LINQ as an excellent way to cut corners and query databases and basically said that because Microsoft know what they are doing you can use the T-SQL LINQ it outputs , without having to think about coding SQL again. He even went on to to say that you can use the output from LINQ to quickly build stored procedures; copy and paste and there is your database stuff done.
Now, coming from a SQL Developer perspective I was a little shocked at this – not because I thought my job was in jeopardy (“hey, just use LINQ and you wont have to write another line of SQL”) but that people actually might believe the hype around LINQ and other developers might think the same way.
At the end of the day LINQ is great at creating boiler-plate SQL access code;making it easier to get up and running and quickly. A good way to start your model, something you can expand on. However, for pure performance the T-SQL it generates will (currently) not touch what a good SQL Developer can squeeze out of SQL Server. It just couldn’t be used for a high percentage of business applications.
My recommendation is to still use Stored Procedures via LINQ (LINQ does allow you to easily substitute the SELECT, UPDATE , INSERT and DELETE calls for each object with a Stored Procedure) for all but the simplest objects, possibly keeping the boiler-plate LINQ to SQL output for lookup tables or non-critical tables.
What do other developers and SQL Developers think about LINQ?
