public static void FixIndex(SqlConnection connection, Index index)
{
string sqlInfo = string.Format(index.IsColumnstore ? Query.AfterFixColumnstoreIndex : Query.AfterFixIndex,
index.ObjectId, index.IndexId, index.PartitionNumber);
bool isDeadIndex = (index.FixType == IndexOp.Disable || index.FixType == IndexOp.Drop);
string sql = isDeadIndex || index.FixType == IndexOp.CreateIndex
? index.GetQuery()
: $"{index.GetQuery()} \n {sqlInfo}";
SqlCommand cmd = new SqlCommand(sql, connection)
{
CommandTimeout = Settings.Options.CommandTimeout
};
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet data = new DataSet();
try {
adapter.Fill(data);
}
catch (Exception ex) {
index.Error = ex.Message;
}
if (index.FixType == IndexOp.CreateIndex)
{
index.Fragmentation = 0;
}
if (isDeadIndex)
{
index.PagesCountBefore = index.PagesCount;
index.Fragmentation = 0;
index.PagesCount = 0;
index.UnusedPagesCount = 0;
index.RowsCount = 0;
}
else if (data.Tables.Count == 1 && data.Tables[0].Rows.Count == 1)
{
DataRow row = data.Tables[0].Rows[0];
index.PagesCountBefore = index.PagesCount - row.Field <long>(Resources.PagesCount);
index.Fragmentation = row.Field <double>(Resources.Fragmentation);
index.PagesCount = row.Field <long>(Resources.PagesCount);
index.UnusedPagesCount = row.Field <long>(Resources.UnusedPagesCount);
index.RowsCount = row.Field <long>(Resources.RowsCount);
index.DataCompression = ((DataCompression)row.Field <byte>(Resources.DataCompression));
index.IndexStats = row.Field <DateTime?>(Resources.IndexStats);
}
}
private void GetActiveObjectInfo(object sender, ToolTipControllerGetActiveObjectInfoEventArgs e) {
if (e.Info == null || e.SelectedControl == gridControl1) {
GridHitInfo info = gridView1.CalcHitInfo(e.ControlMousePosition);
if (info.InRowCell && info.RowHandle != -1 && info.Column != null && info.Column.FieldName == "Progress") {
Index index = (Index)gridView1.GetRow(info.RowHandle);
e.Info = new ToolTipControlInfo($"{info.RowHandle} - {info.Column}", $"{index.GetQuery()}\n{index.Error}");
}
}
}
private void CopyFixScript(object sender, EventArgs e)
{
Index row = (Index)view.GetFocusedRow();
if (row == null)
{
return;
}
string query = $"USE {row.DatabaseName.ToQuota()}\nGO\n{row.GetQuery()}\nGO\n";
Clipboard.SetText(query);
}
public static string FixIndex(SqlConnection connection, Index ix)
{
int indexId = ix.FixType == IndexOp.CREATE_COLUMNSTORE_INDEX && ix.IndexType == IndexType.HEAP ? 1 : ix.IndexId;
string sqlInfo = string.Format(ix.IsColumnstore ? Query.AfterFixColumnstoreIndex : Query.AfterFixIndex,
ix.ObjectId, indexId, ix.PartitionNumber, Settings.Options.ScanMode);
string query = ix.GetQuery();
string sql = ix.FixType == IndexOp.DISABLE_INDEX ||
ix.FixType == IndexOp.DROP_INDEX ||
ix.FixType == IndexOp.DROP_TABLE ||
ix.FixType == IndexOp.CREATE_INDEX ||
ix.FixType == IndexOp.UPDATE_STATISTICS_FULL ||
ix.FixType == IndexOp.UPDATE_STATISTICS_RESAMPLE ||
ix.FixType == IndexOp.UPDATE_STATISTICS_SAMPLE
? query
: $"{query} \n {sqlInfo}";
SqlCommand cmd = new SqlCommand(sql, connection)
{
CommandTimeout = Settings.Options.CommandTimeout
};
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet data = new DataSet();
try {
adapter.Fill(data);
}
catch (Exception ex) {
ix.Error = ex.Message;
}
if (string.IsNullOrEmpty(ix.Error))
{
try {
if (ix.FixType == IndexOp.UPDATE_STATISTICS_FULL || ix.FixType == IndexOp.UPDATE_STATISTICS_RESAMPLE || ix.FixType == IndexOp.UPDATE_STATISTICS_SAMPLE)
{
ix.IndexStats = DateTime.Now;
}
else if (ix.FixType == IndexOp.CREATE_INDEX)
{
ix.IndexStats = DateTime.Now;
ix.Fragmentation = 0;
}
else if (ix.FixType == IndexOp.DISABLE_INDEX || ix.FixType == IndexOp.DROP_INDEX || ix.FixType == IndexOp.DROP_TABLE)
{
ix.PagesCountBefore = ix.PagesCount;
ix.Fragmentation = 0;
ix.PagesCount = 0;
ix.UnusedPagesCount = 0;
ix.RowsCount = 0;
}
else if (data.Tables.Count == 1 && data.Tables[0].Rows.Count == 1)
{
DataRow row = data.Tables[0].Rows[0];
ix.PagesCountBefore = ix.PagesCount - row.Field <long>(Resources.PagesCount);
ix.Fragmentation = row.Field <double>(Resources.Fragmentation);
ix.PageSpaceUsed = row.Field <double?>(Resources.PageSpaceUsed);
ix.PagesCount = row.Field <long>(Resources.PagesCount);
ix.UnusedPagesCount = row.Field <long>(Resources.UnusedPagesCount);
ix.RowsCount = row.Field <long>(Resources.RowsCount);
ix.DataCompression = ((DataCompression)row.Field <byte>(Resources.DataCompression));
ix.IndexStats = row.Field <DateTime?>(Resources.IndexStats);
if (ix.FixType == IndexOp.CREATE_COLUMNSTORE_INDEX)
{
ix.IndexName = "CCL";
ix.IndexType = IndexType.CLUSTERED_COLUMNSTORE;
}
}
}
catch (Exception ex) {
ix.Error = ex.Message;
}
}
return(query);
}
public static string FixIndex(SqlConnection connection, Index ix)
{
int indexId = ix.FixType == IndexOp.CREATE_COLUMNSTORE_INDEX && ix.IndexType == IndexType.HEAP ? 1 : ix.IndexId;
string sql;
string query = ix.GetQuery();
if (ix.FixType == IndexOp.DISABLE_INDEX || ix.FixType == IndexOp.DROP_INDEX || ix.FixType == IndexOp.DROP_TABLE || ix.FixType == IndexOp.CREATE_INDEX)
{
sql = query;
}
else
{
string sqlInfo;
if (ix.IsColumnstore)
{
sqlInfo = Query.AfterFixColumnstoreIndex;
}
else if (Settings.ServerInfo.IsFullStats && (ix.IndexType == IndexType.CLUSTERED || ix.IndexType == IndexType.NONCLUSTERED) && !ix.IsPartitioned)
{
sqlInfo = Query.AfterFixIndexWithStats;
}
else
{
sqlInfo = Query.AfterFixIndex;
}
sql = $"{query} \n {string.Format(sqlInfo, ix.ObjectId, indexId, ix.PartitionNumber, Settings.Options.ScanMode)}";
}
SqlCommand cmd = new SqlCommand(sql, connection)
{
CommandTimeout = Settings.Options.CommandTimeout
};
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet data = new DataSet();
try {
adapter.Fill(data);
}
catch (Exception ex) {
ix.Error = ex.Message;
}
if (string.IsNullOrEmpty(ix.Error))
{
try {
if (ix.FixType == IndexOp.CREATE_INDEX)
{
ix.IndexStats = DateTime.UtcNow;
ix.Fragmentation = 0;
}
else if (ix.FixType == IndexOp.DISABLE_INDEX || ix.FixType == IndexOp.DROP_INDEX || ix.FixType == IndexOp.DROP_TABLE || ix.FixType == IndexOp.TRUNCATE_TABLE)
{
ix.PagesCountBefore = ix.PagesCount;
ix.Fragmentation = 0;
ix.PagesCount = 0;
ix.UnusedPagesCount = 0;
ix.RowsCount = 0;
}
else if (data.Tables.Count == 1 && data.Tables[0].Rows.Count == 1)
{
DataRow row = data.Tables[0].Rows[0];
ix.PagesCountBefore = ix.PagesCount - row.Field <long>(Resources.PagesCount);
ix.Fragmentation = row.Field <double>(Resources.Fragmentation);
ix.PageSpaceUsed = row.Field <double?>(Resources.PageSpaceUsed);
ix.PagesCount = row.Field <long>(Resources.PagesCount);
ix.UnusedPagesCount = row.Field <long>(Resources.UnusedPagesCount);
ix.RowsCount = row.Field <long>(Resources.RowsCount);
ix.DataCompression = ((DataCompression)row.Field <byte>(Resources.DataCompression));
ix.IndexStats = row.Field <DateTime?>(Resources.IndexStats);
ix.StatsSampled = row.Field <double?>(Resources.StatsSampled) ?? ix.StatsSampled;
ix.RowsSampled = row.Field <long?>(Resources.RowsSampled) ?? ix.RowsSampled;
if (ix.FixType == IndexOp.CREATE_COLUMNSTORE_INDEX)
{
ix.IndexName = "CCL";
ix.IndexType = IndexType.CLUSTERED_COLUMNSTORE;
}
}
}
catch (Exception ex) {
ix.Error = ex.Message;
}
}
return(query);
}
