The basic code doesn't cause locking all by itself. So, the problem must lie elsewhere. Are you getting errors? Does the code that's calling the proc have a transcation wrapper?

It does sound like you might be seeing the results of different paths through the execution plan working better for one data set than another. Check the execution plan for...

No, SQL Server will determine the order of resolution based on the indexes and their statistics on the table (or the lack thereof). You have very little control over the...

Well the OP came back with what seems to be an appropriate level of contrition and received help.

I just don't understand how you do it Lynn. I try to avoid...

If the database is running in 80 compatibility mode, you won't be able to run 90 type queries, which include using the CROSS APPLY query.

Well the OP came back with what seems to be an appropriate level of contrition and received help.

I just don't understand how you do it Lynn. I try to avoid...

Well the OP came back with what seems to be an appropriate level of contrition and received help.

I just don't understand how you do it Lynn. I try to avoid...

Well, I finally got around to RTFM... It is noted in BOL that this will run slow on large tables. So, you're just getting hammered. You're already doing the right...

Ray Herring (3/24/2009)

---

Hi Grant,

The DBCC is returning 3 rows for the table, the heap, the Blobs, and one non-clusterer.

The DM returns the same 2 indexes but it is 4 or...

I...

I doubt there's a way to do this in a single go. Each table will need and individual PK created. Unless you've named the primary key in all 100 tables...

Try looking at sys.dm_tran_locks to see what locks are being held. When I run it, I see an exclusive intent lock on the page from the first statement. That's causing...

Ray Herring (3/23/2009)

---

Hi All,

I have two related questions. First I have been working with sys.dm_db_index_physical_stats and I am really surprised by it's relative poor performance compared to DBCC.

I am...

If you're trying to check to see if a particular procedure is running at, say, noon, you can could query the sys.dm_exec_requests DMV to see if it's there.

No, it's not true. Depending on how you're calling nested queries you may not get the plan you think you'd get, as Gail outlined, but the plans will be created...