Query Store Usage Scenarios

What is Query Store?

Query Store is a tool to troubleshoot query performance by capturing a range of information about query usage, CPU, memory consumption, I/O and execution time, and retaining every Execution Plan for analysis.

So, if query store stores my query information, what kind of scenarios I could use this information?

1. To fix queries with plan choice regressions is one example where to use it. Query Optimizer may decide to choose a different plan and without Query Store would be difficult to identify and fix it.
2.  Tune top resource consuming queries is much more easier with Query Store because this tool will show the plan is using in a query that has high reads or duration for example.
3. To compare workload performance before and after the application change or adding new hardware, create indexes or the next scenario SQL Server updates.
4. To keep performance stability during the upgrade to newer SQL Server
5. To improve ad hoc workloads. Run the Transact-SQL script bellow to get total number of query texts, queries, and plans in the system and determine how different they are by comparing their query_hash and plan_hash

/*Do cardinality analysis when suspect on ad hoc workloads*/
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM sys.query_store_query;
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;
SELECT COUNT(DISTINCT query_plan_hash) AS CountDifferentPlanRows FROM sys.query_store_plan; 

 

SQL Server Undocumented 2

One more undocumented function/commands. The fn_dump_dblog function is used to read transaction logs from a log backup file.

This is an example fo the undocumented function to read transaction logs:

SELECT [Current LSN], [Operation], [Transaction Name], [Transaction ID], SUSER_SNAME ([Transaction SID]) AS DBUser 
FROM fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'C:\temp\Backup_Logfile.trn',
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

A good example to use it is when I tried to restore up to specific LSN using the command

RESTORE LOG  WITH STOPBEFOREMARK = ‘lsn:’;

To read your current log file the function fn_dblog is used. For more information about this function click here

Follow bellow the operations is returned for the function:

OPERATION	DESCRIPTION
LOP_ABORT_XACT	Indicates that a transaction was aborted and rolled back.
LOP_BEGIN_CKPT	A checkpoint has begun.
LOP_BEGIN_XACT	Indicates the start of a transaction.
LOP_BUF_WRITE	Writing to Buffer.
LOP_COMMIT_XACT	Indicates that a transaction has committed.
LOP_COUNT_DELTA	?
LOP_CREATE_ALLOCCHAIN	New Allocation chain
LOP_CREATE_INDEX	Creating an index.
LOP_DELETE_ROWS	Rows were deleted from a table.
LOP_DELETE_SPLIT	A page split has occurred. Rows have moved physically.
LOP_DELTA_SYSIND	SYSINDEXES table has been modified.
LOP_DROP_INDEX	Dropping an index.
LOP_END_CKPT	Checkpoint has finished.
LOP_EXPUNGE_ROWS	Row physically expunged from a page, now free for new rows.
LOP_FILE_HDR_MODIF	SQL Server has grown a database file.
LOP_FORGET_XACT	Shows that a 2-phase commit transaction was rolled back.
LOP_FORMAT_PAGE	Write a header of a newly allocated database page.
LOP_HOBT_DDL	?
LOP_HOBT_DELTA	?
LOP_IDENT_NEWVAL	Identity’s New reseed values
LOP_INSERT_ROWS	Insert a row into a user or system table.
LOP_LOCK_XACT
LOP_MARK_DDL	Data Definition Language change – table schema was modified.
LOP_MARK_SAVEPOINT	Designate that an application has issued a ‘SAVE TRANSACTION’ command.
LOP_MIGRATE_LOCKS
LOP_MODIFY_COLUMNS	Designates that a row was modified as the result of an Update command.
LOP_MODIFY_HEADER	A new data page created and has initialized the header of that page.
LOP_MODIFY_ROW	Row modification as a result of an Update command.
LOP_PREP_XACT	Transaction is in a 2-phase commit protocol.
LOP_SET_BITS
LOP_SET_BITS	Designates that the DBMS modified space allocation bits as the result of allocating a new extent.
LOP_SET_FREE_SPACE	Designates that a previously allocated extent has been returned to the free pool.
LOP_SORT_BEGIN	A sort begins with index creation. – SORT_END end of the sorting while creating an index.
LOP_SORT_EXTENT	Sorting extents as part of building an index.
LOP_UNDO_DELETE_SPLIT	The page split process has been dumped.
LOP_XACT_CKPT	During the Checkpoint, open transactions were detected.