rudi-bruchez/tsql-scripts

Transact-SQL scripts and gists

☯ tsql-scripts

Transact-SQL scripts and gists for administration and diagnostics.

You'll also find some management stored procedures

Feel free to use them and copy them. If you have significant improvements to propose, please fork the repo and propose a pull request.

Folders

You'll find here the following folders :

• azure-sql-database — queries for Azure SQL Database administration and diagonstics. Those queries use specific Azure SQL Database views and metadata. Some Extended events for Azure can also be found in extended-events/azure-sql-database.
• database-administration — queries for database maintenance, DDL generation, informations about security principals and privileges, SQL Server Agent, alerts and code to create the _dba database I use for some customers.
• database-information — metadata about databases : size, compression, transaction log, etc.
• diagnostics — diagnostics queries.
  • execution — diagnostics queries to inspect running queries, procedures and active transactions.
  • execution-stats — statistics about query performances.
  • IO — information about physical IO.
  • locking — locking and blocking.
  • memory — memory usage : buffer pool and plan cache.
  • query-store — Query Store management.
  • sessions — opened sessions.
  • tempdb — tempdb diagnostics queries, including version store.
  • wait_statistics — Wait statistics.
• extended-events — code to create extended events on-prem and on Azure SQL Database. You'll also find queries to read the content of the targets.
• hadr — queries for AlwaysOn Failover Clustering and AlwaysOn Availability Groups.
• index-management — missing indexes, index usage, fragmentation analysis, etc.
• monitoring — queries to monitor current operations, like backups, shrink or DBCC execution.
• powershell — Powershell scripts for administration.
• replication — Replication related queries.
• server-information — queries to get server / instance information.
• service-broker — Service Broker related queries
• stored-procedures — Stored procedure for quick info in your database, like getting active transactions, database information, memory status or sp_logspace, a replacement for DBCC SQLPERF (LOGSPACE).

tsql-scripts's Issues

Add schema name to statistics.sql

We can add the schema name in the select statement

DECLARE @tablename sysname = '%';
DECLARE @onlyAuto bit = 0; -- analyze only auto statistics

SELECT
SCHEMA_NAME(o.schema_id) as [Schema]
,OBJECT_NAME(stat.object_id) as tbl,
stat.object_id,
stat.name,
c.name as column_name,
CAST(sp.last_updated as datetime2(0)) as last_updated,
sp.rows,
sp.rows_sampled,
sp.steps,
sp.unfiltered_rows,
sp.modification_counter,
CONCAT('UPDATE STATISTICS ', QUOTENAME(SCHEMA_NAME(o.schema_id)), '.', QUOTENAME(o.name),
' ', stat.name, ' WITH FULLSCAN'
) as update_ddl,
CONCAT('DROP STATISTICS ', QUOTENAME(SCHEMA_NAME(o.schema_id)), '.', QUOTENAME(o.name),
'.', QUOTENAME(stat.name), ';'
) as drop_ddl
FROM sys.stats AS stat
JOIN sys.stats_columns sc ON stat.object_id = sc.object_id AND stat.stats_id = sc.stats_id
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
AND sc.stats_column_id = 1
JOIN sys.objects o ON stat.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE o.is_ms_shipped = 0
AND o.name LIKE @tablename
-- AND (stat.name LIKE 'WA_Sys%' OR @onlyAuto = 0)
AND (stat.auto_created = 1 OR @onlyAuto = 0)
ORDER BY tbl, column_name, stat.name
OPTION (RECOMPILE, MAXDOP 1);