Data Diagnostics and Missing Index Recommendations

What is this?

When Margin Master gets slow - a refresh that used to take a second now takes ten, a strategy run that crawls, selection boxes that lag - the cause is almost always the database rather than the program: indexes SQL Server wishes it had, fragmented indexes, stale statistics, or a file that has grown far past the data it holds. Three built-in tools diagnose and fix those without a database administrator, and this page is the order to try them in.

Start here: slow grid or slow strategy run

Run these when no import or strategy is in progress; on a large database Optimize/Shrink can take several minutes and holds the window with a Please Wait panel.

Who is this for?

Any Margin Master user who notices the application slowing down, or who wants to proactively maintain database health. No SQL Server expertise is required -- the tools guide you through each step and explain what they are doing.

Where to Find These Tools

The SQL optimization features are accessed from two locations in the menu bar:

Missing Index Recommendations

What It Does

SQL Server continuously tracks which indexes would improve query performance. This tool reads those recommendations from SQL Server's Dynamic Management Views (DMVs) and presents them in a simple interface where you can review, register, and create the suggested indexes.

Indexes that you register are stored in a Managed Index Registry inside your database. Whenever Margin Master rebuilds a table (for example, during data import), all registered indexes for that table are automatically recreated. This means you don't lose your performance tuning when tables are rebuilt.

Opening the Window

Click Help > Missing Index Recommendations from the menu bar. The window loads automatically and queries your database for current recommendations.

The Missing Index Recommendations Window

1. DMV Recommendations - every index SQL Server currently suggests, sorted by impact score.
2. Copy CREATE INDEX, Add to Registry and Add All to Registry - act on the selected recommendation, or register all of them.
3. Auto-create threshold - the impact score above which Margin Master creates indexes without asking.
4. Managed Index Registry - the indexes Margin Master owns and recreates after every table rebuild.
5. Remove from Registry - stop managing the selected index.
6. Add All & Rebuild / Rebuild All Indexes - register everything and build, or rebuild the registry as it stands.

The window has two main sections:

Top Section: DMV Recommendations

This grid shows SQL Server's current recommendations, sorted by impact score (highest first). Each row represents a suggested index:

Tip: Recommendations with impact scores in the thousands or higher are strong candidates for creation. Low scores (under 100) may not provide noticeable improvement.

Bottom Section: Managed Index Registry

This grid shows all indexes you have previously registered. These are the indexes that Margin Master will automatically recreate after table rebuilds:

Actions

The following buttons are available between and below the two grids:

Auto-Create Threshold

On the right side of the action bar, a "Auto-create threshold" dropdown controls which recommendations are automatically created during strategy execution:

This setting is persisted between sessions. During strategy execution, Margin Master checks for missing index recommendations that exceed this threshold and creates them automatically.

Stale Index Detection

If the window detects managed indexes that reference columns no longer present in your active selection boxes, a warning message appears in orange text:

"3 managed index(es) reference columns no longer in selection boxes. Consider removing them."

This happens when selection box fields are changed after indexes were created. Stale indexes consume disk space and slow down data writes without providing query benefits.

Click the Remove Stale Indexes button (which appears alongside the warning) to automatically remove all detected stale indexes from the registry in one click.

Deduplication

The recommendations grid automatically deduplicates results from SQL Server. If multiple DMV entries suggest the same key columns on the same table, only the highest-impact version is shown. Recommendations for indexes that are already in the registry are also filtered out.

Workflow: Reviewing and Creating Indexes

1. Open Help > Missing Index Recommendations
2. The window loads DMV recommendations and your existing registry
3. Review recommendations sorted by impact score -- focus on high-score entries
4. Select a recommendation and click Add to Registry to save it, or click Add All to Registry for all of them
5. Click Rebuild All Indexes to create the registered indexes on disk
6. Alternatively, use Add All & Rebuild to do steps 4 and 5 in one click
7. Check the status message at the bottom for confirmation (e.g., "3 recommendation(s), 5 managed index(es) in registry.")
8. If a stale index warning appears, click Remove Stale Indexes to clean up indexes for columns no longer in your selection boxes
9. Click Close when done

Optimize/Shrink Database

What It Does

This is a one-click optimization tool that performs a comprehensive database health check and cleanup. It scans for fragmented indexes, rebuilds them, updates statistics, and shrinks the database files to reclaim unused disk space.

Opening the Tool

Click File > Database > Utilities > Optimize/Shrink Database from the menu bar.

What Happens Step by Step

Step 1: Unknown Table Check

Margin Master first scans for tables in your database that are not part of its known schema. If unknown tables are found, you are prompted:

"There are 3 Unknown Tables in your Database."

(A list of the table names is shown)

"Would you like to remove them ALL?"

You have three choices:

• Yes -- Deletes all unknown tables
• No -- If there are multiple unknown tables, you are prompted for each one individually ("Would you like to remove Table XYZ?"). You can answer Yes, No, or Cancel for each
• Cancel (on individual prompts) -- Stops checking remaining tables

Important: Unknown tables are those not matching Margin Master's internal pattern list. These are typically leftover temporary tables or tables from older versions. If you are unsure, choose No to skip deletion.

Step 2: Space Report

After the table check, a dialog shows your current database size:

"Your Database is 245.50 MB with unused space of 62.30 MB (25.4%)"

If the unused space percentage is below 20%, the message adds:

"Optimization is NOT REQUIRED at this time."

"Would you like to OPTIMIZE Anyway?"

If unused space is 20% or higher, the message says:

"Would you like to OPTIMIZE your database now?"

Click Yes to proceed with optimization, or No to skip.

If your database is hosted on Azure SQL, the message reads differently -- see Azure SQL below.

Step 3: Database Shrink

The database data files are shrunk first, using DBCC SHRINKDATABASE with a target of 10% free space remaining. This reclaims unused pages left behind by deleted data and earlier rebuilds.

"Shrinking database to reclaim space (this may take a moment)..."

Shrinking runs before the index rebuild on purpose: a shrink moves pages around and fragments indexes, so rebuilding afterwards leaves the database both smaller and defragmented.

Step 4: Log File Shrink

The transaction log file is shrunk to reclaim space:

"Shrinking transaction log file..."

Step 5: Index Fragmentation Analysis

A progress screen appears as the system scans all indexes for fragmentation. An index is considered fragmented if its fragmentation exceeds 30% and it is large enough for fragmentation to matter (about 8 MB or more).

The scan uses SQL Server's sys.dm_db_index_physical_stats DMV in LIMITED mode for efficiency.

Step 6: Index Rebuild

Each fragmented index is rebuilt one at a time. The progress screen shows:

"Rebuilding index 3 of 12: IX_Store_StoreNumber"

Index rebuilds use the following settings:

• FILLFACTOR = 90 -- Leaves 10% free space on each page to accommodate future inserts without immediate page splits
• ONLINE = OFF -- Locks the index during rebuild for maximum speed
• MAXDOP = 1 -- Uses a single processor to avoid contention on busy servers

If a deadlock occurs during an index rebuild, the system retries up to 3 times with increasing wait times (4, 8, and 16 seconds). If all retries fail, that index is skipped and the process continues.

Step 7: Statistics Update

After index rebuilds, the system runs sp_updatestats to refresh all table statistics. Accurate statistics help SQL Server choose efficient query plans.

The progress screen shows:

"Updating database statistics (this may take a moment)..."

Step 8: Results

The final message reports how much space was recovered:

"Optimization complete! Freed 45.20 MB (18.4% reduction)"

If the database was already well optimized:

"Optimization complete! Database is already well optimized."

Automatic Daily Optimization

Margin Master also runs this optimization on its own, at most once a day, shortly after startup -- but only when the space report shows 20% or more unused space. When it runs you will see the same progress messages ("Shrinking database to reclaim space...", "Rebuilding index 3 of 12...") on the startup splash screen. There is nothing to click; let it finish.

Azure SQL

If your Margin Master database is hosted on Azure SQL Database (including the Hyperscale tier) or Azure SQL Managed Instance, Margin Master detects that and changes what it does:

• The database and log are never shrunk. Azure manages the storage itself; a shrink there is slow, generates a large amount of transaction log, and inflates your backups without reclaiming anything you are charged for.
• The automatic daily optimization does not run. Its trigger is the unused-space percentage, which is not a meaningful number on Azure SQL.
• Optimize/Shrink Database still works, but the Step 2 message tells you the shrink will be skipped and asks whether to rebuild fragmented indexes and update statistics instead.
• Optimize Database Indexes works the same way and reports "shrink skipped: Azure SQL" in its summary.
• Shrink Log Files is not offered in Run Maintenance Scripts.

Nothing needs to be configured for this; the detection is automatic.

Space Report Details

The space report measures three values:

The optimization threshold is 20% unused space. Above this level, the tool recommends optimization.

Run Maintenance Scripts

What It Does

This tool provides eight individual SQL Server maintenance operations that you can run selectively. Unlike the one-click optimizer, this gives you fine-grained control over exactly which maintenance tasks to perform.

Opening the Tool

Click File > Database > Utilities > Run Maintenance Scripts from the menu bar.

The Maintenance Scripts Window

A checklist of available maintenance operations is displayed. You can select individual scripts or use "Select All" to check everything.

Available Scripts

1. Rebuild and Reorganize Indexes

Scans all indexes across the database and either rebuilds or reorganizes them based on fragmentation level:

2. Update Statistics

Runs sp_updatestats to refresh statistics for all tables. SQL Server uses statistics to decide which query plan to use. Outdated statistics can lead to slow queries because SQL Server may choose suboptimal plans.

When to use: After large data imports, bulk deletes, or whenever query performance degrades unexpectedly.

3. Remove Unused Indexes

Identifies indexes that have had zero user seeks, scans, or lookups since the last SQL Server restart. These indexes consume disk space and slow down INSERT/UPDATE/DELETE operations without providing any query benefit.

The script reports unused indexes with their DROP INDEX statements. Primary key and unique indexes are never flagged.

Note: This script identifies unused indexes and generates DROP statements but does not automatically execute them. Review the output before taking action.

4. Kill Long Running Queries

Lists all currently executing queries that have been running for more than 60 seconds. For each long-running query, it shows:

• Session ID
• Start time
• Status and wait information
• Database name
• Query text (first 2,000 characters)
• A KILL command you can execute if needed

Note: This script identifies long-running queries but does not automatically kill them. The KILL statements are provided for your review.

5. Shrink Log Files

Reduces the size of transaction log files to a maximum of 500 MB. Transaction log files can grow very large during heavy operations (like bulk imports or strategy execution) and do not automatically shrink.

When to use: When disk space is low and log files have grown significantly.

This script is not listed when the database is hosted on Azure SQL (see Azure SQL).

6. Remove Duplicate Indexes

Identifies indexes that have the exact same column list as another index on the same table. Duplicate indexes waste disk space and slow down write operations. The script reports duplicates with their DROP INDEX statements.

Note: This script identifies duplicates but does not automatically drop them. Review the output carefully -- sometimes indexes with the same columns but different INCLUDE columns serve different purposes.

7. Identify Missing Indexes

Queries SQL Server's missing index DMVs and reports all missing index recommendations with their columns, seek counts, and average cost. This is similar to the Missing Index Recommendations tool but runs as a raw SQL script and shows results in the output log rather than an interactive grid.

8. Create Missing Indexes

Generates CREATE INDEX statements for all missing index recommendations from DMVs and logs them. This script provides the SQL statements for review.

Running Scripts

1. Open File > Database > Utilities > Run Maintenance Scripts
2. Check the scripts you want to run (or click "Select All")
3. Click Process
4. A progress screen shows each script running in sequence with a status message
5. After each script completes, a success or failure message is displayed
6. When all selected scripts have finished, the dialog closes

Tip: Scripts run one at a time in the order listed. If one fails, the remaining scripts still execute.

How These Tools Work Together

The three tools complement each other for different use cases:

Automatic Index Management

In addition to the manual tools, Margin Master performs some index management automatically:

• Critical indexes (such as Store's StoreNumber+SKU composite index and mainTable StoreNumber indexes) are seeded into the Managed Index Registry when tables are first created
• After table rebuilds (during data import), all enabled managed indexes for the rebuilt table are automatically recreated
• During strategy execution, if Query Store is enabled (SQL Server 2016+), the system analyzes query performance and can auto-create high-impact missing indexes based on your configured threshold

Common Questions

Q: Will optimization lock my database or prevent others from working? A: Index rebuilds use ONLINE = OFF, which briefly locks individual tables during the rebuild. Other tables remain accessible. The overall optimization process runs in the background with a progress screen. Avoid running optimization while another user is actively executing a strategy.

Q: How often should I optimize? A: There is no fixed schedule. Run the Optimize/Shrink Database tool if you notice the application slowing down, or after importing large amounts of new data. The tool checks whether optimization is needed and tells you if your database is already healthy.

Q: What is the Managed Index Registry? A: It is a database table (ManagedIndexes) that stores the definitions of indexes you want to keep. Because Margin Master rebuilds (DROP + CREATE) data tables during import, regular indexes would be lost. The registry ensures your performance-tuned indexes are automatically recreated after every table rebuild.

Q: Can these tools damage my data? A: No. Index rebuilds and statistics updates do not modify your data -- they only reorganize how SQL Server stores and accesses it. The Optimize/Shrink Database tool can optionally delete unknown tables, but only with your explicit confirmation for each table.

Q: What if I don't have SQL Server 2016 or later? A: The Missing Index Recommendations and all maintenance scripts work on SQL Server 2005 and later. The Query Store features (automatic performance analysis during strategy execution) require SQL Server 2016 or later but gracefully degrade on older versions.

Q: What does "Impact Score" mean? A: The impact score is calculated by SQL Server as: (user_seeks + user_scans) * avg_total_user_cost * avg_user_impact. It represents the cumulative performance benefit of creating the index. Higher numbers mean more queries would benefit more significantly.

Q: What happens if an index rebuild fails? A: The system logs the error and moves on to the next index. A failed rebuild on one index does not prevent the remaining indexes from being processed. Deadlock errors are retried up to 3 times automatically.

Q: Should I remove indexes flagged as "stale"? A: Stale indexes reference columns that are no longer in your active selection boxes. If you are certain you won't re-add those columns, removing the stale indexes from the registry saves disk space and improves write performance. The index is only removed from the registry -- it is not dropped from the database automatically.