Query Store vs Extended Events

Question

Query Store vs Extended Events

ktflash

Default port

Points: 1400
More actions
November 5, 2019 at 10:42 am

#3695059
Ahoi,
i am currently reading "SQL Server 2017 Query Performance Tuning" 5th edition by Grant Fritchey.
In Chapter 6: Query Performance Metrics
He suggests finding expensive and longrunning queries by using extended events. I have tested around a bit using different event libraries and configuring them. Now i have 2 questions:
- Is it possible to use this for logging the results into a table? Simple Example: Getting the Query, Duration and Timestamp of execution for a certain database with a duration higher than X. Creating the Event itself is not that hard and the results in the live view are as i wanted them. The question is, can i somehow place these details/results into a custom table instead of a file?
- Is there a reason why he suggests using extendend events instead of the Query Store in SSMS?
I want to be the very best
Like no one ever was
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

the query store is a useful tool, but as soon as your proc cache changes or you reboot your server then you start losing all of the valuable information it collects.

there are quite a few articles you can find about querying data from extended events logging files, but I'm old fashioned - I like profiler traces because I can stick them straight into a table (grant will hate me for that)

my advice - use all of the tools, query store is great for identifying rogue plans... extended events and profiler for deadlocks and security issues... don't pick one over the other

MVDBA

ktflash Default port Points: 1400 More actions · Answer 2

MVDBA (Mike Vessey) wrote:

the query store is a useful tool, but as soon as your proc cache changes or you reboot your server then you start losing all of the valuable information it collects.
there are quite a few articles you can find about querying data from extended events logging files, but I'm old fashioned - I like profiler traces because I can stick them straight into a table (grant will hate me for that)
my advice - use all of the tools, query store is great for identifying rogue plans... extended events and profiler for deadlocks and security issues... don't pick one over the other

does make sense to not only one of these
the part of Query Store only having cached data is the critical information i was missing i guess

Any idea on how to save the extended events results into a table?

I mean i cant be the first one wanting to save the results in a table.

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

just google it, you'll find dozens of articles

extended events sql save to table

that's what I searched for

MVDBA

Andrey Right there with Babe Points: 780 More actions · Answer 4

MVDBA (Mike Vessey) wrote:

the query store is a useful tool, but as soon as your proc cache changes or you reboot your server then you start losing all of the valuable information it collects.

neither proc cache changes, nor reboot affects query store

query store has its own configuration parameters (max size, max size cleanup, max plans/query, etc) which control its size and content

also, queries with (option recompile) will be captured also

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

Just saw this on a search for some information on Query Store. So, I know this is three years old. However...

I forgive you Mike.

Reboots, etc., do not directly affect the data captured by Query Store. One advantage that using QS has over just looking at the DMVs is that it persists the data for a period of time.

You can output the Extended Events to a file, which is preferable. Then, read directly from that file to load everything into a table through fn_xe_file_target_read_file. Here's an example.

Apologies for posting on an old thread.

"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