Plan Guides

In our production environment, we had a poorly performing stored procedure in terms of reads and CPU usage. A quick peek using the available dmv's gave us an insight into the plan and the parameters that were used to obtain that plan. That way, it was easy to simulate the behavior on a development environment.

Apparently, every morning, with a clean procedure cache, the procedure got called with certain parameters which resulted in a not very intuitive, but good plan with a cost of 3 reads. Subsequent calls with different parameters reused the cached plan, but that resulted in > 8000 logical reads and quite some CPU usage. A more intuitive plan resulting from typical parameters had a constant cost of about 8 logical reads for both types of parameters, which can give a really big performance boost, given the fact that the procedure gets called more than 40.000 times a day and delays a vital business process.

Because I'm not allowed to modify anything of the stored procedure (yep, it's CRM), I had to use a plan guide to 'guide' the engine to the more performant plan. There are several options: specifying the whole plan, add the parameters for the compilation, force recompile... I chose the parameter hint.

--solution, add plan guide
exec sp_create_plan_guide
@name = N'Plan for procedure p_GrantInheritedAccess UPDATE'
, @stmt = N'UPDATE poa1
SET InheritedAccessRightsMask = poa1.InheritedAccessRightsMask poa2.AccessRightsMask poa2.InheritedAccessRightsMask 0x08000000
FROM PrincipalObjectAccess poa1 JOIN PrincipalObjectAccess poa2 ON (poa1.PrincipalId = poa2.PrincipalId)
WHERE poa1.ObjectId = @ReferencingObjectId
AND poa1.ObjectTypeCode = @ReferencingObjectTypeCode
AND poa2.ObjectId = @ReferencedObjectId
AND poa2.ObjectTypeCode = @ReferencedObjectTypeCode'
, @type = N'OBJECT'
, @module_or_batch = N'p_GrantInheritedAccess'
, @params = NULL
, @hints = N'OPTION(OPTIMIZE FOR(@ReferencingObjectId=''D4F70FD5-41FA-DB11-9668-0011258C53FD'',@ReferencingObjectTypeCode=4200,@ReferencedObjectId=''2052793D-41FA-DB11-9668-0011258C53FD'',@ReferencedObjectTypeCode=4200))'
SELECT * FROM sys.plan_guides

The result of this simple operation is a clean and transparent solution. I haven't put it into production though, because it stays a last resort, but it's always good to know your options.

MS CRM, from a database perspective

Some time ago, I was appointed as the database dude for our customized MicroSoft CRM 3.0 implementation. The project had already been put into production, but there was a major concern about the performance of the application, mainly on the database. And that was the time when I came into the picture.

We started the cleanup with resolving some major issues in the custom code. That code was written by rather inexperienced developers with little supervision on their creations. That resulted in some very interesting behavior on our production environment from time to time, at least if you're in for a challenge. The problems can be roughly categorized into two groups: locking issues and suboptimal plan usage, mostly due to wrong datatype usage.

After the resolution of the most urgent issues on the customization, I got the chance to take a look on the actual MSCRM database. And it isn't always a pretty sight. Sure, the system of a controlling Metabase and a generic customizable CRM database are pretty neat, but the statements that I see passing in the profiler traces are often quite unmanageable. In exchange, the application itself offers very flexible functionality, but it doesn't always seem to scale that well to the data volumes we are handling in our company.

Well, working with MS CRM 3.0 is proving to be an enriching experience. You actually shouldn't have to look at the database and you should definitely not temper with it, but Sql Server 2005 offers very interesting features to pinpoint the culprits and to help the engine a little in the right direction. More to come in hopefully a little more technical posts ;-).

SSIS BypassPrepare

Yesterday, we stumbled into an annoying bug of the RTM version of SQL Server 2005. A new version of a previous project of mine was being released with a modified SSIS package. One of the modifications consists of the usage of a mapped input parameter in an Execute SQL Task. It requires the BypassPrepare flag of that task to be changed to True to make this construct work. It was succesfully tested in development, acceptance and then, in production, we suddenly got the following error.

Executing the query "…" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This error lists almost everything that can possibly go wrong with an Execute SQL Task without giving any indication what actually went wrong. Sigh.

At first I thought that the problem was caused by the fact that our production environment didn't had SP1 and the necessary hotfixes like it should have. That's an essential factor indeed, but there was more to the problem. The trouble with the RTM version is that it doesn’t take this BypassPrepare into account. Whether you set this flag to true or false in the development environment, if you look at the actual xml that represents the package, you won't find a trace of this setting. This was fixed in SP1 (as correctly answered on the forums).

What I didn't know was that our operational DBA did export and import the package with his local client (SSMS) on which he didn't have SP1 either. Similar to the behavior in Visual Studio, the setting gets lost with SSMS in RTM version. After a quick install and verification, everything worked smoothly.

In the upcoming SP2 it is announced that the default of this setting will be changed to True, meaning that SSIS won't prepare that statement unless you explicitly specify it do so. It could have saved us some trouble. Valuable lesson learned is to keep all environments, and also the clients, always up to date.

Difference between @@IDENTITY and SCOPE_IDENTITY()

A colleague asked me today if he could use @@IDENTITY in the context of an insert stored procedure to return the generated value for an identity column. I recalled there was something about this function so I consulted the Books OnLine.
It states :

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

It took me a minute to find a good example to illustrate the difference between the two, but with a trigger I created the following example:

USE TempDB
GO
CREATE TABLE tst
( a int identity(1,1), s varchar(10))
GO
CREATE TABLE tst2
( a int identity(1000,1), s varchar(10))
GO
CREATE TRIGGER dbo.trgTst
ON tst
AFTER INSERT
AS INSERT tst2 SELECT inserted.s FROM inserted
GO
INSERT tst VALUES('a')
SELECT
@@IDENTITY AS [@@IDENTITY],
SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]
GO
DROP TABLE tst2
DROP TABLE tst

SCOPE_IDENTITY() will give you the value of tst.$IDENTITY, ignoring the identity value that is generated for table tst2 with the trigger after the insert into tst. @@IDENTITY will give you that value from tst2.$IDENTITY.

So the function we actually need in our case is not @@IDENTITY but SCOPE_IDENTITY().

$ROWGUID

We are in the middle of optimizing a big staging database that contains literally billions of uniqueidentifier-integer pairs over several tables. The database is used in a process to convert the nice little GUIDs of our production environment into keys that the reporting environment on Oracle of our enterprise can more easily deal with. Don't ask why we do it - I guess we lost that strategic battle :(

Anyway, when I was looking at some of the generic legacy code to identify the ROWGUID of a table, I remembered something about $ROWGUID. This automatically returns the ROWGUIDCOL of that table. Sadly, it's new in SQL Server 2005 and because we haven't had the chance to migrate that database, we still need to use some looping to investigate the COLUMNPROPERTY to finally get the name of the column that can be used in dynamic SQL to mimic the behavior of $ROWGUID:

USE TempDB
GO
CREATE TABLE SomeTable
(
ID uniqueidentifier ROWGUIDCOL
)
GO
SELECT $ROWGUID FROM SomeTable
GO
DROP TABLE SomeTable

SQL Server 2005 System Views Map

I always seem to forget the link for the great SQL Server 2005 System Views Map so I might as well dedicate a little post about it. The map gives an overview that seems quite chaotic at first, but when you zoom in you can find valuable information to be able to combine several related dmv's.

Be creative with BCP

My favorite colleague - you know the one :) - left me with a simple task before he left on a holiday. I had to finish this export for him that we decided to rewrite. He had to export several quite large recordsets to multiple XML-files. We agreed to use FOR XML EXPLICIT instead of a custom made SSIS component to be able to deploy it on our current production environment, still being SQL Server 2000.

When using FOR XML EXPLICIT, you don't get that many options to export the result to a file because of the special nature of that result. But with BCP, everything is possible - at least I thought so. Because of the complexity of the export, some data had to be staged temporarily in the stored procedure that retrieved the data and formatted the XML. And that's where the trouble started.
With the code below you simulate the core of the problem - which isn't FOR XML by the way.

USE TempDB
GO

CREATE PROC usp_bcp_test
AS
SET NOCOUNT ON
IF 1=0
BEGIN
DECLARE @Tbl TABLE (Id INT)
INSERT @Tbl VALUES (1)
END
SELECT 1 as ColA, 'test' as ColB
GO

DECLARE @Stmt VARCHAR(1024)
SET @Stmt = 'BCP "EXEC TempDB.dbo.usp_bcp_test" QUERYOUT C:\TEMP\tst.txt -c -S ' + @@SERVERNAME + ' -T'
EXEC master.dbo.xp_cmdshell @Stmt
GO

DROP PROC usp_bcp_test
GO

I try to use the output of the stored procedure as the 'query' for BCP, but this fails because of the INSERT-statement in the stored procedure, even though it's unreachable code. BCP gives the following feedback:

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column
NULL

Using Profiler, I discovered that BCP issued the following statement to determine the resultset it can expect.

SET FMTONLY ON EXEC TempDB.dbo.usp_bcp_test SET FMTONLY OFF

In the messages pane of SSMS you can see the following:

(0 row(s) affected)

(0 row(s) affected)

Which explains the fact that BCP complains about the stored proc: it expects the INSERT statement to give a columnlist for its resultset, which it doesn't have of course. Moving the SELECT clause to the begin of the stored proc makes this construct work because BCP only looks at the first resultset.

The solution to this problem is straightforward: split the retrieval and the formatting over multiple stored procs so that the stored proc used for the BCP only contains a simple SELECT-statement.

Nice little detail: I was only able to simulate this behavior in SQL Server 2005, not with SQL Server 2000.