0

Can you point me in the right direction on where to look to figure out why one plan cache is different from the other one in another database. One database is older and has less data but the schema should be intact, along with the compatibility mode.

I have done things like freeproc, reset statistics, full scan and more. One seems to do an Index Scan while the other an Index Seek. They have the same indexes, stats look similar but not exact. The query is listed in blitzCache but no warnings.

Production https://www.brentozar.com/pastetheplan/?id=rkU8tqhmY

Development https://www.brentozar.com/pastetheplan/?id=S1-XYc2mY

ALTER PROCEDURE [GetTeamPlayerCount]
    @EventId INT,
    @Active INT = 1
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        tp.TeamId,
        COUNT(*) AS [Count]
    FROM
        Division d 
    INNER JOIN
        DivisionTeam dt ON dt.DivisionId = d.Id 
    INNER JOIN
        TeamPlayer tp ON dt.Id = tp.TeamId
    WHERE
        d.EventId = @EventId AND tp.Active = @Active
    GROUP BY
        tp.TeamId
END

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
  • 1
    Can you include the actual execution plans using [PasteThePlan](https://www.brentozar.com/pastetheplan). Have you tried both with the same literal values instead of parameters. also what version of SQL Server? – Stu Sep 25 '21 at 12:38
  • Yes same literal values, pasted the plan links – Mike Flynn Sep 25 '21 at 12:43
  • I'm assuming both databases are on the same server? – Mitch Wheat Sep 25 '21 at 12:51
  • Yes they are on the same server – Mike Flynn Sep 25 '21 at 12:51
  • 1
    I suggest you update stats. The better plan shows a last stats update for index `IX_TeamId_Includes` of '2021-09-25T08:31:09.86' but it's '2021-08-26T13:10:57.88' in the worse plan. – Dan Guzman Sep 25 '21 at 12:58
  • Do the queries differ significantly in elapsed time? – Mitch Wheat Sep 25 '21 at 12:58
  • 1
    At a quick glance I can see the statistics are quite different between the two, with different sampling percentages and dates. The plan shows the estimates are quite innacurate, I would update stats / rebuild all the indexes on both and see what results. – Stu Sep 25 '21 at 13:01
  • Both execute within 0 seconds. I reset statistics from management studio on `[IX_TeamId_Includes]` and still its doing a scan on that index and not a seek like the other. – Mike Flynn Sep 25 '21 at 13:02
  • 1
    It's not just the one index that's relevant though. the scan is most likely based on the data cardinality in the stats which has resulted in a merge join operation having a lower cost. – Stu Sep 25 '21 at 13:05
  • 3
    "Both execute within 0 seconds" - so what's your concern? – Mitch Wheat Sep 25 '21 at 13:07
  • Showing up in BlitzCache, one is different, so I am just curious on why and if I can get them the same. This is not the end of the world like you mentioned. Also getting an excessive grant warning in the query plan. – Mike Flynn Sep 25 '21 at 13:08
  • 1
    one is reporting an 'excessive memory grant', BUT the requested memory is the default requested of 1024KB, it's just that it only used 48KB of it. I think you might be worrying about this unnecessarily (out of date stats withstanding) – Mitch Wheat Sep 25 '21 at 13:10
  • SQL Server uses statistical optimization, which means that the query plan is based on both the amount and distribution of data in the tables. If they are not the same, then different plans can result *because the optimizer thinks that they require different plans for best performance*. – RBarryYoung Sep 25 '21 at 13:14
  • Ideally the query should be using the Division table first, since its a small number to join with to the TeamPlayer index. The query that does the scan starts with the Index which does a scan first. Just curious why the Division isnt used first in the plan. – Mike Flynn Sep 25 '21 at 13:16
  • 2
    @MikeFlynn, update status for all tables and indexes in the query: `UPDATE STATISTICS dbo.Division WITH FULLSCAN;UPDATE STATISTICS dbo.DivisionTeam WITH FULLSCAN;UPDATE STATISTICS dbo.TeamPlayer WITH FULLSCAN;` – Dan Guzman Sep 25 '21 at 13:19
  • Yes the "excessive memory grant" warning is a complete red-herring, SQL Server could be better at this, 1mb is the default memory allocation per query and SQL Server *can't* allocate less, unless you fiddle with the server defaults. – Stu Sep 25 '21 at 13:34
  • 1
    Honestly I doubt either plan is really a concern. You could try rewriting this query as an `EXISTS` and see how that goes – Charlieface Sep 25 '21 at 23:14
  • @DanGuzman I did what you suggested and got much better statistics. If you want to mark that as an answer please do. – Mike Flynn Sep 27 '21 at 14:20

1 Answers1

0

The last stats update as shown in the execution plans is significantly different between the two databases. This may result in different plans even with similar data due to different row count estimates.

Update statistics for all tables referenced in the query. The FULLSCAN option might be overkill but won't do harm.

UPDATE STATISTICS dbo.Division WITH FULLSCAN;
UPDATE STATISTICS dbo.DivisionTeam WITH FULLSCAN;
UPDATE STATISTICS dbo.TeamPlayer WITH FULLSCAN;
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71