Hi all,
Here is a brief description of a problem I encountered, and how I
found a work around after 3 long days.

I have a VB6 app that uses ADO and ODBC to get communicate with SQL
server 2000 (sp3, running in win2003).

Everything was running great for a few weeks, but one day an update
statement that used to work just stopped working. It was a simple
update of 1 field in a table (about 30 columns, about 20k records).
SQL server acutally hung while it waited for a response that never
came, and everyone else on the network was also locked out of sql
server. Everyone had to do a ctrl alt del to crash the programme.

Steps I took...
Rebuilt the database. Same.
Restored backup. Same.
Moved database to another server. Same.
Checked for viruses, that no updates had happened, memory checks and
so on.
Still no joy.
Someone then recommended tinkering with the indexes of the table in
question. Which I did.
I added indexes, removed them, and eventuall found a combination that
worked. And this is it.

I removed the primary key, and replaced it with a clustered index.
That's it. And now it all appears to be running OK.

WHY WHY WHY?
I just don't get it.
Have I found a bug in sql server or odbc or ado? Is it a known issue?
What is the impact of what I have done?

Thanks in advance for your comments, and I hope this solution saves
someone else 3 days of hell!

Tim

Tim (thew@ltons.freeserve.co.uk) writes:
> I have a VB6 app that uses ADO and ODBC to get communicate with SQL
> server 2000 (sp3, running in win2003).

You should be using the SQLOLEDB provider. The default provider, OLE DB
over ODBC may be good for data sources for which there is no targeted
OLE DB provider, but this is not the case for SQL Server. Not that this
has anything to do with the problem you are describing, but nevertheless
I like to point this out.

> Everything was running great for a few weeks, but one day an update
> statement that used to work just stopped working. It was a simple
> update of 1 field in a table (about 30 columns, about 20k records).
> SQL server acutally hung while it waited for a response that never
> came, and everyone else on the network was also locked out of sql
> server. Everyone had to do a ctrl alt del to crash the programme.
>
> Steps I took...
> Rebuilt the database. Same.
> Restored backup. Same.
> Moved database to another server. Same.
> Checked for viruses, that no updates had happened, memory checks and
> so on.
> Still no joy.
> Someone then recommended tinkering with the indexes of the table in
> question. Which I did.
> I added indexes, removed them, and eventuall found a combination that
> worked. And this is it.
>
> I removed the primary key, and replaced it with a clustered index.
> That's it. And now it all appears to be running OK.
>
> WHY WHY WHY?
> I just don't get it.
> Have I found a bug in sql server or odbc or ado? Is it a known issue?
> What is the impact of what I have done?

There is next to nothing of useful information to comment the actual
case, so I can only answer in general terms.

In general, one needs to understand there are few tools that are so
powerful to make things run really slow like a relational database engine.
All modern DBMS has a cost-based optimizer that seeks find the best
way to execute a query, and to make its decisions it uses some information
about the data. SQL Server maintains statistics about the data and how it
is distributed. Most queries can be executed in a number of ways, and
the optimizer tries to estimate the most effecient plan. Note that was
the best plan yesterday, may not be the best plan today, because data
has changed, for instance increased in size. While optimizers often do a
good job, they are estimates, and sometimes things can go seriously wrong.
So this could explain why your query worked fine one day and then was
out to lunch the next day.

Now, there is a whole lot of things you can do help the optimizer, and
the most important is to ensure that your tables properly indexed for
the queries you use. After all, if you are to access one single row in
a million-row tables without any index at all, there is no more effecient
plan than to scan all million rows.

So there is nothing magic going on here, and least of all of any bug.
And ODBC is completely innocent. If you submit your own UPDATE statement,
ADO is too. If you use the .Update method in ADO, which I recommend
against, ADO is the one that builds the UPDATE statement. But the major
area for concern is the database design. If one has no knowledge about
database design and no understanding about indexing, you are very likely
to run into performance problems sooner or later, as soon as you get any
volume in your database.

If you feel that you would like to learn something in this area,
attending a class on SQL Server performance may be a good idea.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp