1) Confio

2) SQL Sentry Performance Advisor

The best one is actually ME! 😎

Let me see if I have this right: you are dumping 120K relatively fat rows into a TEMPORARY TABLE then selecting those rows back out and it is taking...

Guess we will have to agree to disagree on this one. In my world, 93.7% of the developers will take the easiest way to slap code together as quick...

Consider it job security! That view (especially these days) makes all kinds of things seem more acceptable!

Hope the refactor really smokes the original version!

shannonjk (11/13/2009)

---

That's actually exactly my intended need!

That solution crossed my mind a few times, the problem is I would have to rewrite the query I have which is over 200...

The 'best' way to get the optimal plan for most everything covered in this thread is dynamic sql. Yep, you pay a small price for this in compilations and...

Please note my email address for future reference if you intend to have any scale to systems you develop with ANY ORM. You WILL be needing my performance...

Read, and REALLY understand, this and the 2 subsequent blog posts. They constitute the Bible of Deadlock Troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

sajid.mohd (11/12/2009)

---

TheSQLGuru (11/12/2009)

---

How about simply stepping outside the box (litterally and figuratively) and simply prevent the loading of more than one file of data at a time?? VOILA!! ...

Not sure I follow exactly, but this query is much more efficient from a read perspective, but it doesn't seem to give the same output as what you have. ...

Can you give whomever is asking my contact information please? ...

1) Buy more CPUs

2) Buy more RAM

3) Put TempDB on SSDs (or at least VERY fast IO subsystem), because it is getting CRUSHED by the sorting/grouping

4) do file IO stall...

How about simply stepping outside the box (litterally and figuratively) and simply prevent the loading of more than one file of data at a time?? VOILA!! No more...

there certainly are ways to answer aggregate queries without OVER, but they can be significantly LESS performant. As Lynn said, we need tables, data information, queries, and query plans...

Can you please describe EXACTLY what behavior you are observing (CPU, IO, paging, etc)? How exactly did you identify this command/spid as the one causing the problem? if...