You, and...

I don't know about for the net in general, but for this forum, maybe Steve could institute some sort of marking for people that know what they are talking about....

Still a relevant topic, even if the original question is old. Stuff like this resurfaces regularly, as new people enter the profession and run across older discussion threads in the...

Attitudes like "X" should always be "Y" are silly and counterproductive, usually wrong, and anyone spouting them should be sent to go stand in the corner for a while. Logs...

Jeff Moden (8/24/2016)

---

BTW... if the table isn't critical to a DR restore, you might want to simply move it to another database and add a synonym for it. That...

BLOB_EATER (8/24/2016)

---

For me partitioning is about manageability first and performance second.

Your MDF is 500MB with 8 years growth? I'm not so sure you need to go down this route......

The filtered indexes do seem to help. I didn't run any precise before-and-after metrics, but responses seem snappier. I also ran this command: UPDATE STATISTICS paleodata.tax.podrobnostitmp No idea if it...

Thanks for the extra tips, Jacob, I'll post results that way next time.

As for the statistics, I've no doubt you're right. The data in these columns is very 'clumpy' (is...

Here are the outputs from the SHOW_STATISTICS command:

IX_Podrobnosti_SubPhylum_5000Feb 23 2016 10:51PM7071513285100YESNULL70715

10SubPhylum_5000

NULL07071501

Sorry about the ugly output - if there is a tidy way to post table output, I don't know how...

ScottPletcher (2/23/2016)

---

I think this will identity whether or not a row exist and will probably run much more quickly, since no massive join is involved, although a huge sort will...

Jacob Wilkins (2/23/2016)

---

Ah, interesting. Estimating 1240 rows and getting an actual 25 million is certainly problematic 🙂

I must have misunderstood you earlier, though. I thought the stand-alone COUNT queries were...

Here is one queryplan that takes forever and returns a count of 0.

Jacob, I think this article that you posted does, in fact, pinpoint the trouble: http://blogs.msdn.com/b/bartd/archive/2012/03/14/row-goals-gone-rogue.aspx

It matches what I recall, in that the most troublesome queries were those that returned zero...

I wound up putting a single-field index on every column that is used in this circus, about twenty of them. That has made the performance acceptable. I'll put up some...

ChrisM@Work (2/23/2016)

---

Your two queries are logically different - sorry if I was ambiguous, what I meant was remove the NULL checks on the join columns only, like this:

SELECT COUNT(1)

FROM...