Indexes get disabled RRS feed

  • Question

  • How do Indexes get disabled?
    Thursday, February 14, 2019 1:32 PM

All replies

  • By manually diabling the index.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, February 14, 2019 1:33 PM
  • Could a script or any other job do it?
    Thursday, February 14, 2019 1:35 PM
  • https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints?view=sql-server-2017

    SELECT i.name AS Index_Name, i.index_id, i.type_desc, s.name AS 'Schema_Name', o.name AS Table_Name
    FROM sys.indexes i
    JOIN sys.objects o on o.object_id = i.object_id
    JOIN sys.schemas s on s.schema_id = o.schema_id
    WHERE i.is_disabled = 1

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 14, 2019 1:43 PM
  • Of course it's script like ALTER INDEX ON dbo.Table DISABLE and that can be executed by a user or by a Job.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, February 14, 2019 1:45 PM
  • Yes, here's an example script from SQL Server documentation:

    USE AdventureWorks2012;  
    -- disables the IX_Employee_OrganizationLevel_OrganizationNode index  
    -- on the HumanResources.Employee table  
    ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  

    Disable Indexes and Constraints


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, February 14, 2019 1:50 PM
    Thursday, February 14, 2019 1:50 PM