seo/sqlinsight-pro
1
0
Fork 0
Code Issues Pull requests Projects Releases 11 Packages Wiki Activity Actions
sqlinsight-pro/SQL Scripts/SQLI-DB-015.sql
Søren Eggert Lundsteen Olsen 2284e7f082 SQL Scripts: one reviewable script per rule 
A DBA should be able to review every statement SQLInsight sends to a
monitored server before granting it access. "SQL Scripts/<rule-id>.sql"
holds, per rule (82 built-in, 16 community), a Danish header (what and
why, how C# judges the data, thresholds with config keys, severity,
exact GRANTs, versions, collector file and member) and the collector's
SQL verbatim, every version variant as its own batch.
"_collectors/<Collector>.sql" holds each collector's queries once.

The only statement that is not strictly read-only is documented in the
scripts: the deadlock collector's session temp table (#system_health).

Inline SQL in the collectors moved to internal constants and Build*Sql
methods (no text changed), so SqlScriptTests can check that the scripts
still contain the SQL the code sends, that every rule has a script, that
every collector query is registered, and that nothing writes.

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

299 lines
16 KiB
Transact-SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

-- ===================================================================================================================
-- SQLI-DB-015: tempdb har for få eller ulige store datafiler
-- ===================================================================================================================
-- Regel-id: SQLI-DB-015
-- Titel: tempdb har for få eller ulige store datafiler
-- Alvor: Medium for for få filer; Low for ulige store filer.
-- Versioner: Alle. Ikke relevant på Azure SQL Managed Instance.
--
-- Hvad tjekkes, og hvorfor:
-- Om tempdb har for få eller ulige store datafiler, hvilket giver PAGELATCH-contention på allokeringssider.
--
-- Vurdering (sker i C# ud fra de indsamlede data, ikke i SQL'en, medmindre andet er nævnt):
-- tempdb's datafiler (type ROWS fra Files2005/Files2000, database_id 2) og cpu_count (ServerCollector). Anbefalet =
-- min(8, logiske CPU'er). Fund 1: antal filer < anbefalet og < 4. Fund 2: mere end én fil, og størrelser eller
-- autogrowth (MB/%) er ikke ens.
--
-- Grænseværdier (konfigurationsnøgle i agentens appsettings.json og standardværdi):
-- Op til 8 filer, én pr. logisk CPU; under 4 filer (fast i koden).
--
-- Rettigheder til agentens login (se db/permissions/grant-least-privilege.sql; [agent] = agentens login):
-- GRANT VIEW SERVER STATE TO [agent]; -- SQL Server 2005-2019
-- GRANT VIEW SERVER PERFORMANCE STATE TO [agent]; -- SQL Server 2022+ (erstatter VIEW SERVER STATE)
-- GRANT VIEW SERVER SECURITY STATE TO [agent]; -- SQL Server 2022+
-- GRANT VIEW ANY DEFINITION TO [agent]; -- metadata; giver IKKE adgang til data
-- GRANT CONNECT ANY DATABASE TO [agent]; -- SQL Server 2014+; giver IKKE adgang til data
-- Før 2014 i stedet, i hver brugerdatabase: CREATE USER [agent] FOR LOGIN [agent];
-- USE msdb; GRANT SELECT ON dbo.backupset TO [agent];
-- SQL Server 2000 har ikke VIEW SERVER STATE; se docs/legacy-versions.md (fx kræver DBCC SQLPERF(WAITSTATS) forhøjede rettigheder).
--
-- Data kommer fra (collector, fil : medlemmer, interval):
-- DatabaseCollector
-- Fil: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs
-- Medlemmer: DatabaseCollector.BuildSql2005(instance, capabilities), DatabaseCollector.BuildSql2000(),
-- DatabaseCollector.Files2005, DatabaseCollector.Files2000, Db.UtcOffsetSql, DatabaseCollector.VlfSql,
-- DatabaseCollector.BuildQueryStoreSql(database)
-- Interval: 15 min som standard (SQLInsight:CollectorIntervals:databases)
-- Delt script: SQL Scripts/_collectors/DatabaseCollector.sql
-- ServerCollector
-- Fil: src/SQLInsight.Collectors/Instance/ServerCollector.cs
-- Medlemmer: ServerCollector.BuildSql(version), Db.StartTimeSql(version), ServerCollector.Sql2000
-- Interval: 15 min som standard (SQLInsight:CollectorIntervals:server)
-- Delt script: SQL Scripts/_collectors/ServerCollector.sql
-- Regelkode: src/SQLInsight.Rules/Databases/DatabaseRules.cs : TempdbFilesRule.Evaluate
--
-- Read-only: Ja: kun SELECT samt xp_msver (læser versionsoplysninger), SET-sessionsindstillinger. Intet ændrer
-- data, indstillinger eller objekter.
-- Kørsel: Kør i SSMS som agentens login (eller et login med samme rettigheder). Hver variant er sin egen batch
-- (GO); en variant for en anden SQL Server-version end din kan fejle - det er forventet.
-- Per-database-forespørgsler er vist for databasen msdb, som findes overalt: ret navnet til den
-- database, du vil se.
-- ===================================================================================================================
-- -------------------------------------------------------------------------------------------------------------------
-- 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
-- -------------------------------------------------------------------------------------------------------------------
-- DatabaseCollector: Databaseindstillinger og -tilstand, backup-historik (msdb), seneste fejlfri CHECKDB, VLF'er,
-- Query Store-tilstand og filer.
-- -------------------------------------------------------------------------------------------------------------------
-- Variant: SQL Server 2016 SP2 og nyere (med LastGoodCheckDbTime og availability groups)
-- Kilde: DatabaseCollector.BuildSql2005(2016 SP2+)
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.BuildSql2005(instance, capabilities)
SELECT d.name, d.database_id, d.state_desc, d.recovery_model_desc, d.compatibility_level, d.page_verify_option_desc,
d.is_auto_close_on, d.is_auto_shrink_on, d.is_read_only, d.is_trustworthy_on, d.create_date,
CASE WHEN EXISTS (SELECT 1 FROM sys.database_mirroring m WHERE m.database_id = d.database_id AND m.mirroring_role = 2)
OR EXISTS (SELECT 1 FROM sys.dm_hadr_database_replica_states rs
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = rs.replica_id
WHERE rs.database_id = d.database_id AND rs.is_local = 1 AND ars.role = 2)
THEN 1 ELSE 0 END,
CASE WHEN d.replica_id IS NOT NULL THEN 1 ELSE 0 END,
CASE WHEN b.last_full >= DATEADD(second, -1, d.create_date) THEN b.last_full END,
CASE WHEN b.last_diff >= DATEADD(second, -1, d.create_date) THEN b.last_diff END,
CASE WHEN b.last_log >= DATEADD(second, -1, d.create_date) THEN b.last_log END,
CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS datetime),
d.is_auto_create_stats_on, d.is_auto_update_stats_on
FROM sys.databases d
LEFT JOIN (
SELECT database_name,
MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full,
MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS last_diff,
MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log,
MIN(backup_finish_date) AS first_backup
FROM msdb.dbo.backupset
GROUP BY database_name
) b ON b.database_name = d.name
WHERE d.name <> N'tempdb'
GO
-- Variant: SQL Server 2012 – 2016 SP1
-- Kilde: DatabaseCollector.BuildSql2005(2012)
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.BuildSql2005(instance, capabilities)
SELECT d.name, d.database_id, d.state_desc, d.recovery_model_desc, d.compatibility_level, d.page_verify_option_desc,
d.is_auto_close_on, d.is_auto_shrink_on, d.is_read_only, d.is_trustworthy_on, d.create_date,
CASE WHEN EXISTS (SELECT 1 FROM sys.database_mirroring m WHERE m.database_id = d.database_id AND m.mirroring_role = 2)
OR EXISTS (SELECT 1 FROM sys.dm_hadr_database_replica_states rs
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = rs.replica_id
WHERE rs.database_id = d.database_id AND rs.is_local = 1 AND ars.role = 2)
THEN 1 ELSE 0 END,
CASE WHEN d.replica_id IS NOT NULL THEN 1 ELSE 0 END,
CASE WHEN b.last_full >= DATEADD(second, -1, d.create_date) THEN b.last_full END,
CASE WHEN b.last_diff >= DATEADD(second, -1, d.create_date) THEN b.last_diff END,
CASE WHEN b.last_log >= DATEADD(second, -1, d.create_date) THEN b.last_log END,
CAST(NULL AS datetime),
d.is_auto_create_stats_on, d.is_auto_update_stats_on
FROM sys.databases d
LEFT JOIN (
SELECT database_name,
MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full,
MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS last_diff,
MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log,
MIN(backup_finish_date) AS first_backup
FROM msdb.dbo.backupset
GROUP BY database_name
) b ON b.database_name = d.name
WHERE d.name <> N'tempdb'
GO
-- Variant: SQL Server 2005 – 2008 R2
-- Kilde: DatabaseCollector.BuildSql2005(2005)
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.BuildSql2005(instance, capabilities)
SELECT d.name, d.database_id, d.state_desc, d.recovery_model_desc, d.compatibility_level, d.page_verify_option_desc,
d.is_auto_close_on, d.is_auto_shrink_on, d.is_read_only, d.is_trustworthy_on, d.create_date,
CASE WHEN EXISTS (SELECT 1 FROM sys.database_mirroring m WHERE m.database_id = d.database_id AND m.mirroring_role = 2)
THEN 1 ELSE 0 END,
0,
CASE WHEN b.last_full >= DATEADD(second, -1, d.create_date) THEN b.last_full END,
CASE WHEN b.last_diff >= DATEADD(second, -1, d.create_date) THEN b.last_diff END,
CASE WHEN b.last_log >= DATEADD(second, -1, d.create_date) THEN b.last_log END,
CAST(NULL AS datetime),
d.is_auto_create_stats_on, d.is_auto_update_stats_on
FROM sys.databases d
LEFT JOIN (
SELECT database_name,
MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full,
MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS last_diff,
MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log,
MIN(backup_finish_date) AS first_backup
FROM msdb.dbo.backupset
GROUP BY database_name
) b ON b.database_name = d.name
WHERE d.name <> N'tempdb'
GO
-- Variant: SQL Server 2000 (legacy ODBC-sti)
-- Kilde: DatabaseCollector.BuildSql2000
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.BuildSql2000()
SELECT d.name, d.dbid,
CAST(DATABASEPROPERTYEX(d.name, 'Status') AS nvarchar(60)),
CAST(DATABASEPROPERTYEX(d.name, 'Recovery') AS nvarchar(60)),
d.cmptlevel,
CASE WHEN DATABASEPROPERTYEX(d.name, 'IsTornPageDetectionEnabled') = 1 THEN N'TORN_PAGE_DETECTION' ELSE N'NONE' END,
CAST(DATABASEPROPERTYEX(d.name, 'IsAutoClose') AS int),
CAST(DATABASEPROPERTYEX(d.name, 'IsAutoShrink') AS int),
CASE WHEN DATABASEPROPERTYEX(d.name, 'Updateability') = 'READ_ONLY' THEN 1 ELSE 0 END,
0,
d.crdate,
0,
0,
CASE WHEN b.last_full >= DATEADD(second, -1, d.crdate) THEN b.last_full END,
CASE WHEN b.last_diff >= DATEADD(second, -1, d.crdate) THEN b.last_diff END,
CASE WHEN b.last_log >= DATEADD(second, -1, d.crdate) THEN b.last_log END,
CAST(NULL AS datetime),
CAST(DATABASEPROPERTYEX(d.name, 'IsAutoCreateStatistics') AS int),
CAST(DATABASEPROPERTYEX(d.name, 'IsAutoUpdateStatistics') AS int)
FROM master.dbo.sysdatabases d
LEFT JOIN (
SELECT database_name,
MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full,
MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS last_diff,
MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log,
MIN(backup_finish_date) AS first_backup
FROM msdb.dbo.backupset
GROUP BY database_name
) b ON b.database_name = d.name
WHERE d.name <> N'tempdb'
GO
-- Variant: SQL Server 2005 og nyere
-- Kilde: DatabaseCollector.Files2005
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.Files2005
SELECT database_id, name, type_desc, physical_name,
CAST(size AS bigint) / 128,
CASE WHEN is_percent_growth = 1 THEN growth ELSE CAST(growth AS bigint) / 128 END,
is_percent_growth,
CASE WHEN max_size = -1 THEN -1 ELSE CAST(max_size AS bigint) / 128 END
FROM sys.master_files
WHERE type_desc IN (N'ROWS', N'LOG')
GO
-- Variant: SQL Server 2000 (legacy ODBC-sti)
-- Kilde: DatabaseCollector.Files2000
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.Files2000
SELECT dbid, name, CASE WHEN groupid = 0 THEN N'LOG' ELSE N'ROWS' END, filename,
CAST(size AS bigint) / 128,
CASE WHEN status & 0x100000 <> 0 THEN growth ELSE CAST(growth AS bigint) / 128 END,
CASE WHEN status & 0x100000 <> 0 THEN 1 ELSE 0 END,
CASE WHEN maxsize = -1 THEN -1 ELSE CAST(maxsize AS bigint) / 128 END
FROM master.dbo.sysaltfiles
GO
-- Variant: Alle versioner: serverens afstand til UTC (msdb-tider er lokal tid)
-- Kilde: Db.UtcOffsetSql
-- Kode: src/SQLInsight.Collectors/Db.cs : Db.UtcOffsetSql
SELECT DATEDIFF(minute, GETUTCDATE(), GETDATE())
GO
-- Variant: SQL Server 2016 SP2 og nyere (sys.dm_db_log_info)
-- Kilde: DatabaseCollector.VlfSql
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.VlfSql
SELECT d.database_id, COUNT(*)
FROM sys.databases d
CROSS APPLY sys.dm_db_log_info(d.database_id) li
WHERE d.state = 0
GROUP BY d.database_id
GO
-- Variant: SQL Server 2016 og nyere: én gang pr. online brugerdatabase (vist for msdb)
-- Kilde: DatabaseCollector.BuildQueryStoreSql
-- Kode: src/SQLInsight.Collectors/Databases/DatabaseCollector.cs : DatabaseCollector.BuildQueryStoreSql(database)
SELECT desired_state_desc, actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb
FROM [msdb].sys.database_query_store_options
GO
-- -------------------------------------------------------------------------------------------------------------------
-- ServerCollector: Starttidspunkt, logiske CPU'er, schedulers, NUMA-noder, fysisk hukommelse og virtualisering.
-- -------------------------------------------------------------------------------------------------------------------
-- Variant: SQL Server 2012 og nyere
-- Kilde: ServerCollector.BuildSql(2012+)
-- Kode: src/SQLInsight.Collectors/Instance/ServerCollector.cs : ServerCollector.BuildSql(version)
SELECT
(SELECT sqlserver_start_time FROM sys.dm_os_sys_info),
cpu_count,
(SELECT COUNT(*) FROM sys.dm_os_schedulers WHERE status = N'VISIBLE ONLINE'),
(SELECT COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE status = N'VISIBLE ONLINE'),
physical_memory_kb / 1024,
virtual_machine_type_desc
FROM sys.dm_os_sys_info
GO
-- Variant: SQL Server 2008 R2 SP1 og senere 2008 R2-builds
-- Kilde: ServerCollector.BuildSql(2008 R2 SP1)
-- Kode: src/SQLInsight.Collectors/Instance/ServerCollector.cs : ServerCollector.BuildSql(version)
SELECT
(SELECT sqlserver_start_time FROM sys.dm_os_sys_info),
cpu_count,
(SELECT COUNT(*) FROM sys.dm_os_schedulers WHERE status = N'VISIBLE ONLINE'),
(SELECT COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE status = N'VISIBLE ONLINE'),
physical_memory_in_bytes / 1048576,
virtual_machine_type_desc
FROM sys.dm_os_sys_info
GO
-- Variant: SQL Server 2008 og 2008 R2 før SP1
-- Kilde: ServerCollector.BuildSql(2008)
-- Kode: src/SQLInsight.Collectors/Instance/ServerCollector.cs : ServerCollector.BuildSql(version)
SELECT
(SELECT sqlserver_start_time FROM sys.dm_os_sys_info),
cpu_count,
(SELECT COUNT(*) FROM sys.dm_os_schedulers WHERE status = N'VISIBLE ONLINE'),
(SELECT COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE status = N'VISIBLE ONLINE'),
physical_memory_in_bytes / 1048576,
CAST(NULL AS nvarchar(60))
FROM sys.dm_os_sys_info
GO
-- Variant: SQL Server 2005
-- Kilde: ServerCollector.BuildSql(2005)
-- Kode: src/SQLInsight.Collectors/Instance/ServerCollector.cs : ServerCollector.BuildSql(version)
SELECT
(SELECT create_date FROM sys.databases WHERE database_id = 2),
cpu_count,
(SELECT COUNT(*) FROM sys.dm_os_schedulers WHERE status = N'VISIBLE ONLINE'),
(SELECT COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE status = N'VISIBLE ONLINE'),
physical_memory_in_bytes / 1048576,
CAST(NULL AS nvarchar(60))
FROM sys.dm_os_sys_info
GO
-- Variant: SQL Server 2000 (legacy ODBC-sti): starttidspunkt
-- Kilde: ServerCollector.StartTimeSql(2000)
-- Kode: src/SQLInsight.Collectors/Db.cs : Db.StartTimeSql(version)
SELECT (SELECT login_time FROM master.dbo.sysprocesses WHERE spid = 1)
GO
-- Variant: SQL Server 2000 (legacy ODBC-sti): CPU og hukommelse
-- Kilde: ServerCollector.Sql2000
-- Kode: src/SQLInsight.Collectors/Instance/ServerCollector.cs : ServerCollector.Sql2000
EXEC master.dbo.xp_msver
GO
Reference in a new issue View git blame Copy permalink