SQL Server Performance

Dynamic Website is slow with SQL Cluster

Discussion in 'Performance Tuning for Hardware Configurations' started by Pitbull, Jan 8, 2004.

  1. Pitbull New Member

    Hello. My first post so be easy with me <img src='/community/emoticons/emotion-5.gif' alt=';)' /> Ok my scenario is as follows. My Dynamic website is slow and it seems to be from the Clustered machines. Currently my IIS box is running off both databases for testing. The SQL machines are on the same segments to avoid discrepencies in the network. Using .NET we connect to our SQL database (clustered and single proc tester). I have tested this on a single 1.2 processor DESKTOP(IDE) machine running SQL 2000 and our 2 Dual 1.2 Processor with 1 gig of ram Clustered with Raid 5.<br />Servers <br />1.2 Gig Proc 1 Gig Ram<br />Ultra 3 360 Clustered<br />10000 RPM drives<br /><br />The logs and data are separate but on the cluster. Stripped on different segments of the cluster. I have done things like shut off parallelism and also assigned 1 specific proc to SQL as oppose to allowing it the option of both. Our database is very small considering the horespower we have to run it (150mb). I have done some testing and the server doesnt seem to be taxed at all. I know clustering and RAID are for redundancy and usually at a cost of performance; but how much of a cost should I expect? <br /><br />Any help would be appreciated.<br />Thanks<br /><br />
  2. bradmcgehee New Member

    If I understand you correctly, you have a database running on a cluster that appears slow to you when you run your website app against it. But if then switch your website app to a non-clustered server, the website app runs faster? Is this correct?

    Assuming the above is correct, you first must ensure that you are testing apples to apples. In other words, are the hardware virtually identical, is SQL Server configured exactly on both systems, is the database identical on both systems?

    But your testing may not be the issue.

    What I would do first is to perform a performance audit of your cluster and web app, as described on this website. This will catch any basic issues. Next, do a Profiler trace to identify all long running queries, and then analyze them, one at a time, until you identify, and correct, any slow running queries.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. Twan New Member

    Also, can you confirm that when you are saying that you tested it on a desktop, you are not saying that both the IIS server and the database were running on the desktop. This again would be a major difference, which depending on the application architecture can have a significant implication

    Cheers
    Twan
  4. Pitbull New Member

    <br /><br />Yes the test system is a desktop machine. I do not have a spare same server to compare it with <img src='/community/emoticons/emotion-2.gif' alt=':D' /> To be honest the SQL on the desktop is installed with no tweaks to it. I doubt the clutster has been touched either. The database is the same on both systems and I am sure other then the clustering the setup is the same.<br /><br />I am going to check through your site as to performance audits. If it was anything different from perfomon I did checks and comparison as recommended by MS I will definitly check it out. I did notice on some of the queries the execution was done differently by the cluster as opposed to the desktop machine.<br /><br />My concern is paying 20k+ for a cluster server that gives me failover but less performance to a desktop machine for 1k.<br />
  5. Pitbull New Member

    The IIS box is connected to both SQL servers the test and the clustered. This is to refrain from the IIS box being a problem in the equation.
  6. satya Moderator

    To get optimum performance its better to seperate IIS and SQL server and not to keep on the same server.

    BTW what is the level of service packs on SQL, IIS & OS?

    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.
  7. Twan New Member

    Hi ya,

    Ok well if the only difference that you can think of is the database being on different hardware, then you'll have to follow Brad's suggestion of using Profiler and perfmon to compare the performance on the two machines. In perfmon check for the obvious things first, memory, disk, cpu, network.

    There has to be something that is not configured optimally or not functioning correctly... you could try comparing a file copy from the desktop to the webserver and the server to the webserver to make sure that there is no straight network connectivity issue (which could be misconfiguration or a network card problem)

    Good luck
    Twan
  8. Argyle New Member

    When you transfered the database into the cluster did you do it with a backup/restore? If so did you run sp_updatestats on the database afterwards?

    Edit:
    oops, didn't see it was an old topic.

    /Argyle

Share This Page