You add the WITH (NOLOCK) to the end of the table definition in the query:  

select m.addressbook,m.firstname,si.rows as 'Row_Count' from tblmembers m with (nolock),
sysobjects so with (nolock),sysindexes si with (nolock)  where si.id = so.id and si.indid in(0,1) and so.type = 'U' and so.name in
(select cast(addressbook as nvarchar(50)) from tblmembers)
and so.name=cast(m.addressbook as nvarchar(50)) and si.rows>=3500 and m.isactive=1

Hi dear friend, when i incorporated the query given by you as shown below, am getting errors..

select d.firstname,d.lastname,d.emailaddress,d.phone,d.Row_Count,d.optouts,
count(a.addressbook) as 'invalid',d.row_count-(d.optouts+count(a.addressbook)) as 'valid' from tblusercategory a,

(select d.addressbook,d.firstname,d.lastname,d.emailaddress,d.Row_Count,d.phone,
count(a.addressbook) as 'optouts' from tblusercategory a,

(select m.addressbook, m.firstname, m.lastname, m.emailaddress, sum(rows) as rows, m.phone
from sys.indexes si
join sys.objects so on si.[object_id] = so.[object_id]
                      and so.type_desc = 'user_table'
join sys.partitions sp on sp.[object_id] = so.[object_id]
                      and sp.index_id = si.index_id
                      and si.index_id in (0,1)
join dbo.tblmembers m on cast(m.addressbook as nvarchar) = so.[name] and m.isactive = 1
group by m.addressbook, m.firstname, m.lastname, m.emailaddress, m.phone
having sum(rows) >= 3500
)

as d where d.addressbook*=a.addressbook and a.categoryid in

(select id from tblcategorylist where categoryname='opt-out' and addressbook in
( select m.addressbook
from sys.indexes si
join sys.objects so on si.[object_id] = so.[object_id]
                      and so.type_desc = 'user_table'
join sys.partitions sp on sp.[object_id] = so.[object_id]
                      and sp.index_id = si.index_id
                      and si.index_id in (0,1)
join dbo.tblmembers m on cast(m.addressbook as nvarchar) = so.[name] and m.isactive = 1
group by m.addressbook
having sum(rows) >= 3500
))
 
group by d.addressbook,d.firstname,d.lastname,d.emailaddress,d.Row_Count,d.phone)
as d where d.addressbook*=a.addressbook and a.categoryid in

(select id from tblcategorylist where categoryname='invalid emailaddress' and addressbook in
(select m.addressbook
from sys.indexes si
join sys.objects so on si.[object_id] = so.[object_id]
                      and so.type_desc = 'user_table'
join sys.partitions sp on sp.[object_id] = so.[object_id]
                      and sp.index_id = si.index_id
                      and si.index_id in (0,1)
join dbo.tblmembers m on cast(m.addressbook as nvarchar) = so.[name] and m.isactive = 1
group by m.addressbook
having sum(rows) >= 3500
))

group by d.firstname,d.lastname,d.emailaddress,d.Row_Count,d.phone,d.optouts
order by valid desc

*************************************************************************************************

i removed the order by clause from the query u have given since its showing errors.. but i couldnt eliminate the errors given below 

Msg 1016, Level 15, State 3, Line 19
Outer join operators cannot be specified in a query containing joined tables.
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'group'.
Msg 156, Level 15, State 1, Line 50
Incorrect syntax near the keyword 'group'.

This query is not what was originally provided.... I'll need a schema.

 Also, this line uses deprecated join syntax and should be fixed

 as d where d.addressbook*=a.addressbook and a.categoryid in

 Hi, am back.. i think the entire problem can be solved if the deprecated join is fixed.. And the schema is given below:

tblusercategory : ID (int), addressbook(int)(pk), categoryID (int)(pk), userID(int) (pk)

tblcategorylist : ID (int), addressbook(int), categoryName(nvarchar), dataEntered(smalldatetime), categoryDesc (nvarchar)

tblmembers : addressbook(int)(PK), emailaddress(nvarchar), firstname(nvarchar), lastname(nvarchar), phone(nvarchar) , isActive(bit), hasAddressbook(bit) are some of the fields..

only addressbook is  common..

Hi

I just don't have enough understanding of what you fundamentally need.  The original query I provided seemed to take care of your original problem of finding addressbook entries with > 3500 rows.  I don't what the massive query you pasted in is trying to do.  I will need sample dummy data inserts and what the expected output should be.   That old style join needs to be ditched but I don't know what the query is trying to accomplish. 

Hi.. let me explain my requirement.. i need to display firstname, lastname, emailaddress from tblmembers, si.rows as 'Row_Count', optouts,
count(addressbook) as 'invalid', row_count-(optouts+count(addressbook)) as 'valid' from tblusercategory


(select m.firstname,m.lastname,m.emailaddress,si.rows as 'Row_Count',m.phone from tblmembers m,
sysobjects so,sysindexes si where si.id = so.id and si.indid in(0,1) and so.type = 'U' and so.name in
(select cast(addressbook as nvarchar(50)) from tblmembers)
and so.name=cast(m.addressbook as nvarchar(50)) and si.rows>=3500 and m.isactive=1)

firstname, lastname, emailaddress, Row_Count are displayed by executing the above query.

along with this, i need to display optouts, valid and invalid..

to get optouts, first getting the id which has categoryname='opt-out. see the below query for that.

(select id from tblcategorylist where categoryname='opt-out' and addressbook in
(select m.addressbook from tblmembers m,sysobjects so, sysindexes si where si.id = so.id
and si.indid in(0,1) and so.type = 'U'and so.name in
(select cast(addressbook as nvarchar(50)) from tblmembers)
and so.name=cast(m.addressbook as nvarchar(50)) and si.rows>=3500 and m.isactive=1))

Then giving this id from the above query as categoryid to tblusercategory
count(a.addressbook) as 'optouts' from tblusercategory a

the same process is repeated for getting 'invalid'. only diference is
giving categoryname='invalidemailaddress' in the above given query.

to get the valid: row_count-(optouts+count(addressbook)) as 'valid'. To display all these fields depricated join is used. This is time consuming..

Could u please figure out how to display all these fields without the deprecated join... Thanks..

 Hi.. u can use any method including stored procedures, temp tables etc to accomplish the above scenario.. u have absolute freedom to choose your method.. thanks a lot..

Hi my frnd, finally i was able to find a solution for the query time out issue.

i used a temp table and then i called 3 updates to update 3 fields i required. and put everything in a stored procedure.. now its working fine. Thanks a lot my dear for encouraging me and providing me the required tips.. Thanks again for spending some time for this issue..

GO
select m.addressbook, m.firstname, m.lastname, m.emailaddress, m.phone, sum(rows) as 'Row_Count',
sum(0) as 'optouts', sum(0) as 'invalid',sum(0) as 'valid'
into #temp1
from sys.indexes si
join sys.objects so on si.[object_id] = so.[object_id]
                      and so.type_desc = 'user_table'
join sys.partitions sp on sp.[object_id] = so.[object_id]
                      and sp.index_id = si.index_id
                      and si.index_id in (0,1)
join dbo.tblmembers m on cast(m.addressbook as nvarchar) = so.[name] and m.isactive = 1
group by m.addressbook, m.firstname, m.lastname, m.emailaddress, m.phone
having sum(rows) >= 3500

update #temp1 set #temp1.optouts=DT.optouts
from (select t.addressbook, count(a.addressbook) as 'optouts' from tblusercategory a
    right join #temp1 t on t.addressbook = a.addressbook and a.categoryid in
    (select id from tblcategorylist where categoryname='opt-out' and addressbook in
    (select addressbook from #temp1))
    group by t.addressbook) DT
where #temp1.addressbook = DT.addressbook

update #temp1 set #temp1.invalid=DT.invalid
from (select t.addressbook, count(a.addressbook) as 'invalid' from tblusercategory a
    right join #temp1 t on t.addressbook = a.addressbook and a.categoryid in
    (select id from tblcategorylist where categoryname='invalid emailaddress' and addressbook in
    (select addressbook from #temp1))
    group by t.addressbook) DT
where #temp1.addressbook = DT.addressbook

update #temp1 set #temp1.valid = #temp1.Row_Count -(#temp1.invalid + #temp1.optouts)

select firstname, lastname, emailaddress, phone, Row_Count, optouts, invalid, valid from #temp1 order by addressbook
drop table #temp1
GO