Query Store Complete plan

Question

Query Store Complete plan

simon_s

Ten Centuries

Points: 1358
More actions
September 12, 2022 at 10:27 am

#4086563

Hi , is it possible to retrieve the entire plan for an object from querystore rather than the plan "snippet" that it views as troublesome ?

many thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

/*

Get long query text from QueryStore 

*/
Select *
from sys.query_store_query
where query_id = 44068


SELECT QSQT.query_text_id, 
       QSQT.query_sql_text, 
       QSQT.statement_sql_handle, 
       QSQT.is_part_of_encrypted_module, 
       QSQT.has_restricted_text
FROM sys.query_store_query QSQ
inner join sys.query_store_query_text QSQT 
on QSQT.query_text_id = QSQ.query_text_id
where QSQ.query_id = 44068 
--and query_text_id = 2211

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

simon_s Ten Centuries Points: 1358 More actions · Answer 2

Hi Johan

thank you for replying but that doesnt actually return the complete query plan for an object

simon

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

I'm sorry, I misinterpreted / half read your request.

As you noticed, in SQL2019 QS only stores individual query plans.

For stored procedures its tied by object_id.

Maybe this gets you started.

Declare @ProcName sysname = 'spc_Del_Serverdata_for_CollectTS'

if object_id('tempdb..#ObjId') is not null drop table #ObjId ;
Create table #ObjId ( object_id int not null )

Declare @object_id int = object_id(@ProcName)

Insert into #ObjId values ( @object_id )

SELECT convert(xml,p.query_plan) as query_plan
 , *
FROM sys.query_store_plan AS p
where exists ( Select *
from sys.query_store_query AS q
JOIN #ObjId O
on O.object_id = q.object_id
where q.query_id = p.query_id
) 
order by p.plan_id ;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Just for clarification, each execution plan is only, ever, for a given query. Sure, a batch or procedure might have 50 queries, but there will be 50, individual, execution plans for each of those queries. Yes, if you run the batch and capture the plans, you see all 50, but, that's 50 individual plans, not one plan.

Johan's totally on the right track.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

simon_s Ten Centuries Points: 1358 More actions · Answer 5

Hi Grant

yes fully understand that and my fault for not been clearer in my initial request.. I should have worded it more

" is there anyway to get all the individual plans for a parent object (proc) ". I will amend

many thanks Simon

simon_s Ten Centuries Points: 1358 More actions · Answer 6

simon_s

Ten Centuries

Points: 1358

September 13, 2022 at 3:01 pm

#4087149

thank you much appreciated I shall have a play !