Monday, February 8, 2016

SQL Server 2016 Query Store - export & import?

Looking around for a good way to export and import query store from SQL Server 2016.
 
I'm performing large scale comparisons of the legacy cardinality estimator and the new cardinality estimator in SQL Server 2016.
 
I've captured over 9000 queries in two flavors.  First execution was SQL Server 2016 compatibility mode with trace flag 9481 (to force use of legacy cardinality estimator) & trace flag 4134 (I'll retire as soon as its confirmed the wrong results issue is corrected by default).  The second execution was with no trace flags enabled.
 
 
The database will soon be replaced with another test bed - approximately 1 TB larger.  I'd love to be able to compare behavior against the existing database to behavior on the larger database.
 
If all else fails, I'll set all user index & column stats to norecompute then truncate all user tables.  Then shrink the database and smush it into a single file.  That would preserve all the info I am interested in - and I could compare stats & query store across the smushed database and the new test bed.
 
Although I could export information from each of the query store catalog views, I suspect there might be information in the query store that isn't exposed through the catalog views.  Plus I'd rather not handle over 19,000 XML plans :-)
 
So I'm kinda hoping there is a clever export/import possibility... 
 
Posted by at

4 comments:

  1. UnknownJune 2, 2016 at 4:46 PM

    At present, there is no option to export and/or import Query Store data, but there is a Connect item to vote for: https://connect.microsoft.com/SQLServer/feedback/details/2620017/export-query-store-tables-separately-from-the-database-tables

    ReplyDelete
    Replies
    1. SQL_SasquatchJune 13, 2016 at 9:24 AM

      Thanks, Erin!
      I've heard through the grapevine there isn't much "hidden behind the scenes" so I'm not losing much by sending the system table contents into my own tables in order to port. But that does mean two sets of tables if I transport from one test instance to another.
      I upvoted and commented.

      Delete
    2. UnknownMarch 8, 2019 at 6:01 AM

      This comment has been removed by the author.

      Delete
  2. UnknownMarch 8, 2019 at 11:44 AM


    If you’re coming here _from the future_, this ticket made the leap to the new feedback system:

    https://feedback.azure.com/forums/908035-sql-server/suggestions/32901670-export-query-store-tables-separately-from-the-data

    ReplyDelete

Subscribe to: Post Comments (Atom)