public CmdWorker(List <CmdArgument> args)
{
Host host = new Host();
Settings.Options.IgnorePermissions = false;
Settings.Options.IgnoreReadOnlyFL = false;
foreach (CmdArgument cmd in args)
{
switch (cmd.Argument)
{
case "connection":
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(cmd.Params[0]);
host.Server = sb.DataSource;
host.AuthType = sb.IntegratedSecurity ? AuthTypes.Windows : AuthTypes.SqlServer;
host.User = sb.UserID;
if (!string.IsNullOrEmpty(sb.InitialCatalog))
{
host.Databases.Add(sb.InitialCatalog);
}
if (!sb.IntegratedSecurity)
{
host.Password = sb.Password;
}
Settings.Options.ConnectionTimeout = sb.ConnectTimeout;
break;
case "databases":
host.Databases.Clear();
List <string> dbs = new List <string> (cmd.Params[0].Split(';'));
host.Databases.AddRange(dbs.Where(_ => !string.IsNullOrEmpty(_)).Distinct());
break;
case "connectiontimeout":
Settings.Options.ConnectionTimeout = Convert.ToInt32(cmd.Params[0]);
break;
case "commandtimeout":
Settings.Options.CommandTimeout = Convert.ToInt32(cmd.Params[0]);
break;
case "online":
Settings.Options.Online = true;
break;
case "ignoreheap":
Settings.Options.ScanHeap = false;
break;
case "ignorecolumnstore":
Settings.Options.ScanClusteredColumnstore = false;
Settings.Options.ScanNonClusteredColumnstore = false;
break;
case "ignorebtree":
Settings.Options.ScanClusteredIndex = false;
Settings.Options.ScanNonClusteredIndex = false;
break;
case "maxdop":
Settings.Options.MaxDop = Convert.ToInt32(cmd.Params[0]);
break;
case "rebuildthreshold":
Settings.Options.RebuildThreshold = Convert.ToInt32(cmd.Params[0]);
break;
case "reorganizethreshold":
Settings.Options.ReorganizeThreshold = Convert.ToInt32(cmd.Params[0]);
break;
case "minindexsize":
Settings.Options.MinIndexSize = Convert.ToInt32(cmd.Params[0]);
break;
case "predescribesize":
Settings.Options.PreDescribeSize = Convert.ToInt32(cmd.Params[0]);
break;
case "maxindexsize":
Settings.Options.MaxIndexSize = Convert.ToInt32(cmd.Params[0]);
break;
default:
throw new ArgumentException($"Unknown argument \"{cmd.Argument}\"");
}
}
if (host.Databases.Count == 0)
{
throw new ArgumentException("No database selected");
}
using (SqlConnection connection = Connection.Create(host)) {
try {
host.ServerInfo = QueryEngine.GetServerInfo(connection);
}
catch (Exception ex) {
throw new ArgumentException(ex.Message.Replace(". ", "." + Environment.NewLine));
}
finally {
connection.Close();
}
}
if (host.ServerInfo.MajorVersion < 10)
{
throw new ArgumentException(Resources.MinVersionMessage);
}
else
{
Settings.ActiveHost = host;
}
}
private void ScanIndexStart(object sender, DoWorkEventArgs e)
{
_errors = 0;
using (ConnectionList connectionList = new ConnectionList(Settings.ActiveHost)) {
foreach (var database in Settings.ActiveHost.Databases)
{
List <Index> idx = new List <Index>();
if (_workerScan.CancellationPending)
{
e.Cancel = true;
return;
}
SqlConnection connection = connectionList.Get(database);
if (connection == null)
{
continue;
}
Output.Current.Add($"Describe: {database}");
Stopwatch opw = Stopwatch.StartNew();
short retries = 0;
while (true)
{
try {
idx = QueryEngine.GetIndexes(connection);
break;
}
catch (SqlException ex) {
_errors++;
if (!ex.Message.Contains("timeout"))
{
Output.Current.Add($"Error: {ex.Source}", ex.Message);
XtraMessageBox.Show(ex.Message.Replace(". ", "." + Environment.NewLine), ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Output.Current.Add($"Pre-describe #{retries} failed: {database}. Rescan...", ex.Message);
retries++;
if (retries > 2)
{
break;
}
}
}
opw.Stop();
Output.Current.Add($"Pre-describe: {(idx.Count(_ => _.Fragmentation != null))}. " +
$"Post-describe: {(idx.Count(_ => _.Fragmentation == null))}", null, opw.ElapsedMilliseconds);
List <int> clid = new List <int>();
foreach (Index index in idx.Where(_ => _.Fragmentation == null)
.OrderBy(_ => _.ObjectId)
.ThenBy(_ => _.IndexId)
.ThenBy(_ => _.PartitionNumber))
{
try {
if (_workerScan.CancellationPending)
{
_scanIndexes.AddRange(idx);
e.Cancel = true;
return;
}
connection = connectionList.Get(database);
if (connection == null)
{
break;
}
if (index.IndexType == IndexType.CLUSTERED_COLUMNSTORE || index.IndexType == IndexType.NONCLUSTERED_COLUMNSTORE)
{
if (!clid.Exists(_ => _ == index.ObjectId))
{
Output.Current.AddCaption(index.ToString());
opw = Stopwatch.StartNew();
QueryEngine.GetColumnstoreFragmentation(connection, index, idx);
clid.Add(index.ObjectId);
opw.Stop();
Output.Current.Add(index.ToString(), null, opw.ElapsedMilliseconds);
}
}
else
{
Output.Current.AddCaption(index.ToString());
opw = Stopwatch.StartNew();
QueryEngine.GetIndexFragmentation(connection, index);
opw.Stop();
Output.Current.Add(index.ToString(), null, opw.ElapsedMilliseconds);
}
}
catch (Exception ex) {
_errors++;
Output.Current.Add($"Failed: {index}", ex.Message);
}
}
_scanIndexes.AddRange(idx);
}
}
}
public int FixIndexes()
{
Output.Current.Add($"Host: {Settings.ActiveHost}");
using (ConnectionList connectionList = new ConnectionList(Settings.ActiveHost)) {
List <Index> scanIndex = new List <Index>();
Stopwatch watch;
Stopwatch totalWatch = Stopwatch.StartNew();
foreach (var database in Settings.ActiveHost.Databases)
{
List <Index> idx = new List <Index>();
watch = Stopwatch.StartNew();
SqlConnection connection = connectionList.Get(database);
if (connection == null)
{
continue;
}
Output.Current.Add(new string('-', 150));
Output.Current.Add($"Describe: {database}");
short retries = 0;
while (true)
{
try {
idx = QueryEngine.GetIndexes(connection);
break;
}
catch (SqlException ex) {
if (ex.Message.Contains("Incorrect syntax"))
{
throw new ArgumentException($"Syntax error: {ex.Source} {ex.Message}");
}
retries++;
if (retries > 2 || !ex.Message.Contains("timeout"))
{
break;
}
}
}
watch.Stop();
Output.Current.Add($"Pre-descibe: {(idx.Count(_ => _.Fragmentation != null))}. " +
$"Post-describe: {(idx.Count(_ => _.Fragmentation == null))}", null, watch.ElapsedMilliseconds);
List <int> clid = new List <int>();
foreach (Index index in idx.Where(_ => _.Fragmentation == null)
.OrderBy(_ => _.ObjectId)
.ThenBy(_ => _.IndexId)
.ThenBy(_ => _.PartitionNumber))
{
try {
connection = connectionList.Get(database);
if (connection == null)
{
break;
}
if (index.IndexType == IndexType.ColumnstoreClustered || index.IndexType == IndexType.ColumnstoreNonClustered)
{
if (!clid.Exists(_ => _ == index.ObjectId))
{
watch = Stopwatch.StartNew();
QueryEngine.GetColumnstoreFragmentation(connection, index, idx);
clid.Add(index.ObjectId);
watch.Stop();
Output.Current.Add(index.ToString(), null, watch.ElapsedMilliseconds);
}
}
else
{
watch = Stopwatch.StartNew();
QueryEngine.GetIndexFragmentation(connection, index);
watch.Stop();
Output.Current.Add(index.ToString(), null, watch.ElapsedMilliseconds);
}
}
catch (Exception ex) {
Output.Current.Add($"Failed: {index}", ex.Message);
}
}
scanIndex.AddRange(idx);
}
var fixIndex = scanIndex.Where(_ => _.Fragmentation >= Settings.Options.ReorganizeThreshold &&
_.PagesCount >= Settings.Options.MinIndexSize.PageSize() &&
_.PagesCount <= Settings.Options.MaxIndexSize.PageSize()).ToList();
Output.Current.Add(new string('-', 150));
Output.Current.Add($"Processed: {scanIndex.Count}. Fragmented: {fixIndex.Count}", null, totalWatch.ElapsedMilliseconds);
if (fixIndex.Count > 0)
{
totalWatch = Stopwatch.StartNew();
Output.Current.Add("Fix...");
foreach (Index ix in fixIndex)
{
if (ix.Fragmentation < Settings.Options.RebuildThreshold && ix.IsAllowReorganize)
{
ix.FixType = IndexOp.Reorganize;
}
else if (Settings.Options.Online && ix.IsAllowOnlineRebuild)
{
ix.FixType = IndexOp.RebuildOnline;
}
else
{
ix.FixType = IndexOp.Rebuild;
}
watch = Stopwatch.StartNew();
SqlConnection connection = connectionList.Get(ix.DatabaseName);
if (connection != null)
{
QueryEngine.FixIndex(connection, ix);
}
watch.Stop();
if (string.IsNullOrEmpty(ix.Error))
{
Output.Current.Add(ix.ToString(),
$"Fragmentation: {ix.Fragmentation:n2}%. " +
$"Size: {(Convert.ToDecimal(ix.PagesCountBefore) * 8).FormatSize()} -> {(Convert.ToDecimal(ix.PagesCount) * 8).FormatSize()}. " +
$"Saved: {(Convert.ToDecimal(ix.PagesCountBefore - ix.PagesCount) * 8).FormatSize()}", watch.ElapsedMilliseconds);
}
else
{
Output.Current.Add(ix.ToString(), ix.Error);
}
}
Output.Current.Add(new string('-', 150));
Output.Current.Add($"Processed: {fixIndex.Count(_ => string.IsNullOrEmpty(_.Error))}. " +
$"Errors: {fixIndex.Count(_ => !string.IsNullOrEmpty(_.Error))}", null, totalWatch.ElapsedMilliseconds);
}
}
return(0);
}