April 27, 2020 at 2:05 pm
I could not find such script but I am sure it should exist. If I want to ask a DBA of a particular database of a particular server to send me ALTER DATABASE etc.. script that would allow me run it and recreate his precise Query Store settings in my database.
I want to just send DBA the script to run that will produce the QueryStore script for me. Is anyone aware of such a script ? I must be not the first one wanting to do it but cannot find any published scripts to do just this. thanks.
Likes to play Chess
April 27, 2020 at 3:38 pm
That's not something I've ever had to do before, or even wondered how to do if I did. If you use SSMS to script out the database, then the ALTER DATABASE statement to enable Query Store is included in the ensuing script, so I suppose you could use Extended Events to capture what commands are executed in order to generate the script, and include them in your own script.
John
April 27, 2020 at 4:40 pm
From the Microsoft documentation to enable query store on SQL 2016:
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
DATA_FLUSH_INTERVAL_SECONDS = 900,
QUERY_CAPTURE_MODE = AUTO,
MAX_STORAGE_SIZE_MB = 1000,
INTERVAL_LENGTH_MINUTES = 60
);
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 27, 2020 at 4:56 pm
The user can get their QDS properties in SSMS and there is a "script" button at the top.
Grant has a couple books. I think one is on Kindle Unlimited if you have that
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply