SQL Server Performance

When do you know you've outgrown SQL7/NT4?

Discussion in 'Performance Tuning for Hardware Configurations' started by cdooer, Oct 28, 2004.

  1. cdooer New Member

    Hello. We currently have our core CRM application running on SQL 7, and NT4.0. We did a hardware upgrade about a year ago, because users (around 400) were complaining about deathly slow performance. Once the hardware upgrade was complete, the users were very happy with the performance improvements. The database server is now a dual 3GHz (Xeon) Proliant, with 3GB of RAM. Now, after 10 months, users are again experiencing deathly slow performance.

    Now that that is out of the way, I was just wondering if there were any real signs that we have outgrown SQL7/NT4.0, and need to go to 2K (possibly Enterprise). I would like to bring some real stats to the management team, and be able to concretely tell them that we have outgrown the current OS and SQL revision. Are there performance counters I can look at? Any advice would be much appreciated.

  2. joechang New Member

  3. Luis Martin Moderator

    I've installed severals times SQL 2000 replancing SQL 7.0 with NT4.0.
    All process run faster than SQL 7.0. Much better with w2k. (Test and customers oppinions)
    One recomendation: if are planing to install SQL 2000 with NT4.0 over existing SQL7.0, I recomend to detach database (or backup, etc), remove SQL 7.0 and install fresh SQL 2000. I never get success with upgrade process.

    Luis Martin

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

  4. joechang New Member

    i would highly recommend upgrading the OS to w2k, or even better w2k3
    i would have thought that benefiting from S2K over S7 would require tuning to the new capabilities of s2k, but i have never verified this
  5. cdooer New Member

    I have been doing some more digging, and I have noticed that the % Disk Time for the array that the database sits on often goes to 100% and stays there for 10 seconds at a time. This seems to be during reads, not writes. The Current Disk Queue Length doesn't seem to indicate a problem though, bouncing between 0 and 4 (two physical disks in the array). Our RAID controller has 192MB of cache on it, and is split 50/50 read/write. Any ideas what would cause the % Disk Time to be so high?
  6. satya Moderator

    How about placement of data and log files on the system?
    What was previous memory utilization for the server. How is the server primarily used?
    Is memory utilization constant or does it rise to 1.5 GB at certain times.
    How much memory total memory is on the server and how much of it is allocated to SQL Server?

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Chappy New Member

    Its worth running a profile of the sort of queries CRM is running. Im assuming its an off the shelf package, and not an inhouse development. There are a long list of commercial CRM softwares which are notoriously badly written in terms of sql performance
  8. cdooer New Member

    The database and the transaction logs are on two separate arrays. Each array consists on two 146GB disks, in a RAID 1+0 configuration. The array controller is a fairly new Compaq Smart Array 6400, which really hauls. The server is dedicated to SQL.

    Memory utilization remains constant at 1.8GB. SQL is configured to use RAM dynamically. I am monitoring quite a few things right now, like Full Scans/sec, and Page Splits/sec, but I'm really not sure what numbers are acceptable. The Buffer Cache Hit Ratio is above 99%. With the SQL counter Cache Size (pages), if I multiply the number I am getting by 8K, will this give me the amount of RAM SQL actually needs? If so, this number is very low, around 150MB.

    There is a lot of suspicion surrounding the CRM application, since it was bought 'of the shelf' around 5 years ago. Our developers have done a lot of customizing, but the application has a lot of 'core' code that can't be touched.
  9. Chappy New Member

    Maybe its time to consider an upgrade. If the application code is very poor, moving to sql 2k might help a little bit, but in the end the application will start bogging you down again
  10. satya Moderator

    The PERFMON stats doesn't seems to be suspicious in this case and as Chappy refers it may be time to consider upgrade or fine tune from the CRM application.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. aawara New Member

    Its one of the common issues in database envoirnment. A lot of times (about 80% and up), the application tema comes and complain that the server was running fine and not its not. In almost all the cases its the code which was running on a small database (sometimes even without indexes) and do fine. Its only when the database grows bigger, They see an issue. Look at your queries and do statistices IO and maybe look at your joins. A deadly combination of high reads and poor join results into something like this. My recommendation is to look at your queries. If your reads are high, I would do a cache hit ratio, disk reads (physical vs logical). I would recomend a query tunning.
  12. satya Moderator

    See if SQL Server is doing a lot of I/O. Tools like SQL Profiler and Blocker Script output can tell you if the query(s) are resulting in these large I/Os, and tuning or optimizing these individual queries from a statistics and indexes point of view may help from the disk I/O perspective.

    An improper index strategy can add to a disk subsystem workload. For example, instead of doing an Index Seek, the SQL Server Optimizer may choose to do a Table/Index Scan, resulting in a lot of unnecessary I/O.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. Argyle New Member

    I would strongly recommend to move away from NT4 since it's reaching end-of-life at the end of this year and no more hot fixes or security patches will be available.


    "Windows NT Server 4.0 incident and security related hotfix support is available through 31-Dec-2004. Non-security related hotfix support is available only through a custom support contract after 31-Dec-2003"
  14. cdooer New Member

    The CRM application is Janna (now owned by Siebel). We have done quite a bit of customizing, but there is a lot of core code we can't touch. The Buffer Cache Hit Ratio still sits at around 99.7%, so we can rule out a memory problem. Both CPU's sit at around 35-40% all day. There are spikes, but not for any signifigant amount of time. Looks like it's time to tell the DBA's (again) that they need to do some more work on their queries.

    Thanks for all the replies.
  15. Luis Martin Moderator

    In my experience, severals times with SQL 7.0 SP4, some queries take long time and huge reads.
    Once converted to SQL 2000 SP3 all those queries work fine without any additional index.

    Luis Martin

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

  16. satya Moderator

    True I agree with the gang to upgrade the OS & SQL to higher version to take advantage of optimization and performance gain. And you can see from Argyle's reference about NT life support from MS is nearing out.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  17. cdooer New Member

    I just heard from an HP Engineer who told us that Hyper-Threading should be disabled in the BIOS on our server. He says that this could hurt the performance when running Windows NT4.0/SQL7.0. Anybody have any comments on this, maybe an example that supports his claim?
  18. aawara New Member

    I agree for HT. Even win2k is not all that intelligent for HT. ONly win2003 is capable of handling complexity of HT. If at all suppport for HT is required, please consider Win2003. This is all I would say at this time. However, Application tunning remains on top of the list for any serious improvements. Just an opinion.
  19. mmarovic Active Member

    High CPU usage may come from different reasons, to mention just a few I had to deal most often:

    1. Cursors
    2. Sp recompilations because of temp table usage inside stored procedures
    3. Sp recompilations because of inconsistent capitalization of proc names when called from application
    4. Sp recompilations because of branching in the code

    To solve issue #2 mssql 2000 offers table variables and table functions that can replace temp tables and don't cause sp recompilations. Of course application has to use stored procedures and you have to be allowed to make changes in their code to take advantage of this feature.

Share This Page