Index Corruption?

Question

Index Corruption?

tosscrosby

Hall of Fame

Points: 3140
More actions
October 28, 2004 at 12:06 pm

#86781

I have a select statement as follows on a table (indexes shown) which returns the following data:
Before index rebuild:

SELECT
K.PAGE_NUM,
K.LINE_NUM
FROM PS_PAY_LINE K, PS_PAY_EARNINGS L
WHERE K.EMPLID     = '110164'
AND K.EMPL_RCD = 0
AND K.COMPANY    = 'CGP'
AND K.PAYGROUP   = 'CGP'
AND K.PAY_END_DT = '10/24/04'
AND L.COMPANY=K.COMPANY
  AND L.PAYGROUP=K.PAYGROUP
AND L.PAY_END_DT=K.PAY_END_DT
AND L.OFF_CYCLE=K.OFF_CYCLE
AND L.PAGE_NUM=K.PAGE_NUM
AND L.LINE_NUM=K.LINE_NUM
AND K.MANUAL_CHECK = 'N'

Data Returned:
11                    1
107                  6

Indexes:

PSAPAY_EARNINGS          nonclustered located on PRIMARY              COMPANY, PAYGROUP, PAY_END_DT, OFF_CYCLE, PAGE_NUM, LINE_NUM, SEPCHK, ADDL_NBR, PAY_LINE_STATUS, OK_TO_PAY, SINGLE_CHECK_USE, EMPLID, EMPL_RCD, BENEFIT_RCD_NBR
PSBPAY_EARNINGS          nonclustered located on PRIMARY              EMPLID, COMPANY, PAYGROUP, PAY_END_DT, PAY_LINE_STATUS
PSEPAY_EARNINGS          nonclustered located on PRIMARY              FLSA_END_DT, EMPLID, PAYGROUP, COMPANY, EMPL_RCD, PAY_LINE_STATUS
PSFPAY_EARNINGS           nonclustered located on PRIMARY              COMPANY, PAYGROUP, PAY_END_DT, PAGE_NUM, LINE_NUM, SEPCHK, OK_TO_PAY, EMPLID, EMPL_RCD
PS_PAY_EARNINGS           clustered, unique located on PRIMARY       COMPANY, PAYGROUP, PAY_END_DT, OFF_CYCLE, PAGE_NUM, LINE_NUM, ADDL_NBR
After I issue a DBCC DBREINDEX (PS_PAY_EARNINGS, '')

the results returned for the same select, same indexes are:
107                  6
11                    1
How can this be? Are the statistics corrupted? The second result set is what was expected but i cannot figure out why the results would different?

Terry

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The only thing I can think off is that the statistic were updated and that the execution plan changed after the reindex because a better index was found.

I ran into a strange situation not unlike yours yesterday ::

I create a query, test it with a few parameters and it works great (1-2 ms each time), then 1 param change cause the query to take more than 2 secs. After playing around with the execution plan I try to run sp_updatestats and Voila 2 ms for that query too.

sunjiulu Ten Centuries Points: 1232 More actions · Answer 2

re-write your query and include an "order by " clause will guareentee the order of resultset.

after the index got rebuild, the order that records are returning can be changed because the b-tree will be re-structured.