Execution Plan with Laravel Eloquent?

I am using Laravel 9.

I have built an API that connects to our clients SQL Server Databases for queries.

I want to be able to run a execution plan on a SQL Server script, in hopes to find any missing indexes.

After alot of research this is what keeps coming back to achieve my goal

use Illuminate\Support\Facades\DB;

function getExecutionPlanWithIndexes($query)
{
// Enable STATISTICS XML to capture the execution plan with index suggestions
DB::connection('sqlsrv')->statement('SET STATISTICS XML ON;');

// Run the query - this should return the execution plan with potential missing index recommendations
$plan = DB::connection('sqlsrv')->select($query);

// Disable STATISTICS XML after retrieving the plan
DB::connection('sqlsrv')->statement('SET STATISTICS XML OFF;');

// Display the XML execution plan
return $plan;
}

// Example usage:
$executionPlan = getExecutionPlanWithIndexes("SELECT * FROM your_table");
dd($executionPlan);


However, this is always just returning the results of the query not the execution plan,

If I open SSMS and run the same raw script, if does give me both the execution plan and the results. I have tried looking for 3rd party packages that can help but I am not having much luck, can anyone help?

Continue reading...