SQL Server Performance

archiving very slow

Discussion in 'Performance Tuning for DBAs' started by v1rtu0s1ty, Nov 12, 2004.

  1. v1rtu0s1ty New Member

    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
  2. thomas New Member

    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
  3. joechang New Member

    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
  4. v1rtu0s1ty New Member

    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
  5. thomas New Member

    Find out what the index is called (e.g. by running sp_help &lt;tablename&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' />, then run <br /><br />DROP INDEX &lt;tablename&gt;.&lt;indexname&gt;<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
  6. v1rtu0s1ty New Member

    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?
  7. thomas New Member

    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
  8. v1rtu0s1ty New Member

    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&lt;='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 />
  9. v1rtu0s1ty New Member

    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 />
  10. Twan New Member

    Hi ya,

    I'd say that on the tuauxiliary table you need an index on the combined columns

    ( currlocation, createdate, barcode )

    Cheers
    Twan
  11. v1rtu0s1ty New Member

    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
  12. mmarovic Active Member

    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.
  13. mmarovic Active Member

    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 TABLENAME
    Do you have this indexes on target TableFieldAudit table? Do you have any query that has condition on SubKey1 or SubKey2 without TableName = <something>?
  14. Twan New Member

    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
  15. v1rtu0s1ty New Member

    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
  16. mmarovic Active Member

    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.
  17. v1rtu0s1ty New Member

    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.

Share This Page