view name is dbo.display_list and the code has the slow function which is showing up as top CPU..the function name and code is in the above code snippet.

stringmultitypes

T-SQL , SQL Server , As I said its a database view and legacy code.. That's how code was written  in the SQL Server 2000, 2005 days !

Thank you, The code of the view which uses this function is below:

CREATE VIEW [dbo].

AS

SELECT DISTINCT

dbo.protocol_id,

--dbo.sitecode,

cs.sitecode,

dbo.PI_name as pi,

'SW' + RIGHT('00000000' + CAST(dbo._id AS VARCHAR(8)), 8) AS _id,

dbo.subject,

CASE

WHEN state = 0...

list of values..

say 500 databases and each db has a table called queue with 1 million records . out of 500 databases only 300 are active.

Thank you for the response, but need explanation for this problem / solution.

CPU looked ok on the VM ( ~ 30% ) .  but view was opening ok till Friday,...

Also sql server error log is consuming memory and space. I noticed it was a 2 GB file and consuimg 1-2% memory , after I cleared it . memory reduced...

Thank you for the responses . Also is there a connection between CPU pressure and Memory , say I notice constant High CPU- 90% on a sql server instance, and...

Thank you for the inputs, we cannot change the name of server in config files to point to sqldevnew. It has to be sqldev.

Can you tell me the exact steps...

I noticed the default settings maxdop=0 and cost threshold of parallelism=5 and the production instance is a VMWare virtual machine.

I'm thinking changing cost threshold of parallelism to 50 ...

Just so every one knows , the sql server error log was the culprit which took about 36 GB memory , the IT guy used sysinternal tools to identify that....

only DB engine , no SSIS SSRS OR SSAS