January 7, 2015 at 8:10 am
I have this query below which gets executed thousands of times in a day and I have been seeing some locking issues. No user has complained yet.
UPDATE COLUMN_NEXT_ID_RECENT_ITEMS
SET Next_ID = Next_ID + 1
WHERE Company = @1
AND Form_Name = @2
Tuesday, it got executed around 6400 times and cumulative wait time was about 18:00 minutes (LCK_M_X). Is it normal or is there something I should be doing to prevent locking issues.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 7, 2015 at 8:37 am
UPDATES will always generate locks (and any other DDL and DML statement). You might have been concerned about blocking instead of locking.
I'm not an expert on this subject, but I believe that there's information missing.
This could be a good read for a start: http://aboutsqlserver.com/2011/09/28/locking-in-microsoft-sql-server-table-of-content/
January 7, 2015 at 8:44 am
New Born DBA (1/7/2015)
Tuesday, it got executed around 6400 times and cumulative wait time was about 18:00 minutes (LCK_M_X).
So on average each update waited 15ms for a lock. Is 15ms too slow?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2015 at 8:50 am
I think it's 150ms, isn't it - 6400 waits, 1080 seconds of wait time? But that's only if that's the only query that took that type of lock - the number you see in sys.dm_os_wait_stats is the cumulative wait time for all occurrences of that wait type.
John
January 7, 2015 at 8:52 am
GilaMonster (1/7/2015)
New Born DBA (1/7/2015)
Tuesday, it got executed around 6400 times and cumulative wait time was about 18:00 minutes (LCK_M_X).So on average each update waited 15ms for a lock. Is 15ms too slow?
I don't know if it's too slow or not and that's why I am seeking help from experts like yourself so next time if someone comes to me and ask me, I will have the answer.:-)
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 7, 2015 at 9:02 am
New Born DBA (1/7/2015)
GilaMonster (1/7/2015)
New Born DBA (1/7/2015)
Tuesday, it got executed around 6400 times and cumulative wait time was about 18:00 minutes (LCK_M_X).So on average each update waited 15ms for a lock. Is 15ms too slow?
I don't know if it's too slow or not
Well I sure don't know, I don't know what the application requires from that query, I don't know whether it's causing performance problems in your app. (and yes, it's 150ms, assuming that there were no other queries wanting that lock type during the period you monitored)
Whether a query is too slow or not depends on the requirements. If you have an SLA that states that the update in question must complete within 50ms, no exceptions, then it's too slow. If it runs once an hour and can take up to 5 seconds, then 150ms is probably fine. As with many things in SQL, there's no hard line dividing 'acceptable' and 'no acceptable', it depends on the circumstances, on whether that's a critical, time-dependant piece of code or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2015 at 9:03 am
Take a look at the execution plan for the query. See how it's being resolved. That will tell you if it's doing a scan or a seek for the UPDATE. That will give you suggestions for improvement, if any.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2015 at 12:13 pm
If you primarily use columns "Company" and "Form_Name" to do lookups and/or joins on table "COLUMN_NEXT_ID_RECENT_ITEMS", make sure the table is clustered on those columns. [It might, instead, be harmfully (in this case) clustered on an identity column.]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 7, 2015 at 12:27 pm
Please see the attachment for a little update on the blocking.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 7, 2015 at 12:53 pm
Definitely blocking. But no other information there. I don't think anyone is going to have anything to add beyond the suggestions we've made.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply