SQL Server query store as oracle plan management

Oracle DBA use SQL plan management (SPM) to stabilize query performance, now SQL Server 2016 has similar feature called query store,  This is case study to answer a few questions:

1. Observe how query store work, comparing query plan.

2.Can similar queries which use different literal use other query's plan? if not, what DBA can do to influence optimizer to to choose better query plan compiled for other literal.

3. Can the plan be exported from one sqlserver and import to other sqlserver?

Let's get into it:

1. Observe how query store work, comparing query plans.

I have two queries which are different with literal, one run fast, the other one run slow.

Slow query: use isnull(rh.userid,0) = 255 

SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 255 AND md.functionnum = 45000 and rh.remark like 'http%'))

Fast query: use isnull(rh.userid,0) = 167 

SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 167 AND md.functionnum = 45000 and rh.remark like 'http%'))

I ran the two queries a few times, notice the two query plans are different, even the slow query generated a good plan at one time, I guess I am lucky.

There is a way to guide SQL Server optimizer to choose good plan, I have a case study documented here:

https://walkthrough-dba.blogspot.com/2021/10/case-study-of-sql-server-plan-guide.html

I use the following query to find out query plan history, Let's call this dba_query_store_1:

SELECT qsp.query_id, QSP.plan_id,  QST.query_sql_text, qsq.query_hash, qsp.query_plan_hash, QSRS.last_duration,

QSRS.avg_duration, qsp.query_plan, QSQ.last_execution_time,  -- timezone is not right, show 7 hours ahead, also sometimes it's even less than the first_execution_time

QSRS.first_execution_time,  -- timezone is not right, show 7 hours ahead

QSRS.avg_logical_io_reads,QSRS.avg_logical_io_writes 

FROM sys.query_store_plan AS QSP  JOIN sys.query_store_query AS QSQ      ON QSP.query_id = QSQ.query_id  

JOIN sys.query_store_query_text AS QST      ON QSQ.query_text_id = QST.query_text_id 

JOIN sys.query_store_runtime_stats  QSRS   ON QSP.plan_id =QSRS.plan_id

where QST.query_sql_text like 'SELECT top 1 %'

order by QSQ.last_execution_time 

(query_id=30 and plan_id=4) is the slow one, (plan_id=6) is faster one

You can save the query_plan as .sqlplan and compare it to other plan, I will do that later.

So, let me fix the query to use a good and bad plan respectively

EXEC sp_query_store_force_plan @query_id = 30, @plan_id = 4;

Run the slow query, it completed slow as expected, and dba_query_store_1 and observe which plan did it run, it generated a new plan plan_id=13, compare plan_id= 13 and plan_id=4, they are same as operators and steps, though the node id is different but in right order

Now switch it to plan_id=6, and run the query in SSMS with 'include actual execution plan', it complete d fast as expected.

EXEC sp_query_store_force_plan @query_id = 30, @plan_id = 6;

This time it use plan_id=6 and did not create new plan_id. interesting.

That's all for the observation. now let's move to next question:

2.  Can similar queries which use different literal use other query's plan? if not, what DBA can do to influence optimizer to to choose better query plan compiled for other literal.

Let's fix the slow query with the fast query's plan:

EXEC sp_query_store_force_plan @query_id = 30, @plan_id = 5;

Msg 12406, Level 11, State 1, Procedure sp_query_store_force_plan, Line 1 [Batch Start Line 83]

Query plan with provided plan_id (5) is not found in the Query Store for query (30). Check the plan_id value and rerun the command.

So the query plan is not exchangeable to the queries that use different literal. 

Can DBA give optimizer some hint as Oracle can do with outline? the answer is yes, plan guide can do it, I documented the case study here:

https://walkthrough-dba.blogspot.com/2021/10/case-study-of-sql-server-plan-guide.html

After I have a good plan for the slow query, then I can force the plan for the slow query, just as shown above.

I can also use another way of query guide: 

select statement blabla Option (use plan N'xml plan of faster query literal here')

which is documented in 

https://walkthrough-dba.blogspot.com/2021/10/case-study-of-sql-server-plan-guide.html

Let's move to question 3:

3. Can the plan be exported from one sqlserver and import to other sqlserver?

This link suggest that I can do it, let's give it a try.

https://dba.stackexchange.com/questions/231650/how-can-i-export-query-store-data

sp_configure 'remote admin connections',1

reconfigure

C:\>sqlcmd -S sqlservername -E -A

1> use dbname

2> go

1> select * into dropit from sys.query_store_plan

2> go

(13 rows affected)

1> delete from dropit where plan_id<>5

2> go

(12 rows affected)

1> update dropit set query_id=30, plan_id=14

2> go

(1 rows affected)

1> insert into sys.query_store_plan select * from dropit

2> go

Msg 259, Level 16, State 1, Server , Line 1

Ad hoc updates to system catalogs are not allowed.

ok, let's try another way:

1> select * into dropit from dbname.sys.plan_persist_plan where plan_id=5

2> go

1> update dropit set query_id=30, plan_id=15

2> go

1> INSERT INTO  dbname.sys.plan_persist_plan SELECT * FROM dropit

2> go

(1 rows affected)

1> EXEC sp_query_store_force_plan @query_id = 30, @plan_id = 15;

2> go

Msg 12406, Level 11, State 1, Server , Procedure sp_query_store_force_plan, Line 1

Query plan with provided plan_id (15) is not found in the Query Store for query (30). Check the plan_id value and rerun the command.

1> delete from eatecx.sys.plan_persist_plan where plan_id=15

2> go

(1 rows affected)

1>

So I cannot steal and force plan from another query literal, I do think that I can trick it more and make it work, but because I already have solution of guide the slow query to use a good plan by using option (use plan N'xml faster plan'), why bother to trick it this way? I can just guide the slow query to use faster plan, then force it as shown above.

That's all. feel better to know that Microsoft SQL Server is having some of Oracle's SPM and outline features!