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?
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
