PowerShell: Generate a report of failed SQL Agent jobs

$cms ="CMSServer"
$serverlist = invoke-sqlcmd -serverinstance $cms -database 'CMSCentralDB' -query "
    SELECT DISTINCT ServerName FROM AllSQLServers"

$qry = "
USE [msdb]

SELECT  @@SERVERNAME ServerName,
        j.name JobName,
        js.last_outcome_message LastOutcomeMessage,
        LastRunTime = dbo.agent_datetime(
            CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
            CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END) 
FROM    dbo.sysjobs j
        INNER JOIN dbo.sysjobservers js 
        ON j.job_id = js.job_id
  WHERE js.last_run_outcome = 0
  AND j.name like 'dba%'
  AND j.job_id NOT IN (  
select job_id from msdb.dbo.sysjobactivity
where start_execution_date IS NOT NULL AND stop_execution_date IS NULL
AND session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) )
"

$results = new-object object[] 0

foreach ($server in $serverlist)
{ 
$server.ServerName
trap [Exception] {continue}   
     $results += invoke-sqlcmd -Query $qry –serverinstance $server.ServerName -database 'msdb'    
}

foreach ($r in $results)  
{      
        $ServerName = $r.ServerName
        $JobName = $r.JobName
        $LastOutcomeMessage = $r.LastOutcomeMessage
        $LastRunTime = $r.LastRunTime
          
        invoke-sqlcmd -serverinstance $cms -database 'CMSCentralDB' -query " 
                 insert dbo.SQLAgentJobsFailed (ServerName, JobName, LastOutcomeMessage, LastRunTime) select  '$ServerName','$JobName','$LastOutcomeMessage','$LastRunTime'"
}

 

ColumnStore Indexes concepts and maintenance

What are Columnstore Indexes?

Understanding Columnstore Indexes in SQL Server Part 1

https://www.sqlpassion.at/archive/2017/01/30/columnstore-segment-elimination/

https://www.sqlpassion.at/archive/2016/03/21/clustered-columnstore-indexes-space-savings/

https://techcommunity.microsoft.com/t5/sql-server/columnstore-index-defragmentation-using-reorganize-command/ba-p/384653#

https://techcommunity.microsoft.com/t5/datacat/sql-2016-columnstore-row-group-merge-policy-and-index/ba-p/305255#

https://techcommunity.microsoft.com/t5/sql-server/adaptive-index-defrag/ba-p/383893

 

https://fonsecasergio.wordpress.com/2019/04/15/lesson-learned-1-columnstore-compressed-rowgroups-clustered-vs-nonclustered/

Rebuild Partitioned Clustered Columnstore Indexes

 

 

 

 

 

Useful SQL scripts to extract replication metadata about articles and columns

Show Articles And Columns For All Publications

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helpsubscription-transact-sql?view=sql-server-ver15

Useful PowerShell code to capture sp_helpsubscription output into a table

invoke-sqlcmd -serverinstance PublicationServer -database 'PublicationDatabase' -query sp_helpsubscription -OutputAs DataTables |             
Write-SqlTableData -ServerInstance PublicationServer -DatabaseName DBAMonitoringDB -SchemaName dbo -TableName PublicationDatabasesubscriptions -Force

SQL to extract replication articles information

USE DBAMonitoringDB
GO

select @@SERVERNAME source_server, p.publication publication_name, 'PublicationDatabase' source_database_name, s.article source_table_name,p.Columns source_list_of_columns, 
subscriber destination_server, [destination database], s.article destination_table_name,p.Columns 
destination_list_of_columns  from PublicationDatabasepublications p 
join PublicationDatabasesubscriptions s on s.publication=p.publication and p.article=s.article

PublicationDatabasepublications table extracted through below SQL in above URL Show Articles And Columns For All Publications

USE 'PublicationDatabase'


GO

SELECT syspublications.NAME                AS "Publication",        sysarticles.NAME                    AS "Article",        Stuff((SELECT ', ' + syscolumns.NAME AS [text()]               FROM   sysarticlecolumns WITH (nolock)                      INNER JOIN syscolumns WITH (nolock)                              ON sysarticlecolumns.colid = syscolumns.colorder               WHERE  sysarticlecolumns.artid = sysarticles.artid                      AND sysarticles.objid = syscolumns.id               ORDER  BY syscolumns.colorder               FOR xml path('')), 1, 2, '') AS "Columns" FROM   syspublications WITH (nolock)        INNER JOIN sysarticles WITH (nolock)                ON syspublications.pubid = sysarticles.pubid ORDER  BY syspublications.NAME,           sysarticles.NAME;

 

Extract consolidated list of SQL Servers from Central Management Server hierarchy

SELECT DISTINCT     
  c.name as Environment ---Higher level 1 folders   
  , a.server_name as ServerName    
  , case when c.name = 'PRD' then 1    
  when c.name = 'STG' then 2    
  when c.name = 'QA' then 3    
  when c.name = 'DEV' then 4    
  end as CriticalityOrder    
  , substring(b.name, CHARINDEX('SQL', b.name), LEN(b.name)) as SQLVersion ---If subfolder names has got SQL Server versions    
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal a    
JOIN msdb.dbo.sysmanagement_shared_server_groups_internal b    
    ON a.server_group_id = b.server_group_id    
JOIN msdb.dbo.sysmanagement_shared_server_groups_internal c    
    ON b.parent_id = c.server_group_id    
WHERE c.parent_id = ID number of the root container level 0  

SQL to copy Maintenance Plans between SQL Servers

SELECT        
    name, 
    id, 
    description, 
    createdate, 
    folderid, 
    ownersid, 
    cast(
        cast(
            replace(
                cast(
                    CAST(packagedata AS VARBINARY(MAX)) AS varchar(max)
                ), 
            'OldServer', 'NewServer') 
        as XML) 
    as VARBINARY(MAX)) as packagedata, 
    packageformat, 
    packagetype, 
    vermajor, 
    verminor, 
    verbuild, 
    vercomments, 
    verid, 
    isencrypted, 
    readrolesid, 
    writerolesid

FROM
    msdb.dbo.sysssispackages
WHERE        
    (name in ('MaintPlan1','MaintPlan2'))

SQL Script for updating statistics on all tables in a database

DECLARE @sql nvarchar(MAX);
SELECT @sql = (SELECT 'UPDATE STATISTICS ' +
                      quotename(s.name) + '.' + quotename(o.name) +
                      ' WITH FULLSCAN; ' + char(13) + char(10) AS [text()]
               FROM   sys.objects o
               JOIN   sys.schemas s ON o.schema_id = s.schema_id
               WHERE  o.type = 'U'
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql
EXEC (@sql)

Non-Clustered Indexes, Tipping point for Bookmark Lookup vs Table Scans

https://www.sqlpassion.at/archive/2013/06/12/sql-server-tipping-games-why-non-clustered-indexes-are-just-ignored/

When you have a specific query which produces a Bookmark Lookup, it depends on the number of retrieved pages if SQL Server is doing a full scan or using the Bookmark Lookup. The number of retrieved pages dictates if it’s good or not good to do a Bookmark Lookup! Therefore it is completely *irrelevant* how many records a specific query returns, the only thing that matters is the number of pages.

The Tipping Point is somewhere between 24% – 33% of the total pages the query has to read.

Before that range, the Query Optimizer chooses a Bookmark Lookup, after the query tipped over, the Query Optimizer produces an Execution Plan with a full scan of the table (with a Predicate inside the Scan operator). This also means that the size of your records defines where the Tipping Point lives. With very small records you can only retrieve a smaller set of records from your table, with larger records you can retrieve a huger set of records, before the query is over the Tipping Point and is doing a full scan.

Example:

A table consists of 4000 data pages in the leaf level of the Clustered Index, which means the Tipping Point is somewhere between 1000 and 1333 pages that we are reading for that specific table. This means you can read about 1.25% – 1.67% (1000/80000, 1333/80000) of the records from the table, before the Query Optimizer decides to do a full scan of the table.

Tipping Point in terms of number of rows = 24% – 33% of the total pages at leaf-level of heap/clustered index divided by total number of rows in the table

With Bookmark Lookups you can’t achieve Plan Stability!

A Non-Clustered Index, which isn’t a Covering Non-Clustered Index has a very, very, very, very, very selective Use Case in SQL Server! Think about that the next time when you are working on your indexing strategy.

Tipping Point is only relevant for Non-Covering Non-Clustered Indexes. An index which acts as a Covering Non-Clustered Index for a specific query doesn’t have a Tipping Point.