sp_MSestimatesnapshotworkload (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSestimatesnapshotworkload(nvarchar @publication)

MetaData:

 create procedure sys.sp_MSestimatesnapshotworkload (  
    @publication sysname  
    )  
as  
begin  
    set nocount on  
    set DEADLOCK_PRIORITY LOW  
  
    declare @taskload                       int  
    declare @bigtaskload                    bigint  
    declare @table_created                  bit  
    declare @retcode                        int  
  
    --  Publication info  
    declare @pubid                          int  
    declare @compress_snapshot              bit  
    declare @snapshot_in_defaultfolder      bit  
    declare @alt_snapshot_folder            nvarchar(255)  
    declare @enabled_for_internet           bit  
    declare @ftp_password                   nvarchar(524)   
    declare @ftp_subdirectory               nvarchar(255)  
    declare @pre_snapshot_script            nvarchar(255)  
    declare @post_snapshot_script           nvarchar(255)      
      
    --  Per publication summary stats  
    declare @numarticles                    int  
    declare @totalrowcount                  int     
    declare @needsysprescript               bit  
    declare @copysnapshot                   bit  
    declare @deletefiles                    bit  
    declare @scriptproccost                 int  
    declare @addcommandstotal               int  
    declare @numscripts                     int  
    declare @totalbcpcostper100             int  
    declare @numprescriptcommands           int   
  
    select @numarticles = 0  
    select @totalrowcount = 0  
    select @needsysprescript = 0  
    select @copysnapshot = 0  
    select @deletefiles = 0  
    select @scriptproccost = 0  
    select @deletefiles = 0  
    select @addcommandstotal = 0  
    select @numscripts = 0  
    select @totalbcpcostper100 = 0  
    select @numprescriptcommands = 0  
  
    --  Per article variables  
    declare @artid                          int  
    declare @schema_option                  int  
    declare @creation_script                nvarchar(255)  
    declare @type                           tinyint  
    declare @objid                          int  
    declare @rowcount                       int  
    declare @name                           sysname  
    declare @pre_creation_command           int  
                    ,@result int  
  
  
    select @table_created = 0  
  
    --  Security check  
    select @retcode = 0  
    EXEC @retcode = sys.sp_MSreplcheck_publish  
    IF @@ERROR <> 0 or @retcode <> 0  
        return (1)  
  
    select @pubid = null  
    select @compress_snapshot = 0  
    select @snapshot_in_defaultfolder = 0  
    select @alt_snapshot_folder = null  
    select @enabled_for_internet = 0  
    select @ftp_password = null  
    select @ftp_subdirectory = null  
    select @pre_snapshot_script = null  
    select @post_snapshot_script = null  
    --  Validate publication and retrieve publication information   
    select @pubid = pubid,   
           @compress_snapshot = compress_snapshot,  
           @snapshot_in_defaultfolder = snapshot_in_defaultfolder,  
           @alt_snapshot_folder = alt_snapshot_folder,  
           @enabled_for_internet = enabled_for_internet,  
           @ftp_password = ftp_password,  
           @ftp_subdirectory = ftp_subdirectory,  
           @pre_snapshot_script = pre_snapshot_script,  
           @post_snapshot_script = post_snapshot_script  
           from syspublications  
     where name = @publication  
    if @pubid = null  
    begin     
        raiserror(20026, 11, -1, @publication)  
        return (1)  
    end  
  
    --  Only attempt to get an application lock if the caller is   
    --  db_owner as the intention of the lock is for coordinating between  
    --  the snapshot agent (db_owner) and ddl replication (requires db_owner  
    --  via sp_MSprep_exclusive)  
    IF is_member(N'db_owner') = 1  
    BEGIN  
    EXEC @result = sys.sp_getapplock @Resource = @publication,   
                @LockMode = N'Shared',   
                @LockOwner = N'Session',   
                @LockTimeout = 0,  
                @DbPrincipal = N'db_owner' --  Note that we already require db_owner because of security check performed at the beginning of the procedure  
        IF @result < 0  
        BEGIN  
            RAISERROR(21385, 16, -1, @publication)  
            RETURN (1)  
        END  
    END  
  
    --  An artid of -1 means the task is a per publication  
    --  task  
  
    --  Task id mapping - This is the list of task whose completion can be   
    --  reported by the snapshot agent  
    --  0 - Total workload (not really a task)  
    --  1 - Schema script generation  
    --  2 - Trigger script generation   
    --  3 - XProp script generation  
    --  4 - Bcp file generation  
    --  5 - Activating subscription (estimate number of subscriptions?)  
    --  6 - Adding snapshot commands  
    --  7 - System pre-script generation  
    --  8 - Flushing folder for scripts  
    --  9 - Constraint script generation  
    --  10 - Copying pre-script  
    --  11 - Copying post-script  
    --  12 - Copying custom schema creation script  
    --  13 - Index script generation  
    --  14 - Flushing the cabinet  
    --  15 - Adding rowguid column  
    --  16 - Setting article procs  
    --  17 - Adding merge triggers  
    --  18 - Generating system table scripts  
    --  19 - Generating system table bcp files  
    --  20 - Making publication generation  
    --  21 - Generating and setting conflict script  
    --  22 - Generating publication views  
  
    --  Weights and overheads  
    --  DMO Script generation - 7/script  
    --  BCP 5 overhead + 5/100 rows  
    --  Flush folder for bcp 2/100 rows  
    --  Delete file 2  
    --  System pre-snapshot script 1 overhead + 0.5/pre-creation command  
    --  Copying user file 2  
    --  Adding file to cabinet 1 for scripts and 1/100 rows in bcp file  
    --  Loading article info 3  
    --  Delete bcp file 2/100rows   
    --  Copy bcp file  2/100rows    
    --  Flush scripts folder 1/2 scripts  
    --  Flushing cabinet per 5 scripts 1  
    --  Flushing cabinet per 500 rows 1  
  
    --  Here is the list of cost factors   
    declare @addcommandcost     int  
    declare @dmoscriptcost      int  
    declare @bcpoverhead        int  
    declare @bcpcostper100      int  
    declare @flushper100        int  
    declare @flushperscript     int  
    declare @delfilecost        int  
    declare @syspreoverhead     int  
    declare @syspreper2commands int  
    declare @addbcptocabper100  int  
    declare @addscripttocab     int  
    declare @copyfilecost       int  
    declare @loadartinfo        int      
    declare @deletebcpper100    int  
    declare @copybcpper100      int  
    declare @flushper2scripts   int  
    declare @flushcabper5scripts int  
    declare @flushcabper500bcprows int  
    declare @maxint             int  
  
    select @addcommandcost = 2  
    select @dmoscriptcost = 7  
    select @bcpcostper100 = 20  
    select @bcpoverhead = 2  
    select @flushper100 = 2  
    select @flushperscript = 3  
    select @delfilecost = 2  
    select @syspreoverhead = 1  
    select @syspreper2commands = 1  
    select @addbcptocabper100 = 1  
    select @addscripttocab = 1  
    select @copyfilecost = 2  
    select @loadartinfo = 3      
    select @deletebcpper100 = 1  
    select @copybcpper100 = 2  
    select @flushper2scripts = 1  
    select @flushcabper5scripts = 1  
    select @flushcabper500bcprows = 1  
    select @maxint = 2147483647  
  
    create table #workload_breakdown  
    (  
        name        sysname collate database_default,  
        artid       int,  
        taskid      int,  
        taskload    int  
    )  
    if @@error <> 0  
        goto Failure  
  
    select @table_created = 1  
  
    --  Per publication work load estimate that can be done using publication  
    --  properties alone  
      
    --  Adding snapshot header commands  
      
    --  Snapshot header begins + Snapshot header ends +  
    --  directory command + Snapshot trailer command = 3 * @addcommandcost  
    select @addcommandstotal = 4  
      
    --  Alternate snapshot folder = 1 * @addcommandcost  
    if @alt_snapshot_folder is not null and  
       @alt_snapshot_folder <> N'' and  
       @snapshot_in_defaultfolder = 1  
    begin  
        select @addcommandstotal = @addcommandstotal + @addcommandcost  
    end  
  
    --  Ftp commands  
    if @enabled_for_internet = 1  
    begin  
        --  ftp_address and ftp_port must be there +=2 * @addcommandcost  
        select @addcommandstotal = @addcommandstotal + 2 * @addcommandcost  
  
        --  ftp_password += 1 * @addcommandcost  
        if @ftp_password is not null and  
           @ftp_password <> N''  
        begin  
            select @addcommandstotal = @addcommandstotal + @addcommandcost  
        end  
          
        --  ftp_subdirectory += 1 * @addcommandcost  
        if @ftp_subdirectory is not null and  
           @ftp_subdirectory <> N''  
        begin  
            select @addcommandstotal = @addcommandstotal + 1 * @addcommandcost  
        end  
    end  
  
    --  Compressed archive path  
    if @compress_snapshot = 1  
    begin  
        select @addcommandstotal = @addcommandstotal + 1 * @addcommandcost  
    end      
  
    --  Snapshot trailer command  
    select @addcommandstotal = @addcommandstotal +  @addcommandcost  
  
    --  Need to copy files?  
    if @alt_snapshot_folder is not null and @alt_snapshot_folder <> N''  
       and @snapshot_in_defaultfolder = 1 and @compress_snapshot = 0  
    begin  
        select @copysnapshot = 1  
    end  
  
    --  Compute per-script file post processing cost and bcp cost per 100 rows  
    --  Need to delete files?  
    if (@alt_snapshot_folder is null or @alt_snapshot_folder <> N''  
       or @snapshot_in_defaultfolder = 0) and @compress_snapshot = 1  
    begin  
        select @deletefiles = 1  
    end  
  
    select @totalbcpcostper100 = @bcpcostper100  
    --  Copy file?  
    if @copysnapshot = 1  
    begin  
        select @scriptproccost = @scriptproccost + @copyfilecost  
        select @totalbcpcostper100 = @totalbcpcostper100 + @copybcpper100  
    end  
  
    --  Add file to cabinet?  
    if @compress_snapshot = 1  
    begin  
        select @scriptproccost = @scriptproccost + @addscripttocab   
        select @totalbcpcostper100 = @totalbcpcostper100 + @addbcptocabper100  
    end  
  
    --  Delete file?  
    if @deletefiles = 1  
    begin   
        select @scriptproccost = @scriptproccost + @delfilecost  
        select @totalbcpcostper100 = @totalbcpcostper100 + @deletebcpper100  
    end  
  
    --  Pre/Post-snapshot scripts, has to be computed after the   
    --  the per-script post-processing cost is calculated   
    if @pre_snapshot_script is not null and  
       @pre_snapshot_script <> N''  
    begin  
        --  Cost of copying the script file  
        select @taskload = @copyfilecost + @scriptproccost  
        insert #workload_breakdown values (N'', -1, 10, @taskload)          
        --  Cost of adding the pre-snapshot command  
        select @addcommandstotal = @addcommandstotal + @addcommandcost  
        --  Increment scripts counter  
        select @numscripts = @numscripts + 1  
    end  
  
    if @post_snapshot_script is not null and  
       @post_snapshot_script <> N''  
    begin  
        --  Cost of copying the script file  
        select @taskload = @copyfilecost + @scriptproccost  
        insert #workload_breakdown values (N'', -1, 11, @taskload)  
        --  Cost of adding the post-snapshot command  
        select @addcommandstotal = @addcommandstotal + @addcommandcost  
        --  Increment scripts counter  
        select @numscripts = @numscripts + 1  
    end  
             
    --  Estimate the break down of per article tasks   
    declare hCarticles cursor local fast_forward  
    for select artid, creation_script, objid, convert(int, schema_option), type, name, pre_creation_cmd    
          from sysextendedarticlesview where pubid = @pubid   
  
    open hCarticles  
     
    fetch hCarticles into @artid, @creation_script, @objid, @schema_option, @type, @name, @pre_creation_command  
    while (@@fetch_status <> -1)  
    begin  
        if @type = 0x40 --  Schema view articles, may require index scripting  
        begin  
            select @taskload = 0  
            --  See if a custom creation script will be used   
            if @schema_option = 0 and @creation_script is not null and  
               @creation_script <> N''  
            begin  
                --  Script generation  
                select @taskload = @copyfilecost + @scriptproccost  
                insert #workload_breakdown values (@name, @artid, 12, @taskload)  
                --  Cost of adding commands for the custom script  
                select @addcommandstotal = @addcommandstotal + @addcommandcost  
                --  Increment scripts counter  
                select @numscripts = @numscripts + 1  
                --  No need to worry about index script  
            end  
            else  
            begin  
                --  Script generation   
                select @taskload = @dmoscriptcost + @scriptproccost  
                --  Must script out schema script  
                insert #workload_breakdown values (@name, @artid, 1, @taskload)  
                --  Cost of adding the command  
                select @addcommandstotal = @addcommandstotal + @addcommandcost  
                --  Increment scripts counter  
                select @numscripts = @numscripts + 1  
  
                --  Index script?  
                if (@schema_option & 0x50) <> 0x0  
                begin  
                    --  Cost of script generation  
                    select @taskload = @dmoscriptcost + @scriptproccost  
                    insert #workload_breakdown values (@name, @artid, 13, @taskload)  
                    --  Cost of adding command for the script  
                    select @addcommandstotal = @addcommandstotal + @addcommandcost  
                    --  Increment scripts counter  
                    select @numscripts = @numscripts + 1  
                end  
  
                --  Trigger script?  
                if (@schema_option & 0x100) <> 0x0  
                begin  
                    --  Cost of script generation  
                    select @taskload = @dmoscriptcost + @scriptproccost  
                    insert #workload_breakdown values (@name, @artid, 2, @taskload)  
                    --  Cost of adding command for the script  
                    select @addcommandstotal = @addcommandstotal + @addcommandcost  
                    --  Increment scripts counter  
                    select @numscripts = @numscripts + 1  
                end   
  
                --  XProp script?  
                if (@schema_option & 0x2000) <> 0x0  
                begin  
                    --  Cost of script generation   
                    select @taskload = @dmoscriptcost + @scriptproccost  
                    insert #workload_breakdown values (@name, @artid, 3, @taskload)  
                    --  Cost of adding command  
                    select @addcommandstotal = @addcommandstotal + @addcommandcost  
                    --  Increment scripts counter  
                    select @numscripts = @numscripts + 1  
                end  
            end          
        end  
        else if @type in (0x08, 0x18, 0x20, 0x80) --  Regular schema or proc exec articles   
        begin  
            select @taskload = 0  
            --  See if a custom creation script will be used   
            if @schema_option = 0 and @creation_script is not null and  
               @creation_script <> N''  
            begin  
                --  Script generation  
                select @taskload = @copyfilecost + @scriptproccost  
                insert #workload_breakdown values (@name, @artid, 12, @taskload)  
                --  Cost of adding commands for the custom script  
                select @addcommandstotal = @addcommandstotal + @addcommandcost  
                --  Increment scripts counter  
                select @numscripts = @numscripts + 1  
            end  
            else   
            begin  
                --  Script generation   
                select @taskload = @dmoscriptcost + @scriptproccost  
                --  Must script out schema script  
                insert #workload_breakdown values (@name, @artid, 1, @taskload)  
                --  Cost of adding the command  
                select @addcommandstotal = @addcommandstotal + @addcommandcost  
                --  Increment scripts counter  
                select @numscripts = @numscripts + 1  
  
                --  XProp script?  
                if (@schema_option & 0x2000) <> 0x0  
                begin  
                    --  Cost of script generation   
                    select @taskload = @dmoscriptcost + @scriptproccost  
                    insert #workload_breakdown values (@name, @artid, 3, @taskload)  
                    --  Cost of adding command  
                    select @addcommandstotal = @addcommandstotal + @addcommandcost  
                --  Increment scripts counter  
                select @numscripts = @numscripts + 1  
                end  
            end  
        end  
        else --  Log based articles, requires bcp  
        begin  
           select @taskload = 0  
           --  See if a custom creation script will be used   
            if @schema_option = 0 and @creation_script is not null and  
               @creation_script <> N''  
            begin  
                --  Script generation  
                select @taskload = @copyfilecost + @scriptproccost  
                insert #workload_breakdown values (@name, @artid, 12, @taskload)  
                --  Cost of adding commands for the custom script  
                select @addcommandstotal = @addcommandstotal + @addcommandcost  
                --  Increment scripts counter  
                select @numscripts = @numscripts + 1  
                --  No need to worry about index script  
            end  
            else  
            begin  
                --  Script generation   
                select @taskload = @dmoscriptcost + @scriptproccost  
                --  Must script out schema script  
                insert #workload_breakdown values (@name, @artid, 1, @taskload)  
                --  Cost of adding the command  
                select @addcommandstotal = @addcommandstotal + @addcommandcost  
                --  Increment scripts counter  
                select @numscripts = @numscripts + 1  
  
                --  Index script is not optional for logbased articles  
                --  Cost of script generation  
                select @taskload = @dmoscriptcost + @scriptproccost  
                insert #workload_breakdown values (@name, @artid, 13, @taskload)  
                --  Cost of adding command for the script  
                select @addcommandstotal = @addcommandstotal + @addcommandcost  
                --  Increment scripts counter  
                select @numscripts = @numscripts + 1  
  
                --  Constraint script?  
                if (@schema_option & 0xf00) <> 0x0  
                begin  
                    --  Cost of script generation  
                    select @taskload = @dmoscriptcost + @scriptproccost  
                    insert #workload_breakdown values (@name, @artid, 9, @taskload)  
                    --  Cost of adding command for the script  
                    select @addcommandstotal = @addcommandstotal + @addcommandcost  
                    --  Increment scripts counter  
                    select @numscripts = @numscripts + 1  
                end  
      
                --  Trigger script?  
                if (@schema_option & 0x100) <> 0x0  
                begin  
                    --  Cost of script generation  
                    select @taskload = @dmoscriptcost + @scriptproccost  
                    insert #workload_breakdown values (@name, @artid, 2, @taskload)  
                    --  Cost of adding command for the script  
                    select @addcommandstotal = @addcommandstotal + @addcommandcost  
                    --  Increment scripts counter  
                    select @numscripts = @numscripts + 1  
                end   
          
                --  XProp script?  
                if (@schema_option & 0x2000) <> 0x0  
                begin  
                    --  Cost of script generation   
                    select @taskload = @dmoscriptcost + @scriptproccost  
                    insert #workload_breakdown values (@name, @artid, 3, @taskload)  
                    --  Cost of adding command  
                    select @addcommandstotal = @addcommandstotal + @addcommandcost  
                    --  Increment scripts counter  
                    select @numscripts = @numscripts + 1  
                end  
            end          
          
        if @objid is not null  
        begin  
                --  Get fast row count from sysindexes for bcp op estimation  
                select @rowcount =  
                        case when isnull(rowcnt,0) > @maxint then @maxint / 10000  
                             when isnull(rowcnt,0) > @maxint / 10000 then rowcnt / 10000  
                             else isnull(rowcnt,0)  
                        end       
                  from sysindexes   
                 where id = @objid   
                   and indid in (0,1)  
        end  
        else  
        begin  
                --  TODO: HREPL --  pick arbitrary value of 100 to represent 1,000,000 rows  
                select @rowcount = 100  
        end  
  
            --  Increment the total row count  
            select @totalrowcount = @totalrowcount + @rowcount          
  
            --  BCP computation              
            --  Bcp file generation  
            select @taskload = @bcpoverhead + (@rowcount * @totalbcpcostper100) / 100   
            insert #workload_breakdown values (@name, @artid, 4, @taskload)  
        end      
  
        --  System pre-snapshot script required?  
        if @type in (0x40, 0x80) or ((@type & 0x1)<>0 and @pre_creation_command =  3)  
        begin  
            select @needsysprescript = 1  
            --  Incrememt the number of pre-script command counter  
            select @numprescriptcommands = @numprescriptcommands + 1  
        end  
  
        --  Increment the article count  
        select @numarticles = @numarticles + 1  
  
        fetch hCarticles into @artid, @creation_script, @objid, @schema_option, @type, @name, @pre_creation_command  
    end  
      
    --  System pre-snapshot script processing cost   
    if @needsysprescript = 1  
    begin  
        select @taskload = @syspreoverhead +   
                (@numprescriptcommands * @syspreper2commands) / 2 +  
               @scriptproccost  
        insert #workload_breakdown values (N'', -1, 7, @taskload)           
        select @numscripts = @numscripts + 1  
    end  
  
    --  Cost of flushing folder for the scripts   
    if @compress_snapshot = 1  
    begin  
        select @taskload = (@numscripts * @flushper2scripts) / 2  
        insert #workload_breakdown values (N'', -1, 8, @taskload)   
    end  
  
    --  Synctran commands cost?  
          
  
    --  Subscription activation cost?  
  
  
    --  Cost for flushing the cabinet  
    if @compress_snapshot = 1  
    begin  
        select @taskload = (@numscripts * @flushcabper5scripts / 5) +  
                           (@totalrowcount * @flushcabper500bcprows / 500)  
        insert #workload_breakdown values (N'', -1, 14, @taskload)  
    end  
  
    --  Add the total add commands cost  
    insert #workload_breakdown values (N'', -1, 6, @addcommandstotal)  
  
    --  Compute the total workload and put that in the   
    --  workload break down  
    select @bigtaskload = sum(convert(bigint,taskload)) from #workload_breakdown  
    select @taskload =  
            case when @bigtaskload > @maxint then @maxint  
                 else @bigtaskload  
            end  
    insert #workload_breakdown values (N'', -1, 0, @taskload)   
  
    select name, taskid, taskload from #workload_breakdown order by artid, taskid asc  
    drop table #workload_breakdown  
  
    return 0  
  
Failure:  
    if @table_created = 1  
       drop table #workload_breakdown  
    return 1  
  
end