Yes completely agree.

Just...

Have you manually set the processor affinity or is it set to Automatic ?

Do you require changes replicated between different subscribers ?

It sounds that there are to many plans in your plan cache memory.10% of buffer pool memory from 4gb-64gb + 5%>64gb is allocated to plan cache.

Can you post the output...

In our SQL environment my observation always had been that TVP outperformed all the custom TSQL splitter functions and CLR methods.In some instances the TVP performance was on par with...

Also maybe this:

SELECT Datepart(yyyy, d.date) AS "Year",

Count(*) ...

Only scope of improvement I can see in the given state is to add an NC index on #OrderDetailsHistory for columns(TSID DESC,ActionCode,OrderID).

CREATE INDEX NCI_OrderDetailsHistory

ON #OrderDetailsHistory(TSID DESC,ActionCode,OrderID)

The APPLY clause...

Yes I think you will have to run DBCC CHECKTABLE incase DBCC CHECKDB does not return any error.To run DBCC CHECKTABLE first identify all the possible tables that have LOB...

This ?

DECLARE @str VARCHAR(40)='ek/df/cv/'

DECLARE @strreverse VARCHAR(40)=Reverse(@str)

SELECT Substring(@strreverse, 1, Charindex('/', @strreverse) - 1)

Is the column size for Clientname VARCHAR(64) enough ?

Out of curiosity wondering how does this perform ?

SELECT v.parcel,v.value

FROM @m M

JOIN @v-2 v

on v.Parcel = m.Parcel

group...

Using SQL Server 2012 function in use

select * from

(

select

*,CUME_DIST () OVER (PARTITION BY marker ORDER BY val)-

RANK() OVER (PARTITION...

One way would be to create a UDF to check the values and use that as constraint for the table.

Make sure the Business Layer is ready.Never go for table design unless your business layer is not ready.That's my personal opinion.

You will have to rollback the transaction in the CATCH block.