April 27, 2012 at 7:50 am
dwilliscp (4/27/2012)
I found the following on MS site (http://msdn.microsoft.com/en-us/library/ms178067(v=sql.90).aspx)So my boss is correct.. the recommended state (by MS) is to let SQL Server get what ever memory it can from the server.
....assuming that SQL Server is the only thing using memory on that box. Rare.... Check it out in task manager and let me know what you see. Regardless, the OS needs memory too and when you are constantly having SQL Server work for memory space and contending with the OS and other resources you aren't doing SQL or the OS any favors.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 27, 2012 at 8:06 am
dwilliscp (4/27/2012)
I found the following on MS site (http://msdn.microsoft.com/en-us/library/ms178067(v=sql.90).aspx)"Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for Windows 2000 or Windows Server 2003 and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you can assign to SQL Server."
So my boss is correct.. the recommended state (by MS) is to let SQL Server get what ever memory it can from the server.
No. That is not what the article is saying. It is saying it is recommended to allow SQL Server to manage memory dynamically, i.e. max memory != min memory. Not setting max memory at all is a dangerous configuration on 64-bit servers.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 12:22 pm
I created a trace, and then exported to sql and now running in the background. However the Duration, CPU, and Memory are all blank... and getting a lot of records too.
/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 04/27/2012 08:55:31 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100
exec @rc = sp_trace_create @TraceID output, 0, N'C:\tracefiles\crash_trace', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @bigintfilter = 15000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
set @bigintfilter = 0
exec sp_trace_setfilter @TraceID, 13, 0, 1, @bigintfilter
set @bigintfilter = 100000
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter
set @bigintfilter = 0
exec sp_trace_setfilter @TraceID, 16, 0, 1, @bigintfilter
set @intfilter = 10000
exec sp_trace_setfilter @TraceID, 18, 0, 4, @intfilter
set @intfilter = 0
exec sp_trace_setfilter @TraceID, 18, 0, 1, @intfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
April 27, 2012 at 12:31 pm
Remove these from the script and start again. These are all connection events and really aren't necessary for this.
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
By the way, did you adjust max_server_memory? You really need to. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 27, 2012 at 2:15 pm
My boss is against setting the Max Memory... so until I can Prove the effect of MS recommended setting.. I will just have to monitor.
April 27, 2012 at 2:19 pm
dwilliscp (4/27/2012)
My boss is against setting the Max Memory... so until I can Prove the effect of MS recommended setting.. I will just have to monitor.
Sounds like you need a new boss, one way or the other 😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 2:26 pm
Your boss should really re-read the statement from MS. They definitely recommend letting memory be dynamically managed but I'm sure if you were to contact product support they would tell you to set max_server_memory prior to proceeding with any other diagnosis.
Read the following MS blog post on the importance of setting max_server_memory - http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/12/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it.aspx
I'll leave it at that... 😀
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply