• /* Contest Entry */

    You said that this was a 2008 machine, so for Query 1 and 2 (and the 3rd non top 10 query to do the insert) you would be fine with a MERGE statement (killing three birds with one stone):

    /* Query 1 & 2 */

    DECLARE @roundedtime datetime = '2011-07-17 00:00:00.000',

    @data varchar(50) = 'article_loginclick' ;

    BEGIN TRAN

    MERGE dbo.AnalysisLog trg

    USING

    (SELECT @roundedtime AS [Time],

    @data AS [Data]) src

    ON src.Data = trg.DATA

    AND src.[Time] = trg.[Time]

    WHEN NOT MATCHED

    THEN INSERT ([Count],[Time],Data)

    VALUES (1,src.[Time], src.Data)

    WHEN MATCHED

    THEN UPDATE SET trg.[Count]=trg.[Count]+1

    ;

    COMMIT TRAN

    I would also add the following index to assist the statement:

    CREATE INDEX idx1 ON AnalysisLog ([Time],Data) INCLUDE (Count)

    On Query 3 I see a you are not getting any data returned. If this is typical activity, I would want to know if there has maybe been a logic change elsewhere in the system that is possibly rendering this query obsolete (in it's current state). If not you could replace the statement with the following:

    SELECT ROW_NUMBER() OVER (ORDER BY P.PostID) AS IndexID,

    P.PostID

    FROM cs_Posts P

    WHERE P.IsApproved = 1

    AND P.PostLevel > 1

    AND P.ApplicationPostType <> 16

    AND P.SectionID = @SectionID

    AND P.PostName = @PostName

    ORDER BY P.PostDate

    You could take into consideration if the temp table is necessary at all, removing that if possible. It may also be possible to perform some index consolidation, changing the index that provides a seek on SecntionID and PostName to become a covering index for this query. Maybe something like:

    CREATE INDEX idx ON cs_Posts (PostName,SectionId,IsApproved) INCLUDE (PostLevel, ApplicationPostType, PostId)

    This is going on the premise that the three key columns will be highly selective (if not unique) and the rest being added as includes to cover the query. Not knowing the table structure and meaning of the columns restricts this to only a possibility though.

    For Query 4, it may be an idea to reduce the columns to an absolute necessity. Then I would look into the LOB reads - what is this actually achieving (if anything). The join syntax works, but is deprecated and should be changed to an INNER JOIN.

    Regards,

    WilliamD