I didn't see anything about patching up this new server. IIRC there have been lots of perf regressions fixed in SP/CUs. Try that first if you can patch....

There is a performance implication in that TVPs cannot have statistics on them so you wind up with the same occassionally suboptimal plans that you can get with table variables.

Sean Lange (8/30/2011)

---

TheSQLGuru (8/30/2011)

---

1) dynamic sql is an INCREDIBLY POWERFUL AND EFFICIENT solution to a number of classes of problems. I strive mightily to always use the right tool...

shrinking a database is a devastatingly bad thing to do. Massive internal fragmentation AND external fragmentation results.

Find out what is consuming the space and address it. there are...

I think this problem could also be solved with my favorite 4-letter TSQL word: CASE. That is a nice solution you have too, btw.

I believe the CPU dipped when you rebuilt the clustered index because a) that operation kicks the shit out of your IO and probably causes CPUs to wait for data...

You are absolutely wrong in your recommendation to use table vars instead of temp tables. I recommend the exact opposite in almost all cases except VERY high-volume execution environments...

Perhaps you have data skew, where say 70% of your Colx values are a single thing and the others are spread around? Or there are just 4 evenly-distributed values...

1) dynamic sql is an INCREDIBLY POWERFUL AND EFFICIENT solution to a number of classes of problems. I strive mightily to always use the right tool for the task...

You are seeing a "random win". You should address the root cause by doing something such as OPTION (RECOMPILE) or even dynamic sql (be sure to guard against SQL...

clubbavich (8/24/2011)

---

Yes and no. I believe that the book is dated a bit though. SQL Server Express 2005, the database limit was 4G. That limit was increased to 10G in...

1) Please don't post a "me...

Wayne, can you please email me a link to your blog with the perf testing of the Denali OVER enhancements? I did a web search but couldn't find it....

My guess is that this is actually a symptom of something else, likely a poor IO subsystem. Have you done a waitstats and file IO stall analysis? Also,...

Partitioning is a very advanced/complex subject. You REALLY need to have experience/training to both setup/manage them properly as well as write proper queries that might take advantage of partition...