_WA_ entries in sysindexes

Hi,
When I look at sysindexes, there are lot of _WA_% entries in there for my
database. These are constraints I believe, created by checking 'Auto create
statistics' option at the DB level. SOmewhere I read that if you see lot of
these constraints, it is better to create 'em as permanent indexes for bette
r
performance.
1) Is that true ?
2) How do I extract the ddl for these constraints ?
TIA
KNM> When I look at sysindexes, there are lot of _WA_% entries in there for my">
> database. These are constraints I believe
No, constraints is something entirely different.

> ...created by checking 'Auto create
> statistics' option at the DB level.
Correct. These entries are statistics, the same thing as when you execute th
e CREATE STATISTICS
command.

> SOmewhere I read that if you see lot of
> these constraints, it is better to create 'em as permanent indexes for bet
ter
> performance.
That is not necessarily the case. First, these are not indexes, it is statis
tics. Big difference.
Also, just because the optimizer feels like having statistics over a column
doesn't mean that an
index would be beneficial (and an index comes with overhead). You should ana
lyze what indexes your
queries would benefit from, and it is possible that some of these auto creat
ed statistics should be
indexes, but only you can know whether that is the case or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MO" <MO@.discussions.microsoft.com> wrote in message
news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
> Hi,
> When I look at sysindexes, there are lot of _WA_% entries in there for m
y
> database. These are constraints I believe, created by checking 'Auto creat
e
> statistics' option at the DB level. SOmewhere I read that if you see lot o
f
> these constraints, it is better to create 'em as permanent indexes for bet
ter
> performance.
> 1) Is that true ?
> 2) How do I extract the ddl for these constraints ?
> TIA
> KNM|||Thx for your info.
Dumb Q. How do I see what columns the stats is for ?
I know the stats name is say _WA_Sys_ord_billto_66F53242
But on what column the stats is built on ? What command will tell me that ?
TIA
Knm
"Tibor Karaszi" wrote:

> No, constraints is something entirely different.
>
> Correct. These entries are statistics, the same thing as when you execute
the CREATE STATISTICS
> command.
>
> That is not necessarily the case. First, these are not indexes, it is stat
istics. Big difference.
> Also, just because the optimizer feels like having statistics over a colum
n doesn't mean that an
> index would be beneficial (and an index comes with overhead). You should a
nalyze what indexes your
> queries would benefit from, and it is possible that some of these auto cre
ated statistics should be
> indexes, but only you can know whether that is the case or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:18235A22-6059-4F5E-B2A8-71121719E519@.microsoft.com...
>|||> Dumb Q. How do I see what columns the stats is for ?
One method is to query the system table with a query like the following:
Try
SELECT
OBJECT_NAME(i.id) ,AS TableName
i.name AS StatisticsName,
c.name AS ColumnName
FROM sysindexes i
JOIN sysindexkeys k ON
k.id = i.id and k.indid = i.indid
JOIN syscolumns c on
c.id = k.id and c.colid = k.colid
WHERE INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1
ORDER BY
OBJECT_NAME(i.id),
i.name,
c.name
Hope this helps.
Dan Guzman
SQL Server MVP
"MO" <MO@.discussions.microsoft.com> wrote in message
news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...[vbcol=seagreen]
> Thx for your info.
> Dumb Q. How do I see what columns the stats is for ?
> I know the stats name is say _WA_Sys_ord_billto_66F53242
> But on what column the stats is built on ? What command will tell me that
> ?
> TIA
> Knm
> "Tibor Karaszi" wrote:
>|||If you know the table, you can use sp_helpstats
You can pull the table name from sysindexes with this:
select object_name(id)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"MO" <MO@.discussions.microsoft.com> wrote in message
news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...[vbcol=seagreen]
> Thx for your info.
> Dumb Q. How do I see what columns the stats is for ?
> I know the stats name is say _WA_Sys_ord_billto_66F53242
> But on what column the stats is built on ? What command will tell me that
> ?
> TIA
> Knm
> "Tibor Karaszi" wrote:
>|||Thanks for the info. I looked at a table, it has 118 entries in sysindexes
for that table. 6 indexes and 112 contraints. I dropped those constraints to
see if they are being used, but they all got created in couple of min which
tells me that they are being used. So, my question is should I convert those
constraints to an index ?
What are the cons of keeping these as a constraint and not converting to an
index in the long run ?
TIA
MO
"Kalen Delaney" wrote:

> If you know the table, you can use sp_helpstats
> You can pull the table name from sysindexes with this:
> select object_name(id)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "MO" <MO@.discussions.microsoft.com> wrote in message
> news:80809AF6-100F-46E9-8CF3-A7F9B8D2E2AF@.microsoft.com...
>
>|||They are not constraints, bit difference. They are entries for SQL Server to
hold statistics over
the data in a column of the table. Whether you should create such indexes, o
nly you can tell. It is
likely that your queries can benefit from indexes on some over the columns t
hat these statistics has
been created. OTOH, you most probably don't want to have 118 indexes on a ta
ble (imagine the
slowness of your modifications and storage requirements). Analyze your queri
es, their execution
plans and create indexes that makes sense.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MO" <MO@.discussions.microsoft.com> wrote in message
news:AE7CA10C-4F2C-49D0-B64D-CA40728053CC@.microsoft.com...[vbcol=seagreen]
> Thanks for the info. I looked at a table, it has 118 entries in sysindexes
> for that table. 6 indexes and 112 contraints. I dropped those constraints
to
> see if they are being used, but they all got created in couple of min whic
h
> tells me that they are being used. So, my question is should I convert tho
se
> constraints to an index ?
> What are the cons of keeping these as a constraint and not converting to a
n
> index in the long run ?
> TIA
> MO
> "Kalen Delaney" wrote:
>