High PF Usage

  • We have performance problem in our SQL server2005. Indexes are rebuilt, statistics are updated and slow queries are tuned however the SQLserver is still slow! In task manager CPU usage is around 3% sometimes goes up to 50% when we try to run a query through our web application. and the other problem is that PF Usage gets so high. Server has 8GB of RAM and PFUsage sometimes goes to 4GB or 7GB

    other values in task manager is as follows:

    totals:

    handles: 21145 , threads 1164, processes 70

    Physical memory:

    total: 8386216, available 4435588, system cache 1510852

    commit change:

    total: 4171988, limit 10030600, peak 4256540

    Kernel Memory:

    total 131980, paged 87172, nonpages 44820

    I wonder what really causes our sql server become slow. It is slow even if no user is connected to our application.

    besides, in processes tab of task manager the memusage of sqlserver.exe is around 4GB!

    Could anyone tell me how I can resolve this problem.

  • what are the min and max server values???

    also check the page file size..??

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Check out any windows scheduler tasks are scheduled on the server.

    We too had the same problem.we had dual core cpus that time.After changing them to quad-core the issue was solved.cpu usage is steady now.

    Post your cpu configuration and sp_configure results(max.deg of parallelism,affinity mask ,etc.)

  • what are the min and max server values???

    How can I check this please:blush:

    total paging size for all drive is 2046MB

    Check out any windows scheduler tasks are scheduled on the server.

    We too had the same problem.we had dual core cpus that time.After changing them to quad-core the issue was solved.cpu usage is steady now.

    Post your cpu configuration and sp_configure results(max.deg of parallelism,affinity mask ,etc.)

    CPU is 3.00GHz and we have 8GB of RAM

    name minimum maximum config_value run_value

    ----------------------------------- ----------- ----------- ------------ -----------

    Ad Hoc Distributed Queries 0 1 0 0

    affinity I/O mask -2147483648 2147483647 0 0

    affinity mask -2147483648 2147483647 0 0

    affinity64 I/O mask -2147483648 2147483647 0 0

    affinity64 mask -2147483648 2147483647 0 0

    Agent XPs 0 1 1 1

    allow updates 0 1 0 0

    awe enabled 0 1 0 0

    blocked process threshold 0 86400 0 0

    c2 audit mode 0 1 0 0

    clr enabled 0 1 0 0

    cost threshold for parallelism 0 32767 5 5

    cross db ownership chaining 0 1 0 0

    cursor threshold -1 2147483647 -1 -1

    Database Mail XPs 0 1 0 0

    default full-text language 0 2147483647 1033 1033

    default language 0 9999 0 0

    default trace enabled 0 1 1 1

    disallow results from triggers 0 1 0 0

    fill factor (%) 0 100 0 0

    ft crawl bandwidth (max) 0 32767 100 100

    ft crawl bandwidth (min) 0 32767 0 0

    ft notify bandwidth (max) 0 32767 100 100

    ft notify bandwidth (min) 0 32767 0 0

    index create memory (KB) 704 2147483647 0 0

    in-doubt xact resolution 0 2 0 0

    lightweight pooling 0 1 0 0

    locks 5000 2147483647 0 0

    max degree of parallelism 0 64 0 0

    max full-text crawl range 0 256 4 4

    max server memory (MB) 16 2147483647 2147483647 2147483647

    max text repl size (B) 0 2147483647 65536 65536

    max worker threads 128 32767 0 0

    media retention 0 365 0 0

    min memory per query (KB) 512 2147483647 1024 1024

    min server memory (MB) 0 2147483647 0 16

    nested triggers 0 1 1 1

    network packet size (B) 512 32767 4096 4096

    Ole Automation Procedures 0 1 0 0

    open objects 0 2147483647 0 0

    PH timeout (s) 1 3600 60 60

    precompute rank 0 1 0 0

    priority boost 0 1 0 0

    query governor cost limit 0 2147483647 0 0

    query wait (s) -1 2147483647 -1 -1

    recovery interval (min) 0 32767 0 0

    remote access 0 1 1 1

    remote admin connections 0 1 0 0

    remote login timeout (s) 0 2147483647 20 20

    remote proc trans 0 1 0 0

    remote query timeout (s) 0 2147483647 600 600

    Replication XPs 0 1 0 0

    scan for startup procs 0 1 0 0

    server trigger recursion 0 1 1 1

    set working set size 0 1 0 0

    show advanced options 0 1 1 1

    SMO and DMO XPs 0 1 1 1

    SQL Mail XPs 0 1 0 0

    transform noise words 0 1 0 0

    two digit year cutoff 1753 9999 2049 2049

    user connections 0 32767 0 0

    user options 0 32767 0 0

    Web Assistant Procedures 0 1 0 0

    xp_cmdshell 0 1 0 0

  • How can I check this please [Blush]

    total paging size for all drive is 2046MB

    the page size is not set correctly. with 8 gb of ram min page size shud be 12GB.

    write click on the server->properties->memory

    check whether AWE is enabled or not. I guess it is enabled as u said that SQL srvr is taking 4 GB of ram.

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • AWE is not enabled!

  • enable AWE...refer to this

    http://msdn.microsoft.com/en-us/library/ms179301.aspx"> http://msdn.microsoft.com/en-us/library/ms179301.aspx

    u can set the max server memory to 6 GB and leave 2 GB for OS....if there are no other application running other than SQL server...other wise plan accordingly.

    also did u set the page file size as I mentioned earlier.

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • for page file size, there is an initial size and max size (computer properties-> advanced -> ..virtual memory) I don't know if it is the correct place to change and if I should change the initial size

    besides max server memory is set to (2147483647MB) should i change it?

    I'll enable AWE 🙂

  • peace2007 (3/16/2009)


    for page file size, there is an initial size and max size (computer properties-> advanced -> ..virtual memory) I don't know if it is the correct place to change and if I should change the initial size

    besides max server memory is set to (2147483647MB) should i change it?

    I'll enable AWE 🙂

    u are at the right place...change the size to recommended size.

    also change the max memory as I said in the earlier post.

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • I set initial size and maximum size of page file to 12GB and enabled AWE then set the max server memory to 6GB now let's see what happens when all staff (1500 people) come. I'm 2 hrs early at work :hehe:

    shouldn't I restart the server after these changes?

  • by the way, I have 2 drives, shall I set the page file size for each drive to 12 GB or the total size should be 12? besides, what shall I set for initial and max size

  • peace2007 (3/16/2009)


    I set initial size and maximum size of page file to 12GB and enabled AWE then set the max server memory to 6GB now let's see what happens when all staff (1500 people) come. I'm 2 hrs early at work :hehe:

    shouldn't I restart the server after these changes?

    nt required

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • peace2007 (3/16/2009)


    by the way, I have 2 drives, shall I set the page file size for each drive to 12 GB or the total size should be 12? besides, what shall I set for initial and max size

    could anyone answer this please?

  • recommended, 2000 as min and 4000 as max. But tell me please what is the size of your DB?

  • the size of my DB is around 20GB

Viewing 15 posts - 1 through 15 (of 19 total)

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