Example audit report

Server SQL01, collected 27 August at 14:18, Windows authentication.

About this collection

The collection took 106 seconds and all 82 queries completed. No permission was missing, so nothing is absent from this report for want of rights.

One thing to keep in mind throughout. The instance restarted on 15 August at 17:38, 11.9 days ago. Every cumulative counter, waits, per-file I/O, index usage, covers that window and nothing before it. The Query Store on ERPPROD, on the other hand, goes back to 27 July, which gives a full month of query history.

Do you know why the server restarted on 15 August? The error log begins at that startup and says nothing about what preceded it.

Three user databases: ERPPROD (56,320 MB), ERPOLD (19,098 MB), DBADMIN (50 MB). SQL Server 2022 at 16.0.4250.1, which is CU24, Standard Edition 64-bit, virtual machine, 16 logical processors, 64 GB of RAM.

Server configuration

Memory

Max server memory is capped at 59,392 MB, 58 GB out of the machine’s 64. That leaves 6 GB to Windows, and the available physical memory counter had fallen to 1,736 MB at collection time, which is 2.65% of RAM.

Lock Pages in Memory is enabled and 57.4 GB are locked. Locked pages cannot be reclaimed by Windows under pressure. The 6 GB margin is therefore all the operating system has, and it is a little tight.

The cache itself is in good shape: Database Cache Memory is at 47,654 MB and Page Life Expectancy at 14,959 seconds against a calculated target of 4,350. There is no sign of memory pressure on the data side. So this is not urgent, but I would lower the cap by 2 GB to give the system some air.

EXEC sys.sp_configure N'max server memory (MB)', N'57344'
RECONFIGURE WITH OVERRIDE

Is there a backup agent, an antivirus or a monitoring agent running on this machine? I can see a monitoring tool’s queries going through the plan cache, and those processes consume memory outside SQL Server too.

Parallelism

Max degree of parallelism is set to 1. No query can use more than one processor, on a machine that has sixteen.

The machine has no hardware NUMA: hardware_numa_present is false and there is a single memory node. The two nodes SQLOS reports are automatic soft-NUMA, created because one node exceeded eight logical processors. MAXDOP is therefore calculated across the whole machine, and the collector itself suggests 8.

MAXDOP 1 is the right setting for SharePoint and for a few packaged applications that require it. Is that the case for your ERP? If the vendor mandates it under the support contract, leave it and record it in the operations documentation. If not, it is a limit you are imposing on yourself for nothing in return.

What it costs is visible in the Query Store. The heaviest query of the month, a SELECT DISTINCT on ORDERS, consumed 4,516 seconds of CPU in a single execution on 21 August, on one thread. With MAXDOP 8 it could have been spread.

Cost threshold for parallelism is still 5, the engine default, set almost thirty years ago. Both should be raised together.

EXEC sys.sp_configure N'max degree of parallelism', N'8'
EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
RECONFIGURE WITH OVERRIDE

Both options are dynamic; no restart is needed. Since there is no CXPACKET wait at all on this instance today, the wait profile should be reviewed a week after the change.

Plan cache

The cache holds 20,287 plans for 4,959 MB, of which 14,595 single-use plans account for 2,855 MB, 57.6% of the cache. The CACHESTORE_SQLCP clerk is the instance’s second largest memory consumer, behind the buffer pool.

“Optimize for ad hoc workloads” is off, and enabling it is the reflex here. It would achieve nothing. Single-use plans of type Adhoc account for only 89.7 MB. The remaining 2,759 MB are Prepared plans, parameterised statements sent by the application driver, and the option does not touch those.

The lever is on the application side. The driver sends 13,496 distinct prepared statement shapes that are never reused. That looks like literals concatenated into a query text that is otherwise parameterised. More on this in the query section below.

You can still enable the option; it costs nothing and it will cover the 89 MB of ad hoc.

EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
RECONFIGURE WITH OVERRIDE

Backup messages in the error log

The error log holds 645 lines over the last twelve days, of which 288 are successful-backup messages. Almost every other line is noise, and it buries the rest.

No global trace flag is active and none of the three startup parameters contains a -T. Enable Trace Flag 3226, at session scope for immediate effect and as a startup parameter so it survives a restart.

DBCC TRACEON (3226, -1)

Backup compression and checksum

Both defaults are zero.

Backup compression has been available in Standard Edition since 2008 R2; there is no reason to do without it. One caveat: your backups go through a virtual device, so through an external tool, and the ratio measured across ERPPROD’s 61 backups is 0.986. In other words they are not compressed at all today, and changing the server option will change nothing while the external tool is driving. That is where to look.

The checksum, on the other hand, is worth setting.

EXEC sys.sp_configure N'backup checksum default', N'1'
EXEC sys.sp_configure N'backup compression default', N'1'
RECONFIGURE WITH OVERRIDE

remote admin connections at 0 means the dedicated administrator connection is only reachable locally. Not serious, but the day the instance stops responding, somebody has to be able to open a session on the machine itself.

Storage and file layout

tempdb write latency

This is the most serious finding in this collection.

The four tempdb data files sit on volume G:. Their average write latencies, accumulated since the 15 August startup, are 1,694.7, 1,718.7, 1,720.0 and 1,862.0 milliseconds. A write to tempdb takes an average of one and seven tenths of a second.

The usual benchmark for tempdb is 5 to 10 ms. This is over a hundred times above it, averaged across twelve days, which rules out a one-off incident.

Three things confirm the problem is the write path of that volume, and not tempdb or the workload:

• the same files read at 1.5 ms, which is excellent;
• templog, on volume H:, writes at 3.5 ms;
• the error log carries the decisive message, on 21 August at 10:17:50: “SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\TempDB\tempdb.mdf]”. That I/O took 15,231 ms. Four “Long Sync IO” messages on scheduler 5 are recorded the same morning, between 10:10 and 10:34.

I would strongly recommend raising this with the storage or hypervisor team, with these figures. Volume G: is labelled “SysDB” and carries nothing but the four tempdb files, so nothing else is competing for its bandwidth. What is needed is to know what kind of storage sits behind it, whether it shares spindles with anything else, and whether there is a disabled write cache or a saturated queue at the host level.

Until that is resolved, every operation that goes through tempdb pays this price: sorts, temporary tables, joins that spill. The temp-table creation counter is at 1,403 per second.

Free space on G:

The same volume deserves a second remark. It is 69.9 GB and only 17.8 GB remain free.

The four tempdb files were configured at 6,144 MB each, but they occupy 13,312 MB each today, 52 GB in total. They have grown, in 1 GB increments, and their maximum size is unlimited. At the next query that spills, they will keep going until the disk is full.

Set a maximum size, or resize the files to their actual size and monitor the volume.

The rest of the file layout

The separation is correct: E: for user data, F: for transaction logs, G: for tempdb data, H: for its log. Free space margins there are comfortable, 38% to 84%.

ERPPROD’s data file, on E:, writes at 30.4 ms on average and reads at 1.7 ms. Reads are good; writes are above what one expects from a data volume. The point is less acute than tempdb, but it points the same way and is worth raising in the same conversation with the storage team.

Three details, none of them serious. The tempdb files mix G:\TempDB\ and G:\tempDB\ depending on the file. The secondary tempdb files are named tempdb1.mdf, tempdb2.mdf and tempdb3.mdf, where the convention is .ndf for a secondary file. And the system databases, along with DBADMIN, live under D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\, on an engine that is MSSQL16. That is the trace of an in-place upgrade from SQL Server 2016, which the startup parameters confirm. It all works and it is not serious, but the next reinstall is worth starting clean.

Database configuration

Recovery model and backups

All three databases are in SIMPLE recovery.

In SIMPLE, transaction log backup is impossible. There is therefore no restore point between two full backups. If ERPPROD is lost at 8 p.m., you restore the 22:17 state from the previous night and you lose the working day.

The RPO question is yours and it is settled with the business. If losing a day of activity on ERPPROD is acceptable, SIMPLE is the right choice and there is nothing to change. If not, ERPPROD needs to move to FULL with scheduled log backups. DBADMIN already holds Ola Hallengren’s procedures, so dbo.DatabaseBackup is probably available to schedule the cadence without writing anything new.

What I observe, though, is that the question is not posed anywhere in the current configuration. This was already raised at the previous collection and nothing has moved, which is normal in two weeks. Has it been put to the business?

Test restores

The instance’s restore history is empty. Not a single restore recorded, ever, while the backup history goes back a year.

A backup that has never been read back is not yet a backup. There should be at least one restore of ERPPROD onto a test machine, once, to measure the duration and confirm that the external tool really returns a usable set of files. How long would bringing ERPPROD back into service take today? Nobody knows, and that is the number the business will ask you for on the day it happens.

High availability

There is no failover mechanism at all. Always On is disabled, there is no availability group, no mirroring, no log shipping, and the machine is not clustered. A failure of the virtual machine stops the ERP until it is brought back up.

That can be perfectly acceptable if the agreed RTO allows it. Does it?

Compatibility level

All three databases are at compatibility level 130, SQL Server 2016, on an engine that is at 160.

This is the highest return in this report relative to its cost. Every database-scoped configuration tied to 2019 and 2022 is already enabled on all three databases: PARAMETER_SENSITIVE_PLAN_OPTIMIZATION, CE_FEEDBACK, MEMORY_GRANT_FEEDBACK_PERSISTENCE, OPTIMIZED_PLAN_FORCING. All of them are inert while the compatibility level stays where it is. You are paying for a 2022 licence and running a 2016 optimiser.

The change is not trivial, because it changes execution plans. The safe method goes through the Query Store, which is already running on ERPPROD with a month of history:

ALTER DATABASE [ERPPROD] SET COMPATIBILITY_LEVEL = 160

and if a query regresses, force its previous plan from the Query Store while you work out why. This is an operation for a quiet window, with a week of monitoring behind it.

In the same movement, QUERY_OPTIMIZER_HOTFIXES is zero on all three databases, so optimiser fixes shipped since the product’s release do not apply.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON

Query Store

Query Store is enabled on ERPPROD only. It runs READ_WRITE, in AUTO capture mode, and holds 18,223 queries and 33,462 plans over one month. It is what made most of the query analysis below possible.

It occupies 797 MB of the 1,000 allocated. Size-based automatic cleanup is on, so it will not flip to read-only, but it is about to start losing history. Raise the limit to 2 GB if space allows.

Enable it on ERPOLD and DBADMIN as well. The cost is negligible and it provides the visibility that is missing today.

ALTER DATABASE [ERPOLD] SET QUERY_STORE = ON
ALTER DATABASE [ERPOLD] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

DBADMIN has no owner

The DBADMIN database has no owner. The recorded SID no longer resolves to any login on the instance, which means the account that created it is gone. ERPPROD and ERPOLD are owned by sa, which is fine.

The owner matters for advanced security features and for cross-database permissions. An orphaned owner eventually causes problems that take a long time to trace back to their cause.

ALTER AUTHORIZATION ON DATABASE::[DBADMIN] TO [sa]

Collations

ERPPROD and ERPOLD use Latin1_General_CI_AS. DBADMIN and tempdb use SQL_Latin1_General_CP1_CI_AS.

Since temporary tables are created in tempdb with tempdb’s collation, any query comparing a text column of a temporary table with a column from an ERPPROD table raises error 468. You probably do not see any, or you would know, because the application code must declare its columns explicitly or avoid that kind of join. So this is a dormant trap rather than a current problem. Worth noting for the day somebody writes an operations script.

The ERPOLD database

ERPOLD occupies 19 GB and I see no activity in it. Across its 1,082 indexes, not one has a row in the usage statistics since the 15 August startup. The 20,880 reads recorded on its data file are explained by CHECKDB and the backup. Its oldest statistics date from a year ago.

Is this database still in use? If it is an old environment or a database carried over from a previous system, it continues to cost backup time, CHECKDB time and reindexing every night. Taking it offline, or setting it read-only, would remove all of that from the maintenance plan.

Wait analysis

Over the twelve days since the restart, excluding idle waits, the total is 55,479 seconds broken down as follows:

This profile points to no saturated resource, and that is a result in itself. There is no CPU pressure: signal wait time, time spent waiting for a processor to become free, is 6.5% of the total, where the threshold for attention is around 20 to 25%. THREADPOOL totals 450 milliseconds across 489 waits, against 704 configured worker threads, so there is no thread starvation either. There is no CXPACKET wait at all, which is the direct consequence of MAXDOP 1.

Two lines deserve attention.

WRITELOG at the top with a 3.2 ms average is not a storage problem. Volume F: writes at 3.0 ms, which is fine. It is the count that stands out: 6.5 million waits, 8.2 million log flushes and 171.6 GB written to the logs in twelve days, for 50.2 million transactions. The workload consists of a great many very small transactions, and each one pays its round trip to disk. That is an application pattern, which I come back to below.

PREEMPTIVE_OS_AUTHENTICATIONOPS in second place is the clearest signal in this collection: 3.72 million Windows authentications in twelve days, roughly three per second on average. Those are not queries, they are connection openings.

The backup waits in third and fourth place, with a 17.5 ms average on BACKUPIO, say the backup target is slow. The same shows in ASYNC_IO_COMPLETION: 227 waits at 12.5 seconds each.

Lock waits remain modest. LCK_M_X totals 766 waits for 341 seconds, a 445 ms average, which is long per wait but represents 0.6% of the time. On ERPPROD, the index IX_INVOICE_LINES_05 alone carries 1,514 lock escalations and 21.5 seconds of waiting. It is localised on the INVOICE_LINES table and it deserves a look, not an alarm.

Queries

Connections

Here are the most frequently executed queries of the month on ERPPROD, as the Query Store counts them:

The fourth line is the key. select usertype,type,name from systypes is a query that client drivers issue once per connection, to build their type mapping table. It ran 3.1 million times in one month.

Below it, a whole family of session-startup queries clusters around 2,083,900 executions: reading the accounts table, the user profile, the password, the tenant configuration, the client dialogs. The numbers are the same to within a few units, so it is indeed the same sequence, repeated on every open.

That matches exactly the 3.72 million authentications measured on the wait side, and the 1.22 million calls to password policy validation. The application opens and closes connections continuously instead of taking them from a pool.

Each connection costs a Windows authentication, a negotiation, then some fifteen context queries before the first useful statement. The gain is twofold: that traffic leaves the server, and with it a good share of the 13,496 single-use prepared plans occupying 2.7 GB of cache.

This is a matter for the vendor rather than for you. Two questions to put to them: is connection pooling enabled in the connector configuration, and if so, is there anything in the connection string preventing reuse? A database context switch, a particular SET or an impersonation option is enough to make every connection unique in the pool’s eyes.

SARGability

These queries on the ROUTES table come round again and again:

SELECT ROUTES.CROSS_DOCK FROM ROUTES
WHERE (ROUTES.EXTERNAL_REF LIKE '%4471102983'
  AND ROUTES.SHIP_TYPE IN ('A1','B1','A2','B2','C1')
  AND ROUTES.CONSIGNEE_ID NOT IN ('4010','4025','4088'))
  AND (ROUTES.TENANT_ID = 1)

The LIKE begins with a wildcard. No index can be used for a seek, so the whole table is scanned every time. ROUTES holds 3,215,081 rows for 2,042 MB.

The measured cost: one variant with 592 executions consumes 1,836 seconds, 3.1 seconds per execution, almost all of it CPU. Another variant with 416 executions consumes 1,275. Adding up the variants of the same pattern present in the Query Store, the total exceeds 3,300 seconds of CPU for the month, for a query that returns a single column.

Note also that every value searched produces a distinct query, since the number is concatenated into the text instead of being passed as a parameter. That is one of the sources of plan cache bloat.

The fix is on the application side. If EXTERNAL_REF holds a composite identifier of which only the tail is searched, either store that tail in a dedicated indexed column, or store the reversed value and search with LIKE 'xxx%', which is SARGable. See my article on the subject: https://www.pachadata.com/docs/articles/sqlserver/performances/sargabilite/

Large reports

Three isolated executions dominate the month’s consumption:

The first ran for one hour and forty-five minutes on 21 August at 09:39, in the middle of the working day, and read 219 million pages. The third is a SELECT * with no WHERE clause on a 46-million-row table.

Across the fifty most expensive queries, the top eight account for 54% of CPU, and the first alone for 15.8%.

These three look like manual extractions rather than application traffic. Can you confirm they are hand-run queries, and by whom? If they are recurring exports, they belong at night, or in a reporting database. And the SELECT * FROM ORDER_LINES of 12 August deserves a word with its author.

Table structure

This is the most structural finding of the collection, and also the one you have least control over.

In ERPPROD, all 482 tables are heaps. None has a primary key. In ERPOLD it is 455 out of 455. Nor is there a single foreign key or CHECK constraint in either database: the only constraint objects are 2,241 and 1,800 default values. There is not one view, stored procedure, function or trigger either.

In other words the schema is a set of flat tables, referential integrity lives entirely in the application, and all SQL is sent by the client. That is a vendor design choice, most likely inherited from a port off another engine, and you will not change it with a command. But it needs to be understood, because it explains almost everything else.

Forwarded records

When a row in a heap is updated and the new version no longer fits in its page, SQL Server leaves a pointer at the old location and moves the row. That pointer is a forwarded record. Every read through a non-clustered index then follows two hops instead of one, and those hops never resolve on their own.

On ERPPROD, the counter totals 225,687,712 forwarded record reads since 15 August, spread across 18 heaps. dbo.ORDER_LINES alone carries 198,152,342 of them. It holds 46,464,519 rows, of which 1,238,500 are currently forwarded, 2.67%.

These counters read in one direction only. A high figure is reliable; nothing inflates it. A low figure would prove nothing, because they reset to zero without warning. Here it is 225 million in twelve days, so there is no ambiguity.

The immediate remedy is a heap rebuild, which relocates the rows and removes the pointers:

ALTER TABLE dbo.ORDER_LINES REBUILD

Two caveats. In Standard Edition this operation is offline: the table is locked for its whole duration, and this is 15 GB. It also rebuilds every non-clustered index on the table, which lengthens it further. So it needs to be scheduled in a downtime window, and timed on a copy before being run in production.

More importantly, it is not permanent. Without a clustered index, forwarded records will re-form at the pace of updates, and it will have to be done again. So either add this rebuild to the maintenance plan for the large heaps, or put the question to the vendor: does the ERP support creating a clustered index on its main tables? The answer is often no, for support reasons, but it is worth asking, because it is the only solution that holds over time. We can discuss it.

Indexing

A caveat before any figure

The instance restarted 11.9 days ago and index usage statistics reset to zero at that point. The window covers no month-end close. Everything below on unused indexes is therefore a list of candidates to confirm, not a list of deletions. The measurement must be repeated after thirty full days of activity before anything is dropped.

Indexes maintained and never read

Of ERPPROD’s 711 non-clustered indexes, 43 served no read at all while absorbing writes. They weigh 2,828 MB. The main ones:

These are maintained, reindexed every night, occupy the buffer pool, and have not served a single read in twelve days. The first has taken 5.8 million updates for nothing.

Another 498 indexes have no row in the usage statistics at all, which is not the same thing. They have been neither read nor written, so their table may no longer be used at all. They weigh only 124 MB in total, housekeeping rather than performance.

The IX_LEGACY_* prefix on ERPPROD indexes is intriguing. Do these come from a migration off the old database, or from an interface feature no longer in use?

Duplicate indexes

Seven pairs of ERPPROD indexes have exactly the same key columns, and ten further pairs overlap by prefix. On ERPOLD, five and six.

In each pair, one is unique and the other is not. The non-unique one is the one to keep least willingly, since it brings neither a constraint nor extra selectivity. Here too, the vendor’s agreement is needed before touching indexes they created.

Missing indexes

The optimiser has recorded 144 suggestions on ERPPROD, several with an estimated impact of 100%.

The TENANT_ID, EXTERNAL_KEY pattern recurs on four different tables with 100% impact. It is a lookup by external key, most likely an exchange interface, and today it scans tables of several million rows. That one is worth creating.

Do not, however, apply the suggestions as they stand. The engine proposes one index per query shape and never merges two: on PARCELS, two suggestions differ only by an included column list that covers almost the whole table. Consolidate before creating, and create few.

On PARCELS, the sensible version looks like this, to be validated with the vendor:

CREATE NONCLUSTERED INDEX IX_PARCELS_TENANT_EXTKEY
ON dbo.PARCELS (TENANT_ID, EXTERNAL_KEY)

Sizes, data types and compression

Compression

Nothing is compressed. On ERPPROD, 1,192 of 1,193 storage units are at compression NONE, for 52,285 MB reserved and 645 million rows. On ERPOLD it is 1,082 of 1,082 for 1,848 MB. No table is partitioned.

Data compression has been available in Standard Edition since SQL Server 2016 SP1, so you are entitled to it. On tables of this kind, ROW compression commonly yields 20 to 40% and PAGE more, at the cost of some CPU on read. Since CPU is not the constrained resource here and I/O is, the trade is favourable.

The candidates, by size:

Measure before acting. The collection did not run the estimate, because it is an expensive operation that is not performed by default:

EXEC sp_estimate_data_compression_savings 'dbo', 'ORDER_LINES', NULL, NULL, 'PAGE'

Good news in passing: on heaps, compression is applied by the same ALTER TABLE ... REBUILD that removes forwarded records. Both operations fit in a single window.

ALTER TABLE dbo.ORDER_LINES REBUILD WITH (DATA_COMPRESSION = PAGE)

One exception to watch: dbo.ROUTES is the only table already mixing two compression settings, NONE and ROW depending on the storage unit. Somebody started something. Do you know who, and why it stopped?

Archiving

Fifteen ERPPROD tables exceed one million rows, for 51.4 GB in total. All of them carry datetime columns, so the age of the data is measurable.

dbo.ORDER_LINES, with 46.5 million rows and 23.5 GB including indexes, is half the database on its own. dbo.APP_JOB_HISTORY holds 1,531,402 rows and carries a history table’s name. No application purge job exists in the Agent: the only one that purges anything touches msdb alone.

How long do ORDER_LINES rows need to be kept? If a business retention rule exists, purging or partitioning would return both space and backup and CHECKDB time. Start by measuring the actual spread:

SELECT YEAR(CREATED_AT) AS year, COUNT(*) AS rows
FROM dbo.ORDER_LINES WITH (READUNCOMMITTED)
GROUP BY YEAR(CREATED_AT) ORDER BY year

You also have 63 empty tables among ERPPROD’s 200 largest, and 100 among ERPOLD’s 200. None carries a suspicious name, so they are most likely application model tables that are never populated. What are they for?

Data types

Across the 5,480 columns recorded in ERPPROD, there are 566 datetime columns and 449 float columns. No text, ntext or image column, which is already good news, and almost no Unicode: 14 columns only.

The float intrigues me. The Query Store’s query texts show @P1 float parameters on what look like amounts and weights. A float is an approximate type: two successive additions do not necessarily give the same result as a sum, and an equality comparison can fail on values one believes identical. For monetary amounts, decimal is the type. Again, this is the vendor’s schema, but the question is worth asking if there have already been discrepancies of a few cents.

datetime is the old type, with its 3.33-millisecond resolution and its range starting in 1753. datetime2 does better and takes less space. Not serious, a modernisation point for the day the vendor revisits its schema.

Maintenance plans

Three jobs exist in the Agent, all current and without failure: 54 executions in 30 days, zero failures. They are built on Ola Hallengren’s scripts, which is the right choice.

CHECKDB runs weekly on all three databases and found nothing, neither on 16 nor on 23 August. No suspect page is recorded. On that front all is well.

Statistics updates

This is the point to correct in these plans.

The update goes through sp_MSforeachdb and sp_updatestats. That procedure does its job, but it leaves the sampling rate at the default, which the engine derives from table size. On dbo.ORDER_LINES, the result is a sampling rate of 0.58%. The statistics of a 46-million-row table are therefore built on 270,000 randomly drawn rows.

On a table that large and that heavily updated, that is not enough. The histograms miss the real distribution, the optimiser estimates badly, and you end up with the 219-million-logical-read plans seen above.

You already have IndexOptimize in place, and it can update statistics with a chosen rate. Better to give it both jobs than to run sp_updatestats behind it:

EXECUTE dbo.IndexOptimize
  @Databases = 'ALL_DATABASES',
  @Indexes = 'ALL_INDEXES',
  @UpdateStatistics = 'ALL',
  @OnlyModifiedStatistics = 'Y',
  @StatisticsSample = 100,
  @Execute = 'Y', @LockTimeout = 1800, @TimeLimit = 5400

100% sampling across all databases would probably be too long for the available window. Start by measuring the duration on ERPPROD alone, and if that is too much, reserve FULLSCAN for the largest tables and leave the default elsewhere.

There is corroborating evidence in the waits: WAIT_ON_SYNC_STATISTICS_REFRESH totals 720 seconds across 2,636 waits, 273 ms paid inside user queries while the engine recomputes a stale statistic. Switching ERPPROD to asynchronous updates would move that cost off the query path:

ALTER DATABASE [ERPPROD] SET AUTO_UPDATE_STATISTICS_ASYNC ON

Defragmentation

IndexOptimize runs daily with its default thresholds, 5% and 30%, and a 5,400-second time limit. It used 2,701 of them last time. So it is working correctly and without excess: only five non-clustered indexes exceed 30% fragmentation in the measurement, on small volumes.

One reading caveat, because the measurement can mislead. Twenty heaps appear at 99.6% or 99.8% fragmentation. That figure comes from a LIMITED scan, where the fragmentation measure means nothing for a heap: a SAMPLED scan of the same objects returns 0%. So that is not the figure to look at for heaps, the forwarded record count, covered above, is.

The real gap is that IndexOptimize does not process heaps. Since all your tables are heaps, the daily maintenance takes care of the indexes and leaves the tables themselves aside.

Failure notifications

None of the three jobs has any notification configured: no event log, no email, no net send, no pager. And Database Mail XPs is zero, so database mail is not even enabled on the instance.

Today the jobs succeed, so it does not show. The day CHECKDB reports corruption or a backup fails, nobody will learn it from the server.

This is the kind of thing one fixes in half an hour and never regrets. Configure Database Mail, create an operator, and attach it to all three jobs. Alerts should also be created on severities 19 to 25 and on errors 823, 824 and 825, which signal I/O consistency problems. The collection does not report existing alerts, so I cannot say whether there are any. Is there external monitoring watching the Agent, in which case the question takes a different form?

Security

The general picture is sound. Every attack-surface option is closed: xp_cmdshell, OLE Automation procedures, ad hoc distributed queries, CLR, cross-database ownership chaining, external scripts. And clr strict security is 1, which is the right value. The engine and Agent service accounts are domain managed service accounts, distinct from each other.

Three points to look at.

The SQL login reporting_user is a member of the sysadmin server role. It is not a service account; it is a named or application SQL login. You already have two Active Directory groups in sysadmin, CORP\GRP-SQL-Management and CORP\GRP-SQL-Admins-PROD, which should be enough for administrators. What is this account for, and does it really need every right?

The login SVC-INTERFACE-PROD was created without password policy checking: CHECK_POLICY is OFF. Its password dates from March 2022 and nothing guarantees its complexity. None of the five SQL logins has password expiration enabled.

The sa account is enabled, still carries its name, and its password was set in April 2021. Mixed authentication is enabled on the instance. If no application uses sa, disable it. Otherwise, at least change the password and record the date.

ALTER LOGIN [sa] DISABLE

Transparent data encryption is enabled on no database. That is judged on the sensitivity of ERPPROD’s data and on the physical exposure of the volumes, about which the collection says nothing. If the storage sits on an array already encrypted at rest, there is not much to add.

Monitoring

The capture mechanism is in place. The blocked process threshold is 10 seconds, and an Extended Events session “Blocked process” starts with the instance and writes to D:\Extended events\Blocked process.xel. A “Deadlocks” session does the same. Two blocked process reports were recorded in twelve days, on 19 and 26 August, and no deadlock.

So the files exist and nobody reads them. Those are exactly the two occurrences to open in order to find out what is blocking INVOICE_LINES. I can look at them if you send them, or the next collection can carry them along with the options that were not passed this time.

A monitoring agent is already running on the instance: I can see its performance counter queries in the plan cache, executed 376 times in the hour before the collection. Which one is it, and what does it report today? If it already collects system counters, it should be able to alert on tempdb file latency, which is the thing to watch first.

Finally, two traces of failed connections in the ring buffers: an 18456 error in state 8, a wrong password, from 192.0.2.45 on 19 August, and twenty socket drops from 192.0.2.118 between 15 and 16 August. Two occurrences in twelve days is not an attack. But failed logins are being logged, so an alert on error 18456 would be easy to add to the same mechanism.

Still to look at

Seven queries in the corpus did not run in this collection, because their options are not enabled by default. Three of them bear directly on open points above and should be passed next time:

• --include-blocked-process-reports and --include-deadlock-graphs, to read the two blocking reports rather than merely knowing they exist;
• --estimate-compression, to quantify the compression gain on the six large tables instead of estimating it.

Outside the archive, there remain: the application connector’s configuration, for connection pooling; the nature of the storage behind volume G:, with the team that operates it; and the Agent’s alerts, which the collection does not report.

Finally, the index usage measurement should be repeated in thirty days, if the instance does not restart before then. That is the condition for turning the list of 43 never-read indexes into a list of deletions.

Report produced from a sql-auditor collection, github.com/rudi-bruchez/sql-auditor. Rudi Bruchez, SQL Server expert, pachadata.com