fillfactor = 80 is rather low. What did you base that on?

sgmunson (7/10/2014)

---

That has some nasty potential performance with the OR in the JOIN. As we don't know much about the actual desired result, I decided making a guess...

By far the biggest tuning pay back is to get the best clustering index on all tables, esp. large ones. Until you do that, the rest is honestly pretty...

I must be missing something, because with the limited search criteria so far it looks straightforward. I can't tell whether they will pass in a single search id or...

If you do many lookups by ManagerID, you'd likely be able to avoid messing with all this by clustering the table differently:

CREATE TABLE Employees (

ID INT...

You should also be able to use a CROSS APPLY to a "SELECT TOP (1) ..." to get the most recent row for any given user.

I've been a SQL Server DBA for 13+ years and I...

SELECT

COALESCE(t1.Account, t2.Account) AS Account,

MAX(Date) AS Date

FROM (

SELECT Account, MAX(Date) AS Date

FROM dbo.table1

...

The single biggest factor in overall query performance is getting the best clustered index on each table (hint: it's rarely an identity column). Typically you'd start with a higher...

I'm virtually certain the tables are clustered improperly. That should be fixed before you do the partitioning. It's even possible you won't need to partition if you put...

You can, and definitely should, avoid converting cStaging.cDate if you can.

--if "cDate" is a datetime column:

--instead of this:

--WHERE (CONVERT(DATE, cStaging.cDate) BETWEEN @startdate AND @endDate) AND (CONVERT(DATE,...

sqldriver (7/5/2014)

---

TheSQLGuru (7/1/2014)

---

create index ix_Split_1 on #Split_1 (NewParameter)

Good call on the joining to a split-built derived table and how putting that into a temp table could make things faster. ...

By far the biggest payback is in correcting clustered indexes if the wrong key column(s) were chosen for it. And that alone often allows very large numbers of other...

I also make the doc tables "live". That is, the code that created/initialized/set up a new db should use the "documentation" table to determine whether the table is static...

TheSQLGuru (7/2/2014)

---

ScottPletcher (7/2/2014)

---

I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions of...