Updating index statistics sql

Stale statistics can lead to poor geodatabase performance.

So what is the use to update statistics of an outdated index.Pinal is also a Cross Fit Level 1 Trainer (CF-L1) and Cross Fit Level 2 Trainer (CF-L2).Tuning your RDBMS is an important aspect of maintaining your enterprise geodatabase.USE tempdb GO -- Drop Table if Exists IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Table Name]') AND type IN (N'U')) DROP TABLE [dbo].[Table Name] GO -- Create Table CREATE TABLE [dbo].[Table Name]( [ID] [int] NOT NULL, [First Col] [varchar](50) NULL ) GO -- Insert Some data INSERT INTO Table Name SELECT 1, 'First' UNION ALL SELECT 2, 'Second' UNION ALL SELECT 3, 'Third' UNION ALL SELECT 4, 'Fourth' UNION ALL SELECT 5, 'Five' GO -- Create Clustered Index ALTER TABLE [Table Name] ADD CONSTRAINT [PK_Table Name] PRIMARY KEY CLUSTERED ([ID] ASC) GO -- Create Nonclustered Index CREATE UNIQUE NONCLUSTERED INDEX [IX_Non Clustered_Table Name] ON [dbo].[Table Name] ([First Col] ASC) GO -- Check that all the indexes are enabled SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'Table Name' GO -- Update the stats of table UPDATE STATISTICS Table Name WITH FULLSCAN GO -- Check Statistics Last Updated Datetime SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS Stats Updated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('Table Name') GO -- Disable Indexes -- Disable Nonclustered Index ALTER INDEX [IX_Non Clustered_Table Name] ON [dbo].[Table Name] DISABLE GO -- Disable Clustered Index ALTER INDEX [PK_Table Name] ON [dbo].[Table Name] DISABLE GO -- Check that all the indexes are disabled SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'Table Name' GO -- Update the stats of table UPDATE STATISTICS Table Name WITH FULLSCAN GO /* -- Above operation should thrown following error Msg 1974, Level 16, State 1, Line 1 Cannot perform the specified operation on table 'Table Name' because its clustered index 'PK_Table Name' is disabled.*/ -- Now let us rebuild clustered index only ALTER INDEX [PK_Table Name] ON [dbo].[Table Name] REBUILD GO -- Check that all the indexes status SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'Table Name' GO -- Check Statistics Last Updated Datetime SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS Stats Updated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('Table Name') GO -- Update the stats of table UPDATE STATISTICS Table Name WITH FULLSCAN GO -- Check Statistics Last Updated Datetime SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS Stats Updated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('Table Name') GO -- Clean up DROP TABLE [Table Name] GO Pinal, What is the use of updating a disabled index?

Search for updating index statistics sql:

updating index statistics sql-30updating index statistics sql-74

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating index statistics sql”

  1. I believe it’s important to step back from the pressures around you and take a good look at the whole issue of dating. I hope that by giving you such a full answer, it will open your heart to the counter-cultural approach I am proposing to you regarding romance, dating and courtship.