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...

Vlad-207446 (9/4/2015)

---

on one of the places I worked a few years back, we had similar situation.

although we had an update/upgrade windows from 12AM to 6AM once or twice per...

The problem is that is that 1000 might be too many or too few.

andy.m.guscott (9/4/2015)

---

I'm sure there's no need for the view, I'd just loop on the identity coulmn(or another suitable indexed column) and delete where less than min(ident column ) + 2000...

Lee Linares (8/17/2015)

---

Jonathan,

Great script. Thanks for sharing. One small problem though is this line:

IF CONVERT(int, @ServerVersion) < 10

The issue is that SQL Server 2008 does NOT support sys.dm_os_volume_stats which was...

Perry Whittle (8/14/2015)

---

Jonathan AC Roberts (7/24/2015)

---

Hi Christopher

The script only calls xp_fixeddrives if the version of SQL server is below 2008, When the version is 2008 or higher it uses dm_os_volume_stats...

I've got to say the first thing I thought as I got halfway through the article was "my god they need some indexes on those tables". You'd be surprised how...

Hi Christopher

The script only calls xp_fixeddrives if the version of SQL server is below 2008, When the version is 2008 or higher it uses dm_os_volume_stats and reports on the mounted...

Thanks for the script, it looks useful particularly the queries that return the growth settings. I'll use it when I need that information. Unfortunately, sys.dm_os_volume_stats doesn't exist in versions of...

I've written a script which works on both SQL Server 2005 and 2008. I'm running it from a registered server group. It all works but for some reason I'm getting...

SimonH (7/6/2015)

---

...

I can improve performance by performing a soft delete (Updating a bit column(isdeleted)) and then purging the records at a more convenient time. This runs in a second but...