SQL Performance Q''s
-
Hi - we have a EMCcx500 san with a 4way xeon proc setup and 32gbs of ram. We have a database application overlain with foxpro on SQL. We are also running Server 2003 enterprise and sql 2000 enterprise. We are going through the common growing pains of a flat file to sql conversion but often throughout the day the proc pegs at 100% for seconds/few minutes at a time. I am showing in task man processes that sqlserver has 30gb of mem... but how am to be sure that the cpus and/or ram is configured/utilized correctly? I looked into the sp_configure and found that AWE is minimum0 maximum1 config_value0 run_value0. Is this correct?
Also - there are no other application and/or use for this sql server other than the application in question. I am using the veritas in depth for sql and it shows that the non sql server page faults are average 1321 a second!! The sql server page faults are only 27... is this correct? This looks way off to me. Thanks for any advice at all that anyone can give.
Spencer
-
To use the memory on your sql server you must set the min and max memory and set awe on
exec dbo.sp_configure 'min server memory',27500
exec dbo.sp_configure 'max server memory',27500
exec dbo.sp_configure 'awe enabled',1
reconfigure with override
go
for example to use ( approx ) 27Gb of your 30Gb memory for sql server
To check how much memory is being used run this query
select * from master.dbo.sysperfinfo where counter_name like '%server memory%'
You should see figures of around 27737784 for 27Gb. I allocate server memory less 2Gb on our main servers which have 32gb ram.
SQL Server is designed not to page. I'd get the memory right then look again.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Wow... I ran that query and got the results as below:
Target Server Memory 29686312 KB
Total Server Memory 29686312 KB
My supervisor states that AWE is not an option (that box is 64 bit)
He doesnt know much about SQL though - so here goes:
sp_configure shows
name / minimum / maximum / config_value run_value
affinity mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 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
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 28387 28387
max text repl size (B) 0 2147483647 2147483647 2147483647
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 28387 28387
nested triggers 0 1 1 1
network packet size (B) 512 65536 4096 4096
open objects 0 2147483647 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 login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Thanks for any performance increase tips you can give. I notice that svchost.exe is paging around 2000 times a second, sqlserver is paging 25-30 times as second.
-
You state that the cpu levels are at 100%, is the task manager showing that it's SQL that using that cpu, or is there another process?
-
You also mentioned that the server is a 4 way xeon, but the xeon is a 32 bit processor, how can you be running 64bit SQL?
-
whoa, nice catch there Nicholas.
-
Also ...
>>it shows that the non sql server page faults are average 1321 a second!!
What switches are in effect in your server's BOOT.INI file ? Looking for info on whether or not switches like /PAE and /3GB are in effect.
-
If this is windows/sql 64 bit you don't need to add the switches as the 64 bit OS supports that memory natively (nicey nice, one huge reason for going to 64 bit architecture along).
-
Sorry - got those specs confused with a test box (must have been thinking of it when typing
The specs on the host for the SAN in question is an HP RX4640 4way 1.5 Itanium 2's... we are getting % processor times of 99% and 100% when it peaks in perfmon. I have yet to hop over to taskman and look at the sqlserver process but I am sure its showing the same for cpu. I will check again the next time though. FYI - I think sql is assigning processor usage correctly... is it possible the high cpu times are a result of the excessive paging / mem config or could it only be a cpu issue? Thanks again!!! -
Are you loading data from a remote server, or is it from locally held files?
The other thing is, did you install the OS from scratch yourself, or did you have HP come in and configure the server?
-
yes, I was informed that the AWE and PAE are not even applicable in the 64 bit architecture (by my supervisor). I am keeping an eye on things though because if I do find something and it gets out - he could be in a world of trouble because this has been effecting our production enviro for a while now and he's been blaming it on the developments code only.... but now their code has been being cleaned up nicely and it just isn't making sense on why we get sluggish performance and high cpu times throughout the day.
-
The data I am guessing you mean database - it is ony that server itself. No other applications installed - totally dedicated. HP/EMC guys game and did everything from top to bottom.
-
The 100% cpu is definately related to SQL?
When you are hitting these cpu levels run a quick query to see what's going on, get an idea of what processes within SQL could be causing the problem.
Quick example, I use this, which shows me what process is using the most CPU at the time (can be a little misleading due to long open connections). I also remove the SA login and also other logins that are part of background processes like replication.
SELECT SPID, RTRIM(STATUS), CMD, RTRIM(LOGINAME), RTRIM(HOSTNAME),* FROM MASTER..SYSPROCESSES WHERE LOGINAME NOT LIKE 'SA%' ORDER BY CPU DESC
-
That's exactly just what I needed... I wanted to sort by cputime... excellent stuff. We are just assuming that sql is taking up the processor because that is the only thing running on the server. I am watching task man right now and sql cpu % is jumping to 80-90 (everyone out to lunch).... it will probably be higher in a hour or so and I will analyze the query results. Like I said in the beginning - we know that some queries are running inefficiently and those are being slowly optimized. But, I am just trying to clear the possibility of it being the slightest hardware prob. I hope I am not asking too much
-
I know that you are running InDepth, I don't know if you are gaining any real useful information out of it. I didn't want to spend the money to get the training for the tool and have found much more use out of the Idera Diagnostic Manager tool (like Quest Spotlight for SQL). You might want to trial a version and use that to get some insight into your install.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply