Problem solve Get help with specific problems with your technologies, process and projects.

Copying the _WA_sys indexes/statistics

For MS-SQL 7/2000, how do you get the _WA_sys indexes/statistics copied when you copy a database? When we copy a database we do it via a restore. Doing this we lose all _WA_sys_ indexes/statistics, and the server performs horribly. Waiting indefinitely for it to optimize again isn't really an option. If it can't be done during a copy, or I even need to create them later, how would I create their identical equivalent? Or what's the closest I can come?

SQL Server creates statistics when it can not find an adequate index to perform a particular query. It makes this determination based on an algorithm that takes into account frequently used queries. This prevents the engine from creating statistics every time it doesn't find an index, but still allows it to self-optimize when it determines a need. Remember, the presence of statistics that SQL Server builds is an indication of inadequate indexing. Instead of trying to copy stats around, you should be asking yourself why does SQL Server even have to create those stats in the first place? It is creating those stats because you have not created sufficient indexes for the query patterns your users are placing against the server? Stats are not transferred and are transitory things. Your performance problems are solved by creating the indexes you need, instead of relying on SQL Server to finally determine that you need them, and then creating stats to fill the void your lack of indexes have left.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.