Sergiy (10/26/2016)

---

yakko_Warner (10/26/2016)

---

I'm working on a project with a co-worker and I was working on creating a database for some integration of data for his workflow.

I was trying to figure...

Is the proc running inside a transaction issued from the application?

Deletes and updates should be avoided.

Use partitions for your fact tables and apply partition switching instead of deleting data.

Then you can consider creating a clustered columnstore index cos I think...

If the data is being paged out of physical...

Thanks both for the references.

Must say, this was a great day learning about this.(MS Dev's really earning their pay with this grand feature.)

Should be required reading.

Just thinking about the problems...

If you willing to go the CLR route, and the LOB is compressible, you can use compression functionality from System.IO.Compression to compress into a staging table locally, push smaller staging...

How many tempdb data files do you have?

Do you have snapshot isolation?

Let us have a look at the transaction.

Difficult to speak hypothetically.

Not sure if you want the readers to block updates or not to block updates or if you want incrased...

Its a serial plan caused by Scalar function and is impossible to parallelise with the current versions of SQLServer up to 2016.

This used to be the reason why partitioning with...

Scalar functions are a black-box from a cardinality estimation perspective.

If you're encountering plan quality issues due to them, consider inline table functions as an alternative – or even pulling...

Sqlsavy (10/19/2016)

---

Thank you all for the responses.

I've attached query plan with resrticated and unlimitted memory please have a look.

Thanks

query:

UPDATE dbo.table1

SET table1.field = somevalue

FROM dbo.table1 AS ABC

INNER JOIN dbo.table2 AS xyz...

Here is a previous post where Gail suggested running checkdb.

http://www.sqlservercentral.com/Forums/Topic1682406-2799-1.aspx

Also, here is Microsoft topic where they suggest patching to latest SP or CU.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ae0e6f71-ff63-49ee-bcaf-c53fa4dea6e4/error36-17-145-occurred-while-attempting-to-drop-allocation-unit?forum=sqldatabaseengine

Key lookups can cause deadlocks. An execution plan, and some table and index definitions would be good.

Will be hard unless we can see the code that is resulting in the error.

;WITH SumCount

AS (

SELECT NF.NAME

,NF.[Role]

,RRL.PriorityRank

,sum([Count]) AS SumOfCount

FROM NameFrequency NF inner join RoleRankLookup RRL on RRL.[role]=NF.Role

GROUP BY NAME

,[Role]

,PriorityRank

)

,MaxCount

AS (

SELECT *

,ROW_NUMBER() OVER (

PARTITION BY NAME ORDER BY SumOfCount DESC,PriorityRank asc

) RN

FROM SumCount

)

SELECT NAME

,[Role]

,SumOfCount

FROM...