I wonder if the SET statements in your trigger aren't causing extra recompiles - which can cause some interesting bottlenecks. Check for that, and if they are happening try...

1) what happens to the table during the day? that would determine if you need more frequent stats updates

2) could this be a simple case of parameter sniffing? updating...

1) lock reason: snapshotting perhaps? or deferred temptable dropping?

2) still wondering about trigger code. can you post that?

3) have you tried sp_whoisactive to see if you can get details about...

get a copy of the great free tool sqljobvis. It shows you a gantt chart of agent job executions, making it VERY nice for checking for overlaps. Not...

1) can you post the code for the login trigger?

2) if you are having an 'outage' of this nature, I recommend you get a professional on board to quickly isolate/fix...

I actually think ApexSQL's Log product might be able to unwind or redo transactions given your scenario. Tell them TheSQLGuru sent you.

Why don't you run some tests and find out (and then tell us what you find)??

In any case, I imagine that your query will be overwhelming compared to...

jefydominic (4/25/2011)

---

Hi All,

We are planning to setup a new SQL server 2008 (enterprise edition).

Average data size is 100GB. Every weekend we will delete some data and add some new...

Regarding Paul Randall's quote: "Update statistics for all of the non-indexed columns", it would seem that there is a flaw in that statement, since statistics are only kept on search...

data types are probably pretty far down on the list of reasons why your query performance is poor. Look to indexing and query construction first.

There are FAR more important things to do to improve your database application performance than worry with IO configuration. However, what you seek (general advise on tuning) goes WAY...

1) There are many places where microsoft recommendations are suboptimal, poor, inappropriate, dead wrong, etc.

2) MANY resources you can find on the web are OUTDATED. This case is definitely...

mohan.pariveda 18256 (4/24/2011)

---

Hi All,

I am new to the SQL Server tuning and facing a critical performence problem.

My environment : Windows 2003 R2 (32 bit OS), SQL 2005 ENT, 8 GB...

Jack Corbett (4/22/2011)

---

Lots of good advice here. The one thing I learned back when I used linked servers is that you should do as much work on the linked...

P.S. I specifically recommend that you do not REORGANIZE your indexes. Reorganization (defragmentation) takes about 8 times longer than rebuilding, if pages in the table are exclusively locked, the reorg...