Mssql Profiler Bundle Laravel Package

Getting Started

Minimal Setup

1. Install the Package

   composer require --dev dek-cz/mssql-profiler-bundle
   

   Add to config/bundles.php (for Laravel 8+):

   Dekcz\MssqlProfiler\MssqlProfilerBundle::class => ['dev' => true, 'test' => true],
   

2. Configure the Bundle Create config/packages/dev/mssql_profiler.yaml (or test/):

   mssql_profiler:
       web_profiler: true
       client_definition: 'App\Services\MssqlConnector'  # Your custom connector class
   

3. Implement the Collector Interface Create a custom connector (e.g., app/Services/MssqlConnector.php):

   use Dekcz\MssqlProfiler\DataCollector\MssqlCollectorInterface;
   use Dekcz\MssqlProfiler\DataCollector\MssqlCollectorTrait;
   
   class MssqlConnector implements MssqlCollectorInterface
   {
       use MssqlCollectorTrait;
   
       // Override methods to inject your DB connection logic
       public function getConnection(): \PDO
       {
           return \DB::connection('sqlsrv')->getPdo();
       }
   }
   

4. First Use Case Run a Laravel app in dev mode (APP_ENV=dev) and visit /_profiler. The toolbar will now include an MSSQL Procedures tab, showing stored procedure calls.

Implementation Patterns

Workflow Integration

1. Database Connection Handling

   • Use Laravel’s built-in DB facade or a custom connection (e.g., sqlsrv).
   • Override getConnection() in your collector to return the correct \PDO instance.
   • Example:

     public function getConnection(): \PDO
     {
         return \DB::connection('sqlsrv')->getPdo();
     }
     

2. Query Logging

   • The bundle automatically captures stored procedure calls (e.g., EXEC sp_name).
   • For raw SQL queries, ensure your PDO driver logs them (e.g., via PDO::MYSQL_ATTR_INIT_COMMAND or middleware).

3. Environment-Specific Activation

   • Enable only in dev/test environments via bundles.php and mssql_profiler.yaml.
   • Disable in production to avoid overhead.

4. Custom Data Collection

   • Extend MssqlCollectorTrait to add custom metrics (e.g., execution time, parameters).
   • Example:

     public function collect(\Symfony\Component\HttpFoundation\Request $request)
     {
         $this->startCollection();
         // Your custom logic (e.g., log slow procedures)
         $this->stopCollection();
     }
     

5. Toolbar Integration

   • The bundle adds a new toolbar icon (🔧) and a Profiler panel under "MSSQL Procedures."
   • Useful for debugging stored procedure performance in real-time.

Gotchas and Tips

Pitfalls

1. Connection Issues

   • If getConnection() returns null, the profiler will show no data.
   • Fix: Verify your PDO connection is active and accessible in the collector.

2. Stored Procedure Filtering

   • The profiler captures all stored procedure calls, including system procedures (e.g., sp_who2).
   • Tip: Filter out noise by overriding shouldCollect():

     public function shouldCollect(string $procedureName): bool
     {
         return !str_starts_with($procedureName, 'sp_');
     }
     

3. Performance Overhead

   • Profiling adds minor latency. Disable in production via bundles.php.

4. Missing Data in Profiler

   • If no procedures appear, check:
     • The client_definition in mssql_profiler.yaml points to a valid class.
     • Your PDO driver supports procedure logging (e.g., SQL Server’s PDO_SQLSRV).
     • The web_profiler is set to true.

Debugging Tips

1. Log Collector Events Add debug logs in your collector:

   public function collect(\Symfony\Component\HttpFoundation\Request $request)
   {
       \Log::debug('MSSQL Profiler: Starting collection');
       $this->startCollection();
       // ...
   }
   

2. Verify PDO Driver Ensure your PDO driver supports procedure logging:

   $pdo = \DB::connection('sqlsrv')->getPdo();
   \Log::debug('PDO Driver:', [$pdo->getAttribute(\PDO::ATTR_DRIVER_NAME)]);
   

3. Clear Cache After changes, run:

   php artisan config:clear
   php artisan cache:clear
   

Extension Points

1. Custom Procedure Metadata Extend the trait to fetch additional details (e.g., parameter values):

   public function getProcedureMetadata(string $procedureName): array
   {
       $metadata = parent::getProcedureMetadata($procedureName);
       $metadata['custom_field'] = $this->fetchCustomData($procedureName);
       return $metadata;
   }
   

2. Visual Customization Override the Twig template for the profiler panel:

   • Copy vendor/dekcz/mssql-profiler-bundle/resources/views/profiler.html.twig to resources/views/vendor/mssql_profiler/profiler.html.twig.
   • Modify to highlight slow procedures or add charts.

3. Integration with Laravel Debugbar If using barryvdh/laravel-debugbar, merge MSSQL data into the existing panel:

   public function collect(\Symfony\Component\HttpFoundation\Request $request)
   {
       $this->startCollection();
       $data = $this->getCollectedData();
       \Debugbar::addCollector(new MssqlDebugbarCollector($data));
   }