_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 better
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 the CREATE STATISTICS
command.
> SOmewhere I read that if you see lot of
> these constraints, it is better to create 'em as permanent indexes for better
> performance.
That is not necessarily the case. First, these are not indexes, it is statistics. 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 analyze what indexes your
queries would benefit from, and it is possible that some of these auto created 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 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 better
> 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:
> > 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 the CREATE STATISTICS
> command.
>
> > SOmewhere I read that if you see lot of
> > these constraints, it is better to create 'em as permanent indexes for better
> > performance.
> That is not necessarily the case. First, these are not indexes, it is statistics. 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 analyze what indexes your
> queries would benefit from, and it is possible that some of these auto created 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 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 better
> > performance.
> >
> > 1) Is that true ?
> >
> > 2) How do I extract the ddl for these constraints ?
> >
> > TIA
> > KNM
>|||> 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...
> 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:
>> > 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
>> the CREATE STATISTICS
>> command.
>>
>> > SOmewhere I read that if you see lot of
>> > these constraints, it is better to create 'em as permanent indexes for
>> > better
>> > performance.
>> That is not necessarily the case. First, these are not indexes, it is
>> statistics. 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
>> analyze what indexes your
>> queries would benefit from, and it is possible that some of these auto
>> created 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 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
>> > better
>> > performance.
>> >
>> > 1) Is that true ?
>> >
>> > 2) How do I extract the ddl for these constraints ?
>> >
>> > TIA
>> > KNM
>>|||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...
> 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:
>> > 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
>> the CREATE STATISTICS
>> command.
>>
>> > SOmewhere I read that if you see lot of
>> > these constraints, it is better to create 'em as permanent indexes for
>> > better
>> > performance.
>> That is not necessarily the case. First, these are not indexes, it is
>> statistics. 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
>> analyze what indexes your
>> queries would benefit from, and it is possible that some of these auto
>> created 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 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
>> > better
>> > performance.
>> >
>> > 1) Is that true ?
>> >
>> > 2) How do I extract the ddl for these constraints ?
>> >
>> > TIA
>> > KNM
>>|||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...
> > 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:
> >
> >> > 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
> >> the CREATE STATISTICS
> >> command.
> >>
> >>
> >> > SOmewhere I read that if you see lot of
> >> > these constraints, it is better to create 'em as permanent indexes for
> >> > better
> >> > performance.
> >>
> >> That is not necessarily the case. First, these are not indexes, it is
> >> statistics. 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
> >> analyze what indexes your
> >> queries would benefit from, and it is possible that some of these auto
> >> created 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 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
> >> > better
> >> > performance.
> >> >
> >> > 1) Is that true ?
> >> >
> >> > 2) How do I extract the ddl for these constraints ?
> >> >
> >> > TIA
> >> > KNM
> >>
> >>
>
>|||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, only you can tell. It is
likely that your queries can benefit from indexes on some over the columns that these statistics has
been created. OTOH, you most probably don't want to have 118 indexes on a table (imagine the
slowness of your modifications and storage requirements). Analyze your queries, 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...
> 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...
>> > 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:
>> >
>> >> > 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
>> >> the CREATE STATISTICS
>> >> command.
>> >>
>> >>
>> >> > SOmewhere I read that if you see lot of
>> >> > these constraints, it is better to create 'em as permanent indexes for
>> >> > better
>> >> > performance.
>> >>
>> >> That is not necessarily the case. First, these are not indexes, it is
>> >> statistics. 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
>> >> analyze what indexes your
>> >> queries would benefit from, and it is possible that some of these auto
>> >> created 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 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
>> >> > better
>> >> > performance.
>> >> >
>> >> > 1) Is that true ?
>> >> >
>> >> > 2) How do I extract the ddl for these constraints ?
>> >> >
>> >> > TIA
>> >> > KNM
>> >>
>> >>
>>sql