This is so typical of these types of situations. As usual, there is a vastly better clustering key available than identity.

The (first) clustering key for this table should be...

If you need to reference that table in multiple places, just load it into a temp table, global temp table or even a regular table in the tempdb db. ...

It's...

You could either restore the master db --or-- bring down SQL, copy the original master files over the new master files, and start SQL normally. You'll see a lot...

Large data files are typically not an issue, assuming IFI is on. But the log file must always be preformatted. If possible, you'll want to reduce the log...

Edit: An inline table-valued function is much more efficient, and your code is easily converted to one, as below:

CREATE FUNCTION [dbo].[fn_GetDupsFeeds](

@ContractNbr int,

@DOCId int,

@FeedTime int,

@ServiceTypeId varchar(10),

@FlightStart smalldatetime,

@FlightEnd smalldatetime = null,

@IsMonday...

Also make sure the msdb db is not overloaded with, say,...

WHEREtbl.Body + '.' LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%'

Most likely rather than a blank the first char in the column is some non-displayable character, such as a tab (char(9)), cr (char(13)), or line feed (char(10)).

The single biggest SQL tuning payback, after any infrastructure issues are addressed, is making sure you have the best clustered index on every significant table. Hint: This is most...

If you (almost) always process data by month (or other date range(s) too) then cluster the table on the related date column. You should far less performance issues then.

Edit:...

FridayNightGiant (8/14/2015)

---

ScottPletcher (8/12/2015)

---

Here's a method without an external function, just out of curiosity mostly about how it would perform vs. other methods. It uses a "standard" tally table. ...

Here's a method without an external function, just out of curiosity mostly about how it would perform vs. other methods. It uses a "standard" tally table. This method...

By the way, you should change the WHERE clause to make it "sargable":

...

WHERE SamplingDate >= '20150101' AND SamplingDate < '20160101'

...

For best overall performance, for this proc and for others, I strongly urge you to switch the clustering on the Performance to be on ReportDate, or, if you prefer, (...