SQL Server Performance

sql svr2k on ms vs2k5

Discussion in 'Performance Tuning for Hardware Configurations' started by pajonz, Feb 14, 2005.

  1. pajonz New Member

    Hi

    I was wanting your opinions on this subject. We have an experienced client, who have put our application which uses sql server2000 on a new box. They have started complaining that when ever someone does a new bill create in the application the sqlservr.exe jumps to 99%.
    From this point on the performance degrades and they end up having to reboot said server machine.

    Found out yesterday that they have put the sql server installation on a Microsoft Virtual Server 2005.

    I saw the post on a similar subject with VMware and was wondering if this might have something to do with it.?

    Does anyone have any comments?

    thanks very much
    john
  2. satya Moderator

    A high CPU usage rate can indicate performance bottlenecks. Consider doing any of the following to lower CPU usage. Allocate resources more efficiently (for example, dedicate a computer to run an instance of Microsoft SQL Server)
    - Identify resource-intensive applications and optimize those applications
    - Reduce the workload on the system by query tuning or improving indexes to reduce I/O and, subsequently, CPU usage

    The most common tools required to investigate high CPU utilization are the System Monitor (a.k.a. the Performance Monitor), SQL Profiler, and the Blocker Script from this KBAhttp://support.microsoft.com/?id=271509.

    Check System Monitor with the following counters:

    • Processor --> % Processor Time
    • Process --> % Processor Time (all processes)
    • Thread --> % Processor Time (all threads of the SQL Server Instance)
    • SQL Server --> SQL Statistics/SQL Compilations/sec
    • SQL Server --> SQL Statistics/SQL Re-Compilations/sec
    • Server Work Queues --> Queue Length

    Resolving high CPU utilization issues can be very time-consuming, especially when you don't know where the problem lies. Try to solve the problem, and not the symptoms. With proper optimization techniques, such as adding proper indexes, redesigning badly written queries, and so on, you can avoid almost all of these issues.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. mmarovic Active Member

    Have you run trace to catch cpu consuming query? Since it is your application you may know how query is written. Do you use cursors for bill creation? Do you use stored procedures?
  4. pajonz New Member

    thanks for the replies

    at this stage I just wanted to know if there was any reason that virtual machine and sql server could not be readily deployed with tuning in either environment.

    The machine is at a client site and was built by their IT guys. I don't have access so cannot do most of what has been suggested. I will however try and get the client to commit time to investigate this.

    I have been advised that they have NOT upgraded our application and its the same version running on the previous standalone server.

    thanks
  5. satya Moderator

    Until unless you get the figures from SYSMON (PERFMON) it may not be easy to guess the performance issues.

    I will try to reproduce the situation at my end and get back to you.
    But at the end of the day it relies on the set of queries and their execution plan for optimum perforamnce.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. lancer New Member

    We are also having significant SQL 2000 performance issues, but running VM Ware ESX server. After speaking with Microsoft experts, they advised NOT running SQL Server on ANY virtual environments (including Virtual Server) as SQL is optimised to perform for a specified CPU and not temporary CPU time on different physical CPUs. This is unofficial and only for transaction processing systems.

    Our issue is more of an I/O or SAN performance issue.
  7. satya Moderator

    True and I feel SQL2K5 is optimized to run on the virutal servers, you can use Virtual server concept on the test bed and not on the production environment on a enterprise network.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page