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 on

and then...

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 https://www.grapefruitmoon.net/table-variables-identity-columns-and-reseeding/

but...

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 2 years, 1 months 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 2 years, 1 months 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...

change process so that you use staging tables (not tempdb!!!) for each task - and you switch in/out of these and it is also where you do your inserts (so...

it is possible to load a DLL that is NOT in the GAC - have a look at the following

https://cultivatingsoftware.wordpress.com/2018/08/21/ssis-load-dll-gac/

https://blog.matrixpost.net/load-an-custom-assembly-in-sql-server-integration-services-ssis-script-task-that-is-not-or-cannot-be-stored-in-the-gac/

There is an app that was sending DML requests from a SQL2016 to SQL2008, and we saw lots of issues and it could have been driver related where record...

instead of trying now to follow a different approach why don't you tell us, very detailed, what you are trying to accomplish with having some software inserting those sql statements...