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.googlegroups.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

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.googlegroups.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

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

ms sql equivalent of this oracle

Hi.
I'm a casual sql user. I have found a situation where I need to convert an
oracle statement to tsql, one I can just fire off in any sql tool against an
ms sql server database.

I studied the exists statement and I think I understand it somewhat, however I
was not sure how to get it quite right. If you have an idea and a minute or
two I'd appreciate any insight or tutorial.

insert into authorization (program, optiontitle, usergroup, authorizationid)
select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextval
from allgroups where exists (select * from authorization
where authorization.USERGROUP = allgroups.USERGROUP and
authorization.optiontitle = 'READ' and authorization.program = 'EVERYWHERE')

I believe that because in my data, three values of usergroup from allgroups
return true from the exists, that this is supposed to insert three rows into
authorization.

But I can't figure out what to do about the authorization.nextval.. I tried
various max(authorization)+1
etc but nothing seemed to compile/work

thanks
Jeff KishHi

You can make the column an identity, this will not guarantee contiguous
number but it will be increasing/decreasing and unique. You can then miss it
out from the statement altogether.

These may help:
http://vyaskn.tripod.com/oracle_sql...equivalents.htm
http://www.microsoft.com/technet/pr...art2/c0761.mspx

John

"Jeff Kish" <jeff.kish@.mro.com> wrote in message
news:b5a7u1lod76n3at35csf2b6f8nvpj35jn8@.4ax.com...
> Hi.
> I'm a casual sql user. I have found a situation where I need to convert an
> oracle statement to tsql, one I can just fire off in any sql tool against
> an
> ms sql server database.
> I studied the exists statement and I think I understand it somewhat,
> however I
> was not sure how to get it quite right. If you have an idea and a minute
> or
> two I'd appreciate any insight or tutorial.
> insert into authorization (program, optiontitle, usergroup,
> authorizationid)
> select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextval
> from allgroups where exists (select * from authorization
> where authorization.USERGROUP = allgroups.USERGROUP and
> authorization.optiontitle = 'READ' and authorization.program =
> 'EVERYWHERE')
>
> I believe that because in my data, three values of usergroup from
> allgroups
> return true from the exists, that this is supposed to insert three rows
> into
> authorization.
> But I can't figure out what to do about the authorization.nextval.. I
> tried
> various max(authorization)+1
> etc but nothing seemed to compile/work
> thanks
> Jeff Kish|||Jeff,

authorizationseq seems to be a sequence. Sequences just don't exist in
MS SQL Server 2000/7/6.5. You might want to have an identity column.|||On Sat, 4 Feb 2006 16:17:55 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:

>Hi
>You can make the column an identity, this will not guarantee contiguous
>number but it will be increasing/decreasing and unique. You can then miss it
>out from the statement altogether.
>These may help:
>http://vyaskn.tripod.com/oracle_sql...equivalents.htm
>http://www.microsoft.com/technet/pr...art2/c0761.mspx
thanks. I'm still not sure of how to do something here, though.

This is directly related to the problem but re-worded because I need to
get the next value using max(authorizationid)+1 ...

Given two tables:
allgroups(usergroup, otherdata) =
{'group1',otherdata1,
'group2',otherdata2,
'group3',otherdata3,
:
:
'groupn',otherdatan}

and
authorization(program,optiontitle,
usergroup,authorizationid) =
{'pro1','title1','ug1',3,
'pro2','title2','ug2',4,
:
'pron','titlen','ugn',m}

How can I insert multiple
lines (one for each usergroup
in allgroups) using one sql statement
into authorization if this is correct for a
single insert:
insert into authorization(program,
optiontitle,usergroup,
authorizationid)
select 'proq','titleq','ug1',
max(authorizationid)+1
from authorization

bascially I'd like each usergroup
from allgroups to be used to create a
new line in authorization, having
the authorizationid increment one from
the current max.

Yes, I have no control over the design/use of
an identity column.

Is it possible?
Thanks
Jeff Kish|||Jeff Kish wrote:

> thanks. I'm still not sure of how to do something here, though.
> This is directly related to the problem but re-worded because I need to
> get the next value using max(authorizationid)+1 ...
> Given two tables:
> allgroups(usergroup, otherdata) =
> {'group1',otherdata1,
> 'group2',otherdata2,
> 'group3',otherdata3,
> :
> :
> 'groupn',otherdatan}
> and
> authorization(program,optiontitle,
> usergroup,authorizationid) =
> {'pro1','title1','ug1',3,
> 'pro2','title2','ug2',4,
> :
> 'pron','titlen','ugn',m}
> How can I insert multiple
> lines (one for each usergroup
> in allgroups) using one sql statement
> into authorization if this is correct for a
> single insert:
> insert into authorization(program,
> optiontitle,usergroup,
> authorizationid)
> select 'proq','titleq','ug1',
> max(authorizationid)+1
> from authorization
> bascially I'd like each usergroup
> from allgroups to be used to create a
> new line in authorization, having
> the authorizationid increment one from
> the current max.
> Yes, I have no control over the design/use of
> an identity column.
> Is it possible?
> Thanks
> Jeff Kish

Assuming SQL Server 2005 (you didn't specify otherwise), use the
ROW_NUMBER function. For example:

INSERT INTO [authorization]
(program, optiontitle, usergroup, authorizationid)
SELECT program, optiontitle, usergroup,
authorizationid+
(SELECT MAX(authorizationid)
FROM [authorization])
FROM
(SELECT 'proq','titleq', usergroup, ROW_NUMBER()
OVER (ORDER BY usergroup)
FROM allgroups)
AS T(program, optiontitle, usergroup, authorizationid) ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||<snip>
>Assuming SQL Server 2005 (you didn't specify otherwise), use the
Sql Server 2000. Really sorry I did not say it up front. Do you know
of a way using that?
Regards,
Jeff

>ROW_NUMBER function. For example:
>INSERT INTO [authorization]
> (program, optiontitle, usergroup, authorizationid)
> SELECT program, optiontitle, usergroup,
> authorizationid+
> (SELECT MAX(authorizationid)
> FROM [authorization])
> FROM
> (SELECT 'proq','titleq', usergroup, ROW_NUMBER()
> OVER (ORDER BY usergroup)
> FROM allgroups)
> AS T(program, optiontitle, usergroup, authorizationid) ;
>--
>David Portas, SQL Server MVP
>Whenever possible please post enough code to reproduce your problem.
>Including CREATE TABLE and INSERT statements usually helps.
>State what version of SQL Server you are using and specify the content
>of any error messages.
>SQL Server Books Online:
>http://msdn2.microsoft.com/library/...US,SQL.90).aspx|||Jeff Kish wrote:
> <snip>
> >Assuming SQL Server 2005 (you didn't specify otherwise), use the
> Sql Server 2000. Really sorry I did not say it up front. Do you know
> of a way using that?
> Regards,
> Jeff

INSERT INTO [authorization]
(program, optiontitle, usergroup, authorizationid)
SELECT program, optiontitle, usergroup,
authorizationid+
(SELECT COALESCE(MAX(authorizationid),0)
FROM [authorization])
FROM
(SELECT 'proq','titleq', A1.usergroup, COUNT(*)
FROM allgroups AS A1
JOIN allgroups AS A2
ON A1.usergroup >= A2.usergroup
GROUP BY A1.usergroup)
AS T(program, optiontitle, usergroup, authorizationid) ;

This assumes that usergroup is unique in Allgroups. If I'm wrong then
use the key of that table in the join. You need a key in order to
generate the sequence.

Read my signature. It may help you get faster answers in future.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--