That's a big proc... Hmmm...

Run it in the proc and as a query, and have a look at the execution plan of each, and the results of statistics IO.

Are...

Marios Philippopoulos (5/20/2008)

Thank you for the response, that makes sense.

Don't these tables need their stats updated though, just like user tables?

I see that some of them have not been...

I've seen that with web pages before. Very irritating.

With stored procs, as soon as the proc is modified, or any object that the proc depends on is modified, the cached...

Post the proc definition please.

I am thinking parameter sniffing....

Indeed. Is most intriguing...

Personally, I'm thinking that an instead of trigger might be a better idea, as it allows the insert to not actually be run, removing the need...

QFT.

Altering a procedure automatically invalidates its cached plan.

Check to make sure you don't have 2 versions, one in the dbo schema and one in a different schema

Methinks you're going too complex

CREATE TABLE #tempFileName (

FileNumber INT IDENTITY,

FileName varchar(50)

)

INSERT INTO #TempFileName (FileName)

SELECT DISTINCT Filename FROM #Temp

UPDATE #Temp

SET FileNumber = #tempFileName.FileNumber

FROM #tempFileName WHERE #tempFileName.FileName =...

Johann, have you come right?

If not, please post some code that reproduces the problem (complete with table creation script and inserts for sample data) and I'll see if I...

In SQL Server, unless you specify an order by, the order of rows returned is not guaranteed. You've just been lucky up until now.

I would suggest, any time you need...

There's an excellent article by Jeff Moden on running totals. You'll find it in the articles section here.

Actually, I just noticed this little gem...

ALTER TRIGGER [dbo].[trg_UpdateProcessedData]

ON [dbo].[ProcessedData]

AFTER INSERT

....

DELETE ProcessedData

WHERE HistoryID IN (SELECT...

Are you using a maintenance plan? I beleive in a maint plan you can specify system databases only, user databases only or all databases.

If it's set to all, is the...

It shouldn't. Rownumber starts at 1, regardless of how many times you run it. It's the row numbers for the current query, nothing more. Each time the partition columns changes...

Sandy (5/20/2008)

Books Online is a good place to start.

Johann, I could probably shave a couple more seconds off with proper indexing. In case...