# convert_sp_plan_dbccdb.awk
# transform sp_plan_dbccdb to sql commands
# FA, 2007

# Functions #######################################################

# convert2megs : 
function convert2megs (size) {
        sub("M","",size)
        k=sub("K","",size)
        if ( k > 0 ) size=int(size/1024)+1
        return size
}
 
function max (x,y) {
        if ( int(x) > int(y) ) { return x } else { return y }
}
 
# BEGIN ###########################################################

BEGIN {
        max_scan=10
        max_text=1
        max_cache=10
        max_proc=1
}


# Filters #########################################################

# filter system databases apart from master
/tempdb|model|sybsystem|dbccdb|sybsecurity/     { next }

#Filter empty lines
/^ *$/                                          { next }


# For each line in scope ##########################################

/^dbname/,/EOF/ {
        if ( $1 == "dbname" )   { next }
        if ( $1 == "(return" )  { next }

        db[$1]++
        proc[$1]=$NF 
        cache[$1]=convert2megs($4)

        max_scan=max(max_scan,convert2megs($2))
        max_text=max(max_text,convert2megs($3))
}


# END #############################################################

END {
        print "use dbccdb"
        print "go"
        print ""
        printf "exec sp_dbcc_createws dbccdb, 'default', scan_ws, scan, '%sM'\n", max_scan
        printf "exec sp_dbcc_createws dbccdb, 'default', text_ws, text, '%sM'\n", max_text
        print "go"
        print ""
        for ( i in db ) {
                printf "exec sp_dbcc_updateconfig %s, 'max worker processes', '%d'\n" , i, proc[i]
                printf "exec sp_dbcc_updateconfig %s, 'dbcc named cache', 'default data cache', '%sM'\n", i, cache[i]
                printf "exec sp_dbcc_updateconfig %s, 'scan workspace', scan_ws\n" , i
                printf "exec sp_dbcc_updateconfig %s, 'text workspace', text_ws\n" , i
                print "go"
                print ""
        }
}