[font="Arial"]I prefer to avoid the IF/ELSE scenario for queries. Eventually, you'll have to "fix it" in two places. Plus, you open up the possibility of two different signatures...

[font="Arial"]Regarding blocking SPIDs, here is a query that I use:[/font]

SELECT TOP 1

&#160 session_id AS Blocked_SPID,

 &#160blocking_session_id AS Blocking_SPID,

 &#160wait_time

FROM sys.dm_exec_requests

WHERE wait_time >= 15000--15 seconds or more

AND blocking_session_id != 0

ORDER BY...

Thanks, Elliott. FMTONLY may work, although some initial testing with some various sp's gives me some errors. It should be pretty easy to get what I need via...

RE: Your trace findings.

Are there stored procs that are running slowly with a high number of reads and CPU?

If so, how many? If it's just a...

Hi Joseph,

•Deletes are easy:...

The update stats job that should already be in your maintenance plan is critical to the performance of your applications, possibly more so than the rebuild indices job, and if...

When I'm performance testing a stored proc, the first thing I do is rebuild all indexes and update statistics. (I'll assume you can do this in a non-production environment.)

Then...

David (3/16/2009)

---

A possible sign that your statistics are out of date is if the actual and estimated row counts in the execution plans are very different.

I expect that you know,...

To get the info in a query, try using this dynamic mgmt view: sys.dm_exec_requests.

For instance, to get all the spid's that are currently blocked:

SELECT *

FROM sys.dm_exec_requests

WHERE wait_time >...

t.walker (3/12/2009)

---

Dave,

OK .. I can see how you know how many updates.

Sorry about the schema point .. My mistake in misreading what you did!

To see whether a shrink has occurred...

GilaMonster (3/11/2009)

---

Just bear in mind that by setting a fill factor to 75%, your table is now taking a fair bit more pages. This means more space on disk, more...

t.walker (3/12/2009)

---

Dave,

There is something in your figures I still can't understand here. You are right, they don't stack up.

How do you know you are updating only a few hundred rows?...

I did some further testing. I dumped a table into a temp db and re-created all of the indexes, including the PK, which is clustered. It's on a...

GilaMonster (3/10/2009)

---

I wouldn't worry until it reaches...

Jeffrey Williams (3/10/2009)

---

In addition to what Gail has mentioned, how much space is available in the data file? Do you have enough space available to rebuild indexes - or,...