February 14, 2008 at 3:24 am
I've had a trawl through the event logs and there are no errors/warnings about memory problems or anything hardware fault related. This server runs just the one instance of SQL and is a dedicated server so is not used for anything else. I've been informed that we aren't using CLR proceduers within our replication process.
Max SQL memory is set to 14000MB and Minimum is 0MB (the box has 16GB physical memory and 4GB of swap). The SqlServer process shows as using 14,837,208KB of memory in Task Manager. Index creation memory is set to 0 and minimum memory per query is set to 1024
The results of the sp-configure are:
Name Min Max Config_value Run_value
----------------------------------------------------
allow updates01 0 0
clr enabled 0 1 0 0
cross db ownership chaining0100
default language0999900
max text repl size (B)021474836476553665536
nested triggers0111
remote access0111
remote admin connections0100
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
server trigger recursion0111
show advanced options0100
user options03276700
Thanks to everyone for your help with this 🙂
February 14, 2008 at 3:51 am
Stu (2/14/2008)
This server runs just the one instance of SQL and is a dedicated server so is not used for anything else. I've been informed that we aren't using CLR proceduers within our replication process.Max SQL memory is set to 14000MB and Minimum is 0MB (the box has 16GB physical memory and 4GB of swap). The SqlServer process shows as using 14,837,208KB of memory in Task Manager. Index creation memory is set to 0 and minimum memory per query is set to 1024
That's really odd. The only time the OS should page SQL's working set to disk (as the error log says has happened) is if the OS is starved for memory (Which should not be the case here, certainly not right after startup) and SQL can't reduce teh memory its using due to a high setting for min server memory (again, not the case here)
Run perfmon and look at the Available memory (MBytes). What's its value?
Again -Contact PSS. There's just too many weird things going on there. Assertion failures and stack dumps aren't really problems that we can fix here.
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
February 14, 2008 at 4:03 am
Name Min Max Config_value Run_value
----------------------------------------------------
clr enabled 0 1 0 0
confirms CLR is not activated
Would you mind alowing to view all configs ?
That can be done with
-- enable advanced options
exec sp_configure 'show advanced options', 1
reconfigure
go
-- show full results
exec sp_configure
go
-- disable advanced options
exec sp_configure 'show advanced options', 0
reconfigure
go
and don't forget to contact PSS, as stated more than once in the quest...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 14, 2008 at 4:15 am
Here are the memory numbers:
Physical Memory
----------------
Total: 16775868k
Available: 438440k
System Cache: 717340k
Commit Charge
--------------
Total: 16322444k
Limit: 20328567k
Peak: 17499536k
I was under the impression that to view the advanced config required SQL to be restarted? This can't be done as it's the production server for the company. I'll have a go as long as it isn't going to break anything.
February 14, 2008 at 4:30 am
When you are working out how much memory is in use, you need to add the Commit Charge and the System Cache amounts together.
If the total is less than the physical memory, you have no memory pressure. If the total is higher then you have a memory shortage that you need to resolve by either adding more physical memory or by reducing the memory that is allocated.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 14, 2008 at 5:27 am
Okay, here is the math:
16322444 + 717340 = 17,039,784
17,039,784 - 16775868 = 263,916
So it looks like I'm 264MB short on physical memory. I assume that changing the SQL Max memory setting will require a SQL restart? I'll have to schedule some downtime. So this should get rid of the "A significant part of sql server process memory has been paged out" message, but will it also stop it crashing? :unsure:
February 14, 2008 at 5:43 am
I was under the impression that to view the advanced config required SQL to be restarted?
An instance restart is certainly not needed to view the advanced config settings !
It is the "reconfigure" statement that activates the set parameters.
from BOL
The RECONFIGURE statement updates some options dynamically; other options require a server stop and restart. For example, the min server memory and max server memory server memory options are updated dynamically in the Database Engine; therefore, you can change them without restarting the server. By contrast, reconfiguring the running value of the fill factor option requires restarting the Database Engine.
After running RECONFIGURE on a configuration option, you can see whether the option has been updated dynamically by executing sp_configure 'option_name'. The values in the run_value and config_value columns should match for a dynamically updated option. You can also check to see which options are dynamic by looking at the is_dynamic column of the sys.configurations catalog view.
As you can see even the min server memory and max server memory server memory options are updated dynamically in the Database Engine :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 14, 2008 at 6:11 am
I assume you will put a monitor on commit load and run it for a few days to see if any time-dependant processes use more memory than the values you saw.
One thing that can badly affect memory use is copying of large files. Windows is happy to use up to 50% of physical memory for a file cache during copy operations. Google can find you postings about this. If you copy backup files to a network drive, this could hit you. In this situation, you may want to look at Uwe Sieber's SetSystemFileCacheSize routine that can limit how much memory Windows will use for this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 14, 2008 at 8:14 am
Here are the advanced options
Name Minimum Maximum Config_value Run_value
------------------------------------------------------------
Ad Hoc Distributed Queries0100
affinity I/O mask-2147483648214748364700
affinity mask-2147483648214748364700
affinity64 I/O mask-2147483648214748364700
affinity64 mask-2147483648214748364700
Agent XPs0111
allow updates0100
awe enabled0100
blocked process threshold08640000
c2 audit mode0100
clr enabled0100
cost threshold for parallelism03276755
cross db ownership chaining0100
cursor threshold-12147483647-1-1
Database Mail XPs0111
default full-text language0214748364710331033
default language0999900
default trace enabled0111
disallow results from triggers0100
fill factor (%)010000
ft crawl bandwidth (max)032767100100
ft crawl bandwidth (min)03276700
ft notify bandwidth (max)032767100100
ft notify bandwidth (min)03276700
index create memory (KB)704214748364700
in-doubt xact resolution0200
lightweight pooling0100
locks5000214748364700
max degree of parallelism06400
max full-text crawl range025644
max server memory (MB)1621474836471400014000
max text repl size (B)021474836476553665536
max worker threads1283276700
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5123276740964096
Ole Automation Procedures0100
open objects0214748364700
PH timeout (s)136006060
precompute rank0100
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote admin connections0100
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
Replication XPs0100
scan for startup procs0111
server trigger recursion0111
set working set size0100
show advanced options0111
SMO and DMO XPs0111
SQL Mail XPs0100
transform noise words0100
two digit year cutoff1753999920492049
user connections03276700
user options03276700
Web Assistant Procedures0100
xp_cmdshell0100
February 14, 2008 at 8:26 am
You REALLY need to be talking with PSS about the server crashes.
2 more minor points: 1) 14GB is probably too high sql mem max for a 16GB box. I would go with 13 and monitor paging to see if it needs to be even lower. 2) I would set cost threshold for parallelism higher than 5 on this box. Actually if it is a dedicated, appropriately indexed OLTP box consider setting max deg of parallelism to 1. If it is a dedicated report-type box, I would test out numbers between 10 and 20 for CTP. If mixed, maybe 10.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2008 at 8:31 am
Late joining back in, but 14GB is too much. After 8GB, there's 1GB being used to move stuff in an out of PAE memory. Meaning you've left 1GB for the OS, which might be tight.
Not sure about the parallelism, but I'll defer to the SQL guru on that. I would recommend you call PSS. You've probably spent over $300 of your time on this and they might have helped you by now.
February 14, 2008 at 8:43 am
It's a x64 server, so no PAE memory, but 14GB is still a little too high. I prefer my available memory to be at least 1.5-2GB on servers with 16GB or higher.
Still doesn't explain why SQL would get paged out during startup, before bringing any of the DBs online. With min memory 0, it shouldn't be allocating the full 14GB at startup.
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
February 14, 2008 at 9:08 am
I wonder if it is an HP box and they are being hit with the iLO driver bug.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2008 at 9:24 am
Lock Pages in Memory not assigned?
That is strange. I'd call PSS.
February 14, 2008 at 10:57 am
Steve Jones - Editor (2/14/2008)
Lock Pages in Memory not assigned?
Standard Edition. IIRC, Lock pages is only honoured on Enterprise.
I'd still call PSS. I don't know anyone who can read stackdumps like their guys can. We're just going around in circles here.
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
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply