Follow by Email

Friday, February 3, 2012

More Take-Aways from TechOnTap

One of the extra benefits of http://www.techontap.org/ 's TechOnTap V1.1 was some book give-aways brought by Grant Fritchey and supplied by RedGate.   Thank you AGAIN. I got SQL Server Statistics by Holger Schmelling.  Everything below is from the book's first chapter discussing the basics, but I add a few of my own comments.

The Setting - Blood Center of Wisconsin - 
I give blood platelets on a regular schedule, about once a month; this benefits 3 Luekemia or cancer patients, helps me by taking the sticky clotting agents out of me and giving me about 2 hours to read.  There are studies that show up to a 30% reduction in heart disease for regular donors of blood.  I encourage all of you to also 1-800-BeAHero.   Only about 5% of the population who can give, do.  Whole blood donations may take only 1/2 hour.  Do it.  Be a Hero. End of speech.

Cool Queries in the book -


This is a very useful table as a row provider... the author credits Itzik Ben-Gan's Inside Microsoft SQL Server 2005 T-SQL Querying [I]

I added Use StatisticsTest; (my first run put it on the Master. Oops.)

Select * from Numbers
where n > 4999990
 reveals: 10 rows
 two columns, the primary key which has a sequential record number starting at 4999991 and a corresponding n column that has the same number as the ID up to our 5000000 in the tenth row.  This might indeed be handy.

What's up with sys.trace_event_bindings ?
http://www.sqlnotes.info/2011/12/09/sql-server-tracing-system-1-dmvs/  this unfortunately did not enlighten me much, as a newbie nor did  http://technet.microsoft.com/en-us/library/ms175073.aspx    Looks to me like since we are using the meta data built in Row_Number in our select, that we have to use this other variable, but I am curious about the order by and the from as sequence using it as b1 and b2...  Perhaps you can enlighten me with an explanation of the invocation of sys.trace_event_bindings in a comment below.
------
I renamed the T0 to Tzero to be more readable; T0 is just too to-looking.

(you can look at the estimated and actual row counts by highlighting a select statement row and <CTRL>+L and then hovering over the operator)  I think the most interesting thing about the select c=2000 is the cached size of the entire query is 24b, and it computes a scalar value for the =2000; but that's probably just me.


                                   

No comments:

Post a Comment