October 19, 2015 at 11:36 am
On simple queries, involving one smallish table, the DTA works. When I give it a complex query with several joins and large tables, it crashes. Is this expected? Trying to troubleshoot deadlocks on updates to a large table.
I've run DTA on a Server 2008 R2 machine with lots of memory and on a Server 2012 VM with 8GB of memory. ( also on Win 7, same result )
Faulting application name: DTAEngine.exe, version: 2011.110.5058.0, time stamp: 0x53740751
Faulting module name: ntdll.dll, version: 6.1.7601.18247, time stamp: 0x521ea8e7
Exception code: 0xc0000374
Fault offset: 0x000ce753
Faulting process id: 0x2da8
Faulting application start time: 0x01d10928327e9e64
Faulting application path: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\DTAEngine.exe
Faulting module path: C:\Windows\SysWOW64tdll.dll
Report Id: 7700bbe4-751b-11e5-9f47-000af70e6672
UPDATE SERVICE_REQUEST SET SERVICE_REQUEST_STATUS = 'PrintedOnPullReport', LAST_PRINTED_ON_REPORT_DATE = '2015-10-15 00:00:00', LAST_MOD_DATE_TIME = '2015-10-15 06:10:03.223', LAST_MODIFIER = 60417, PRINT_REQUEST_ID = 69432 FROM SERVICE_REQUEST AS CollateralGroupRequest02 CROSS JOIN TITLING_AGENCY_REQUEST_TRANSACTION_TYPE AS ServiceRequestTransactionType13 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedCollateralGroupItem24 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedOwnershipDoc25 INNER JOIN (OWNERSHIP_DOC AS OwnershipDoc26 LEFT OUTER JOIN (SCANNED_DOCUMENT_DATA AS ScannedDocumentData28 INNER JOIN BATCH AS Batch29 ON ScannedDocumentData28.BATCH_ID=Batch29.BATCH_ID) ON OwnershipDoc26.SCANNED_DOCUMENT_DATA_ID=ScannedDocumentData28.SCANNED_DOCUMENT_DATA_ID) ON ServicedOwnershipDoc25.OWNERSHIP_DOC_ID=OwnershipDoc26.OWNERSHIP_DOC_ID) ON ServicedCollateralGroupItem24.SERVICED_COLLATERAL_GROUP_ITEM_ID=ServicedOwnershipDoc25.SERVICED_COLLATERAL_GROUP_ITEM_ID) ON CollateralGroupRequest02.SERVICE_REQUEST_ID=ServicedCollateralGroupItem24.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID LEFT OUTER JOIN PRINT_REQUEST AS PrintRequest27 ON CollateralGroupRequest02.PRINT_REQUEST_ID=PrintRequest27.PRINT_REQUEST_ID WHERE ((CollateralGroupRequest02.REQUEST_TRANSACTION_TYPE = ServiceRequestTransactionType13.REQUEST_TRANSACTION_TYPE AND CollateralGroupRequest02.REQUEST_TRANSACTION_TYPE IN ( 'ReleaseToRegisteredOwner') AND CollateralGroupRequest02.IS_EXPEDITED_FROM_VAULT = 0 AND CollateralGroupRequest02.MAIL_CARRIER IS NULL AND CollateralGroupRequest02.RECORDED_REQUEST_DATE <= '2015-10-15 06:10:03.240' AND CollateralGroupRequest02.BUSINESS_PROCESS_STATUS = 'Open' AND CollateralGroupRequest02.IS_ON_HOLD_INDEFINITELY = 0 AND CollateralGroupRequest02.CLIENT_ID IN ( 11330, 11220, 12001, 11234, 12336, 10712, 12342, 10, 11303, 12332, 12572) AND CollateralGroupRequest02.REQUEST_TRANSACTION_TYPE NOT IN ( 'ContractRequest', 'ContractRequestRc', 'ContractRequestLc') AND OwnershipDoc26.BUSINESS_OBJECT_STATUS = 'OPEN' AND ServicedOwnershipDoc25.IS_PRIMARY_COLLATERAL_GROUP_ITEM = 1 AND (CollateralGroupRequest02.SERVICE_REQUEST_STATUS = 'EligibleForPullReport' AND CollateralGroupRequest02.FACILITY_ID = 1 OR (PrintRequest27.BUSINESS_PROCESS_STATUS = 'Open' AND PrintRequest27.LAST_MOD_DATE_TIME < '2015-10-15 06:05:03.223' AND CollateralGroupRequest02.SERVICE_REQUEST_STATUS = 'PrintedOnPullReport')) AND (CollateralGroupRequest02.IS_ACCELERATED_TITLE = 0 OR (CollateralGroupRequest02.IS_ACCELERATED_TITLE IS NULL )) AND (CollateralGroupRequest02.IS_ACCELERATED_TITLE = 0 OR (CollateralGroupRequest02.IS_ACCELERATED_TITLE IS NULL )) AND ((Batch29.CREATED_DATE_TIME <= '2015-10-15 00:00:00') OR (ScannedDocumentData28.SCANNED_DOCUMENT_DATA_ID IS NULL ))))
October 19, 2015 at 12:01 pm
Not sure how the DTA is going to help with a deadlock issue. Have you captured the deadlock graph to see where the deadlock(s) are occurring?
October 19, 2015 at 12:17 pm
Yes the deadlock graphs are showing page wait on one particular index. I just read an article recently suggesting use of DTA ( after searching on this issue ). But DTA just blows up on this update statement ( below ). The plan cache seems to be showing plans created but rarely re-used ( creation time and last execution are the same generally ) ORM generated sql.
CREATE INDEX [index_ServiceRequest_PullReport_FacilityId] ON [dbo].[SERVICE_REQUEST] ( [BUSINESS_PROCESS_STATUS], [CONCRETE_TYPE], [CLIENT_ID], [IS_ON_HOLD_INDEFINITELY], [RECORDED_REQUEST_DATE], [SERVICE_REQUEST_STATUS], [FACILITY_ID] )
INCLUDE ( [PRINT_REQUEST_ID], [SERVICE_REQUEST_ID]) WITH (FILLFACTOR=90, ONLINE=?, SORT_IN_TEMPDB=?);
sp_blitzIndex snippet:
Reads: 456,030 (455,912 seek 118 scan) Writes:6,434,658
0 singleton lookups; 8,328,793 scans/seeks; 0 deletes; 0 updates;
Size: 84,847,323 rows; 7.6GB
Row lock waits: 7; total duration: 6 seconds; avg duration: 0 seconds; Page lock waits: 30,510; total duration: 7,670 minutes; avg duration: 15 seconds; Lock escalation attempts: 39,749; Actual Escalations: 758.
CREATE TABLE [dbo].[SERVICE_REQUEST](
[SERVICE_REQUEST_ID] [int] IDENTITY(1,1) NOT NULL,
[BUSINESS_PROCESS_STATUS] [varchar](10) NOT NULL,
[VIRGINIA_CUSTOMER_SERVICE_CENTER_ID] [int] NULL,
[TITLING_AGENCY_LIENHOLDER_ID] [int] NULL,
[CONCRETE_TYPE] [varchar](150) NOT NULL,
[USR_ID] [int] NOT NULL,
[LAST_MOD_DATE_TIME] [datetime] NOT NULL,
[LAST_MODIFIER] [int] NOT NULL,
[TRANS_SEQ_NUM] [smallint] NOT NULL,
[RECORDED_REQUEST_DATE] [smalldatetime] NOT NULL,
[REQUEST_MECHANISM_TYPE] [varchar](8) NULL,
[CREATED_DATE_TIME] [datetime] NOT NULL,
[REQUEST_TRANSACTION_TYPE] [varchar](60) NULL,
[ADDRESS_ID] [int] NULL,
[IS_EXPEDITED] [bit] NULL,
[RESEARCH_QUESTION] [varchar](173) NULL,
[REQUEST_CATEGORY] [varchar](30) NOT NULL,
[XML_SERIALIZED_INSTANCE_ID] [int] NULL,
[LAST_EXPORT_TO_STATE_DATE] [datetime] NULL,
[LAST_STATE_RESPONSE_DATE] [datetime] NULL,
[STATE_CLOSED_DATE] [datetime] NULL,
[LAST_STATE_RESPONSE_CODE] [varchar](120) NULL,
[REJECTION_NOTE] [varchar](200) NULL,
[IS_REASSIGNED] [bit] NULL,
[PORTFOLIO_TRANSFER_NAME] [varchar](50) NULL,
[TRANSFER_TO_TITLING_AGENCY_LIENHOLDER_ID] [int] NULL,
[LIENHOLDER_PERFORMING_LIEN_FILING_ID] [int] NULL,
[VEHICLE_TYPE] [varchar](30) NULL,
[DOCUMENT_NUMBER] [varchar](30) NULL,
[NUMBER_OF_MEMO_TITLES] [smallint] NULL,
[OHIO_CONTROL_NUMBER] [varchar](9) NULL,
[DISPOSITION_CODE] [char](1) NULL,
[NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] [varchar](30) NULL,
[LIEN_RELEASE_SEQUENCE_NUMBER] [smallint] NULL,
[MAKE] [varchar](25) NULL,
[YEAR] [char](4) NULL,
[MANUFACTURER_ID] [varchar](30) NULL,
[ELECTRONIC_TITLES_TO_TRANSFER_PER_DAY] [int] NULL,
[FINANCED_DATE] [datetime] NULL,
[TITLE_NUMBER] [varchar](30) NULL,
[OHIO_CONTROL_NUMBER1] [varchar](9) NULL,
[NUMBER_OF_OPEN_LIENS] [smallint] NULL,
[PARENT_SERVICE_REQUEST_ID] [int] NULL,
[PREVIOUS_SERVICE_REQUEST_ID] [int] NULL,
[SERVICE_REQUEST_STATUS] [varchar](40) NULL,
[SPECIAL_MAIL_HANDLING_INSTRUCTIONS] [varchar](200) NULL,
[MAIL_CARRIER] [varchar](40) NULL,
[CLIENT_ID] [int] NULL,
[IS_ON_HOLD_INDEFINITELY] [bit] NULL,
[PRINT_REQUEST_ID] [int] NULL,
[ACTION_DATE] [datetime] NULL,
[DUPLICATE_TITLE_REQUEST_REASON] [varchar](30) NULL,
[FDI_DEPARTMENT_ID] [int] NULL,
[JURISDICTION_ID] [int] NULL,
[ELT_RESPONSE_ERRORERROR_ID] [int] NULL,
[TRANSFERRED_OWNERSHIP_DOC_ID] [int] NULL,
[ACCOUNT_IMPORT_ERRORERROR_ID] [int] NULL,
[TRANSFERRED_ACCOUNT_ID] [int] NULL,
[COLLATERAL_GROUP_STATUS] [varchar](50) NULL,
[XML_DOCUMENT_DATA] [text] NULL,
[IS_EXPEDITED_FROM_VAULT] [bit] NULL,
[IS_CONFIRM_RECEIPT] [bit] NULL,
[CHECK_REPORT_ID] [int] NULL,
[MANUFACTURER_ID1] [varchar](30) NULL,
[BUYERS_NAME] [varchar](60) NULL,
[DEALER_REPORT_NUMBER] [varchar](10) NULL,
[DATE_OF_SALE] [smalldatetime] NULL,
[RECEIPT_NUMBER] [varchar](30) NULL,
[DMV_FEES_COLLECTED] [decimal](9, 2) NULL,
[IS_USED] [bit] NULL,
[CALIFORNIA_TRANSMITTAL_FORMXML_SERIALIZED_INSTANCE_ID] [int] NULL,
[AMOUNT] [decimal](9, 2) NULL,
[AMOUNT_BILLED] [decimal](9, 2) NULL,
[CHECK_NUMBER] [varchar](10) NULL,
[PAYEE_NAME] [varchar](50) NULL,
[IS_RELEASE_DUE_TO_INSURANCE_LOSS] [bit] NULL,
[IS_LETTERS_PRINTED] [bit] NULL,
[LAST_SERVICE_REQUEST_STATUS_CHANGE_DATE_TIME] [datetime] NULL,
[IS_NOTARY_REQUIRED] [bit] NULL,
[NOTARY_STATE_ABBR] [varchar](10) NULL,
[AMOUNT_OF_CASH_OR_CHECK] [decimal](9, 2) NULL,
[IS_BILLABLE] [bit] NULL,
[SALES_TAX] [decimal](8, 2) NULL,
[END_OF_LEASE_PURCHASE_PRICE] [decimal](16, 2) NULL,
[MISCELLANEOUS_FEES] [decimal](8, 2) NULL,
[ODOMETER_READING] [int] NULL,
[LIEN_DATE] [datetime] NULL,
[CAN_DISPATCH_BE_COMPLETED] [bit] NOT NULL,
[DOES_REQUIRED_DUPLICATE_NEED_TO_BE_ORDERED] [bit] NOT NULL,
[STATUS_CHANGE_REASON] [varchar](25) NULL,
[OPERATION_CODE_ID] [int] NULL,
[PREVIOUS_SERVICE_REQUEST_STATUS] [varchar](40) NULL,
[CARRIER_TRACKING_NUMBER] [varchar](50) NULL,
[LUXURY_TAX] [decimal](8, 2) NULL,
[PROPERTY_TAX] [decimal](8, 2) NULL,
[ODOMETER_READING_DATE] [datetime] NULL,
[OWNERSHIP_DOC_TRANSFER_METHOD] [varchar](20) NULL,
[TRANSFER_FROM_TITLING_AGENCY_LIENHOLDER_ID] [int] NULL,
[PORTFOLIO_TRANSFER_BRANCH] [varchar](30) NULL,
[TTA_CLIENT_REQUEST_RELEASE_CODE] [varchar](10) NULL,
[LAST_PRINTED_ON_REPORT_DATE] [datetime] NULL,
[REQUEST_STATE_TO_PRINT_PAPER_TITLE] [bit] NOT NULL,
[CREATED_DATE] [datetime] NULL,
[CURRENT_ACTIVITY_SEQUENCE] [smallint] NULL,
[IS_LIEN_FILING_DUE_TO_DIRECT_LENDING] [bit] NULL,
[MAILED_TO_DMV_DATE] [datetime] NULL,
[OWNER_EIN_FID] [varchar](9) NULL,
[RELATED_BUSINESS_PROCESS] [varchar](30) NULL,
[EEE_CAUSE] [varchar](25) NULL,
[EEE_DATE] [datetime] NULL,
[POST_EEE] [varchar](8) NULL,
[POST_EEE_DATE] [datetime] NULL,
[PREV_POST_EEE] [varchar](8) NULL,
[EEE_REASON_ID] [int] NULL,
[BILLING_TYPE_ID] [int] NULL,
[PEER_SERVICE_REQUEST_ID] [int] NULL,
[IS_LOGICALLY_SUSPENDED] [bit] NULL,
[ADDRESS_DERIVATION] [varchar](20) NULL,
[ERROR_CODE] [varchar](1) NULL,
[COMMENT] [varchar](100) NULL,
[SUPPRESSED_DATE] [datetime] NULL,
[FLEET_REQUEST_ID] [uniqueidentifier] NULL,
[STATE_CLOSED_DATE_ONLY] [datetime] NULL,
[FACILITY_ID] [smallint] NULL,
[DUPLICATE_TITLE_SLA_START_DATE] [datetime] NULL,
[IS_SUBSEQENT_RELEASE_REQUIRE] [bit] NULL,
[IS_ACCELERATED_TITLE] [bit] NULL,
[IS_DMV_REJECT] [bit] NULL,
[IS_POST_FEE] [bit] NULL,
[AT_PAYOFF_AMOUNT] [decimal](10, 2) NULL,
[AT_EFFECTIVE_DATE] [datetime] NULL,
CONSTRAINT [SERVICE_REQUEST_PK] PRIMARY KEY CLUSTERED
(
[SERVICE_REQUEST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [CM_Log]
) ON [CM_Log] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD DEFAULT ((0)) FOR [CAN_DISPATCH_BE_COMPLETED]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD DEFAULT ((0)) FOR [DOES_REQUIRED_DUPLICATE_NEED_TO_BE_ORDERED]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD DEFAULT (NULL) FOR [STATUS_CHANGE_REASON]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD DEFAULT (NULL) FOR [OPERATION_CODE_ID]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD CONSTRAINT [DF_SERVICE_REQUEST_REQUEST_STATE_TO_PRINT_PAPER_TITLE] DEFAULT ((0)) FOR [REQUEST_STATE_TO_PRINT_PAPER_TITLE]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD DEFAULT ((0)) FOR [IS_DMV_REJECT]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ACCOUNT_SERVICE_REQUEST_FK1] FOREIGN KEY([TRANSFERRED_ACCOUNT_ID])
REFERENCES [dbo].[ACCOUNT] ([ACCOUNT_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ACCOUNT_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ADDRESS_SERVICE_REQUEST_FK1] FOREIGN KEY([ADDRESS_ID])
REFERENCES [dbo].[ADDRESS] ([ADDRESS_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ADDRESS_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [BILLING_TYPE_SERVICE_REQUEST_FK1] FOREIGN KEY([BILLING_TYPE_ID])
REFERENCES [dbo].[BILLING_TYPE] ([BILLING_TYPE_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [BILLING_TYPE_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [CHECK_REPORT_SERVICE_REQUEST_FK1] FOREIGN KEY([CHECK_REPORT_ID])
REFERENCES [dbo].[CHECK_REPORT] ([CHECK_REPORT_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [CHECK_REPORT_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [EEE_REASON_SERVICE_REQUEST_FK1] FOREIGN KEY([EEE_REASON_ID])
REFERENCES [dbo].[EEE_REASON] ([EEE_REASON_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [EEE_REASON_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ERROR_SERVICE_REQUEST_FK1] FOREIGN KEY([ELT_RESPONSE_ERRORERROR_ID])
REFERENCES [dbo].[ERROR] ([ERROR_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ERROR_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ERROR_SERVICE_REQUEST_FK2] FOREIGN KEY([ACCOUNT_IMPORT_ERRORERROR_ID])
REFERENCES [dbo].[ERROR] ([ERROR_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ERROR_SERVICE_REQUEST_FK2]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [FACILITY_SERVICE_REQUEST_FK1] FOREIGN KEY([FACILITY_ID])
REFERENCES [dbo].[FACILITY] ([Facility_Id])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [FACILITY_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [FDI_DEPARTMENT_SERVICE_REQUEST_FK1] FOREIGN KEY([FDI_DEPARTMENT_ID])
REFERENCES [dbo].[FDI_DEPARTMENT] ([FDI_DEPARTMENT_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [FDI_DEPARTMENT_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [FROM_TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK] FOREIGN KEY([TRANSFER_FROM_TITLING_AGENCY_LIENHOLDER_ID])
REFERENCES [dbo].[TITLING_AGENCY_LIENHOLDER] ([TITLING_AGENCY_LIENHOLDER_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [FROM_TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [LEGAL_ENTITY_SERVICE_REQUEST_FK1] FOREIGN KEY([VIRGINIA_CUSTOMER_SERVICE_CENTER_ID])
REFERENCES [dbo].[LEGAL_ENTITY] ([LEGAL_ENTITY_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [LEGAL_ENTITY_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [OPERATION_CODE_SERVICE_REQUEST_FK1] FOREIGN KEY([OPERATION_CODE_ID])
REFERENCES [dbo].[OPERATION_CODE] ([OPERATION_CODE_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [OPERATION_CODE_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ORGANIZATION_SERVICE_REQUEST_FK2] FOREIGN KEY([CLIENT_ID])
REFERENCES [dbo].[ORGANIZATION] ([ORGANIZATION_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ORGANIZATION_SERVICE_REQUEST_FK2]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ORGANIZATION_SERVICE_REQUEST_FK3] FOREIGN KEY([JURISDICTION_ID])
REFERENCES [dbo].[ORGANIZATION] ([ORGANIZATION_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ORGANIZATION_SERVICE_REQUEST_FK3]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [OWNERSHIP_DOC_SERVICE_REQUEST_FK1] FOREIGN KEY([TRANSFERRED_OWNERSHIP_DOC_ID])
REFERENCES [dbo].[OWNERSHIP_DOC] ([OWNERSHIP_DOC_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [OWNERSHIP_DOC_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [PRINT_REQUEST_SERVICE_REQUEST_FK1] FOREIGN KEY([PRINT_REQUEST_ID])
REFERENCES [dbo].[PRINT_REQUEST] ([PRINT_REQUEST_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [PRINT_REQUEST_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK1] FOREIGN KEY([PARENT_SERVICE_REQUEST_ID])
REFERENCES [dbo].[SERVICE_REQUEST] ([SERVICE_REQUEST_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK2] FOREIGN KEY([PREVIOUS_SERVICE_REQUEST_ID])
REFERENCES [dbo].[SERVICE_REQUEST] ([SERVICE_REQUEST_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK2]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK3] FOREIGN KEY([PEER_SERVICE_REQUEST_ID])
REFERENCES [dbo].[SERVICE_REQUEST] ([SERVICE_REQUEST_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK3]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK1] FOREIGN KEY([TITLING_AGENCY_LIENHOLDER_ID])
REFERENCES [dbo].[TITLING_AGENCY_LIENHOLDER] ([TITLING_AGENCY_LIENHOLDER_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK2] FOREIGN KEY([LIENHOLDER_PERFORMING_LIEN_FILING_ID])
REFERENCES [dbo].[TITLING_AGENCY_LIENHOLDER] ([TITLING_AGENCY_LIENHOLDER_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK2]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK3] FOREIGN KEY([TRANSFER_TO_TITLING_AGENCY_LIENHOLDER_ID])
REFERENCES [dbo].[TITLING_AGENCY_LIENHOLDER] ([TITLING_AGENCY_LIENHOLDER_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK3]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [USR_SERVICE_REQUEST_FK1] FOREIGN KEY([USR_ID])
REFERENCES [dbo].[USR] ([USR_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [USR_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [XML_SERIALIZED_INSTANCE_SERVICE_REQUEST_FK1] FOREIGN KEY([XML_SERIALIZED_INSTANCE_ID])
REFERENCES [dbo].[XML_SERIALIZED_INSTANCE] ([XML_SERIALIZED_INSTANCE_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [XML_SERIALIZED_INSTANCE_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [XML_SERIALIZED_INSTANCE_SERVICE_REQUEST_FK2] FOREIGN KEY([CALIFORNIA_TRANSMITTAL_FORMXML_SERIALIZED_INSTANCE_ID])
REFERENCES [dbo].[XML_SERIALIZED_INSTANCE] ([XML_SERIALIZED_INSTANCE_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [XML_SERIALIZED_INSTANCE_SERVICE_REQUEST_FK2]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUEST_REQUEST_CATEGORY_Chk] CHECK (([REQUEST_CATEGORY] = 'TITLE_MAINTENANCE' or [REQUEST_CATEGORY] = 'NULL'))
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUEST_REQUEST_CATEGORY_Chk]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUESTBUSINESS_PROCESS_STATUS_Chk] CHECK (([BUSINESS_PROCESS_STATUS]='Cancelled' OR [BUSINESS_PROCESS_STATUS]='Closed' OR [BUSINESS_PROCESS_STATUS]='Open' OR [BUSINESS_PROCESS_STATUS]='NotStarted'))
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUESTBUSINESS_PROCESS_STATUS_Chk]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUESTELECTRONIC_TITLES_TO_TRANSFER_PER_DAY_Chk] CHECK (([ELECTRONIC_TITLES_TO_TRANSFER_PER_DAY]<=(5000)))
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUESTELECTRONIC_TITLES_TO_TRANSFER_PER_DAY_Chk]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUESTNEW_YORK_LIEN_FILING_TRANSACTION_TYPE_Chk] CHECK (([NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'Notice' or [NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'Cancellation' or [NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'Release' or [NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'CollateralLoan' or [NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'Amendment' or [NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'InitialFiling'))
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUESTNEW_YORK_LIEN_FILING_TRANSACTION_TYPE_Chk]
GO
October 19, 2015 at 12:33 pm
Here is the update query initially post in a slightly more readable format:
UPDATE SERVICE_REQUEST
SET SERVICE_REQUEST_STATUS = 'PrintedOnPullReport'
,LAST_PRINTED_ON_REPORT_DATE = '2015-10-15 00:00:00'
,LAST_MOD_DATE_TIME = '2015-10-15 06:10:03.223'
,LAST_MODIFIER = 60417
,PRINT_REQUEST_ID = 69432
FROM SERVICE_REQUEST AS CollateralGroupRequest02
CROSS JOIN TITLING_AGENCY_REQUEST_TRANSACTION_TYPE AS ServiceRequestTransactionType13
INNER JOIN (
SERVICED_COLLATERAL_GROUP_ITEM AS ServicedCollateralGroupItem24 INNER JOIN (
SERVICED_COLLATERAL_GROUP_ITEM AS ServicedOwnershipDoc25 INNER JOIN (
OWNERSHIP_DOC AS OwnershipDoc26 LEFT JOIN (
SCANNED_DOCUMENT_DATA AS ScannedDocumentData28 INNER JOIN BATCH AS Batch29 ON ScannedDocumentData28.BATCH_ID = Batch29.BATCH_ID
) ON OwnershipDoc26.SCANNED_DOCUMENT_DATA_ID = ScannedDocumentData28.SCANNED_DOCUMENT_DATA_ID
) ON ServicedOwnershipDoc25.OWNERSHIP_DOC_ID = OwnershipDoc26.OWNERSHIP_DOC_ID
) ON ServicedCollateralGroupItem24.SERVICED_COLLATERAL_GROUP_ITEM_ID = ServicedOwnershipDoc25.SERVICED_COLLATERAL_GROUP_ITEM_ID
) ON CollateralGroupRequest02.SERVICE_REQUEST_ID = ServicedCollateralGroupItem24.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID
LEFT JOIN PRINT_REQUEST AS PrintRequest27 ON CollateralGroupRequest02.PRINT_REQUEST_ID = PrintRequest27.PRINT_REQUEST_ID
WHERE (
(
CollateralGroupRequest02.REQUEST_TRANSACTION_TYPE = ServiceRequestTransactionType13.REQUEST_TRANSACTION_TYPE
AND CollateralGroupRequest02.REQUEST_TRANSACTION_TYPE IN ('ReleaseToRegisteredOwner')
AND CollateralGroupRequest02.IS_EXPEDITED_FROM_VAULT = 0
AND CollateralGroupRequest02.MAIL_CARRIER IS NULL
AND CollateralGroupRequest02.RECORDED_REQUEST_DATE <= '2015-10-15 06:10:03.240'
AND CollateralGroupRequest02.BUSINESS_PROCESS_STATUS = 'Open'
AND CollateralGroupRequest02.IS_ON_HOLD_INDEFINITELY = 0
AND CollateralGroupRequest02.CLIENT_ID IN (
11330
,11220
,12001
,11234
,12336
,10712
,12342
,10
,11303
,12332
,12572
)
AND CollateralGroupRequest02.REQUEST_TRANSACTION_TYPE NOT IN (
'ContractRequest'
,'ContractRequestRc'
,'ContractRequestLc'
)
AND OwnershipDoc26.BUSINESS_OBJECT_STATUS = 'OPEN'
AND ServicedOwnershipDoc25.IS_PRIMARY_COLLATERAL_GROUP_ITEM = 1
AND (
CollateralGroupRequest02.SERVICE_REQUEST_STATUS = 'EligibleForPullReport'
AND CollateralGroupRequest02.FACILITY_ID = 1
OR (
PrintRequest27.BUSINESS_PROCESS_STATUS = 'Open'
AND PrintRequest27.LAST_MOD_DATE_TIME < '2015-10-15 06:05:03.223'
AND CollateralGroupRequest02.SERVICE_REQUEST_STATUS = 'PrintedOnPullReport'
)
)
AND (
CollateralGroupRequest02.IS_ACCELERATED_TITLE = 0
OR (CollateralGroupRequest02.IS_ACCELERATED_TITLE IS NULL)
)
AND (
CollateralGroupRequest02.IS_ACCELERATED_TITLE = 0
OR (CollateralGroupRequest02.IS_ACCELERATED_TITLE IS NULL)
)
AND (
(Batch29.CREATED_DATE_TIME <= '2015-10-15 00:00:00')
OR (ScannedDocumentData28.SCANNED_DOCUMENT_DATA_ID IS NULL)
)
)
)
Looking at this, I started having a problem following it. This is something I'd have to look at with more time than I have at the moment.
October 19, 2015 at 12:55 pm
Lynn Pettis (10/19/2015)
Here is the update query initially post in a slightly more readable format:...
Looking at this, I started having a problem following it. This is something I'd have to look at with more time than I have at the moment.
That's probably what DTA was trying to say too. 😛
Indianrock, if you can't find resolution for the DTA error, then paste SQL into SSMS Query window, highlight, and then do 'Display Estimated Execution Plan' option from right-click menu. In addition to showing plan and giving estimated cost for each operation, it sometimes provides a suggested missing index.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 19, 2015 at 12:58 pm
Yes, its not pretty. With 30 indexes on the table, I was hoping to find some to remove or change. Very difficult to do when such updates aren't normally run in QA. It may have to go back to development to see if they hand crafted this thing or if the ORM is generating it.
October 19, 2015 at 1:42 pm
Indianrock (10/19/2015)
Yes, its not pretty. With 30 indexes on the table, I was hoping to find some to remove or change. Very difficult to do when such updates aren't normally run in QA. It may have to go back to development to see if they hand crafted this thing or if the ORM is generating it.
So the table already has (30) indexes on it, and you're getting deadlocking errors on update? All these indexes require locking and blocking and sometimes cause deadlocking, because each row update requires a cascade of all related indexes to be updated as well. In this scenario, dropping (not adding) indexes may be the solution.
You can query sys.dm_db_index_usage_stats to get last read date/time, total number reads, etc. for each index. This can be used as a basis for determining which indexes are not being used. The DTA actually uses this and other system views behind the scenes to do it's analysis.
SELECT getdate() AS RunTime
, DB_NAME(i.database_id) as DatabaseName
, OBJECT_NAME(i.object_id, i.database_id) as ObjectName
, *
FROM sys.dm_db_index_usage_stats AS i
WHERE object_id > 100;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 20, 2015 at 7:44 am
I've used both Brent Ozar's Sp_BlitzIndex and Jason Strate's sp_indexanalysis. There seems to be precious little as far as any indexes not being used at all, but I need to dig into that more.
On the database tuning advisor, when I boiled the query down to a much simpler select, it still bombs. The table involved shows in Spotlight as 45GB in size including all indexes but also shows the total of all nonclustered indexes as 89GB ( ? ) In any event, I suspect DTA is running out of memory and crashing as the QA box where I've run it most of the time has 256GB of memory but QA instances are already using 240 of that. When I set allowed memory in DTA to 15GB it stops right away and reports something to the effect that that limit isn't adequate to proceed.
If DTA has to "swallow" all of the indexes it is evaluating, it won't be much use except in smallish databases.
select facility_id FROM SERVICE_REQUEST
WHERE facility_id = 1 and BUSINESS_PROCESS_STATUS = 'Open'
AND SERVICE_REQUEST_STATUS = 'PrintedOnPullReport' AND IS_ACCELERATED_TITLE = 0
October 20, 2015 at 7:49 am
To be honest, DTA's not that useful full stop. It's fine as a place to start, but it has a bad habit of over-recommending indexes and statistics, and you can't use it to identify indexes to be removed.
Can you post the deadlock graph please, as well as the CREATE INDEX statements for all the indexes on the problematic table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2015 at 8:04 am
Deadlock this morning. Table/index definitions are below the deadlock xml
index_ServiceRequest_ClientId
index_ServiceRequest_PullReport_FacilityId
Deadlock xml -- first process is the victim waitresource="PAGE: 5:15:8013416 points to the two indexes listed above
<deadlock-list>
<deadlock victim="process5e696b2cf8">
<process-list>
<process XDES="0x6e95e27be0" clientapp=".Net SqlClient Data Provider" clientoption1="671090784" clientoption2="128056" currentdb="5" ecid="3" hostname="DEIMOS" hostpid="4940" id="process5e696b2cf8" isolationlevel="read committed (2)" kpid="23436" lastattention="1900-01-01T00:00:00.827" lastbatchcompleted="2015-10-19T06:08:16.827" lastbatchstarted="2015-10-19T06:08:16.840" lasttranstarted="2015-10-19T06:08:16.823" lockMode="U" lockTimeout="4294967295" logused="0" ownerId="18991502955" priority="0" sbid="0" schedulerid="53" spid="147" status="suspended" taskpriority="0" trancount="0" transactionname="user_transaction" waitresource="PAGE: 5:15:8013416 " waittime="5147" xactid="18991502955">
<executionStack>
<frame line="1" procname="adhoc" sqlhandle="0x0200000052d8882790c6b6edf9cb0634286809e246cccceb0000000000000000000000000000000000000000" stmtstart="3112">UPDATE SERVICE_REQUEST SET SERVICE_REQUEST_STATUS = @DerivedTable01_SERVICE_REQUEST_STATUS5529, LAST_PRINTED_ON_REPORT_DATE = @DerivedTable01_LAST_PRINTED_ON_REPORT_DATE5730, LAST_MOD_DATE_TIME = @DerivedTable01_LAST_MOD_DATE_TIME5931, LAST_MODIFIER = @DerivedTable01_LAST_MODIFIER6132, PRINT_REQUEST_ID = @DerivedTable01_PRINT_REQUEST_ID6333 FROM SERVICE_REQUEST AS CollateralGroupRequest02 CROSS JOIN TITLING_AGENCY_REQUEST_TRANSACTION_TYPE AS ServiceRequestTransactionType13 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedCollateralGroupItem24 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedOwnershipDoc25 INNER JOIN (OWNERSHIP_DOC AS OwnershipDoc26 LEFT OUTER JOIN (SCANNED_DOCUMENT_DATA AS ScannedDocumentData28 INNER JOIN BATCH AS Batch29 ON ScannedDocumentData28.BATCH_ID=Batch29.BATCH_ID) ON OwnershipDoc26.SCANNED_DOCUMENT_DATA_ID=ScannedDocumentData28.SCANNED_DOCUMENT_DATA_ID) ON ServicedOwnershipDoc25.OWNERSHIP_DOC_ID=OwnershipDoc26.OWNERSHIP_DOC_ID) ON ServicedCollateralGroupItem24.SERVICED</frame>
<frame line="1" procname="unknown" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">unknown</frame>
</executionStack>
<inputbuf>(@DerivedTable01_REQUEST_TRANSACTION_TYPE40 varchar(8000),@DerivedTable01_IS_EXPEDITED_FROM_VAULT61 bit,@DerivedTable01_RECORDED_REQUEST_DATE92 datetime,@DerivedTable01_BUSINESS_PROCESS_STATUS113 varchar(8000),@DerivedTable01_IS_ON_HOLD_INDEFINITELY134 bit,@DerivedTable01_CLIENT_ID155 int,@DerivedTable01_CLIENT_ID166 int,@DerivedTable01_CLIENT_ID177 int,@DerivedTable01_CLIENT_ID188 int,@DerivedTable01_CLIENT_ID199 int,@DerivedTable01_CLIENT_ID2010 int,@DerivedTable01_CLIENT_ID2111 int,@DerivedTable01_CLIENT_ID2212 int,@DerivedTable01_CLIENT_ID2313 int,@DerivedTable01_CLIENT_ID2414 int,@DerivedTable01_CLIENT_ID2515 int,@DerivedTable01_REQUEST_TRANSACTION_TYPE2716 varchar(8000),@DerivedTable01_REQUEST_TRANSACTION_TYPE2817 varchar(8000),@DerivedTable01_REQUEST_TRANSACTION_TYPE2918 varchar(8000),@DerivedTable01_BUSINESS_OBJECT_STATUS3219 varchar(8000),@DerivedTable01_IS_PRIMARY_COLLATERAL_GROUP_ITEM3420 bit,@DerivedTable01_SERVICE_REQUEST_STATUS3621 varchar(8000),@DerivedTable01_FACILITY_ID3822 smallint,@Derived</inputbuf>
</process>
<process XDES="0x4dcd7d96a8" clientapp=".Net SqlClient Data Provider" clientoption1="671090784" clientoption2="128056" currentdb="5" ecid="0" hostname="OPHELIA" hostpid="5388" id="process2b024b3c38" isolationlevel="read committed (2)" kpid="1580" lastattention="2015-10-19T05:46:39.487" lastbatchcompleted="2015-10-19T06:08:23.027" lastbatchstarted="2015-10-19T06:08:23.057" lasttranstarted="2015-10-19T06:08:06.637" lockMode="U" lockTimeout="4294967295" loginname="CM\DefaultAppPoolID" logused="4064532" ownerId="18991468394" priority="0" sbid="0" schedulerid="44" spid="139" status="suspended" taskpriority="0" trancount="2" transactionname="user_transaction" waitresource="PAGE: 5:15:7754211 " waittime="4334" xactid="18991468394">
<executionStack>
<frame line="1" procname="adhoc" sqlhandle="0x02000000e5e0e11468bba237d3e15ce6ed27246f8d62f1350000000000000000000000000000000000000000" stmtstart="4258">UPDATE SERVICE_REQUEST SET SERVICE_REQUEST_STATUS = @DerivedTable01_SERVICE_REQUEST_STATUS3445, LAST_PRINTED_ON_REPORT_DATE = @DerivedTable01_LAST_PRINTED_ON_REPORT_DATE3646, LAST_MOD_DATE_TIME = @DerivedTable01_LAST_MOD_DATE_TIME3847, LAST_MODIFIER = @DerivedTable01_LAST_MODIFIER4048, PRINT_REQUEST_ID = @DerivedTable01_PRINT_REQUEST_ID4249 FROM SERVICE_REQUEST AS ContractRequest02 LEFT OUTER JOIN PRINT_REQUEST AS PrintRequest13 ON ContractRequest02.PRINT_REQUEST_ID=PrintRequest13.PRINT_REQUEST_ID WHERE ((ContractRequest02.CLIENT_ID IN ( @DerivedTable01_CLIENT_ID20, @DerivedTable01_CLIENT_ID31, @DerivedTable01_CLIENT_ID42, @DerivedTable01_CLIENT_ID53, @DerivedTable01_CLIENT_ID64, @DerivedTable01_CLIENT_ID75, @DerivedTable01_CLIENT_ID86, @DerivedTable01_CLIENT_ID97, @DerivedTable01_CLIENT_ID108, @DerivedTable01_CLIENT_ID119, @DerivedTable01_CLIENT_ID1210) AND ContractRequest02.RECORDED_REQUEST_DATE <= @DerivedTable01_RECORDED_REQUEST_DATE1411 AND ContractRequest02.BUSINESS_PROCESS_STATUS = @de</frame>
<frame line="1" procname="unknown" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">unknown</frame>
</executionStack>
<inputbuf>(@DerivedTable01_CLIENT_ID20 int,@DerivedTable01_CLIENT_ID31 int,@DerivedTable01_CLIENT_ID42 int,@DerivedTable01_CLIENT_ID53 int,@DerivedTable01_CLIENT_ID64 int,@DerivedTable01_CLIENT_ID75 int,@DerivedTable01_CLIENT_ID86 int,@DerivedTable01_CLIENT_ID97 int,@DerivedTable01_CLIENT_ID108 int,@DerivedTable01_CLIENT_ID119 int,@DerivedTable01_CLIENT_ID1210 int,@DerivedTable01_RECORDED_REQUEST_DATE1411 datetime,@DerivedTable01_BUSINESS_PROCESS_STATUS1612 varchar(8000),@DerivedTable01_IS_ON_HOLD_INDEFINITELY1813 bit,@DerivedTable01_CLIENT_ID214 int,@DerivedTable01_CLIENT_ID315 int,@DerivedTable01_CLIENT_ID416 int,@DerivedTable01_CLIENT_ID517 int,@DerivedTable01_CLIENT_ID618 int,@DerivedTable01_CLIENT_ID719 int,@DerivedTable01_CLIENT_ID820 int,@DerivedTable01_CLIENT_ID921 int,@DerivedTable01_CLIENT_ID1022 int,@DerivedTable01_CLIENT_ID1123 int,@DerivedTable01_CLIENT_ID1224 int,@DerivedTable01_IS_EXPEDITED_FROM_VAULT1525 bit,@DerivedTable01_REQUEST_TRANSACTION_TYPE1826 varchar(8000),@DerivedTable01_IS_ACCELERATED_TITLE</inputbuf>
</process>
</process-list>
<resource-list>
<pagelock associatedObjectId="72057986607349760" dbid="5" fileid="15" id="lock4a43598800" mode="UIX" objectname="COLLATERALMANAGER.dbo.SERVICE_REQUEST" pageid="8013416" subresource="FULL">
<owner-list>
<owner id="process2b024b3c38" mode="UIX" />
</owner-list>
<waiter-list>
<waiter id="process5e696b2cf8" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock associatedObjectId="72057986607349760" dbid="5" fileid="15" id="lock59f4cbaa80" mode="U" objectname="COLLATERALMANAGER.dbo.SERVICE_REQUEST" pageid="7754211" subresource="FULL">
<owner-list>
<owner id="process5e696b2cf8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process2b024b3c38" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>
Table and index definitions
/****** Object: Table [dbo].[SERVICE_REQUEST] Script Date: 10/20/2015 6:57:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SERVICE_REQUEST](
[SERVICE_REQUEST_ID] [int] IDENTITY(1,1) NOT NULL,
[BUSINESS_PROCESS_STATUS] [varchar](10) NOT NULL,
[VIRGINIA_CUSTOMER_SERVICE_CENTER_ID] [int] NULL,
[TITLING_AGENCY_LIENHOLDER_ID] [int] NULL,
[CONCRETE_TYPE] [varchar](150) NOT NULL,
[USR_ID] [int] NOT NULL,
[LAST_MOD_DATE_TIME] [datetime] NOT NULL,
[LAST_MODIFIER] [int] NOT NULL,
[TRANS_SEQ_NUM] [smallint] NOT NULL,
[RECORDED_REQUEST_DATE] [smalldatetime] NOT NULL,
[REQUEST_MECHANISM_TYPE] [varchar](8) NULL,
[CREATED_DATE_TIME] [datetime] NOT NULL,
[REQUEST_TRANSACTION_TYPE] [varchar](60) NULL,
[ADDRESS_ID] [int] NULL,
[IS_EXPEDITED] [bit] NULL,
[RESEARCH_QUESTION] [varchar](173) NULL,
[REQUEST_CATEGORY] [varchar](30) NOT NULL,
[XML_SERIALIZED_INSTANCE_ID] [int] NULL,
[LAST_EXPORT_TO_STATE_DATE] [datetime] NULL,
[LAST_STATE_RESPONSE_DATE] [datetime] NULL,
[STATE_CLOSED_DATE] [datetime] NULL,
[LAST_STATE_RESPONSE_CODE] [varchar](120) NULL,
[REJECTION_NOTE] [varchar](200) NULL,
[IS_REASSIGNED] [bit] NULL,
[PORTFOLIO_TRANSFER_NAME] [varchar](50) NULL,
[TRANSFER_TO_TITLING_AGENCY_LIENHOLDER_ID] [int] NULL,
[LIENHOLDER_PERFORMING_LIEN_FILING_ID] [int] NULL,
[VEHICLE_TYPE] [varchar](30) NULL,
[DOCUMENT_NUMBER] [varchar](30) NULL,
[NUMBER_OF_MEMO_TITLES] [smallint] NULL,
[OHIO_CONTROL_NUMBER] [varchar](9) NULL,
[DISPOSITION_CODE] [char](1) NULL,
[NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] [varchar](30) NULL,
[LIEN_RELEASE_SEQUENCE_NUMBER] [smallint] NULL,
[MAKE] [varchar](25) NULL,
[YEAR] [char](4) NULL,
[MANUFACTURER_ID] [varchar](30) NULL,
[ELECTRONIC_TITLES_TO_TRANSFER_PER_DAY] [int] NULL,
[FINANCED_DATE] [datetime] NULL,
[TITLE_NUMBER] [varchar](30) NULL,
[OHIO_CONTROL_NUMBER1] [varchar](9) NULL,
[NUMBER_OF_OPEN_LIENS] [smallint] NULL,
[PARENT_SERVICE_REQUEST_ID] [int] NULL,
[PREVIOUS_SERVICE_REQUEST_ID] [int] NULL,
[SERVICE_REQUEST_STATUS] [varchar](40) NULL,
[SPECIAL_MAIL_HANDLING_INSTRUCTIONS] [varchar](200) NULL,
[MAIL_CARRIER] [varchar](40) NULL,
[CLIENT_ID] [int] NULL,
[IS_ON_HOLD_INDEFINITELY] [bit] NULL,
[PRINT_REQUEST_ID] [int] NULL,
[ACTION_DATE] [datetime] NULL,
[DUPLICATE_TITLE_REQUEST_REASON] [varchar](30) NULL,
[FDI_DEPARTMENT_ID] [int] NULL,
[JURISDICTION_ID] [int] NULL,
[ELT_RESPONSE_ERRORERROR_ID] [int] NULL,
[TRANSFERRED_OWNERSHIP_DOC_ID] [int] NULL,
[ACCOUNT_IMPORT_ERRORERROR_ID] [int] NULL,
[TRANSFERRED_ACCOUNT_ID] [int] NULL,
[COLLATERAL_GROUP_STATUS] [varchar](50) NULL,
[XML_DOCUMENT_DATA] [text] NULL,
[IS_EXPEDITED_FROM_VAULT] [bit] NULL,
[IS_CONFIRM_RECEIPT] [bit] NULL,
[CHECK_REPORT_ID] [int] NULL,
[MANUFACTURER_ID1] [varchar](30) NULL,
[BUYERS_NAME] [varchar](60) NULL,
[DEALER_REPORT_NUMBER] [varchar](10) NULL,
[DATE_OF_SALE] [smalldatetime] NULL,
[RECEIPT_NUMBER] [varchar](30) NULL,
[DMV_FEES_COLLECTED] [decimal](9, 2) NULL,
[IS_USED] [bit] NULL,
[CALIFORNIA_TRANSMITTAL_FORMXML_SERIALIZED_INSTANCE_ID] [int] NULL,
[AMOUNT] [decimal](9, 2) NULL,
[AMOUNT_BILLED] [decimal](9, 2) NULL,
[CHECK_NUMBER] [varchar](10) NULL,
[PAYEE_NAME] [varchar](50) NULL,
[IS_RELEASE_DUE_TO_INSURANCE_LOSS] [bit] NULL,
[IS_LETTERS_PRINTED] [bit] NULL,
[LAST_SERVICE_REQUEST_STATUS_CHANGE_DATE_TIME] [datetime] NULL,
[IS_NOTARY_REQUIRED] [bit] NULL,
[NOTARY_STATE_ABBR] [varchar](10) NULL,
[AMOUNT_OF_CASH_OR_CHECK] [decimal](9, 2) NULL,
[IS_BILLABLE] [bit] NULL,
[SALES_TAX] [decimal](8, 2) NULL,
[END_OF_LEASE_PURCHASE_PRICE] [decimal](16, 2) NULL,
[MISCELLANEOUS_FEES] [decimal](8, 2) NULL,
[ODOMETER_READING] [int] NULL,
[LIEN_DATE] [datetime] NULL,
[CAN_DISPATCH_BE_COMPLETED] [bit] NOT NULL,
[DOES_REQUIRED_DUPLICATE_NEED_TO_BE_ORDERED] [bit] NOT NULL,
[STATUS_CHANGE_REASON] [varchar](25) NULL,
[OPERATION_CODE_ID] [int] NULL,
[PREVIOUS_SERVICE_REQUEST_STATUS] [varchar](40) NULL,
[CARRIER_TRACKING_NUMBER] [varchar](50) NULL,
[LUXURY_TAX] [decimal](8, 2) NULL,
[PROPERTY_TAX] [decimal](8, 2) NULL,
[ODOMETER_READING_DATE] [datetime] NULL,
[OWNERSHIP_DOC_TRANSFER_METHOD] [varchar](20) NULL,
[TRANSFER_FROM_TITLING_AGENCY_LIENHOLDER_ID] [int] NULL,
[PORTFOLIO_TRANSFER_BRANCH] [varchar](30) NULL,
[TTA_CLIENT_REQUEST_RELEASE_CODE] [varchar](10) NULL,
[LAST_PRINTED_ON_REPORT_DATE] [datetime] NULL,
[REQUEST_STATE_TO_PRINT_PAPER_TITLE] [bit] NOT NULL,
[CREATED_DATE] [datetime] NULL,
[CURRENT_ACTIVITY_SEQUENCE] [smallint] NULL,
[IS_LIEN_FILING_DUE_TO_DIRECT_LENDING] [bit] NULL,
[MAILED_TO_DMV_DATE] [datetime] NULL,
[OWNER_EIN_FID] [varchar](9) NULL,
[RELATED_BUSINESS_PROCESS] [varchar](30) NULL,
[EEE_CAUSE] [varchar](25) NULL,
[EEE_DATE] [datetime] NULL,
[POST_EEE] [varchar](8) NULL,
[POST_EEE_DATE] [datetime] NULL,
[PREV_POST_EEE] [varchar](8) NULL,
[EEE_REASON_ID] [int] NULL,
[BILLING_TYPE_ID] [int] NULL,
[PEER_SERVICE_REQUEST_ID] [int] NULL,
[IS_LOGICALLY_SUSPENDED] [bit] NULL,
[ADDRESS_DERIVATION] [varchar](20) NULL,
[ERROR_CODE] [varchar](1) NULL,
[COMMENT] [varchar](100) NULL,
[SUPPRESSED_DATE] [datetime] NULL,
[FLEET_REQUEST_ID] [uniqueidentifier] NULL,
[STATE_CLOSED_DATE_ONLY] [datetime] NULL,
[FACILITY_ID] [smallint] NULL,
[DUPLICATE_TITLE_SLA_START_DATE] [datetime] NULL,
[IS_SUBSEQENT_RELEASE_REQUIRE] [bit] NULL,
[IS_ACCELERATED_TITLE] [bit] NULL,
[IS_DMV_REJECT] [bit] NULL,
[IS_POST_FEE] [bit] NULL,
[AT_PAYOFF_AMOUNT] [decimal](10, 2) NULL,
[AT_EFFECTIVE_DATE] [datetime] NULL,
CONSTRAINT [SERVICE_REQUEST_PK] PRIMARY KEY CLUSTERED
(
[SERVICE_REQUEST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [CM_Log]
) ON [CM_Log] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [index_SERVICE_REQUEST_XML_SERIALIZED_INSTANCE_ID] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_SERVICE_REQUEST_XML_SERIALIZED_INSTANCE_ID] ON [dbo].[SERVICE_REQUEST]
(
[XML_SERIALIZED_INSTANCE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Index]
GO
/****** Object: Index [index_ServiceRequest_AccountImportErrorErrorId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_AccountImportErrorErrorId] ON [dbo].[SERVICE_REQUEST]
(
[ACCOUNT_IMPORT_ERRORERROR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_ActionDate] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_ActionDate] ON [dbo].[SERVICE_REQUEST]
(
[ACTION_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_AddressId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_AddressId] ON [dbo].[SERVICE_REQUEST]
(
[ADDRESS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [index_ServiceRequest_BusinessProcessStatus] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_BusinessProcessStatus] ON [dbo].[SERVICE_REQUEST]
(
[BUSINESS_PROCESS_STATUS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_CaliforniaTransmittalFormXmlSerializedInstanceId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_CaliforniaTransmittalFormXmlSerializedInstanceId] ON [dbo].[SERVICE_REQUEST]
(
[CALIFORNIA_TRANSMITTAL_FORMXML_SERIALIZED_INSTANCE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [index_ServiceRequest_CarrierTrackingNumber] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_CarrierTrackingNumber] ON [dbo].[SERVICE_REQUEST]
(
[CARRIER_TRACKING_NUMBER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_ClientId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_ClientId] ON [dbo].[SERVICE_REQUEST]
(
[CLIENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [index_ServiceRequest_ConcreteType] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_ConcreteType] ON [dbo].[SERVICE_REQUEST]
(
[CONCRETE_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_IsExpedited] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_IsExpedited] ON [dbo].[SERVICE_REQUEST]
(
[IS_EXPEDITED] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Index]
GO
/****** Object: Index [index_ServiceRequest_LastExportToStateDate] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_LastExportToStateDate] ON [dbo].[SERVICE_REQUEST]
(
[LAST_EXPORT_TO_STATE_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_LastStateResponseDate] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_LastStateResponseDate] ON [dbo].[SERVICE_REQUEST]
(
[LAST_STATE_RESPONSE_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_LienholderPerformingLienFilingId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_LienholderPerformingLienFilingId] ON [dbo].[SERVICE_REQUEST]
(
[LIENHOLDER_PERFORMING_LIEN_FILING_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_ParentServiceRequestId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_ParentServiceRequestId] ON [dbo].[SERVICE_REQUEST]
(
[PARENT_SERVICE_REQUEST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_PeerServiceRequestId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_PeerServiceRequestId] ON [dbo].[SERVICE_REQUEST]
(
[PEER_SERVICE_REQUEST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_PreviousServiceRequestId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_PreviousServiceRequestId] ON [dbo].[SERVICE_REQUEST]
(
[PREVIOUS_SERVICE_REQUEST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_PrintRequestId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_PrintRequestId] ON [dbo].[SERVICE_REQUEST]
(
[PRINT_REQUEST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [index_ServiceRequest_PullReport_FacilityId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_PullReport_FacilityId] ON [dbo].[SERVICE_REQUEST]
(
[BUSINESS_PROCESS_STATUS] ASC,
[CONCRETE_TYPE] ASC,
[CLIENT_ID] ASC,
[IS_ON_HOLD_INDEFINITELY] ASC,
[RECORDED_REQUEST_DATE] ASC,
[SERVICE_REQUEST_STATUS] ASC,
[FACILITY_ID] ASC
)
INCLUDE ( [SERVICE_REQUEST_ID],
[PRINT_REQUEST_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log]
GO
/****** Object: Index [index_ServiceRequest_RecordedRequestDate] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_RecordedRequestDate] ON [dbo].[SERVICE_REQUEST]
(
[RECORDED_REQUEST_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [index_ServiceRequest_RequestMechanismType] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_RequestMechanismType] ON [dbo].[SERVICE_REQUEST]
(
[REQUEST_MECHANISM_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [index_ServiceRequest_RequestTransactionType] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_RequestTransactionType] ON [dbo].[SERVICE_REQUEST]
(
[REQUEST_TRANSACTION_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [index_ServiceRequest_ServiceRequestID] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_ServiceRequestID] ON [dbo].[SERVICE_REQUEST]
(
[LIENHOLDER_PERFORMING_LIEN_FILING_ID] ASC,
[BUSINESS_PROCESS_STATUS] ASC,
[CONCRETE_TYPE] ASC,
[LAST_EXPORT_TO_STATE_DATE] ASC,
[LAST_STATE_RESPONSE_DATE] ASC,
[SERVICE_REQUEST_ID] ASC
)
INCLUDE ( [REQUEST_CATEGORY],
[DOCUMENT_NUMBER],
[NEW_YORK_LIEN_FILING_TRANSACTION_TYPE],
[MAKE],
[YEAR],
[MANUFACTURER_ID],
[FINANCED_DATE]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Index]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [index_ServiceRequest_ServiceRequestStatus] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_ServiceRequestStatus] ON [dbo].[SERVICE_REQUEST]
(
[SERVICE_REQUEST_STATUS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [index_ServiceRequest_ServiceRequestStatus_BusinessProcessStatus] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_ServiceRequestStatus_BusinessProcessStatus] ON [dbo].[SERVICE_REQUEST]
(
[SERVICE_REQUEST_STATUS] ASC,
[BUSINESS_PROCESS_STATUS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_StateClosedDate] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_StateClosedDate] ON [dbo].[SERVICE_REQUEST]
(
[STATE_CLOSED_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_TitlingAgencyLienholderId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_TitlingAgencyLienholderId] ON [dbo].[SERVICE_REQUEST]
(
[TITLING_AGENCY_LIENHOLDER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_TransferFromTitlingAgencyLienholderId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_TransferFromTitlingAgencyLienholderId] ON [dbo].[SERVICE_REQUEST]
(
[TRANSFER_FROM_TITLING_AGENCY_LIENHOLDER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_TransferToTitlingAgencyLienholderId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_TransferToTitlingAgencyLienholderId] ON [dbo].[SERVICE_REQUEST]
(
[TRANSFER_TO_TITLING_AGENCY_LIENHOLDER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
/****** Object: Index [index_ServiceRequest_VirginiaCustomerServiceCenterId] Script Date: 10/20/2015 6:57:00 AM ******/
CREATE NONCLUSTERED INDEX [index_ServiceRequest_VirginiaCustomerServiceCenterId] ON [dbo].[SERVICE_REQUEST]
(
[VIRGINIA_CUSTOMER_SERVICE_CENTER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD DEFAULT ((0)) FOR [CAN_DISPATCH_BE_COMPLETED]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD DEFAULT ((0)) FOR [DOES_REQUIRED_DUPLICATE_NEED_TO_BE_ORDERED]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD DEFAULT (NULL) FOR [STATUS_CHANGE_REASON]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD DEFAULT (NULL) FOR [OPERATION_CODE_ID]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD CONSTRAINT [DF_SERVICE_REQUEST_REQUEST_STATE_TO_PRINT_PAPER_TITLE] DEFAULT ((0)) FOR [REQUEST_STATE_TO_PRINT_PAPER_TITLE]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] ADD DEFAULT ((0)) FOR [IS_DMV_REJECT]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ACCOUNT_SERVICE_REQUEST_FK1] FOREIGN KEY([TRANSFERRED_ACCOUNT_ID])
REFERENCES [dbo].[ACCOUNT] ([ACCOUNT_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ACCOUNT_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ADDRESS_SERVICE_REQUEST_FK1] FOREIGN KEY([ADDRESS_ID])
REFERENCES [dbo].[ADDRESS] ([ADDRESS_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ADDRESS_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [BILLING_TYPE_SERVICE_REQUEST_FK1] FOREIGN KEY([BILLING_TYPE_ID])
REFERENCES [dbo].[BILLING_TYPE] ([BILLING_TYPE_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [BILLING_TYPE_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [CHECK_REPORT_SERVICE_REQUEST_FK1] FOREIGN KEY([CHECK_REPORT_ID])
REFERENCES [dbo].[CHECK_REPORT] ([CHECK_REPORT_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [CHECK_REPORT_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [EEE_REASON_SERVICE_REQUEST_FK1] FOREIGN KEY([EEE_REASON_ID])
REFERENCES [dbo].[EEE_REASON] ([EEE_REASON_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [EEE_REASON_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ERROR_SERVICE_REQUEST_FK1] FOREIGN KEY([ELT_RESPONSE_ERRORERROR_ID])
REFERENCES [dbo].[ERROR] ([ERROR_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ERROR_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ERROR_SERVICE_REQUEST_FK2] FOREIGN KEY([ACCOUNT_IMPORT_ERRORERROR_ID])
REFERENCES [dbo].[ERROR] ([ERROR_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ERROR_SERVICE_REQUEST_FK2]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [FACILITY_SERVICE_REQUEST_FK1] FOREIGN KEY([FACILITY_ID])
REFERENCES [dbo].[FACILITY] ([Facility_Id])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [FACILITY_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [FDI_DEPARTMENT_SERVICE_REQUEST_FK1] FOREIGN KEY([FDI_DEPARTMENT_ID])
REFERENCES [dbo].[FDI_DEPARTMENT] ([FDI_DEPARTMENT_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [FDI_DEPARTMENT_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [FROM_TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK] FOREIGN KEY([TRANSFER_FROM_TITLING_AGENCY_LIENHOLDER_ID])
REFERENCES [dbo].[TITLING_AGENCY_LIENHOLDER] ([TITLING_AGENCY_LIENHOLDER_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [FROM_TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [LEGAL_ENTITY_SERVICE_REQUEST_FK1] FOREIGN KEY([VIRGINIA_CUSTOMER_SERVICE_CENTER_ID])
REFERENCES [dbo].[LEGAL_ENTITY] ([LEGAL_ENTITY_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [LEGAL_ENTITY_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [OPERATION_CODE_SERVICE_REQUEST_FK1] FOREIGN KEY([OPERATION_CODE_ID])
REFERENCES [dbo].[OPERATION_CODE] ([OPERATION_CODE_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [OPERATION_CODE_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ORGANIZATION_SERVICE_REQUEST_FK2] FOREIGN KEY([CLIENT_ID])
REFERENCES [dbo].[ORGANIZATION] ([ORGANIZATION_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ORGANIZATION_SERVICE_REQUEST_FK2]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [ORGANIZATION_SERVICE_REQUEST_FK3] FOREIGN KEY([JURISDICTION_ID])
REFERENCES [dbo].[ORGANIZATION] ([ORGANIZATION_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [ORGANIZATION_SERVICE_REQUEST_FK3]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [OWNERSHIP_DOC_SERVICE_REQUEST_FK1] FOREIGN KEY([TRANSFERRED_OWNERSHIP_DOC_ID])
REFERENCES [dbo].[OWNERSHIP_DOC] ([OWNERSHIP_DOC_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [OWNERSHIP_DOC_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [PRINT_REQUEST_SERVICE_REQUEST_FK1] FOREIGN KEY([PRINT_REQUEST_ID])
REFERENCES [dbo].[PRINT_REQUEST] ([PRINT_REQUEST_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [PRINT_REQUEST_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK1] FOREIGN KEY([PARENT_SERVICE_REQUEST_ID])
REFERENCES [dbo].[SERVICE_REQUEST] ([SERVICE_REQUEST_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK2] FOREIGN KEY([PREVIOUS_SERVICE_REQUEST_ID])
REFERENCES [dbo].[SERVICE_REQUEST] ([SERVICE_REQUEST_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK2]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK3] FOREIGN KEY([PEER_SERVICE_REQUEST_ID])
REFERENCES [dbo].[SERVICE_REQUEST] ([SERVICE_REQUEST_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUEST_SERVICE_REQUEST_FK3]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK1] FOREIGN KEY([TITLING_AGENCY_LIENHOLDER_ID])
REFERENCES [dbo].[TITLING_AGENCY_LIENHOLDER] ([TITLING_AGENCY_LIENHOLDER_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK2] FOREIGN KEY([LIENHOLDER_PERFORMING_LIEN_FILING_ID])
REFERENCES [dbo].[TITLING_AGENCY_LIENHOLDER] ([TITLING_AGENCY_LIENHOLDER_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK2]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK3] FOREIGN KEY([TRANSFER_TO_TITLING_AGENCY_LIENHOLDER_ID])
REFERENCES [dbo].[TITLING_AGENCY_LIENHOLDER] ([TITLING_AGENCY_LIENHOLDER_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [TITLING_AGENCY_LIENHOLDER_SERVICE_REQUEST_FK3]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [USR_SERVICE_REQUEST_FK1] FOREIGN KEY([USR_ID])
REFERENCES [dbo].[USR] ([USR_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [USR_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [XML_SERIALIZED_INSTANCE_SERVICE_REQUEST_FK1] FOREIGN KEY([XML_SERIALIZED_INSTANCE_ID])
REFERENCES [dbo].[XML_SERIALIZED_INSTANCE] ([XML_SERIALIZED_INSTANCE_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [XML_SERIALIZED_INSTANCE_SERVICE_REQUEST_FK1]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [XML_SERIALIZED_INSTANCE_SERVICE_REQUEST_FK2] FOREIGN KEY([CALIFORNIA_TRANSMITTAL_FORMXML_SERIALIZED_INSTANCE_ID])
REFERENCES [dbo].[XML_SERIALIZED_INSTANCE] ([XML_SERIALIZED_INSTANCE_ID])
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [XML_SERIALIZED_INSTANCE_SERVICE_REQUEST_FK2]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUEST_REQUEST_CATEGORY_Chk] CHECK (([REQUEST_CATEGORY] = 'TITLE_MAINTENANCE' or [REQUEST_CATEGORY] = 'NULL'))
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUEST_REQUEST_CATEGORY_Chk]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUESTBUSINESS_PROCESS_STATUS_Chk] CHECK (([BUSINESS_PROCESS_STATUS]='Cancelled' OR [BUSINESS_PROCESS_STATUS]='Closed' OR [BUSINESS_PROCESS_STATUS]='Open' OR [BUSINESS_PROCESS_STATUS]='NotStarted'))
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUESTBUSINESS_PROCESS_STATUS_Chk]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUESTELECTRONIC_TITLES_TO_TRANSFER_PER_DAY_Chk] CHECK (([ELECTRONIC_TITLES_TO_TRANSFER_PER_DAY]<=(5000)))
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUESTELECTRONIC_TITLES_TO_TRANSFER_PER_DAY_Chk]
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] WITH CHECK ADD CONSTRAINT [SERVICE_REQUESTNEW_YORK_LIEN_FILING_TRANSACTION_TYPE_Chk] CHECK (([NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'Notice' or [NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'Cancellation' or [NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'Release' or [NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'CollateralLoan' or [NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'Amendment' or [NEW_YORK_LIEN_FILING_TRANSACTION_TYPE] = 'InitialFiling'))
GO
ALTER TABLE [dbo].[SERVICE_REQUEST] CHECK CONSTRAINT [SERVICE_REQUESTNEW_YORK_LIEN_FILING_TRANSACTION_TYPE_Chk]
GO
October 20, 2015 at 8:12 am
The following indexes are redundant (left-based subsets of other indexes), and hence can be dropped
index_ServiceRequest_BusinessProcessStatus
index_ServiceRequest_LienholderPerformingLienFilingId
index_ServiceRequest_ServiceRequestStatus
Since most are single-column indexes, I doubt most are useful. That doesn't mean they won't be used, just that they probably aren't very useful to SQL. For nonclustered indexes, wider is often better.
Could you post actual execution plans for this please?
UPDATE SERVICE_REQUEST SET SERVICE_REQUEST_STATUS = @DerivedTable01_SERVICE_REQUEST_STATUS5529, LAST_PRINTED_ON_REPORT_DATE = @DerivedTable01_LAST_PRINTED_ON_REPORT_DATE5730, LAST_MOD_DATE_TIME = @DerivedTable01_LAST_MOD_DATE_TIME5931, LAST_MODIFIER = @DerivedTable01_LAST_MODIFIER6132, PRINT_REQUEST_ID = @DerivedTable01_PRINT_REQUEST_ID6333 FROM SERVICE_REQUEST AS CollateralGroupRequest02 CROSS JOIN TITLING_AGENCY_REQUEST_TRANSACTION_TYPE AS ServiceRequestTransactionType13 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedCollateralGroupItem24 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedOwnershipDoc25 INNER JOIN (OWNERSHIP_DOC AS OwnershipDoc26 LEFT OUTER JOIN (SCANNED_DOCUMENT_DATA AS ScannedDocumentData28 INNER JOIN BATCH AS Batch29 ON ScannedDocumentData28.BATCH_ID=Batch29.BATCH_ID) ON OwnershipDoc26.SCANNED_DOCUMENT_DATA_ID=ScannedDocumentData28.SCANNED_DOCUMENT_DATA_ID) ON ServicedOwnershipDoc25.OWNERSHIP_DOC_ID=OwnershipDoc26.OWNERSHIP_DOC_ID) ON ServicedCollateralGroupItem24.SERVICED
Also, what statements run before that update within the transaction it's in?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2015 at 10:15 am
Here is an example of the execution plan for these updates ( below ). When I look in the plan cache around the time of the deadlocks I see quite a few of these updates running around the same time with number of executions at 1 or 2. They get cached but quite often not re-used.
I'm checking with development about the "trans count=2" but might have to run a server side trace to get that.
creation_time last_execution_time
10/19/15 1:16 PM 10/19/15 1:16 PM
10/19/15 1:15 PM 10/19/15 1:15 PM
10/19/15 1:15 PM 10/19/15 1:15 PM
10/19/15 10:09 AM10/19/15 1:14 PM
10/19/15 10:06 AM10/19/15 1:14 PM
10/19/15 10:08 AM10/19/15 1:14 PM
October 20, 2015 at 1:59 pm
Would you mind deleting those hundreds of lines and rather just attaching the .sqlplan file please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2015 at 2:23 pm
I attached two
October 21, 2015 at 1:50 am
Lynn Pettis (10/19/2015)
Here is the update query initially post in a slightly more readable format:
UPDATE SERVICE_REQUEST
SET SERVICE_REQUEST_STATUS = 'PrintedOnPullReport'
,LAST_PRINTED_ON_REPORT_DATE = '2015-10-15 00:00:00'
,LAST_MOD_DATE_TIME = '2015-10-15 06:10:03.223'
,LAST_MODIFIER = 60417
,PRINT_REQUEST_ID = 69432
FROM SERVICE_REQUEST AS CollateralGroupRequest02
CROSS JOIN TITLING_AGENCY_REQUEST_TRANSACTION_TYPE AS ServiceRequestTransactionType13
INNER JOIN (
SERVICED_COLLATERAL_GROUP_ITEM AS ServicedCollateralGroupItem24 INNER JOIN (
SERVICED_COLLATERAL_GROUP_ITEM AS ServicedOwnershipDoc25 INNER JOIN (
OWNERSHIP_DOC AS OwnershipDoc26 LEFT JOIN (
SCANNED_DOCUMENT_DATA AS ScannedDocumentData28 INNER JOIN BATCH AS Batch29 ON ScannedDocumentData28.BATCH_ID = Batch29.BATCH_ID
) ON OwnershipDoc26.SCANNED_DOCUMENT_DATA_ID = ScannedDocumentData28.SCANNED_DOCUMENT_DATA_ID
) ON ServicedOwnershipDoc25.OWNERSHIP_DOC_ID = OwnershipDoc26.OWNERSHIP_DOC_ID
) ON ServicedCollateralGroupItem24.SERVICED_COLLATERAL_GROUP_ITEM_ID = ServicedOwnershipDoc25.SERVICED_COLLATERAL_GROUP_ITEM_ID
) ON CollateralGroupRequest02.SERVICE_REQUEST_ID = ServicedCollateralGroupItem24.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID
LEFT JOIN PRINT_REQUEST AS PrintRequest27 ON CollateralGroupRequest02.PRINT_REQUEST_ID = PrintRequest27.PRINT_REQUEST_ID
WHERE (
(
CollateralGroupRequest02.REQUEST_TRANSACTION_TYPE = ServiceRequestTransactionType13.REQUEST_TRANSACTION_TYPE
AND CollateralGroupRequest02.REQUEST_TRANSACTION_TYPE IN ('ReleaseToRegisteredOwner')
AND CollateralGroupRequest02.IS_EXPEDITED_FROM_VAULT = 0
AND CollateralGroupRequest02.MAIL_CARRIER IS NULL
AND CollateralGroupRequest02.RECORDED_REQUEST_DATE <= '2015-10-15 06:10:03.240'
AND CollateralGroupRequest02.BUSINESS_PROCESS_STATUS = 'Open'
AND CollateralGroupRequest02.IS_ON_HOLD_INDEFINITELY = 0
AND CollateralGroupRequest02.CLIENT_ID IN (
11330
,11220
,12001
,11234
,12336
,10712
,12342
,10
,11303
,12332
,12572
)
AND CollateralGroupRequest02.REQUEST_TRANSACTION_TYPE NOT IN (
'ContractRequest'
,'ContractRequestRc'
,'ContractRequestLc'
)
AND OwnershipDoc26.BUSINESS_OBJECT_STATUS = 'OPEN'
AND ServicedOwnershipDoc25.IS_PRIMARY_COLLATERAL_GROUP_ITEM = 1
AND (
CollateralGroupRequest02.SERVICE_REQUEST_STATUS = 'EligibleForPullReport'
AND CollateralGroupRequest02.FACILITY_ID = 1
OR (
PrintRequest27.BUSINESS_PROCESS_STATUS = 'Open'
AND PrintRequest27.LAST_MOD_DATE_TIME < '2015-10-15 06:05:03.223'
AND CollateralGroupRequest02.SERVICE_REQUEST_STATUS = 'PrintedOnPullReport'
)
)
AND (
CollateralGroupRequest02.IS_ACCELERATED_TITLE = 0
OR (CollateralGroupRequest02.IS_ACCELERATED_TITLE IS NULL)
)
AND (
CollateralGroupRequest02.IS_ACCELERATED_TITLE = 0
OR (CollateralGroupRequest02.IS_ACCELERATED_TITLE IS NULL)
)
AND (
(Batch29.CREATED_DATE_TIME <= '2015-10-15 00:00:00')
OR (ScannedDocumentData28.SCANNED_DOCUMENT_DATA_ID IS NULL)
)
)
)
Looking at this, I started having a problem following it. This is something I'd have to look at with more time than I have at the moment.
Thanks for posting this Lynn. I have trouble following it too! The first thing that springs to mind is that the query isn't hand-coded by design, it's a trial and error affair using a query designer and it's horrible. Whilst it might be possible to improve the performance with a little judicious index tweaking, a total rewrite would yield far more significant wins. That shouldn't be too hard - it's always easier to rewrite a query than set one up from scratch because result sets can be compared.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply