cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Index...on table...has fragmentation 90.00% and should be defragmented manually

Jump to solution

Is this still the solution for SQL Express?

https://support.solarwinds.com/Success_Center/Network_Performance_Monitor_(NPM)/Critical_index_fragm...

I have a SQL express server.  I followed the instructions up to the part where it states in step 4, "Right-click any table and select Query table."  Each table already has it's default or own query.  If I erase that and execute the query in this process, what will happen to the deleted query?  Will the table keep the new query I just executed for defragging?  I just want to make sure I don't break anything by doing this process because I am not a Database guy.  Please advise.

1 Solution
Level 12

Figured it out.  The steps for the SQL Server Express that I did were:

1.Open the Database Manager.

2.Click Add default server.

3.Locate and right-click your SolarWinds Orion database.

4.Select "New query..."

5.Execute the following query in the blank white area:

     UPDATE dbo.Settings SET CurrentValue = 1 WHERE SettingID LIKE 'dbm-defragmentation-enabled%'

Automatic index defagmentation is configured on your SolarWinds database and you can now exit the program.  This is not like Microsoft Office where you need to save your work which I thought I had to do.  If you want to check to see that defrag is enabled, follow steps 1-5 again BUT this time execute this query instead:

Select * from dbo.settings where settingID like 'dbm-defrag%'

A table will show up.  Look under the column "Name" for "Is defragmentation enabled?" and then look under the column "CurrentValue"  for "1" that corresponds to "Is defragmentation enabled?"  This "1" means that defrag is enabled.  "0" means it is disabled.

View solution in original post

10 Replies
Level 12

Figured it out.  The steps for the SQL Server Express that I did were:

1.Open the Database Manager.

2.Click Add default server.

3.Locate and right-click your SolarWinds Orion database.

4.Select "New query..."

5.Execute the following query in the blank white area:

     UPDATE dbo.Settings SET CurrentValue = 1 WHERE SettingID LIKE 'dbm-defragmentation-enabled%'

Automatic index defagmentation is configured on your SolarWinds database and you can now exit the program.  This is not like Microsoft Office where you need to save your work which I thought I had to do.  If you want to check to see that defrag is enabled, follow steps 1-5 again BUT this time execute this query instead:

Select * from dbo.settings where settingID like 'dbm-defrag%'

A table will show up.  Look under the column "Name" for "Is defragmentation enabled?" and then look under the column "CurrentValue"  for "1" that corresponds to "Is defragmentation enabled?"  This "1" means that defrag is enabled.  "0" means it is disabled.

View solution in original post

Level 11

Hi All,

I know it old thread. Can you please let me know how to check the current value?

     UPDATE dbo.Settings SET CurrentValue = 1 WHERE SettingID LIKE 'dbm-defragmentation-enabled%'

0 Kudos

select currentvalue from settings where settingid like 'dbm-defragmentation-enabled%'

- Marc Netterfield, Github
0 Kudos
Level 16

What version are you on? We have this enabled now by default.

Enable Automatic Index Defragmentation - SolarWinds Worldwide, LLC. Help and Support

0 Kudos
Level 12

Wanted to add to my questions.  Why wouldn't I right-click my Solarwinds Orion database and select "New query..." then execute the script there?  Why would I need to right-click and run the script in an existing table where I would need to erase the existing script and then execute another script within that table? 

0 Kudos

That query that comes up in SMSS is just a placeholder that would be like a show all kind of command.  You can safely erase it, no problems.  The commands they give you just update the contents of the settings table, when you go to close the window it will most likely ask you if you want to save the query but you do not need to. 

You can pull up your new query at the database or table level, they would be the same thing for this purpose since the table you are acting on is defined in the query you paste in.

- Marc Netterfield, Github
0 Kudos
Level 12

mesverrum,

So it would be okay to just create a new query in my server to execute the script then not save that new query?  The defrag will still happen automatically every night?

0 Kudos
Level 16

Step 4 does state to right click on your Orion database and select new query not sure where you saw to run on an existing table also if you are on core 2017.3 this is enabled by default in your polling settings.

  1. Expand Databases > Your_SolarWinds_Database > Tables.
  2. Click New Query.
0 Kudos
Level 12

neo,

You were looking at the other step 4.  The step 4 that concerns me is for the SQL express server and that one states:

4. Right-click any table and select Query table.

0 Kudos

The query in that example is turning on a setting within orion, and as Neo said, for the last several versions that option is already set so it is likely to not be much help to you, but maaaaaaybe your environment is different.

- Marc Netterfield, Github
0 Kudos