64 BIT SQL Server

Hi,
We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
size is going 57 Gb, but our server performance is very poor, I configured
Min
memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
15.7 Gb, and machine is going slow, some time i saw some transaction is going
Suspended mode,
my Box task manager showing available M Bytes 270 MB,
your answers will be appreciate and help full to me
JayJay wrote:
> Hi,
> We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
> size is going 57 Gb, but our server performance is very poor, I configured
> Min
> memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
> 15.7 Gb, and machine is going slow, some time i saw some transaction is going
> Suspended mode,
> my Box task manager showing available M Bytes 270 MB,
>
> your answers will be appreciate and help full to me
> Jay
>
Hi Jay,
Performance is not only a question of Memory..:-).
What about indexes and statistics on you database - Are they being kept
updated? You could also look at the the performance counters to see what
it is that is slow.
Try to take a look at -
http://www.sql-server-performance.com/sql_server_performance_audit2.asp
- that should give you an idea which counters to look for.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks steen for reply,
but every day am doing main tables index and every week end i am doing total
tables rebuilding the index and in the week end i am updating the statistics
also.
but my server very slow. do u know why i allocated min memory to 1 GB and
max memory to 14 gb , remaing 2 GB for os but in Task manager its showing 230
mb Available.
please your reply will be great guidence to me
regards
jay
""Steen Schlüter Persson (DK)"" wrote:
> Jay wrote:
> > Hi,
> > We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
> > size is going 57 Gb, but our server performance is very poor, I configured
> > Min
> > memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
> > 15.7 Gb, and machine is going slow, some time i saw some transaction is going
> > Suspended mode,
> >
> > my Box task manager showing available M Bytes 270 MB,
> >
> >
> > your answers will be appreciate and help full to me
> >
> > Jay
> >
> Hi Jay,
> Performance is not only a question of Memory..:-).
> What about indexes and statistics on you database - Are they being kept
> updated? You could also look at the the performance counters to see what
> it is that is slow.
> Try to take a look at -
> http://www.sql-server-performance.com/sql_server_performance_audit2.asp
> - that should give you an idea which counters to look for.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>|||Jay -
Did you change the min/max using the GUI tool? If so, you still need to go
into a query window and do a:
reconfigure with override
It sounds like you are still running with the default max memory value
(which is something like 2TB).|||Hello apf,
thanks for your mail.
yes i did this configuration through t-sql commans only.
dear apf i am waiting for your reply, it will be more help full to me
"apf" wrote:
> Jay -
> Did you change the min/max using the GUI tool? If so, you still need to go
> into a query window and do a:
> reconfigure with override
> It sounds like you are still running with the default max memory value
> (which is something like 2TB).
>
>|||Jay -
If your output of:
sp_configure, 'max server memory (MB)'
returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
is set correctly and your problem is elsewhere.|||Dear APF,
its showing right but my server is too much slow, i am sending to you my sys
configuration details.
am waiting for your reply.
my database is hitting every seconds 30000
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
16391 Ad Hoc Distributed Queries 1 0 1 1 Enable or disable Ad Hoc
Distributed Queries 1 1
1550 affinity I/O mask 0 -2147483648 2147483647 0 affinity I/O mask 0 1
1535 affinity mask 0 -2147483648 2147483647 0 affinity mask 1 1
1551 affinity64 I/O mask 0 -2147483648 2147483647 0 affinity64 I/O mask 0 1
1549 affinity64 mask 0 -2147483648 2147483647 0 affinity64 mask 1 1
16384 Agent XPs 1 0 1 1 Enable or disable Agent XPs 1 1
102 allow updates 0 0 1 0 Allow updates to system tables 1 0
1548 awe enabled 0 0 1 0 AWE enabled in the server 0 1
1569 blocked process threshold 0 0 86400 0 Blocked process reporting
threshold 1 1
544 c2 audit mode 0 0 1 0 c2 audit mode 0 1
1562 clr enabled 0 0 1 0 CLR user code execution enabled in the server 1 0
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for
parallelism 1 1
400 cross db ownership chaining 0 0 1 0 Allow cross db ownership chaining 1 0
1531 cursor threshold -1 -1 2147483647 -1 cursor threshold 1 1
16386 Database Mail XPs 0 0 1 0 Enable or disable Database Mail XPs 1 1
1126 default full-text language 1033 0 2147483647 1033 default full-text
language 1 1
124 default language 0 0 9999 0 default language 1 0
1568 default trace enabled 1 0 1 1 Enable or disable the default trace 1 1
114 disallow results from triggers 0 0 1 0 Disallow returning results from
triggers 1 1
109 fill factor (%) 0 0 100 0 Default fill factor percentage 0 1
1567 ft crawl bandwidth (max) 100 0 32767 100 Max number of full-text crawl
buffers 1 1
1566 ft crawl bandwidth (min) 0 0 32767 0 Number of reserved full-text crawl
buffers 1 1
1565 ft notify bandwidth (max) 100 0 32767 100 Max number of full-text
notifications buffers 1 1
1564 ft notify bandwidth (min) 0 0 32767 0 Number of reserved full-text
notifications buffers 1 1
1505 index create memory (KB) 0 704 2147483647 0 Memory for index create
sorts (kBytes) 1 1
1570 in-doubt xact resolution 0 0 2 0 Recovery policy for DTC transactions
with unknown outcome 1 1
1546 lightweight pooling 0 0 1 0 User mode scheduler uses lightweight
pooling 0 1
106 locks 0 5000 2147483647 0 Number of locks for all users 0 1
1539 max degree of parallelism 0 0 64 0 maximum degree of parallelism 1 1
1563 max full-text crawl range 4 0 256 4 Maximum crawl ranges allowed in
full-text indexing 1 1
1544 max server memory (MB) 14000 16 2147483647 14000 Maximum size of server
memory (MB) 1 1
1536 max text repl size (B) 65536 0 2147483647 65536 Maximum size of a text
field in replication. 1 0
503 max worker threads 0 128 32767 0 Maximum worker threads 0 1
1537 media retention 0 0 365 0 Tape retention period in days 0 1
1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per
query (kBytes) 1 1
1543 min server memory (MB) 1024 0 2147483647 1024 Minimum size of server
memory (MB) 1 1
115 nested triggers 1 0 1 1 Allow triggers to be invoked within triggers 1 0
505 network packet size (B) 4096 512 32767 4096 Network packet size 1 1
16388 Ole Automation Procedures 0 0 1 0 Enable or disable Ole Automation
Procedures 1 1
107 open objects 0 0 2147483647 0 Number of open database objects 0 1
1557 PH timeout (s) 60 1 3600 60 DB connection timeout for full-text
protocol handler (s) 1 1
1556 precompute rank 0 0 1 0 Use precomputed rank for full-text query 1 1
1517 priority boost 0 0 1 0 Priority boost 0 1
1545 query governor cost limit 0 0 2147483647 0 Maximum estimated cost
allowed by query governor 1 1
1541 query wait (s) -1 -1 2147483647 -1 maximum time to wait for query
memory (s) 1 1
101 recovery interval (min) 0 0 32767 0 Maximum recovery interval in
minutes 1 1
117 remote access 1 0 1 1 Allow remote access 0 0
1576 remote admin connections 0 0 1 0 Dedicated Admin Connections are
allowed from remote clients 1 0
1519 remote login timeout (s) 20 0 2147483647 20 remote login timeout 1 0
542 remote proc trans 0 0 1 0 Create DTC transaction for remote procedures 1 0
1520 remote query timeout (s) 0 0 2147483647 0 remote query timeout 1 0
16392 Replication XPs 0 0 1 0 Enable or disable Replication XPs 1 1
1547 scan for startup procs 0 0 1 0 scan for startup stored procedures 0 1
116 server trigger recursion 1 0 1 1 Allow recursion for server level
triggers 1 0
1532 set working set size 0 0 1 0 set working set size 0 1
518 show advanced options 1 0 1 1 show advanced options 1 0
16387 SMO and DMO XPs 1 0 1 1 Enable or disable SMO and DMO XPs 1 1
16385 SQL Mail XPs 0 0 1 0 Enable or disable SQL Mail XPs 1 1
1555 transform noise words 0 0 1 0 Transform noise words for full-text
query 1 1
1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff 1 1
103 user connections 0 0 32767 0 Number of user connections allowed 0 1
1534 user options 0 0 32767 0 user options 1 0
16389 Web Assistant Procedures 0 0 1 0 Enable or disable Web Assistant
Procedures 1 1
16390 xp_cmdshell 1 0 1 1 Enable or disable command shell 1 1
"apf" wrote:
> Jay -
> If your output of:
> sp_configure, 'max server memory (MB)'
> returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
> is set correctly and your problem is elsewhere.
>