Thanks all. I will reduce the max to around 13 gig. This server is only used for SQL. Nothing else runs on it.

These are my counters for SQl Server memory: both look the same

Target Server Memory:14377680

Total Server Memory:14377680

Should I be changing the max threshold?

I get data in seconds without the top operator. I will check out the blog. Its really frustating as i have tried rewriting it different ways, I updated stats with...

Yes I have updated the stats.

I did move the other two statements to the where clause. My only concern is that I have to force the index usage.. currently my clustered index is on LocationId,ProviderID,Network....

I added the index you suggested and there is a considerable improvement. But i have to force the query to use it, it uses the current index, if I drop...

We load the data into these tables every week with truncate data,create indexes and Update Stats. These are read only tables never updated. The updates happen in stage and we...

I saved the exec as sqlplan and Zipped it up and posted....

Thanks. Let me try that and will post.. I was finally able to find the attachement button to post the EXEC plan..please take a look at it.

Thanks. Yes I tired that and it worked.

create table #temp1(num int)-- cna uses a derived table too

insert into #temp1

SELECT COUNT(*) AS NUM

FROM table1

WHERE EmpTermDate IS NULL

UNION all

SELECT COUNT(*) AS NUM

FROM table2

WHERE EmpTermDate...

Thanks a lot for the info, I will try running the script.

Thanks for the info , I also wanted to know from performance and db size point of view. Which is more effecient.