Momentary index corruption, will turning off SMB2 fix this (on Windows 7 and Windows 2008 R2)?

[D44z]

We are getting strange corrupt index error messages mostly in a function called from the SELECT field list of a query. I know it is a "no no" performance wise to call user defined functions within a SELECT field list, but this is long running / existing code doing this. When a user receives this error, every single time so far, they are able to simply re-run that same code without the error. So these corrupt index errors seem to be happening only momentarily / seem to Not be actual true index corruption issues.

I have read a lot about how SMB2 running in a Windows 7 and Windows 2008 Server environment can cause index corruption. So my 1st question is, could SMB2 also be the cause of my above described 'momentary' index corruption?

My 2nd question, if we did turn off SMB2, would this adversely affect MS SQL Server 2008 (running on that same Windows 2008 Server)?

 

[SytzedeBoer]

Apart from SMB2, also check the AntiVirus system
What happens if you exclude your App folder

 

[Olaf Doschke]

SMB2 means oplocks and causes trouble updating data and indexes. But you have a temporary problem when reading the index, that vanishes. Seems like a caching issue and oplocks is a caching mechanism in the end. So it could be, you're lucky the index updates still work in the end, but temporarily you see a tag only half cached for example, which causes its seeming corruption.

What error do you get? Are you doing anything unusual, eg copy CDX files, REINDEX or anything influencing the indexes during working hours other than just modifying the data? Is there anything special about the indexes, eg expressions using user defined functions?

Bye, Olaf.

 

[Mike Lewis]

I know it is a "no no" performance wise to call user defined functions within a SELECT field list,

This mihgt not be directly related to your problem, but let me set you straight on the above statement.

It's not a performance issue. It's more a question of what is supported and what isn't. In general, it's OK to include UDFS in SELECT statements, provided the function does not alter the data environment in any way. By "alter the data environment", I mean open or close tables, change the work area, move any record pointers, change the index order, set or clear a filter, or similar data-related things. If you do any of those things within the function, the results are unpredictable. That's true even if you carefully restore everything to the state it was in at the start of the function.

If your UDF simply operates on memory variables, it should be perfectly safe.

Given that your code has been running properly for a long time, this is probably not the cause of your problem. But it would be worth taking a moment to check.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I disagree a bit. You can do anything of the above in tables and work areas not related with the queried tables. Often enough it's a sign you should alter the query, though, and functions making sense are in general in the category, which Mike described, working on memory variables only. But that can include field values passed in as parameters. In the same way you can write PADR(REDUCE(FIRSTNAME+" "+LASTNAME),50) AS FULLNAME you can also write FULLNAME(FIRSTNAME,LASTNAME) AS FULLNAME and write the concatenation of name fields in a function, which might also add in titles or put together more complex names.

The performance hit is with such function calls in join conditions or where clauses, as you hinder rushmore optimization on the expressions used in the functions that way, rushmore can't possibly look into your function and see what index would be usable to optimize it. And the major disadvantage is, that you have a hard time migrating such queries into other sql dialects when migrating data to another database. But that's also true for simpler expressions.

It's not a no go to compute anything in the field list part of SQL. MSSQL's T-SQL also has functions usable in queries, last not least CASE like foxpros IIF or ICASE, so it's not forbidden, also user defined functions and stored procedures exist in database servers and also are eligible to be used, that's what they are for, not only instead of queries, but also inside queries.

As you say your code uses indexes within functions you call from the query. If that's indexes of the table in query you're doing what Mike would avoid. That it worked for years shows it's not necessarily a strict rule to avoid, but it can interfere with rushmores index usage, it can interfere with order of records processed and you might cause a hiccup like an infinite loop when jumping around with SEEKS. Anyway, you'd most probably have the same problem, if that code would be expressions directly embedded in the query and not separate functions.

Bye, Olaf.

 

[Mike Lewis]

Olad, you're right that you can reference fields within a UDF. What I meant was that you cannot change anything data-related in a UDF. So, you can call functions like ALIAS() and DBF(), but you can't SELECT another work area or open a table, not can you update the contents of a field. (To be more exact, you can do all those things - you won't get a run-time error - but the results will almost certainly not be what you expect.)

As you say, this applies to UDFs called from anywhwre in a SELECT - not just in the field list.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I certainly agree to that. Even just repositioning with SEEK in some query related table would not be something I'd do. It might work well, but if a SEEK is done in assumtion it'll always find a record this is something that can work for years and then break on an unexpected index error.

Bye, Olaf.

 

[D44z]

Thank you for all of your feedback so far.

I had the antivirus scanning turned off from running in our application data folder a few months ago, and I have asked the admin to please make sure it is still turned off. Thank you, SytzedeBoer.

The specific error message we're receiving:

​

Index file "f:\ems_pay\data\ems_post.cdx" tag "Paycodev" is corrupted. Please rebuild it.

That "Paycodev" tag is on expression of VAL(PayCode) - PayCode is C(4) - so nothing special.
We re-index only once a week at the end of the day when all users are of course off the system.
The function which errors out is like I mentioned, called within the SELECT statement, and is itself another query (so there are No changing of record pointers or updates within the function).

 

[Mike Lewis]

Have you checked that you can access the index other than from your SELECT statement? For example, open the table, SET ORDER to the index, and then do a BROWSE or a LIST. That will at least tell you whether the index is at fault, or whether the problem is somewhere in the SELECT.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

As you already said the same query works again after a case with an error, what Mike suggest will not reveal much.

But perhaps your idea of corrupt indexes is wrong. An index can work for many values you look up via it and only fail for some. I once had an index broken on some IDs and you could verify that reproducable by first SELECT DISTINCT ID from table and then iterating all the different IDs and SEEK them. Some values caused an error, not all. That's nopt astonishing, if you know an index is a binary tree. A tree structure can be broken in one branch and the rest of the tree can still be intact.

That means the same query with different parameterizations can work in some and not other cases. What I would really accept as a temporary error is the exact same query touching the exact same records through the exact same parameterization yielding the exact same result, of course excluding the error.

And it's also a misunderstanding a REINDEX rebuilds the index full. It does not. DELETE TAG ALL and rebuild indexes via INDEX ON and ALTER TABLE (the only way to create primary indexes).

Bye, Olaf.

 

[D44z]

Yes, I did try all of those rudimentary checks more than once immediately the error (open with ORDER to that index, SEEK values on the expression, BROWSE FOR the expression, SELECT WHERE based on the expression). None of those checks failed, like Olaf expected, as was I, considering that the same functionality would run without an issue the very next moment.

Also, Olaf, thank you for clearing up that misunderstanding about REINDEX fully rebuilding the indexes. I am going to change our weekly process to DELETE TAG ALL and INDEX ON for all the indexes, so the indexes are truly, fully rebuilt.

I also tried your suggestion, Olaf, of getting unique values of the index expression and then iterating thru each one and doing a seek with the value - and it ran all the way thru with No errors and No failed SEEKs.

 

[Olaf Doschke]

> it ran all the way thru with No errors and No failed SEEKs.
If that doesn't prrove the index is fully functional, I don't know.

All I can say is, this is the first time I hear about a temporary index corruption and the only thing I can think of is any current change of the cdx hanging somewhere in an uncommitted cache temporarily.

Bye, Olaf.

 

[D44z]

You are the Man, Olaf!

A slight variation/addition to that concept, that of querying for each unique value, finally found me an errant record.

I added in the the key unique field into the original group by so that I grouped / got unique instances and queried by the unique id (check#) plus val(paycode) - this way specific instance each VAL(paycode) could be verified. Sure enough, in unique instance# 6123 of 4044294, I got the error message!

You are awesome, Olaf!

Thanks for all your help (SytzedeBoer and Mike aslo)!


Dan Ross
Programmer Analyst
Consultant/Programmer
Dans4444world@gmail.com