MS SQL Server 2005 hang

Hi All,
We get very strange hang situation at our customers from time to time.
I execute very simple query like
insert into <table1>
select <columns> from <table2>
where <conditions>
Table <table1> has clustered index on float column.
Normally this query is executing, say, 2 minutes. But sometimes it
suddenly begins to hang for 2 hours and go to query timeout. I did not
find something special or different in execution plan.
The only workaround I have found is to recreate <table1>. I just copy
all data from this table to another table, then drop table <table1>,
then create it with adding necessary index and then copy data back
from temptable to original one. And it helps! The same data is easily
inserted in 2 minutes.
I have never experienced such problem on SQL Server 2000, only on
2005. Unfortunately, we cannot reproduce it on our environment but
there are no visible differences in server or db options.
Probably somebody already solved such problem or can advise where to
go. Any help would be appreciated.
Thanks in advance!
Hi
"prudon@.inbox.ru" wrote:

> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>
Have you checked the version of SQL 2005 that you are running? Make sure
that it is up to date. Also look for blocking
http://support.microsoft.com/kb/271509 missing indexes
http://msdn2.microsoft.com/en-us/library/ms345524.aspx or out of date
statistics http://msdn2.microsoft.com/en-us/library/ms190397.aspx
John
|||1) Almost certainly a blocking situation. moving (potentially large)
amounts of data like this is often a performance issue because the locks
escalate to full table, preventing ANY other update/delete/insert access to
the table for the duration of the transaction.
2) My gut tells me to question a clustered index on a float datatype.
TheSQLGuru
President
Indicium Resources, Inc.
<prudon@.inbox.ru> wrote in message
news:1180681124.707731.111770@.q69g2000hsb.googlegr oups.com...
> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>
|||Thank you very much!
The specific thing of our application that there is only one connect
per database. So, there are no other transactions on those database. I
cannot understand why we didn't experienced such problems on SQL
Server 2000 for more than 4 years nowhere. If the problem is in
clustered index, why "re-creating" table with index helps to avoid the
problem. Next time I get such problem I will check statistics, but I'm
afraid it will not give anything.
Many thanks for your feedback