Post reply

Confused regarding QueryStore and Azure Diagnostics

  • December 19, 2017 at 10:37 am

    #326944

    Greetings,
    I am interested in configuring our Azure SQL databases to use QueryStore. It seems like it's mostly just a matter of setting the database property for Query Store to be enabled/on, using Management Studio, and then optionally adjusting the default properties such as Size Based Cleanup Mode, Query Store Capture Mode, etc.
    However, there is something in the Azure Portal that seems to be related, & I'm unsure if I need to configure those features as well. If you go into the azure portal for a SQL database, there is a left-menu option "Diagnostic Settings", which if opened says "Turn on diagnostics to collect the following data" which includes QueryStoreRuntimeStatistcis & QueryStoreWaitStatistics, SQLInsights, & others. If I click on the "Turn on diagnostics" link, I am shown a page in which I can select "Archive to a storage account", "Stream to an event hub", or "Send to Log Analytics". I can also specify which of those logs to track, such as QueryStoreRuntimeStatistics or QueryStoreWaitStatistics.
    My primary question is, do I need to do something with those settings in the Azure Portal, or, am I fully able to use Query Store in SQL Server, as well as Query Performance Insight in the Azure Portal, without needing to do anything with those Azure Diagnostic Settings?
    My secondary question (assuming that I don't need to do anything with those Azure settings), then in what situations would I want to be doing something with those Azure settings, i.e., what are they for?
    Thanks,
    Randy
    iI

  • December 20, 2017 at 5:33 am

    #1972883

    Those are two different things. The Query Store is gathering query metrics inside your database. The Azure Diagnostics are gathering metrics about the performance of the database and it's relationship to the server. That's effectively outside your database. I'd use both. The wait statistics you're seeing are measuring how Query Store is behaving. In some edge cases, Query Store can negatively affect your database, so Microsoft is supplying metrics to observe the behavior.

    In short, use both.

    "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

Viewing 2 posts - 1 through 1 (of 1 total)

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