SQL Database queries

Question

Hello,

I am planning to do database index as per the below procedure.
https://stevethompsonmvp.wordpress.com/2016/11/29/optimizing-sccm-databases-revisited/

I have below doubts and please assist

1. What are the pre-requesite before I start the Indexing
2. How do I duplicate the database to perform the test before doing the indexing in actual database
3. How to check the health of the SQL before start the indexing and after complete of the indexing

Answer 1

Creating a test environment for SSCM may be not possible for you. You may want to script your current indexes. Examine the current performance of your stored procedures with a query like below:

SELECT TOP (100)
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id]),
    type_desc,
    cached_time,
    last_execution_time,
    execution_count,
    total_worker_time,
    last_worker_time,
    min_worker_time,
    max_worker_time,
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,
    max_physical_reads,
    total_logical_writes,
    last_logical_writes,
    min_logical_writes,
    max_logical_writes,
    total_logical_reads,
    last_logical_reads,
    min_logical_reads,
    max_logical_reads,
    total_elapsed_time,
    last_elapsed_time,
    min_elapsed_time,
    max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE DB_NAME(database_id) = N'Your Database Name'
ORDER BY total_elapsed_time / execution_count DESC;

Then create the indexes on the article you shared with us.

After creating the indexes clear the procedural cache.

DBCC FREEPROCCACHE 

After a day, run again the query to get the stored procedure performance statistics. Compare these results with the previous results.

You can also run below query to verify how many stored procedures are using the indexes you changed based on the Steve Thompson article. Run the query for each index you changed.

DECLARE @Index SYSNAME = N'PK_SalesOrderHeader_SalesOrderID';

SELECT t.[text], s.execution_count, CONVERT(XML, p.query_plan)
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_text_query_plan
(
  s.plan_handle, s.statement_start_offset, s.statement_end_offset
) AS p
CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
WHERE p.query_plan LIKE '%Index="\[' + @Index + '\]"%' ESCAPE '\';

Indexes changes not in use by any stored procedure you may want to consider roll them back.

Answer 2

First of all, are you using System Center Configuration Manager? The article is specifically geared for SCCM? If you are working with System Center, you may want to add a tag for System Center, to get input from that crowed. On the other hand, you have added a tag for Azure SQL Database, which makes me think that you are not using System Center at all.

Anyway, the article is about maintenance of existing indexes - not adding new indexes to the database. Few people would actually benchmark whether the index maintenance helps - although in this day and age, it is far from certain that it actually does.