2022.2 Weird SQL Stuff Happening

Kinetic ERP
1

I haven’t a clue what in the f$%# is going on but we are riding the struggle bus hard after go live.

Biggest issue and I can’t figure out what is going on is if I try to save the company record in Company Maint SQL spirals out of control with this query. Eventually I get a lock timeout error. My BAQ subscribes are ungodly slow as well. I’ve disabled a bunch with a GetList base BPM just to get things functional.

Anyone have any clues?

<?query --
SELECT TOP (1)
    [Extent2].[Company] AS [Company],
    [Extent2].[Name] AS [Name],
    [Extent2].[Address1] AS [Address1],
    [Extent2].[Address2] AS [Address2],
    [Extent2].[Address3] AS [Address3],
    [Extent2].[City] AS [City],
    [Extent2].[State] AS [State],
    [Extent2].[Zip] AS [Zip],
    [Extent2].[Country] AS [Country],
    [Extent2].[PhoneNum] AS [PhoneNum],
    [Extent2].[FaxNum] AS [FaxNum],
    [Extent2].[MfgSys] AS [MfgSys],
    [Extent2].[EMailLinkPort] AS [EMailLinkPort],
    [Extent2].[EmailFromAddr] AS [EmailFromAddr],
    [Extent2].[EmailFromLabel] AS [EmailFromLabel],
    [Extent2].[SMTPServer] AS [SMTPServer],
    [Extent2].[SMTPPort] AS [SMTPPort],
    [Extent2].[AttachDefaultMechanism] AS [AttachDefaultMechanism],
    [Extent2].[MetadataPath] AS [MetadataPath],
    [Extent2].[WinWebURL] AS [WinWebURL],
    [Extent2].[TrackSysActivity] AS [TrackSysActivity],
    [Extent2].[TrackPersonalizationChg] AS [TrackPersonalizationChg],
    [Extent2].[ReportTypePref] AS [ReportTypePref],
    [Extent2].[MobileURL] AS [MobileURL],
    [Extent2].[MobileMetaPath] AS [MobileMetaPath],
    [Extent2].[WorkstationMethod] AS [WorkstationMethod],
    [Extent2].[EntSearchURL] AS [EntSearchURL],
    [Extent2].[SMTPAcct] AS [SMTPAcct],
    [Extent2].[SMPTAcctPW] AS [SMPTAcctPW],
    [Extent2].[SSRSURL] AS [SSRSURL],
    [Extent2].[GlobalEntSearch] AS [GlobalEntSearch],
    [Extent2].[SCServer] AS [SCServer],
    [Extent2].[SCUserID] AS [SCUserID],
    [Extent2].[SCPassword] AS [SCPassword],
    [Extent2].[UBAQWFPackage] AS [UBAQWFPackage],
    [Extent2].[InstallationID] AS [InstallationID],
    [Extent2].[CountryGroupCode] AS [CountryGroupCode],
    [Extent2].[CountryCode] AS [CountryCode],
    [Extent2].[CountryCodeID] AS [CountryCodeID],
    [Extent2].[DefaultLabelsPrinter] AS [DefaultLabelsPrinter],
    [Extent2].[DefaultReportsPrinter] AS [DefaultReportsPrinter],
    [Extent2].[IsSMTPCredential] AS [IsSMTPCredential],
    [Extent2].[HelpURI] AS [HelpURI],
    [Extent2].[SysRevID] AS [SysRevID],
    [Extent1].[ForeignSysRowID] AS [ForeignSysRowID],
    [Extent2].[SSRSBaseURL] AS [SSRSBaseURL],
    [Extent2].[SSRSDatabaseServerName] AS [SSRSDatabaseServerName],
    [Extent2].[SSRSAuthenticationType] AS [SSRSAuthenticationType],
    [Extent2].[SSRSDatabaseUser] AS [SSRSDatabaseUser],
    [Extent2].[SSRSDatabaseUserPassword] AS [SSRSDatabaseUserPassword],
    [Extent2].[SSRSDatabaseName] AS [SSRSDatabaseName],
    [Extent2].[SSRSOverrideDefaults] AS [SSRSOverrideDefaults],
    [Extent2].[SSRSPrintOptions] AS [SSRSPrintOptions],
    [Extent2].[TimeZoneID] AS [TimeZoneID],
    [Extent2].[SMTPEnableSSL] AS [SMTPEnableSSL],
    [Extent2].[DefaultLayoutID] AS [DefaultLayoutID],
    [Extent2].[ODBCUserID] AS [ODBCUserID],
    [Extent2].[ODBCPassword] AS [ODBCPassword],
    [Extent2].[EpicorEducationURL] AS [EpicorEducationURL],
    [Extent2].[EpicorHelpURL] AS [EpicorHelpURL],
    [Extent2].[TenantID] AS [TenantID],
    [Extent2].[FileTransferMode] AS [FileTransferMode],
    [Extent2].[GridsUseBaseCurrencyInfo] AS [GridsUseBaseCurrencyInfo],
    [Extent2].[EDDURL] AS [EDDURL],
    [Extent2].[EdiProcessing] AS [EdiProcessing],
    [Extent2].[ReportLoggingLevel] AS [ReportLoggingLevel],
    [Extent2].[ImportAPIMaxDOP] AS [ImportAPIMaxDOP],
    [Extent2].[TelemetryOptIn] AS [TelemetryOptIn],
    [Extent2].[TelemetryOptOutReason] AS [TelemetryOptOutReason],
    [Extent2].[ImportPurgeInterval] AS [ImportPurgeInterval],
    [Extent2].[ImportMaxFileSize] AS [ImportMaxFileSize],
    [Extent2].[TelemetryKey] AS [TelemetryKey],
    [Extent2].[DefaultHomepageLayoutID] AS [DefaultHomepageLayoutID],
    [Extent2].[IsLive] AS [IsLive],
    [Extent2].[KineticColor] AS [KineticColor],
    [Extent2].[DefaultPaperSize] AS [DefaultPaperSize],
    [Extent2].[NomenclatureID] AS [NomenclatureID],
    [Extent1].[UD_SysRevID] AS [UD_SysRevID],
    [Extent1].[FirebaseToken_c] AS [FirebaseToken_c]
    FROM  [Ice].[SysCompany_UD] AS [Extent1]
    INNER JOIN [Ice].[SysCompany] AS [Extent2] ON [Extent1].[ForeignSysRowID] = [Extent2].[SysRowID]
    WHERE [Extent2].[Company] = @p__linq__0
--?>

image
image1680×665 67.2 KB

2

This script and update to CU18 took care of 100% of the weirdness

-- Expand TempDB Space
USE [tempdb]
GO
DBCC SHRINKFILE (N'templog' , 8)
GO
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 8388608KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 8388608KB , FILEGROWTH = 0)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp3', FILENAME = N'T:\tempdb_mssql_3.ndf' , SIZE = 8388608KB , FILEGROWTH = 0)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp4', FILENAME = N'T:\tempdb_mssql_4.ndf' , SIZE = 8388608KB , FILEGROWTH = 0)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp5', FILENAME = N'T:\tempdb_mssql_5.ndf' , SIZE = 8388608KB , FILEGROWTH = 0)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp6', FILENAME = N'T:\tempdb_mssql_6.ndf' , SIZE = 8388608KB , FILEGROWTH = 0)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp7', FILENAME = N'T:\tempdb_mssql_7.ndf' , SIZE = 8388608KB , FILEGROWTH = 0)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp8', FILENAME = N'T:\tempdb_mssql_8.ndf' , SIZE = 8388608KB , FILEGROWTH = 0)
GO

-- Enable Read Committed Snap
USE [master]
GO
ALTER DATABASE [Production] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
4 Likes
3

I see… Did you find that via the internet or…?

4

Or did you run the PDT for SQL?

5

Ran PDT then built the scripts to save time over plunking through the UI, the great @aidacra noticed our SQL version was grossly out of date.

3 Likes
6

What do you mean build scripts to save time? Save time doing what?

7

Leave it to the great @aidacra to find a solution!

8

Save time from clicking through the interface to add the temp dbs and set the params. We took the system down between shifts I had a 10 minute window to shut down the appservers, detach connections to the database, run those scripts, install a Windows update for SQL, reboot, bring it all online, and test it. Seconds mattered in this case lol

3 Likes
9

Nice find @aidacra - and very odd behavior @jgiese.wci . Glad you found it without too much trouble!

1 Like
10

Why the first statement for tempdb does not have FILEGROWTH?

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 8388608KB )
GO

will it make it different size eventually?

Also I am curios, is it the same DB @josecgomez complained about functions created on the fly?

11

Did the PDT tool help you figure out that you needed to add the temp DBs and set the params?

12

No it is not. This one is his, though I believe he resolved the problem by updated to the latest SQL

2 Likes
13

Because it was already set for file growth. I only leave one available for auto grow of the 8.

It is not this is a new 2022 server running SQL 2019 with a fresh install of 2022.2.4. That’s the current environment.

14

No, I just copied the structure we had adjusted to over the years on our old server. There is a great script out there that Brent Ozar references that will generate your tempdbs “smartly” he says it works, I’ve never tried it. You can really adjust tempdbs whenever just want it to be off-hours because when you adjust SQL can start locking on specific temp files and not distribute nicely. Called hotspotting.

This is a great place to start exploring if you need adjustments https://www.brentozar.com/blitz/tempdb-data-files/

1 Like
16

lol, i was just recalling him said that all tempdb files must be the same size. Does he leave one for autogrowing too?
Upd. No, he set same autogrowth on all

17

So were the temp tables the problem or was it the version setting or was it the windows update or maybe a combo of all?

18

Combo of all. Mainly I believe the issue was the Read Committed Snapshot setting and the SQL version we were on, but in the past I had some issues with long running queries needing a place to go and PDT wants tempdb files 8 or greater so we’ll call that change insurance.

As to why they say 8 my guess is due to this KB Recommendations to reduce allocation contention - SQL Server | Microsoft Learn we have 20 logical so I went with the recommended 8 before and 8 again.

1 Like
19

To quantify the effect of these 3 changes this was yesterday

image
image1029×973 31.6 KB

This is today

image
image1021×945 17.3 KB

20

Hey out of curiosity, what is the neat tool/script you are using to send SQL events to an Outlook calendar like that?

1 Like
21

Thanks for taking the time to elaborate man.