June 17, 2011 at 10:02 am
Hello to all,
First off thanks for your time in reading this and if any reply or guidance given its much appreciated. 🙂
Well i have been working in my environment for a little time and in a position to where i can finally try to look at the statistics of the SQL databases and try to see if this is any indicator that i can use for. I am pretty sure it is but here is the problem. I have very little knowledge in this area and with that said you see why i have come to the knowledge of the dependable of SSC.. 😀
* What am i looking for with the use of statistics? I run the commands DBCC update stats and the likes but to give an analogy its like sitting in the cockpit of the space shuttle or a Boeing 767...I see all this information given to me but don't know what i am looking at and/or how its useful to me. I also understand that the use of statistics is a book all on its own.
I know that is a loaded question and a person can type for days i am sure but if i can get a bit of guidance and ideas how to use the information that comes from the queries when pulling statistics it would be greatly appreciated.
Thanks
DHeath
DHeath
June 17, 2011 at 10:12 am
Have a look at http://www.sqlservercentral.com/Forums/Topic1126580-146-1.aspx#bm1127227
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 17, 2011 at 10:15 am
DHeath (6/17/2011)
* What am i looking for with the use of statistics? I run the commands DBCC update stats and the likes but to give an analogy its like sitting in the cockpit of the space shuttle or a Boeing 767...I see all this information given to me but don't know what i am looking at and/or how its useful to me. I also understand that the use of statistics is a book all on its own.
Performance statistis are used by query optimizer to generate a reasonable execution plan.
Performance statistis describe how data is distributed in a particular table.
Imagine a table called "MyFriends" where one of the columns is the gender of the friend. Let's say you have 1,000 friends and you have set a constraint where Gender can be either "M" or "F". You also have an index on Gender.
For some particular reason happens that you have 990 Females and 10 Males in your MyFriends list; also happens that you write a query to retrieve your Male friends.
If there are NO statistics on such table query optimizer assumes normal distribution therefore it "thinks" you have 500 Male friends and 500 Female ones then execution plan will call for reading all 1000 friends, filter the Males and return those to you.
If there are up-to-date statistics query optimizer knows there are only 10 rows out of 1000 to be retrieved so execution plan will hit index on Gender column and retrieve your 10 rows.
Your basic concern about statistics is to have up-to-date statistis, which in the case of SQL Server you can ask the rdbms to keep up-to-date automatically.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 17, 2011 at 10:44 am
Thanks for the responses... i now i have a better idea from the reading posted to help increase my knowledge in this area (the friend analogy rocked...thanks a TON). I have gathered that the stats help with the way the code is ran to better assist in the query plan that would be shown to let a person "visually" understand whats happening with the query plan when its executed. The one can make adjustments to there code accordingly to try and get things to run at its fastest or best path.
For some reason i feel like i am still off base but not too sure :ermm:... o well.... it is what it is..
once again thanks for your time...
DHeath
The red-gate book helped me a great deal here..Thanks for the link...GREAT SOURCE
http://www.red-gate.com/products/dba/sql-monitor/entrypage/statistics-ebook
DHeath
June 17, 2011 at 12:26 pm
DHeath (6/17/2011)
Thanks for the responses... i now i have a better idea from the reading posted to help increase my knowledge in this area (the friend analogy rocked...thanks a TON). I have gathered that the stats help with the way the code is ran to better assist in the query plan that would be shown to let a person "visually" understand whats happening with the query plan when its executed. The one can make adjustments to there code accordingly to try and get things to run at its fastest or best path.
The job of Query Optimizer is to generate a "query plan", which is the road map SQL Server engine will follow to execute the particular query. Query Optimizer builds the query plan based on statistics.
Bottom line is, query plan is there because with no query plan SQL Server engine cannot execute the query.
On top of it, Microsoft added tools to show how query plan looks like so to allow DBA to troubleshoot performance issues and verify how changes on code, data, data structure, objects and/or statistics affect a particular query plan.
Hope this clarifies a little
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 19, 2011 at 10:17 pm
Please refer the below link too:
http://sqlzealot.blogspot.com/search/label/SQL%20server%20-%20Statistics
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply