Пример #1
0
        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);
                }
            }
        }
Пример #3
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("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);
        }