Random Slowness in Insert statment

Question

Random Slowness in Insert statment

Aspet Golestanian Namagerdi

SSCarpal Tunnel

Points: 4444
More actions
December 20, 2011 at 8:50 pm

#265216

We have SQL SERVER 2008 R2 64bit and Windows 2008 as OS in our production environment.Our database is being hitted heavily everyday.Generally all the insert operations are taking 30 - 40 ms to be completed,but recently randomly some of the Insert operations are taking 2 sec to complete.Our insert operation is very time sensetive and it should be below 1 sec.
We do not have any page split and all of the statistics of tables are uptodate.Also we do not have any File growth in our database,during the insert operation.When I checked the wait Times,I got below(attachment):
I would appreciare if someone help me.
Thanks

Viewing 15 posts - 1 through 15 (of 21 total)

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

Normal blocking, I would say. What does the table you're inserting into look like and which column is the clustered index on? Also, what do the other indexes look like? You may not have any page splits but you could be having some major extent splits with the non-clustered indexes.

Also, what else is going on with the table other than inserts?

--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)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

ONDEMAND_TASK_QUEUE

Occurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern.

Is it not a dedicated database server? It looks like your system is occupied with heavy loads outside SQL Server.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I'm with Jeff on this - have you monitored for blocking?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 4

Thanks for Replys.

This is dedicated Server for SQL SERVER and we do not have any other process on this machine.Also we have Transaction replication(The problematic machine acts as Publisher).

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Your replication speaks to the wait type ONDEMAND_TASK_QUEUE. It could also contribute to the occasional slowness.

Blocking occurs within SQL Server. You will need to look in SQL to find other processes that may be blocking your inserts.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 6

Aspet Golestanian Namagerdi

SSCarpal Tunnel

Points: 4444

December 21, 2011 at 1:19 pm

#1424437

Do we have any indicator for Blocking?

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 7

Aspet Golestanian Namagerdi

SSCarpal Tunnel

Points: 4444

December 21, 2011 at 3:46 pm

#1424502

Also,I notice this slowness happens every 10 minutes.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Aspet Golestanian Namagerdi (12/21/2011)
Also,I notice this slowness happens every 10 minutes.

So what runs every 10 minutes on your server?

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 9

I checked and we do not have any SQL job on this machine.But this server act as a Publisher in Transactional Replication and there is a Job on distributor(which is on another machine)"Replication Agent Checkup" that runs every 10 min and check the agents.Is it possible that this job has caused the slowness?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Replication is not my forte but that would be suspect #1 on my list now.

Easy test is to stop replication for 25 minutes if this has no hill effects (which I have no clue if it does).

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

Aspet Golestanian Namagerdi (12/21/2011)
Also,I notice this slowness happens every 10 minutes.

Transaction log backup up or replication could both cause this.

--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)

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 12

Actually I stopped the Log shipping Backup job and also I disable the replication jobs but still every 10 minutes,slowness happens on SQL statments that run against SQL SERVER.It seems that transaction log file is being locked every 10 min.but I could not find what is causing that lock

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

Aspet Golestanian Namagerdi (12/21/2011)
Actually I stopped the Log shipping Backup job and also I disable the replication jobs but still every 10 minutes,slowness happens on SQL statments that run against SQL SERVER.It seems that transaction log file is being locked every 10 min.but I could not find what is causing that lock

You'll need to dig deeper, then. There's either something running on that box every ten minutes or it's plugged into the same outlet as a coffee pot (has been know to cause massive collisions in network cabling... seriously).

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Jeff Moden (12/21/2011)
Aspet Golestanian Namagerdi (12/21/2011)
Actually I stopped the Log shipping Backup job and also I disable the replication jobs but still every 10 minutes,slowness happens on SQL statments that run against SQL SERVER.It seems that transaction log file is being locked every 10 min.but I could not find what is causing that lock
You'll need to dig deeper, then. There's either something running on that box every ten minutes or it's plugged into the same outlet as a coffee pot (has been know to cause massive collisions in network cabling... seriously).

Seriously??, got a link to that :-D.

My previous favorite was the screensaver that pegged the cpu at 100%, but this might be the new favorite!