Query question

First of all, welcome!

A few things jump out at me about this one:

• This transaction is waiting on locks to be released (could be causing them also, stepping on its own toes).
• It's also waiting a long time writing to the transaction log (look for high write latency for the tran log file under the storage i/o view).
• It's finding one row per execution (so only looking for 1 row), but doing very few logical writes, so establishing a lock, then writing a transaction that very well may not have updated anything to a new value?
• Your plan recently changed - what is new plan vs old plan? My guess is the added index - optimizer now using it?
• Each execution is now taking 10x longer to run vs three-ish days ago. Going in a bad direction

Recommendations:

• reconsider that index or altering it if that was the catalyst
• I think I'd profile this statement for a few minutes and peek at those parameters. See if @p1 = @p5 and @p2 = @p6 for the same execution. Better logic might be to run a select, then conditionally update if different, else do nothing.
• Dig into the plans and see what the optimizer is doing differently now.
• Look at the workload the engine is doing by viewing the historic chart for this statement.
• If high write latencies, see what can be done at the storage level (look for RAID 5 config with the added parity calc slowing down writes)

Hope this helps - would really like to hear how it goes.

I'd also like to hear other ideas on this one from the community...

Hi Mandevil,

I’m attaching the historical chart. It seems that after Nov 6 things started taking a turn for the worse.

I ran sql profiler and filtered on deadlocks. Can you open the attachment?

I’ve removed that index that I created.

I ran database engine tuning advisor on this one table and it recommended an index and 10 statistics to be created.

SET ANSI_PADDING ON

CREATE CLUSTERED INDEX ON .[ActiveModules_T]

( ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON) ON

CREATE STATISTICS ON .[ActiveModules_T](, , , , , , )

Do you trust this tuning advisor?

How are things running after removing the index? That index could have actually been at the heart of the blocking issue.

I don't trust any tuning advise. 8 )

I'd test it out in pre-production, look with DPA - make sure it's not introducing the long waits again. Then promote to production if it looks safe.

Have you gone and looked at the blocking tab within a one hour trend view?

What does it show for blocker? What was the activity the blocker was doing?

Things are no better it seems.

A very very very long list of blockers. It’s like everyone is stepping on each others toes.

I'd recommend opening a support case at this point. May need to do a screenshare and dive in.

How different is the new (clustered) index from the one you implemented on the 6th?

Right now there is no index on that table. The application vendor never had an index. In fact, most if not all tables never had an index.

Support says they don't really offer this kind of support. He's trying to find someone.

Marshall Mednick

System and Database Administrator

University of Toronto

Faculty of Dentistry

416-864-8126

Sent from my iPhone

Yeah, this is beyond product support - more consulting at this point. 8 )

Unfortunately, I don't really free up until after the turkey day holidays. Want to try to connect after that?

Thanks. I’ve contact our application vendor to see what they know about this.

I’ll let you know what I find out.

I’ve tried rebuilding the table statistics and removed indexes from both of these tables.