Solved

_WA_SYS statistics created - should you index on these as well

Posted on 2006-10-26
2
1,558 Views
Last Modified: 2008-01-09
We have a number of WA_SYS statistics created (auto on) against tables. Is it wise to create indexes for these fields listed in the WA_SYS if you dont have them setup already. This is a DSS environment.

Thnaks

Paul
0
Comment
Question by:Paul_Blackler
2 Comments
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 200 total points
ID: 17814211
I wouldn't blindly go create an index just because the system did it.  I would rather do some investigating and (SQL) profiling to see where my perf can be improved and create indexes there if necessary.
0
 
LVL 10

Accepted Solution

by:
AaronAbend earned 300 total points
ID: 17814602
Be careful - statistics are used by the optimizer to choose the path for the query - they are not actually used to execute the query itself.  

A lot of small, low cardinality columns will be scanned by the optimizer and stats will be created. You do not want indexes on these columns because their low cardinality (low number of values) means an index will not help much (imagine a table of states that also includes the country but there is only one value for country - indexing country does not help your query performance).

Check the strategies and base your indexes on what the strategies are doing. If you see time consuming tablescans, definitely create indexes to prevent that from happening.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question