September 19, 2016 at 10:35 am
When I capture the query plan of a sql statement, normal parameters are shown at the bottom of the query plan xml. The string supplied in a CONTAINS full-text parameter isn't shown there anywhere. Do I have to run profiler to gather that?
CONTAINS(AccountOwnershipDocSummary02.BORROWER_FULL_NAMES, @DerivedTable01_BORROWER_FULL_NAMES52
SELECT TOP 100 AccountOwnershipDocSummary02.ACCOUNT_ID AS AccountId,AccountOwnershipDocSummary02.OWNERSHIP_DOC_ID AS OwnershipDocId,AccountOwnershipDocSummary02.OWNER_FULL_NAMES AS Owners,AccountOwnershipDocSummary02.BORROWER_FULL_NAMES AS Borrowers,AccountOwnershipDocSummary02.OWNERSHIP_DOC_MODIFIED_MANUFACTURER_ID AS OwnershipDocVin,AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS ModifiedAccountVin,AccountOwnershipDocSummary02.ORIGINAL_ACCOUNT_MANUFACTURER_ID AS OriginalAccountVin,AccountOwnershipDocSummary02.OWNERSHIP_DOC_ISSUING_STATE_ABBR AS OwnershipDocState,AccountOwnershipDocSummary02.CLIENT_ID AS ClientId,AccountOwnershipDocSummary02.LIENHOLDER_IDENTIFIER AS LienholderIdentifier,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_1 AS AccountNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_2 AS LoanNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_3 AS LoanSuffix,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_4 AS Branch,AccountOwnershipDocSummary02.EXPECTED_TITLING_STATE_ABBR AS AccountState,AccountOwnershipDocSummary02.STATUS AS Status,AccountOwnershipDocSummary02.LICENSE_NUMBER AS LicenseNumber,AccountOwnershipDocSummary02.OWNERSHIP_DOC_DOCUMENT_NUMBER AS TitleNumber,Client14.SHORT_NAME AS ClientShortName FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 INNER JOIN ORGANIZATION AS Client14 ON AccountOwnershipDocSummary02.CLIENT_ID=Client14.ORGANIZATION_ID WHERE ((AccountOwnershipDocSummary02.ID IN (SELECT AccountOwnershipDocSummary02_ID8 FROM (SELECT AccountOwnershipDocSummary02.ID AS AccountOwnershipDocSummary02_ID8 FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 WHERE ((((AccountOwnershipDocSummary02.EXPECTED_TITLING_STATE_ABBR = @DerivedTable01_EXPECTED_TITLING_STATE_ABBR10) OR (AccountOwnershipDocSummary02.OWNERSHIP_DOC_ISSUING_STATE_ABBR = @DerivedTable01_OWNERSHIP_DOC_ISSUING_STATE_ABBR31)) AND ( CONTAINS(AccountOwnershipDocSummary02.BORROWER_FULL_NAMES, @DerivedTable01_BORROWER_FULL_NAMES52) OR ( CONTAINS(AccountOwnershipDocSummary02.OWNER_FULL_NAMES, @DerivedTable01_OWNER_FULL_NAMES73)))))) AS ScalarQueryTable) AND AccountOwnershipDocSummary02.CLIENT_ID IN (SELECT Client02_ORGANIZATION_ID4 FROM (SELECT Client02.ORGANIZATION_ID AS Client02_ORGANIZATION_ID4 FROM ORGANIZATION AS Client02 INNER JOIN USR_ORGANIZATION_AUTHORIZATION AS UserOrganizationAuthorization13 ON Client02.ORGANIZATION_ID=UserOrganizationAuthorization13.SECURED_ORGANIZATION_ID WHERE ((UserOrganizationAuthorization13.USR_ID = @DerivedTable01_USR_ID24) AND ((Client02.CONCRETE_TYPE IN ( @DerivedTable01_35))))) AS ScalarQueryTable)))
September 19, 2016 at 11:28 am
Can you post the execution plan? Save the .sqlplan file, not a picture. We need to be able to see the properties.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 19, 2016 at 11:48 am
plan attached
September 19, 2016 at 12:43 pm
It's the table valued function, FullTextMatch. It says the estimated cost is zero, but I wouldn't believe that at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 19, 2016 at 1:20 pm
I was going to run the query and get the actual plan but needed the "borrower name" strings used in the original.( full text index on that ) I don't see that in the plan. I could just make up something:
contains 'Fitch'
September 19, 2016 at 1:53 pm
September 19, 2016 at 7:38 pm
That is a big number. Usually, but not always, that would indicate that the statistics are out of date or incorrect.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 20, 2016 at 7:26 am
Conclusion: query plans to not reveal the string values supplied for a full text CONTAINS statement.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply