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")) {
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.CLUSTERED_COLUMNSTORE || index.IndexType == IndexType.NONCLUSTERED_COLUMNSTORE)
{
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.FirstThreshold &&
_.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.IndexType == IndexType.MISSING_INDEX)
{
ix.FixType = IndexOp.CREATE_INDEX;
}
else if (ix.Fragmentation < Settings.Options.SecondThreshold && ix.IsAllowReorganize)
{
ix.FixType = IndexOp.REORGANIZE;
}
else if (Settings.Options.Online && ix.IsAllowOnlineRebuild)
{
ix.FixType = IndexOp.REBUILD_ONLINE;
}
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);
}
