this is a waste of time in my opinion - see history behind this request at https://www.sqlservercentral.com/forums/topic/degraded-performance-in-2017-vs-2008-r2-with-inserts

I can run this code in multiple servers, with different specs and...

and the views used? user_Type_mgmt is a view as well - but in this case I don't think it won't matter its content

if you can replace this query and stop...

and corresponding info for old server?

once I have time I'll dig a few scripts to extract a different set of info - but nothing special on the above (other than...

I'm more interested on the server info -  did you run it with the flag I mentioned? it should not have returned any specific database information.

CU you should definitely upgrade...

can you also download and install sp_blitz (https://www.brentozar.com/blitz/) - you only need to install sp_Blitz.sql - this will add a few SP's to the database you run it...

or define variable outside cursor and delete it in each loop - only feasible if the table variable does not have a identity column which needs reseed - see

ah well... files don't wanna be loaded anymore - so putting contents here

sqliosim.cfg.ini

[CONFIG]
ErrorFile=sqliosim.log.xml
;CPUCount=2
;Affinity=0
;IOAffinity=0
;MaxMemoryMB=209
StopOnError=TRUE
TestCycles=1
TestCycleDuration=300
CacheHitRatio=10000
NoBuffering=TRUE
WriteThrough=TRUE
MaxOutstandingIO=0
TargetIODuration=100
AllowIOBursts=TRUE
UseScatterGather=TRUE
ForceReadAhead=FALSE
DeleteFilesAtStartup=TRUE
DeleteFilesAtShutdown=TRUE
StampFiles=FALSE

[File1]
FileName=C:\sqliotest\sqliosim.mdx
InitialSize=500
MaxSize=5000
Increment=500
Shrinkable=TRUE
LogFile=FALSE
Sparse=FALSE

[File2]
FileName=C:\sqliotest\sqliosim.ldx
InitialSize=500
MaxSize=5000
Increment=500
Shrinkable=FALSE
LogFile=TRUE
Sparse=FALSE

sqliosim.seqwrites.cfg.ini

[CONFIG]
ErrorFile=sqliosim.log.xml
;CPUCount=2
;Affinity=0
;IOAffinity=0
;MaxMemoryMB=209
StopOnError=TRUE
TestCycles=1
TestCycleDuration=300
CacheHitRatio=10000
NoBuffering=TRUE
WriteThrough=TRUE
MaxOutstandingIO=0
TargetIODuration=100
AllowIOBursts=TRUE
UseScatterGather=TRUE
ForceReadAhead=FALSE
DeleteFilesAtStartup=TRUE
DeleteFilesAtShutdown=TRUE
StampFiles=FALSE

[RandomUser]
UserCount=0
JumpToNewRegionPercentage=500
MinIOChainLength=50
MaxIOChainLength=100
RandomUserReadWriteRatio=9000
MinLogPerBuffer=64
MaxLogPerBuffer=8192
RollbackChance=100
SleepAfter=5
YieldPercentage=0
CPUSimulation=FALSE
CPUCyclesMin=0
CPUCyclesMax=0

[AuditUser]
UserCount=0
BuffersValidated=64
DelayAfterCycles=2
AuditDelay=200

[ReadAheadUser]
UserCount=0
BuffersRAMin=32
BuffersRAMax=64
DelayAfterCycles=2
RADelay=200

[BulkUpdateUser]
;UserCount=4
BuffersBUMin=600
BuffersBUMax=1000
DelayAfterCycles=2
BUDelay=1

[ShrinkUser]
MinShrinkInterval=120
MaxShrinkInterval=600
MinExtends=1
MaxExtends=20

[File1]
FileName=c:\sqliotest\sqliosim.mdx
InitialSize=500
MaxSize=5000
Increment=500
Shrinkable=FALSE
LogFile=FALSE
Sparse=FALSE

[File2]
FileName=c:\sqliotest\sqliosim.ldx
InitialSize=500
MaxSize=5000
Increment=500
Shrinkable=FALSE
LogFile=TRUE
Sparse=FALSE

sqliosim.seqwrites_64k.cfg.ini

[CONFIG]
ErrorFile=sqliosim.log.xml
;CPUCount=2
;Affinity=0
;IOAffinity=0
;MaxMemoryMB=209
StopOnError=TRUE
TestCycles=1
TestCycleDuration=300
CacheHitRatio=10000
NoBuffering=TRUE
WriteThrough=TRUE
MaxOutstandingIO=0
TargetIODuration=100
AllowIOBursts=TRUE
UseScatterGather=TRUE
ForceReadAhead=FALSE
DeleteFilesAtStartup=TRUE
DeleteFilesAtShutdown=TRUE
StampFiles=FALSE

[RandomUser]
UserCount=0
JumpToNewRegionPercentage=500
MinIOChainLength=50
MaxIOChainLength=100
RandomUserReadWriteRatio=9000
MinLogPerBuffer=64
MaxLogPerBuffer=8192
RollbackChance=100
SleepAfter=5
YieldPercentage=0
CPUSimulation=FALSE
CPUCyclesMin=0
CPUCyclesMax=0

[AuditUser]
UserCount=0
BuffersValidated=64
DelayAfterCycles=2
AuditDelay=200

[ReadAheadUser]
UserCount=0
BuffersRAMin=32
BuffersRAMax=64
DelayAfterCycles=2
RADelay=200

[BulkUpdateUser]
;UserCount=4
BuffersBUMin=64
BuffersBUMax=128
DelayAfterCycles=2
BUDelay=1

[ShrinkUser]
MinShrinkInterval=120
MaxShrinkInterval=600
MinExtends=1
MaxExtends=20

[File1]
FileName=c:\sqliotest\sqliosim.mdx
InitialSize=500
MaxSize=5000
Increment=500
Shrinkable=FALSE
LogFile=FALSE
Sparse=FALSE

[File2]
FileName=c:\sqliotest\sqliosim.ldx
InitialSize=500
MaxSize=5000
Increment=500
Shrinkable=FALSE
LogFile=TRUE
Sparse=FALSE

• This reply was modified 6 months, 3 weeks ago by  frederico_fonseca.

SQLIOSIM is part of the standard SQL installation since 2008 - located on the binn directory (for a sql 2022 it would be C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn - assuming default...

• This reply was modified 6 months, 3 weeks ago by  frederico_fonseca.

I gave you a series of links for IO testing - and I've also gave you a series of questions to ask your VM admin - have these get back...

can you post a deadlock xml so we can look at it.

as well as the code involved in writing-reading that log table

not much you can do here other than rewrite that view (and likely the views contained within it).

likely best thing is to see what really needs to be retrieved and...

if you tried to upload a sqlplan here it won't work - you need to use https://www.brentozar.com/pastetheplan/ and share the link to it here

but one thing you can take for granted - all currently supported versions of the software you mentioned work in all currently supported versions of Windows (and in some *Nix)...

Thanks for the advise.

If you don't mind me asking - why do you advise that? I'm not disagreeing, I'm asking because I'm new to Powershell and interested to learn.

I...

just noticed this was part of a SQL Agent step.

word of advise - NEVER EVER use powershell directly on a step - either code it in C# on a SSIS...