Getting started with Microsoft SQL ServerOVER ClausePIVOT / UNPIVOTDatabase SnapshotsRetrieve information about the databaseThe STUFF FunctionFOR XML PATHCursorsJoinCommon Table ExpressionsMove and copy data around tablesDatesLimit Result SetRetrieve Information about your InstanceWith Ties Option VariablesJSON in Sql ServerWindow functionsPartitioningStored ProceduresGROUP BYGenerating a range of datesCOALESCESplit String function in Sql ServerINSERT INTOCREATE VIEWString FunctionsResource GovernorORDER BYWHILE loopSystem database - TempDbMigrationPrimary KeysMERGEFull-Text IndexingFOR JSONSELECT statementDBMAILIndexQueries with JSON dataStoring JSON in SQL tablesOPENJSONRanking FunctionsTriggerConverting data typesNULLsTransaction isolation levelsAdvanced optionsIF...ELSETRY/CATCHData TypesUser Defined Table TypesTable Valued ParametersIn-Memory OLTP (Hekaton)Temporal TablesInsertSequencesSCOPE_IDENTITY()ViewsUse of TEMP TableScheduled Task or JobIsolation levels and lockingSorting/ordering rowsPrivileges or PermissionsForeign KeysSQLCMDFile Groupcross applyBasic DDL Operations in MS SQL ServerComputed ColumnsUNIONSubqueriesLast Inserted IdentityCLUSTERED COLUMNSTOREParsenameInstalling SQL Server on WindowsAggregate FunctionsQuerying results by pageSchemasBackup and Restore DatabaseTransaction handlingNatively compiled modules (Hekaton)Database permissionsSpatial DataDynamic SQLPaginationQuery HintsModify JSON textRow-level securityDynamic data maskingExport data in txt file by using SQLCMDEncryptionManaging Azure SQL DatabaseCommon Language Runtime IntegrationDelimiting special characters and reserved wordsCASE StatementDBCCBULK ImportQuery StoreService brokerAnalyzing a QueryMicrosoft SQL Server Management Studio Shortcut KeysPermissions and SecurityPHANTOM readFilestreamDrop KeywordString Aggregate functions in SQL ServerSQL Server Evolution through different versions (2000 - 2016)SQL Server Management Studio (SSMS)Logical FunctionsDynamic SQL PivotAlias Names in Sql Serverbcp (bulk copy program) Utility

Query Store

Other topics

Enable query store on database

Query store can be enabled on database by using the following command:

ALTER DATABASE tpch SET QUERY_STORE = ON

SQL Server/Azure SQL Database will collect information about executed queries and provide information in sys.query_store views:

  • sys.query_store_query
  • sys.query_store_query_text
  • sys.query_store_plan
  • sys.query_store_runtime_stats
  • sys.query_store_runtime_stats_interval
  • sys.database_query_store_options
  • sys.query_context_settings

Get execution statistics for SQL queries/plans

The following query will return informationa about qeries, their plans and average statistics regarding their duration, CPU time, physical and logical io reads.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id,
        avg_duration, avg_cpu_time, 
        avg_physical_io_reads, avg_logical_io_reads
FROM sys.query_store_plan AS Pl  
JOIN sys.query_store_query AS Qry  
    ON Pl.query_id = Qry.query_id  
JOIN sys.query_store_query_text AS Txt  
    ON Qry.query_text_id = Txt.query_text_id
JOIN sys.query_store_runtime_stats Stats
    ON Pl.plan_id = Stats.plan_id

Remove data from query store

If you want to remove some query or query plan from query store, you can use the following commands:

EXEC sp_query_store_remove_query 4;
EXEC sp_query_store_remove_plan 3; 

Parameters for these stored procedures are query/plan id retrieved from system views.

You can also just remove execution statistics for particular plan without removing the plan from the store:

EXEC sp_query_store_reset_exec_stats 3;  

Parameter provided to this procedure plan id.

Forcing plan for query

SQL Query optimizer will choose the baes possible plan that he can find for some query. If you can find some plan that works optimally for some query, you can force QO to always use that plan using the following stored procedure:

EXEC sp_query_store_unforce_plan @query_id, @plan_id

From this point, QO will always use plan provided for the query.

If you want to remove this binding, you can use the following stored procedure:

EXEC sp_query_store_force_plan @query_id, @plan_id

From this point, QO will again try to find the best plan.

Contributors

Topic Id: 7349

Example Ids: 24390,24391,24392,24393

This site is not affiliated with any of the contributors.