Example #1
0
        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);
        }
Example #2
0
        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);
                }
            }
        }
Example #3
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);
                }
            }
        }