The excution plan is still showing an index scan in 2005 vs a seek in 2000. 🙁 It's also got an estimated row count in the 100,000s where in 2000...

Okay...I change those two views as per Matt's suggestion. It didn't help. 🙁

CPU Utlization was still very high and the query was very slow to return.

Jessica

Hi Matt,

We are getting there.

Now I get this error:

Msg 207, Level 16, State 1, Procedure vwMinHTOXLinc, Line 32

Invalid column name 'F_K_HTXTITL_HTXLINC'.

Msg 207, Level 16, State 1, Procedure vwMinHTOXLinc, Line 35

Invalid...

Hi Matt,

I tried yours...but it complained.

First about the ) at the end of

(Select Min(DHL_LINC_NBR) as MinDlincNBR FROM ALTA_Staging..DB_HTXLINC group by F_K_HTXTITL_HTXLINC) HTXLINCB on HTXLINCB.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC).

I removed that...but then...

Could you provide an example on what you mean by getting the select out into a derived table?

Thanks!!!

vwMinLTOXHTOXLinc:

Select TITLE_REFRNC_NBR,

LINC_NBR,

RIGHTS_IND,

NON_PATENT_IND,

MUNC_CODE,

PROPRTY_PARCEL_ID,

LincCount,

'LTOX' as LTOXHTOX

from vwMinLTOXLINC

union all

SelectTITLE_REFRNC_NBR,

LINC_NBR,

RIGHTS_IND,

NON_PATENT_IND,

MUNC_CODE,

PROPRTY_PARCEL_ID,

LincCount,

'HTOX' as LTOXHTOX

from vwMinHTOXLINC

vwMinHTOXLINC:

Select DHT_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,

HTXLINC.DHL_LINC_NBR as LINC_NBR,

DHL_RIGHTS_IND as RIGHTS_IND,

DPR_NON_PATENT_IND as NON_PATENT_IND,

DPR_MUNC_CODE MUNC_CODE,

DPR_PROPRTY_PARCEL_ID PROPRTY_PARCEL_ID,

(Select Count(*) from ALTA_Staging..DB_HTXLINC HTXLINCA

WHERE HTXLINCA.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC)...

On the DBs being used in this query, auto update and auto create statistics is enabled.

Two days ago we rebuilt all indexes in all tables using the maintenance plan. Would that not have created statistics for us?

Also, the specific tables where SQL SErver is scanning...

I ran dbcc updateusage on the two databases accessed in the query. No impact. The query continues to run high and CPU utlization is excessive while the query runs.

We ran full backups of the database in 2000 and then recovered those backups into 2005.

Got this from another DBA:

The problem appears to be that the tables DB_HTXTITL and DB_LTXTITL are scanning an index instead of seeking on based on the DRE_TITLE_REFRNC_NBR column of the...

Sorry...yes....the load is the same.

They are seperate boxes.

The 2005 box is supposedly much bigger and faster. More ram and faster processeors.

Thanks.

Thanks everyone for the great suggestions and explanations!!! We willl likely be going with:

Delete from FT

WHERE [Text] IS NULL

AND NOT EXISTS (

SELECT *

FROM FTR

1. Index and PK on Polygon Table:

ALTER TABLE [dbo].[Polygon] ADD

 CONSTRAINT [DF_Polygon_ISLAND] DEFAULT (0) FOR [ISLAND],

 CONSTRAINT [PK_Polygon] PRIMARY KEY  NONCLUSTERED

 (

  [MSLINK]

&nbsp WITH  FILLFACTOR =...