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.

Making a Stored Procedure in MS-SQL 2000

Hi,

Sorry, this is a very easy question. I have to create a stored
procedure in a SQL SERVER Database.

I have read lots on MSDN about the syntax of this stored procedure :
ie.

CREATE PROCEDURE spGetContctDetails
AS
-- get everything out of contact table
SELECT * FROM tblContact

but I cannot find anywhere how to actually add this procedure, so that
it is accessable in my VB.NET program.

What I am asking is : what screen do I type this into? Is it Query
Analyzer, if so where?

Its got me oh so confused. When I've got time i'll read all about SQL
Server, but for this morning I need to know how to simply add that
Stored Procedure to my database.

Thanks LOADS for any help.Type the CREATE PROCEDURE code in Query Analyzer. You can then execute
the proc from VB using the ADO command.execute method for example.
--
David Portas
SQL Server MVP
--

make a user for the database

hi
how can i make a user give it a name ReportUser and a password msdn for a certain database named on the sql server 2000 and thank you for your help and oyur timeWissam,
Let's say you have a database on SQL2000 with the name, WEBDATA.

1. Make sure your SQL Instance allows SQL Server and Windows authentication enabled
* From Enterprise Mgr, right click on your server icon, and select Properties
* In the SQL Server Properties window, and select the SECURITY tab
* Check SQL Server and Windows authentication
* You will be prompted to restart the SQL Server service - go ahead and do it (just make sure you are the only user on the databases that are running on that sql instance)
2. Create a new Login User, by going in to the SECURITY - Logins and right-click to create a 'New Login', we can call it ReportUser
3. Once that new Login is created, give it the appropriate permissions for ReportUser to access only the databases you want it to touch, as well as triggering stored procedures, etc.

I didn't want to give you all the answer, but just enough to get you rolling! :-)

Happy Learning

raj

Maintenance plans

We have Veritas' Backupexec running in our Enterprise and the Veritas Install actually installs MS SQL Server MSDN on each Server in the Enterprise.

It looks like it also sets up a default Maintenance plan within each of the MSDN Instances.

I guess my question is.. Can I manage the Maintenance Plans on these MSDN Instances via the SQL Server EM GUI from my desktop?? Seems like when I look at the Maintenance plans alot of the options are greyed out or not available. What I am trying to do is modify one of the maintenance plans to have the backups deleted after one week (One of the Instances has been running a complete backup on the Backupexec Databases for a year and there are a years worth of backups on the Server) but the option to "remove files older than" is 'greyed out' ??The main reason I've seen options greyed out is insufficient privleges.|||I logged into my local MSDN Instance as 'sa' and created a new maintenance plan via EM on my desktop.

I can create the plan, but the options to "remove files older than" is still missing. This is so puzzling!!!!!??|||Have you checked with Veritas? My guess is that Backupexec may have permanently disabled those items.|||I thought Veritas might be the culprit also, So I installed MSDE locally on my machine and I get the same results. In a new maintenance plan, on the 'specify backup disk directory' screen, I put a check in the 'remove files older than' box and the dropdrop that should read 'hours, days, weeks, months' is blank and willl not populate. Is this something that is particular to MSDE???|||There's a reason it is called MSDE and not MS SQL Server 2000. MSDE is intended for disconnected applications and/or development. It is not intended as a way to avoid paying for what you need.

You need to read up on limitations in MSDE. Here's one article that may be interpreted as a keen marketing ploy. (http://www.teratrax.com/articles/msde_vs_sql_server.html)|||Thank you for the link to the article!!!!!!!!!! basically, i need to go back to veritas then.