Tuesday 16 February 2016

Index REBUILD vs. REORGANIZE in SQL SERVER

Couple of days back there was an interesting statement (or rather a question) was brought up by one of the colleagues in the company. Ultimately the initial stement left us with one simple question, which is the difference between Index REBUILD and REORGANIZE and when should be exactly use it.

If you google the aforementioned you can find numerous posts/blogs regarding this. Therefore I will keep things very simple and easier way to understand.

Rebuilding an index or Reorganizing is required when index fragmentation has reached to a considerable percentage. The fragmentation percentage can be identified using the Dynamic Management View - sys.dm_db_index_physical_stats in SQL Server.

You may get more details on the view on the following link: https://msdn.microsoft.com/en-us/library/ms188917.aspx

You can get a list of fragmented indexes using the following query:

SELECT
    OBJECT_NAME(Stat.object_id)
    ,I.name
    ,Stat.index_type_desc
    ,Stat.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) AS Stat
JOIN sys.indexes AS I
        ON Stat.index_id = I.index_id
        AND Stat.object_id = I.object_id
WHERE
    Stat.avg_fragmentation_in_percent > 30

Executing the above query will give you a list of fragmented indexes which has more than 30% fragmentation. ‘index_type_desc’ will give you a hint what sort of index is it. (clustered, non-clustered, heap etc…)

As per the guidlines provided by Microsoft, it’s the best practice to Reorganize the index if the fragmentation is less than or equal to 30% (more than 5%) and Rebuild it if it’s more than 30%

 

Rebuilding Indexes

  • Should perform this if the fragmentation is more than 30%
  • Operation can be done online or offline

Index rebuilding can be done useing the following syntax:

In order to build all the indexes on a specific table:

USE <Database_Name>
GO

ALTER INDEX ALL ON <Table_Name> REBUILD
GO

 

In order to build only a specific index:

USE <Database_Name>
GO

ALTER INDEX <Index_Name> ON <Table_Name> REBUILD
GO

 

Reorganizing Indexes

  • Should perform this if the fragmentation is more than 5% but less than or equal to 30%
  • Operation is always online

Index reorganizing can be done using the following syntx:

In order to reorganize all the indexes on a specific table:

USE <Database_Name>
GO

ALTER INDEX ALL ON <Table_Name> REORGANIZE
GO

In order to reorganize only a specific index:

USE <Database_Name>
GO

ALTER INDEX <Index_Name> ON <Table_Name> REORGANIZE
GO

 

Optionally you can set many attributes during the Rebuild or Re-Organize process (Eg: FILLFACTOR, SORT_IN_TEMPDB etc..). Please check on the following link for more details on the REBUILD options: https://msdn.microsoft.com/en-us/library/ms188388.aspx

How ever REBUILD or REORGANIZE will not have an effect on the HEAP fragmentation. In order to remove the heap fragmentation you can use the followng syntax (*** NOT THE BEST PRACTICE):

USE <Database_Name>
GO

ALTER TABLE <Table_Name> REBUILD
GO

** Eventhough the aforementioned syntax will remove the HEAP fragmentation, it is considered as bad as creating and dropping a clustered index, which will leave behind lots of fragmentation on non clustered indexes. The best practise would be to create a clustered index on the table to remove the HEAP fragmentation. You can find more details on this on the following blog post by Paul. S Randal which he had illustrated nicely.

Wednesday 20 January 2016

Error accessing Oracle Database Objects via Linked Server in SQL Server (The OLE DB provider "OracleOLEDB.Oracle" for linked server reported an error. Access denied.)


In one of the project we are working these days, there was a requirement to fetch some details, directly from the Oracle Database via VIEWS. Initially everything was setup correctly on the Oracle Database & Server side so that we can access the relevant schemas and fetch data without any issue. And once the oracle client is setup and the configurations are correctly setup (“tnsnames.ora”), we were able to fetch the details using .Net Code. And when we checked using the Oracle SQL Developer UI, it was evident that the details were easily fetched.
How ever we faced an issue when we were asked to access and fetch the same set of details from SQL objects using OPENQUERY. Even when we try a simple query such as retrieving “sysdate”, we got an ‘Access Denied’ error.


SELECT FROM OPENQUERY ([LINKED_SERVER], 'SELECT sysdate FROM DUAL')

The OLE DB provider "OracleOLEDB.Oracle" for linked server reported an error. Access denied. Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "<Linked_Server>"

image_thumb[2]

After spending some time with the configurations on both SQL and Oracle side, we were able to rectify this issue by allowing “Allow inprocess” option in linked server providers in SQL side.

image_thumb[4]

I am sharing this hoping that it would help someone to resolve the similar kind of issue without any hassle.