To much estimated rows

LukaszTerlecki 2015-04-01 15:44:10

Please if you can look into my issue and addvice if there is any possible fix for my issue. Database is for Microsoft Service Manager and is arround 80GB whene estimated data size by SQL is in TB. There is no way to change query so maybe additional index or some othere trick could help.

/storage/attachments/1186-Plan.pesession

SQLkiwi 2015-04-01 16:23:29

You say you can't change the query, but would you be able (for example) to change the current KEEP PLAN query hint to KEEPFIXED PLAN? How about a Plan Guide? Is that an option for you?

LukaszTerlecki 2015-04-02 06:30:24

Hi,
I don't have access to application code and no change to code can be made. I have sysadmin rights to SQL and plan guide can could be an option.

SQLkiwi 2015-04-03 13:48:20

I'm going to start by assuming existing statistics are FULLSCAN and are up-to-date.

The joins that result in the biggest increases in estimated cardinality appear to be joins to the Relationship table. One thing you could try is to create filtered statistics to communicate better information about the data for those joins to the query optimizer.

You may find that estimates improve after creating filtered statistics on SourceEntityID for a each distinct RelationshipTypeId referenced in a join to the Relationship table. For example, working from the query given, you could create these:

CREATE STATISTICS s01 -- Choose better names!
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'A860C62E-E675-B121-F614-E52FCBD9EF2C'};
 
CREATE STATISTICS s02
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'CE423786-16DD-DA9C-FB7B-21AB5189E12B'};
 
CREATE STATISTICS s03
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'835A64CD-7D41-10EB-E5E4-365EA2EFC2EA'};
 
CREATE STATISTICS s04
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'2DA498BE-0485-B2B2-D520-6EBD1698E61B'};
 
CREATE STATISTICS s05
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'CB6CE813-EA8D-094D-EE5A-B755701F4547'};
 
CREATE STATISTICS s06
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'B73A6094-C64C-B0FF-9706-1822DF5C2E82'};
 
CREATE STATISTICS s07
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'D96C8B59-8554-6E77-0AA7-F51448868B43'};
 
CREATE STATISTICS s08
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'EC8A256B-CC89-6F18-AF6A-E9CDC38CC573'};
 
CREATE STATISTICS s09
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'AA8C26DC-3A12-5F88-D9C7-753E5A8A55B4'};
 
CREATE STATISTICS s10
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'DF738111-C7A2-B450-5872-C5F3B927481A'};
 
CREATE STATISTICS s11
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'15E577A3-6BF9-6713-4EAC-BA5A5B7C4722'};
 
CREATE STATISTICS s12
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE'};
 
CREATE STATISTICS s13
ON [ServiceManager].[dbo].[Relationship] ([SourceEntityID])
WHERE [RelationshipTypeId] = {guid'FFD71F9E-7346-D12B-85D6-7C39F507B7BB'};

These new statistics may give better estimates and a shorter compilation time. If not, you might get lucky with an OPTION (FORCE ORDER, LOOP JOIN) sort of hint. That would rather depend on the written order of the query also being a reasonable execution order, and that all required indexes exist to support nested loops joins. You could try OPTION (FORCE ORDER, HASH JOIN) as well, but with such inaccurate estimates, that may cause as many problems as it solves. Adding these hints without changing the query would require a plan guide.

In short, unless you can find a way to improve the estimates using filtered statistics/filtered indexes, it comes down to getting lucky with hints. Achieving more than that from here would require physical access to the database, I'm afraid.

April 1, 2015

No Comments