Grant Fritchey (9/29/2010)

---

I don't know about everyone else, but I'm seeing less and less data design these days as we use more and more ORM tools. They "do the design...

Personally, the easiest would be to put SQL Server Integration Services to work on it. SSIS can do this type of break down quite easily.

Another option, not as easy, would...

For it to simply stop execution I'd expect an error or message of some sort. However, to try to figure it out, I'd wrap the statements in a TRY/CATCH block...

Yeah, I'd add a few counters to that.

SQLServer:Buffer Manager:Page Life Expectancy

SQLServer:Buffer Manager:Lazy writes/sec

SQLServer:Memory Manager:Memory Grants Pending

Memory:Pages/sec

Memory:Page Faults/sec (really useful for trend analysis)

Memory:Pages Input/sec

Memory:Pages Output/sec

If you want to know if...

If the query is for reporting only, why would it be taking out an exclusive lock on the index? Are you sure that the query isn't doing some type of...

Just to reiterate, since you bounced the server, there's no real recourse for identifying an issue other than the error logs. That is, unless you put some type of monitoring...

What are you trying to do? There are ways to create temporary tables (as is shown above), table variables, and you can create permanent tables, all on the fly. So...

Yep, pretty much. The dateadd function can manipulate hours, minutes, seconds, days, months, years... whatever you need.

I don't know about everyone else, but I'm seeing less and less data design these days as we use more and more ORM tools. They "do the design for you"......

lallu_jaya (9/28/2010)

---

Chris,

I shall do it that way. I actually found that one of the join was redundant and removed it and found performance improvement.

Grant,

Thanks for your reply. Shall not worry...

That's pretty much what you need. While you're there you should probably go ahead and capture duration, the database, start time, maybe the user... you can add six or seven...

Jeff Moden (9/28/2010)

---

Heh... I've been trying to find incentive to get back to mine because I've been thinking the same thing with every word or bit of code I write....

Complete side point, but note the difference between how Gail had you do the table joins and how you were doing them in the original query. What you're doing is...

I'm not a guru either.

It sounds like you were probably hitting a combination of issues. I suspect the transactions were holding locks open for an excessive amount of time, partly...

Because the dependency viewing in SQL Server is more than a little bit messed up. Try running a query against sys.dm_referencing_entities to get the objects that are referring to the...