Thanks a lot Jo Pattyn.  I really like the filtered index idea. Other thing struck in my mind is computed column.

However,This is a vendor specific database and we cant change...

Thank you Jeff and Frederico.

I didn't get it. the code doesn't...

In addition to, creating a non-clustered index on "first_nm" column, is there a way to make it run faster? can it done in small chunks ?

Regards,

Sam

Another question is, How to tell which tables needs FULLSCAN and which ones not. someetimes , we may or may not afford to run 100% on each and every statistics....

Thanks everyone. Till now, I was under the impression that it is only updating stats.

One question,  came to mind . What would be the Sampling percent used, if I don't...

If all of your code has NOLOCK - then enabling RCSI and not removing NOLOCK is just adding overhead for now useful purpose.  SQL Server will be maintaining the...

Thanks Scott. Some vendor apps are not supporting the partitioning. Any other options?

Thanks sir....

Read committed.

Just curious, actually, RCSI is also turned on the database. Why SQL Server is not showing it as "Read committed Snapshot Isolation" level?

Thanks,

Sam

Looking at first glance with lot of scans , I felt the same. Thanks for the pointers. Now, I am getting a doubt, are they even getting the desired output...

This is a different instance. Will create...

Two way street here, Sam.  You've not answered my question above.  Why is someone trying to Truncate a table while others are trying to SELECT from...

Thank you All.

Thank you Jeffery and Jeff. I got it. I can sense that I shouldn't get into all sort unnecessary things rather focus on the problem.

I have informed them to make...