StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to optimize indexes for a busy table?
text
Body
copied!<p>i have this table of comments (little over 1 milion rows) that gets around 10.000 inserts and around 100.000 queries against it every day, and minor deletions and updates. the query that gets the comments causes performance issues that sometimes it locks up entire database and i am getting a lot of timeouts. please help me adjust my indexes and anything else so that it performs better. below i included the info about it, if you need more please ask. i rebuild all indexes daily and run a sql server 2008 web edition on a 2008 server.</p> <p>thank you :)</p> <p><strong>structure:</strong></p> <pre><code>id (int, identity) profile_id (int) owner_id (int) added_date (datetime) comments varchar(4000) logical_delete (datetime) </code></pre> <p><strong>indexes:</strong></p> <pre><code>id (PK, clustered) profile_id (70% fill) owner_id (70% fill) added_date (70% fill) profile_id + logical_delete (70%) </code></pre> <p><strong>query:</strong></p> <pre><code> select c.id, c.owner_id, c.comments, c.is_public, c.added_date, u.first_name, u.last_name, c.profile_id from [profile_comment] c with(nolock) inner join [user] u with(nolock) on u.id = c.owner_id where c.profile_id = @profile_id and c.logical_delete is null order by c.added_date desc </code></pre> <p><strong>execution plan:</strong></p> <pre><code> |--Nested Loops(Inner Join, OUTER REFERENCES:([c].[owner_id], [Expr1005]) WITH ORDERED PREFETCH) |--Sort(ORDER BY:([c].[added_date] DESC)) **[5%]** | |--Nested Loops(Inner Join, OUTER REFERENCES:([c].[id], [Expr1004]) WITH UNORDERED PREFETCH) **[0%]** | |--Index Seek(OBJECT:([DB].[dbo].[profile_comment].[IX_profile_comment_combined1] AS [c]), SEEK:([c].[profile_id]=(1) AND [c].[logical_delete]=NULL) ORDERED FORWARD) **[1%]** | |--Clustered Index Seek(OBJECT:([JakLeci].[dbo].[profile_comment].[PK__profile_comment__primary] AS [c]), SEEK:([c].[id]=[JakLeci].[dbo].[profile_comment].[id] as [c].[id]) LOOKUP ORDERED FORWARD) **[47%]** |--Clustered Index Seek(OBJECT:([DB].[dbo].[user].[PK__user__id] AS [u]), SEEK:([u].[id]=[DB].[dbo].[profile_comment].[owner_id] as [c].[owner_id]) ORDERED FORWARD) **[47%]** </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload