July 13, 2016 at 2:50 am
Hi all,
i have a problem on an application.
Details:
Table AAA, about 500 records, not growing
Many users (about 100) make a read from this table, many times (about 120 times for minute)
Others users write on this table, about 10 times for minute
Example:
SELECT A_FIELD FROM AAA WHERE B_FIELD = 'xxx' (about 50/60 times for minute)
UPDATE AAA SET A_FIELD=A_FIELD+1 WHERE B_FIELD = 'xxx' (about 10 times for minute)
A_FIELD is a Decimal(10,0), B_FIELD is a Char(100)
Normally, every select and every update take 1/2 ms, MAX 10ms
But sometimes (every 10 minutes, sometimes), the UPDATE take 10, 20 seconds!
A problem of concurrency, not?
What kinds of controls or modification i can make to resolve this problem?
(i cannot modify the application, i can only work on db setting, index, etc.)
(which other information you need to suggest something? I can say tha snapshot isolation situation of db is the following:
namesnapshot_isolation_statesnapshot_isolation_state_descis_read_committed_snapshot_onrecovery_modelrecovery_model_desccollation_name
master1ON03SIMPLELatin1_General_CI_AS
tempdb0OFF03SIMPLELatin1_General_CI_AS
model0OFF03SIMPLELatin1_General_CI_AS
msdb1ON03SIMPLELatin1_General_CI_AS
db_name1ON13SIMPLELatin1_General_CI_AS
need other?
Thank you very much!
Paolo
July 13, 2016 at 4:10 am
Capture actual execution plans for each operation against the table, and post them as .sqlplan file attachments.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 13, 2016 at 5:02 am
voilà 🙂
thank you
Paolo
July 13, 2016 at 5:12 am
paolobianchi (7/13/2016)
voilà 🙂thank you
Paolo
Thanks - but these are estimated plans. If possible can you post up actual plans? An actual plan of the SELECT is straightforward, use the query from the estimated plan. Cheers.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 13, 2016 at 5:18 am
they seems identic to me, but ok, take the actual execution plan 🙂
July 13, 2016 at 5:27 am
For such simple queries, there's not much in the way of tuning opportunities. The SELECT query is TRIVIAL, which means that there is only one possible execution plan. The one suggestion I would make is to fix your predicate and it's data values because you're getting an implicit conversion:
CONVERT_IMPLICIT(decimal(10,0),[ahisimonetti].[dbo].[cpwarn].[autonum]+CONVERT_IMPLICIT(decimal(10,0),[@1],0),0)
Passing the correct data type will help a very tiny amount.
I'd suggest using extended events to capture the wait statistics for the UPDATE query to understand specifically what is causing it to wait.
You do know that NOLOCK means that you're likely to see incorrect data? Not simply data in flight, but it's possible, even with a SEEK operation, missing rows or extra rows due to page splits. A better approach than NOLOCK is to use Read Committed Snapshot as the isolation level on the database.
"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
July 13, 2016 at 5:31 am
paolobianchi (7/13/2016)
they seems identic to me, but ok, take the actual execution plan 🙂
Same plan, but a little bit of added detail. It's the estimate vs. actual that gets interesting in Actual plans. In this can, the estimate is for 2 rows, the actual is for 1. That difference is trivial and the plan wouldn't change at all for a recompile. I'm back to capture wait statistics because there's little we're going to see with the just the queries.
"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
July 13, 2016 at 5:35 am
Is the UPDATE always followed by a SELECT of the new values?
UPDATE [cpwarn] set [autonum] = [autonum]+@1
WHERE [tablecode]=@2
SELECT autonum, tablecode FROM cpwarn WITH (NOLOCK)
WHERE tablecode='prog\DONUMREG\''001''\''2016 '''
or tablecode='prog\DONUMREG\''001''\''2016'''
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 13, 2016 at 5:57 am
Grant Fritchey (7/13/2016)
For such simple queries, there's not much in the way of tuning opportunities. The SELECT query is TRIVIAL, which means that there is only one possible execution plan. The one suggestion I would make is to fix your predicate and it's data values because you're getting an implicit conversion:
CONVERT_IMPLICIT(decimal(10,0),[ahisimonetti].[dbo].[cpwarn].[autonum]+CONVERT_IMPLICIT(decimal(10,0),[@1],0),0)Passing the correct data type will help a very tiny amount.
I'd suggest using extended events to capture the wait statistics for the UPDATE query to understand specifically what is causing it to wait.
You do know that NOLOCK means that you're likely to see incorrect data? Not simply data in flight, but it's possible, even with a SEEK operation, missing rows or extra rows due to page splits. A better approach than NOLOCK is to use Read Committed Snapshot as the isolation level on the database.
Thank you!
I can't understand, how a UPDATE table SET F1=F1+1 can generate a implicit conversion? FIeld updated is a Decimal(10,0).. I miss something!
Database settinga are already with ALLOW_SNAPSHOT_ISOLATION ON and READ_COMMITTED_SNAPSHOT ON
i can't touch "structure" of the query like adding o removing NOLOCK hints, because they are generated from a programming tools... 🙁
For lock stats, i can show you the attachment attachment.
(can i find waiting cause of a single query someway? I can just profile becuse i don't have sufficient permission on server to use extended events... 🙁
Thank you!
Paolo
July 13, 2016 at 6:00 am
ChrisM@Work (7/13/2016)
Is the UPDATE always followed by a SELECT of the new values?UPDATE [cpwarn] set [autonum] = [autonum]+@1
WHERE [tablecode]=@2
SELECT autonum, tablecode FROM cpwarn WITH (NOLOCK)
WHERE tablecode='prog\DONUMREG\''001''\''2016 '''
or tablecode='prog\DONUMREG\''001''\''2016'''
Mhhh maybe, because reading run 1/2 times a second and update about every 10 seconds...
Knowing a little the logic of the application, it's possible/probable that:
- read field autonum
- make some other things (several other query)
- update autonum=autonum+1
Thank you
Paolo
July 13, 2016 at 6:26 am
paolobianchi (7/13/2016)
(can i find waiting cause of a single query someway? I can just profile becuse i don't have sufficient permission on server to use extended events... 🙁
Thank you!
Paolo
You can using extended events. There are good filtering mechanisms available and a better set of events than traditional trace. However, if you don't have permission we're stuck. Other than the implicit conversion there's not a single thing in the code that screams out "PROBLEM" so we need to gather other metrics to understand where the problem may lie.
Using NOLOCK is a very poor coding practice if you already have snapshot isolation enabled and you need to communicate that to the developers. I'll bet it's not the only query it's on. You're probably in one of those shops that requires NOLOCK on every single query. That's such a horrific poor practice there ought to be prosecutions and jail time or at least fines.
Anyway, you need to capture the wait metrics. You've got two trivial execution plans with no real tuning opportunities (the conversion is clearly not a real issue, just a minor nit). There is no obvious path here to explain what's happening without additional information.
"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
July 13, 2016 at 6:27 am
Oh, and as far as the wait stats go, that's cumulative for the server. Looking at it in isolation for a problem of this type doesn't tell us anything at all.
"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
July 13, 2016 at 7:00 am
What you're dealing with is probably blocking of some time. To get to the bottom of this issue, during these occasions where query latency is extremely long, you need to focus on identifying what is the type of wait state for the blocked sessions, what other session is blocking them, and what specfific SQL statement each is running at that moment.
There are a large variety of queries posted here over the years, but the following stored procedure written by Adam Mechanic can do this for you. It is very comprehensive and folks are familiar with how to interpret the results. So basically you deploy this stored procedure, run it when your database appears in an extended blocked state, and then reply back with the results.
http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 13, 2016 at 7:33 am
1) Please provide the FULL definitions of the table. All indexes, data types, etc. included.
2) It is possible that the app (which you don't have control over) is simply going out to lunch and having an open transaction held open way too long which will block other incompatible activity.
3) Speaking of app, have you brought this issue up with the vendor??
4) Look into the Blocked Process Report. Use that to capture details of the blocking event in real time and find out exactly what is happening during them - without having to sit there and run sp_whoisactive over and over. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 13, 2016 at 8:07 am
Hello,
thank to all for precious tips.
I run whoisactive and i get 2 update, one of 19sec!
The type of lock is (19860ms)LCK_M_X
This query (call it UPDATE1) was locked from another query,
update cptmp_kredvfohoj set cpccchk='12avxgd'
different tables...
Then i get another update blocked for 3 seconds, LCK_M_X, blocked by the query UPDATE1
I'm trying to get another case but i think that always happen something like this
Tips?
Tnx
Paolo
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply