SQL Server Performance

Slow TSQL performance on Quad Machine

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by Captain DB, Feb 4, 2009.

  1. Captain DB New Member

    I run a TSQL query using SQL Management Studio and have noticed the performance is significantly degraded when running on a 4-processor (quad) machine then when running on a (2-processor) dual-core machine.
    It is a fairly simple query with a 2 way join that returns over 1 million rows. On a Intel Dual-Core 2.0 GHZ machine, the query takes ~14 seconds. On a Intel 4-CPU 3.2 GHZ machine, the query takes ~24 seconds. The results are always consistent.
    Both machines are running Windows 2003 with SP2. Both machines are running SQL Server 2005 with SP2. Both machines have 4 GIG of ram. Both machines have exactly the same database schema. The execution plans for the query on both machines are identical.
    I have noticed, however, that if I run the query remotely to the quad machine from the Dual_Core machine, the query takes ~14 seconds, the same time it would take running locally on a dual-core machine. So it doesn't seem there is a problem with the database or server component of SQL Server.
    I have configured SQL Server to use only 1 or 2 CPU's on the quad machine but it did not change the results.
    I have also noticed that in Task Manager, the System Idle time is about 60% on the Quad machine; the SQL Mgmt. Studio was getting about 30%.
    On the Dual-Core machine, the numbers were almost reverse: System Idle time is about 30% and SQL Mgmt Studio was about 60%. It's almost as if the process is not getting enough cpu time.
    Is there any explanation and resolution for this degraded performance on a 4-CPU system?
  2. MohammedU New Member

    Check the query plan on both machines and see what is the difference?
    It could be parallalism issue...check both servers have the same configuration about parallalism...
  3. Captain DB New Member

    The query plan on both machines are identical.
    The configuration settings for parallelism on both machines are also identical.
  4. Adriaan New Member

    The problem with parallellism is that sometimes the SQL Server engine will employ too many processors for a simple task, which can lead to longer execution times.
    If the parallellism setting on both servers is to use all available processors, then you can add OPTION (MAXDOP 1) as the last part of your query statement. This will force the server to execute the query with just one processor.
  5. Captain DB New Member

    Thanks for your advice.
    Unfortunately, I don't have access to the quad machine at the moment to try out your solution.
    But I did configure SQL Server via sp_configure to use only 1 CPU. I also used Task Manager to set the affinity for SQL Mgmt. Studio to use 1 CPU.
    However, it did not improve the performance.
  6. moh_hassan20 New Member

    can you run that script on both servers and post results
    -- clear SQL Server’s data and procedure cache , to be sure starting from a base level, normally it is used in test servers.

    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE



    SET STATISTICS IO ON
    SET STATISTICS TIME ON
    -- run your query
    SET STATISTICS IO off
    SET STATISTICS TIME off

  7. Bob L New Member

    I agree, clear the buffers to make sure both are being judged correctly.Others can chime in, but if everything else is equal, I would also look at I/O, if it is I/O intensive. Is the disk array as fast? Could be a bottleneck. Use SQLIO from Microsoft to check the two out.I just ran it for the first time on our main SQL Server (stand-alone) and our virtual SQL Server. The stand-alone's SAN is twice as fast.
  8. Captain DB New Member

    Yes, I was thinking about I/O as a bottle neck but shouldn't there be the same bottleneck when running the query remotely? The remote query ran as fast as on a dual-core machine.
    I ran Perfmon on Physical disk activity. When I recycled SQL Server, there was of course considerable disk activity. But when I kept re-running the query, perfmon showed 0 disk activity. Could this be because results were cached? There were over 1 million rows so that's a lot to cache.
    But I will try your suggestion when I get the chance.
    Thanks.
  9. Bob L New Member

    Another question, are they 32 or 64 bit? One 32 and one 64?
    Is one set to allow only a certain amount of memory to SQL Server (this can be set), or set for all it can use?
    These two situations would make a difference.
    Right click the instance, choose Properties, and choose Memory on the left side. Also note the "Minimum memory per query" setting for each in the Memory screen.
    Bob L
  10. Captain DB New Member

    They are both 32 bit machines.
    Memory settings are the same.
    However, I just reserved another testing machine which was not a Quad but a Dual-core and I still see the same diminished results for my application.
    But when I run the same app remotely using the "diminished results" db, the performance is 60% better.
    How is this possible? All OS and Sql Server settings are the same. The new machine I order was imaged with a ghost image of the OS? Could this be affecting performance?
  11. Adriaan New Member

    My suggestion was not to change the overall SQL Server configuration to use only 1 cpu, but to do this on individual queries that present this performance issue, using the OPTION (MAXDOP 1) clause.
    In case the hardware upgrade was prompted by the data growing beyond a certain point, perhaps the indexing might need to be reviewed. So why not run the ITW ...
  12. Captain DB New Member

    I used the MAXDOP 1 OPTION but it did not affect the performance results.
    What is ITW?
  13. moh_hassan20 New Member

    i find a similar slow in server performance due to a network card
    check network Card configuration:
    • linked speed/duplex mode = auto mode
    • configure network card : file and printer sharing : Maximize Data Throughput, select The Maximize Data Throughput For Network Applications option
    review your server configuration for the two servers , you may something different
    SELECT * FROM sys.configurations
    ORDER BY name ;
    if the server is dedicated only for sql server , enable Priority Boost option (set it to 1)
    It can potentially offer a performance advantage
    IS Your IO the same in the two servers, disk speed , RAID level , disk cach?

Share This Page