Performance improvement following a disable indexes, shrink, enable indexes - but why?

• Jeff Moden

  SSC Guru

  Points: 1004414

  More actions

  February 19, 2018 at 7:08 pm

  #1980369

  What do you mean by "pre-compacted"?  If you mean you did a shrink-file or shrink-database, then don't bother because just updating stats on tables that have been logically inverted by the shrink won't get you far at all.

  You've got some decent size objects and indexes there.  The grand total for the dpages and index pages is about 1.4TB.

  1.  Am I making a correct assumption when I presume that the items that have the "vwi" and "vw" prefixes aren't tables but indexed views?
  2.  Any chance of getting the DML for the table, constraints, and the indexes for the first 4 items on that list? (Attach as a text file)
  3.  Can you run the following code and attach the results as an Excel file?

  
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.DIM_SITE_PATIENT_OBSERVATION'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.vwi_grouped_prescription_status'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.FACT_SITE_PATIENT_MONTHLY_SNAPSHOT'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.VW_DIM_SITE_PATIENT_OBSERVATION'),NULL,NULL,'Sampled')
;

  Last but not least...
  4.  Do you have a disk on the system with about 600GB or so of free space on it that we can use for a temporary gyration if we need it?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• pcd_au

  Ten Centuries

  Points: 1289

  More actions

  February 19, 2018 at 9:09 pm

  #1980378

  Jeff Moden - Monday, February 19, 2018 7:08 PM

  What do you mean by "pre-compacted"?  If you mean you did a shrink-file or shrink-database, then don't bother because just updating stats on tables that have been logically inverted by the shrink won't get you far at all.

  You've got some decent size objects and indexes there.  The grand total for the dpages and index pages is about 1.4TB.

  1.  Am I making a correct assumption when I presume that the items that have the "vwi" and "vw" prefixes aren't tables but indexed views?
  2.  Any chance of getting the DML for the table, constraints, and the indexes for the first 4 items on that list? (Attach as a text file)
  3.  Can you run the following code and attach the results as an Excel file?

  
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.DIM_SITE_PATIENT_OBSERVATION'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.vwi_grouped_prescription_status'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.FACT_SITE_PATIENT_MONTHLY_SNAPSHOT'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.VW_DIM_SITE_PATIENT_OBSERVATION'),NULL,NULL,'Sampled')
;

  Last but not least...
  4.  Do you have a disk on the system with about 600GB or so of free space on it that we can use for a temporary gyration if we need it?

  Jeff
  'pre-compacted' means the database as delivered, with the fragmentation all in place. This is to test the recommendation from your and Gail on just updating the stats. 'pre' as in before we ran the compaction exercise.
  1. These are materialised views. They were 'just' views, now they are tables (with indexes)
  2. Let me see what I can do
  3. Not until the database gets restored, which may take a while as I'm only here 3 days a week and the accidental DBA is snowed under
  4. Possibly could be arranged

  pcd

• Jeff Moden

  SSC Guru

  Points: 1004414

  More actions

  February 20, 2018 at 9:21 am

  #1980487

  pcd_au - Monday, February 19, 2018 9:08 PM

  Jeff Moden - Monday, February 19, 2018 7:08 PM

  What do you mean by "pre-compacted"?  If you mean you did a shrink-file or shrink-database, then don't bother because just updating stats on tables that have been logically inverted by the shrink won't get you far at all.

  You've got some decent size objects and indexes there.  The grand total for the dpages and index pages is about 1.4TB.

  1.  Am I making a correct assumption when I presume that the items that have the "vwi" and "vw" prefixes aren't tables but indexed views?
  2.  Any chance of getting the DML for the table, constraints, and the indexes for the first 4 items on that list? (Attach as a text file)
  3.  Can you run the following code and attach the results as an Excel file?

  
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.DIM_SITE_PATIENT_OBSERVATION'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.vwi_grouped_prescription_status'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.FACT_SITE_PATIENT_MONTHLY_SNAPSHOT'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.VW_DIM_SITE_PATIENT_OBSERVATION'),NULL,NULL,'Sampled')
;

  Last but not least...
  4.  Do you have a disk on the system with about 600GB or so of free space on it that we can use for a temporary gyration if we need it?

  Jeff
  'pre-compacted' means the database as delivered, with the fragmentation all in place. This is to test the recommendation from your and Gail on just updating the stats. 'pre' as in before we ran the compaction exercise.
  1. These are materialised views. They were 'just' views, now they are tables (with indexes)
  2. Let me see what I can do
  3. Not until the database gets restored, which may take a while as I'm only here 3 days a week and the accidental DBA is snowed under
  4. Possibly could be arranged

  pcd

  pcd_au - Monday, February 19, 2018 9:08 PM

  Jeff Moden - Monday, February 19, 2018 7:08 PM

  What do you mean by "pre-compacted"?  If you mean you did a shrink-file or shrink-database, then don't bother because just updating stats on tables that have been logically inverted by the shrink won't get you far at all.

  You've got some decent size objects and indexes there.  The grand total for the dpages and index pages is about 1.4TB.

  1.  Am I making a correct assumption when I presume that the items that have the "vwi" and "vw" prefixes aren't tables but indexed views?
  2.  Any chance of getting the DML for the table, constraints, and the indexes for the first 4 items on that list? (Attach as a text file)
  3.  Can you run the following code and attach the results as an Excel file?

  
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.DIM_SITE_PATIENT_OBSERVATION'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.vwi_grouped_prescription_status'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.FACT_SITE_PATIENT_MONTHLY_SNAPSHOT'),NULL,NULL,'Sampled') UNION ALL
SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.VW_DIM_SITE_PATIENT_OBSERVATION'),NULL,NULL,'Sampled')
;

  Last but not least...
  4.  Do you have a disk on the system with about 600GB or so of free space on it that we can use for a temporary gyration if we need it?

  Jeff
  'pre-compacted' means the database as delivered, with the fragmentation all in place. This is to test the recommendation from your and Gail on just updating the stats. 'pre' as in before we ran the compaction exercise.
  1. These are materialised views. They were 'just' views, now they are tables (with indexes)
  2. Let me see what I can do
  3. Not until the database gets restored, which may take a while as I'm only here 3 days a week and the accidental DBA is snowed under
  4. Possibly could be arranged

  pcd

  Yeah... on #3, it would definitely be good to see with all the fragmentation all in place.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)