March 10, 2008 at 6:57 am
Physical memory 67102876 PF was 1.34 Gig.
Available memory 65 gig
Then performed DBCC CHECKDB on 40 gig database.
The PF went to 32 GIG
Available 25 gig
Is this what happens when sql uses the memory the PF grews so when the available memory is low its cause the memory is the PF that sql is using.
March 10, 2008 at 7:34 am
SQL could cause the pagefile to grow. It depends on how you have the OS configured. DBCC does take a lot of memory and disk to run.
March 10, 2008 at 8:49 am
can you provide some more specific info ?
OS ? (+ sp)
SQL version + sp
exec sp_configure 'show advanced options' , 1
exec sp_configure -- information (extended info activated please)
This mainly to see of your sqlinstance as some memory restrictions.
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
March 10, 2008 at 10:19 am
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
allow updates 0 1 0 0
clr enabled 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 0 0
max text repl size (B) 0 2147483647 65536 65536
nested triggers 0 1 1 1
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
server trigger recursion 0 1 1 1
show advanced options 0 1 1 0
user options 0 32767 0 0
Intel Xeon X7350 2.93 GHZ 64 GB Ram 8 processors.
SQL build is 9.03054 SP2 64bit
Microsoft Server 2003 R2 Enterprise X64 Edition SP2
Cluster environment
Not installed the latest CU6 update until i get an error or have to install this.
The lock pages in memory has been set - 64 BIT system. SQL 2005 Enterprise
64 GIG RAM
102 PAGE FILE
March 11, 2008 at 1:21 am
Oops, I missed the reconfigure statement.
That's why not all config options show up.
exec sp_configure 'show advanced options' , 1
reconfigure
exec sp_configure -- information (extended info activated please)
Can you post again ?
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
March 11, 2008 at 7:36 am
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
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
common criteria compliance 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 51200 51200
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 0
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 11, 2008 at 7:54 am
- I'm glad to see you've set the max server memory for this instance !
This is a must for large systems.
- did you configure the os best performance for programs ?
(sytem properties / advanced / performance options)
- you may also want to prevent sql from paging by
granting "lock pages in memory" for your instance service account.
( Control Panel / Admin Tools / Local Security Policy -> "lock pages in memory" rightclick and add)
You can see this setting is active in the sqlserver errorlog at startup time there should be a notification of "using locked pages for buffer pool."
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
March 11, 2008 at 7:58 am
I have done the lock in pages in memory.
Yes done all the best performance guides.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply