Yes. It's easy, just like it sounds: create the...

Please list all indexes on [BuildTable].

Also, shouldn't the SecondApply query have a condition based on FirstApply value, something like:

WHERE [BuildTable].[rownum] > [FirstApply].[rownum]

?

Edit: Added "WHERE" for clarity.

My approach below. I added a phone_id to the phone#s in the tables so I could join and show the expected output in the same query as the original...

Not necessarily. It depends...

SELECT DISTINCT m1.*

FROM Matches m1

WHERE

EXISTS(

SELECT 1

FROM Matches m2

...

SELECT time_int, CAST(DATEADD(MINUTE, time_int / 100 * 60 + time_int % 100, 0) AS time) AS time

FROM (

SELECT 0 AS time_int UNION ALL

...

If you (almost) always do lookups on the table based on "businessid", then cluster the table on businessid. If you need to keep the PK, make the PK nonclustered...

BWFC (12/4/2014)

---

ScottPletcher (12/3/2014)

---

BWFC (11/27/2014)

---

You're welcome.

One other thing, be careful using BETWEEN for date range queries. You're usually better using

where

[Date] >= @startdate

and

[Date] <= @enddate

Have a look at...

If you're not using service broker any more, then it's 100% safe to run this:

ALTER DATABASE msdb SET NEW_BROKER

which should clear that table and allow you to release the space.

BWFC (11/27/2014)

---

You're welcome.

One other thing, be careful using BETWEEN for date range queries. You're usually better using

where

[Date] >= @startdate

and

[Date] <= @enddate

Have a look at this...

To avoid rows already being modified, you can use the READPAST hint. That's often used for work-queue-type processing. If you can use that, there's no good reason to...

Ok, let's see what this shows:

IF OBJECT_ID('tempdb.dbo.#showfilestats') IS NOT NULL

DROP TABLE #showfilestats

CREATE TABLE #showfilestats (

Fileid smallint NOT NULL,

FileGroup...

417 pages is a trivial size, you can ignore it if that's the total.

Does "DB size shows 215 GB" include log file? If so, what size is the log...

I suggest RECOMPILE as well. That may take some time, but it will be trivial compared to a terrible plan.

Also, if you leave auto stats update on, explicitly set...

SQL also creates internal tables for certain types of data, including: primary XML indexes,

service broker, change tracking and a few other things. You'll need to include the size...