Just thought I'd try this, I noticed that the description says "The second CTE, named L1, uses the BaseNum CTE to generate a second result set with one thousand rows....

You could do it with global temporary table and drop it after you've finished with it.

declare @sSql as nvarchar(1000)

declare @GuidTableName as nvarchar(50)

set @GuidTableName = NEWID()

set @sSql='set nocount on;;with...

And does this return anything in any of the date columns?

SELECT T.name, I.name, U.*

FROM sys.tables T

INNER JOIN sys.dm_db_index_usage_stats U

ON U.OBJECT_ID = T.OBJECT_ID

INNER JOIN sys.indexes I ON...

What index does the execution plan say it uses when you access it?

If the table...

Nice script, thank you.

Table Value Constructors were introduced in SQL 2008, so this script doesn't work with versions below that. I've rewritten the query inside the function so it will...

.

Ed Wagner (1/31/2016)

---

Jonathan AC Roberts (1/31/2016)

---

CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();

This is not good practice, I'm not sure it will even work in versions of SQL Server...

CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();

This is not good practice, I'm not sure it will even work in versions of SQL Server later than 2005.

You should be...

If it is parameter sniffing causing the problem then you can just alter the stored procedure to copy the parameters passed in into ones created within the SP.

Example:

GO

CREATE PROCEDURE mySPWithParameterSniffing

(

...

Instead of

WHERE Primary_Diagnosis between 'C00%' and 'C14%'

Just have :

WHERE Primary_Diagnosis >='C00'

AND Primary_Diagnosis <= 'C1499999'

I would try two compound indexes on the Message table.

CREATE INDEX IX_Message ON [Message](Value asc ,TypeId asc )

CREATE INDEX IX_Message_1 ON [Message](GroupId asc ,TypeId asc)

This should make the query...

keyurgondalia (9/13/2015)

---

Yes, i have more than 10 tables where data is in millions. One job is running to delete data, but when it goes in a table where there is...

carbogast (3/6/2013)

---

I did something very similar which is working great. The commit after each small batch is the real secret. I also put a WAITFOR DELAY '00:00:02' into...