seo/sqlinsight-pro
1
0
Fork 0
Code Issues Pull requests Projects Releases 11 Packages Wiki Activity Actions
sqlinsight-pro/SQL Scripts/SQLI-AX-008.sql
Søren Eggert Lundsteen Olsen be039a6f73 Review 1.4: AX reads put the agent's session settings back 
AxSql.Reset ran SET LOCK_TIMEOUT -1 and READ COMMITTED after every AX read
(and after the AX detection in discovery). The collectors of an instance
share one connection, so every collector after the AX ones waited on locks
without limit instead of the agent's 5 s, and no longer read uncommitted.
Reset now restores the agent's own settings, from one place
(Core.Instances.AgentSession) that SqlConnectionFactory.SessionSettings also
uses. The AX reads themselves are unchanged: READ COMMITTED (or SNAPSHOT)
with a 2 s lock timeout inside sp_executesql, never NOLOCK.

Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
2026-10-02 14:14:24 +02:00

127 lines
12 KiB
Transact-SQL
Raw Permalink Blame History

-- ===================================================================================================================
-- SQLI-AX-008: Kontinuerlig Dynamics AX-nummerserie er ikke ryddet op
-- ===================================================================================================================
-- Regel-id: SQLI-AX-008
-- Titel: Kontinuerlig Dynamics AX-nummerserie er ikke ryddet op
-- Alvor: Low
-- Versioner: SQL Server 2005 og nyere (ikke 2000). Axapta 3.0: verificeret mod en kundes skema (docs/dynamics-ax/
-- schema-ax30.tsv). AX 4.0, AX 2009, AX 2012 og F&O: baseret på Microsofts dokumenterede skema - ikke
-- verificeret mod kundedata.
--
-- Hvad tjekkes, og hvorfor:
-- En kontinuerlig serie må ikke have huller. Numre reserveret af afbrudte sessioner bliver liggende som optagede, til
-- serien ryddes op; uden oprydning opstår huller og fejl, og serien bliver en flaskehals, fordi hver tildeling låser
-- rækken i NUMBERSEQUENCETABLE, til transaktionen er færdig.
--
-- Vurdering (sker i C# ud fra de indsamlede data, ikke i SQL'en, medmindre andet er nævnt):
-- For kontinuerlige (CONTINUOUS = 1), ikke spærrede, ikke manuelle serier, der er taget i brug (NEXTREC > LOWEST):
-- fund, når seneste oprydning er ældre end grænsen eller aldrig er sket (1900-01-01). Seneste oprydning er
-- LATESTCLEANDATETIME (UTC) i AX 2009+, og LATESTCLEANDATE + LATESTCLEANTIME (lokal tid, sekunder efter midnat) i
-- Axapta 3.0. Mangler kolonnen, gives intet fund. Højst 25 fund pr. database.
--
-- Grænseværdier (konfigurationsnøgle i agentens appsettings.json og standardværdi):
-- 7 dage (fast, AxNumberSequenceContinuousRule.MinimumDays), eller 2 x seriens eget CLEANINTERVAL (timer), hvis længere.
--
-- Rettigheder til agentens login (se db/permissions/grant-least-privilege.sql og grant-dynamics-ax-read.sql):
-- Valgfri AX-læseadgang, kun SELECT (db/permissions/grant-dynamics-ax-read.sql; [bruger] = agentens databasebruger):
-- Axapta 3.0: GRANT SELECT ON [bmssa].[NUMBERSEQUENCETABLE] TO [bruger];
-- GRANT SELECT ON [bmssa].[NUMBERSEQUENCEREFERENCE] TO [bruger]; -- valgfri: om serien bruges
-- AX 4.0/2009: de samme tabeller i [dbo]
-- AX 2012/F&O: desuden GRANT SELECT ON [dbo].[NUMBERSEQUENCESCOPE] TO [bruger]; -- valgfri: firma
-- Mangler NUMBERSEQUENCETABLE, er reglen "ikke målbar" med den nøjagtige GRANT for databasen. Uden GRANT ændres intet.
-- Genkendelsen: kun grant-least-privilege.sql (VIEW ANY DEFINITION; CONNECT ANY DATABASE fra 2014, ellers en
-- databasebruger: CREATE USER [agent] FOR LOGIN [agent];).
--
-- Data kommer fra (collector, fil : medlemmer, interval):
-- AxDatabaseDetector (den fælles AX-genkendelse)
-- Fil: src/SQLInsight.Collectors/DynamicsAx/AxDatabaseDetector.cs
-- Medlemmer: AxDatabaseDetector.DatabasesSql, AxDatabaseDetector.BuildDetectionSql(database)
-- Interval: hver time pr. database (hvert 5. minut, mens AX-læseadgangen mangler), delt af alle AX-collectors
-- Delt script: SQL Scripts/_collectors/AxDatabaseDetector.sql
-- AxSequenceCollector
-- Fil: src/SQLInsight.Collectors/DynamicsAx/AxSequenceCollector.cs; SQL: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs
-- Medlemmer: AxSchemaAdapter.NumberSequences(database), AxSchemaAdapter.RecIds(database), AxSql.InDatabase
-- Interval: 1 time som standard (SQLInsight:CollectorIntervals:dynamics-ax-sequences)
-- Delt script: SQL Scripts/_collectors/AxSequenceCollector.sql
-- Regelkode: src/SQLInsight.Rules/DynamicsAx/DynamicsAxRules.cs : AxNumberSequenceContinuousRule
--
-- Read-only: Ja: kun SELECT med TOP og SET-sessionsindstillinger. AX-tabellerne læses i AX-databasen via
-- [db].sys.sp_executesql (sessionen skifter aldrig database) under READ COMMITTED med SET LOCK_TIMEOUT 2000
-- (SNAPSHOT, når databasen tillader det), aldrig NOLOCK; bagefter sættes agentens sessionsindstillinger
-- igen. Intet ændrer data, indstillinger eller objekter. Kun i databaser genkendt som AX og valgt til
-- overvågning.
-- Kørsel: Kør i SSMS som agentens login (eller et login med samme rettigheder). Hver variant er sin egen batch
-- (GO); varianter for andre AX-versioner end din fejler eller giver ingen rækker - det er forventet.
-- AX-forespørgslerne er vist for databasen AXDB: ret [AXDB] til din AX-database.
-- ===================================================================================================================
-- -------------------------------------------------------------------------------------------------------------------
-- Forbindelsen: Sessionsindstillinger, agenten sætter på hver forbindelse
-- -------------------------------------------------------------------------------------------------------------------
-- Variant: Alle versioner: sættes på hver forbindelse, agenten åbner
-- Kilde: SqlConnectionFactory.SessionSettings
-- Kode: src/SQLInsight.Connectivity/SqlConnectionFactory.cs : SqlConnectionFactory.SessionSettings
SET LOCK_TIMEOUT 5000; SET DEADLOCK_PRIORITY LOW; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
-- -------------------------------------------------------------------------------------------------------------------
-- AxDatabaseDetector: Hvilke overvågede databaser er Dynamics AX (kun metadata)
-- -------------------------------------------------------------------------------------------------------------------
-- Variant: Alle versioner fra SQL Server 2005: online brugerdatabaser og deres snapshot-indstillinger
-- Kilde: AxDatabaseDetector.DatabasesSql
-- Kode: src/SQLInsight.Collectors/DynamicsAx/AxDatabaseDetector.cs : AxDatabaseDetector.DatabasesSql
SELECT name, snapshot_isolation_state, is_read_committed_snapshot_on
FROM sys.databases
WHERE database_id > 4 AND state = 0 AND source_database_id IS NULL AND HAS_DBACCESS(name) = 1
ORDER BY name
GO
-- Variant: SQL Server 2005 og nyere: AX-genkendelsen i hver overvåget database (kun metadata; vist for AXDB)
-- Kilde: AxDatabaseDetector.BuildDetectionSql
-- Kode: src/SQLInsight.Collectors/DynamicsAx/AxDatabaseDetector.cs : AxDatabaseDetector.BuildDetectionSql(database) = AxSql.InDatabase(database, AxDetection.MetadataSql)
-- Agenten kører dette i hver overvåget database fra listen ovenfor (ikke *_model). Ret [AXDB] til den database, du vil tjekke.
EXEC [AXDB].sys.sp_executesql N'SET NOCOUNT ON; SET LOCK_TIMEOUT 2000; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT s.name, UPPER(t.name), UPPER(c.name), TYPE_NAME(c.system_type_id), c.precision, c.max_length,
ISNULL(HAS_PERMS_BY_NAME(QUOTENAME(s.name) + N''.'' + QUOTENAME(t.name), N''OBJECT'', N''SELECT''), 0),
(SELECT TOP 1 p.name FROM sys.database_principals p WHERE p.sid = SUSER_SID())
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
WHERE UPPER(t.name) IN (N''SQLDICTIONARY'', N''DATAAREA'', N''SYSCONFIG'', N''BATCH'', N''BATCHJOB'', N''BATCHJOBHISTORY'', N''BATCHGROUP'', N''NUMBERSEQUENCETABLE'', N''NUMBERSEQUENCESCOPE'', N''NUMBERSEQUENCEREFERENCE'', N''SYSTEMSEQUENCES'', N''MODELELEMENT'')
ORDER BY s.name, t.name, c.column_id';
SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
-- -------------------------------------------------------------------------------------------------------------------
-- AxSequenceCollector: Nummerserier i AX-databasen (valgfri AX-læseadgang)
-- -------------------------------------------------------------------------------------------------------------------
-- Variant: Axapta 3.0 (skema bmssa, verificeret mod kundedata): nummerserier
-- Kilde: AxSchemaAdapter.NumberSequences(3.0)
-- Kode: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs : AxSchemaAdapter.NumberSequences(database) via AxSql.InDatabase
EXEC [AXDB].sys.sp_executesql N'SET NOCOUNT ON; SET LOCK_TIMEOUT 2000; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT TOP (5000) CAST(n.[RECID] AS bigint), n.[NUMBERSEQUENCE], n.[DATAAREAID], n.[TXT], CAST(n.[LOWEST] AS bigint), CAST(n.[HIGHEST] AS bigint), CAST(n.[NEXTREC] AS bigint), n.[FORMAT], ISNULL(n.[BLOCKED], 0), ISNULL(n.[CONTINUOUS], 0), ISNULL(n.[CYCLIC], 0), ISNULL(n.[MANUAL], 0), CASE WHEN EXISTS (SELECT 1 FROM [bmssa].[NUMBERSEQUENCEREFERENCE] r WHERE r.[NUMBERSEQUENCE] = n.[NUMBERSEQUENCE] AND r.[DATAAREAID] = n.[DATAAREAID]) THEN 1 ELSE 0 END, n.[LATESTCLEANDATE], n.[LATESTCLEANTIME], CAST(n.[CLEANINTERVAL] AS float), DATEDIFF(minute, GETDATE(), GETUTCDATE()) FROM [bmssa].[NUMBERSEQUENCETABLE] n ORDER BY (CAST(n.[NEXTREC] AS float) - n.[LOWEST]) / NULLIF(CAST(n.[HIGHEST] AS float) - n.[LOWEST], 0) DESC';
SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
-- Variant: AX 4.0 (skema dbo) - ikke verificeret mod kundedata: nummerserier
-- Kilde: AxSchemaAdapter.NumberSequences(4.0)
-- Kode: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs : AxSchemaAdapter.NumberSequences(database) via AxSql.InDatabase
EXEC [AXDB].sys.sp_executesql N'SET NOCOUNT ON; SET LOCK_TIMEOUT 2000; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT TOP (5000) CAST(n.[RECID] AS bigint), n.[NUMBERSEQUENCE], n.[DATAAREAID], n.[TXT], CAST(n.[LOWEST] AS bigint), CAST(n.[HIGHEST] AS bigint), CAST(n.[NEXTREC] AS bigint), n.[FORMAT], ISNULL(n.[BLOCKED], 0), ISNULL(n.[CONTINUOUS], 0), ISNULL(n.[CYCLIC], 0), ISNULL(n.[MANUAL], 0), CASE WHEN EXISTS (SELECT 1 FROM [dbo].[NUMBERSEQUENCEREFERENCE] r WHERE r.[NUMBERSEQUENCE] = n.[NUMBERSEQUENCE] AND r.[DATAAREAID] = n.[DATAAREAID]) THEN 1 ELSE 0 END, n.[LATESTCLEANDATE], n.[LATESTCLEANTIME], CAST(n.[CLEANINTERVAL] AS float), DATEDIFF(minute, GETDATE(), GETUTCDATE()) FROM [dbo].[NUMBERSEQUENCETABLE] n ORDER BY (CAST(n.[NEXTREC] AS float) - n.[LOWEST]) / NULLIF(CAST(n.[HIGHEST] AS float) - n.[LOWEST], 0) DESC';
SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
-- Variant: AX 2009 - baseret på Microsofts dokumenterede skema, ikke verificeret mod kundedata: nummerserier
-- Kilde: AxSchemaAdapter.NumberSequences(2009)
-- Kode: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs : AxSchemaAdapter.NumberSequences(database) via AxSql.InDatabase
EXEC [AXDB].sys.sp_executesql N'SET NOCOUNT ON; SET LOCK_TIMEOUT 2000; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT TOP (5000) CAST(n.[RECID] AS bigint), n.[NUMBERSEQUENCE], n.[DATAAREAID], n.[TXT], CAST(n.[LOWEST] AS bigint), CAST(n.[HIGHEST] AS bigint), CAST(n.[NEXTREC] AS bigint), n.[FORMAT], ISNULL(n.[BLOCKED], 0), ISNULL(n.[CONTINUOUS], 0), ISNULL(n.[CYCLIC], 0), ISNULL(n.[MANUAL], 0), CASE WHEN EXISTS (SELECT 1 FROM [dbo].[NUMBERSEQUENCEREFERENCE] r WHERE r.[NUMBERSEQUENCE] = n.[NUMBERSEQUENCE] AND r.[DATAAREAID] = n.[DATAAREAID]) THEN 1 ELSE 0 END, n.[LATESTCLEANDATETIME], CAST(NULL AS int), CAST(n.[CLEANINTERVAL] AS float), DATEDIFF(minute, GETDATE(), GETUTCDATE()) FROM [dbo].[NUMBERSEQUENCETABLE] n ORDER BY (CAST(n.[NEXTREC] AS float) - n.[LOWEST]) / NULLIF(CAST(n.[HIGHEST] AS float) - n.[LOWEST], 0) DESC';
SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
-- Variant: AX 2012 RTM/R2/R3 - baseret på Microsofts dokumenterede skema, ikke verificeret mod kundedata: nummerserier
-- Kilde: AxSchemaAdapter.NumberSequences(2012)
-- Kode: src/SQLInsight.Core/DynamicsAx/AxSchemaAdapter.cs : AxSchemaAdapter.NumberSequences(database) via AxSql.InDatabase
EXEC [AXDB].sys.sp_executesql N'SET NOCOUNT ON; SET LOCK_TIMEOUT 2000; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT TOP (5000) CAST(n.[RECID] AS bigint), n.[NUMBERSEQUENCE], NULLIF(sc.[DATAAREA], N''''), n.[TXT], CAST(n.[LOWEST] AS bigint), CAST(n.[HIGHEST] AS bigint), CAST(n.[NEXTREC] AS bigint), n.[FORMAT], ISNULL(n.[BLOCKED], 0), ISNULL(n.[CONTINUOUS], 0), ISNULL(n.[CYCLIC], 0), ISNULL(n.[MANUAL], 0), CASE WHEN EXISTS (SELECT 1 FROM [dbo].[NUMBERSEQUENCEREFERENCE] r WHERE r.[NUMBERSEQUENCEID] = n.[RECID]) THEN 1 ELSE 0 END, n.[LATESTCLEANDATETIME], CAST(NULL AS int), CAST(n.[CLEANINTERVAL] AS float), DATEDIFF(minute, GETDATE(), GETUTCDATE()) FROM [dbo].[NUMBERSEQUENCETABLE] n LEFT JOIN [dbo].[NUMBERSEQUENCESCOPE] sc ON sc.[RECID] = n.[NUMBERSEQUENCESCOPE] ORDER BY (CAST(n.[NEXTREC] AS float) - n.[LOWEST]) / NULLIF(CAST(n.[HIGHEST] AS float) - n.[LOWEST], 0) DESC';
SET LOCK_TIMEOUT 5000; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
Reference in a new issue View git blame Copy permalink