We have a query that runs in our application very often. It starts with select top 100 and I posted a typical example at the bottom here as captured by profiler. The execution plan is attached and in that you can see wide variance between estimated and actual rows on two items in the account_ownership_doc_summary table.
The client in this example is fairly large. These queries also do a full text search on borrower/owner names – that’s where you’ll see the CONTAINS command. There is a unique non-clustered index on the table called ACCOUNT_OWNERSHIP_DOC_SUMMARY_UC1] UNIQUE NONCLUSTERED
I have some filtered statistics in place on the client_id column – ie, a statistic with a where clause so sql can estimate rows based on how many rows belong to a specific client.
There is an old partition scheme on the table that lists four clients, one of these is “FDI”, the old company name which doesn’t even have any rows in the table. It’s possible the partitioning is contributing to the poor cardinality estimates – not sure. We can’t just drop the index since it’s definitely getting used fairly heavily.
USE mydatabase GO
/ Object: PartitionScheme [OrgDataPartition] Script Date: 1/22/2017 5:37:38 PM / CREATE PARTITION SCHEME [OrgDataPartition] AS PARTITION [OrgDataPartitionFunction] TO ([FdFileGroup], [NmFileGroup], [ChaFileGroup], [FoFileGroup], [PRIMARY]) GO
USE mydatabase GO
/ Object: PartitionFunction [OrgDataPartitionFunction] Script Date: 1/22/2017 5:38:37 PM / CREATE PARTITION FUNCTION [OrgDataPartitionFunction](int) AS RANGE LEFT FOR VALUES (1, 10, 20, 30) GO
select o.short_name,o.organization_id,count(*) as clientcount from organization o join ACCOUNT_OWNERSHIP_DOC_SUMMARY a on o.ORGANIZATION_ID=a.client_id where o.organization_id in (1,10,20,30) group by o.short_name,o.organization_id
short_name, client_id record count in summary table FO 30 2300283 CHA 20 8200681 NM 10 8940409
ALTER TABLE [dbo].[ACCOUNT_OWNERSHIP_DOC_SUMMARY] ADD CONSTRAINT [ACCOUNT_OWNERSHIP_DOC_SUMMARY_UC1] UNIQUE NONCLUSTERED ( [CLIENT_ID] ASC, [ACCOUNT_ID] ASC, [OWNERSHIP_DOC_ID] ASC
exec sp_executesql N'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,Client13.SHORT_NAME AS ClientShortName, @LargeClient5 AS LargeClient FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 INNER JOIN (ORGANIZATION AS Client13 INNER JOIN USR_ORGANIZATION_AUTHORIZATION AS UserOrganizationAuthorization14 ON Client13.ORGANIZATION_ID=UserOrganizationAuthorization14.SECURED_ORGANIZATION_ID) ON AccountOwnershipDocSummary02.CLIENT_ID=Client13.ORGANIZATION_ID WHERE ((UserOrganizationAuthorization14.USR_ID = @DerivedTable01_USR_ID30 AND AccountOwnershipDocSummary02.CLIENT_ID = @DerivedTable01_CLIENT_ID51 AND ( CONTAINS(AccountOwnershipDocSummary02.BORROWER_FULL_NAMES, @DerivedTable01_BORROWER_FULL_NAMES72) OR ( CONTAINS(AccountOwnershipDocSummary02.OWNER_FULL_NAMES, @DerivedTable01_OWNER_FULL_NAMES93)))) AND ((Client13.CONCRETE_TYPE IN ( @DerivedTable01_114)))) ORDER BY 1',N'@DerivedTable01_USR_ID30 int,@DerivedTable01_CLIENT_ID51 int,@DerivedTable01_BORROWER_FULL_NAMES72 varchar(8000),@DerivedTable01_OWNER_FULL_NAMES93 varchar(8000),@DerivedTable01_114 varchar(8000),@LargeClient5 int',@DerivedTable01_USR_ID30=76712,@DerivedTable01_CLIENT_ID51=12332,@DerivedTable01_BORROWER_FULL_NAMES72='"glen*" AND "mott*"',@DerivedTable01_OWNER_FULL_NAMES93='"glen*" AND "mott*"',@DerivedTable01_114='Fdi.Po.Client',@LargeClient5=1
By Burnhaven 1 asked Jan 23, 2017 at 02:12 AM
I've discovered that removing the order by speeds this up from minutes to 5 seconds. The order by adds no value since it's a foreign key column that our application doesn't even display. Now to get the dev team to remove the order by in their ORM code. The partition scheme still bothers me though. Need to figure out how to get rid of it in QA without breaking things.
By Burnhaven 1 answered Feb 03, 2017 at 04:35 PM