Managed code memory issues

There is an interesting article from MSDN Magazine titled "Identify and Prevent Memory Leaks in Managed Code"

http://msdn.microsoft.com/msdnmag/issues/07/01/ManagedLeaks/default.aspx

Are there any additional documents or utilities that people would suggest for monitoring and managing CLR impact on SQL server resources and performance?

Hi,

Assuming you are interested in SQL Server's hosting of the CLR you can find a few (not many) SQLCLR-specific counters in both Profiler & Performance Monitor....CLR is "asking" SQL Server for resources....see below BOL excerpts:

The CLR calls SQL Server primitives for allocating and de-allocating its memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure the CLR and SQL Server are not competing with each other for memory. SQL Server can also reject CLR memory requests when system memory is constrained, and ask CLR to reduce its memory use when other tasks need memory.

Profiler Trace Events

SQL Server provides SQL Trace and event notifications to monitor events that occur in the Database Engine. By recording specified events, SQL Trace helps you troubleshoot performance, audit database activity, gather sample data for a test environment, debug Transact-SQL statements and stored procedures, and gather data for performance analysis tools. For more information, see Monitoring Events.

Event Description

Assembly Load Event Class

Used to monitor assembly load requests (success and failures).

SQL:BatchStarting Event Class, SQL:BatchCompleted Event Class

Provides information about Transact-SQL batches that have started or completed.

SPtarting Event Class, SP:Completed Event Class

Used to monitor the execution of Transact-SQL stored procedures.

SQLtmtStarting Event Class, SQLtmtCompleted Event Class

Used to monitor the execution of CLR and Transact-SQL routines.

Performance Counters

SQL Server provides objects and counters that can be used by System Monitor to monitor activity in computers running an instance of SQL Server. An object is any SQL Server resource, such as a SQL Server lock or a Windows XP process. Each object contains one or more counters that determine various aspects of the objects to monitor. For more information, see Using SQL Server Objects.

Object Description

SQL Server, CLR Object

Total time spent in CLR execution.

Managed code memory issues

There is an interesting article from MSDN Magazine titled "Identify and Prevent Memory Leaks in Managed Code"

http://msdn.microsoft.com/msdnmag/issues/07/01/ManagedLeaks/default.aspx

Are there any additional documents or utilities that people would suggest for monitoring and managing CLR impact on SQL server resources and performance?

Hi,

Assuming you are interested in SQL Server's hosting of the CLR you can find a few (not many) SQLCLR-specific counters in both Profiler & Performance Monitor....CLR is "asking" SQL Server for resources....see below BOL excerpts:

The CLR calls SQL Server primitives for allocating and de-allocating its memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure the CLR and SQL Server are not competing with each other for memory. SQL Server can also reject CLR memory requests when system memory is constrained, and ask CLR to reduce its memory use when other tasks need memory.

Profiler Trace Events

SQL Server provides SQL Trace and event notifications to monitor events that occur in the Database Engine. By recording specified events, SQL Trace helps you troubleshoot performance, audit database activity, gather sample data for a test environment, debug Transact-SQL statements and stored procedures, and gather data for performance analysis tools. For more information, see Monitoring Events.

Event Description

Assembly Load Event Class

Used to monitor assembly load requests (success and failures).

SQL:BatchStarting Event Class, SQL:BatchCompleted Event Class

Provides information about Transact-SQL batches that have started or completed.

SPtarting Event Class, SP:Completed Event Class

Used to monitor the execution of Transact-SQL stored procedures.

SQLtmtStarting Event Class, SQLtmtCompleted Event Class

Used to monitor the execution of CLR and Transact-SQL routines.

Performance Counters

SQL Server provides objects and counters that can be used by System Monitor to monitor activity in computers running an instance of SQL Server. An object is any SQL Server resource, such as a SQL Server lock or a Windows XP process. Each object contains one or more counters that determine various aspects of the objects to monitor. For more information, see Using SQL Server Objects.

Object Description

SQL Server, CLR Object

Total time spent in CLR execution.

Manage server in single user mode

Hello-
I am trying to use SQL server in single user mode to move the system
databases to a different drive as described in KB article
http://support.microsoft.com/kb/224071/
I have sql server started up in single user mode with the other startup
options configured as required. However I can not seem to find a way that it
will let me log in in order to run the procedures. The SQL server agent is
stopped as described in order to not use up the available connection.
Whenever I try to connect with SQL Server management studio it says "Login
failed for user xxxx. Reason: Server is in single user mode. Only one
administrator can connect at this time (Error 18461)."
I am the only person who even knows about this server so I know no one else
is conecting to it. I thought it would work to try and do this procedure
through the command line but i cant figure out how to connect to the server
using the command line (im doing this all local on the server).
Can anyone give me any suggestions on how to go about this or what I could
be doing wrong?
Thanks!It's probably from Object Explorer - you probably have SSMS
configured to open Object Explorer at startup. Close object
explorer and try to connect. You can hit cancel when the
Login dialog comes up and and SSMS opens with no connection.
Then close object explorer. Then connect using New Query.
-Sue
On Mon, 23 Jul 2007 12:26:02 -0700, Ehren
<Ehren@.discussions.microsoft.com> wrote:
>Hello-
>I am trying to use SQL server in single user mode to move the system
>databases to a different drive as described in KB article
>http://support.microsoft.com/kb/224071/
>I have sql server started up in single user mode with the other startup
>options configured as required. However I can not seem to find a way that it
>will let me log in in order to run the procedures. The SQL server agent is
>stopped as described in order to not use up the available connection.
>Whenever I try to connect with SQL Server management studio it says "Login
>failed for user xxxx. Reason: Server is in single user mode. Only one
>administrator can connect at this time (Error 18461)."
>I am the only person who even knows about this server so I know no one else
>is conecting to it. I thought it would work to try and do this procedure
>through the command line but i cant figure out how to connect to the server
>using the command line (im doing this all local on the server).
>Can anyone give me any suggestions on how to go about this or what I could
>be doing wrong?
>Thanks!|||On Jul 24, 2:25 am, Sue Hoegemeier <Su...@.nomail.please> wrote:
> It's probably from Object Explorer - you probably have SSMS
> configured to open Object Explorer at startup. Close object
> explorer and try toconnect. Youcanhit cancel when the
> Login dialog comes up and and SSMS opens with no connection.
> Then close object explorer. Thenconnectusing New Query.
> -Sue
> On Mon, 23 Jul 2007 12:26:02 -0700, Ehren
>
> <Eh...@.discussions.microsoft.com> wrote:
> >Hello-
> >I am trying to use SQLserverinsingleusermodeto move the system
> >databases to a different drive as described in KB article
> >http://support.microsoft.com/kb/224071/
> >I have sqlserverstarted up insingleusermodewith the other startup
> >options configured as required. However Icannot seem to find a way that it
> >will let me log in in order to run the procedures. The SQLserveragent is
> >stopped as described in order to not use up the available connection.
> >Whenever I try toconnectwith SQLServermanagement studio it says "Login
> >failed foruserxxxx. Reason:Serveris insingleusermode. Onlyone
> >administratorcanconnectat thistime(Error 18461)."
> >I am theonlyperson who even knows about thisserverso I know nooneelse
> >is conecting to it. I thought it would work to try and do this procedure
> >through the command line but i cant figure out how toconnectto theserver
> >using the command line (im doing this all local on theserver).
> >Cananyone give me any suggestions on how to go about this or what I could
> >be doing wrong?
> >Thanks!- Hide quoted text -
> - Show quoted text -
The process is
NET START MSSQLSERVER /f /T3608 (as per MS article)
Make sure no other SQL tools are running (config manager etc)
Start SQL Server management studio
When it prompts you to connect to the server press cancel (if you
press connect it is THIS connection which prevents you from running
the alter database query)
At this point you are not connected to anything (it will say "No
Server Connection" in big letters)
Press the New Query button
Paste your amended alter database code into thew query pane and
execute
Continue as per article
Cheers
Barry