SQL Server Performance

Another Win2k-SQL Server 2000 Question

Discussion in 'Performance Tuning for DBAs' started by fredo3000, Feb 4, 2004.

  1. fredo3000 New Member

    I have a 72GB DB running on Win2K, SQL 2000, 8 processor server, 4GB of RAM, a SAN of 300GB and a third party appls, i already check for indexes, and statistics. I run SQL Profiler and found that Audit Logout is talking aprox 21000 and the applz most everyday have timeout problems + I used de perfmon and found out that i have a Ave Disk Queue Read that max up to 1400...
    is this a storage problem or a application problem?
    fredo3000, Feb 4, 2004
    #2

  2. fredo3000 New Member

    I forgot to mention that is a 72GB running on only a RAID 5, with 1 mdf and 1 ldf
    fredo3000, Feb 4, 2004
    #2

  3. Twan New Member

    Hi Fredo,

    use Profiler to capture RPC:Completed and TSQL:BatchCompleted filtering for duration more than say 100ms, If there are a number of queries taking substantially longer than than, then you're talking about an application performance problem. If there are none, then a configuration/OS/hardware problem is more likely

    Cheers
    Twan
    Twan, Feb 4, 2004
    #2

  4. Chappy New Member

    I think the duration in the audit logout event indicated the total duration of that connection, i could be wrong but 21000 is not necessarily anything to worry about in itself
    Chappy, Feb 5, 2004
    #2

  5. fredo3000 New Member

    ok,. i got it..
    how about the disk queue for the san, its me or its a bit high?
    its just that is my mayor DBA work and i had never seen something like this.
    fredo3000, Feb 5, 2004
    #2

  6. Luis Martin Moderator

    How about Avg. Disk Queue Length?


    Luis Martin
    Moderator
    SQL-Server-Performance.com
    Luis Martin, Feb 5, 2004
    #2

  7. fredo3000 New Member

    it was an ave of 400, max 1400
    fredo3000, Feb 5, 2004
    #2

  8. Chappy New Member

    I just noticed you mention you run 3rd party apps on the server too?
    What are these apps and could they be affecting overall system performance ? Do they access the database also, maybe theyre causing excessive locking?
    Chappy, Feb 5, 2004
    #2

  9. fredo3000 New Member

    well actually the apps runs in a separete server.
    fredo3000, Feb 5, 2004
    #2

  10. Luis Martin Moderator

    I never see those values, Avg. Disk Queue Length (reads and writes) must be lower than 2, to get good performance.
    If there is not hardware problems, I suggest to run Profiler to find optimizations.
    Also monitor Tables Full scans and Index Scan to check if indexs are using or not.
    I can't believe RAID 5 is causing that queue. With RAID 0 or 10 you cant get more performance, but first you have to see all picture.


    Luis Martin
    Moderator
    SQL-Server-Performance.com
    Luis Martin, Feb 5, 2004
    #2

  11. Luis Martin Moderator

    I never see those values, Avg. Disk Queue Length (reads and writes) must be lower than 2, to get good performance.
    If there is not hardware problems, I suggest to run Profiler to find optimizations.
    Also monitor Tables Full scans and Index Scan to check if indexs are using or not.
    I can't believe RAID 5 is causing that queue. With RAID 0 or 10 you cant get more performance, but first you have to see all picture.


    Luis Martin
    Moderator
    SQL-Server-Performance.com
    Luis Martin, Feb 5, 2004
    #2

  12. fredo3000 New Member

    Its pretty amazing because hardware is excelent, and in perfmon the chart is always on top
    also about 400 users quering the same server
    fredo3000, Feb 5, 2004
    #2

  13. fredo3000 New Member

    I was able to down the ave disk queue to 50+ but still i have performance problems.
    Anything else?
    fredo3000, Feb 10, 2004
    #2

  14. Luis Martin Moderator

    How about to reduce parallelism to one processor?.
    This tip is for performance. Regards 50+, is all the time?


    Luis Martin
    Moderator
    SQL-Server-Performance.com
    Luis Martin, Feb 10, 2004
    #2

  15. fredo3000 New Member

    thats an ave of a couple of days of monitoring.
    but from time to time goes up and down
    fredo3000, Feb 10, 2004
    #2

  16. Luis Martin Moderator

    I think there is a lot of index and statistics to implementate.
    I don't know if you can implementate changes after running Profiler and find better index, due 3rd party software.
    I do that in all my customer, and get very good performance. I think 3rd party has nothing to say, because I don't change a single line, only apply indexs and statistics.
    I believe data belongs to customers, code belong to 3rd party software.

    Luis Martin
    Moderator
    SQL-Server-Performance.com
    Luis Martin, Feb 10, 2004
    #2

  17. ChrisFretwell New Member

    How is your data fragmentation? Badly fragmented data can cause more data reads, more disk load, more queing etc. Its worth checking.

    Chris
    ChrisFretwell, Feb 10, 2004
    #2

  18. fredo3000 New Member

    Yes, it was badly fragmented i did a reindex and help a lot,
    now that i had check most of the optimization options im starting to think that its a app problem
    fredo3000, Feb 10, 2004
    #2

Share This Page