Ah yes, I should have spotted that - you're basing it on the current date, not on any value in the column.  Yes, that should work.  My only advice would...

Is DecoderCode always a number?  If so, store it in a column with a numeric data type.  If you don't have any control over the database design, convert it to...

AND dbo.manifests.manifest_date >= DATEADD(DAY,CASE WHEN DAY(manifest_date)=1 THEN -1 ELSE 0 END, DATEADD(MONTH,
      DATEDIFF(MONTH, 0,
       CURRENT_TIMESTAMP),
      0)

You can also use the...

If the only permissions they have are those you mentioned, they won't be able to do the things you don't want them to.  But if you're worried about them getting...

I think your query could do with some serious tuning if it's chewing up 250GB of tempdb for 20 million rows.  Does the whole thing run in an explicit transaction,...

Not without capturing them as you go, no, or maybe with the Query Store in SQL Server 2016 and later.  The best you can do is get the aggregates (max...

What have you already tried, and what are you struggling with - the queries to do the actual health checks, or getting them to run on all servers?  I think...

You can try this for yourself - not on a production server, though.  Use sys.dm_os_buffer_descriptors to see what pages of data are in memory.  Use DBCC...

Yes, if you don't want gaps for missing months then you'll need to left join to a calendar table.  You can either use a permanent table in your...

I think the DMV you're looking for is called something like sys.dm_index_physical_stats.  There'll be plenty of sample queries out there if you search for them.

John

The referenced page mentions that the connection must identify itself as the application in question.  I answered that this isn't possible, since any connection can identify itself as any application.

Difficult to diagnose after the event.  Maybe out-of-date statistics, or parameter sniffing.  If you could capture some actual execution plans next time it happens, that would help.  Do you have...

CREATE TABLE #PleasePostConsumableDDLNextTime (
     id int
,    status char(4)
,    crdate date
    );
INSERT INTO #PleasePostConsumableDDLNextTime
VALUES
     (1,'open','01/31/2017')
,    (1,'IP','03/31/2017')
,    (1,'cl','05/31/2017')

SELECT
     p.id
,    p.status