reasons for High Duration

Question

reasons for High Duration

rubes

SSCrazy

Points: 2058
More actions
September 4, 2007 at 2:39 pm

#176377

Running SQL 2005 SP2 on windows server 2003 sp2.
Performing a simple one record insert through a stored procedure called from an app server, the CPU is 0 to 16, the Reads are below 50 (usually 2).
What are some things that would cause a Duration of ~500 (half a second)? Actually duration fluctuates drastically. It's either really low (under 5) or around 500. This really affects performance as this is how we do data imports... RBAR. We can't fix the app at the moment, but are there some things we can do to keep it at 5 instead of 500?

Regards,
Rubes

Viewing 10 posts - 1 through 9 (of 9 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

you are likely experiencing "blocking". Run a trace ( or sp_blocker script) to detect those and figure out what is the root cause

* Noel

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

1) Are there indexes on the table being inserted into? Index maintenance can cause more page splits and thus delays. Speaking of page splits, are your fill factors set appropriately for high-volume inserts?

2) Are any Foreign Keys involved? Another point of effort and also possible contention.

3) Does your database have much free space? If you 'forgot' to size the database correctly and set the growth increment of the data file you are really screwing yourself because a) 1MB is default growth --> very frequent growths which is a minor delay each time and b) your data file is fragmented to hell and back causing overall suboptimal performance.

4) Checking for blocking is key too, as noeld mentioned.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

rubes SSCrazy Points: 2058 More actions · Answer 3

Thanks for the advice.

No blocking
The table only has one index which is the PK on an int identity column
No foreign keys
Databases are set to grow 10% and the amount of data on a given import is usually a few megs at best

I did find that it is a larger issue. Several jobs are showing longer run times. One piece to the puzzle is higher than usual waittimes (500 ms) on WriteLog. This points to disk IO issues but these databases reside on a SAN. Plus, there are other (almost identical in configuration and hardware) database servers that reside on the same SAN that are *not* having issues.

Regards,
Rubes

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Since you now bring up SAN, what is the layout of the LUNs and also the file placements for both the db that is having perf issues as well as the ones that are not? There are some really sub-optimal things you can do with file placements, SAN or direct-attached. If the slow db's log file(s) are on a lun that is raid5 and which is also getting hit with lots of other I/Os (especially non-sequential), performance could really suck.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

rubes SSCrazy Points: 2058 More actions · Answer 5

MDFs and LDFs are on separate RAID5 SAN drives. There are no other files on these drives. All servers have the same configuration and reside on the same SAN. Backups are on a 3rd SAN drive and we use Tivoli to backup the backup files to tape.

Regards,
Rubes

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 6

Have you fired up perfmon and or any SAN client tools yet and taken a look at the various disk counters, etc.? Even the magic SAN has limitations/gets busy and you could very well be waiting on I/O.

Joe

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Passing any parameters to the sproc? If you are, post the code so we can take a peek for ya...

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

noeld SSC Guru Points: 96590 More actions · Answer 8

noeld

SSC Guru

Points: 96590

September 6, 2007 at 2:48 pm

#731973

are you experiencing autogrows ?

* Noel

Ayman SSChasing Mays Points: 637 More actions · Answer 9

Check execution plans of your query; if you find the estimated number of records way lower than the actual returned records, then that's sign of stats. Also, check the stats date on the index/table. Most likely, your statistics are dated. if so, run update stats with full scan and that should do the trick.

Thanks,

Ayman