Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2006-04-13 : 10:57:59
|
| I have an issue, or rather a colleague has a re-occurring issue. We have a database (16462mb), and when trying to insert a record into a table, it takes a really long time to perform the insert. Second insert, its fine. The table is only 72kb in size and contains just over 500 rows, and consists of 3 columns. The clustered index is the primary key which is an auto-increment data type.I know this is very little information to go on, but I was wondering does anyone have an idea what the issue can be? The colleague tried to recreate the issue, but had little success. He said that the only difference would be that the maintenance plans had run (reorganising the indexes, backing up etc). But surely that would improve performance rather than impede it?I have no idea where to start to look so was hoping you could give me some guidance?Hearty head pats |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-13 : 11:11:15
|
| "But surely that would improve performance rather than impede it?"If the indexes have been reorganised but the Stats NOT updated that might be an issue.Similarly if you are using a SProc then it may need recompiling to force a new query plan, matching the newly-optimised indexes, to be generated.Maybe similarly for any triggers.Maybe there are Triggers which need to open a connection to some external server - and that takes a little while to initiate the first time?Maybe the first instance is forcing stuff from disk into memory, which is then cached for subsequent iterations?Kristen |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2006-04-13 : 11:42:48
|
| Hey KristenThanks for you answer. Its all food for thought. I thnk I am going to have to do some more delving and trap laying. Then perhaps I will have more of a clue as to whats going on!Have a good weekend.Hearty head pats |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-13 : 11:54:40
|
| "... trap laying"Now there's a plan I like the sound of! |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2006-04-13 : 12:07:25
|
| Ok, I have asked a few more questions, and these are the answers:The query is a simple insert query and is embedded in the application. The application is run once a month, therefore, the execution plan would have well and truely been evicted from cache.The table on which the insert is performed has only the one index, but very narrow (one column) and consists of only just over 500 rows. The statistics are updated as part of the maintenance plan.This is the results after running DBCC SHOWCONTIG:- Pages Scanned................................: 3- Extents Scanned..............................: 2- Extent Switches..............................: 1- Avg. Pages per Extent........................: 1.5- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 50.00%- Avg. Bytes Free per Page.....................: 2992.0- Avg. Page Density (full).....................: 63.03%Other applications use the database, but none of them write to the database, therefore, the cause is unlikely to be a lock held on the table.Potentially, can other activity on the server could have impacted to the point of causing a simple insert on a small table to run for over 1 minute?The main pain in the derrier is that because we don't know what caused this to happen, we cannot recreate the issue. Therefore, I can only hypothosise about the resolution (which is rubbish). Any other ideas would be greatly appreciated.Hearty head pats |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2006-04-13 : 12:25:05
|
| Righty-ho, here is another update.......I was thinking that server load could've had an impact. However, having delved a little more, it appears that this delay issue went on for a period of time. The application would time out as it would meet the timeout threshold.The query itself was then executed directly on the server, and it took over 3mins. After that, a matter of milliseconds. Operations have assured me that nothing else was running around that time.The issue had occurred before with the same database, but on a different table. I'm flummoxed!Hearty head pats |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-13 : 12:35:41
|
| "Can other activity on the server could have impacted to the point of causing a simple insert on a small table to run for over 1 minute?"On a table with only 500 rows, no matter how badly it was 'fragged, I can't see it taking any time at all.Maybe the delay is elsewhere - perhaps in the logic that assembles the data and then hands it to SQL Server to insert?What does the Insert application logic look like - Multiple-row insert all in one, or several rows all one-by-one? Calling SProcs or just doing Dynamic INSERTs?Any triggers on the table? Lots of complex Foreign keys, or none?Kristen |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2006-04-18 : 04:40:21
|
| Hi KristenHope you had a lovely Easter.I have checked the table, and it has no Triggers and 1 foriegn key reference (where it is on the child side of the relationship). The statement is embedded in the application and is a simple insert, inserting only the one row. The table itself consists of three columns, one an autoincrement as the PK, the other a foreign key reference (but not a complex one - it references a smaller table of less than 50 rows), and the other a date timestamp. I think you may be correct in saying the bottleneck can be elsewhere, in the application, as apparently, the query took over 3mins in the application before timing out. When run directly on the server, it still took a ridiculous time of 1min, but that is substantially faster than when run from the application. When the application was run, immediately after already having done an insert, it was instant. So potentially, as you mentioned beforehand, maybe some of the time was taken to pull the tables into memory. However, it still should not take over a minute. If I can recreate the problem, how can I track the bottleneck? I have used profiler and perfmon in just made up scenarios, but I don't really know what events or counters to track. Any recommendations would be greatly appreciated.ThankyouHearty head pats |
|
|
|
|
|
|
|
|
Insert issue - long delays inserting a record