Data Profiler - Deployment Guide

⚡ V3 Stored Procedure Deployment (Optional Performance Optimization)

TL;DR for DBAs:

📁 File: DatabaseProfiler.App/Docs/usp_ProfileTable_v3_OPTIMIZED.sql

🎯 Run it in SSMS on databases you want to profile

🚀 Get 30-40% faster profiling

✅ Optional - App works fine without it (automatic fallback)

The V3 stored procedure (usp_ProfileTable) provides significant performance improvements (30-40% faster) and richer metadata compared to the legacy dynamic SQL approach.

Is This Required?

No! The application works perfectly without the stored procedure:

✅ Profiling works out-of-the-box using dynamic SQL
✅ Automatic fallback if procedure not found
✅ Same core statistics (NULL counts, distinct values, MIN/MAX, averages, frequency analysis)
⚠️ Slightly slower performance
⚠️ Missing some advanced stats (string length analysis, date ranges)

Deploy the stored procedure when:

You want 30-40% faster profiling on large tables
You need detailed string length statistics
You want date range calculations
You're profiling production databases with performance requirements

Prerequisites

SQL Server 2016 or later
db_owner or CREATE PROCEDURE permission on target database

Option 1: Deploy to a Single Database

-- Connect to your target database in SSMS
USE YourDatabase;
GO

-- Run the entire usp_ProfileTable_v3_OPTIMIZED.sql script
-- Path: DatabaseProfiler.App/Docs/usp_ProfileTable_v3_OPTIMIZED.sql

Option 2: Deploy to All User Databases (Recommended for Multi-Database Profiling)

-- Create the procedure in each database you want to profile
EXEC sp_MSforeachdb '
IF DB_ID(''?'') > 4 -- Skip system databases
BEGIN
	USE [?];
	EXEC(''
		-- Paste usp_ProfileTable_v3_OPTIMIZED.sql content here
	'');
END
'

Option 3: Create in a Central Utility Database

You can create the procedure in a utility database and use USE statements:

USE UtilityDB;
GO

CREATE OR ALTER PROCEDURE dbo.usp_ProfileTable
	@DatabaseName sysname,
	@TableName sysname,
	...
AS
BEGIN
	DECLARE @SQL nvarchar(max);
	SET @SQL = 'USE ' + QUOTENAME(@DatabaseName) + '; ...';
	-- Execute profiling in target database
END

(This approach is more complex and requires cross-database permissions)

Application Configuration

The application is configured via appsettings.json:

{
  "TableReports": {
	"Profiling": {
	  "UseStoredProcedure": true,  // Enable v3 stored procedure
	  ...
	}
  }
}

Configuration Options

Setting	Default	Description
`UseStoredProcedure`	`true`	Use v3 stored procedure for profiling. If `false` or procedure not found, falls back to legacy dynamic SQL.

Fallback Behavior

The application includes automatic fallback logic:

If UseStoredProcedure = true:
- Attempts to call dbo.usp_ProfileTable
- If procedure not found (SQL Error 2812), automatically falls back to legacy approach
- Ensures profiling always works, even if procedure isn't deployed
If UseStoredProcedure = false:
- Uses legacy dynamic SQL approach directly
- No stored procedure required

Verification

Test the Stored Procedure in SSMS

-- Basic test
EXEC dbo.usp_ProfileTable @TableName = 'YourSchema.YourTable';

-- Without frequency analysis (faster)
EXEC dbo.usp_ProfileTable 
	@TableName = 'YourSchema.YourTable',
	@IncludeFrequencyAnalysis = 0;

-- Check execution time
SET STATISTICS TIME ON;
EXEC dbo.usp_ProfileTable @TableName = 'YourSchema.YourTable';
SET STATISTICS TIME OFF;

Test in the Application

Run the application
Navigate to the Profiling page
Select a database and table
Click Profile Table
Verify the rich metadata is displayed:
- Precision/Scale for numeric columns
- Collation for character columns
- Identity seed/increment
- Computed definitions
- Character length statistics
- Date range statistics

Performance Comparison

Scenario	V2 (Legacy)	V3 (Stored Proc)	Improvement
Small table (<10K rows, 20 cols)	2-3 sec	1-2 sec	~40% faster
Medium table (100K rows, 30 cols)	15-20 sec	10-14 sec	~35% faster
Large table (1M rows, 40 cols)	60-80 sec	40-55 sec	~33% faster

Using frequency analysis on AdventureWorks-style tables

Troubleshooting

"Could not find stored procedure 'dbo.usp_ProfileTable'"

Cause: Stored procedure not deployed to the target database.

Solution:

Connect to the database in SSMS
Run usp_ProfileTable_v3_OPTIMIZED.sql
Refresh the application

The application will automatically fall back to legacy mode if this error occurs.

Stored Procedure Returns Different Results Than Expected

Cause: Schema/data profiling expectations mismatch.

Solution:

Check the procedure version:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.usp_ProfileTable'));

Look for version comment at the top: -- Version: 3.0
Redeploy if necessary

Performance Not Improved

Possible causes:

Very small tables (overhead dominates)
Frequency analysis disabled (less room for optimization)
Storage/network bottlenecks

Verify:

-- Compare V2 vs V3 execution plans
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Your legacy profiling query here
-- vs
EXEC dbo.usp_ProfileTable @TableName = '...';

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Rollback to Legacy Mode

If you encounter issues with the v3 stored procedure:

Temporary (no code change):

{
  "TableReports": {
	"Profiling": {
	  "UseStoredProcedure": false  // Disable stored procedure
	}
  }
}

Permanent (remove procedure):

DROP PROCEDURE IF EXISTS dbo.usp_ProfileTable;

The application will continue to work using the legacy dynamic SQL approach.

Next Steps

After successful deployment:

Monitor Performance
- Compare profiling times before/after
- Verify accuracy of new metadata fields
Deploy to Additional Databases
- Use Option 2 script above for batch deployment
Explore Enhanced Features
- Precision/Scale display in Schema Browser
- Identity/Computed badges
- Character length statistics
- Date range analysis
- Richer Excel reports
Provide Feedback
- Report any data accuracy issues
- Share performance metrics
- Suggest additional metadata to profile

Support

For questions or issues:

Check the V3_IMPLEMENTATION_NOTES.md for technical details
Review the stored procedure comments in usp_ProfileTable_v3_OPTIMIZED.sql
Verify configuration in appsettings.json
Test fallback behavior by temporarily renaming the procedure

FilesExpand file tree

DEPLOYMENT_GUIDE.md

Latest commit

History