SQL Server Performance

32-bit SQL Server 2005 Standard running on 64-bit processor server

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Narine, Oct 14, 2008.

  1. Narine New Member

    Hi all,
    I'm looking for some advice. We currently have a 32-bit SQL server 2005 Standard edition running on a 32-bit processor server with 2 dual-core CPU's and 8GB of RAM. We consistenly have performance problems with a big CPU bottleneck.
    We may be able to upgrade our server to the 64-bit processor. Will we see any performance gain from the upgrade? All I've been able to find so far is how 64-bit SQL server offers great performance, but nothing about 32-bit SQL server on 64-bit processor.
    I found an article that says the Standard edition of SQL Server cannot do 'Lock Pages in Memory' which we're using right now with AWE enabled. Is it going to affect the memory usage.
    Please let me know if anyone else has that setup.
    Thanks
    Narine
  2. gurucb New Member

    Moving to 64 bit may not be the right option. Before going to go to 64 Bit architecture it would be better if High CPU performance could be resolved with current setup. Typical High CPU operations in SQL Server include query Compilation recompilations, Hash Matches , Sort operations. To narrow down which of these operations cause high CPU below monitors can be reviewed
    * SQL Statistics
    -> Batches / Second
    -> Compilations / Second
    -> Recompilations / Second
    * Access Methods
    -> Workfiles Created / Second
    -> WorkTables Created / Second
    And in Memory
    * Memory
    -> Available MBytes

    If Compilations / Second are equal Batches / Second then may be load on SQl Server is adhoc and converting them to stored procedures may be solution. Also SP_ExecuteSQL can be tried as well. But if Recompilations are more it may indicate statistics or schema modifications. If Workfiles and WorkTables created are equal to batches / seconds it may indicate query tuning / indexing is needed on server.It would be better if these can be tried and then take decision on moving to 64 Bit.
    IF you have some diagnostics tools use them to find out most CPU queries or try using SQLNexus tool at www.codeplex.com (free tool) to find out most expensive queries wrt to CPU / IO.
  3. Saurabh Srivastava New Member

    What is driving CPu high? Have you identified it?
  4. Narine New Member

    Thanks so much for the info ot tuning, gurucb! In our environmnet none of this is true. Compilations are much smaller than Batches/sec and Recompilation is tiny. Worktables and Workfiles are also much smaller than Batches per second.
    We just have an underpowered server for what we need. Our application is very complex in terms of there are just a lot of different processes running on the server at the same time together with users logged in, which causes resources to get maxed out.
    I believe we need to upgrade, but it sounds like upgrading the server is not the only thing that needs to be done. We would need to upgrade OS and the SQL Server to the 64-bit in order to get maximum performance.
    Thanks everyone for your great suggestions!!
  5. rohit2900 Member

    To get the benifit of 64 bit hardware, you need to upgrade atleast your OS to 64-bit and then go with AWE enabled.
    What is the db size, no of users, and total no. of databases on the server, apart from this what is the storage configuration? What type of performance problems you are facing? How did you know that the prob u r facing is due to CPU bottleneck? Have u enabled AWE?
    Use profiler to check for long running queries and then check for the execution plans...
    Upgrade is always good and safe from a DBA's prospective but I'll still suggest you check everything before going for a upgrade. Cos without it the upgrade also won't give you that much performance boost.
  6. Saurabh Srivastava New Member

    Regarding you Standard edition question look at this post, it might help you; http://sql-server-performance.com/Community/forums/p/23768/134258.aspx
    there are mixed opinions but I think you should test it. First run a stress test without lock pages in memory and second load test with this setting and compare memory and buffer performn counters in bot cases.
    For 1000% confirmation just give a call to Microsoft support.
    If I find any documention reagding this option on Microsoft website wil let you know.

Share This Page