Rebuild or Reorganize index SQL Server

Introduction

There are two solutions available to you depending on the severity of your issue

Replace with your own values, as follows:

• Replace XXXMYINDEXXXX with the name of an index.
• Replace XXXMYTABLEXXX with the name of a table.
• Replace XXXDATABASENAMEXXX with the name of a database.

Solution 1. Indexing

Rebuild all indexes for a table in offline mode

ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD

Rebuild one specified index for a table in offline mode

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD

Solution 2. Fragmentation

Fragmentation is an issue in tables that regularly have entries both added and removed.

Check fragmentation percentage

SELECT  
    ips.[index_id] ,
    idx.[name] ,
    ips.[avg_fragmentation_in_percent]
FROM    
    sys.dm_db_index_physical_stats(DB_ID(N'XXXMYDATABASEXXX'), OBJECT_ID(N'XXXMYTABLEXXX'), NULL, NULL, NULL) AS [ips]
    INNER JOIN sys.indexes AS [idx] ON [ips].[object_id] = [idx].[object_id] AND [ips].[index_id] = [idx].[index_id]

Fragmentation 5..30%

If the fragmentation value is greater than 5%, but less than 30% then it is worth reorganising indexes.

Reorganise all indexes for a table

ALTER INDEX ALL ON XXXMYTABLEXXX REORGANIZE

Reorganise one specified index for a table

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REORGANIZE

Fragmentation 30%+

If the fragmentation value is 30% or greater then it is worth rebuilding then indexes in online mode.

Rebuild all indexes in online mode for a table

ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD WITH (ONLINE = ON)

Rebuild one specified index in online mode for a table

ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD WITH (ONLINE = ON)

Connect to SQL Server with .bat file to run .SQL commands.

Save the following commands in a notepad with .bat as extension-

SQL Auth Mode:

REM name of the server that hosts the SQL Server DB
set svr= localhost
REM name of the DB
set dbname=databaseNAME
REM credentials of the user that has permissions to create DB objects
set uname=USERname
set pwd=PASSword

osql -S %svr% -d %dbname% -U %uname% -P %pwd% -i "Create_Table"  -o "Create_Table.txt"  -n


Windows Auth Mode:

REM name of the server that hosts the SQL Server DB
set svr= localhost
REM name of the DB
set dbname=databaseNAME

osql -E -S %svr% -d %dbname% -i "Create_Table.sql"  -o "Create_Table.txt"  -n

How to search for Arabic words using Thesaurus XML file in SQL Server.

Accent insensitive search in full-text catalog!

Turning off Accent insensitive option for existing search catalog will allow to search Arabic words using the Thesaurus feature in SQL Server.

In SQL server 2008 noise words have been replaced by stop words. http://msdn.microsoft.com/en-us/library/ms142551.aspx

And it is as you suspected; ”been” is classified as a stop word in the standard list for the English language. When the fulltext catalog was created with accent sensitivity the word “béen” was hit in searches. But when I created the index with accent insensitivity “béen” became equal to “been” and was therefore treated as a stop word. This explains the weird behavior and why “béen” would not be hit when accent insensitivity was set.

I used the following query to find the stoplist for my index. It was set to the value 0 meaning the default list was being used.

select fulltext_catalog_id,stoplist_id, * from sys.fulltext_indexes;

And this to get the list of words from the default list for the language.

select ssw.stopword, slg.name as language

from sys.fulltext_system_stopwords ssw

join sys.fulltext_languages slg

on slg.lcid = ssw.language_id and ssw.language_id = 1033;

The question is what I should do about this. Changing language from English doesn’t seem like such a good option since we have users from Sweden, Denmark, Estonia, Great Britain and soon Finland. “Béen” is actually the last name of one users.

UPDATE

I ended up with this solution.

-- Create stoplist copy from system stoplist
CREATE FULLTEXT STOPLIST SL FROM SYSTEM STOPLIST;

-- Set the index to use the new stoplist
ALTER FULLTEXT INDEX ON searchtable set stoplist SL

-- Removing "stop" word from stoplist

ALTER FULLTEXT STOPLIST SL

DROP 'been' LANGUAGE 'English';

Thanks for all the help finding this Erland.