| Author |
Topic |
|
jonathant
Starting Member
6 Posts |
 Posted - 2008-01-12 : 18:10:34
|
| I have a scenario where a table has 2.1 million records in it. The primary key is a composite key:client_id char(5)transaction_id char(35)This table has a large number of indexes (15) to support all the various reporting queries that have to take place. A typical insert into this table takes a 25-200 ms typically. The table has one trigger on it that updates another table when a certain column changes values, but that is an AFTER UPDATE trigger so it should not no impact on the insert since it isn't called.Below is some sample timing information that shows the time to perform the insert observed by the client in milliseconds:2008-01-12 17:32:49,777 time [63]2008-01-12 17:34:04,389 time [34]2008-01-12 17:34:05,638 time [129]2008-01-12 17:34:47,892 time [49]2008-01-12 17:34:56,669 time [223]2008-01-12 17:36:52,226 time [16]2008-01-12 17:38:22,423 time [391]2008-01-12 17:38:47,038 time [746]2008-01-12 17:44:23,453 time [168,240]2008-01-12 17:44:23,979 time [67,022]2008-01-12 17:44:24,558 time [235,738]2008-01-12 17:44:24,709 time [178,876]2008-01-12 17:44:24,756 time [81,065]2008-01-12 17:44:25,023 time [285,516]2008-01-12 17:44:25,061 time [83,065]2008-01-12 17:44:25,549 time [181,647]2008-01-12 17:45:32,396 time [188]2008-01-12 17:45:38,917 time [270]As you can see the timing is usually normal and then there will be a block of inserts that took a variety of time to complete. There are never any deadlock messages reported related to this table so I don't think it's a deadlock, but whenever I see the problem underway I would run the following query to see if a certain lock was having issues being acquired:SELECT object_name(object_id), *FROM sys.partitionsWHERE hobt_id in ( select resource_associated_entity_id from sys.dm_tran_locks where request_status <> 'GRANT')This has not yielded anything meaningful. I thought that it might have something to do with synchronous statistics updating so I turned on the "update statistics asynchronously" on the database, but that did not change the problem. To be as helpful as possible I've included the output of a DBCC SHOWCONTIG:DBCC SHOWCONTIG scanning 'client_transaction' table...Table: 'client_transaction' (1434488189); index ID: 1, database ID: 5TABLE level scan performed.- Pages Scanned................................: 645727- Extents Scanned..............................: 81621- Extent Switches..............................: 88886- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 90.81% [80716:88887]- Logical Scan Fragmentation ..................: 2.42%- Extent Scan Fragmentation ...................: 54.45%- Avg. Bytes Free per Page.....................: 1611.5- Avg. Page Density (full).....................: 80.09%During these very slow inserts were not seeing anything else about the application slowing down. Everything else feels normal.Does anyone have any ideas of how to go about troubleshooting this?Thanks,Jonathan |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-01-12 : 18:35:50
|
quote:
The primary key is a composite key:client_id char(5)transaction_id char(35)
That would likely be the problem... You probably made the Primary Key using standard syntax which automatically makes the PK a CLUSTERED index... that's very bad in this case because inserts can happen to the "middle" of the table... SQL Server will reorganize the data (once the fill factor has been overcome) to make room for the new row in the middle of the data. That'll take quite a while on 2.1 million rows.In this case, the primary key should be a NON-Clustered index. You will need to change it. In order to keep the table from just being a "heap" after removing the clustered PK, you may want to add an IDENTITY column to the table and put a unique clustered index on that.--Jeff Moden |
 |
|
|
jonathant
Starting Member
6 Posts |
Posted - 2008-01-12 : 18:49:06
|
quote:
Originally posted by Jeff Moden
quote:
The primary key is a composite key:client_id char(5)transaction_id char(35)
That would likely be the problem... You probably made the Primary Key using standard syntax which automatically makes the PK a CLUSTERED index... that's very bad in this case because inserts can happen to the "middle" of the table... SQL Server will reorganize the data (once the fill factor has been overcome) to make room for the new row in the middle of the data. That'll take quite a while on 2.1 million rows.In this case, the primary key should be a NON-Clustered index. You will need to change it. In order to keep the table from just being a "heap" after removing the clustered PK, you may want to add an IDENTITY column to the table and put a unique clustered index on that.--Jeff Moden
I feared that might be the problem. It's doable to switch to an integer (identity) based and carry it through the child tables. Is there any way to use any of the SQL tools to determine if indeed it's the reorg once the fill factor is reached so I have a smoking gun to show my boss? If so, what events would I look for in the trace?Thanks,JonathanJonathan |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-01-12 : 19:18:02
|
| You do not need to change from the natural key... just make it a non-clustered primary key instead of a clustered one. No changes needed to the child tables.Purpose of the IDENTITY column I suggested is just to have a decent column to put a clustered index on to keep it from being a heap. It is NOT part of the primary key.--Jeff Moden |
|
|
|
jonathant
Starting Member
6 Posts |
Posted - 2008-01-13 : 12:46:09
|
quote:
Originally posted by Jeff Moden
You do not need to change from the natural key... just make it a non-clustered primary key instead of a clustered one. No changes needed to the child tables.Purpose of the IDENTITY column I suggested is just to have a decent column to put a clustered index on to keep it from being a heap. It is NOT part of the primary key.--Jeff Moden
OK, I think I understand the proper steps to take:1) ALTER TABLE client_transaction add client_transaction_oid bigint NOT NULL IDENTITY(1,1)2) Change the PK from clustered to nonclustered. Not sure how to do this. I can't drop the PK because of all the FK relationships on this table. Do I need to drop all those FK relationships, Drop the PK on the table, create a new non-clustered PK (never done a non-clustered one so I'm not sure of the syntax), and then restore all the FK relationships? 3) Create the clustered index on the identity column.So I could really use some pointers on the proper step 2. One other area that I'm concerned with is the amount of time it will take to perform this operation. So would anything in step to be sped up by dropping all the indexes on client_transaction and then recreating them in a step 4?Thanks,Jonathan |
|
|
|
jonathant
Starting Member
6 Posts |
Posted - 2008-01-14 : 11:44:29
|
| Well I went ahead and changed the table last night to make the identity column the clustered index. I thought things were going great, but then I ran into the bottle neck again this morning. This time wasn't as severe (longest insert took 95 seconds), but still that is very long. What command against the management tables or sql trace could I possibly run to find out what work is being done during this huge slow down?Thanks,Jonathan |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-01-14 : 18:20:43
|
| Did you rebuild the indexes after all of that?Also, do you happen to have any triggers on the table you're inserting to?--Jeff Moden |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-01-15 : 02:58:11
|
| Could you post the INSERT statement code?Is the code doing lookups in subqueries?Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-01-15 : 07:16:57
|
| I guess the next step would be to try to consolidate the reporting indexes into composite indexes...--Jeff Moden |
|
|
|
jonathant
Starting Member
6 Posts |
Posted - 2008-01-15 : 07:20:08
|
quote:
Originally posted by Jeff Moden
Did you rebuild the indexes after all of that?Also, do you happen to have any triggers on the table you're inserting to?--Jeff Moden
There is one trigger, but it's an AFTER UPDATE trigger so that shouldn't come into play at all on an insert.I went ahead and made the clustered index change as well to child tables that exhibited the same complex primary key that was also the clustered index. I was thinking that if those tables are having the same problem they could be slowing down the overall transaction and holding up the inserts by other processes. So now all the tables involved have clustered indexes on identity columns as suggested. We'll have to see how things perform today. I'll post back my findings.Thanks,Jonathan |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-01-15 : 09:34:26
|
| Non-clustered indexes involve performance trade-offs. They speed up SELECTS, but slightly impede INSERT, UPDATE, and DELETE statements.Most of the time, the overhead for transactions is outweighed by the benefits for searching, but in your case it may not. Are you sure you need all 15 of those indexes? What is the cardinality of each index? Can any be combined into composite indexes? Are all of them actually being used?e4 d5 xd5 Nf6 |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-01-15 : 18:50:48
|
| Like I said... "I guess the next step would be to try to consolidate the reporting indexes into composite indexes..."--Jeff Moden |
|
|
|
andtheraincamein
Starting Member
3 Posts |
Posted - 2008-02-12 : 07:52:36
|
 |
|
|
|
andtheraincamein
Starting Member
3 Posts |
Posted - 2008-02-12 : 08:49:28
|
| |
|
|
|
andtheraincamein
Starting Member
3 Posts |
Posted - 2008-02-12 : 10:12:09
|
| |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-02-12 : 11:50:01
|
| WTF?Spam??? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-02-12 : 13:21:06
|
| Looney-bin.e4 d5 xd5 Nf6 |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-12 : 13:47:24
|
| What is the growth factor on the Database files set to? How much data is being inserted each time? What does the query look like that is being used for insert (already asked, but not answered)? Can you make a backup of the database and restore it w/ a different name? If so, what happens if you drop all other indexes except the clustered and the one for the PK? When did this issue start happening? How fast have you gotten the 2.1m records?Are you using PerfMon to look at how the server is performing when you hit one of the slow inserts?How much free disk space, free mem, etc. is on the server? |
|
|
|
|
Single row insert taking 1-3 minutes