Creating Stats - 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)

Creating Stats

Author

Topic

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-10-21 : 13:38:53

Hi,
When I generate a script for rebuilding indxes within PSoft, it gives me a Create and Drop Index script for all indexes on a particular table. Some of these seems to be the statistics. See script below for a particular table.
The question I have is - Is it dropping the stats? If Yes, does the system automatically rebuild the stats? Because all I am doing after running this script is running an update stats with full scan option.
There is only one index on this table!

-- Script follows --
SET IMPLICIT_TRANSACTIONS ON
go
IF EXISTS (SELECT 'X' FROM SYSINDEXES WHERE NAME = 'PS_PAY_GARNISH')
DROP INDEX PS_PAY_GARNISH.PS_PAY_GARNISH
go
IF EXISTS (SELECT 'X' FROM SYSINDEXES WHERE NAME =
'_WA_Sys_DEDUCT_AMT') DROP INDEX PS_PAY_GARNISH._WA_SYS_DEDUCT_AMT
go
IF EXISTS (SELECT 'X' FROM SYSINDEXES WHERE NAME =
'_WA_Sys_DEDUCT_GAR') DROP INDEX PS_PAY_GARNISH._WA_SYS_DEDUCT_GAR
go
IF EXISTS (SELECT 'X' FROM SYSINDEXES WHERE NAME =
'_WA_Sys_DEDUCT_PAY') DROP INDEX PS_PAY_GARNISH._WA_SYS_DEDUCT_PAY
go
IF EXISTS (SELECT 'X' FROM SYSINDEXES WHERE NAME =
'_WA_Sys_GARN_RULE_') DROP INDEX PS_PAY_GARNISH._WA_SYS_GARN_RULE_
go
CREATE UNIQUE CLUSTERED INDEX PS_PAY_GARNISH ON PS_PAY_GARNISH
(COMPANY,
PAYGROUP,
PAY_END_DT,
OFF_CYCLE,
PAGE_NUM,
LINE_NUM,
SEPCHK,
PLAN_TYPE,
BENEFIT_PLAN,
DEDCD,
DED_CLASS,
GARN_PRIORITY,
GARNID)
go
COMMIT
go
--End Of Script--
Thanks,
Sarat

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 13:41:54

Do you have auto create stats turned on for your database (right click on db in EM, go to properties, then to options)? If so, then SQL will create them for you when it finds it needs them.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-10-21 : 14:03:33

Yes, both create n update are set to on.
But when does sql server create it? Does DBCC DBREINDEX also drop stats?
Thanks,
Sarat

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 14:10:14

From BOL (sp_dboption article):

auto create statistics - When true, any missing statistics needed by a query for optimization are automatically built during optimization. For more information, see CREATE STATISTICS.

I am not sure about DBCC DBREINDEX. BOL didn't mention it.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-10-21 : 15:06:12

ok i got it. every time i run the above script, the indexes(or stats) starting with 'WA_' get dropped which I don't build specifically so system must be creating it based on the data changes in the table.
but the sql script is confusing since there is no index called 'WA_%' when I do sp_helpindex on the table. Is it possible that in this script, index 'WA_%' is same as stats?
Thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 15:52:36

When SQL Server auto creates the statistics, it names them starting with _WA. You can also create your own statistics and call them whatever you want. IsStatistic will tell you if the index is a statistic or not. It is more reliable than just assuming _WA indexes are statistics.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-11-03 : 11:12:04

Based on the below script:
--
IF EXISTS (SELECT 'X' FROM SYSINDEXES WHERE NAME =
'_WA_Sys_COURSE_END') DROP INDEX PS_TRAINING._WA_SYS_COURSE_END
go
--
I did the following:

SELECT INDEXPROPERTY(OBJECT_ID('PS_TRAINING'), '_WA_Sys_COURSE_END',
'IsStatistics')
--> I got a NULL in return. Why is the system unable to determine if it is a statistic or not? Is there any other way?
Thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-11-03 : 11:27:32

Is it necessary that I will see this object (stat) '_WA_Sys_COURSE_END' in any sys table if this exists?
Thanks

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-03 : 15:37:17

If you don't want to see those rows, then just use IsStatistic option.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-11-03 : 16:18:56

I didn't quite understand what you mean. I want to see all stats (including _WA ones) on a table.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-03 : 16:21:56

To see all stats, just select against sysindexes using IsStatistic = 1. I think that you'll need to join to sysobjects in order to be able to put the table name in the WHERE clause.

Take a look at IsStatistic in BOL for more information.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-11-03 : 17:02:05

There isn't any column called IsStatistic in sysindexes table. Only things I see are in sysobjects called userstat and sysstat.
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-11-03 : 17:34:40

I think you meant sp_helpstats. I got it.
Thanks.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-03 : 18:02:38

IsStatistic is a function that you can call to determine if the index is a statistic or not. It is not a column in the table. Take a look at IsStatistic in BOL for details.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-11-03 : 18:02:45

Ok, I also found out that the sql script wasn't displaying the entire name: ex: _WA_Sys_COURSE_END_DT_1151B979 was displayed as
_WA_Sys_COURSE_END thats why when I plugged it in select index property statement, it returned NULL.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-03 : 20:04:46

IsStatistics is used with the INDEXPROPERTY function:

SELECT name FROM sysindexes WHERE INDEXPROPERTY(id, indid, 'IsStatistics')=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources