Unexpected dump

Had some fun with SQL Server 2000 (SP4 + hotfixes) today. While tuning a certain stored procedure, I made a stupid variation with an obvious error (wrong default value for a procedure parameter). It returned no problem on creation, it just dumps the connection on executing (and thus compiling) the procedure in a very nasty way. Don’t try this on production systems ;-). Luckily, this problem gets intercepted in Sql Server 2005 at runtime.

USE TempDB
GO
CREATE PROC proc_error
@Date DATETIME = blabla --no good!
AS
SELECT @Date + 1
GO
EXEC proc_error
GO
DROP PROC proc_error
GO

Msg 0, Level 19, State 0, Procedure proc_error, Line 4
SqlDumpExceptionHandler: Process 65 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

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 ;-).