April 22, 2005 at 6:13 am
SELECT DISTINCT(PRG_GROUP_ROLE_APPS.APP_ID) AS app_id, PRG_APP_LINKS.APP_LINK_NAME AS app_link_name, PRG_APP_LINKS.APP_LINK_POSITION AS position FROM PRG_GROUP_ROLE_APPS INNER JOIN PRG_APP_LINKS ON
PRG_GROUP_ROLE_APPS.APP_ID = PRG_APP_LINKS.APP_ID WHERE
(PRG_GROUP_ROLE_APPS.GRP_ROLE_ID IN (SELECT GRP_ROLE_ID FROM
PRG_USER_GROUP_ROLE WHERE USER_ID IN (SELECT USER_ID FROM
PRG_USER_DETAILS WHERE USER_ID = @loginuser OR USER_ID =
'GeneralUser')))ORDER BY PRG_APP_LINKS.APP_LINK_POSITION
i have query above when i execute the above query and clicked estimated execution plan "PRG_APP_LINKS" shows a warning in red color
"Warning: Statistics missing for the table"
I did as below :-
The graphical execution plan suggests remedial action for improving performance. In the case of missing statistics you can right-click the icon, and click Manage Statistics to create the missing statistics.
Yes there is no warning now.
But what happened tecnically here??I am not able to understand?
please somebody explain what was wrong?
Is the query wrong?
or something else?
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
April 22, 2005 at 6:19 am
You probabely know what an index does and what it is used for. Statistics are indexes that sqlserver creates when you run queries so that a sort or a where condition can be executed faster. Usually these statistics are very well maintained by the server but ever once in a while something goes wrong and the stats expire or are deleted.
It is considered a good pratice to force the server to update its stats every once in a while by running this command :
exec sp_updatestats
April 22, 2005 at 7:06 am
From the sounds of things it looks like auto create stats is turned off for the DB.
You can re-enable it with the following script
EXEC sp_dboption '<yourDB>', auto create statistics', 'TRUE'
April 22, 2005 at 7:20 am
Does this mean that it was th eproblem with indexes!
and running above commands will update the indexes!!
Does this has to do any thing with the query i wrote???
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
April 22, 2005 at 7:31 am
good questions.
The query optimizer relies on the statistics to decide whether to choose an index or not, if the statistics are missing then it won't use an index and will probably default to a table scan/clustered index scan (read everything). The same thing applies if the statistics are too far out of date, they will also be ignored.
In order to know whether your query is using the right index you need to have a look at the query plan and see what it says, bookmark lookups with high percentages and table scans are always bad but that's a whole article to fix in itself (which i'm sure people will have done here already)
It's hard to advise on indexes and the like because we don't know your data, or the table structure. The small script that I sent you turns on the automatic creation of statistics but it's also likely that they're not being updated automatically either, the previous post from someone with the sp_updatestats command is definately worth looking at (or enable the auto-update stats in the database).
Hope this helps a little.
April 22, 2005 at 7:38 am
I have auto-update stats on and I still have to recreate the stats manually every once in a while (happenned 2 times last year). And I know many dbas here run sp_updatestats as part of a periodic job.
April 25, 2005 at 1:17 am
This was a very clear reply!
Thanks a lot
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply