outragous query duration during load

Question

outragous query duration during load

aviadavi

SSC Veteran

Points: 258
More actions
March 6, 2014 at 8:42 am

#396381

Hi,
I'm performing a load test on my SQL server.
I start a load of one main stored procedure in our SQL and after 7 minutes (when it gets to ~2500 transaction per secs), I see in SQL profiler that the duration increases from less than 0.2 second to more than 3 seconds).
In SQL profiler I've included "SP:StmtCompleted" to understand which statement takes that long time and i see it is one simple update query :
UPDATE dbo.Table1 WITH (READPAST)
SET AccessDate = GETUTCDATE()
WHERE ID = @id
Only this query takes long time during load .
What can I do in order to improve this?
BR,
Aviad

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

May be the SP is getting recompiled. Please also capture the execution plan and check it. If you can post it it would be very helpful.

--

SQLBuddy

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

If it's an update statement, you could be seeing contention from other resources doing reads at the same time as you're trying to modify the data. That's a pretty classic reason. But, it could be all sorts of things. Maybe the update kicked off page splits that had to rearrange a lot of indexes or something. It's hard to know without also knowing what the query is waiting on, what, specifically, was causing it to run slow.

"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

aviadavi SSC Veteran Points: 258 More actions · Answer 3

But if I can see all performance counters, and open an SQL profiler I can find the reason, am I wrong?

I now added page splits (at perfmon) and SP:Recompile (at SQL profiler) , maybe i will see something..

if you have more suggestions to monitor, please let me know..

aviadavi SSC Veteran Points: 258 More actions · Answer 4

one more thing,

How can I know (during load) if the SQL is now in bottleneck ?

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

aviadavi (3/9/2014)
one more thing,
How can I know (during load) if the SQL is now in bottleneck ?

You're either seeing long wait times, or blocked processes, or both. If you're seeing waits, learn what the waits are so you know where the bottlenecks are. If you're seeing blocked processes, understand which is blocking and why it's causing the blocks.

"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

aviadavi SSC Veteran Points: 258 More actions · Answer 6

which i can see only by queries? or I can see it somewhere else? (Profiler/PerfMon etc..)

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 7

Perfmon doesn't show that type of material. You'll want to capture query metrics using a server-side trace (not Profiler, don't run the GUI against production machines) or extended events. But to see wait statistics and on-going blocking, you'll need to use queries against Dynamic Management Objects (DMO). That's the best method to see that type of information. For lots more details on how to capture query behavior and tune queries, I strongly recommend my book, below in my signature. For more details on DMOs in particular, you can download a copy of Performance Tuning with SQL Server Dynamic Management Views.

"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

MyDoggieJessie SSC-Forever Points: 44519 More actions · Answer 8

Wow Grant, that "SQL Server 2012 Query Performance Tuning" book is $40- do we get an SSC discount? 😀

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 9

MyDoggieJessie (3/9/2014)
Wow Grant, that "SQL Server 2012 Query Performance Tuning" book is $40- do we get an SSC discount? 😀

Ha! Price is set by Apress. They've actually come down some (which is a mixed news for the author).

"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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 10

aviadavi (3/6/2014)
Hi,
I'm performing a load test on my SQL server.
I start a load of one main stored procedure in our SQL and after 7 minutes (when it gets to ~2500 transaction per secs), I see in SQL profiler that the duration increases from less than 0.2 second to more than 3 seconds).
In SQL profiler I've included "SP:StmtCompleted" to understand which statement takes that long time and i see it is one simple update query :
UPDATE dbo.Table1 WITH (READPAST)
SET AccessDate = GETUTCDATE()
WHERE ID = @id
Only this query takes long time during load .
What can I do in order to improve this?
BR,
Aviad

What's the rest of the code? Are you using that UPDATE in a loop?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)