.Net'ers

Every once in a while when doing customization or development for MS CRM I need to run a SQL trace. Anyone who has ever watched a trace of SQL Server for MS CRM (or any enterprise level application, for that matter) knows that a lot of SQL gets generated.

In my case, I usually don’t want to see all the “background noise” SQL that the app generates for it’s internal processing – I’m more interested in what SQL is running to select, insert or update records. SQL Server has the option to add filters to filter out items you don’t want to see in a SQL trace.

Assuming you already know how to create a filter in SQL Server, you can add each of the following statements as a filter to the column “TextData” and filter out a lot of things that you would prefer not to see in the results. I hope it helps, of course, but I warn you that if I am filtering out something that you actually do want to see in the results, don’t blame me:)

NOTE: This is for CRM 4.0 and has not been testing in MSCRM 2011. I would say that it probably will not work very well.

%exec sp_executesql N'With TopSuspendedAsyncOperations(AsyncOperationId)%
%if exists (select * from WorkflowWaitSubscriptionBase (nolock) where IsModified = 1)%
%exec GetDBVersion @DBVersion=@p1 output%
%exec sp_reset_connection%
%exec sp_executesql N'update AsyncOperationBase%
%-- network protocol: LPC%
%exec sp_executesql N'SELECT OrganizationId, EventId, EventData, CreatedOn FROM Notification%
%exec sp_executesql N'select subscription.WorkflowWaitSubscriptionId, subscription.AsyncOperationId, subscription.EntityName,%
%if exists (select * from WorkflowWaitSubscriptionBase (nolock) where IsModified = 1)%
%and DeletionStateCode = 0',N'@lockedState int,@hostId nvarchar(62),@pausingStatus int,@cancelingStatus%
%exec sp_executesql N'SELECT Id FROM DataEncryptionKey  WHERE (((IsConfigurationRow = @IsConfigurationRow0))%
%exec sp_executesql N'SELECT Id, ColumnName, BigIntColumn, IntColumn, SmallIntColumn, TinyIntColumn,%
%exec sp_executesql N'SELECT Id, CreatedOn, Enabled, KeyType, ScaleGroupId FROM CrmKey  WHERE (((Id = @Id0)) ) AND (IsDeleted =
%exec sp_executesql N'SELECT Id, CreatedOn, Enabled, KeyType, ScaleGroupId FROM CrmKey%
%exec sp_executesql N'select sdkmessagefilter0.PrimaryObjectTypeCode as ''primaryobjecttypecode'',%
%exec sp_executesql N'SELECT LocalizedLabelId AS localizedlabelid,%
%exec sp_executesql N'select sdkmessage0.SdkMessageId as ''sdkmessageid'' from SdkMessage%
%exec sp_executesql N'update WorkflowLogBase set <a href="mailto:ModifiedBy=@ModifiedBy0">ModifiedBy=@ModifiedBy0</a>, <a href="mailto:ModifiedOn=@ModifiedOn0">ModifiedOn=@ModifiedOn0</a>, Message=NULL,%
%exec p_GetFullName @organizationid=%
%exec sp_executesql N'select workflow0.UIData as ''uidata'', workflow0.IsCrmUIWorkflow as ''iscrmuiworkflow'',%
%exec sp_executesql N'select systemuser0.AccessMode as ''accessmode'', systemuser0.SystemUserId%
%exec sp_executesql N'select workflowlog0.AsyncOperationId as ''asyncoperationid'',%
%exec sp_executesql N'select workflow0.WorkflowId as ''workflowid'', workflow0.PrimaryEntity as ''primaryentity'',%
%exec sp_executesql N'delete from WorkflowCompletedScopeBase%
%exec sp_executesql N'SELECT Id, KeyType, ActiveKeyId, Enabled%
%exec sp_executesql N'SELECT Id, ScaleGroupId, KeyType,%
%select sdkmessageprocessingstepimage0.CreatedOn as ''createdon''%
%exec sp_executesql N'SELECT Label AS label,%
%SELECT EntityRelationshipId AS entityrelationshipid,%
%SELECT CascadeAssign AS cascadeassign,%
%exec sp_executesql N'SELECT EntityId AS entityid,%
%exec sp_executesql N'SELECT LogicalName AS logicalname,%
%exec sp_executesql N'SELECT T1.LocalizedLabelId AS localizedlabelid,%
%exec sp_executesql N'SELECT RelationshipId AS relationshipid,%
%exec sp_executesql N'IF EXISTS (SELECT 1 FROM dbo.MatchCode%
%SELECT MatchCode FROM dbo.MatchCode%
%exec sp_executesql N'update WorkflowWaitSubscriptionBase%
%exec sp_executesql N'select distinct AsyncOperationId from WorkflowWaitSubscriptionBase%
%exec sp_executesql N'SELECT Id, HelpServerUrl, InstallOn, IsRegistered,%
%exec sp_executesql N'insert into AsyncOperationBase%
%exec sp_executesql N'SELECT T1.AttributeId AS attributeid,%
%exec sp_executesql N'select sdkmessagerequestinput%
%exec sp_executesql N'SELECT Id, KeyType, ScaleGroupId, ActiveKeyId%
%exec sp_executesql N'select sdkmessageprocessingstep0%
%AttributeId AS attributeid FROM AttributeLogicalView%
%FROM AttributePicklistValueLogicalView WHERE AttributeId%
%exec sp_executesql N'SELECT T1.Label AS label,%
%select VersionNumber from EntityView%
%select VersionNumber from AttributePicklistValueView%
%select VersionNumber from EntityRelationshipView%
%select VersionNumber from RelationshipView%
%select VersionNumber from LocalizedLabelView%
%select VersionNumber from AttributeView%
%select VersionNumber from AttributeLookupValueView%
%select VersionNumber from EntityRelationshipRoleView%
%select VersionNumber from EntityRelationshipRelationshipsView%
%select VersionNumber from ViewAttributeView%
%exec sp_executesql N'select transactioncurrency0.ImportSequenceNumber%
%exec sp_executesql N'SELECT EntityId AS entityid FROM EntityLogicalView%
%exec sp_executesql N'SELECT AttributeTypeId AS attributetypeid,%
%exec sp_executesql N'SELECT Id FROM Organization  WHERE (((UniqueName%
%FROM AttributePicklistValueLogicalView WHERE AttributeId%
%exec sp_executesql N'SELECT T1.Label AS label,%
%exec sp_executesql N'exec p_GrantInheritedAccess @referencingId, @referencingOTC,%
%exec sp_executesql N'select contact0.OwningBusinessUnit as ''owningbusinessunit''%
%case when WorkflowState is null then 0 else 1 end%
%exec sp_executesql N'select asyncoperation0.AsyncOperationId as ''asyncoperationid''%
%exec sp_executesql N'SELECT Id, LOWER(SqlServerName) FROM Organization%
%isvalidforadvancedfind FROM EntityLogicalView%
%exec sp_executesql N'SELECT T1.AttributeTypeId AS attributetypeid,%
%exec sp_executesql N'SELECT ReferencedEntityId AS referencedentityid,%
%exec sp_executesql N'select workflowdependency0.CreatedBy as ''createdby'',%
%exec sp_executesql N'select workflow0.WorkflowId as%
%exec sp_executesql N'select asyncoperation0.OwningUser%
%exec sp_executesql N'select workflow0.WorkflowId as%
%create table #CascadeCollect(id INT IDENTITY(1,1)%
%exec sp_executesql N'if (NOT EXISTS(select 0 from PrincipalObjectAccess%
%exec sp_executesql N'select systemuser0.OrganizationId as%
%exec sp_executesql N'select privilege0.PrivilegeId as%
%exec sp_executesql N'SELECT Id, LicenseKey, ProductId, InstallOn FROM ConfigSettings%
%exec sp_executesql N'select asyncoperation0.OwningUser as %
%#CascadeCollect%
%select organization0.SystemUserId as%
%insert into WorkflowLogBase%
%select sdkmessageprocessingstepimage0.SdkMessageProcessingStepImageId%
%exec sp_executesql N'exec p_GetCrmUserId @OrganizationId, @AuthInfo'%
%SELECT Id, DefaultOrganizationId FROM Deployment%
%exec sp_executesql N'SELECT Id, UserId FROM SystemUserOrganizations%
%exec sp_executesql N'SELECT Id, AuthInfo FROM SystemUserAuthentication%
%SELECT M.ObjectId FROM dbo.MatchCode%
%exec sp_executesql N'select subscription.WorkflowWaitSubscriptionId%
%and StatusCode in (@pausingStatus, @cancelingStatus)%
%exec sp_executesql N'select timezonedefinition0.TimeZoneCode%
%exec GetMyRunningJobs%
%exec sp_executesql N'select sdkmessagefilter0%
%exec sp_executesql N'select sdkmessagerequestfield0.Name%
%KeyType, ScaleGroupId FROM CrmKeySetting%
%select report0.Name as ''name'', report0.ReportTypeCode as%
%from TimeZoneRule as timezonerule0%
%exec sp_executesql N'select queue0.QueueId%
%truncate table #SyncEntry%
%create table #SyncEntry%
%exec sp_executesql N'update Subscription set LastSyncStartedOn%
%exec sp_executesql N'select subscriptionclients%
%exec sp_executesql N'select displaystring0.DisplayStringId%
%drop table #SyncEntryIds;%
%select convert(bigint, min_active_rowversion()-1)%
%select ObjectTypeCode, SyncState,%
%insert into SubscriptionSyncInfo%

Tags: ms crm, SQL Server, SQL Trace, Trace Filter

This entry was posted on Tuesday, March 15th, 2011 at %I:%M %p and is filed under MS CRM, SQL, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.