Thanks all for your reply.

I will check with senior person if we can remove unlimited auto-growth option and set something about 400-500 MB in place of 10%.

I would also...

Thanks for your prompt response. I understand that we can't limited to growth. But, this is how they implemented tempdb. According to technet.microsoft if we have 200 MB or more...

First i made changes to existing index but as due to business rule, i have rollbacked the changed and created new index similar to 14147.

Here are the defination of both index

CREATE NONCLUSTERED INDEX [AI_OperatorCBGEstimate_14947]

ON OperatorCBGEstimate ( DateEndedStandard,UpdOperation)

INCLUDE ( KeyInstn )

WHERE MostRecentSequence...

I have also noted one more thing.

I can't to modify existing index due to business rule. So, i havent used AI_OperatorCBGEstimate_14947. But i have created new index(AI_OperatorCBGEstimate_HAP) with the same...

ChrisM@Work (4/22/2014)

---

A little more info - this is the same question on MSDN:

[Quote]I have one simple query to optimize but somehow after creating couple of index this query is still...

Please find the updated sql plan.

Thanks Scott and Lynn for your inputs.

As suggested by Scott i have made changes in existing index with filtered index (suggested by Lynn). And i am able to bring the...

GilaMonster (4/16/2014)

---

The query is reading just over 14 million rows and aggregating that down to 1099. If it's reading 14 million rows from a table (the rows which satisfy the...

Table contains total 14208765 rows but query itself fetching total 1099 rows.

ChrisM@Work (4/16/2014)

---

Did you look at the execution plans to see if the...

I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).

Does not make any difference.

Getting same result.

Please find the attached excel sheet for more information.

I have attached execution plan.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table...