Degraded Performance in 2017 vs 2008 R2 with inserts

Question

Degraded Performance in 2017 vs 2008 R2 with inserts

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

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

MichaelT Old Hand Points: 386 More actions · Answer 1

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 6 months, 2 weeks ago by MichaelT.
This reply was modified 6 months, 2 weeks ago by MichaelT.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

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.

MichaelT Old Hand Points: 386 More actions · Answer 3

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?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

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)

MichaelT Old Hand Points: 386 More actions · Answer 5

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 6 months, 2 weeks ago by MichaelT.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

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)

MichaelT Old Hand Points: 386 More actions · Answer 7

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?

This server might be just sitting around idle, or someone may have cleared wait stats recently.
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
79552.00MB free on C drive
169346.00MB free on E drive
Logical processors: 4. Physical memory: 24GB.
Node: 0 State: ONLINE Online schedulers: 4 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 30
VM-BASE-SQL2008
Jun 28 2023 2:05PM
Version: 10.50.1617.0. Patch Level: RTM. Edition: Standard Edition (64-bit). AlwaysOn Enabled: 0. AlwaysOn Mgr Status: 0

MichaelT Old Hand Points: 386 More actions · Answer 8

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 6 months, 2 weeks ago by MichaelT.
This reply was modified 6 months, 2 weeks ago by MichaelT.
This reply was modified 6 months, 2 weeks ago by MichaelT.

MichaelT Old Hand Points: 386 More actions · Answer 9

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'?>

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

</ENTRY>

<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>

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

</ENTRY>

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

</ENTRY>

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

</ENTRY>

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

</ENTRY>

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

</ENTRY>

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

</ENTRY>

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

</ENTRY>

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

</ENTRY>

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

</ENTRY>

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

</ENTRY>

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

</ENTRY>

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

</ENTRY>

<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>

<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>