AI SQL Tuner Recommendations

The two dominant wait types consume nearly 100% of recorded wait time:

• PWAIT_DIRECTLOGCONSUMER_GETNEXT (49.88%): Background log consumer thread waiting for new log records. This is normal internal SQL Server activity, not a user workload bottleneck.
• QDS_PERSIST_TASK_MAIN_LOOP_SLEEP (49.68%): Query Data Store background flush thread sleeping between persistence cycles. Normal with Query Store enabled across all databases.

These waits indicate the server is essentially idle from a workload perspective. No performance action is needed for wait statistics.

All database files show excellent I/O latency. Thresholds for concern are typically >20ms for data files and >10ms for log files:

Storage performance is healthy. No I/O tuning is required at this time.

Committed memory is only 526 MB against a max server memory cap of 23,168 MB. This is expected for a developer machine under light/no workload. The max server memory setting of 23,168 MB leaves approximately 9,359 MB for the OS on a 32,527 MB machine, which is a reasonable split.

• No memory pressure is detected; Buffer Pool Lazy Writer is not under stress.
• If this machine is used interactively (Windows 10 Home desktop), consider monitoring OS memory usage under concurrent workloads and adjusting the cap down if needed.
• Recommended: Max 23,168 MB is acceptable. Min server memory = 0 (effective running value 16 MB) is fine for a dev/test instance.

Since CHECKDB has never been run and no maintenance plan is in place, index fragmentation is also unknown. After establishing a backup and integrity baseline, schedule index maintenance on active databases: SQLStorm (highest I/O), WideWorldImporters, tpcc, tpch, and tpch10.

-- Check index fragmentation for a specific database (example: SQLStorm)
USE SQLStorm;
SELECT
    OBJECT_NAME(ips.object_id)        AS table_name,
    i.name                             AS index_name,
    ips.index_type_desc,
    CAST(ips.avg_fragmentation_in_percent AS DECIMAL(5,1)) AS fragmentation_pct,
    ips.page_count
FROM sys.dm_db_index_physical_stats(
        DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i
    ON ips.object_id = i.object_id
    AND ips.index_id  = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
  AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;

General guidelines:

• 10–30% fragmentation: REORGANIZE the index.
• >30% fragmentation: REBUILD the index (use ONLINE = ON where possible — available in Enterprise/Developer Edition).

All other databases (CRM, DigitsSolver, SQLStorm, tpcc, tpch, tpch10) have consistent, sensible Query Store settings:

• State: READ_WRITE ✓
• Compatibility Level: 160 ✓
• Storage utilization: all under 2% ✓
• Cleanup Mode: AUTO ✓
• Capture Mode: AUTO ✓

No changes required for these databases' Query Store configuration.

One unencrypted connection was detected: NT SERVICE\SQLTELEMETRY via Shared Memory transport (local machine only). Per analysis guidelines:

• This is a shared memory transport — data never leaves the local machine kernel and cannot be intercepted over a network.
• The login is NT SERVICE\SQLTELEMETRY (SQL Server CEIP telemetry service) — no user data is exposed.
• Risk assessment: Minimal / Acceptable. No action required for this specific connection.

Only one sysadmin member: RockyPC\k_a_f. This is a Windows-authenticated local user account, which is appropriate for a developer workstation. The number is minimal and follows least-privilege principles for a single-user dev machine.

• No excess sysadmin accounts detected.
• SA account status and SQL logins not detailed in the data — verify SA account is disabled if not needed.

-- Verify SA account is disabled (best practice if not using SQL auth)
SELECT name, is_disabled
FROM sys.sql_logins
WHERE name = 'sa';

-- Disable SA if not needed
-- ALTER LOGIN sa DISABLE;

Integrated Security Only = No — SQL Server is running in Mixed Authentication Mode (both SQL and Windows logins accepted). For a developer machine this is commonly needed, but consider:

• Ensure all SQL logins have strong passwords.
• Review and disable any unused SQL logins.
• If only Windows auth is needed, switch to Windows Authentication Mode only.

-- Audit SQL logins (non-Windows)
SELECT name, is_disabled, create_date, modify_date,
       LOGINPROPERTY(name, 'PasswordLastSetTime') AS pwd_last_set
FROM sys.sql_logins
ORDER BY name;

As workload testing increases (SQLStorm, tpcc, tpch workloads), proactively configure deadlock monitoring via Extended Events to capture future events:

-- Create a lightweight deadlock capture session
CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION (sqlserver.database_name,
            sqlserver.sql_text,
            sqlserver.username)
)
ADD TARGET package0.ring_buffer(SET max_memory = 4096)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);

ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE = START;

System database files (master.mdf, mastlog.ldf, MSDBData.mdf, MSDBLog.ldf, tempdb, model) are located at C:\Data\MSSQL16.MSSQLSERVER\MSSQL\DATA\ which is a non-default path (default would be the SQL Server install directory). This is acceptable if intentionally configured, but ensure this path is included in backup scope and has appropriate access permissions.

tempdb has 8 data files (temp2–temp8 + tempdev), which aligns with the recommended practice of matching logical CPU count (up to 8 files for CPUs >8). Current tempdb I/O is extremely low. No changes required. If heavy tempdb workloads develop from TPC benchmarks, consider enabling memory-optimized tempdb metadata (Section 2).

With all databases at compatibility level 160 (except WideWorldImporters), the following SQL Server 2022 IQP features are available that can benefit TPC workloads and analytical queries:

• Parameter Sensitive Plan (PSP) Optimization: Automatically creates multiple plans for queries with parameter skew (beneficial for tpcc/tpch workloads).
• Degree of Parallelism (DOP) Feedback: Automatically adjusts parallelism for individual queries based on observed performance (beneficial on the 16-core machine).
• Memory Grant Feedback (persistent, via Query Store): Persists memory grant adjustments across restarts.
• Cardinality Estimation Feedback: Corrects CE model errors over time.

These features are automatically active at compatibility level 160 with Query Store enabled. No additional configuration is needed — they will engage as workloads run and Query Store accumulates data.

Database Mail XPs = 0. Enabling Database Mail and configuring SQL Server Agent alerts for critical conditions (severity 16+, severity 17+, specific error numbers) provides proactive notification of issues:

• Backup failure alerts
• CHECKDB failure / corruption detected
• Disk space warnings
• SQL Server error log severity 17–25 events

This is optional for a developer workstation but useful if the machine runs unattended for extended periods.