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...

A little piling on in this case is good.

I've worked on systems that let the code handle all referential constraints. It failed. Every time. I'm sure it's possible to do,...

You can open the properties of the first operator of the plan when looking at the graphical plan. That will contain the information about which plan guides have been used,...

GilaMonster (2/13/2014)

---

Grant Fritchey (2/13/2014)

---

The fact that you have four clustered index scans and a heap scan should be a bigger concern than sorting cost. And, what the heck, that's a...

There are lots of blog posts on DBA interview questions. Just do a search for them.

The fact that you have four clustered index scans and a heap scan should be a bigger concern than sorting cost. And, what the heck, that's a completely different execution...