I work with a motley crowd of developers and have the opportunity to give lunch and learn training sessions on SQL Development.

Most recently, I put together a .zip file of T-SQL scripts that developers would find handy.  You can download it here.

Here's a summary:

Missing Indexes - missing indexes.sql, missing index setup demo.sql - more about this here

Index Usage - index usage.sql

Sessions and Requests - sessions and requests.sql - more about this here

Most expensive queries - worst queries.sql - from MS PSS

Tables without clustered indexes - tables without clustered indexes.sql

Deadlocks XEvents - deadlocks in xevents.sql -  more about this here

Dependencies - dependencies.sql -  more about this here

Stored Proc Execution plans - cached exec plans.sql -  more about this here

Volume Stats - volume stats.sql

Modules vs. Routines - modules vs routines.sql -  more about this here