Post reply

Degraded Performance in 2017 vs 2008 R2 with inserts

  • May 7, 2024 at 5:50 pm

    #4419483

    Thanks Federico, I'll give it a try.

    I'm running a query that should peg the CPU but it only uses 25% (1 of the 4 CPUs) on both servers and it doesn't even peg one CPU at 100%, it just distributes load across all CPUs showing 25%+ for CPU usage until it's done.

    https://stackoverflow.com/questions/24810905/trying-to-create-an-sql-query-that-will-max-all-cpus-to-100

    Are SSMS query windows limited to a single CPU?  I'm hoping to see all 4 CPUs pegged near 100% but I can't even do that.

    SELECT SUM(CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id) + CONVERT(BIGINT, o4.object_id))

    FROM sys.objects o1

    CROSS JOIN sys.objects o2

    CROSS JOIN sys.objects o3

    CROSS JOIN sys.objects o4

    OOps,

    Should have read the reply which explains that the author issue and created a parallel query:

     

    USE master

    SELECT MyInt = CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id)

    INTO #temp

    FROM sys.objects o1

    JOIN sys.objects o2 ON o1.object_id < o2.object_id

    JOIN sys.objects o3 ON o1.object_id < o3.object_id

    SELECT SUM(CONVERT(BIGINT, o1.MyInt) + CONVERT(BIGINT, o2.MyInt))

    FROM #temp o1

    JOIN #temp o2 ON o1.MyInt < o2.MyInt

    • This reply was modified 7 months, 3 weeks ago by  MichaelT.
    • This reply was modified 7 months, 3 weeks ago by  MichaelT.

  • May 7, 2024 at 6:37 pm

    #4419491

    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 run  (as sysadmin)

    exec sp_Blitz @CheckServerInfo=1

    from the output there is lots of info you can filter down - like server name, backup info, monitoring , servername, account names and so on - you should be able to figure out which ones are of interest - those on server info are nearly all important.

    run in both old and new and give us the output.

     

  • May 7, 2024 at 9:03 pm

    #4419518

    Thank you,  I ran blitz and also installed sp_whoisactive (nifty tool).

    Here are the main performance related ones:

    • Recovery Interval Not Optimal -The database [xxx] has a target recovery interval of 0, which is a legacy default, and should be changed to 60.
    • cost threshold for parallelism - Set to 5, its default value. Changing this sp_configure setting may reduce CXPACKET waits.
    • Query Store Disabled (for all databases) - The new SQL Server 2016 Query Store feature has not been enabled on this database.
    • User-Created Statistics In Place - [xxx] has 2 user-created statistics. This indicates that someone is being a rocket scientist with the stats, and might actually be slowing things down, especially during stats updates. 
    • Missing Features - SQL 2017 is being used but not Cumulative Update 3. We'd recommend patching to take advantage of increased analytics when running BlitzCache.
    • Indexes Disabled - The index [xxx].[dbo].[xxx].[IX_IncidentTidalTrajectory] is disabled. This index is not actually helping performance and should either be enabled or removed.  

    I have GDR 14.0.2052.1 - do I need a CU as well?  Does the Query Store affect performance - it's like a Sql server flight recorder.

    I looked at the Table with the disabled Index and they were all enabled.  I'm not sure how to even find the disabled index.

    I use Ola Hallengren's plans for maintenance.

    Do any of these look important?

     

     

  • May 7, 2024 at 9:24 pm

    #4419521

    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 but not sure if that would be the issue here - I'm more inclined to VM misconfiguration or SQL Server being core limited (by edition) or by setup (someone may have disabled/changed core affinity on instance)

  • May 8, 2024 at 12:56 pm

    #4419735

    Yes, I ran it with the CheckServerInfo flag and here are all the entries for server info.  In my query, I was able to peg all 4 CPUs @100% on both 2008 and 2017.

    • 3 databases, 38.69 GB total file size
    • The default trace holds 157 hours of data between May 1 2024 4:46AM and May 7 2024 5:14PM. The default trace files are located in: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log
    • 42.05 GB free on C drive out of 99.51 GB total (57.74% used)
    • 171.48 GB free on D drive (DATA) out of 200.00 GB total (14.26% used)
    • 108.00 GB free on E drive (LOG) out of 120.00 GB total (10.00% used)
    • 166.21 GB free on F drive
    • Logical processors: 4. Physical memory: 64GB.
    • Node: 0 State: ONLINE Online schedulers: 4 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 95
    • The service account has the Perform Volume Maintenance Tasks permission.
    • You're running Windows Server 2016 Standard, version 10.0
    • Your server has 2.10GHz CPUs, and is in high performance power mode

      Dec 21 2023 6:39PM

    • VMSQLPROD2016
    • Service: SQL Server (MSSQLSERVER) runs under service account NT Service\MSSQLSERVER. Last startup time: May 6 2024 5:08PM. Startup type: Automatic, currently Running.
    • Service: SQL Server Agent (MSSQLSERVER) runs under service account NT Service\SQLSERVERAGENT. Last startup time: not shown.. Startup type: Automatic, currently Running.

      May 6 2024 5:08PM

    • Version: 14.0.2052.1. Patch Level: RTM. Edition: Standard Edition (64-bit). Availability Groups Enabled: 0. Availability Groups Manager Status: 1
    • Type: (HYPERVISOR)

    How long should it take to insert 100,000 rows in a table with PK identity, a text field, and a date field?  Without any transactions from SSMS, I'd expect it to be very quick.  We see 8 seconds in 2008, 21 seconds in 2017, and 75-85 seconds on 2 SQL Server instances on AWS.

    I've gone through the SQL Server options and they are almost identical - not much has been added to the options since 2008R2:-)  I was hoping for a switch that says "Make everything run 10x faster" that's off by default 🙂

     

     

    • This reply was modified 7 months, 3 weeks ago by  MichaelT.

  • May 8, 2024 at 1:27 pm

    #4419764

    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 this being a low spec server which may be enough for the workload)

  • May 8, 2024 at 1:33 pm

    #4419766

    Here's the old server info using the deprecated Sql_Blitz commands.  Do you know how much quicker SQL 2017 is supposed to be over SQL 2008?

    1. This server might be just sitting around idle, or someone may have cleared wait stats recently.
    2. The default trace holds 7555 hours of data between Jun 28 2023 2:05PM and May 8 2024 9:30AM. The default trace files arelocated in: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log
    3. 79552.00MB free on C drive
    4. 169346.00MB free on E drive
    5. Logical processors: 4. Physical memory: 24GB.
    6. Node: 0 State: ONLINE Online schedulers: 4 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 30
    7. VM-BASE-SQL2008

      Jun 28 2023 2:05PM

    8. Version: 10.50.1617.0. Patch Level: RTM. Edition: Standard Edition (64-bit). AlwaysOn Enabled: 0. AlwaysOn Mgr Status: 0

     

     

  • May 8, 2024 at 6:48 pm

    #4419857

    I installed HammerDB and I found a video of a VMWare machine with the same CPU processor (6130) - unbelievable luck.  The only difference is that the server has 8 virtual CPUs while ours has 4.

    https://youtu.be/9bjjcqEhc60?si=36lXfAs4umO3yV1g&t=233

    It's in Spanish so I couldn't understand it but he's getting 600,000 TPS.  I set up the test the exact same way he did.  Same ODBC driver over TCP.

    As you can see from the logs below, ours tops at 14,000TPS - it's also extremely inconsistent.  We have half the CPUs so I guess 250,000-300,000 should be expected.

    0 MSSQLServer tpm @ Wed May 08 14:13:06 EDT 2024

    276 MSSQLServer tpm @ Wed May 08 14:13:19 EDT 2024

    498 MSSQLServer tpm @ Wed May 08 14:13:35 EDT 2024

    3858 MSSQLServer tpm @ Wed May 08 14:13:45 EDT 2024

    11838 MSSQLServer tpm @ Wed May 08 14:13:56 EDT 2024

    1962 MSSQLServer tpm @ Wed May 08 14:14:07 EDT 2024

    2580 MSSQLServer tpm @ Wed May 08 14:14:17 EDT 2024

    3234 MSSQLServer tpm @ Wed May 08 14:14:28 EDT 2024

    3450 MSSQLServer tpm @ Wed May 08 14:14:39 EDT 2024

    12276 MSSQLServer tpm @ Wed May 08 14:14:49 EDT 2024

    3018 MSSQLServer tpm @ Wed May 08 14:14:59 EDT 2024

    7410 MSSQLServer tpm @ Wed May 08 14:15:11 EDT 2024

    7518 MSSQLServer tpm @ Wed May 08 14:15:21 EDT 2024

    14094 MSSQLServer tpm @ Wed May 08 14:15:32 EDT 2024

    12180 MSSQLServer tpm @ Wed May 08 14:15:42 EDT 2024

    3720 MSSQLServer tpm @ Wed May 08 14:15:52 EDT 2024

    4392 MSSQLServer tpm @ Wed May 08 14:16:02 EDT 2024

    14202 MSSQLServer tpm @ Wed May 08 14:16:13 EDT 2024

    4242 MSSQLServer tpm @ Wed May 08 14:16:24 EDT 2024

    3120 MSSQLServer tpm @ Wed May 08 14:16:35 EDT 2024

    13344 MSSQLServer tpm @ Wed May 08 14:16:45 EDT 2024

    2232 MSSQLServer tpm @ Wed May 08 14:16:56 EDT 2024

     

    • This reply was modified 7 months, 3 weeks ago by  MichaelT.
    • This reply was modified 7 months, 3 weeks ago by  MichaelT.
    • This reply was modified 7 months, 3 weeks ago by  MichaelT.

  • May 14, 2024 at 7:56 pm

    #4421291

    I've created the ini files you provided.  I hit ok after loading the file and then start but nothing happens after the last entry

    Created buffer pool. Size=18300 MB, buffers 2342400, locked pages disabled.

    I check task manager and SQLIOSim goes down to 0% while SQL Server is also idle.  Here's the XML file prior to Stopping the simulation.

    Any idea why it won't run?

     

    <?xml version='1.0'?>

    <?xml-stylesheet type='text/xsl' href='ErrorLog.xslt'?>

    <ERRORLOG>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\simulator_main.cpp' Line='299' Func='Simulator_Main' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Starting Microsoft SQL Server(c) Simulator Stress Test Version 14.0.2052.1</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='223' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Config file C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\sqliosim.seqwrites.cfg.ini, error log sqliosim.log.xml</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='233' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>CPUCount = 4, Affinity = 0, IOAffinity = 0, MaxMemory = 18375 MB, StopOnError = Yes, TestCycles = 1, TestCycleDuration = 300 s, CacheHitRatio = 100.00%</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='240' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>NoBuffering = Yes, WriteThrough = Yes, UseScatterGather = Yes, ForceReadAhead = No, MaxOutstandingIO = 0, TargetIODuration = 100 ms</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='245' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Always validate read buffers = Yes</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='248' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Validate files on restart = No</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='251' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>AllowIOBursts = Yes, DeleteFilesAtStartup = Yes, DeleteFilesAtShutdown = Yes, StampFiles = No</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='257' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>RandomUser: UserCount = 0, JumpToNewRegionPercentage = 5.00%, MinIOChainLength = 50, MaxIOChainLength = 100, RandomUserReadWriteRatio = 90.00%</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='261' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>RandomUser: MinLogPerBuffer = 64, MaxLogPerBuffer = 8192, RollbackChance = 1.00%, SleepAfter = 5 ms, YieldPercentage = 0.00%</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='268' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>AuditUser: UserCount = 0, BuffersValidated = 64, DelayAfterCycles = 2, AuditDelay = 200 ms</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='274' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>LogAuditUser: UserCount = 1, AuditDelay = 5000 ms, ReadSize = 8 MB</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='280' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>ReadAheadUser: UserCount = 0, BuffersRAMin = 32, BuffersRAMax = 64, DelayAfterCycles = 2, RADelay = 200 ms</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='286' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>BulkUpdateUser: UserCount = 8, BuffersBUMin = 600, BuffersBUMax = 1000, DelayAfterCycles = 2, BUDelay = 1 ms</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='292' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>ShrinkUser: MinShrinkInterval = 120 s, MaxShrinkInterval = 600 s, MinExtends = 1, MaxExtends = 20</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='301' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>File #0 D:\SQL Data\sqliosim.mdx: InitialSize = 500 MB, MaxSize = 5000 MB, Increment = 500 MB, LogFile = No, Shrinkable = No, Sparse = No</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\logicalconfig.cpp' Line='301' Func='LOGICALCONFIG::PrintConfigurationToLog' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>File #1 E:\SQL Log Files\sqliosim.ldx: InitialSize = 500 MB, MaxSize = 5000 MB, Increment = 500 MB, LogFile = Yes, Shrinkable = No, Sparse = No</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3372' User='CPU Idle User' File='sql\ntdbms\storeng\util\sqliosim\logicalcpu.cpp' Line='295' Func='CLogicalCPU::UserEntryPoint' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Executing user entry point.</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3524' User='CPU Idle User' File='sql\ntdbms\storeng\util\sqliosim\logicalcpu.cpp' Line='295' Func='CLogicalCPU::UserEntryPoint' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Executing user entry point.</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='5132' User='CPU Idle User' File='sql\ntdbms\storeng\util\sqliosim\logicalcpu.cpp' Line='295' Func='CLogicalCPU::UserEntryPoint' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Executing user entry point.</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='5360' User='CPU Idle User' File='sql\ntdbms\storeng\util\sqliosim\logicalcpu.cpp' Line='295' Func='CLogicalCPU::UserEntryPoint' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Executing user entry point.</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='6620' User='Core Initialization' File='sql\ntdbms\storeng\util\sqliosim\logicalcpu.cpp' Line='295' Func='CLogicalCPU::UserEntryPoint' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Executing user entry point.</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\logicalcpu.cpp' Line='295' Func='CLogicalCPU::UserEntryPoint' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Executing user entry point.</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='7100' User='CreateDB' File='sql\ntdbms\storeng\util\sqliosim\logicalcpu.cpp' Line='295' Func='CLogicalCPU::UserEntryPoint' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Executing user entry point.</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='2744' User='CreateFileStream' File='sql\ntdbms\storeng\util\sqliosim\logicalcpu.cpp' Line='295' Func='CLogicalCPU::UserEntryPoint' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Executing user entry point.</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='2744' User='CreateFileStream' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='1356' Func='CFileManager::CreateFileW' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Creating file #0 (D:\SQL Data\sqliosim.mdx)</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='5244' User='CreateFileStream' File='sql\ntdbms\storeng\util\sqliosim\logicalcpu.cpp' Line='295' Func='CLogicalCPU::UserEntryPoint' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Executing user entry point.</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='2744' User='CreateFileStream' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='1128' Func='CLogicalFile::ExpandCollapse' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Expanding file D:\SQL Data\sqliosim.mdx from 0MB to 500MB</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='5244' User='CreateFileStream' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='1356' Func='CFileManager::CreateFileW' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Creating file #1 (E:\SQL Log Files\sqliosim.ldx)</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='2744' User='CreateFileStream' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='1242' Func='CLogicalFile::ExpandCollapse' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Expanding file D:\SQL Data\sqliosim.mdx attempt COMPLETE</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='5244' User='CreateFileStream' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='1128' Func='CLogicalFile::ExpandCollapse' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Expanding file E:\SQL Log Files\sqliosim.ldx from 0MB to 500MB</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='5244' User='CreateFileStream' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='1242' Func='CLogicalFile::ExpandCollapse' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Expanding file E:\SQL Log Files\sqliosim.ldx attempt COMPLETE</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='6212' User='System' File='sql\ntdbms\storeng\util\sqliosim\sqliosimview.cpp' Line='266' Func='CSQLIOSimView::OnWndMsg' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Starting test cycle</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:41:59' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\buffer.cpp' Line='1185' Func='CBufferPool::hrCreateBufferPool' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Created buffer pool. Size=18375 MB, buffers 2352000, locked pages disabled.</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\simulator_main.cpp' Line='449' Func='Simulator_Main' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Stopping simulation</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='230' Func='CLogicalFile::~CLogicalFile' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Closing file D:\SQL Data\sqliosim.mdx</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='537' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>********** Final Summary for file D:\SQL Data\sqliosim.mdx **********</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='541' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>File Attributes: Compression = No, Encryption = No, Sparse = No</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='549' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 100, Number of times IO throttled = 0, IO request blocks = 1</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='560' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Reads = 0, Scatter Reads = 0, Writes = 0, Gather Writes = 0, Total IO Time (ms) = 0</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='582' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>DRIVE LEVEL: Sector size = 512, Cylinders = 26108, Media type = 12, Sectors per track = 63, Tracks per Cylinders = 255</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='596' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>DRIVE LEVEL: Read cache enabled = No, Write cache enabled = No</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='607' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>DRIVE LEVEL: Read count = 0, Read time = 0, Write count = 12, Write time = 1, Idle time = 255056, Bytes read = 0, Bytes written = 77824, Split IO Count = 0, Storage number = 3, Storage manager name = VOLMGR </EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='230' Func='CLogicalFile::~CLogicalFile' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Closing file E:\SQL Log Files\sqliosim.ldx</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='537' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>********** Final Summary for file E:\SQL Log Files\sqliosim.ldx **********</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='541' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>File Attributes: Compression = No, Encryption = No, Sparse = No</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='549' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 100, Number of times IO throttled = 0, IO request blocks = 1</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='560' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Reads = 0, Scatter Reads = 0, Writes = 0, Gather Writes = 0, Total IO Time (ms) = 0</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='582' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>DRIVE LEVEL: Sector size = 512, Cylinders = 15665, Media type = 12, Sectors per track = 63, Tracks per Cylinders = 255</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='596' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>DRIVE LEVEL: Read cache enabled = No, Write cache enabled = No</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\fileio.cpp' Line='607' Func='CLogicalFile::OutputSummary' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>DRIVE LEVEL: Read count = 0, Read time = 0, Write count = 12, Write time = 1, Idle time = 255057, Bytes read = 0, Bytes written = 73728, Split IO Count = 0, Storage number = 4, Storage manager name = VOLMGR </EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\buffer.cpp' Line='1206' Func='CBufferPool::DestroyBufferPool' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Cleaning up buffer pool</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='4384' User='Display Monitor' File='sql\ntdbms\storeng\util\sqliosim\buffer.cpp' Line='1210' Func='CBufferPool::DestroyBufferPool' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Buffer Pool: validated buffers 0, pages 0, discarded buffers 0</EXTENDED_DESCRIPTION>

    </ENTRY>

    <ENTRY TYPE='INFO' TIME='15:46:14' DATE='05/14/24' TID='3288' User='System' File='sql\ntdbms\storeng\util\sqliosim\simulator_main.cpp' Line='472' Func='Simulator_Main' HRESULT='' SYSTEXT=''>

    <EXTENDED_DESCRIPTION>Simulator Stress Test Attempt Complete</EXTENDED_DESCRIPTION>

    </ENTRY>

    </ERRORLOG>

  • May 14, 2024 at 8:48 pm

    #4421305

    did you run the cmd file that I supplied with the 3 commands on it? your reply does not indicate you did it as I asked.

    or maybe you did run it from windows explorer instead of the command line on the folder where you copied both config and cmd files - and just confirmed that the cmd file was "missing" the hardcoded path to the config so would not work as expected

    either run it on the command line or change it so that line 2 and 3 have the full path to the .ini files

    -cfg <path_is_missing_here>sqliosim.....

     

    and open the command prompt as administrator

     

  • May 15, 2024 at 12:46 am

    #4421335

    Thank you for your excellent directions - I should have read it more closely:-)  I've run them on the 2017 box.  Should I run it on the 2008 box as well?

  • May 15, 2024 at 7:02 am

    #4421389

    MichaelT wrote:

    Thank you for your excellent directions - I should have read it more closely:-)  I've run them on the 2017 box.  Should I run it on the 2008 box as well?

    yes - the main point here is to compare both boxes

  • May 15, 2024 at 7:07 am

    #4421391

    Here are the 2008 logs

    Attachments:
    You must be logged in to view attached files.

  • May 15, 2024 at 7:08 am

    #4421395

    Here are the 2017 logs

    Attachments:
    You must be logged in to view attached files.

  • May 15, 2024 at 4:35 pm

    #4421518

    MichaelT wrote:

    Thank you,  I ran blitz and also installed sp_whoisactive (nifty tool).

    I have GDR 14.0.2052.1 - do I need a CU as well?  Does the Query Store affect performance - it's like a Sql server flight recorder.

    from another reply

    Version: 14.0.2052.1. Patch Level: RTM. Edition: Standard Edition (64-bit). Availability Groups Enabled: 0. Availability Groups Manager Status: 1

    well.. yes - GDR only contains security fixes, not functional/performance fixes.

    and on your case you are still on the RTM patch meaning you installed only GDR patches(or just the latest one)

    do install latest CU (CU31) and see how it goes.

    you also mention in one of the posts that you have query store enabled - I would temporary disable it just to see if it is impacting.

     

    regarding the logs for sqliosim - thanks for them - looking at results there isn't anything issue visible on the server and I/O looks to be a bit faster - although this does not mean that the VM is correctly configured - you never got back to us with feedback from VM team regarding the questions I asked before.

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply