2

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?

2 Answers 2

2

where was the clustered index first before you changed it (was it an identity column)?

If so then keep it as it is and create a non-clustered index on the column which you are using for the join and try.

0
3

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.