How to optimize sql query in Microsoft SQL Server

1. Keep in mind that when you write a Store Procedure SQL Server generates an SQL plan. If you happen to write a Store Proc and have used an "OR" SQL Server will generate only one plan, therefore there will be no choosing which plan is better. 

- Do not use "OR" in a query instead the best solution is to use "IF", "Else", check for one used case if true then run the query else run the other one.
- Move conditonal logic outside the query in order for the SQL Server to generate another Execution plan to compare on the fastest.
- If you are worried about using more IF Else blocks of code then use  Dynamic SQL to write your queries. 

SQL Execution Plan

2.  When running SQL query click the actual plan on the SQL Server Management to see the Plan SQL Server generated for you. If there are warnings you will see them as well as suggestions in some cases.

- When coding Store Proc make sure you are NOT using Temp DB, else query won't scale. Every time the query runs SQL Server has to query the Temp Database that could be overloaded by other database queries, in a traffic-heavy environment like production, every time the Temp Database is hit, there is a chance of taking long to respond.

[NB] Solution is to make sure to look at the execution plan and make sure that the temp database is avoided by all means.

2. Optimizing a query with an OR, you could write a function like so: 

Create OR Alter Function()
 Return Table as
Return (--Then write your query here)

- The query above is called writing inline SQL which is much faster.
you could then use the inline function against other queries.

[Updated] If you run a store procedure and look at the execution plan, you will see some suggestions. In this case, the suggestion I got was to create a non-clustered index on the table that had an impact.

Create Nonclustered Index 
_IX_NameOfYourIndex On TableName(ColumnName)
Include(AnotherColumnName,AnotherColumnName)

Related Article: What is New in Entity Framework 5

I have included a documentation on how to optimize a long-running query, mostly you should avoid using Joins un-necessary unless you really need to use it.