Foreign Keys are an important part of the data integrity problem (and can also offer benefits to the optimizer in some cases).

I agree with the other poster that a...

Phil Parkin (7/13/2016)

---

muth_51 (7/13/2016)

---

Table has cluster index with default setting fill factor. The table has around 15 indexes including clustered. I just went through all indexes and some are set...

Glad you found a root cause, and were smart enough to look for other similar situations!

Sounds like a use case for http://www.red-gate.com/products/dlm/dlm-dashboard/ :w00t:

Does this table have a clustered index or not? Can you provide the full create table script for the table (including all indexes)?

One thing you kind of have control over is indexing. If a FK'd table is being updated and that is locking your other table and the update is doing a...

1) What build of 2014 are you on?

2) Version store is in play. Did you add in Always On by any chance? Was some form of snapshot isolation used in...

1) Please provide the FULL definitions of the table. All indexes, data types, etc. included.

2) It is possible that the app (which you don't have control over) is simply...

Alan.B (7/12/2016)

---

TheSQLGuru (7/12/2016)

---

Alan: your PK breaks inserts of >1 row with the same datetime value, which is going to happen both with multi-row batch inserts as well as concurrent access...

sqld-_-ba (7/12/2016)

---

@RVarn , there is no external pressure . this is a SQL-only box (no apps), with 32GB ram on OS and 28GB max mem for SQL

There is no 'internal...

Jeff: I would almost never want to pay the cost to do a SORT/ROW_NUMBER() in SQL Server to present that data. That is cost that just keeps on hitting you...

sqld-_-ba (7/12/2016)

---

Our production server (VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads...

Need a different consultant in the short-term then, IMNSHO! 😎

1) Since systems are being taken offline and jobs are failing I STRONGLY urge you to get a professional consultant on board to stop the bleeding and get the trains...

Note that you only need the ROW_NUMBER() stuff if you are inserting multiple rows in one batch. If you are like almost all of the clients I come across and...

I don't know of an "automated" way to do this - i.e. a system or generated thing that will handle this for you. Number 1 is easy - you simply...