While I didn't notice performance problems at first, it appears to be that the CPU on the SQL Server is getting pegged when more than 10 or 15 simultaneous inserts are happening on the table. This is a quad proc 3Ghz Xeon, so the fact the CPU is hitting 90%+ while doing 15 inserts a second doesn't make sense to me.
Very quickly the sproc that is being repeatedly called by some middle tier components is taking 30+ seconds to execute, eventually causing timeouts. This sproc is very simple. It does a few quick select statements (that take well under 100ms), and then does an insert into the table in question. That's it.
The only thing I can think of is that the overhead of 3 separate indexed views on the table, each of which contains a significant subset of the total rows in the table (550,000+ rows), is causing SQL Server to get swamped trying to keep the indexes up to date.
Does this seem like a possibility? I'm planning on temporarily removing those indexed views to see how it performs without them, although this is dangerous because it creates the potential for invalid data.
In case anybody is interested, it wasn't the Indexed Views. While these did account for a sizable portion of the increased CPU load (~5%), it wasn't almost entirely due to a really, really bad query that wasn't hitting the proper indexes.
Essentially, I had a query that was resulting in 3 index scans and a bookmark lookup on a table with about 1 million rows. This was happening on every insert. (Whoops!)
I modified some indexes and now the execution plan does two index seeks. This resulted in individual inserts going from taking about 5 seconds to taking about 100ms. Load that would have taken over an hour to process before now takes about 15 seconds.
Just goes to show... it pays to understand query plans.
No comments:
Post a Comment