SQL: Microsoft Dynamics – Rebuilding a Database View Named ‘FilteredContact’

Why?

Update: currently, I’m unable to rebuild this complex view due to SQL constraints. Hence, this article is a story of a workaround – tracing the object (db table) where a certain view is based. Indexes can often be created on such tables to improve SQL performance.

Because this MS Dynamics db object cannot be indexed, leading to high CPU consumption during runtime. Here’s an example of an occasion when SQL Server has pegged CPU utilization at 100% for hours.

How?

1. Read the ‘FilteredContact’ view build code to see that it’s based on the ‘Contacts’ view
2. Read the ‘Contacts’ view to realize that it’s based on the ‘ContactBase’ table
3. Add an index to ‘ContactBase’ table

Part 1: get the definitions views of ‘Filtered Contact’

--
-- Method 1: Obtaining definition (code) to create object
--
-- Object: 'FilteredContact' view
DECLARE @objectid int
DECLARE @code VARCHAR(MAX)
SET @objectid=(SELECT object_id FROM sys.objects
WHERE name = 'FilteredContact')
SET @code=(SELECT definition from sys.sql_modules
WHERE object_id = @objectid)

-- Print text blobs that are longer than 8000 chars
-- Be advised that there will be extra carriage returns
-- Hence printed code cannot be executed without fixing
DECLARE @count INT = 0
DECLARE @maxcount INT = (LEN(@code) / 8000) + 1
WHILE @count < @maxcount 
BEGIN
print(substring(@code, @count * 8000, (@count + 1) * 8000))
SET @count = @count + 1
END

An attempt to alter an existing view

USE TEST_MSCRM
GO
--ALTER VIEW [dbo].[FilteredContact] WITH SCHEMABINDING
--AS
--SELECT ...;

Another attempt to recreate view

When trying to create an index on such view using a t-sql such as the one shown below:

-- Creating an index
USE TEST_MSCRM
GO
CREATE UNIQUE CLUSTERED INDEX idx_FilteredContact
ON [dbo].[FilteredContact] (contactid,accountid);
GO

This error message occurs:

Msg 1939, Level 16, State 1, Line 4
Cannot create index on view 'FilteredContact' because the view is not schema bound.

Completion time: 2018-11-04T18:58:37.6112165-08:00

Red letters are added strings to the @code output from prior

USE [TEST_MSCRM]
GO
DROP VIEW IF EXISTS dbo.[FilteredContact];
GO
--
/*create view dbo.[FilteredContact] (
[accountid],
[accountiddsc],
[accountidname],
[accountidyominame],
...
) with view_metadata WITH SCHEMABINDING AS
select
[Contact].[AccountId],
--[Contact].[AccountIdDsc]
0,
[Contact].[AccountIdName],
[Contact].[AccountIdYomiName],
[Contact].[AccountRoleCode],
AccountRoleCodePLTable.Value,
[Contact].[Address1_AddressId],
[Contact].[Address1_AddressTypeCode],
Address1_AddressTypeCodePLTable.Value,
...
*/

The idea to recreate a table or view with the Schema Binding option so it can then be indexed to optimize runtime querying CPU utilization. However, some complex tsql has inner joins that runs into the constraints of ‘object referencing itself.’

Part 2: read ‘Contacts’ view

--
-- Method 2: Obtaining definition (code)
--
-- Object: 'Contact' view
DECLARE @sql VARCHAR(MAX);

SELECT @sql = definition
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('Contact');

-- Print text blobs that are longer than 8000 chars
-- Be advised that there will be extra carriage returns
-- Hence printed code cannot be executed without fixing
DECLARE @count INT = 0
DECLARE @maxcount INT = (LEN(@sql) / 8000) + 1
WHILE @count < @maxcount 
BEGIN
print(substring(@sql, @count * 8000, (@count + 1) * 8000))
SET @count = @count + 1
END

Error message to indicate ‘Contact’ view was based on ‘ContactBase’ table:

Msg 4512, Level 16, State 3, Procedure Contact, Line 437 [Batch Start Line 2]
Cannot schema bind view 'dbo.Contact' because name 'ContactBase' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Part 3: Creating a full-text index

It’s often better to run a SQL Profiler Trace and Database Engine Tuning Advisor to automate this process. Hence, the below instructions can be used for targeted database objects with much more effort than following the instructions provided in the above link.

Sample codes to create an index

-- Creating an index
USE TEST_MSCRM
GO
CREATE UNIQUE CLUSTERED INDEX idx_FilteredContact
ON [dbo].[FilteredContact] (contactid,accountid);
GO

-- Create a nonclustered index on a table or view
CREATE INDEX ContactBase ON ContactBase ('EmployeeId');

-- Create a clustered index on a table and use a 3-part name for the table
CREATE CLUSTERED INDEX ContactBase ON TEST_MSCRM.dbo.ContactBase ('EmployeeId');

Navigate to TEST_MSCRM > Tables > right-click ‘dbo.ContactBase’ > Full-Text Index > Define Full-Text Index

click Next to create an index basing on cndx_PrimaryKey_Contact

Select ‘EmployeeId’ > click Next > Next > Next > Next > Finish

click Finish

Sample code for ‘Contact’ view: