Sql-server – SQL Server – Very high disk activity after running query

The problem is that the query has to retrieve 13.7 billion rows from FIELDDATA in aggregate. This is caused by two significant issues with your query. One will be easy to solve, one will be harder.

The easy problem is with the index. As one commented suggested, you need to add a nonclustered index on FIELDDATA(ID, FIELDID). The clustered index scan is pulling 5764 rows each time it's called, then filtering down to the one row it needs to return. The index should allow it to pull only one row each time it's called, reducing the total to around 2.3 million.

The larger, more difficult problem is that you're using subqueries to return column data in the SELECT clause. SQL Server processes the SELECT clause after most of the other clauses in the query. In this case, the FROM (JOIN) clause would be processed, then the WHERE clause would filter certain rows from those returned by JOIN. The SELECT clause is then applied to each of the rows returned by WHERE. In this case, that's 395,939 rows. Each subquery in the SELECT clause is run once for each row - since you have subqueries on 6 columns, you are querying FIELDDATA 2.3 million times each time your query is run.

To reduce that, you need to get those subqueries out of the SELECT clause and into something that can be joined in your FROM clause. If you're using SQL Server 2005 or later, my best suggestion would be to create a common table expression (or subquery) with a PIVOT clause to bring all of the SVALUE values for each ID into one row. If you're using an earlier version, your best bet may be to extract the FIELDDATA data into a temporary table prior to this query, again with one row per ID that contains all of the necessary SVALUE values.

Given all the detail disclosed…

1. From the comments for what you’ve tried, etc.

   • confirmed no blocking
   • no issues with SELECT statements from VIEWS
   • updated stats
   • rebuilt indexes
   • executed proc with runtime WITH RECOMPILE
   • no other parts of the query being an issue when you tested (other than 2. below)
   • no issues with server level resources such as disk IO, CPU, memory, etc.

2. you specifically narrowing this down to just the one statement in the SP with the logic of:

   SELECT DISTINCT CustomerZipCode, CustomerCountry INTO #temp FROM [dbo].SAP_ONE_SAP
   

3. you confirming that you’ve taken a look at the query plan and see no issues at this level

So for your question. . .

Any ideas why that might be the case? Nothing has changed with the code in the sproc and everything else seems normal. The load on the server seems to be fine as well.

Due to the fact that you have a configuration of AlwaysOn Availability Groups in an Availability Mode of Synchronous-commit mode, I can only assume this is the ROOT cause of the issue (see notes and resource link below).

There could be latency issues with committed transactions in this Availability Mode configuration due to the primary replica waiting for acknowledgement from the secondary replica that it's hardened its transaction logs before it commits its transaction on the primary.

So, not knowing all the business and infrastructure detail on your side, you may want to consider or perhaps test changing the Availability Mode to Asynchronous-commit mode since it commits its transactions without waiting for acknowledgment from the secondary replica that it has hardened its transaction logs.

You may want to check and confirm if there are issues with transaction log hardening on the secondary replica server when this occurs. If you're going over a slower WAN or MAN link perhaps, confirm no issues at network level hops, or any general server issues, etc.

If there is an issue found at one of these other levels, then fixing that should fix your original issue I would think since the primary could acknowledge quicker that the secondary replica hardened its logs and then it'd commit its transaction.

Synchronous-commit mode

An availability replica that uses this availability mode is known as a synchronous-commit replica. Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.

Asynchronous-commit mode

An availability replica that uses this availability mode is known as an asynchronous-commit replica. Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.

RESOURCE: https://msdn.microsoft.com/en-us/library/ff877884(v=sql.110).aspx