Poorly written assembly and CLR

As the CLR is hosted in the SQL OS.

How will the SQL OS handle a situation wherein I have a badly written .net assembly which is chewing up lot of memory and other system resources of the server.

1. Won’t it directly affect the SQL OS and the overall performance of the server?

2. The bad assembly is also hosted in the same app domain as other assemblies;As all assemblies owned by same user are hosted in the same app domain; will this approach not affect other .net assemblies running in the same Appdomain?

Thanks!

1. Whether it will affect the overall performance of the server depends on whether or not the server has enough memory and resources to handle however much it is chewing up :) 2. Yes, other assemblies in the same AppDomain could be affected; but you can control this to some degree by splitting things up into different AppDomains by using different owners... But let's get down to the more important question: If this assembly is so badly written, and using so much memory, why are you hosting it in-process? Wouldn't this be a better candidate for the application tier? The situation you describe is really no different than a badly written or improperly implemented T-SQL stored procedure. If you have something eating up all of the RAM and processor time on your server, perhaps you need to take a hard look at it from an architectural point of view. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Yedu@.discussions.microsoft..com> wrote in message news:79d20459-8f1e-435f-97d9-9c8423852d11@.discussions.microsoft.com... As the CLR is hosted in the SQL OS. How will the SQL OS handle a situation wherein I have a badly written .net assembly which is chewing up lot of memory and other system resources of the server. 1. Won't it directly affect the SQL OS and the overall performance of the server? 2. The bad assembly is also hosted in the same app domain as other assemblies;As all assemblies owned by same user are hosted in the same app domain; will this approach not affect other .net assemblies running in the same Appdomain?Thanks!|||Adam,
On point 1 is it true that SQL OS punishes threads/assemblies that are performing poorly? How does this happen? On what criteria does SQL OS decide this?

On Point 2 I agree with you.

Thanks|||

Most of the memory for CLR comes outside SQL Server buffer pool. If the system runs out of memory then your CLR routine would get an OOM exception and SQL Server would abort this thread and consequently the memory would be freed.

For CPU, yes SQL Server punishes the threads that do not yield the scheduler in a reasonable amount of time. It would force them to yield and put them at the end of the scheduler.

Thanks,
-Vineet.

Point in Time Restore Part II

In the hereunder written message I talk about point in time restore.
It is now based upon the fact that there are no hardware problems or what so
ever.
I just would like to roll back to a situation of some time (minutes, hours
or what ever) ago.

Used to the ingres database a point in time restore can take place UP to
any, any, any time since the last FULL backup. (any time up to now !!!)

I can't understand why a point in time restore can only be done based upon
transaction log backups. The current transaction log is also available in my
opinion. (Turn off the power, turn on the power and you will notice that the
automatic recovery is based upon this transaction log file; so in that case
this file is used)

That's what my question is about. Is it correct that a point in time restore
in a SQL server environment can only be done up to the last transaction log
backup.

Bye

Arno de Jong,
The Netherlands.A.M. de Jong (arnojo@.wxs.nl) writes:
> Used to the ingres database a point in time restore can take place UP to
> any, any, any time since the last FULL backup. (any time up to now !!!)
> I can't understand why a point in time restore can only be done based
> upon transaction log backups. The current transaction log is also
> available in my opinion. (Turn off the power, turn on the power and you
> will notice that the automatic recovery is based upon this transaction
> log file; so in that case this file is used)
> That's what my question is about. Is it correct that a point in time
> restore in a SQL server environment can only be done up to the last
> transaction log backup.

Just because Ingress works in one way, there is no requirement for MS
SQL Server to work in that way too.

In SQL Server you need a transaction-log backup to do a point-in-time
restore, but since you can backup the transaction log at any point,
this is not any serious restriction.

I should add here that you must be running full or bulk_logged recovery
mode to have availability to point-in-time restore. With simple recovery
mode, this feature is not available.

In the case you want to undo a fatal SQL statement like an UPDATE without
a WHERE clause, you may be interested in exploring the third-party tool
Log Explorer from Lumigent, check out www.lumigent.com.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

please verify syntax

I haven't written any .net database updates before. I wrote the following code for a visual web developer application and it appears to work. There is no close function or anything. Will the adapter, builder, dataset, and row objects be cleaned up and disposed of properly? Please tell me if I need to include anything else. The connection object is made elsewhere. Thanks

Dim adapterAsNew Data.SqlClient.SqlDataAdapter("SELECT * FROM Inventory WHERE InUnique=" & ListValue.Value, connect)

Dim builderAsNew Data.SqlClient.SqlCommandBuilder(adapter)

Dim datasetAs Data.DataSet =New Data.DataSet()

adapter.Fill(dataset,"Inventory")

Dim datarowAs Data.DataRow = dataset.Tables(0).Rows(0)

datarow("InAgUnique") = Val(InAgUnique.Text)

datarow("InTyUnique") = Val(InTyUnique.Text)

datarow("InName") = InName.Text

datarow("InPnUniquePrimary") = Val(InPnUniquePrimary.Text)

datarow("InPnUniqueSecondary") = Val(InPnUniqueSecondary.Text)

datarow("InPnUniqueTertiary") = Val(InPnUniqueTertiary.Text)

datarow("InPnUniqueSupervisor") = Val(InPnUniqueSupervisor.Text)

datarow("InDescription") = InDescription.Text

datarow("InProblemInstructions") = InProblemInstructions.Text

adapter.Update(dataset,"Inventory")

If the connection is not open, Adapter would open it and once done with the operation, would close itself (as per .net 1.1), not sure of the chanegs in 2.0 though!

Please validate my query

Hi,

I have written a query for the following scenario :

I have 2 tables:

RecType - RecID(int), RecType(varchar)

Eg: 1 Joined, 2 Transferred, 3 Rusticated

Student - StudentID(int), RecID, Age(smallint), Sex(nchar(1)), DateOfJoining(datetime)

The query is :

select

[RecType] AS RecType,

cnt as TotalCount

from

(

select

RC.RecType,

count(*)as cnt

from

RecType as RC

innerjoin

Studentas stu

on RC.recid= STU.recid

where

STU.DateOfJoining>=convert(varchar(6),dateadd(month,-6,getdate()), 112)+'01'AND

(recid= 1 OR recid = 2 OR recid = 3)

groupby

RC.RecType

)as t

So the final results are as following

RecType Total Count

Joined 55

Transferred 3

Rusticated 0

However, I want to write a query that displays a grouping of students who have either joined, Transferred or rusticated grouped by each month, during the past 6 months. Please not that the date of joining/transfer etc of each student can be different in a month.

So the final results will be as following

RecType Total Count Month

Joined 23 1

Transferred 3 1

Rusticated 0 1

Joined 32 2

and so on..

How do I do this?

Thanks.

It is not bad idea to include the year while fetching the data for month...

Code Snippet

Select

RecType

,[Month]

,[Year]

,Count(*) as TotalCount

From

(

select

RC.RecType

,Month(STU.DateOfJoining) [Month]

,Year(STU.DateOfJoining) [Year]

from

RecType as RC

inner join Student as stu

on RC.recid= STU.recid

where

STU.DateOfJoining >= convert(varchar(6), dateadd(month, -6, getdate()), 112) + '01'

AND(recid= 1 OR recid = 2 OR recid = 3)

) as Data

Group by

RecType

,[Month]

,[Year]

|||

Hi,

Thanks for the query. I understood where I was going wrong.

Manivannan, I know that writing good T-sql queries comes from practise. However, can you suggest me a good book to learn T-sql for sql 2005.

Also can you please visit this post :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1664669&SiteID=1

Thanks.

Please helpInvalid Template Error

I have written a report for a client in Crystal 8 and when they try to run it they get an Invalid Template Error. I run it against various data and I have no problems at all. I have searched all over the Internet and can not find a solution to this problem and my client is getting very upset. Please help.Make sure all the required dlls and dependencies are installed/registered properly on the Client machine. In CR 8.5, there's a file called runtime.hlp that lists all the required files, maybe version 8 has something similar?