channeladvisor / qdstoolbox Goto Github PK

Hi,

With the release of the Powershell module SQL Server 22.0.59 They are now using .net SQL Drivers (Microsoft.Data.SQLClient) V5.x. This means that encrypt connection string property is set to true by default.

References:
https://blog.netnerds.net/2023/03/new-defaults-for-sql-server-connections-encryption-trust-certificate/
https://github.com/microsoft/SQLServerPSModule/issues

You will need to change all the powershell scripts to include the -encrypt optional to revert to old behaviour.

Hi,

There is an issue while trying to purge Query store.

Ran below, and the proc is running for hours and it ran for more than 10 or 12 hours and finally I cancelled it.
Please advise if there is anything we can do to fix.

EXECUTE [dbo].[QDSCacheCleanup]
@DatabaseName = 'DB'
,@CleanAdhocStale = 0
,@CleanStale = 1
,@retention = 12
,@MinExecutionCount = 2
,@CleanOrphan = 1
,@CleanInternal = 1
,@CleanStatsOnly = 0

DECLARE @PlanMinerID BIGINT
EXECUTE DBA.[PlanMiner]
@InstanceIdentifier = @@ServerName
,@DatabaseName = 'mydb'
,@PlanID = 31
,@PlanMinerID = @PlanMinerID OUTPUT

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'LogicalOp', table 'DBAQSadmin.DBA.PlanMiner_IndexOperations'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I talked with Erin about this tool after releasing it, and moderated her pre-con session on Query Store during last week's PASS.
That "forced plans not dropped" is a problem with the "default" Query Store cleanup process, and one of the reasons I wrote my own: the QDSCacheCleanup unforces those plans before dropping them.
I haven't implemented "Drop everything but forced plans" yet, but is something I could do: just open an issue in the project and I'll try to get it ready by next week