samsql (5/20/2010)

---

yes saby i m gettin blocking in the tempdb.

Grant there are procedure with begin and tran

E.g

proc ssa

(

Begin tran

exec procedure1

-----create some temp table and do...

Saby, I'm confused. Where did you get deadlocks in this situation. We're talking about blocking. That's not the same thing as deadlocks.

It really depends on your needs. That query is returning the data you outlined.

Brett Robson (5/20/2010)

---

I don't see why you guys are promotiing the use of dynamic SQL. I'm often asked in job interviews when it's appropriate to use dynamic SQL - the...

Temp tables last for the length of the process that created them. That wrapper proc is the process, so as long as it's running, you'll keep those temp tables. You...

You can capture DDL commands through an even trace or by looking at the default trace on the server. Based on that volume, the default trace might be rolling over...

Yeah, 3 million rows could be a bit off-putting, but it's still a...

There really isn't anything "to do" with the tables. If you have lots of objects in your database, you're going to have lots of rows in the system tables. As...

Generally, you don't. I've never heard of anyone defragging a system index. Since you're operating in 2005/2008, you can't get direct access to the tables anyway, you're just looking at...

No, when a plan is flushed from cache, it's gone forever. The only thing you can do is try to capture the plans regularly (based on how often they flush...

It adds overhead, but it's pretty trivial. The accumulative effects aren't good, but you're unlikely to see any given query perform badly because of this.

Please, edit the post, remove the XML. Then go back and save your execution plan as a .SQLPLAN file. You can then post the file as an attachment. From there...

From the Books Online:

The missing indexes feature is on by default. No controls are provided to turn the feature on or off, or to reset any of the tables returned...

Dynamic methods are frequently problematic, but to get the data you're looking for, check out the system views available under the schema called INFORMATION_SCHEMA. They should supply you with everything...

To be able to use all the methods and abilities of 2008, you will need to set the compatibility mode to 2008, not 2000. Performance may or may not...