DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
What's in store for DevOps in 2023? Hear from the experts in our "DZone 2023 Preview: DevOps Edition" on Fri, Jan 27!
Save your seat
  1. DZone
  2. Data Engineering
  3. Databases
  4. Missing Indexes in the Query Store

Missing Indexes in the Query Store

Another place to find missing index suggestions is the Query Store.

Grant Fritchey user avatar by
Grant Fritchey
·
Mar. 12, 19 · Tutorial
Like (3)
Save
Tweet
Share
6.43K Views

Join the DZone community and get the full member experience.

Join For Free

I've shown before how to use the DMVs that read the plan cache as a way to connect the missing indexes suggestions with specific queries, but the other place to find missing index suggestions is the Query Store.Image title

Pulling From the Query Store

The plans stored in the Query Store are exactly the same as the plans stored within the plan cache. This means that the XML is available and you can try to retrieve information from it directly, just as we did with the missing index queries against the DMVs. Here's the query modified for the Query Store:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT qsqt.query_sql_text,
       rts.plan_id,
       rts.NumExecutions,
       rts.MinDuration,
       rts.MaxDuration,
       rts.AvgDuration,
       rts.AvgReads,
       rts.AvgWrites,
       qsp.QueryPlan,
       qsp.QueryPlan.value(N'(//MissingIndex/@Table)[1]',
                           'NVARCHAR(256)') AS TableName,
       qsp.QueryPlan.value(N'(//MissingIndex/@Schema)[1]',
                           'NVARCHAR(256)') AS SchemaName,
       qsp.QueryPlan.value(N'(//MissingIndexGroup/@Impact)[1]',
                           'DECIMAL(6,4)') AS ProjectedImpact,
       ColumnGroup.value('./@Usage',
                         'NVARCHAR(256)') AS ColumnGroupUsage,
       ColumnGroupColumn.value('./@Name',
                               'NVARCHAR(256)') AS ColumnName
FROM sys.query_store_query AS qsq
    JOIN sys.query_store_query_text AS qsqt
        ON qsqt.query_text_id = qsq.query_text_id
    JOIN (   SELECT query_id,
                    CAST(query_plan AS XML) AS QueryPlan,
                    plan_id
             FROM sys.query_store_plan) AS qsp
        ON qsp.query_id = qsq.query_id
    JOIN (   SELECT qsrs.plan_id,
                    SUM(qsrs.count_executions) AS NumExecutions,
                    MIN(qsrs.min_duration) AS MinDuration,
                    MAX(qsrs.max_duration) AS MaxDuration,
                    AVG(qsrs.avg_duration) AS AvgDuration,
                    AVG(qsrs.avg_logical_io_reads) AS AvgReads,
                    AVG(qsrs.avg_logical_io_writes) AS AvgWrites
             FROM sys.query_store_runtime_stats AS qsrs
             GROUP BY qsrs.plan_id) AS rts
        ON rts.plan_id = qsp.plan_id
    CROSS APPLY qsp.QueryPlan.nodes('//MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS t1(ColumnGroup)
    CROSS APPLY t1.ColumnGroup.nodes('./Column') AS t2(ColumnGroupColumn);

A couple of notes on the query. I cast the query_plan as XML so that I can use the XQuery to pull out the information. It is possible that the plan might be so large that you get an error because of the limit on nesting levels within XML. Also, I aggregate the information from the sys.query_store_runttime_stats. You may want to modify this to only look at limited ranges. I'll leave that to you as an exercise.

Conclusion

If you take that query as written and run it on a production database, it's likely to cause some problems since there is no filtering in place. So, I strongly recommend that before using this, you plan on filtering by query, object, or text. However, once you do, this returns the query, the plan, and the missing indexes information.

If you want to learn more about how the Query Store works, I have a couple of upcoming all-day seminars that include the Query Store as well as other tools built into SQL Server, all focused around making it easier for you to identify and tune queries:

SQL Day, May 13, 2019, Wroclaw, Poland
SQLSaturday Columbus Precon, June 7, 2019, Columbus OH

Database

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Last Chance To Take the DZone 2023 DevOps Survey and Win $250! [Closes on 1/25 at 8 AM]
  • Unlocking the Power of Polymorphism in JavaScript: A Deep Dive
  • Kotlin Is More Fun Than Java And This Is a Big Deal
  • How to Develop a Portrait Retouching Function

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: