sql indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sql indexes

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-24 : 13:53:31

I was looking at this thread for sql 2005
http://www.developer.com/db/article.php/10920_3622881_1
Im on 2000 and noticed the _WA which are updated by sql.

For one of my tables i have
25 of these

The table
CREATE TABLE [ACCT] (
[ACCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACTIVE_FL] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FY_CD_FR] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD_NO_FR] [smallint] NULL ,
[FY_CD_TO] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PD_NO_TO] [smallint] NULL ,
[ACCT_ENTR_GRP_CD] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROJ_REQD_FL] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[S_ACCT_TYPE_CD] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DETL_FL] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACCT_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TOP_FL] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MODIFIED_BY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TIME_STAMP] [datetime] NOT NULL ,
[ROWVERSION] [int] NULL ,
[L1_ACCT_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L2_ACCT_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L3_ACCT_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L4_ACCT_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L5_ACCT_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L6_ACCT_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L7_ACCT_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LVL_NO] [smallint] NULL ,
[L1_ACCT_SEG_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L2_ACCT_SEG_ID] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L3_ACCT_SEG_ID] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L4_ACCT_SEG_ID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L5_ACCT_SEG_ID] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L6_ACCT_SEG_ID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L7_ACCT_SEG_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L8_ACCT_SEG_ID] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TC_ACCT_TYPE_CD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_0039] PRIMARY KEY CLUSTERED
(
[ACCT_ID]
) WITH FILLFACTOR = 70 ON [PRIMARY]
) ON [PRIMARY]
GO

_WA_Sys_ACCT_ENTR_GRP_CD_3E7A942C ACCT_ENTR_GRP_CD
_WA_Sys_ACCT_NAME_3E7A942C ACCT_NAME
_WA_Sys_ACTIVE_FL_3E7A942C ACTIVE_FL
_WA_Sys_DETL_FL_3E7A942C DETL_FL
_WA_Sys_FY_CD_FR_3E7A942C FY_CD_FR
_WA_Sys_FY_CD_TO_3E7A942C FY_CD_TO
_WA_Sys_L1_ACCT_NAME_3E7A942C L1_ACCT_NAME
_WA_Sys_L1_ACCT_SEG_ID_3E7A942C L1_ACCT_SEG_ID
_WA_Sys_L2_ACCT_NAME_3E7A942C L2_ACCT_NAME
_WA_Sys_L2_ACCT_SEG_ID_3E7A942C L2_ACCT_SEG_ID
_WA_Sys_L3_ACCT_SEG_ID_3E7A942C L3_ACCT_SEG_ID
_WA_Sys_L4_ACCT_SEG_ID_3E7A942C L4_ACCT_SEG_ID
_WA_Sys_L5_ACCT_SEG_ID_3E7A942C L5_ACCT_SEG_ID
_WA_Sys_L6_ACCT_SEG_ID_3E7A942C L6_ACCT_SEG_ID
_WA_Sys_L7_ACCT_SEG_ID_3E7A942C L7_ACCT_SEG_ID
_WA_Sys_L8_ACCT_SEG_ID_3E7A942C L8_ACCT_SEG_ID
_WA_Sys_PD_NO_FR_3E7A942C PD_NO_FR
_WA_Sys_PD_NO_TO_3E7A942C PD_NO_TO
_WA_Sys_PROJ_REQD_FL_3E7A942C PROJ_REQD_FL
_WA_Sys_ROWVERSION_3E7A942C ROWVERSION
_WA_Sys_S_ACCT_TYPE_CD_3E7A942C S_ACCT_TYPE_CD
_WA_Sys_TC_ACCT_TYPE_CD_3E7A942C TC_ACCT_TYPE_CD
PATCH1534_IDX1 ACCT_ID, ACTIVE_FL, TC_ACCT_TYPE_CD
PI_0039 ACCT_ID
PK_0039 ACCT_ID

I was wondering why so many...is it cause someone doing
select * from table where PD_NO_FR = 'xxx' or using where column ?

Is it best to remove these or is there a way to check their relevant.

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-24 : 14:17:00

They are names assigned by the system to statistics when you dont name them yourself. Often,when you look at a query plan in query analyzer and it says with a red indicator "Missing statistics" and you can right click on the table and "Create statistics". Then SQL Server gives it a name similar to above "_WA...".

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-24 : 14:43:39

im using SQL 2000 right now....i see the create statistics in 2005.
Should there be that many ?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-24 : 16:29:58

You should know if they are user created on the fly or the ones that you maintain.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-24 : 18:48:52

The WA ones are system generated from statistics i was wondering why so many

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-24 : 19:12:59

quote:
Originally posted by TRACEYSQL

The WA ones are system generated from statistics i was wondering why so many

Only the name is system generated. A user created the statistic.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-24 : 23:52:52

Because optimizer needs them based on queries against the table, sql will update them if db option 'auto update statistics' is set.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-27 : 09:52:03

Maybe im confusing myself...with index and statistics here.
When ever a table is used i.e select * from tablea where column a = x

If column a is not a physical index (Clustered or non clustered index)...then
SQL will generate a statistic for the column a.

If i have column b and no select has been generated i will get no statisics

If for some reason some one ran a bad query on some column lets say
column c select * from table where columnc = 'x'
If the statistics are for this columnc and then the query never ran again
Is this a waste as the statistics are not being updated and this query
will never be used again (Is there a way to determine this are remove this statistic) ?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-27 : 22:08:04

Can't easily tell if it's used again or not, good news is that it doesn't take much resource unless on very large table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources