SQL Server Performance

Select Query is very slow

Discussion in 'ALL SQL SERVER QUESTIONS' started by willington, Jan 3, 2013.

  1. willington New Member

    Hi All

    [SQL Server 2008]
    I have a table(DATALOG_4) with 10 Millions records. It contains 18 columns. The column DATE_TIME is a primary key column. We have created Non-Clustered Index on DATE_Time because we are using this column in WHERE Clause.

    The issue is, It takes more than 20 seconds for even after I created Non-Clustered index.

    We are using the following Query:

    SELECT DATE_TIME, [794] AS CT, [61] AS BID,
    ROW_NUMBER() OVER (PARTITION BY [61] ORDER BY DATE_TIME DESC) AS Row_No
    FROM DATALOG_4 (NOLOCK)
    WHERE
    DATE_TIME >= '2012-11-01 12:00:00' and DATE_TIME <= '2012-12-31 11:59:59'

    Please anyone suggest me, How to make the select Query fast?
  2. Shehap MVP, MCTS, MCITP SQL Server

    First welcome to Forums…

    I am not certainly sure about the design index you used for that T-SQL Query but if I am assuming the other select columns are static values as you sent so please try the below clustered index and then send to me the Query execution plan as XML file :

    CREATE Clustered index DATALOG_4_IX on DATALOG_4( DATE_TIME desc) with (Data_compression=page , fillfactor=80)

    Then we can decide later what other probable solutions that can be helpful for your case either schema partitioning , HW Spec s improvement for CPU and IO in particular

Share This Page