SQL Indexes

Last Post 17 Jan 2008 10:03 AM by SQLUSA. 17 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
TRACEYSQL
New Member
New Member

--
24 Aug 2007 08:54 AM
I was looking at this thread for sql 2005
http://www.developer.com/db/article..._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 ca
TRACEYSQL
New Member
New Member

--
24 Aug 2007 01:52 PM
How do i know if they are used i take it they have to be maintained every time insert or update.
Are they generated only by the auto update statistics ...or when someone does a query against table.

TRACEYSQL
New Member
New Member

--
27 Aug 2007 04:41 AM
Lets say someone queried them with the where used and it was not a primary key or index column as the developers are not that clued in and i have the statistics.

If it was only used once and not used again each time the statistics is updated it is updating these one
consuming resources for ones not used that often.

Im just wondering how to figure out if they are used and if i could delete the ones not used.
Is there a way to determine this
TRACEYSQL
New Member
New Member

--
27 Aug 2007 03:08 PM
Thanks i will give them scripts a try in SQL 2005 and probrably not delete anything just restore to SQL 2005 and see the statistics...

Cheers
SQLUSA
New Member
New Member

--
29 Nov 2007 11:47 PM
Can you update us?

Thanks.

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com/order2005grandslam/
TRACEYSQL
New Member
New Member

--
30 Nov 2007 09:34 AM
I did not get too far with it.... i getting another build of our sql 2005 right now so i reload SQL 2000 on to this and then run index rebuild and see if i get the WA copied over from 2000

Thanks
SQLUSA
New Member
New Member

--
15 Jan 2008 09:21 AM
The auto update statistics option can slow down your system, at the same time guaranteed to speed up queries using it.


So what is the outcome? What did you do?

Let us know pls.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
TRACEYSQL
New Member
New Member

--
15 Jan 2008 10:43 AM
I just finish building my entire SQL 2005 database from 4 SQL Servers.

I still see the _WA on here after doing full update
Sp_MSForEachTable 'Update Statistics ? WITH FULLSCAN'
Rebuild all indexes on database.
DBCC UPDATEUSAGE WITH COUNT_ROWS

How to run these two scripts
sys.dm_db_index_operational_stats
sys.dm_db_index_usage_stats

for one database and one table.

Then i can get an idea of what last used.
If not used recently can delete the _WA


SQLUSA
New Member
New Member

--
15 Jan 2008 12:16 PM
Instead of using raw DMV(DMF) queries, you can use SQL Server 2005 Performance Dashboards.

I find the dashboard reports real useful.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/highperformance2005/ The Best SQL Server 2005 Training in the World!

SQLUSA
New Member
New Member

--
15 Jan 2008 08:30 PM
You can use DMVs also:

SELECT [Table]=o.name, [Index]=x.name, user_scans, user_seeks, user_lookups,
last_user_scan
from sys.dm_db_index_usage_stats u
join sys.sysdatabases d
on u.database_id = d.dbid
join sys.sysindexes x
on u.object_id = x.id
and u.index_id = x.indid
join sys.sysobjects o
on u.object_id = o.id
where d.name = 'AdventureWorks'
and o.type = 'u'
order by user_scans desc, user_seeks desc


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!

TRACEYSQL
New Member
New Member

--
16 Jan 2008 05:25 AM
Bear with me just got SQL 2005 here been busy setting up everything. Just realised the bak were not being deleted and had to create maintenance plans.

Anyway back to indexes and the dynamic views.

Ran the query and see the user counts etc...
Do not see anything in here for the _wa statistics on the table so i assume it is ok to delete all these.

Or would these statistics not come out.

Does the _wa get created when some one is doing
select * from table where customer_id = 'xxx'
so it would create an statistic for this if no index.

TRACEYSQL
New Member
New Member

--
16 Jan 2008 05:50 AM
I think i got it.

_WA are statistics created when there is a missing index on the table.

So if i just restored from SQL 2000 to SQL 2005 i should delete all the _WA and then in a few months check if i have any more _WA then review the statistics created and see if to add indexes or not.

Is that correct, if so is there an easy way to delete all the statistics _wa from database.

Thanks
SQLUSA
New Member
New Member

--
16 Jan 2008 06:13 AM
SQL 2005 collects statistical information about indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan.

You can use DROP STATISTICS to drop them if they become too annoying.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
TRACEYSQL
New Member
New Member

--
16 Jan 2008 01:03 PM
Back...finished my new script which will run everything night and only rebuild indexes online, offline accordlingly.

That working a treat.

Now back to the _WA

Just for testing i create table

CREATE TABLE [dbo].[CUSTOMERTEST](
[Customer_Id] [nchar](10) NOT NULL,
[Customer_Name] [nvarchar](1000) NULL,
[Customer_Address] [nvarchar](1000) NULL,
[Customer_Address1] [nchar](1000) NULL,
CONSTRAINT [PK_CUSTOMERTEST] PRIMARY KEY CLUSTERED
(
[Customer_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Then did select * from CUSTOMERTEST where customer_name = 'xxx'

Now i see the _WA

When i do right click on the _WA to remove it ----message comes back and says cannot remove no associated columsn.

So just to have whole picture...how do i remove the _WA.

Thanks you all very much

SQLUSA
New Member
New Member

--
16 Jan 2008 10:32 PM
You have to use DROP STATISTICS. You can write a script generator.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
TRACEYSQL
New Member
New Member

--
17 Jan 2008 07:31 AM
As this is a brand new SQL 2005 with x memory and 8 processors and a full conversion from SQL 2000

I think i will drop all the _WA then from day one on production.
This way i will know what indexes are required.
We done quite a few updates with vendor software so probrably some of these indexes were created and the old _wa are from way back.

Thanks all for your help.


SQLUSA
New Member
New Member

--
17 Jan 2008 10:03 AM
>This way i will know what indexes are required.

You can use the Database Engine Tuning Advisor (DTA) to help you with indexing. Make sure you supply a good sample though!

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/highperformance2005/ The Best SQL Server 2005 Training in the World!
TRACEYSQL
New Member
New Member

--
17 Jan 2008 11:14 AM
Thanks made note of statistics - indexes which are both separate entities.

Also to do the Database Engine Tuning Advisor.

Just finished entire setup of SQL 2005 in our test.
Only 100 databases or ........ need a helper.


Im good to go for a while.........on indexes and statistics.

Got some questions on other things but i post on another thread.







Acceptable Use Policy
---