To much estimated rows

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.

Plan.pesession (22.8 kB)
avatar image By LukaszTerlecki 1 asked Apr 01, 2015 at 03:44 PM
more ▼
(comments are locked)
avatar image SQLkiwi ♦ Apr 01, 2015 at 04:23 PM

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?

avatar image LukaszTerlecki Apr 02, 2015 at 06:30 AM

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.

10|10000 characters needed characters left

1 answer: sort voted first

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.

sp.png (41.7 kB)
sp.png (19.3 kB)
avatar image By SQLkiwi ♦ 6.6k answered Apr 03, 2015 at 01:48 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.