Alright, back in the office today, so I'm attaching two query plans here.

The first one is for a query like so:

SELECT Name FROM VoxcoSystem.dbo.tblObjects

WHERE ParentId = 137402

It pulls all workers that are working on a given project; it runs fairly frequently, in order to determine whether a user's authorized to be on a given project. The estimates on this one are actually fairly spot-on, but that's probably because the histogram for ParentId is actually very fleshed-out and usable, at least in this case.

This next query... Not so much.

SELECT Name FROM VoxcoSystem.dbo.tblObjects

WHERE ParentId = 195150

This will determine what projects are in a given directory; here, ParentId's histogram isn't quite so useful, because the AVG_RANGE_ROWS for the segment this ParentId is in adds up to 14.4, the number of the estimated rows; however, the actual number of rows is 113.

I could provide some more examples if needed; basically, though, the estimates are all over the place, because the number of objects per parent can vary wildly, and, as Jeff noted, there's orphans and gaps all over the place, which further muddles the issue. In almost every case, the estimates are just going to worsen the problem rather than help, at least from my amateurish guesses :-P.

Jeff, there's definitely maintenance being done routinely on the tables. It's not a vendor suggestion, and, in fact, we have to keep it a secret from them; they only tolerate their own maintenance solutions being on the server, which they charge a hefty price for :w00t:. Thankfully, the sorta-kinda-DBA before me ignored their demands and just made some maintenance plans himself. I've since replaced the maintenance plans with Ola Hallengren's awesome maintenance routines instead, so all the better!

But yep, we update statistics nightly, and rebuild indexes on the weekends when the business is closed. Fragmentation, at least on this table, is fairly low, since it doesn't get too many inserts per week. There are some other tables that get pretty fragmented, though, since they experience thousands of inserts per day; however, the rate doesn't get much higher than 40% before the rebuilds hit. The histograms on those are absolutely horrid, though, since they're literally just a table with four INT columns with about 1.5 million rows each.

I know indexing and statistics are a fairly moot point if the queries are horrible, but I'm definitely trying to do what I can to fix up even the slightest little bits. I'll resign myself to serving the fanciest, most delightful cat food this business has seen, if it's the best I can do! 😛

EDIT: Actually adding the attachments this time!