Hey guys, The vendor gave us an archiving tool for our huge tables. We gave them a production copy of our tables. They tested their tool against their own server and told us that it takes 2 seconds to insert each record. I know this is already bad. But today, I tested it against our own test server. They also gave us instruction on what to do first before running the archiving tool. Well anyways, after running the archiving tool, it took 20 seconds to insert 1 record. That's totally bad! I would like to know if you will be able to help me guys identify the problem by just looking at this links. http://restricted.dyndns.org/executionplan.txt http://restricted.dyndns.org/execplan1.gif http://restricted.dyndns.org/execplan2.gif http://restricted.dyndns.org/execplan3.gif The INSERT statement that you will see there consumed 10 seconds of CPU and 30 seconds of Duration. Are there any other statements that I can execute against my captured profiler table that can us facilitate troubleshooting/fixing? Any help will be greatly appreciated. Thanks. V1rt
It is doing a clustered index scan to find the row (in the source table, presumably) before inserting it into the archive table. Look at the WHERE clause of the statement (if you don't know what the statement is, you may have to use Profiler to capture it). Your source table needs an index to satisfy this WHERE clause. Also your archive table has a clustered index on it - for heavy inserts, it may well be better to drop this first, then do the archiving, then recreate it. It sounds like your vendor needs a few lessons in SQL Server ?! Tom Pullen DBA, Oxfam GB
one still needs to explain the difference in time between the vendor test and your test. the plan indicates that Enclosure is a small table? is it heavily accessed ? if so, try a NOLOCK hint on it try an index on Enclosure columns (Barcode,ENCLOSURENUMBER) index on TABLEFIELDAUDIT columns (SUBKEY1,SUBKEY2,TABLENAME) if TABLEFIELDAUDIT already has many indexes, try changing an existing column
hi thomas, I'm interested in the archive table clustered index dropping you have mentioned. I actually want to learn too. How do we drop this clustered index then? Also can you tell me what exactly to drop? Also, before I drop it, how do I display the clustered index, is there a command? hi joechang, You told me that if TABLEFIELDAUDIT already has many indexes, try changing an existing column. What do you mean changing an existing column? Thanks guys, This really helps. V1rt
Find out what the index is called (e.g. by running sp_help <tablename><img src='/community/emoticons/emotion-5.gif' alt='' />, then run <br /><br />DROP INDEX <tablename>.<indexname><br /><br />Make sure you drop any other Nonclustered indexes first and recreate them AFTER recreating the clustered index once you have done your archiving. (order is important!) It'll save you hassle if you script the index creation before dropping, save the script, and use it after. (To script a table, right-click it in EM, Generate Scripts).<br /><br />I think Joe meant change the columns in the indexes on TABLEFIELDAUDIT, but I'm sure he can answer that.<br /><br />Hope this helps. If you're unsure about the index create/drop syntax, (or ALTER TABLE.. ADD CONSTRAINT for primary keys) check Books Online, it's all laid out clearly in there!<br /><br />Tom Pullen<br />DBA, Oxfam GB
Am I correct that adding more and more index makes the search really fast but affects INSERT's performance? By the way, running this select count(*) from apptable.tablefieldaudit gives me the total count of that table which means, that the table exists. But if I run this, sp_help apptable.tablefieldaudit I get this error, Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '.'. If I do it like this, sp_help tablefieldaudit I get the error of this Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71 The object 'tablefieldaudit' does not exist in database 'DBARC'. Any ideas?
Who is the owner of the table? If it's dbo, just do sp_help <table> If it isn't run sp_help 'owner.table' Yes, having lots of indexes helps different queries run fast but will hurt inserts (eventually, but it's suprising what you can sometimes get away with). Tom Pullen DBA, Oxfam GB
Hey guys,<br /><br />I found this query to be running for 221000 milliseconds.<br /><br />Select FOLDERS.*,CURRLOCATION <br />from FOLDERS (NOLOCK) <br />INNER JOIN TUAUXILIARY (NOLOCK)ON FOLDERS.BARCODE=TUAUXILIARY.BARCODE <br />WHERE CREATEDATE<='8/14/2004' AND TUAUXILIARY.CURRLOCATION='SA'<br /><br />I opened the Indexes in QA(Left pane) for TUAUXILIARY table and found this:<br />BOXID<br />CURRLOCATION<br />PERMLOCATION<br /><br />In the TABLEFIELDAUDIT, I found these indexes,<br />TABLEFIELDAUDIT_tablename<br />TABLEFIELDAUDIT_keyvalue<br />TABLEFIELDAUDIT_subkey1<br />TABLEFIELDAUDIT_subkey2<br /><br />In the FOLDERS table, I found these indexes,<br />FOLDERS_DEPARTMENTNUMBER<br />FOLDERS_FILEOWNERNUMBER<br />FOLDERS_FOLDERNUMBER<br />FOLDERS_OFFICENUMBER<br />FOLDERS_PRIMARYNUMBER<br />FOLDERS_RECORDID<br />FOLDERS_SCHEDULEDARCHIVEDATE<br />FOLDERS_SCHEDULEDDESTROYDATE<br />FOLDERS_SCHEDULEDREVIEWDATE<br />FOLDERS_SCHEDULENUMBER<br />FOLDERS_SECONDARYNUMBER<br />FOLDERS_STATUS<br />FOLDERS_TERTIARYNUMBER<br />FOLDERS_FIELD1<br />FOLDERS_FIELD2<br />FOLDERS_FIELD3<br />FOLDERS_FIELD4<br />FOLDERS_FIELD5<br />FOLDERS_FIELD6<br />FOLDERS_FIELD7<br />FOLDERS_FIELD8<br />FOLDERS_FIELD9<br /><br />Is this also the reason why the INSERT is taking a long time? INSERT is insert to tablefieldaudit table btw.<br /><br />And now, this is the INSERT statement that took 36 seconds of Duration and 9 seconds of CPU time:<br /><br />INSERT INTO dbarc.apptable.TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,KEYVALUE,USERID,SUBKEY1, SUBKEY2) <br />SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,E.RECORDID AS KEYVALUE,USERID,SUBKEY1, SUBKEY2 <br />FROM TABLEFIELDAUDIT <br />INNER JOIN dbarc.apptable.ENCLOSURE E <br />ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER <br />WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'<br /><br />Also, the second TABLEFIELDAUDIT is coming from another DB(not dbarc).<br /><br />Thanks again guys. I hope I am on the right path <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />V1rt<br />
Additional info and may be better <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />I used sp_help to get the Indexes values below.<br /><br />TABLEFIELDAUDIT table (source and target db, the same)<br />---------------------<br />PK_TABLEFIELDAUDITclustered, unique, primary key located on PRIMARYSEQNO<br />TABLEFIELDAUDIT_KEYVALUEnonclustered located on PRIMARYKEYVALUE<br />TABLEFIELDAUDIT_SUBKEY1nonclustered located on PRIMARYSUBKEY1<br />TABLEFIELDAUDIT_SUBKEY2nonclustered located on PRIMARYSUBKEY2<br />TABLEFIELDAUDIT_TABLENAMEnonclustered located on PRIMARYTABLENAME<br /><br />TUAUXILIARY table<br />-----------------<br />BOXIDnonclustered located on PRIMARYBOXID<br />CURRLOCATIONnonclustered located on PRIMARYCURRLOCATION<br />PERMLOCATIONnonclustered located on PRIMARYPERMLOCATION<br />PK_TUAUXILIARYclustered, unique, primary key located on PRIMARYBARCODE<br /><br />FOLDERS table<br />-------------<br />FOLDERS_DEPARTMENTNUMBERnonclustered located on PRIMARYDEPARTMENTNUMBER<br />FOLDERS_FIELD1nonclustered located on PRIMARYFIELD1<br />FOLDERS_FIELD2nonclustered located on PRIMARYFIELD2<br />FOLDERS_FIELD3nonclustered located on PRIMARYFIELD3<br />FOLDERS_FIELD4nonclustered located on PRIMARYFIELD4<br />FOLDERS_FIELD5nonclustered located on PRIMARYFIELD5<br />FOLDERS_FIELD6nonclustered located on PRIMARYFIELD6<br />FOLDERS_FIELD7nonclustered located on PRIMARYFIELD7<br />FOLDERS_FIELD8nonclustered located on PRIMARYFIELD8<br />FOLDERS_FIELD9nonclustered located on PRIMARYFIELD9<br />FOLDERS_FILEOWNERNUMBERnonclustered located on PRIMARYFILEOWNERNUMBER<br />FOLDERS_FOLDERNUMBERnonclustered located on PRIMARYFOLDERNUMBER<br />FOLDERS_OFFICENUMBERnonclustered located on PRIMARYOFFICENUMBER<br />FOLDERS_PRIMARYNUMBERnonclustered located on PRIMARYPRIMARYNUMBER<br />FOLDERS_RECORDIDnonclustered, unique located on PRIMARYRECORDID<br />FOLDERS_SCHEDULEDARCHIVEDATEnonclustered located on PRIMARYSCHEDULEDARCHIVEDATE<br />FOLDERS_SCHEDULEDDESTROYDATEnonclustered located on PRIMARYSCHEDULEDDESTROYDATE<br />FOLDERS_SCHEDULEDREVIEWDATEnonclustered located on PRIMARYSCHEDULEDREVIEWDATE<br />FOLDERS_SCHEDULENUMBERnonclustered located on PRIMARYSCHEDULENUMBER<br />FOLDERS_SECONDARYNUMBERnonclustered located on PRIMARYSECONDARYNUMBER<br />FOLDERS_STATUSnonclustered located on PRIMARYSTATUS<br />FOLDERS_TERTIARYNUMBERnonclustered located on PRIMARYTERTIARYNUMBER<br />PK_FOLDERSclustered, unique, primary key located on PRIMARYBARCODE<br /><br />ENCLOSURE table<br />---------------<br />IX_ENCLOSUREclustered located on PRIMARYBARCODE, ENCLOSURENUMBER<br />PK_ENCLOSUREnonclustered, unique, primary key located on PRIMARYRECORDID<br /><br /><br />select count(*) from enclosure --20163781<br />select count(*) from tuauxiliary --2737407<br />select count(*) from TABLEFIELDAUDIT --23689028<br />select count(*) from FOLDERS --2737407<br /><br />--target TABLEFIELDAUDIT (different db, dbarc)<br />select count(*) from TABLEFIELDAUDIT --150<br /><br />
Hi ya, I'd say that on the tuauxiliary table you need an index on the combined columns ( currlocation, createdate, barcode ) Cheers Twan
Hi Twan, Is the information below when I executed 'sp_help tuauxiliary' not considered an index? TUAUXILIARY table ----------------- BOXID nonclustered located on PRIMARY BOXID CURRLOCATION nonclustered located on PRIMARY CURRLOCATION PERMLOCATION nonclustered located on PRIMARY PERMLOCATION PK_TUAUXILIARY clustered, unique, primary key located on PRIMARY BARCODE However, I found out CREATEDATE is in the FOLDERS table and that is not in the information about my previous post. So how do I add just a single column to the index without affecting others? For the BARCODE and CURRLOCATION, it's there right? Thanks. V1rt
quote:INSERT INTO dbarc.apptable.TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,KEYVALUE,USERID,SUBKEY1, SUBKEY2) SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,E.RECORDID AS KEYVALUE,USERID,SUBKEY1, SUBKEY2 FROM TABLEFIELDAUDIT INNER JOIN dbarc.apptable.ENCLOSURE E ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690' Also, the second TABLEFIELDAUDIT is coming from another DB(not dbarc). Having just this specific query in mind you are going to benefit the most if you add SubKey1 as second column of index on TableName. I am talking about source TableFieldAudit table.
quote:TABLEFIELDAUDIT table (source and target db, the same) --------------------- PK_TABLEFIELDAUDIT clustered, unique, primary key located on PRIMARY SEQNO TABLEFIELDAUDIT_KEYVALUE nonclustered located on PRIMARY KEYVALUE TABLEFIELDAUDIT_SUBKEY1 nonclustered located on PRIMARY SUBKEY1 TABLEFIELDAUDIT_SUBKEY2 nonclustered located on PRIMARY SUBKEY2 TABLEFIELDAUDIT_TABLENAME nonclustered located on PRIMARY TABLENAMEDo you have this indexes on target TableFieldAudit table? Do you have any query that has condition on SubKey1 or SubKey2 without TableName = <something>?
HI ya, yes you're right if createdate is not in that table then you already have an index on the combined currlocation, barcode columns (since barcode is the clustered index) You could/should verify that this index is used by doing a showplan on that statement Cheers Twan
Hi mmarovic, On source and target TableFieldAudit, they have the same indexes when I executed sp_help. I don't see any query in the Profiler. Again, this is just the query I captured when I ran the .exe tool that the vendor created. Also, I didn't get what you mean by "add SubKey1 as second column of index on TableName". Thanks buddy. V1rt
quote:Also, I didn't get what you mean by "add SubKey1 as second column of index on TableName". It means: drop index TABLEFIELDAUDIT_TABLENAME go create index TABLEFIELDAUDIT_TABLENAME on TABLEFIELDAUDIT(TableName, SubKey1) go To execute this script you should arrange a downtime, hopefully short depending on your hardware. But before that, you should test configuration on dev environment to see if it helps.
Everything is better now, the single INSERT statement came from 40+ seconds downto 1-2 seconds per record. I don't know if there is still a better way to make it really faster. We dropped one index and everything became better.
