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.

Poorly Performing Unicode Query

The main performance issue is performaing the "N" function infront of the strings in the where clause. I get 20x-45x perfromance boost by removing it, but unfortunately my reporting tool doesn't allow me that option.

Anything I can do on the DB end to solve this? The DB is not unicode.

tia,

paul

SELECT

MAX(Dim9s.FunctionDescription) [Dim9s_FunctionDescription],

MAX(Dim9s.BU_Description) [Dim9s_BU_Description],

SUM(convert(decimal(18,2),ixvTransAx.GLS_AMOUNTMST)) [GLS_AMOUNTMST]

FROM ixvLedgerBalanceDimTransAx AS ixvTransAx

JOIN ixvDim9s AS Dim9s ON (ixvTransAx.Dim9Id=Dim9s.Id)

LEFT OUTER JOIN ixvAccounts AS Accounts ON (ixvTransAx.AccountId=Accounts.Id)

JOIN ixvCompany AS CompanyId ON (ixvTransAx.CompanyId=CompanyId.Id)

WHERE 1=1

AND ((1=1)) AND ((Dim9s.Client ='M99') OR Dim9s.Client IS NULL)

AND ((1=1)) AND ((Accounts.Client ='M99') OR Accounts.Client IS NULL)

AND ((Accounts.Code BETWEEN N' 5000' AND N' 6796') AND (Accounts.ACCOUNTPLTYPE != N'^AC_Total^'))

AND ((Dim9s.BU_Description = N'PI/Dwights') AND (Dim9s.OrganizationalViewDescription = N'2006 Current'))

AND ((1=1))

AND (CompanyId.Code = N'56')

AND ((ixvTransAx.TransDate >= '2006-3-1') AND (ixvTransAx.TransDate < '2006-4-1'))

AND NOT (ixvTransAx.GLS_AMOUNTMST IS NULL)

GROUP BY Dim9s.FunctionDescription, Dim9s.BU_Description

Hi,

I recommend that you post your question on the Transact-SQL forum. The Documentation forum is usually not the best spot for code-specific questions.

Regards,

Gail

Poor performance with functions in selects

Hi

We have the following query that we were trying to execute and it performs very poorly...

SELECT ITEM_ID,

PART_NO,

PART_TITLE,

dbo.SFDB_NVL_VARCHAR(DRAWING_NO_DISP,'N/A') AS DRAWING_NO,

dbo.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS DRAWING_CHG,

DRAWING_NO_DISP,

DRAWING_CHG_DISP,

REV_SOURCE,

ITEM_ID AS ASSY_ITEM_ID,

dbo.SFDB_NVL_VARCHAR(DRAWING_NO_DISP,'N/A') AS ASSY_DRAWING_NO,

SFMFG.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS ASSY_DRAWING_CHG

FROM SFSQA_CHAD_PART_DSPTCH_DSP_SEL

ORDER BY PART_NO

The function SFDB_NVL_VARCHAR is nothing but a replication of nvl function of oracle.

CREATE

FUNCTION [dbo].[SFDB_NVL_VARCHAR]

(

@.VI_SOURCE VARCHAR(4000),

@.VI_VALU_IF_NULL VARCHAR(4000)

)

RETURNS VARCHAR(4000)

AS

BEGIN

IF ISNULL(@.VI_SOURCE,'') =''

RETURN @.VI_VALU_IF_NULL

RETURN @.VI_SOURCE

END

Now if the same query was re-written to remove the function and instead use a CASE WHEN block the performance is up significantly....

SELECT ITEM_ID,

PART_NO,

PART_TITLE,

CASE WHEN DRAWING_NO_DISP IS NULL THEN 'N/A'

WHEN DRAWING_NO_DISP = '' THEN 'N/A'

ELSE DRAWING_NO_DISP

END DRAWING_NO,

CASE WHEN DRAWING_CHG_DISP IS NULL THEN 'N/A'

WHEN DRAWING_CHG_DISP = '' THEN 'N/A'

ELSE DRAWING_CHG_DISP

END DRAWING_CHG,

DRAWING_NO_DISP,

DRAWING_CHG_DISP,

REV_SOURCE,

ITEM_ID AS ASSY_ITEM_ID,

CASE WHEN DRAWING_NO_DISP IS NULL THEN 'N/A'

WHEN DRAWING_NO_DISP= '' THEN 'N/A'

ELSE DRAWING_NO_DISP

END ASSY_DRAWING_NO,

CASE WHEN DRAWING_CHG_DISP IS NULL THEN 'N/A'

WHEN DRAWING_CHG_DISP = '' THEN 'N/A'

ELSE DRAWING_CHG_DISP

END ASSY_DRAWING_CHG

FROM SFSQA_CHAD_PART_DSPTCH_DSP_SEL

ORDER BY PART_NO

Now the execution plan in both cases shows that cost of execution of the funtion is 0%. This is weird. How can function perform so badly for as simple as the one I mentioned,.

Regards

Imtiaz

Calling functions in a SELECT statement is itself not a very good idea for the same reason that for every row that was fetched in the SELECT result, SQL Server has to execute the function (for which it has to check for an available execution plan, find the most optimum plan, use it, then return the result).|||

Tiaz:

I think Dinakar is right. I am afraid that this is just "the nature of the beast". Code that does not use functions will tend to execute better. Here are a couple of threads earlier this year that discussed around the issues of functions; reviewing these might be helpful.

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

Dave

|||But even with such a function the execution should be fast...What is happening here...?|||

Well, I also just noticed this line:

SFMFG.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS ASSY_DRAWING_CHG

Is this a typo in which you really want DBO?

|||OKAT THAT WAS A TYPO.....I HAD THAT SCHEMA BUT REMOVED IT NOT TO CONFUSE U FOLKS....IT IS DBO