SQL Server Performance

19 minutes on server, 2 minutes on desktop...

Discussion in 'Performance Tuning for DBAs' started by andyk, Dec 1, 2003.

  1. andyk New Member

    Hi everyone! This is my first post on this great forum.

    I discovered a weird performance problem on our SQL server this afternoon.
    A stored procedure needed 19 minutes to execute. This is on a database that#%92s only 300MB large and no table has more than 70000 rows. During these 19 minutes the cpu was only loaded to about 50-60%.

    When our developer tested the same sp on the same data on their own desktop computer it only needed 2 minutes to execute. The data was copied to the desktop with a DTS job.

    Database server specs:
    OS: Windows 2000 Server SP3
    DB: SQL Server 2000 SP1
    CPU: 1 x PIII 933
    Memory: 2GB
    Database files are stored on a fibre channel san.

    Every night we run a maintenance job that Reorganize data and index pages.

    Performance counters:

    SQL Sever:Memory Manager
    Target Server Memory: 1677944 KB
    Total Server Memory: 654400 KB

    Memory
    Available KB: 1173440 KB
    Memory pages/sec: 0.000

    SQL Server Buffer Manager:
    Buffer cache hit ratio: 99.998

    There is almost 1GB difference between Target Server Memory and Total Server Memory.
    Why is that? There is plenty free physical memory left for the sqlserver process to grab.

    Does anyone have any clues on what is wrong here?

    Thanks in advance!
    Andreas


  2. joechang New Member

    are the server specs quoted above for the system that took 19min or the developer desktop that took 2min?

    also, are there differences in the execution plans between the 2 system?
  3. Luis Martin Moderator

    Is SQL server dedicated?.
    Try to see what other applications are taking memory.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  4. gaurav_bindlish New Member

    Is there is a reason for SQL Server not having latest service pack?

    I assume u have the service pack applied for the slammer worm.....

    As discussed above can you let us know if the execution plans are different?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. andyk New Member

    quote:Originally posted by joechang

    are the server specs quoted above for the system that took 19min or the developer desktop that took 2min?

    also, are there differences in the execution plans between the 2 system?

    The specs are for the server. The desktop has PIII 1000, 384MB, and IDE-drive.

    I have not yet been able to compare these but I will tell the developer to do this.

    // Andreas
  6. andyk New Member

    quote:Originally posted by LuisMartin

    Is SQL server dedicated?.
    Try to see what other applications are taking memory.

    It's a dedicated SQL Server.

    // Andreas
  7. Luis Martin Moderator

    How about Update Statistics with fullscan?.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  8. andyk New Member

    quote:Originally posted by gaurav_bindlish

    Is there is a reason for SQL Server not having latest service pack?

    I assume u have the service pack applied for the slammer worm.....

    As discussed above can you let us know if the execution plans are different?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard


    The only reason is that we don´t want to mess with a well functioning server (at least it used to be..) and it needs to be up 24*7.

    Could SP3 solve this issues?

    My main concern right now is way does Target and Total Server Memory counters differ so much?

    // Andreas
  9. Luis Martin Moderator

    Take a look task manager to find who are using memory.
    If target memory mainten that value for a while, and SQL memory so, is wierd.
    If both up and down big copy from server to other WS cause that.
    Look to EV to find any razon, hard or soft is not working well.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  10. andyk New Member

    quote:Originally posted by LuisMartin

    Take a look task manager to find who are using memory.
    If target memory mainten that value for a while, and SQL memory so, is wierd.
    If both up and down big copy from server to other WS cause that.
    Look to EV to find any razon, hard or soft is not working well.

    Task manager shows nothing out of the ordinary. Just the normal processes and sqlsrvr uses 646 940KB memory. The second largest memory consumer is explorer.exe at 9MB.

    The target and total memory counter values have not changed much during the last 12 hours.

    EV is completely normal, no errors at all.

    // Andreas
  11. Luis Martin Moderator

    Well, I suggest to install last service pack sp3a, there is a lot of fix from sp1 to sp3, sp3a is just for virus like Gaurav said.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  12. joechang New Member

    i would now suggest that you compare the execution plans on each system,
    any differences here could easy explain the performance difference.

    also check disk loading on the san,
    some some strange reason, some san vendors like to sell extremely expensive storage solutions, then recommend the most horrible possible performance configuration, such that the net performance is less than that of a cheap IDE drive

    finally, when you run this query, how much information comes back in the QA messages window?
  13. andyk New Member

    quote:Originally posted by LuisMartin

    Well, I suggest to install last service pack sp3a, there is a lot of fix from sp1 to sp3, sp3a is just for virus like Gaurav said.

    SP3 is now installed! It's 2 in the morning here so I thought I might as well do it now while I'm still awake and we don´t have so many visitors onour website.

    What is good and bad values for % disk time and avg. disk que length?

    // Andreas
  14. joechang New Member

    % disk time is irrelevent, long term avg. disk que length should be less than ~2 per disk, but may burst much higher (during check points)
    on log disk, avg sec/write should be <0.001sec
  15. Twan New Member

    It is also kind of odd that total server memory is around 600MB yet the target server memory (i.e. the amount SQL wants is 1.5GB) The properties of the SQL server haven't limited the amount of memory that SQL is allowed to consume?

    Cheers
    Twan
  16. ftine New Member

    Hi, I had the exact same problem last week. I even tried to restore the same database on both computers.
    I solved with the sp_updatestats that updates all the statistics on the database.
    Note that I had checked the showcontig and the statistics of the all the involved tables and they were all OK. It only solved the problem when I ran the sp_updatestats.
  17. satya Moderator

    RUnning update stats occasionally on the tables will help, apart from if you have maint.plans to reorg. the data and index pages.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page