It's all about speed. When you need really fast OLTP processing, you go with the in-memory tables and the compiled procedures. The limitations are too severe for any small or...

Michael Valentine Jones (2/14/2014)

---

I am not sure what "overnormalized" even means, but anytime...

Yeah, it could, although you might be better off creating the index on the table after you load it.

It sure sounds to me like the query could be tuned, but as to the long running stuff, I'd assume contention on resources. While it's running, have you collected wait...

Everything Kevin said, then check out the execution plan to understand where the indexes are getting used and where they aren't. Just tossing them vaguely towards a query never works...

This stuff is likely killing your performance too. It can't use indexes

DATEPART(dw, d.GREGORIANDATE) <> 1 AND DATEPART(dw, d.GREGORIANDATE) <> 7'

I've done this using both T-SQL and PowerShell, but the trick is, you have to be able to set up access to a common location. If you can't do that,...

Generally, this is when you go to your backups and restore the database to a point in time prior to the problem.

Evil Kraig F (2/13/2014)

---

Grant,

My understanding was the short circuit capabilities of the CASE statement would improve performance, but in a limited variety.

Instead of a simple data change on the table...

Recompile will override, yes. You can't really see query hints through the execution plan. You can see them in the SQL text that is stored with the plan.

OCTom (2/13/2014)

---

I'd like to add my nickle's worth from my perspective as a developer.

FKs make my job so much easier. I don't have to write the code that prevents orphaned...

Idera's product works. But it will cause hits to i/o and you will have to have storage available for it, although not initially.

Cool question and kind of fun. I tested it out this way:

IF (SELECT OBJECT_ID('Test1')

) IS NOT NULL

DROP TABLE dbo.Test1;

GO

CREATE TABLE...

For just queries... I'd suggest going with Solarwindows (formerly Confio) Ignite. That's one of the best third party tools for just going after query performance. It's not so good with...

Also, check the cost threshold for parallelism on the server. The default value is 5 and that is radically low for most systems. Changing that to an appropriate value (say...