SQL Server Performance

URGENT SIMPLE HARDWARE REQUIREMENT PROBLEM

Discussion in 'Performance Tuning for Hardware Configurations' started by shisir78@yahoo.com, Dec 7, 2004.

  1. shisir78@yahoo.com New Member

    DEAR ALL THE MEMBER
    I M WORKING ON A DATABASE PROJECT WHERE ON THE WEEKLY BASIC THE PROCESSING IS DONE ON THE LOCAL SYSTEM AFTER DOWNLOADING THE BACKUP DATABASE FROM ONLINE. ON THE WHOLE WEEK THE RECORD IS INSERTED ON A TEMP TABLE AND ON THE TIME OF PROCESSING IT INSERTED ON THE ACTUAL TABLE AND THAT TIME I USED TO DO SOME CALCULATION BUT ON TIME OF INSERT ON THE REAL TABLE IT USED TO CHECK ROW BY ROW OF THAT ACTUAL TABLE. AND WHEN NOW THE ACTUAL TABEL ROW IS MORE THAN 3000 I AM GETTING TOOO MUCH SLOW PERFORMANCE. IT TAKES AROUND 80 SECONDS TO INSERT ON ROW AND WEEKLY I HAVE ROUGHTLY 700 RECORD THAT MEAN IT TAKES ME 80*700/60/60=15 HOURS THEORY. BUT TAKE MORE TIME. I KNOW THIS IS A SIMPLE HARDWARE PROBLEM CAN ANY BODY HELP ME!!!!

    THIS IS URGENT I HAVE ONLY FEW DAYS TO GIVE DECESION FOR NEW HARDWARE.

    MY LOCAL SYSTEM IS
    PIV 2.2GHz
    512 MB RAM
    80GB ATI HARDDISK
    DATABASE SIZE IS 6MB
    WINDOWS 2000 WITH SP4
    MSSQL 2000 WITH SP3


    WHAT I HAVE RECOMENED IS
    XENON DUAL PROCESSOR,
    2GB RAM


    WHAT ONLY WANT TO KNOW THAT HOW MUCH TOTAL TIME IT TAKES TO FEATCH THE CURSOR HAVING AROUND 50000 RECORD ON THE XENON DAUL PRCOESSOR SYSTEM.



    SHISIR [V]
  2. biged123456 New Member

    Before you go upgrading hardware, you should determine if and where you have a hardware bottleneck. It is entirely possible your bottleneck is at the hard drive level (not enough spindles to handle the I/O). A good article on this site is athttp://www.sql-server-performance.com/sg_sql_server_performance_article.asp.

    You also may want to look into using bulk imports (although the number of inserts doesn't sound too high).
  3. Luis Martin Moderator

    Al least check with Performance Monitor:
    Disks, Memory and Processor.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  4. joechang New Member

    for such low performance, i would check 2 things,
    1. the status of the OS disk cache
    2. the status of the BIOS setting for the hard drive

    1. try Computer Management, disk administration, right click the C: drive,
    i don't use w2k anymore, so some one else needs to verify this
    2. the BIOS disk property may not have 32-bit mode enabled
  5. shisir78@yahoo.com New Member

    Thank u all for attention


    on performance moniter
    CPU = 100%
    SQL Server Cache Hit Ration = 91.xxx%
    and disk activity is almost none.


    '=================================================================================================================
    '=================================================================================================================
    With rsCursor
    .CursorLocation = adUseServer
    .Open "SELECT transid FROM tblTrans", cnn, adOpenForwardOnly, adLockReadOnly
    End With


    rsCursor.MoveFirst

    '--fetch first record
    Do While rsCursor.EOF = False
    ' if i even remove my some calculation code it takes 44 seconds to loop to another record
    ' why it is taking such long time.
    DoEvents
    rsCursor.MoveNext
    Loop

    etime = Now()
    'Debug.Print stime & " : " & etime
    Debug.Print DateDiff("s", stime, etime)
    '=================================================================================================================


    the table tblTRans have around 3100 records. why it takes only 44 second for just a loop. it is fast till the around 1000 records. i think that i am less prcoessor power.


    shisir

  6. Luis Martin Moderator

    I think you have memory problems.

    Check: Performance Monitor:
    Memory: Pages/sec.
    SQL Server Buffer Manager: Page life expectancy.

    If for a while (10 minuts or more) you have more than 20 pages/sec in average and/or Page Life Expectancy y lower than 300, you need more memory.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  7. Chappy New Member

    For a database of only 6 meg this does sound very odd to be having these performance problems.

    Im sure new hardware would help, but to be honest Id be looking a little deeper before buying..
    I know you seem pretty convinced the problem is hardware, but is that the root cause of the problem ?

    Half a gig of RAM seems pretty adequate to me for a 6 meg database, but...

    What other activity is occurring on the server?
    What other apps are running on the server?
    CPU is being pinned, but very little disk activity, and buffer cache ratio seem reasonable if a little low
    Check to make sure you are not restricting the amount of memory being used by SQL.

    Look in task manager, does sql server appear to be competing with other processes for cpu time, during the processing?

    What is the execution plan of your insert?

    Currently you select a large number of records for your cursor. Have you considered doing the cursor in smaller batches, perhaps remembering the last transid to be processed and looping in a while loop until there are no more remaining.

    Finally I notice you are accessing this from client code. Can you reproduce the performance problems in query analyser using pure T-SQL? Just replicate the cursor code and see if the same performance problems exist in T-SQL, so you can eliminate any problems with your client libs etc
  8. shisir78@yahoo.com New Member

    hello,

    on performance monitor some time it goes to average upto 28 pages for more not most time it is 0 pages and Page Life Expectancy is grows constantaly. no other application is running. and CPU utlization is 99-100%


    can anybody give me how much time takes on their system to loop 3000 record with only 2 integer field without any calculation on the field.


    shisir
  9. Chappy New Member

    What is the network connection like between your client and server ? Or is this app running locally on the same machine as the sql server?

    If the sql server is remote, have you tried using a client side cursor ? Ie, rsCursor.CursorLocation
  10. Twan New Member

    Hi shishir,

    the amount of time taken depends on lots of things... using something like QA to get 3000 rows on a client PC takes < 1 sec for me.

    When you run SQLProfiler to capture any RPC:Completed and Batch:Completed with duration/cpu > 100ms, what do you see? As asked by Chappy can you confirm that there is definitely nothing else running on or against the server, and that you are saying that with a dedicated server for your purpose and that one statement you are having the problems that you describe...?

    Cheers
    Twan
  11. shisir78@yahoo.com New Member

    no i have tested same local machine

    as i have already said that only complete the whole loop for 3040 records takes more than 50 seconds on my machine. which is extermaly slow for me now. coz every time when record insersted on tblTrans it should loop throught the whole table (tblTrans) for once. so on every time the record increases the performance decreased dramttically. on the time of when i have only 1000 records it was fast only takes 4-8 seconds but when i have now only 3040 records it takes 50 seconds (for loop only). and the current db sise is around 7mb.


    i have the problem is to loop throught the records set on the increasing records is very slow. since i have only PIV 2.2GHz
    512 MB RAM
    80GB ATI HARDDISK
    DATABASE SIZE IS 6MB
    WINDOWS 2000 WITH SP4
    MSSQL 2000 WITH SP3

    what i need to increase the faster loop on recordset. i have previously the coded on T-SQL for that procedure but now converted on ADO.

    shisir








  12. Jaap New Member

    If your loop is based on a cursor in T-SQL i'm not suprised it is getting this slow. My recommendation would be to keep as far from cursors as possible since they are notoriousy slow.

    Jaap Kramer
  13. Twan New Member

    HI ya,

    can you please confirm that there is absolutely nothing else going on on the server. i.e. you are the only user and the cpu usage is all used by the SQLServer.exe process The response times that you're getting seem bizarrely slow...

    also use SQLProfiler to capture all RPC:Complete and TSQL:BatchComplete with duration > 100ms, and then let us take a look at this trace

    Cheers
    Twan
  14. Singaravelan New Member

    Do you have index on the table?
    Did you considering the loop to a while loop in case you are doing with Cursor?

Share This Page