sergiisyrovatchenko / sqlindexmanager Goto Github PK

I have the Online=On flag active but when i run the index rebuild it used the Off option, and when i check the script it runs, the flag is off.

Firstly, this is a really great tool. Easy to use.
The app has worked successfully many times but now raises this error dialog:
"Could not find table or object ID 209435820
Check system catalog"
when either selects the database or the refresh button.

I suspect that this is likely an issue in the database.

DevExpress is causing issues when I try to compile.

My solution is to fork and convert to SyncFusion Community, will keep most of the features of the DevExpress controls and is Free for use.

Suggest converting the main branch to these controls.
https://www.syncfusion.com/products/communitylicense

As milestone 1 - port business logic to .Net Standard 2.0.

As milestone 2 - provide CLI tool that can work on latest LTS version .Net Core.
can be executed from local machine, or from VM based on cron.

As milestone 3 - port Windows desktop app to .Net Core 3.0 after it released.

Hello. Can you describe how the "missing index" feature works? How it finds candidates for index building?

I like what you are doing here. I am not sure how hard or worth it, but do you think it would be possible or worth porting this to Azure Data Studio as an extension? I think this would allow for more wide adoption and use. If a lot of the logic is in SQL and just using dotnet to make calls it may be worth investigating. If this would be a substantial rewrite or not what you are interested in then ignore this. I just wanted to bring this up since I think this type of functionality would be amazing as a plugin for Azure Data Studio. It would be cross platform, and already have a standard UI.

I had a filter on [Object] (say some tables), and want to get fresh data for them. If I hit the refresh button program start to refresh ALL objects in the database.

Please, implement refresh only on selected objects.

Thank you!

If there are some offline file groups, i got this

[ 12:29:23.655 ] Error: .Net SqlClient Data Provider
Filegroup FG2 is offline.
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.

and "no indexes found"

Great tool

Just as a DBA I want to keep the index settings already on the index when I perform maintenance. Like Data Compression, Fill Factor Pad Index etc. If you can add an option to use existing this would be a great tool.

No matter which database I connect to, local MSSQL database or Azure SQL database. I always get 'No indexes found'.

Thoughts?

Getting wrong results with filtering:

There are only 4 indexes in table Orders this happens to .6x and the latest .71 version

dbo.Orders.I_Orders_ID (2)
dbo.Orders.I_Orders_ModifiedDate_ModifiedTime (4)
dbo.Orders.PK_Orders (1)
dbo.Orders.I_Orders_SubaccID (3)

Запускаю приложение, выбираю БД, получаю ошибку:

Нажимаю Ctrl+C

`See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Threading.ThreadStateException: Current thread must be set to single thread apartment (STA) mode before OLE calls can be made. Ensure that your Main function has STAThreadAttribute marked on it.
at System.Windows.Forms.Clipboard.SetDataObject(Object data, Boolean copy, Int32 retryTimes, Int32 retryDelay)
at DevExpress.XtraEditors.XtraMessageBoxForm.CopyContent()
at DevExpress.XtraEditors.XtraBaseForm.ProcessCmdKey(Message& msg, Keys keyData)
at System.Windows.Forms.Control.ProcessCmdKey(Message& msg, Keys keyData)
at System.Windows.Forms.Control.PreProcessMessage(Message& msg)
at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg)
at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg)

в логе
15:40:05.780 - Server: SQL Server 2012 RTM (11.0.2100.60) Developer Edition (64-bit) 15:40:06.062 - Refresh databases... 15:40:06.405 - Elapsed time: 00:00:00:331. Found 5 databases 15:40:15.734 - Describe: tornado 15:40:15.796 - Error: .Net SqlClient Data Provider Недопустимое имя объекта "sys.dm_db_stats_properties". 15:40:17.499 - Processed: 0. Fragmented: 0. No indexes found. Try searching again or change settings...

Имеются несколько подключенных баз. Одна имеет ошибки. Я хочу оптимизировать другую(ие)
При подключении к такому серверу получаю ошибку и дальше работать не могу:

Hello. Didn't you think about to create nuget-package from this project? We want to use it to automate nightly defragmentation, and we want to call it from .net app. Yes, we can put the binaries on the server and call it by the command line. But it doesn't look good.

1. Error in log:

Pre-describe #0 failed: <>. Rescan...
Arithmetic overflow error for data type tinyint, value = 398.
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.

2. Noninformative message on error: "No indexes found".

UPD:
after small debug:

diff --git a/Server/Query.cs b/Server/Query.cs
index 232d5fa..8b6b47a 100644
--- a/Server/Query.cs
+++ b/Server/Query.cs
@@ -133,7 +133,7 @@ CREATE TABLE #IndexColumns (
       ObjectID      INT NOT NULL
     , IndexID       INT NOT NULL
     , IndexColumnID INT NOT NULL
-    , ColumnID      TINYINT NOT NULL
+    , ColumnID      INT NOT NULL
     , IsIncluded    BIT NOT NULL
     , PRIMARY KEY (ObjectID, IndexID, ColumnID)
 )

Show Elapsed time in Duration column, and/or progress as described
Progress Of Create Command

After starting the program, the login dialog is displayed. You can only see the name of the SQL server and the text of the "Save Password" checkbox. In the log directory you can only read that the layout of the grid has been restored.

The index screen is very useful .

Add an option to see all indexes with 0% fragmentation was great.

Thanks for your work.

Would it be possible te build an internal databases with an history of the fragmentation of each index?
that way you could easily see if an index gets a lot of rebuilds.

that would help a lot to determine if an index would be beter of with a lesser fillfactor.
or an index that never needs rebuilding could do with a higher fillfactor.

we have stored procedues in place that check each day if indexes need defragmentation/rebuild, and when an index is rebuild 25 days in a month, the fillfactor is decreased by 5% ( 20 days = 4%, 15 = 3%, 10 days is 2%) . when a index is not rebuild in 30 days, the fillfactor gets 1% higher.

this stabilizes in about a year, and gave us a reducement of IO load of about 15% total.

if you dont like the idea, just remove this request :-) its just a thought.

Can you add tooltips at the bottom to explain the icons?

Can this be extended to have something on the initial screen to permit the ExecutionTimeout to be larger than 120 seconds (which appears to be the default?

Error is below:

Application has encountered an unexpected error
Please send error detail to www.github.com/sergiisyrovatchenko/SQLIndexManager

SQL Server: SQL Server 2014 SP2 CU7 (12.0.5556.0) Web Edition (64-bit)
Build: 1.0.0.71

Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Warning: Null value is eliminated by an aggregate or other SET operation.
.Net SqlClient Data Provider
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at SQLIndexManager.QueryEngine.RefreshDatabaseSize(SqlConnection connection, List`1 dbs)
   at SQLIndexManager.DatabaseBox.ScanDatabases(Object sender, DoWorkEventArgs e)

Hi, can you add key/param in cmd for autocreate indexes?

Application has encountered an unexpected error
Please send error detail to www.github.com/sergiisyrovatchenko/SQLIndexManager

SQL Server: SQL Server 2012 RTM (11.0.2100.60) (64-bit)
Build: 1.0.0.68

A severe error occurred on the current command. The results, if any, should be discarded.
A severe error occurred on the current command. The results, if any, should be discarded.
Warning: Null value is eliminated by an aggregate or other SET operation.
.Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at SQLIndexManager.QueryEngine.GetIndexes(SqlConnection connection)
at SQLIndexManager.MainBox.ScanIndexes(Object sender, DoWorkEventArgs e)

Hello. When I try to scan DB' indexes I get the error.

Update data about indexes asynchronously, for some reason in my DBs it takes ages to get data about indexes.

1. Get a list of all indexes, show them in UI
2. Get data for each index, update dynamically table data

Second

• Ability to select rows as usually in UI with Shift/Ctrl
• Change FIX type for only selected row or checked [x] rows

Killer feature:

• Change FILEGROUP
• Support for moving BINARY/LOB to selected FILEGROUP

Thank you for your awesome tool.

You have a good settings pack in command line. Please create "presets" in GUI to create presets for command line use in futute:
SQLIndexManager.exe /preset:presetname
It makes using command line more simple.

In some cases, the option 'Drop table' shows as a possible fix in the combo of posible fixes.
I think this is a bit dangerous (specially for a tool intended to fix indexes only).
Best not showing it.

SQLindexManager_error.txt
The app finds my Azure SQL database ID 5

When try to rebuild an index over partitioned table and using the partition ID, rises the message: 'ONLINE' is not a recognized ALTER INDEX REBUILD PARTITION option.

This is the fix script:

RAISERROR(N'FDESK_CANCUN | dbo.tblUnitTime | IX_Composite_2 [ 77 ] | 6.95 MB', 0, 1) WITH NOWAIT
ALTER INDEX [IX_Composite_2] ON [dbo].[tblUnitTime] REBUILD PARTITION = 77
WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = NONE, ONLINE = OFF, MAXDOP = 0);
GO

Application has encountered an unexpected error\r\nPlease send error detail to sergey.syrovatchenko @gmail.com\r\nBuild: 1.0.0.46\r\nOS: Microsoft Windows NT 6.2.9200.0\r\n\r\nThe system cannot find the file specified\r\nSystem\r\n at System.Diagnostics.Process.StartWithShellExecuteEx(ProcessStartInfo startInfo)
at System.Diagnostics.Process.Start(ProcessStartInfo startInfo)
at SQLIndexManager.AboutBox.Copyright_HyperlinkClick(Object sender, HyperlinkClickEventArgs e)
at DevExpress.XtraEditors.LabelControl.RaiseHyperlinkClick(HyperlinkClickEventArgs e)
at DevExpress.XtraEditors.LabelControl.TryClickHyperlink(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.Utils.Controls.ControlBase.WndProc(Message& m)
at DevExpress.XtraEditors.LabelControl.WndProc(Message& msg)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

The same problem with Feedback and GitHub Project links.
OS: Windows 10 Pro 1903 x64

Hi, @sergeysyrovatchenko
Great thanks for very nice tool. Could you add compiled (.exe) version of tool in releases (I do not have VS installed on my laptop)?

Hello, first i want to send my felicitation beacause i like very much your application.
Now, my question....
Can you put over the flag information about them, example, duplicate index... etc... like a over mouse.

Thanks very much for your time and your works....

Congratulation!!