private void FixIndexes(object sender, DoWorkEventArgs e)
{
List <Index> indexes = (List <Index>)e.Argument;
using (var connectionList = new ConnectionList(Settings.ActiveHost)) {
for (int i = 0; i < indexes.Count; i++)
{
Index item = indexes[i];
if (_workerFix.CancellationPending)
{
Output.Current.Add("Canceled");
e.Cancel = true;
return;
}
item.Progress = imageCollection.Images[1];
_ps.Indexes++;
_ps.IndexesSize += item.PagesCount;
_workerFix.ReportProgress(i + 1);
Output.Current.AddCaption(item.ToString());
Stopwatch watch = Stopwatch.StartNew();
string sql = string.Empty;
SqlConnection connection = connectionList.Get(item.DatabaseName);
if (connection != null)
{
sql = QueryEngine.FixIndex(connection, item);
}
item.Progress = imageCollection.Images[2];
watch.Stop();
item.Duration = (new DateTime(0)).AddMilliseconds(watch.ElapsedMilliseconds);
Output.Current.Add(item.ToString(), sql, watch.ElapsedMilliseconds);
_workerFix.ReportProgress(i + 1);
if (!string.IsNullOrEmpty(item.Error))
{
Output.Current.Add(item.ToString(), item.Error);
item.Progress = imageCollection.Images[3];
_ps.Errors++;
}
else
{
if (Settings.Options.DelayAfterFix > 0 && i < indexes.Count - 1)
{
item.Progress = imageCollection.Images[6];
Thread.Sleep(Settings.Options.DelayAfterFix);
item.Progress = imageCollection.Images[2];
}
}
_ps.SavedSpace += (item.PagesCountBefore ?? 0);
_workerFix.ReportProgress(i + 2);
}
}
}
private void FixIndexes(object sender, DoWorkEventArgs e)
{
List <Index> indexes = (List <Index>)e.Argument;
using (var connectionList = new ConnectionList(Settings.ActiveHost)) {
for (int i = 0; i < indexes.Count; i++)
{
Index index = indexes[i];
if (_workerFix.CancellationPending)
{
Output.Current.Add("Canceled");
e.Cancel = true;
return;
}
index.Progress = Resources.IconRun;
_ps.Indexes++;
_ps.IndexesSize += index.PagesCount;
_workerFix.ReportProgress(i, index);
Output.Current.AddCaption(index.ToString());
Stopwatch watch = Stopwatch.StartNew();
string sql = string.Empty;
SqlConnection connection = connectionList.Get(index.DatabaseName);
if (connection != null)
{
sql = QueryEngine.FixIndex(connection, index);
}
watch.Stop();
Output.Current.Add(index.ToString(), sql, index.Duration);
index.Progress = Resources.IconOk;
index.Duration = watch.ElapsedMilliseconds;
_ps.SavedSpace += (index.PagesCountBefore ?? 0);
if (!string.IsNullOrEmpty(index.Error))
{
Output.Current.Add(index.ToString(), index.Error);
index.Progress = Resources.IconError;
_ps.Errors++;
}
else
{
if (Settings.Options.DelayAfterFix > 0 && i < indexes.Count - 1)
{
_workerFix.ReportProgress(i, index);
index.Progress = Resources.IconDelay;
Thread.Sleep(Settings.Options.DelayAfterFix);
index.Progress = Resources.IconOk;
}
}
_workerFix.ReportProgress(i, index);
}
}
}
private void FixIndexes(object sender, DoWorkEventArgs e)
{
List <Index> indexes = (List <Index>)e.Argument;
using (var connectionList = new ConnectionList(Settings.ActiveHost)) {
int count = 1;
foreach (Index item in indexes)
{
if (_workerFix.CancellationPending)
{
Output.Current.Add("Canceled");
e.Cancel = true;
return;
}
string sql = item.GetQuery();
Output.Current.AddCaption(item.ToString());
Stopwatch watch = Stopwatch.StartNew();
item.Progress = imageCollection.Images[1];
SqlConnection connection = connectionList.Get(item.DatabaseName);
if (connection != null)
{
QueryEngine.FixIndex(connection, item);
}
item.Progress = imageCollection.Images[2];
watch.Stop();
_totalDuration += watch.ElapsedMilliseconds;
item.Duration = (new DateTime(0)).AddMilliseconds(watch.ElapsedMilliseconds);
if (string.IsNullOrEmpty(item.Error))
{
Output.Current.Add(item.ToString(), sql, watch.ElapsedMilliseconds);
}
else
{
Output.Current.Add(item.ToString(), item.Error);
item.Progress = imageCollection.Images[3];
}
_workerFix.ReportProgress(count++);
}
}
}
private void ScanIndexStart(object sender, DoWorkEventArgs e)
{
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) {
if (ex.Message.Contains("Incorrect syntax") || ex.Message.Contains("Invalid"))
{
Output.Current.Add($"Syntax 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 || !ex.Message.Contains("timeout"))
{
break;
}
}
}
opw.Stop();
Output.Current.Add($"Pre-descibe: {(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.ColumnstoreClustered || index.IndexType == IndexType.ColumnstoreNonClustered)
{
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) {
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("timeout"))
{
throw new ArgumentException($"Error: {ex.Source} {ex.Message}");
}
retries++;
if (retries > 2)
{
break;
}
}
}
watch.Stop();
Output.Current.Add($"Pre-describe: {(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);
}
private void ScanIndexes(object sender, DoWorkEventArgs e)
{
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) {
_ps.Errors++;
if (ex.Message.Contains("kill"))
{
Output.Current.Add($"Cancel: {ex.Source}", ex.Message);
return;
}
if (!ex.Message.Contains("timeout"))
{
Utils.ShowErrorFrom(ex);
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>();
_ps.IndexesTotal = idx.Count;
_ps.Indexes = idx.Count(_ => _.Fragmentation != null);
_ps.IndexesSize = idx.Where(_ => _.Fragmentation != null).Sum(_ => _.PagesCount);
_ps.SavedSpace = idx.Where(_ => _.Fragmentation != null).Sum(_ => _.UnusedPagesCount);
foreach (Index index in idx.Where(_ => _.Fragmentation == null)
.OrderBy(_ => _.ObjectId)
.ThenBy(_ => _.IndexId)
.ThenBy(_ => _.PartitionNumber))
{
try {
if (_workerScan.CancellationPending)
{
_indexes.AddRange(idx);
e.Cancel = true;
return;
}
connection = connectionList.Get(database);
if (connection == null)
{
break;
}
_ps.Indexes++;
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) {
_ps.Errors++;
Output.Current.Add($"Failed: {index}", ex.Message);
}
_ps.Indexes = idx.Count(_ => _.Fragmentation != null);
_ps.IndexesSize = idx.Where(_ => _.Fragmentation != null).Sum(_ => _.PagesCount);
_ps.SavedSpace = idx.Where(_ => _.Fragmentation != null).Sum(_ => _.UnusedPagesCount);
_workerScan.ReportProgress(0);
}
_indexes.AddRange(idx);
}
}
}
