Sudden poor SELECT performance on a large table with existing indexes

My company uses a software suite in which the only thing I can truly modify is the database. We've always had performance issues (mostly hardware problems that we hope to resolve soon), but lately it's been particularly bad in a specific area - taking 20-120 seconds to load when it should be less than 5. I shouldered some users, ran a trace on their (and my) machines, and this appears to be the offending query:

SELECT s.create_timestamp, s.create_timestamp, s.create_timestamp_tz, s.row_timestamp, log_msg, pre_mod, post_mod, u.first_name, u.mi, u.last_name, log_id
FROM log_events s (NOLOCK), user_mstr u (NOLOCK)
WHERE s.organization_id = '00001'
    AND source1_id = @account_id --Place holder for a client account unique id
    AND source2_id IS NULL
    AND source3_id IS NULL
    AND source4_id IS NULL
    AND s.created_by = u.user_id

I know NOLOCK is bad, but I have no control over that.

It only lags on the 1st time per new source1_id - every execution after is faster - and it appears to get worse with the greater the row count. I ran the query manually using a source1_id that hadn't been ran yet with SET STATISTICS IO/TIME ON, which returned a small-medium number of rows (490), here's the results:

Table 'user_mstr'. Scan count 0, logical reads 6948, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'log_events'. Scan count 1, logical reads 13939, physical reads 1221, read-ahead reads 34, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 46 ms,  elapsed time = 18751 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

The log_events table is big (31,847,167 rows) and is used as a change/audit log for significant changes throughout our DB. Here's a create statement for the table and simplified indices:

```
CREATE TABLE log_events (
log_event_id UNIQUEIDENTIFIER NOT NULL,
organization_id CHAR

The CPU time is tiny compared to the amount of actual time.

The second time the query runs, it's fast - presumably once the data is in RAM (being one of the few things which benefits from the second run).

Sounds to me like the problem isn't SQL, but the disk. Notice the PAGEIOLATCH waits increasing while your query runs. Have a look at what's happening on that front. Talk to your SAN guy. Find out if there's other disk activity going on. See if a RAID 5 or 6 disk is being rebuilt.

StackExchange Database Administrators Q#102091, answer score: 6