Suggestions for an expensive seek

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.

Plan.pesession (5.3 kB)
avatar image By sqldriver 31 asked Oct 03, 2014 at 03:26 PM
more ▼
(comments are locked)
avatar image Dan Holmes Oct 03, 2014 at 03:36 PM

Does performance improve if you add OPTION(RECOMPILE) to the statement?

avatar image Edward Norris Oct 03, 2014 at 03:36 PM

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!

avatar image SQLkiwi ♦ Oct 04, 2014 at 01:42 AM

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.

10|10000 characters needed characters left

1 answer: sort voted first

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.


avatar image By sqldriver 31 answered Oct 04, 2014 at 03:01 AM
more ▼
(comments are locked)
avatar image SQLkiwi ♦ Oct 04, 2014 at 04:45 AM

No that seems fine now, thanks.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Oct 03, 2014 at 03:26 PM

Seen: 278 times

Last Updated: Oct 04, 2014 at 04:45 AM