microsoft / microsoft/sql-server-samples
query store demo broken in multiple places
Nobody has claimed this yet.
- Dominant language
- PowerShell
- Stars
- 11.2k
- Forks
- 9.1k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 14
Description
trying to use this to help my devs better understand query store and noticed some really glaring issues that make it almost unusable, especially for a novice user.
Perhaps there is a missing "setup" file that would create the required table and views to start the demo with the ShowBasics.sql file.
The entire script relies on a "Part" table that does not exist in the AdventureWorks2016_EXT restored db, nor the install script versions.
I added the following and it seems to work:
/* Create the demo Part table */
CREATE TABLE dbo.Part (
PartId INT NOT NULL PRIMARY KEY,
PartName NVARCHAR(50) NOT NULL
);
GO
INSERT INTO dbo.Part (PartId, PartName)
VALUES
(1, 'Part_000001'),
(2, 'Part_000002'),
(3, 'Part_000003'),
(4, 'Part_000004'),
(5, 'Part_000005');
GO
It is also missing the two views that the script assumes already exists
CREATE VIEW vw_QueryStoreCompileInfo AS
SELECT
qsqt.query_sql_text,
qsq.query_id,
qsp.plan_id,
qsq.object_id,
qsp.query_plan
FROM sys.query_store_query_text AS qsqt
JOIN sys.query_store_query AS qsq
ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan AS qsp
ON qsq.query_id = qsp.query_id;
CREATE VIEW vw_QueryStoreRuntimeInfo AS
SELECT
qsqt.query_sql_text,
qsq.query_id,
qsp.plan_id,
qsrs.count_executions,
qsrs.avg_duration,
qsrs.avg_logical_io_reads,
qsrs.avg_cpu_time,
qsrs.last_execution_time
FROM sys.query_store_query_text AS qsqt
JOIN sys.query_store_query AS qsq
ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan AS qsp
ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats AS qsrs
ON qsp.plan_id = qsrs.plan_id;
I hope those are correct but I got through the end of the script with those.
Lastly the joins on the fn_stmt_sql_handle_from_sql_stmt and vw_QueryStoreCompileInfo do not work by the sql_handle, I was able to use the query_sql_text column, maybe this is a version thing, or again I created the views incorrectly but it appears to fix the issue and display the info as intended, I'll add the entire file here with that corrected block of code.
hth
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with ShowBasics.sql and compare it with the attached ShowBasics-v2.sql, then inspect the AdventureWorks2016_EXT and install script versions for the expected Part table and Query Store views. Reproduce the demo and verify that the setup and joins work through the end of the script, including displaying the intended information.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100