March 16, 2009 at 2:37 am
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.
March 16, 2009 at 2:43 am
what are the min and max server values???
also check the page file size..??
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 16, 2009 at 2:44 am
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.)
March 16, 2009 at 3:44 am
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
March 16, 2009 at 4:00 am
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]
March 16, 2009 at 5:06 am
AWE is not enabled!
March 16, 2009 at 5:48 am
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]
March 16, 2009 at 6:12 am
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 🙂
March 16, 2009 at 11:48 am
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 sizebesides 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]
March 16, 2009 at 10:22 pm
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?
March 16, 2009 at 10:30 pm
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
March 17, 2009 at 12:31 pm
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]
March 17, 2009 at 10:04 pm
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?
March 17, 2009 at 10:25 pm
recommended, 2000 as min and 4000 as max. But tell me please what is the size of your DB?
March 18, 2009 at 12:39 am
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