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(5) NOT NULL,
site_id CHAR(4) NULL,
source1_id UNIQUEIDENTIFIER NULL,
source2_id UNIQUEIDENTIFIER NULL,
source3_id UNIQUEIDENTIFIER NULL,
source4_id UNIQUEIDENTIFIER NULL,
event_source_type CHAR(2) NOT NULL,
log_id CHAR(4) NOT NULL,
log_msg VARCHAR(4000) NOT NULL,
pre_mod VARCHAR(1000) NULL,
post_mod VARCHAR(1000) NULL,
create_timestamp DATETIME NOT NULL CONSTRAINT DFlog_events_create_timestamp DEFAULT (GETDATE()),
created_by INT NOT NULL,
modify_timestamp DATETIME NOT NULL CONSTRAINT DFlog_events_modify_timestamp DEFAULT (GETDATE()),
modified_by INT NOT NULL,
row_timestamp TIMESTAMP,
group_id INT NULL,
create_timestamp_tz SMALLINT NULL,
modify_timestamp_tz SMALLINT NULL,
CONSTRAINT pk_log_events PRIMARY KEY NONCLUSTERED (log_event_id) WITH (FILLFACTOR = 90) ON MAINSYSTEM_INDEX_1
)
--Simplified Indices:
INDEX inx_log_events1 ON log_events (source1_id, source2_id, source3_id, source4_id)
INDEX inx_log_events3 ON log_events (source1_id, source2_id, event_source_type)
INDEX inx_log_events4 ON log_events (site_id, source2_id, source1_id)
INDEX inx_log_events5 ON log_events (source1_id, event_source_type, site_id, log_event_id)
CLUSTERED INDEX inx_log_events7 ON log_events (create_timestamp, event_source_type)
INDEX inx_log_events8 ON log_events (site_id, source2_id, event_source_type, log_id)
INDEX inx_log_events9 ON log_events (organization_id, site_id, source1_id, source2_id, source3_id, source4_id, event_source_type)
The user_mstr table isn't very big, and user_id field is a PK.
I'm a novice when it comes to indexing, so my questions are:
- Can I could improve performance by creating a new index on the organization_id, source1_id, source2_id, source3_id, and source4_id fields?
- If so, what type? If not, what can I do without changing the query?
- Approximately how much disc space would that consume? Or what other drawbacks would there be to creating this seemingly curtailed query?
- What could explain this sudden drop in performance? It's possible our software vendor made some changes over the weekend, but i can't think of what they'd do that would have this effect.