Results 1 to 6 of 6

Thread: Memory In SQL

  1. #1
    Join Date
    Dec 2003
    Posts
    11

    Unhappy Unanswered: Memory In SQL

    Hi there,

    I have just migrated my production SQL Server to a new box and found that the memory usage for sqlservice in Task Manager seemed to be giving a very awkward figure: 118KB which is impossible as the usage before the migration was 1,800,234Kb.

    I did some checking by capturing the perfmon counters: Total and Target Memory in SQL Server and both counters returning the max server memory value (4GB) which I had allocated for SQL Server.

    I worried that this might be indicating that there's something wrong with the memory allocation but having hard time finding the root cause of it.

    Any idea or solution to this?

    Please help.
    Thanks!!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What version of SQL, on what server (hardware and OS), using what memory settings? One thing that would help me a lot with diagnosing your problem would be for you to post the results of running:
    Code:
    EXECUTE sp_configure 'show', '1'
    GO
    EXECUTE sp_configure
    -PatP

  3. #3
    Join Date
    Dec 2003
    Posts
    11
    oops...here's the details:
    Hardware: HP DL380 G4 ( 2 processors with hyperthreading )
    OS: win2k3 Enterprise edition
    RAM: 8GB each cluster nodes.
    Cluster environment : Active/active

    I have added in /3GB /PAE switch in boot.ini and had turned on AWE option.
    Max Server memory configured for node 1 is 5Gb and node 2 is 2GB.
    SQL Server version is 2000 with sp3a.

  4. #4
    Join Date
    Dec 2003
    Posts
    11
    Code:
    name           		minimum     	maximum     	config_value 	run_value   
    -------- 		-----------    	----------- 	------------ 	----------- 
    affinity mask	   -2147483648	    2147483647	        0		0
    allow updates			 0		1		  0		  0
    awe enabled			 0		1		  1		  1
    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		        1		1
    max server memory (MB)	  4		2147483647	5120	     5120
    max text repl size (B)	       0	    2147483647	    65536	65536
    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	   0		0
    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		0	 	 0
    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
    Last edited by Pat Phelan; 08-28-06 at 14:35. Reason: Added code tags trying to improve formatting.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I wouldn't get too 'bent out of shape" about the values reported by Task Manager. The memory configuration looks Ok to me.

    Keep in mind that there are two radically different kinds of memory usage supported and reported by Windows. 32 bit tasks can have AWE or conventional memory allocated to them. Conventional memory is allocated in the 32 bit tasks address space, which can be 2 Gb in normal configuraitons, and can be set to 3 Gb in special cases (which you've already done). This memory is "visible" to Windows Task Manager. AWE memory can be allocated anywhere that Windows has usable memory, without any regard for the 2 or 3 Gb limit. This is the only way that a 32 bit task can address more than 3 Gb of RAM, but AWE lies completely ouside the scope of WIndows Task Manager.

    -PatP

  6. #6
    Join Date
    Dec 2003
    Posts
    11
    i see.. thanks for pointing this out one question though, I did a check on Perfmon's SQL counters: Target Memory vsTotal Memory and found that both the memory value are almost the same (4GB)..does this means that the server has memory bottleneck? Because the Max memory that i allocate for this server is 5GB.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •