I would be very, very, very careful about using NOLOCK. It seems like it solves problems, but it can introduce bad data. By that I mean, repeated rows, missed rows,...

Yep, I've done it. It can work great. It requires a lot of discipline, but you can do it. I've written a couple[/url] of articles[/url] on it (although they're a...

It sounds like it could be contention, probably blocking, from uncommitted transactions. While you're in that stuck position, run this little query:

SELECT der.blocking_session_id,

...

Gotta be a difference in the stats. Should be evident by having different execution plans.

1) Nope, the locks are usually held for the duration of the transaction in case of rollback. If you've got a cursor in a trigger (oy), then the transaction is...

I would take a look at your execution plans from the merge statement. Something is causing this to slow down. You're working in a very small database, so you shouldn't...

Nope. Not with the log in simple recovery. Now, if the log were in full recovery you could either backup the log & retrieve it from there, or use one...

Just so you know, deleting data from a SQL Server database doesn't reduce the file allocations for that database.

CirquedeSQLeil (1/13/2011)

---

Same recommendation I...

Sure, you can use a TOP 1 with an ORDER BY. Something like this:

SELECT y.whatever

FROM MyTable

WHERE ID = (select top(1) ID

from mytable

order by mydate desc) as y

--or in a join...

It basically means that the statement in question is moving lots of data, either in (writes) or out (reads) of the server. What you do about it really depends on...

TheSQLGuru (1/13/2011)

---

>>For the foreseeable future, the best answer is, get someone in who can tune your structures and your queries.

Not true Grant!! I can ALWAYS make time for...

My personal preference for this, Red Gate's SQL Prompt Pro. Fantastic tool. Does a heck of a lot more than just format the code too.

It just has to be a series of TSQL statements. I'd just suggest wrapping it in a transaction so that you know the data was moved successfully prior to deleting...

Just curious, have you tried using the Import Data Wizard? I'm not sure it will meet your needs or not, but you can frequently get a lot more error information...