Post reply

Query Store vs Extended Events

  • 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

  • November 5, 2019 at 11:21 am

    #3695069

    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

  • November 6, 2019 at 8:44 am

    #3695368

    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

  • November 6, 2019 at 8:53 am

    #3695373

    just google it, you'll find dozens of articles

    extended events sql save to table

    that's what I searched for

    MVDBA

  • November 6, 2019 at 11:13 am

    #3695397

    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

     

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

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