none
Query store : Data Flush Interval RRS feed

  • Question

  • If the query store set Data Flush Interval =15 .

    and I execute a query :

    select * from table.

    so the above query plan and stats will be stored in query store after 15 mins?

    Monday, September 2, 2019 2:38 AM

All replies

  • It means  that how often the data from memory is flushed on to the disk automatically rather than you running sp_query_store_flush_db manually 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 2, 2019 4:20 AM
    Moderator
  • Up to 15 minutes. The writing is done at that interval. If you execute a query right after such flush then you it will be on disk after about 15 minutes. If you execute a query right *before* such a flush, then it will be on disk after just a few seconds.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, September 2, 2019 10:30 AM
  • but I have tried to run query select * from table and then restart the DB immediately .

    this query also appear in query store.

    Monday, September 2, 2019 3:34 PM
  • Then I guess you get a flush from the controlled shutdown. Did you try to kill the sqlservr.exe process the hard way? NOTE: Should *NOT* be done on a production server!!!

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, September 2, 2019 3:54 PM
  • let me try

    actually I put the query select * from table. ,

    and I can immediately see it in

    sys.query_store_query_text

    Monday, September 2, 2019 4:44 PM
  • Hi sakurai_db,

    Please read the information about query process from  How Query Store Collects Data . Hope it could help you.

    Best regards, 
    Cathy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, September 3, 2019 7:39 AM
  • The reason that you see it immediately in sys.query_store_query_text is that this combines what is on disk and what is in memory.

    Only way to see this clearly is to do an uncontrolled shutdown (kill the process) and see that the data since last flush occurrence isn't on disk. I just did that. Executed the query, I found it in sys.query_store_query_text. I killed SQL Server. When I now look in sys.query_store_query_text, the query is no longer there.

    I.e., it was never written to disk, but sys.query_store_query_text combined what is in memory and what is on disk.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, September 3, 2019 10:04 AM