Statistics
-
Comments posted to this topic are about the item Statistics
-
Awesome question Ron. This information is extremely useful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Argh, got this one wrong (I knew it as I clicked the button).
Managed to work out what the second field was for and then rushed my answer for the first field. D'oh!
Good question.
Tom
Life: it twists and turns like a twisty turny thing
-
Nice question, Ron. I do not agree with Jason about the importance of the information though - it is interesting to know, but absolutely irrelevant for our jobs. 😉
I do have three minor gripes about the question (sorry!)
First, the wording could have been improved. It took me a very long time before I realized that the "Field 1" and "Field 2" columns were not columns returned by some query, but fragments of the name. It would have been better if the text explicitly stated that the question is about the two hex fragments embedded in each auto_stats name.
Second, Field 1 is not actually the object id of the column, but rather the column id. Columns are identified by a combination of object id (of the table they are part of) and column id (number within the table; numbered from 1 upwards when table is created, though later ALTER TABLE statements can affect the order and cause gaps).
Third, the answer options were in some sort of random order, making it a challenge to not accidentally click wrong. Presenting all three options for Field 1 first, and all three for Field 2 next would have made it easier to get an overview of the options.
However, neither of these gripes is serious enough that they could cause people who know the answer to choose an incorrect answer, so please don't take this as an encouragement to award back any points.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis ...
I full agree.
-
good question.
I thought it couldn't possibly be the object_id of the column
as I looked at some of the columns it would have been creating stats on. - How wrong could I be - realizing the stupid choice of columns for some of the statistics when auto-create stats is on!
-
This was a good question, and makes me wonder if it is a good idea to have auto stats turned on.
When I ran it on one of my servers I got interesting results and makes me wonder if there is more to the values in the fields than what the answer gives.
_WA_Sys_AppName_02883E1E
_WA_Sys_DriveDim_02883E1E
-
Hugo Kornelis (4/16/2010)
Nice question, Ron. I do not agree with Jason about the importance of the information though - it is interesting to know, but absolutely irrelevant for our jobs. 😉😎
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
OK, I so did not understand this question. It wasn't until I read the explanation that I recognized that field1 and field2 were parts of the name!! (Wow, I'm stupid today). I kept trying to figure out what fields from sys.stats they were.
Converting oxygen into carbon dioxide, since 1955. -
This may or may not be the proper place for this background material, but being a long winded individual given to exchanging tidbits technical information with others involved with SQL Server, here I go.
When preparing to scrap an older machine, I reviewed all the files on the hard drive and ran across a text file, describing the naming convention of auto statistics. Unfortunately the note did not indicate the source of the information, and as such ,would not have been a strong support for a QODs correct answer(s). Searching through TechNet, MSDN and Goggle got me nowhere.
As fate would have it, that same week I was privileged to attend an meeting of ONSIG (Ohio North Special Interest Group) at which Paul Randel and Kimberly Tripp gave a most informative presentation. Taking advantage of the after meeting discussion I showed my old note to Paul Randel, and in all honesty he could not verify from memory that the note was correct.
Then much to my surprise Paul posted the proof of how auto statistics were named in his blog. I had the necessary support for a QOD that would perhaps save a piece of Microsoft history lost from public view (obviously still known to some members of the database engine team).
And so this QOD was born.
-
I had no clue on this on. To make matter worse the answers were in my least favorite form: Click all that apply. That makes 2 days in a row for choosing the wrong answers on the QOD.
I'm glad that as Hugo said it isn't info most of us need to do our jobs.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum -
dunnjoe (4/16/2010)
This was a good question, and makes me wonder if it is a good idea to have auto stats turned on.When I ran it on one of my servers I got interesting results and makes me wonder if there is more to the values in the fields than what the answer gives.
_WA_Sys_AppName_02883E1E
_WA_Sys_DriveDim_02883E1E
Sorry to be so late in answering your post, but you could, if you wanted to do:
Using SSMS ...
Select table name
expand
Right click on Statistics
click on "New Statistics"
Then you can create and name your own... for example I did just that and named my new statistic _WA_Something_1F4E99FE.
This NOT to say the way yours were created, but it is a possibility.
-
That's a nice question, thanks Ron.
For what it's worth, I remembered that I read about this naming convention in Inside SQL Server 2005 Query Tuning and Optimization. Page 229. 🙂
Also I would say that object_id in relation to columns is a bit misleading, columns have ordinal positions and column ids local to particular table they are in. In fact, the information in the book is also imprecise, because the first part of name (Field1) is a column_id from sys.columns, not the ordinal position of the column.
I prepared a small script that verifies the above.
if exists(select 1 from sys.tables where name = 'teststats')drop table teststats
go
create table teststats(col1 int, col2 int, col3 int, col4 int)
go
--no stats on columns yet
exec sp_helpstats 'teststats'
go
--creates stats automatically
select * from teststats where col3 = 1
go
select column_id, name from sys.columns where object_id = object_id('teststats')
--column id 3
exec sp_helpstats 'teststats'
go
alter table teststats drop column col2
go
select column_id, name from sys.columns where object_id = object_id('teststats')
--still column id 3
exec sp_helpstats 'teststats'
go
--drop statistics
declare @sql nvarchar(200)
set @sql = N'drop statistics dbo.teststats.' +
(select top 1 name from sys.stats where object_id = object_id('teststats'))
exec sp_executesql @sql
go
--no statistics on table
exec sp_helpstats 'teststats'
go
--recreate statistics
select * from teststats where col3 = 1
go
--still column id used
exec sp_helpstats 'teststats'
go
drop table teststats
go
Regards
Piotr
...and your only reply is slàinte mhath
-
Piotr.Rodak
Inside SQL Server 2005 Query Tuning and Optimization. Page 229.
Thanks for that info, alas I could not use it to support the answers, since it would not be avaiable for the majority of those who attempted to answer. That is why I went ahead and use Paul Randel's blog posting, which was readily available on the internet.
My goal was to keep the information in the realm of publicly available knowledge, and you have helped to achieve that goal through your link to Karen Delaneys book, for that I thank you.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply