Solved

Error Number 644 in SQL 6.5(SP4)

Posted on 1998-06-01
23
626 Views
Last Modified: 2010-05-18
To Speed up searching, I create indices on some varchar Field. The indices can be created successfully.
But when I update or insert records, the server will generate 644 Error!  It's caused by corrupted indices.
I had followed the steps in Book Online to find and fix the corrupted indices successfully.
But the Error 644 will appear again when I update or insert records into indexed tables.
I can't fix the corrupted indices everyday, just hope there is a way to fix the bug forever.

My Server is MS SQL 6.5(SP4), NT Server 4.0(SP3,I386), Please help me
0
Comment
Question by:threshold
  • 11
  • 10
  • +1
23 Comments
 

Expert Comment

by:KirkGray
ID: 1091389
Clustered or Non-clustered?  That may make a difference.....
0
 
LVL 2

Author Comment

by:threshold
ID: 1091390
It's non-clustered indices (indid=3).
And the table is not a system object (objid>100).
It's easy to  fix the corrupted index everytime. But it will be damaged again always when i update/insert records into it.
I want to know why the error 644 happens? Is it a Bug of MS SQL 6.5?
The Error 644 seems to generated by non-clustered indices on varchar fields. The ones on int fields are OK.
Is it caused by setting of character set (950)?
How do i fix it forever?
I have install SP3 for NT4.0 and SP4 for SQL 6.5. But the Error raised still.


0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091391
I have faced similar situations. If you have a look at your optimiser showplan for the update statement you are running,  see if it is a deferred update or an in-place update. Maybe you have page migration when you're doing the update. This involves a lot of data movement( you can avoid page chaining/migration by allocating max rows per page during data loads into tables and introducing fill factors during index creation. Did you check that the optimiser is really using your index or not. Is the index created on the first column_name in the where clause(chances of using the index you created increases for this case). If the showplan shows that the optimiser is not using your index, then you're unneccessarily hosing the system with that index over there.
In fact update insert performance are most often the best with no indexes. The indexes do work miracles for select queries. So if this table is more update, insert intensive take all indexes out, you'll have a lot better performance. This totally depends on how often you want the table to be updated/inserted into as against how many selects/day.

However if you see an output from the optimiser which looks like this:
SHOWPLAN 1:
STEP 1
The type of query is UPDATE
The update mode is direct
FROM TABLE
 then there might me some other reason for this error. Get back to me on this..but to start with try dropping all indexes on this table and doing the update. Unfortunately it is hard to have good performance for a table with heavy selects and updates.
0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091392
I also saw this relevant document on books online. If  what I suggested does not help you and the problems persist then maybe you can give it a try(especially since you have done the whole nine yards before).
Some SQL Server error messages specify a logical page number instead of the table or index name to which the page belongs. The following procedure shows how to determine the object to which a particular database page belongs.
Although you can use DBCC CHECKALLOC and DBCC CHECKDB to identify table names and index IDs, a quicker method is described below. Note, however, that this method tells you only the table or index associated with a particular page number.
Suppose you encounter the following error message:
Error 644, Severity 21, State 1
The non_clustered leaf row entry for page 1342 row 6 was not found in index page 944 indexid 3 database 'production'
 
The error message implies that a nonclustered index is corrupt, but the corresponding table name or index name is not given ¾ only a page number (944) and an index ID (3).
To determine which table or index is involved, follow these steps:
      1.      Log in as sa.
      2.      Determine the database ID (dbid) as follows:
select db_id('database_name')
      3.      Enable trace flag 3604 to allow DBCC output to appear on the server:
dbcc traceon(3604)
      4.      To display information about the page in question, use the DBCC PAGE statement as follows:
dbcc page(database_id, page_number)

Note  The DBCC PAGE statement is not a supported feature, so future compatibility is not assured. It is offered here only for the purpose of providing a faster method of associating a page with an object. Additional information about the output of DBCC PAGE is not available.

For example, assuming that the output from step 2 indicates that the database ID is 6, you can find information about page 944 (the index page indicated in the error message shown above) as follows. (The objid and indid shown in the boxes in the following example are used in steps 5 and 6, below.)
dbcc page (6, 944)
PAGE: Page not found in cache - read from disk.
BUFFER: Buffer header for buffer 0x2c4b30 page=0x540800 bdnew=0x0
bold=0x0 bhash=0x0 bnew=0x0 bold=0x0 bvirtpg=7092 bdbid=6
bpinproc=0 bkeep=0 bspid=0
bstat=0x0000 bpageno=0
PAGE HEADER: Page header for page 0x540800
objid=9051068
pageno=944 nextpg=0 prevpg=0 timestamp=0001
000c70f2
indid=3
nextrno=32 level=0  freeoff=52 minlen=7
page status bits: 0x2,
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
      5.      Translate the object ID (objid) in the PAGE HEADER section of the output into a table name:
use database_name
go
select object_name(9051068)
go
--------------
bad_table
      6.      If an index is applicable, translate the index ID (indid) into an index name:
use database_name
go
select name from sysindexes
where id = 9051068 and indid = 3
go
      7.      To determine the index type, refer to the following table:

      
      
Index Id      Meaning
0      Table data
1      Clustered index
2 - 254       Nonclustered index
255      Text page

Because the DBCC PAGE output indicated that the indid is 3, the page belongs to a nonclustered index. (If the indid is 0, the page belongs to a table, and it does not belong to an index.)
      8.      Disable trace flag 3604:
dbcc traceoff(3604)
 
Especially since you say you have run the recreation of indexes this is possibly not gonna help you..but hey what the heck..just try. However I think my original answer is gonna take care of your problems.
0
 
LVL 2

Author Comment

by:threshold
ID: 1091393
Thank you for Your Replies:

My Table includes a int column and a varchar column that have indice.
The int column is used as a primary key, I need it to be a unique key to identify rows.
The varchar column is used as a non-unique column, it contains important data (subject).
I need the varchar column to be searched.
So, the column need to be indexed for fast selecting and can be updated sometimes.
For searching speed,I can't drop the index . But it causes the 644 Error when i update/insert it.
It's the easy update statement to generate 644 Error:

    update Post set Subject='The Error 644' where ID=77

Maybe, I can drop the index before updating/inserting/deleting rows and recreate it after updating/inserting/deleting.
But, It seems to be a bad idea. It will reduce the speed of updating seriously.

How to solve the Error 644 forever?
Is there service pack N for MS SQL 6.5 to solve the problem?
Is there a same problem in Sybase Server?
Please save me from Hell...  thanks
0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091394
No this problem does not appear in Sybase. But whether you use Sybase or Oracle or MS SQL server..irrespective of the platform, it is very difficult to index a table with both heavy selects and updates.
Did you look at the showplan. If you post the showplanthen I can give more suggestions. You have to make this update a in-place update. There are at least 10 different things one can do after looking throught he showplan. How many rows are in this table. Is the select using this index. How many rows per page of this table. If you also look at the output for the dbcc 302,310 with 3604(for printing info on your screen) you will look at a lot more. If you haven't used these commands, it will take some time before you understand it, but it is not rocket science. since the column in question is a varchar datatype.. I think the page chaining is a distinct possibility. From the output of dbcc 302,310 you can tell how many rows are there per page.  Maybe you can provide a fillfactor when you reload the data.

However the first thing to do is to check whether your select statement is using your index or using a table scan. It does not neccessarily use your index even if you're selecting based on one field in the where condition. If the number of rows returned is greater than the number of index pages for the table, the amount of physical I/O increases if using the index, so the optimiser does a table scan in these cases. The 644 error is a generic error and this really is a bug. Ask Microsoft if they know about it. It should really not be the case. However I have seen thousands of locks on tables when an update takes place on similar tables. You also might wanna see the output of sp_lock as soon as you fire the update query.

More later..
Jit Biswas
0
 
LVL 2

Author Comment

by:threshold
ID: 1091395
I need the Table to be searched quickly, and updated sometime, the non-unique index is needed.
I can't drop it.
There is a way to fix the index in the Book Online, But, I can't do it everytime when I update the table.

0
 
LVL 2

Author Comment

by:threshold
ID: 1091396
Jbiswas, Thanks, I will try it and repost the showplan

0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091397
Yes run the showplan with noexec for an update query and a select query on the same table. Make sure the select is a realistic select, something you would use in the application. Also if you can run the dbcc 302 and dbcc 310 and post them maybe that will give a clearer picture. Also I wanted to ask you, did you try dropping the index and running the select. Is select performance really bad w/o the index?
0
 
LVL 2

Author Comment

by:threshold
ID: 1091398
With Your advice, I run the SQL statements below:

  set showplan on
  dbcc traceon(3604)
  dbcc traceon(306)
  dbcc traceon(310)
  dbcc page(7, 1399)
  select object_name(1625772849)
  select name from sysindexes
  where id = 1625772849 and indid = 2

then I get the Output:

  QUERY IS CONNECTED
   J_OPTIMIZE: Remaining vars=[0]
  NEW PLAN FOR ONEROW (total cost = 32):
  JPLAN (0x385e7fc) varno=0 indexid=1 totcost=32 pathtype=sclause
  class=onerow optype=SUBSTITUTE method=NESTED ITERATION outerrows=1
  rows=1 joinsel=1 lp=2 pp=2 cpages=2 ctotpages=8 corder=2 cstat=0x4
  maxpages=2 crows=1 cjoinsel=0
 
  TOTAL # COMBINATIONS: 0
  TOTAL # PERMUTATIONS: 0
  TOTAL # PLANS CONSIDERED: 0
 
  FINAL PLAN (total cost = 32, maxpages = 2):
  JPLAN (0x385e7fc) varno=0 indexid=1 totcost=32 pathtype=sclause
  class=onerow optype=SUBSTITUTE method=NESTED ITERATION outerrows=1
  rows=1 joinsel=1 lp=2 pp=2 cpages=2 ctotpages=8 corder=2 cstat=0x4
  maxpages=2 crows=1 cjoinsel=0
 
  STEP 1
  The type of query is SETON
  STEP 1
  The type of query is DBCC_CMD
  STEP 1
  The type of query is DBCC_CMD
  STEP 1
  The type of query is DBCC_CMD
  STEP 1
  The type of query is DBCC_CMD
  STEP 1
  The type of query is SELECT
  STEP 1
  The type of query is SELECT
  FROM TABLE
  sysindexes
  Nested iteration
  Using Clustered Index
  DBCC execution completed. If DBCC printed error messages, see your System Administrator.
  DBCC execution completed. If DBCC printed error messages, see your System Administrator.
  DBCC execution completed. If DBCC printed error messages, see your System Administrator.
 
  PAGE:
  Page found in cache.
 
  BUFFER:
  Buffer header for buffer 0x11d0e80
    page=0x157e000 bdnew=0x11d0e80 bdold=0x11d0e80 bhash=0x0
  bnew=0x11d1360
    bold=0x11d2f20 bvirtpg=33555831 bdbid=7 bpinproc=0 bkeep=0
  bspid=0
    bstat=0x1004   bpageno=1399
 
  PAGE HEADER:
  Page header for page 0x157e000
  pageno=1399 nextpg=2176 prevpg=1398 objid=1625772849 timestamp=0001
  00255c85
  nextrno=1997 level=0 indid=2  freeoff=2029 minlen=7
  page status bits: 0x2
 
  DBCC execution completed. If DBCC printed error messages, see your System Administrator.
  ------------------------------
  Segment                        
  (1 row(s) affected)
  name                          
  ------------------------------
  IX_Segment_Search1            
  (1 row(s) affected)

I know that My Non-clustered index 'IX_Segment_Search1' is corrupted, and I can rebuild it.
But, It will be corrupted again when I update the table 'Segment'. I have try to set the fill factor=0%, 50%...

It's hard to understand what the SHOWPLAN means. Please, give me a hit to solve the Error 644. thanks
0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091399
Ok, looks like you possibly did not understand what I asked you to give me. Why are you doing a showplan on sysindexes????? And also why the dbcc 306??? Just do this...and print the output.

This is what you want to run.
1.
set showplan, noexec on
dbcc traceon(302,3604)
select query that you perform on the 'segment' table

2.
set showplan, noexec on
dbcc traceon(302,3604)
update query that you perform on the 'segment' table

Also, did you try dropping the nonclustered index and seeing the performance for both select and update. Perhaps you want to time it, so that you have some way of comparing.

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:threshold
ID: 1091400
Ok, I got it. I have a mistake with 306. It should be 310.

So...
dbcc traceon(302,310,3604)
select * from Segment where Search1 like '%Meta%'

I got it below:
 
*******************************
Leaving q_init_sclause() for table 'Segment' (varno 0).
The table has 113181 rows and 3061 pages.
Cheapest index is index 0, costing 3061 pages per scan.
 
QUERY IS CONNECTED
 
J_OPTIMIZE: Remaining vars=[0]
 
permutation: 0
 
NEW PLAN #1 (total cost = 48976):
JPLAN (0x3a5f49c) varno=0 indexid=0 totcost=48976 pathtype=sclause
class=join optype=? method=NESTED ITERATION outerrows=1 rows=113181
joinsel=1 lp=3061 pp=3061 cpages=3061 ctotpages=3061 corder=1
cstat=0x20 maxpages=3061 crows=113181 cjoinsel=1
 
 
BEST PERMUTATION (total cost = 48976):
JPLAN (0x3a5e7fc) varno=0 indexid=0 totcost=48976 pathtype=sclause
class=join optype=? method=NESTED ITERATION outerrows=1 rows=113181
joinsel=1 lp=3061 pp=3061 cpages=3061 ctotpages=3061 corder=1
cstat=0x20 maxpages=3061 crows=113181 cjoinsel=1
 
 
 
 
TOTAL # COMBINATIONS: 1
TOTAL # PERMUTATIONS: 1
TOTAL # PLANS CONSIDERED: 1
 
FINAL PLAN (total cost = 48976, maxpages = 3061):
JPLAN (0x3a5e7fc) varno=0 indexid=0 totcost=48976 pathtype=sclause
class=join optype=SUBSTITUTE method=NESTED ITERATION outerrows=1
rows=113181 joinsel=1 lp=3061 pp=3061 cpages=3061 ctotpages=3061
corder=1 cstat=0x20 maxpages=3061 crows=113181 cjoinsel=1
 
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
Type Book     SubID  P1   P2   Search1                                                                      Search2                                                                                                                                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------------------------
200  60          1           1             Meta-ethnography..synthesizing qualitative studies  Noblit, George W..Hare, R. Dwight
200  5372      1           1             [Metal structure buildings]..                                      Tajima.
200  5766      1           1             Structural design in Metals..                                     Williams, Clifford D.¡AHarris, Ernest C..
210  4579      1                          Louisville.Reynolds Metals Co.                                (null)                                                                                                                                                                                                                                                          
606  5372      1                          Metal¡ÐBuildings.                                                    (null)                                                                                                                                                                                                                                                          

And I executed the statement below:
update Segment set Search1='Metal¡ÐBuildings. 1997' where Type='200' and Book=5372 and SubID=1

I got:
*******************************
Leaving q_init_sclause() for table 'Segment' (varno 1).
The table has 113181 rows and 3061 pages.
Cheapest index is index 0, costing 3061 pages per scan.
 
*******************************
Entering q_score_index() for table 'Segment' (varno 1).
The table has 113181 rows and 3061 pages.
Scoring the search clause:
AND (!:0x1d08872)  (andstat:0xa)
  EQ (L:0x1c8cfae)  ( rsltype(0x38):INT4 rsllen:4 rslprec:10
  rslscale:0 opstat:0x0)
    VAR (L:0x1c8cf62)  (varname:Book varno:1 colid:2
    coltype(0x38):INT4 colen:4 coloff:5 colprec:10 colscale:0
    vartypeid:7 varnext:1d08804 varusecnt:1 varlevel:0 varsubq:0)
    INT4 (R:0x1c8cf94)  (left:0x1c8cf9c len:4 maxlen:4 prec:4 scale:0
    value:5372)
  AND (R:0x1c8cfc2)  (andstat:0xa)
    EQ (L:0x1d08852)  ( rsltype(0x38):INT4 rsllen:4 rslprec:10
    rslscale:0 opstat:0x0)
      VAR (L:0x1d08804)  (varname:SubID varno:1 colid:3
      coltype(0x38):INT4 colen:4 coloff:9 colprec:10 colscale:0
      vartypeid:7 varnext:1c8cefa varusecnt:1 varlevel:0 varsubq:0)
      INT4 (R:0x1d08838)  (left:0x1d08840 len:4 maxlen:4 prec:1
      scale:0 value:1)
    AND (R:0x1d08866)  (andstat:0xa)
      EQ (L:0x1c8cf4a)  ( rsltype(0x2f):CHAR rsllen:255 rslprec:3
      rslscale:0 opstat:0x0)
        VAR (L:0x1c8cefa)  (varname:Type varno:1 colid:1
        coltype(0x2f):CHAR colen:3 coloff:2 colprec:3 colscale:0
        vartypeid:1 varusecnt:1 varlevel:0 varsubq:0)
        CHAR (R:0x1c8cf30)  (left:0x1c8cf38 len:3 maxlen:3 value:
        '200')
 
Unique clustered index found--return rows 1 pages 2
Cheapest index is index 1, costing 2 pages and generating 1 rows per
scan.
Search argument selectivity is 0.000009.
*******************************
 
QUERY IS CONNECTED
 
J_OPTIMIZE: Remaining vars=[1]
 
NEW PLAN FOR ONEROW (total cost = 32):
JPLAN (0x3a5e7fc) varno=1 indexid=1 totcost=32 pathtype=sclause
class=onerow optype=SUBSTITUTE method=NESTED ITERATION outerrows=1
rows=1 joinsel=1 lp=2 pp=2 cpages=2 ctotpages=3061 corder=1 cstat=0x4
maxpages=2 matcost=78819229775715026 matpages=18941096 crows=1
cjoinsel=0
 
 
 
 
 
TOTAL # COMBINATIONS: 0
TOTAL # PERMUTATIONS: 0
TOTAL # PLANS CONSIDERED: 0
 
FINAL PLAN (total cost = 32, maxpages = 2):
JPLAN (0x3a5e7fc) varno=1 indexid=1 totcost=32 pathtype=sclause
class=onerow optype=SUBSTITUTE method=NESTED ITERATION outerrows=1
rows=1 joinsel=1 lp=2 pp=2 cpages=2 ctotpages=3061 corder=1 cstat=0x4
maxpages=2 matcost=78819229775715026 matpages=18941096 crows=1
cjoinsel=0
 
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
Msg 644, Level 21, State 1
 The non_clustered leaf row entry for page 13972 row 5 was not found in index page 2511 indexid 2 database 'Library'

I can't drop the index, the time used by query is very much without the index.

Jjbiswas, Thank You very much.
0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091401
You did it right this time but you forgot some critical info.
1. Please execute the following for the same queries posted above
(exactly)
set showplan, noexec on
select * from Segment where Search1 like '%Meta%'

AND

set showplan, noexec on
update Segment set Search1='Metal¡ÐBuildings. 1997' where Type='200' and Book=5372 and SubID=1

Please post these outputs ..it would definitely help
AND GIVE ME THE ROWS ON WHICH YOU HAVE THE CLUSTERED INDEX AND THE NONCLUSTERED INDEX.....V.V.IMP

0
 
LVL 2

Author Comment

by:threshold
ID: 1091402
The table 'Segment' is difined by:
CREATE TABLE dbo.Segment (
      Type char (3) NOT NULL ,
      Book int NOT NULL ,
      SubID int NOT NULL ,
      P1 char (1) NOT NULL ,
      P2 char (1) NOT NULL ,
      Search1 varchar (255) NULL ,
      Search2 varchar (255) NULL ,
      CONSTRAINT PK___30__13 PRIMARY KEY  CLUSTERED
      (
            Type,
            Book,
            SubID
      ) WITH  FILLFACTOR = 50
)
CREATE  INDEX IX_Segment_Search1 ON dbo.Segment(Search1) WITH  FILLFACTOR = 50

The table size is 113181 rows in 6122 KB

The output of
 " set showplan, noexec on     select * from Segment where Search1 like '%Meta%'  "
is same as 'dbcc traceon(3604,302,310)

but there is little difference in some value with 'update'
  set showplan,noexec on
  update Segment set Search1='Metal¡ÐBuildings. 1997' where Type='200' and Book=5372 and SubID=1
 
*******************************
Leaving q_init_sclause() for table 'Segment' (varno 1).
The table has 113181 rows and 3061 pages.
Cheapest index is index 0, costing 3061 pages per scan.
 
*******************************
Entering q_score_index() for table 'Segment' (varno 1).
The table has 113181 rows and 3061 pages.
Scoring the search clause:
AND (!:0x1c3c980)  (andstat:0xa)
  EQ (L:0x1c3c8ec)  ( rsltype(0x38):INT4 rsllen:4 rslprec:10
  rslscale:0 opstat:0x0)
    VAR (L:0x1c3c8a0)  (varname:Book varno:1 colid:2
    coltype(0x38):INT4 colen:4 coloff:5 colprec:10 colscale:0
    vartypeid:7 varnext:1c3c912 varusecnt:1 varlevel:0 varsubq:0)
    INT4 (R:0x1c3c8d2)  (left:0x1c3c8da len:4 maxlen:4 prec:4 scale:0
    value:5372)
  AND (R:0x1c3c900)  (andstat:0xa)
    EQ (L:0x1c3c960)  ( rsltype(0x38):INT4 rsllen:4 rslprec:10
    rslscale:0 opstat:0x0)
      VAR (L:0x1c3c912)  (varname:SubID varno:1 colid:3
      coltype(0x38):INT4 colen:4 coloff:9 colprec:10 colscale:0
      vartypeid:7 varnext:1c3c838 varusecnt:1 varlevel:0 varsubq:0)
      INT4 (R:0x1c3c946)  (left:0x1c3c94e len:4 maxlen:4 prec:1
      scale:0 value:1)
    AND (R:0x1c3c974)  (andstat:0xa)
      EQ (L:0x1c3c888)  ( rsltype(0x2f):CHAR rsllen:255 rslprec:3
      rslscale:0 opstat:0x0)
        VAR (L:0x1c3c838)  (varname:Type varno:1 colid:1
        coltype(0x2f):CHAR colen:3 coloff:2 colprec:3 colscale:0
        vartypeid:1 varusecnt:1 varlevel:0 varsubq:0)
        CHAR (R:0x1c3c86e)  (left:0x1c3c876 len:3 maxlen:3 value:
        '200')
 
Unique clustered index found--return rows 1 pages 2
Cheapest index is index 1, costing 2 pages and generating 1 rows per
scan.
Search argument selectivity is 0.000009.
*******************************
 
QUERY IS CONNECTED
 
J_OPTIMIZE: Remaining vars=[1]
 
NEW PLAN FOR ONEROW (total cost = 32):
JPLAN (0x385e7fc) varno=1 indexid=1 totcost=32 pathtype=sclause
class=onerow optype=SUBSTITUTE method=NESTED ITERATION outerrows=1
rows=1 joinsel=1 lp=2 pp=2 cpages=2 ctotpages=3061 corder=1 cstat=0x4
maxpages=2 matcost=78819831071136466 matpages=18943752 crows=1
cjoinsel=0
 
TOTAL # COMBINATIONS: 0
TOTAL # PERMUTATIONS: 0
TOTAL # PLANS CONSIDERED: 0
 
FINAL PLAN (total cost = 32, maxpages = 2):
JPLAN (0x385e7fc) varno=1 indexid=1 totcost=32 pathtype=sclause
class=onerow optype=SUBSTITUTE method=NESTED ITERATION outerrows=1
rows=1 joinsel=1 lp=2 pp=2 cpages=2 ctotpages=3061 corder=1 cstat=0x4
maxpages=2 matcost=78819831071136466 matpages=18943752 crows=1
cjoinsel=0
 
STEP 1
The type of query is DBCC_CMD
STEP 1
The type of query is SETON
STEP 1
The type of query is UPDATE
The update mode is direct
FROM TABLE
Segment
Nested iteration
Using Clustered Index
TO TABLE
Segment

Please help me, thks
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1091403
Can I suggest a work around?  When you use a varchar field, SQL server does not know how many rows will fit on a page.  Extra column handling work and page shuffling take place. Now, as defective as SQL server is at times (sometimes I think it is really only a toy database, so we hang on for the next release!), it should not crap out your index on each update.  However, for faster updates and access, consider defining your varchar column as a fixed length character maximum sized field.  You have then traded off hard drive space for performance, but you've already traded off so much for SQL server, whats a few megabytes more between friends?  and it might stop your index corruption.
0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091404
Nooooooooooo............just do as I asked you to. The showplan output is not same as the dbcc output.

DO NOT RUN THE DBCC(302,310,3604)...Pleaseeeeeeeeeee.

Just run the following
1.set showplan, noexec on
select * from Segment where Search1 like '%Meta%'

AND

2. set showplan, noexec on
update Segment set Search1='Metal¡ÐBuildings. 1997' where Type='200' and Book=5372 and SubID=1

That's all. Perhaps you can use the suggestion made by cymbolic if you can change the datatype. If not then you need to read a bit, 'coz these techniques need understanding what you're doing. Anyways just post these results and then we can figure it out
0
 
LVL 2

Author Comment

by:threshold
ID: 1091405
You r right. It's really a bug.
Maybe, I should find the other ways to solve it (using char(n) or Sybase).
There is any terrible bugs like this in Sybase or Oracle?
BTW, Please check the 'Answer' checkbox. We should close this subject.
Thanks a lot. Good Day!
0
 
LVL 2

Accepted Solution

by:
jbiswas earned 200 total points
ID: 1091406
looks like you gave up on this one. From the dbcc outputs that you had posted it is apparent that it is using the clustered index for both the queries(select and update). I wanted to confirm it with the showplan output.Anyways..

No I have worked with both Sybase and Oracle and it does not have this kinda bugs. Only thing with Oracle is , they have made it unneccesarily cumbersome. If you're used to MS-SQL Server move over to Sybase, then you do not have to unlearn a lot. With Oracle you will have to learn to walk again..it has little or no similarity to MS SQL Server.
0
 
LVL 2

Author Comment

by:threshold
ID: 1091407
Thanks, I run it again.

With Selecting, the showplan is:
STEP 1
The type of query is SELECT
FROM TABLE
Segment
Nested iteration
Table Scan

With Updating, it is:
STEP 1
The type of query is UPDATE
The update mode is direct
FROM TABLE
Segment
Nested iteration
Using Clustered Index
TO TABLE
Segment

I can't see there is anything helpful to me.
Now, I just pray that the Sybase will be better.
0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091408
There you goooooooooooooooooooo...
wonderful!!!!!!!!!
The select is not even using the index you have . It does a Table scan!!!!!!!!!!! That means your index is not even being used here. Drop the nonclustered index!!!!!!!!
The update uses the clustered index, and the update mode is direct, but see if you can make this an in_place update. Direct updates are also expensive. To do this all you have to do is drop the clustered index. There is no advantage of having a clustered index if the select clause is not even using it.

All of the above is valid if and only if the query you executed is a true representation of what you're doing in realtime scenario.


0
 
LVL 2

Author Comment

by:threshold
ID: 1091409
Thanks, But how can I force it to use nonclustered index in selecting?
The nonclustered index is not useful! Why do we use indices?

Is There other Type of Query than 'Table scan'?

Thank you very much, I will drop the damn index.
0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091410
You can force the usage of an index by mentioning the index name beside the table name in the where clause.

A table scan is a sequential read of entries in a column..the other types of scanning are index scan, hash scan, partition scan.
0
 
LVL 2

Author Comment

by:threshold
ID: 1091411
I got it, Thanks....
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now