we found that some basic select queries were taking like 4 minutes, to
execute, and they shouldn’t take more that one second, so we were getting
hundreds of timeouts between our applications, I was assigned to check
what was happening, and I ran a script that i found on the web, that takes
every table of the schema and then reindex it, well the thing was in that
specific table the scrip got an error telling me this:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL
Server Driver][SQL Server]A column has been specified more than once in
the order by list. Columns in the order by list must be unique.
I check all the indexes in that table and i found 4, but none of them had
an order by column duplicated, I even restore the db somewhere else and
played with those indexes, but it seems like I cant delete one of them the
first one, my index script is the following:
CREATE CLUSTERED INDEX [IX_Accountno] ON [dbo].[tblaction]([AccountNo],
[yearno], [w
No], [Dayno]) ON [PRIMARY]GO
CREATE INDEX [Gradectrl] ON [dbo].[tblaction]([GradeCtrl]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [Linelink] ON [dbo].[tblaction]([LineLINK]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ticketno] ON [dbo].[tblaction]([TicketNo]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblaction] ON [dbo].[tblaction]([yearno], [w
No],[Dayno]) ON [PRIMARY]
GO
I tried to remove them all and then recreate them , but when I try to
delete the clustered one, the error pops again and doesn’t let me delete
it, I also check the whole db script to see if i got something else in
another place, but I didn’t found anything... any suggestions?
How can i remove all the indexes, and / or get rid of this slowing error?
Thanks for your time
PD: The following is the reindex script
DECLARE @.MyTable varchar(255)
DECLARE myCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @.MyTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @.MyTable
DBCC DBREINDEX(@.MyTable, '', 90)
FETCH NEXT FROM myCursor INTO @.MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
--Thanks!
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/- Can check the output of sp_helpindex table_name?
- Can you try to recreate the clustered index with the option DROP_EXISTING?
AMB
"Edgardo Sepulveda" wrote:
> Hi, I am having a real headache with a table in my db, one of these days
> we found that some basic select queries were taking like 4 minutes, to
> execute, and they shouldn’t take more that one second, so we were gettin
g
> hundreds of timeouts between our applications, I was assigned to check
> what was happening, and I ran a script that i found on the web, that takes
> every table of the schema and then reindex it, well the thing was in that
> specific table the scrip got an error telling me this:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL
> Server Driver][SQL Server]A column has been specified more than once in
> the order by list. Columns in the order by list must be unique.
> I check all the indexes in that table and i found 4, but none of them had
> an order by column duplicated, I even restore the db somewhere else and
> played with those indexes, but it seems like I cant delete one of them the
> first one, my index script is the following:
> CREATE CLUSTERED INDEX [IX_Accountno] ON [dbo].[tblaction]([AccountNo],
> [yearno], [w
No], [Dayno]) ON [PRIMARY]> GO
> CREATE INDEX [Gradectrl] ON [dbo].[tblaction]([GradeCtrl]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [Linelink] ON [dbo].[tblaction]([LineLINK]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [ticketno] ON [dbo].[tblaction]([TicketNo]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblaction] ON [dbo].[tblaction]([yearno], [w
No],> [Dayno]) ON [PRIMARY]
> GO
> I tried to remove them all and then recreate them , but when I try to
> delete the clustered one, the error pops again and doesn’t let me delete
> it, I also check the whole db script to see if i got something else in
> another place, but I didn’t found anything... any suggestions?
> How can i remove all the indexes, and / or get rid of this slowing error?
>
> Thanks for your time
>
>
> PD: The following is the reindex script
>
>
> DECLARE @.MyTable varchar(255)
> DECLARE myCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN myCursor
> FETCH NEXT FROM myCursor INTO @.MyTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Reindexing Table: ' + @.MyTable
> DBCC DBREINDEX(@.MyTable, '', 90)
> FETCH NEXT FROM myCursor INTO @.MyTable
> END
> CLOSE myCursor
> DEALLOCATE myCursor
> EXEC sp_updatestats
>
> --Thanks!
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
>|||The output of the sp_helpindex was:
aaaaatblaction_PK nonclustered, unique, primary key located on PRIMARY ID
Gradectrl nonclustered located on PRIMARY GradeCtrl
hind_1612584833_58A_83A nonclustered, hypothetical, auto create located on
PRIMARY w
No, yearnohind_1612584833_83A_58A nonclustered, hypothetical, auto create located on
PRIMARY yearno, w
Nohind_c_1612584833_34A clustered, hypothetical, auto create located on
PRIMARY GradeCtrl
hind_c_1612584833_58A clustered, hypothetical, auto create located on
PRIMARY w
Nohind_c_1612584833_83A clustered, hypothetical, auto create located on
PRIMARY yearno
hind_c_1612584833_83A_58A clustered, hypothetical, auto create located on
PRIMARY yearno, w
NoIX_Accountno clustered located on PRIMARY AccountNo, yearno, w
No, DaynoIX_tblaction nonclustered located on PRIMARY yearno, w
No, DaynoLinelink nonclustered located on PRIMARY LineLINK
ticketno nonclustered located on PRIMARY TicketNo
I think this is the problem, too many indexes. or too many combinations.
Thanks for the tip Alejandro, any suggestions Based on this info?
Thanks for your Time.
Edgardo
