Post reply

SQL Performance tuning question

  • December 10, 2008 at 7:10 am

    #125568

    Hi,

    We are using sqlserver 2000 as a backend for our apps. Lately we have observed that some times on my server SQL enterprise manager and Query analyzers are freezing up for some time. At that time if we check in TaskManager, the Sqlservr.exe is taking maximum amount of memory usage.

    Any suggestions on how to fine tune this issue would be greatly appreciable.

    Thanks,

    Swathi

  • December 10, 2008 at 7:21 am

    #910335

    [font="Verdana"]Do the below steps and see:

    1.Goto Enterprise Manager.

    2.Right click on the servername

    3.Goto properties

    4.Click on 'memory' tab and see if 'Dynamically configure SQL Server memory' radio button is selected.

    If 'yes' then, that might be the problem as SQL Server is using most of the available memory. You can also check the priority level of sql server.

    Regards[/font]

    -Hope is a heuristic search :smooooth: ~Hemanth

  • December 10, 2008 at 7:23 am

    #910337

    swathi (12/10/2008)

    At that time if we check in TaskManager, the Sqlservr.exe is taking maximum amount of memory usage.

    Nothing unusual there. SQL will take as much memory as it's allowed to. How much memory does the server have? How much are you seeing SQL using?

    That's probably not the cause of the slow downs. Run profiler for a while and look for queries with high durations, high reads, high CPU (or all of the above).

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 10, 2008 at 7:38 am

    #910354

    Yeah! I have checked those settings, and yes "Dynamically configure Sql server memory" has been checked. Can I change these settings? if yeas what would be the best amount to allocate if I use fixed memory size?

    TIA

  • December 10, 2008 at 7:42 am

    #910361

    Gail Shaw,

    Server has total of 3.12 GB memory and from which sqlservr.exe is taking 1.7gb which is the maximum among all the processes that r running at that time.

    I have started the sql profiler . Ran it for a while. All the CPU,Reads and Duration are normal. nothing suspicious with that.

    TIA

  • December 10, 2008 at 7:51 am

    #910380

    swathi (12/10/2008)

    Gail Shaw,

    Server has total of 3.12 GB memory and from which sqlservr.exe is taking 1.7gb which is the maximum among all the processes that r running at that time.

    Nothing wrong there. 1.7 GB of 3 is a fairly reasonable amount of memory for SQL. Is this a dedicated SQL server? (no other apps)

    Can I change these settings? if yeas what would be the best amount to allocate if I use fixed memory size?

    If it is a dedicated server, 2GB is a good max for SQL with 3 GB in the server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 10, 2008 at 8:16 am

    #910408

    Thanks for the update. ours is a dedicted server.

    then how can i trouble shoot the issue that sql is freezing up some times?

  • December 10, 2008 at 8:51 am

    #910442

    I would also check your pagefile and make sure it has enough room and is not full. The other thing is how big are you keeping your logs and data files and is there enough room on the hard drives to keep supporting your growth? There are alot of possibilities. You most definitely need to look at what the server is doing and tracking its performance.

    Conan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply