Skip to content
digitapulia.com

Month-end slowdowns in SQL Server: finding the queries that hurt

What to measure in SQL Server when the ERP grinds to a halt at month-end close, in what order and with which views, before anyone talks about a bigger server.

· 24 min read

A chrome hand tally counter on a dark grey surface, with its four-digit window, the push lever on top, the knurled reset knob and the wire finger loop.
Photo: Marcin Wichary · CC BY 2.0

For three weeks a month the ERP is fine. Then the close begins. Accounting posts the month’s entries, the warehouse pushes out every order that has to leave before the cut-off, sales pull their monthly reports, and overnight the system generates the month’s invoices. Saving an order takes a minute. A report never finishes. Someone in the warehouse starts writing orders on paper again, and someone else types them in the next morning.

The first explanation on the table is usually the server: it has become too small. Sometimes that is true. But buying a bigger server without measuring is an expensive bet. If the cause is a query that reads a whole table, or a process that holds everyone else up, the new server inherits the same cause and the problem comes back with the next close.

SQL Server keeps a detailed account of where its time goes: what it waits for, which session holds up which, which queries cost the most, which indexes it would have liked to have. Most of that account is already on your server, and reading it stops no one. This article goes through it in the order we use, from the general question to the specific one, based on Microsoft’s documentation. At the end there is a list of what you can prepare this week, before the next close.

A word before starting. Almost everything here is reading: the views we query change nothing in the database. The exceptions — switching on the Query Store, extending what it keeps, setting up a record of blocking, keeping a copy of the wait counters — are flagged where they appear. The fixes, on the other hand, do change things, and they are tested first. If a vendor maintains your ERP, talk to them before touching an index or a query: the database belongs to their program, and they may have rules of their own about it.

What changes at month-end

A server that runs well for three weeks and badly for a few days is not broken. It is being given different work. Three things change at once, and each leaves a different trace in SQL Server.

More people, at the same hours

Each operation on its own may be quick, but many of them at once compete for the processor, the memory and the disk. In SQL Server this shows up as waits: queries that are not progressing because they are waiting for a resource.

Processes that only run those days

The accounting close, the month’s invoicing, the month-end reports. They are long, they read a lot and they write a lot. While they run they hold locks on what they change, and anyone who needs the same rows waits for them to finish. In SQL Server this shows up as blocking.

More data than at the start of the month

Orders, invoices and stock movements pile up over the month. A query that reads a whole table is quick when the table is small and slow when it is not. In SQL Server this shows up as expensive queries, with many reads.

The work, then, is to find out which of the three is costing the most time on your server, and in which queries. That takes four questions, asked in order: what is the server waiting for, who is blocking whom, which queries cost the most, and why those queries cost what they cost.

Before you measure: the symptom, the moment, the permission

Measuring without knowing what you are looking for produces a lot of numbers and few answers. Three things come first.

A symptom you can measure

Talk to the people who suffer the slowness. What were they doing when the system hung: saving an order, opening a report, posting an entry? At what time, on which day of the month, on which screen? «Everything is slow at month-end» cannot be measured. «On the last working day, mid-morning, saving an order takes a minute» can.

The right moment, and a normal one

Measure when the problem happens. A measurement taken on an ordinary Tuesday afternoon will say the server is fine, and it will be right: that day it is. So the measurement is prepared before the close, so that the next close is recorded. And a normal day is recorded too, at the same hours, because the useful answer is rarely «this query is expensive»: it is «this query costs far more at the close than on a normal day».

Permission to read, not to query the tables

The server-level views used here need the VIEW SERVER STATE permission on SQL Server 2019 and earlier, and VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. The Query Store lives inside each database, and its views need VIEW DATABASE STATE, or VIEW DATABASE PERFORMANCE STATE from SQL Server 2022. SQL Server 2022 also has a fixed server role for it, ##MS_ServerPerformanceStateReader##, whose members can read every view the server-level permission covers. These are permissions to read the state of the server and of the database; nothing in this article needs permission to read the ERP’s tables.

One honest caveat: these views return the text of the queries, and a query’s text can contain values written into it, such as a customer code in a filter. Whoever holds the permission can see those values. If that matters in your company, decide it before granting the permission.

What each source remembers

Before reading any view, it helps to know how far back it can see, because several of them forget more than you would expect. Get this wrong and you end up comparing the close with nothing.

SOURCEHOW FAR BACK IT SEESsys.dm_os_wait_statsThe server's waits, added upSince the last restartOr since someone cleared themsys.dm_exec_query_statsThe cost of each cached planWhile the plan is cachedGone when the plan is evictedsys.dm_db_missing_index_*Missing index suggestionsSince the last restartAlso lost at failover, offlinesys.dm_db_index_usage_statsHow each index is usedSince the engine startedStarts again at every restartQuery StoreQueries, plans, waits, by hourSurvives a restartKeeps 30 days by default
How far back each source sees. Only the Query Store survives a restart; the views start again from zero, or forget a query when its plan leaves the cache.

The server’s cumulative wait counters, the missing index suggestions and the index usage counters all start again from zero when the database engine restarts; the index usage rows of a database also go when it is detached or shut down. The statistics of cached queries live only as long as the query’s plan stays in the plan cache. The Query Store is different: it is written inside the database, it survives a restart, and by default it keeps each query’s information for 30 days.

That has a practical consequence. Restarting the server «to make it faster» just before the close erases most of what you need to measure. To know from when the views count, the column sqlserver_start_time of sys.dm_os_sys_info gives the engine’s last start.

First question: what is the server waiting for?

A query that takes long is not always working. It may be waiting: for another transaction to release a lock, for a page to arrive from disk, for memory to run in, for the transaction log to be written, for its turn on a processor. SQL Server records the type of every wait, and knowing what the server waits for during the close tells you which direction to look in.

The waits of the close, not of the year

The view sys.dm_os_wait_stats returns every type of wait the server has met, with how many times it was waited for (waiting_tasks_count) and the total time spent waiting, in milliseconds (wait_time_ms). Its figures are cumulative: they add up everything since the last restart, or since someone cleared them with DBCC SQLPERF. And its times cover only waits that have finished: what is stuck right now is not there yet. On a server that has been up for three months, the waits of the close are diluted in three months of normal days.

The way round it is to take two snapshots and subtract. At the start of the close, keep a copy of the counters in a small database of your own, not in the ERP’s — in this example it is called dba:

SELECT SYSDATETIME() AS taken_at, wait_type,
       waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO dba.dbo.waits_at_start
FROM sys.dm_os_wait_stats;

At the end of the close, or at the moment the system is at its worst, read the counters again and subtract the copy:

SELECT w.wait_type,
       w.wait_time_ms - s.wait_time_ms AS wait_ms_during_close,
       w.waiting_tasks_count - s.waiting_tasks_count AS waits_during_close
FROM sys.dm_os_wait_stats AS w
JOIN dba.dbo.waits_at_start AS s ON s.wait_type = w.wait_type
ORDER BY wait_ms_during_close DESC;

What comes out on top is what the server waited for during the close. Do the same on a normal day, into a table with another name, over the same hours, and compare the two lists. If the server restarts in between, the subtraction makes no sense: start again.

The waits of right now

When someone calls to say «it is slow right now», the cumulative view is too broad. The view sys.dm_exec_requests returns each request running at that moment: whether it is waiting, the wait type (wait_type), how long it has been waiting in milliseconds (wait_time) and, if another session is blocking it, that session’s number (blocking_session_id). The view sys.dm_os_waiting_tasks lists the tasks waiting on a resource. Together they are a photograph of the instant.

If you prefer a screen to a query, the Activity Monitor in SQL Server Management Studio shows the processes on the instance and how they affect it, with a resource waits pane. It needs the VIEW SERVER STATE permission, and Microsoft warns that with a refresh interval under 10 seconds, the time its own queries take can affect the server’s performance.

The waits of each query, over time

Since SQL Server 2017 the Query Store also keeps wait statistics per query, over time, with the wait types grouped into categories such as CPU, lock, memory, buffer IO, transaction log IO, parallelism and network IO. It records waits while queries run, not while they compile. That lets you see what the queries of the last working day were waiting for at mid-morning, after the fact, and compare it with the same hours of a normal day. More on the Query Store below.

What each type of wait points to

Every wait type points to a resource, and each resource sends you to a different part of this article.

WAIT TYPETHE QUERY WAITS FORLOOK NEXT ATLCK_M_…A lock held by anotherBlockingPAGEIOLATCH_…A page from the diskReads, diskSOS_SCHEDULER_YIELDIts turn on a CPUCPU per queryRESOURCE_SEMAPHOREMemory to run inMemory grantsWRITELOGThe log on diskCommits, logCXPACKET, CXCONSUMERParallel threadsQuery first
Six wait types and the resource each points to. A wait is not an error: what counts is which one grows on the bad days.

According to Microsoft’s documentation, long PAGEIOLATCH waits might indicate problems with the disk subsystem, and prolonged SOS_SCHEDULER_YIELD waits most frequently point to queries that scan whole indexes or tables and can be optimised. WRITELOG is the wait for the transaction log to be written, caused commonly by commits and checkpoints. Some waits are a normal part of the work: Microsoft says so of CXCONSUMER, the wait of a thread receiving rows from another in a parallel query. CXPACKET is looked at when it is excessive, and Microsoft’s advice is to tune the query first, with an index for example, before touching the server’s parallelism settings. One more is worth knowing in an ERP: ASYNC_NETWORK_IO means the server is waiting for the client application to acknowledge the rows it was sent, so the time is spent in the program or on the network, not in the database. A wait is not an error. Every server always waits for something. What matters is which waits grow on the bad days compared with the normal ones, and in which queries.

Second question: who is blocking whom?

Blocking is normal. Microsoft puts it plainly: blocking is an unavoidable and by-design characteristic of any relational database management system with lock-based concurrency. What hurts is blocking that lasts. When one transaction takes long to finish, everything that needs what it holds waits behind it.

The chain and its head

During a slow close, chains form: one session blocks another, which in turn blocks others. The session that matters is the first one, the one that blocks without being blocked — the head of the chain. Everything below it is only waiting.

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
       DB_NAME(r.database_id) AS database_name, t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

A session number that appears in the second column and never in the first is a head. A blocking_session_id of 0 or NULL means the request is not blocked, or that the blocker cannot be identified; a negative value is a special code, not a session. In SQL Server Management Studio, the standard report «Activity - All Blocking Transactions» shows the transactions at the head of a chain directly.

What is usually at the head

Microsoft’s guide lists the usual cases: a query that simply runs for a long time, a sleeping session with an uncommitted transaction, a client that has not fetched all its result rows, a session rolling back, an orphaned transaction. One of them deserves attention in an ERP. When a query times out or is cancelled, SQL Server ends the query and its batch but does not roll back or commit the transaction by itself: a screen that gave up waiting can leave a transaction open, still holding its locks. The other usual case at month-end is a close process that does all its work inside one transaction. Against lock escalation, when many row locks become a lock on the whole table, the guide’s simplest and safest prevention is to keep transactions short.

Recording the blocking nobody sees

A long block at eleven at night is seen by no one. SQL Server can produce a blocked process report when a lock wait passes a threshold, the blocked process threshold, set in seconds with sp_configure, from 0 to 86,400; 0 turns it off, and by default no report is produced. Microsoft describes the report as best effort. It is collected with an Extended Events session, and the next morning you know who blocked whom, and for how long. Setting the threshold is a change to the server’s configuration: it is done with whoever administers it.

Row versioning, and why it is not a quick fix

Microsoft’s guide to locking and row versioning describes another way. With the database option READ_COMMITTED_SNAPSHOT on, queries at the default READ COMMITTED isolation level read versions of the rows, so reading does not block updates. It can remove a whole class of month-end blocking, between reports and the processes that write. But it is off by default in SQL Server, and writers still block writers. The row versions are kept in tempdb, or in the database itself when accelerated database recovery is on. Switching it on needs the database to itself: only the connection running the ALTER DATABASE may be in it. And it changes what a query sees while others are writing. With an ERP from a vendor, it is the vendor’s decision, not a setting to switch on the night before the close.

Third question: which queries cost the most?

When blocking is not the problem, or once you know what the server is waiting for, the next step is to find the queries that give it the most work.

Two ways to be expensive

A query can be expensive because each run is expensive: a report that reads the whole orders table every time someone opens it. Or because it is cheap and runs thousands of times: a query executed once for every invoice instead of once for all of them. The first shows up when you sort by average cost; the second, when you sort by total cost. You need both lists.

EXPENSIVE EACH TIMECHEAP, THOUSANDS OF TIMESExample: a reportthat reads the wholeorders tableExample: one query perinvoice, instead of onefor all of themSort by average costSort by total costBoth lists are needed. The examples are illustrations.
Two ways to be expensive, and the sort that finds each.

The statistics of cached queries

The view sys.dm_exec_query_stats returns aggregate performance statistics for the query plans in the plan cache: how many times each plan has run since it was compiled (execution_count), the processor time (total_worker_time) and the elapsed time (total_elapsed_time), both reported in microseconds but accurate only to milliseconds, and the logical reads (total_logical_reads). When the ERP writes the values into the text of its queries, the same query can appear as many entries; the column query_hash is there, in Microsoft’s words, to determine the aggregate resource usage of queries that differ only by literal values.

SELECT TOP (20)
       qs.query_hash,
       SUM(qs.execution_count) AS executions,
       SUM(qs.total_worker_time) AS total_cpu_us,
       SUM(qs.total_worker_time) / SUM(qs.execution_count) AS avg_cpu_us,
       SUM(qs.total_logical_reads) AS total_reads,
       MIN(t.text) AS sample_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
GROUP BY qs.query_hash
ORDER BY total_cpu_us DESC;

Order by avg_cpu_us and you get the list by average cost; by total_reads or executions, the queries with the most reads and the most runs. The text shown is that of the whole batch: the view’s statement_start_offset and statement_end_offset give the position of the single statement. With sys.dm_exec_query_plan you see each plan, as XML: which tables it reads, with which indexes and in what order. A plan already evicted from the cache comes back empty.

Two limits matter. The statistics are updated when a query finishes, so a process that is still running is not there yet. And when a plan leaves the cache, its rows disappear from the view; memory pressure evicts plans too. It is a view of the present, not of the month. For the month, there is the Query Store.

The Query Store: the close against a normal day

The views so far tell you what is happening now, or what is in the cache. To know what happened mid-morning on the last working day, and whether it differs from mid-month at the same hour, you need history. That is the Query Store.

What it is and where it keeps its data

The Query Store has existed since SQL Server 2016. It keeps the history of the queries, of their plans and of their runtime statistics, and it keeps it inside the ERP’s own database. Statistics are aggregated in time intervals, 60 minutes by default.

On SQL Server 2016, 2017 and 2019 it is not on by default. From SQL Server 2022 it is, in read-write mode, for new databases. But a database restored from another instance, or upgraded to SQL Server 2022 from an earlier version, keeps the settings it had. An ERP database with years of history behind it may well be in that position. Check it.

Switching it on before the close

If it is off, it has no history, and the history starts the day you switch it on. It is a change to the database, of the kind that is agreed with whoever administers it and, if there is one, with the program’s vendor:

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

According to Microsoft, one day is usually enough for the Query Store to capture a data set that represents the workload, even a very complex one. To have a normal day to compare with as well, switch it on a few days before the close.

How big it gets and how long it remembers

The Query Store has a maximum size: 100 MB by default on SQL Server 2016 and 2017, and 1,000 MB from SQL Server 2019. Size-based cleanup, on by default, starts at 90 % of the limit, removes the least expensive and oldest queries first and stops at about 80 %. If the limit is reached anyway, the Query Store switches to read-only and stops capturing until enough space has been freed. Microsoft warns that it can change its mode silently. The view sys.database_query_store_options tells you its state: if the actual state differs from the one requested, the change was automatic.

The second limit is less visible. By default the Query Store keeps each query’s information for 30 days. That is enough to compare a normal day with the close of the same month. To compare one close with the previous one, about 30 days earlier, the option STALE_QUERY_THRESHOLD_DAYS has to be extended, and the size watched afterwards.

The reports to open

SQL Server Management Studio has built-in Query Store reports. For a slow close, three matter most. «Top Resource Consuming Queries» shows which queries weighed most in the interval you choose. «Query Wait Statistics» shows what they were waiting for. «Regressed Queries» finds those that suddenly run worse than before.

Comparing two periods

The comparison is the useful part. Pick the hours of the close and the same hours of a normal day. The queries at the top of both lists are the usual heavy ones: worth a look, but they do not explain why the close is different. The ones that are at the top only during the close, or that take far longer during the close, are the ones you are looking for.

Fourth question: why does that query hurt?

By now you have a short list of queries. Each one costs what it costs for a reason, and the reason decides the fix. Four reasons come up again and again.

It reads far more than it returns

A query that returns ten rows and reads a million is the classic case. In its plan you will see a scan of a large table or index where a seek would do. Often an index is missing. SQL Server records missing index suggestions in views such as sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats. They are suggestions, not prescriptions, and Microsoft’s own documentation spells out their limits. They are based on estimates made when optimising a single query, before running it. They do not say in which order to put the columns; the view’s own page advises equality columns before inequality columns, and the rest as included columns. Unique and filtered indexes are never suggested. They are collected for at most 600 groups. And they are lost at an instance restart, a failover or a database taken offline, and for one table, when its structure changes — a column added or dropped, an index created — or at any ALTER INDEX on one of its indexes. Each suggestion is reviewed, combined with similar ones where there are any, tested in a test environment and measured before and after.

It changed plan

Sometimes a query is not slow in itself: SQL Server has chosen a worse plan for it than the one it used before. The Query Store keeps the plans of the queries it captures, and with sp_query_store_force_plan, which needs the ALTER permission on the database, you can force a specific plan for a query. If forcing fails, the optimiser works as normal. And because the plan used may not be identical to the forced one, Microsoft notes, performance may vary; in rare cases it is much worse, and the forced plan then has to be removed. It is a low-risk fix, but it is a remedy, not an explanation: it is worth understanding why the plan changed.

It is fine for some values and terrible for others

A query with parameters can run well with some values and badly with others, because a single cached plan is not the best for every possible value, above all when the data is unevenly spread: one customer with ten orders, another with a hundred thousand. From SQL Server 2022, at database compatibility level 160 or higher, there is an optimisation designed for this case, parameter sensitive plan optimisation. If your database runs at a lower compatibility level, it does not have it.

The optimiser’s picture of the data is out of date

The optimiser chooses how to run each query from statistics that describe how the data is spread. With the AUTO_UPDATE_STATISTICS option on, it detects when statistics may be out of date and updates them when a query uses them. Microsoft warns that switching that option off can cause suboptimal query plans and degraded performance. If it is off in your database, ask why before changing it.

When «out of date» starts matters at month-end, because the tables are at their largest. From SQL Server 2016, at database compatibility level 130 or higher, a table of n rows, above 500, has its statistics marked out of date after MIN(500 + 0.20 × n, √(1,000 × n)) changes: for 2 million rows, 44,721. Below level 130 the older threshold applies, 500 + 0.20 × n, which for the same table is 400,500 changes. On a large table, the two thresholds are far apart.

And the indexes nobody uses

The opposite problem is just as real at month-end. An index that helps no query still has a cost: it takes space and adds work to every insert, update and delete — every order, invoice and movement of the close. The view sys.dm_db_index_usage_stats counts, for each index, the seeks (user_seeks), scans (user_scans) and lookups (user_lookups) that queries made, and the updates (user_updates). Microsoft suggests considering dropping indexes that incur maintenance overhead but are not used by queries, or only infrequently. But the counters go back to zero every time the database engine starts, so read them after a period that includes a full close. And an index unused all month may be the one the quarter-end or year-end close needs: before dropping it, ask which processes run only on those dates.

Putting the causes in order

At the end of the measurement you have a list of causes. The job now is to sort them by effect, starting with those that cost the most time, and to separate the fixes by risk.

Low-risk fixes

An index tested in a test environment, a plan that used to work forced again, an unused index dropped after checking that no process needs it. They are applied with your team, measured with the Query Store at the next close, and they can be undone.

Fixes in the program

A query repeated once per row, a process that does all its work in a single transaction, a report that reads more than it shows. These are not fixed in the database but in the program. If the program comes from a vendor, the measurement gives them what they need: which query, what it costs, when it runs and whom it blocks. It turns a complaint into a precise request.

Fixes in the schedule

Sometimes the fix is not technical at all. A heavy report can run at night; the invoicing run can start at another hour. If two processes block each other at the same time every month, moving one of them may be enough.

Server and configuration changes

Memory settings, parallelism settings, the disk the database files sit on: they are measured too, but they are changed with more care, because they affect the whole server. Two are worth reading at once. The default of max server memory is 2,147,483,647 MB, and Microsoft’s recommendation is to start from 75 % of the system memory not consumed by other processes. The default cost threshold for parallelism, 5, is in Microsoft’s words a starting point, not a recommendation. They are prepared, explained and applied at an agreed moment with no one working. And if after all this the server really is too small, the measurement says why, and a new server is bought knowing what it has to have.

Where to start this week

There is no need to wait for the close. These steps prepare the measurement.

Write down the symptom

Ask the people who suffer the slowness: which operation, which day, what time. Write it down. That is what will be measured.

Check whether the Query Store is on

In the ERP’s database, sys.database_query_store_options tells you whether it is on and in which state. If it is not, talk to whoever administers the server and, if the program’s vendor does not object, switch it on now.

Ask for read permission

The permission to read the server’s state, for whoever will measure: VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE from SQL Server 2022, and a user in the ERP’s database for its Query Store. No permission to query the tables.

Keep a normal day

The most expensive queries and the waits of an ordinary day, at the hours when the close is slow. Keep a snapshot of the wait counters too. That is your reference.

Prepare the record of blocking

With whoever administers the server, the blocked process threshold and the event session that collects its reports, so that the next close leaves a record of who blocked whom, even if no one is watching at the time.

How we do it

This is our SQL Server performance check, a fixed-price engagement: the scope and the price are written down before we start, and they do not change afterwards. First we talk to the people who suffer the slowness: which operations become slow, at what times and on which days of the month. Then we measure on your server, at those same moments, which queries take longest and how often they repeat, what the server is left waiting for, which indexes are missing or surplus and how it is configured. We sort the causes by their effect, starting with the ones that cost the most time.

You receive the list of causes, sorted by their effect, and the fixes applied or ready to apply. The low-risk ones we apply with your team; the others we leave ready and explained, for your team to decide when they go in. We measure with the system in use, reading what SQL Server itself records, without stopping anyone and without permission to query the tables.

If you want to see what it would look like in your case, start with a free one-hour video call: tell us what slows down and when, and we will tell you what we would look at first.

The sources we cite

What we do about it: SQL Server performance check

All resources