I found our issue. This is my best guess since I don't have any concrete evidence to prove it.

I must mention that I did look at the execution plans (that's always the first thing when you have a performance problem, I just didn't mention it, because I guess I thought it went without saying, my bad). The execution plans showed nothing out of the ordinary and were almost an exact match between dev and prod. Only deviated by 1/10 of a percent in most cases, so that led me no where (other than to update my version of SQL Sentry's Plan Explorer).

I did discover that we neglected to move our tempdb from the local super slow physical drive to the super speedy SAN drive a coupe of months ago, so got rid of the waits were seeing in tempdb, which by the way is the only place we saw any kind of waits. Unfortunately, this didn't help either.

I had forgotten about Adam's sp_whoisactive (thank you for reminding me), so downloaded that and discovered something interesting, but not all that helpful. It showed me that the reads were going magnitudes faster in dev than in prod, which I already new, but still had no clue as to why.

I tried moving the physical data files around between SAN drives to see if our tiered storage was really working like was supposed to, which didn't help either.

We also have Idera's SQLdm product and it did nothing to help us, it was like nothing was wrong with the server.

Finally this morning I did one more cursory compare between the tables and discovered a very odd anomaly that I missed earlier on one table. Even though the tables had the exact same number of records, approx 7 Million, (in addition to the exact same records, thanks to RedGate's SQL Data Compare), the table in prod was using 2x more storage than the table in dev. Now, we had rebuilt all the indexes on all the tables (by actually dropping all indexes and then recreating them), updated the stats and usage in both dev and prod earlier, but still no improvement. On a hunch, I decided to recreate this one offending table along with all it's dependent objects (i.e., indexes, views, etc) and Abracadabra back down to 4 minutes in prod. When I say recreate the table, I did a "select into.." with a new table name then renamed.

Not sure exactly what the cause of the bloat was, but now the storage matches, within a byte or two, of the storage in dev. Bad meta data around the table? indexes? not sure. Anybody have any explanation of this?

Thanks for all the suggestions, it's been a while since I've had to do "DBA" work and I had to dust off the cobwebs a bit on this one.

Best Valentine's Day present I've had at work ever!

-A.