How to TEST if statistics should be updated.
-
Here's an interesting issue I just encountered with a client's database:
A posting function in a VB app usually takes around 30 seconds to run. The client reports that the function seems to be taking longer and longer, and now approaches the 10 minute mark. Since it is accessing the SQL server for the data, I assumed that it was either an indexing issue, or that the statistics might be out of date. The clients DBA had set up a plan to reindex the database once a week, and assumed that that also updated statistics, but I'm not sure that is true or not.
running sp_createstats @fullscann='fullscan' seemed to fix and/or hide the issue...the posting now takes 30 seconds again.
Now the meat of the question...is there a way to query a database and find out if the statistics might be old/out of date?
The database is small; under 80 meg in total, and when i restored a backup of their database locally, the Auto Create Statistics option was checked in the database.
Is ther a way to query the db on the worthyness of the statistics, or did the steps i took just hide a potential issue?
Thanks any and all who can provide me witha clue.
Lowell
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Is Auto Update statistics turned on?
It's an interesting question and I thought that reindexing would fix the statistics as well with a new sampling, but perhaps not.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
DBCC DBREINDEX does update the statistics on the indexes being rebuilt.
Perhaps you need to review your indexing strategy. Also, are you sure that all indexes on all tables are being reindexed?
-
unfortunately, the db is in another state, and administered by a dba there; I was trying to diagnose issues by remote;
both Auto Update Statistics and Auto create Statistics are turned on in the restored version of this database, by looking at the Properties>>Options in EM after I restored a backup from the client.
Supposedly they used the Database Maintenance Wizard to create the job to do the reindex; over the phone i verified that there is a job scheduled using this format:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 3415B2E6-933F-4361-8517-EA23F6D807E0 -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '
So the extended stored procedure seems to be rebuilding indexes.
How about this...is it possible that the indexing plan is cached, and only refers to indexes that existed say, a year ago? and if I've added columns and indexes since then, so only some of the indexes get rebuilt?
I'm at a loss as to why the sp_createstats "seemed" to fix the issue, but should not have been required.
Lowell
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Can you check whether structures of tables have been altered, for example adding new column, and the query may use wrong indexes and statistics.
Is Auto Create statistics turned on?
sp_createstats creates single-column statistics for all eligible columns for all user tables in the current database. Columns already having statistics are not touched (for example, the first column of an index or a column with explicitly created statistics).
-
I have would have an another approach.
For me in a small db of 80 MB a query of 30 sec or even minutes is inimaginable. Something must be wrong out there.
- Therefore did you check in the profiler if you are using the indexes correctly or it is making a table scan?
- Do you have clustered indexes on your table?
- Did you check the table fragmention (dbcc showcontig with all_indexes)?
- Do you have enough RAM in your machine?
- Do you experience any locks against the table(s) during your app is running?
Bye
Gabor
Bye
Gabor -
We have a small DB 25G. I have found that I get better performance when I update the statistics. so much so, that I now update every night.
Curtis Smith
Application Dev. Mgr
DBA
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
I guess I should clarify; The Process in VB takes 30 seconds; the process runs hundreds/thousands of similar queries,one for each transaction it tries to post to the accounting tables, and the sum total time of all those transactions takes about 30 seconds;
I created a trace in profiler, ran this 'posting' function, and ran the trace thru the index tuning wizard; it suggested no indexes, and all queries used an existing index. I'm looking into this further, but thanks all who replied with suggestions.
Lowell
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
How is the reindexing being done?
dbcc reindex updates statistics but if you are using indexdefrag I believe the statistics are not updated. Might be worth looking into if you did not write the weekly process.
Trevor Hampson
DBA -
On this issue of index, what are the implications of having duplicate indexes. I have some on my inherited server and I want to gwet rid of the duplicates.
However, I want to know what the implications will be.
Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply