How to improve performance of SP with many optional filtering parameters? I need to write many SP that have many optional filtering parameters. After reading Turgay Sahtiyan's (Senior SQL Server Premier Field Engineer at Microsoft) blog he mentions exactly the same problem and the way to solve it is through dynamic SQL. Found it in his slides http://blogs.msdn.com/b/turgays/archive/2013/09/07/sqlsaturday-243-cape-town-top-10-performance-tips-for-sql-server-developers.aspx

So my questions is it the only and the best approach or are there other options?

WHERE (@EmployeeFirstName IS NULL OR emp.First_Name LIKE @EmployeeFirstName )
AND (@EmployeeLastName IS NULL OR emp.Last_Name LIKE @EmployeeLastName )
AND (@EmployeeNetworkLogOn IS NULL OR emp.Network_ID LIKE @EmployeeNetworkLogOn )
AND (@EmployeeStatus IS NULL OR emp.Employee_Status = @EmployeeStatus )
AND (@EmployeeType IS NULL OR emp.Employee_Type = @EmployeeType )
AND (@SupervisorFirstName IS NULL OR sup.First_Name LIKE @SupervisorFirstName )
AND (@SupervisorLastName IS NULL OR sup.Last_Name LIKE @SupervisorLastName )
AND (@SupervisorNetworkLogOn IS NULL OR sup.Network_ID LIKE @SupervisorNetworkLogOn)
Going to be pretty tough to get consensus on what "best" means, since there is a trade-off with dynamic SQL (you gain some plan stability for each version of the query, depending on parameters, but you lose things like readability, IntelliSense, etc). I've used the dynamic SQL route in many implementations and I highly recommend it. 

Since you will be sending many varying queries to the server, it is best used in combination with the Optimize for Ad Hoc Workloads server setting. This prevents any plan from being fully cached and taking up valuable memory until the same query text has been submitted twice. And if you are seeing wide deltas in performance due to parameter sniffing, you may consider adding OPTION (RECOMPILE) to the query in all cases anyway (or at least in the cases that end up with huge plan skews). For example, you probably won't see any skew when the parameter is the PK and the query ends up being an exact match using a seek. But you might see greater skew when a datetime range is being used, or an exact match to a column with its own asymmetrical skew, and particularly when stats are not being religiously maintained (or no indexes support those specific queries).