this is also one of the major ways I use this SP:

for COMPARISON of several weeks/days/etc:    (will also further modify using Federico's guidance above)

--  ...

NOTES:

table WhoIsActiveB, queried by the above SP for Waits summary is populated via the following call to sp_WhoIsActive every 1 minute:

EXEC sp_WhoIsActive @get_outer_command = 1, @get_plans = 1, @find_block_leaders =...

Thank you all for your most valuable responses/comments/advice!

I also want to share here the SP that I created that produced the report shown in the question.

USE [DBMaint]
GO


Create...

This is the report of all our waits at this time, after GAM and PFS contention got resolved. This is the report that

is created based on capture of all active...

Yes, we have checked all GAM , PFS etc. stuff.

and added 4 files to tempdb.

after which the allocation contention became nearly nonexistent.

however I am looking at the sizes of all...

It all also depends on the needs of a particular IT shop that is looking to hire a DBA.

Of course there are many unspoken factors, too.

Thank you!

VARCHAR(MAX) column values will be stored IN the table row, space permitting. So if you have a single VARCHAR(MAX) field and it's 200, 300 byte, chances are it'll be stored...

Alternatively, you can experiment with OPTION(RECOMPILE) in individual queries within the SP

or WITH RECOMPILE for entire SP.  In one case recently not using any cached exec plans but recompiling  using...

Yes, am engaging right now into doing the test you mentioned.

thank you.

I was just attaching the Excel file to answer Jeff's questions.

Regarding some best practices/col datatypes... useful info, too, but...

I attached an Excel file with all the stats and details.

yes, only 38 max length.

2 cols only.

Thanks everyone for your valuable input!

as far as the mentioned usage of the table is concerned, this is what the indexes' usage stats are:

Scott, but how does your alert know that a deadlock has occurred ? via what mechanism?  Is it a SQL Agent Alert kind of alert?..

dbcc tracestatus (1222,1204) returns all zeros.

so no info stored that way either. No other way to view the historical deadlock occurrences, right? Unless I myself write a collector or enable...