Why parameter for one query returning the same # of results as a different parameter, is extremely disproportionately slower I am an application developer that has inherited a SQL Database from a previous developer. There is one view that is causing an issue. This view selects a bunch of different columns from different tables and contains 6 different inner joins. I am running a query on this view like so...

SELECT * FROM dbo.View where UserID = " + employeeId + "


When I run this query on all of the different employees it returns fine within a few seconds. For one employee however, It takes up to a minute or two even when the result set is nearly the same in terms of quantity and times out in the API. I have narrowed the cause to one of the joined tables which makes sense because this joined table has the most rows and makes a lot of connections. I don't know how to diagnose the problem any further.

What I have tried:


creating an identical table as the table that is causing the issue, copying over the same data, but with a new clustered index. The clustered index column I changed to is the column that is being used in the joins by the view. This didn't help. Is there something I need to do "run the cluster and sort" or something like that?


How would one of you move forward with further diagnosing?
Look at the execution plans.

SQL Server will build different execution plans based on the different number of rows that it expects to return. (Even though the ACTUAL number of rows that may be returned could be the same, the ACTUALS could be different.)

To get the actual execution plans in SSMS, click Query, Include Actual Plan. Then you can compare the different plans to see if they have different shapes.

If you're using SQL Server Management Studio 2016, you can use the Compare Showplan feature to make that easier. (It works even for querying 2008 boxes.)

If you can share the execution plans publicly, use PasteThePlan.com to upload the two different execution plans, and then folks can point out what's different between them. (Be aware that this tool is completely public - everyone will see the full text of your query, and your parameters.)