Suggestions for an expensive seek

sqldriver 2014-10-03 15:26:42

This is a prepared and parameterized bit of code that I can't alter, however I can potentially add indexing if it would help.

Code comes from a front end web gui. It recently started taking 2-3 minutes for clients to load this 'view' in their browsers.

Table is 1.6 billion rows and the query only has one filter so clients can view all actions taken on a particular document.

Any ideas are appreciated.

Dan and Ed,

  1. No help with OPTION RECOMPILE or OPTIMIZE FOR UNKNOWN, but I can't change the code outside of experimenting anyway.
  2. I'll try testing the short circuit out. Is that a case of predicate push down? Even if I can't make that change it would be an interesting find.
  3. No partitioning
  4. Yeah, these Audit tables are ridiculous.

Tried using the where filter as a join predicate instead and no change.

/storage/attachments/1031-Plan.pesession
Dan Holmes 2014-10-03 15:36:36
Does performance improve if you add OPTION(RECOMPILE) to the statement?
Edward Norris 2014-10-03 15:36:44
Whew…1.6 Billion taking that long with all index seeks…
I'm sure there will be some clever answers to this one!
Is the table horizontally partitioned in any way?
Have you tried to short circuit the inner first inner join by using
"ON AuditObject.ArtifactID = @ArtifactID" to disconnect it, and perhaps reduce the nested loop iterations?

I am definitely interested in how other people approach this one!

SQLkiwi 2014-10-04 01:42:16
Can you upload a post-execution plan please? The estimates are only useful up to a point. Ideally, the query would be executed directly from Plan Explorer so we can see I/O etc. as well. Thanks. Also, I suspect the poor performance may be down to a residual predicate, so if you can run a test with OPTION (QUERYTRACEON 9130) attached to the query, that would be ideal.
sqldriver 2014-10-04 03:01:46
link text

Hi Paul,

This was mostly remedied with an index on artifact id and on the date column descending. There's a new missing index request, however three of the columns are large text, and this is a very active table. Inserts slowed down by 3-4x when I either modified the new index to accommodate all of the includes, or when I added the newly requested index.

Anyway, attached is the actual, run in SQL Sentry, with the trace flag as you requested. If you see anything else that might be improved, please let me know.

Thanks

/storage/attachments/1032-take2_newindex.pesession
SQLkiwi 2014-10-04 04:45:55
No that seems fine now, thanks.
October 3, 2014
No Comments