I found the issue, and the restriction isn't at the CPU level.

It turns out the server is memory constrained, with PLE over the index optimization period dropping below 60 seconds...

This issue is resolved. There are internal upgrade changes made to all the user databases when upgrading to SQL 2022 (I would assume other versions as well), but these don't...

I get the stats from the DMV

-- Lots of select logic
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS a
   INNER JOIN sys.master_files AS b
     ...

I've resolved the problem of some commands not returning all elements. I'm now using:

$jsonDBList = $jsonObj.Databases|select ServerName, name, CompatibilityLevel | Format-Table

The error message is certainly misleading, but when you look at the TABULAR result, the reason for the error is clear. There simply isn't any free space to reclaim. You...

While you can't set this at a DB or Server level, it's pretty simple to run code across every table to compress it. The basic command is:

ALTER TABLE TableName REBUILD...

• This reply was modified 3 years, 4 months ago by  Leo.Miller.

One question I have about your script is does it handle offline instances?

At the moment the input for the script is a file with a list of servers, so I...

Well it's not actually possible to install the shared features twice, so this definitely isn't the problem. Also, I've already done all the core testing, at this point I'm testing...

Thanks Ken for pointing me to this article https://docs.microsoft.com/en-us/dynamicsax-2012/developer/optimistic-concurrency-control

Close reading indicates that while you can have Global Optimistic Concurrency Control, it is possible to set Pessimistic Control at a...

A follow on of what I have found. There are a lot of SELECT....with (UPDLOCK) commands. Since the database is already set for Read Committed Snapshot Isolation (to reduce deadlocks,...

No, at least not WRT the tables where I'm seeing blocking.  Most of the clustered indexes are now PARTITIONID, DATAAREAID, TableSpecificID. Typically bigint, nvarchar, bigint.

One issue I have is that...

Thanks for the reply. There's a bit of confusion around this. It turns out the client downloaded the SQL Patch files from WSUS (Windows Server Update service) and this seems...

Thanks for the reference.

Oh, it's not my company, I've been brought in to consult on some other problems, and the use of the Version Store cropped up as...

Thanks for the reference.

Please don't EVER tell me to RTFM, especially given that I mentioned I'd done a bunch of reading. I've been searching for a reference like this for...

Further to this post, I have another post regarding CMEMTHREAD Waits, which is the same client (https://www.sqlservercentral.com/forums/topic/cmemthread-waits-2)

The database is 3.6TB, and their indexes are seriously fragmented, even the clustered...