SQL Server Fragmentation: What it is, what are it’s types (Part 1)

go In this post, we’ll talk about what fragmentation is, what are its different types in SQL Server, how it impacts performance (and when it doesn’t).

This is the first post in the multi part series. In the following posts in this series, we’ll see what causes fragmentation part-2, how to check for it(using DMVs) part-3, how to address it(remove it) part-4, and how to avoid it(control it) part-5.
Read more SQL Server Fragmentation: What it is, what are it’s types (Part 1)

CS #1 Using SQL Hint OPTION RECOMPILE for improving performance

http://threequartersthere.com/2016/04/ Problem Statement 

Stored procedure buy generic Lyrica CS1_RECOMPILE is running slow for some input parameters combinations and running fine for other input parameters.

CREATE PROC CS1_RECOMPILE 
     @start_prod_id INT,
        @end_prod_id   INT
AS
  BEGIN
    SELECT TOP 10 ProductId
    FROM bigProduct
    ORDER BY ListPrice DESC;    

    SELECT TOP 10 *
    FROM bigTransactionHistory
    WHERE ProductID BETWEEN @start_prod_id AND @end_prod_id
    ORDER BY ActualCost DESC;
 END;
GO

Read more CS #1 Using SQL Hint OPTION RECOMPILE for improving performance