SQL Server Performance Forum – Threads Archive
Update Statistics Fail
When I run Update Statistics ‘cmpasociados’ with fullscan, they do the jobs until stop with following message: Server: Msg 169, Level 15, State 2, Line 2A column has been specified more than once in the order by list. Columns in the order by list must be unique. After that I run sp_MShelpindex ‘[dbo].[CMPASOCIADOS]’
and
sp_helpstats ‘cmpasociados’ but there is no duplicate column in all statistics list. Any idea? Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Can you post the results of the sp_help….
Also, can you run sp_helpindex, not just sp_mshelpindex. I’m not sure if there are any subtle differences between the two and what they would provide if there were. chris
Check if there are any hypothetical indexes (generated by the ITW) on the table. If there are, drop them – they are most likely the cause of your problem. You can identify them using
select * from sysindexes where name like ‘hind_%’
HTH Jasper Smith
Oh, went looking some more, and found this exact problem in another list. The user went through all the steps you have,and then some more, including dbcc checks etc, only to find out that the indexes creating issues were ‘phantom/hypothetical’ indexes, once they were dropped all was well. They should show in sp_helpindex, and will look like this indexuid
hind_c_1718297181_14A
clustered, hypothetical, auto create located on PRIMARY I think they also all start with hind_ drop these and your problems will go away (I think) Chris
sp_helpstats statistics_name statistics_keys _WA_Sys_FECHAEXT_182C9B23 FECHAEXT
_WA_Sys_HASTANUMERO_182C9B23 HASTANUMERO
_WA_Sys_HOJAS_182C9B23 HOJAS
_WA_Sys_NUMERO_182C9B23 NUMERO
_WA_Sys_PREFIJO_182C9B23 PREFIJO
_WA_Sys_TALONARIO_182C9B23 TALONARIO
hind_405576483_1A_2A_3A_6A_7A_8A_9A NROTRANS, CODCMP, TALONARIO, FECHAEXT, PREFIJOEXT, NUMEROEXT,NROSDB
hind_405576483_5A_1A NUMERO, NROTRANS
hind03.04.27_405576483_1A_2A_3A_4A_5A NROTRANS, CODCMP, TALONARIO, PREFIJO, NUMERO
hind03.04.27_405576483_1A_2A_6A_7A_8A NROTRANS, CODCMP, FECHAEXT, PREFIJOEXT, NUMEROEXT
hind03.04.27_405576483_1A_2A_7A_8A NROTRANS, CODCMP, PREFIJOEXT, NUMEROEXT
hind03.04.27_405576483_1A_6A_9A NROTRANS, FECHAEXT, NROSDB
hind03.09.17_405576483_2A CODCMP
hind03.10.10_Statistic_Nroasires NROASIRES
hind03.10.10_Statistic_Nrosdb NROSDB
hind03.10.10_Statistic_Numeroext NUMEROEXT
hind03.10.27_405576483_1A_3A_4A_5A NROTRANS, TALONARIO, PREFIJO, NUMERO
hind03.10.27_405576483_1A_5A NROTRANS, NUMERO sp_helpindex CMPASOCIADOS_NROTRANS nonclustered located on PRIMARY NROTRANS, NROSDB
CMPASOCIADOS_PREFIJOEXT nonclustered located on PRIMARY PREFIJOEXT, NUMEROEXT
hind_405576483_1A nonclustered, hypothetical located o PRIMARY NROTRANS
hind_405576483_5A_1A nonclustered, hypothetical, auto create located on PRIMARY NUMERO, NROTRANS
hind_c_405576483_2A clustered, hypothetical located on PRIMARY
CODCMP
IXC03.04.28_CMPASOCIADOS_NroTrans_CodCmp_PrefijoExt_NumeroExt nonclustered located on Secondary NROTRANS, CODCMP, PREFIJOEXT, NUMEROEXT
IXC03.4.28_CMPASOCIADOS_NroTrans clustered located on Secondary NROTRANS
IXCP03.10.27_CMPASOCIADOS_Numero_Talonario_NroTrans_Prefijo nonclustered located on Secondary NUMERO, TALONARIO, NROTRANS, PREFIJO Difficult to format.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Thank guys.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
As referred by Jasper & Chris try to drop these indexes which were autogenerated and this should ease up the issue. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I’m also experiencing these error messages.
Does this mean that the indexes failed to rebuild, or they did get rebuilt and that those were just warning messages? Raymond
Hyphotetical Index, that was my case, are produced by Index Tuning Wizard.
I don’t know if rebuild can do that.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
]]>