SQL RNNR

1 DBA's Professional Blog

Seize the Moment

Categories: News, Professional, SSC

Tags: Community, SQL Internals, Training

Comments: 2 Comments

Published on: April 29, 2011

Today I had a bit of regret slap me in the face. That face slap came from participation in a SQL Quiz on twitter that was hosted by Paul Randal (Blog | Twitter). The questions being thrown out there were deep technical internals type of questions. These weren’t necessarily the type of questions that you would see in an interview and were for fun.

I say it was a bit of a face slap because I had an opportunity to attend an Internals training session presented by SQLSkills in Dallas but was unable to attend. It made me wonder how much more I would have been able to answer had I actually attended the course. If you have an opportunity to attend such an event – DO IT!

From the set of questions today, I learned quite a bit. The knowledge and wealth of information that you can gain by attending one of these events has got to be substantially more than what is presented in the measly ten questions posed in these Pop Quizzes that Paul has conducted.

Now I need to find my way into the Bellevue course.

Physical Row Location

by Jason Brimhall

Categories: News, Professional, SSC

Tags: Scripts, SQL Internals, TSQL

Comments: No Comments

Published on: April 29, 2011

SQL Server 2008 has presented us a couple of options to aid in becoming better DBA’s. You can see this evidenced in many ways in the product. A couple of the things that make me think this is the case boils down to two functions that are new in SQL 2008. I learned about these while trying to learn how to do something else. It just so happens that these functions could possibly help me in the other process (I’ll write more about that later when I have finished it).

These new functions are: sys.fn_PhysLocFormatter and sys.fn_PhysLocCracker. The two functions are really very similar. The first of the two does as the name implies and formats the physical location, while the second of the two provides a table output of the location. If you look at the sp_helptext of both, you can see that they only have minor differences.

physlocformat

-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocFormatter
--
-- Description:
-- Formats the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
CREATE FUNCTION sys.fn_PhysLocFormatter (@physical_locator BINARY (8))
RETURNS VARCHAR (128)
AS
BEGIN  
 
 DECLARE @page_id BINARY (4)
 DECLARE @FILE_ID BINARY (2)
 DECLARE @slot_id BINARY (2)  
 
 -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
 --
 SELECT @page_id = CONVERT (BINARY (4), REVERSE (SUBSTRING (@physical_locator, 1, 4)))
 SELECT @FILE_ID = CONVERT (BINARY (2), REVERSE (SUBSTRING (@physical_locator, 5, 2)))
 SELECT @slot_id = CONVERT (BINARY (2), REVERSE (SUBSTRING (@physical_locator, 7, 2)))  
 
 RETURN '(' + CAST (CAST (@FILE_ID AS INT) AS VARCHAR) + ':' +
     CAST (CAST (@page_id AS INT) AS VARCHAR) + ':' +
     CAST (CAST (@slot_id AS INT) AS VARCHAR) + ')'
END

and

physloccracker

-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
-- Cracks the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
CREATE FUNCTION sys.fn_PhysLocCracker (@physical_locator BINARY (8))
RETURNS @dumploc_table TABLE
(
 [FILE_ID] INT not null,
 [page_id] INT not null,
 [slot_id] INT not null
)
AS
BEGIN  
 
 DECLARE @page_id BINARY (4)
 DECLARE @FILE_ID BINARY (2)
 DECLARE @slot_id BINARY (2)  
 
 -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
 --
 SELECT @page_id = CONVERT (BINARY (4), REVERSE (SUBSTRING (@physical_locator, 1, 4)))
 SELECT @FILE_ID = CONVERT (BINARY (2), REVERSE (SUBSTRING (@physical_locator, 5, 2)))
 SELECT @slot_id = CONVERT (BINARY (2), REVERSE (SUBSTRING (@physical_locator, 7, 2)))  
 
 INSERT INTO @dumploc_table VALUES (@FILE_ID, @page_id, @slot_id)
 RETURN
END

When you look at these two functions, you can easily say that they are similar right up until the end where they diverge in functionality. The first casts the data into the “formatted” version, while the cracker simply outputs to a table.

Use of these functions is also quite easy.

usage

SELECT TOP (10)
	yt.*,
	pl.FILE_ID, pl.page_id, pl.slot_id
FROM yourtable AS yt
cross apply sys.fn_PhysLocCracker(%%physloc%%) AS pl;
 
SELECT TOP 10
	sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID], yt.*
	FROM yourtable yt;

These functions can prove to be very helpful in your troubleshooting or dives into Internals. Check them out and enjoy.

SQLSat 83 – Johannesburg

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Community, PASS, Presentations, SQLSaturday

Comments: 1 Comment

Published on: April 28, 2011

Awesome news. I submitted a presentation for SQLSat 83 in Johannesburg South Africa for May 7, 2011. I saw that they were opening up the presentation schedule a little bit to allow for remote presenters and I decided to give it a try.

I got an email notification yesterday that my presentation has been accepted and I will be presenting – woohoo. If you are interested, you can see the presentation schedule here. My presentation will be similar to the last time I presented at SQLSaturday in Salt Lake City – but with some refinements and hopefully a better overall presentation. I will be sharing some of the tools/scripts I use to help learn more about an environment as I inherit a database.

Here’s the fun part – I will be presenting from my home in Utah which is 8 hours behind Johannesburg. At the time of my presentation (12:15 PM there) it will be 4:15AM local for me. That should make for an interesting presentation if I am groggy.

If you are in the area at the time of the event (in Johannesburg), I highly recommend you make it out to some sessions. From looking at the lineup, there appears to be some really good stuff to be presented. As we get closer to May 7th, I will post more information. For now, I need to go clean up my slidedeck and get it submitted.

Parting note: I am glad I was selected for this SQLSaturday. If you read my SQLPeople interview (here), you may recall that I lived in the area for a couple of years. I only passed through Johannesburg when returning home but have always wanted to go back and visit. It has been quite some time and I have yet to make it back. At least now I can do it virtually!

A SQL Experiment – Mentoring

by Jason Brimhall

Categories: News, Professional, SSC

Tags: Community, mentoring, SQL

Comments: 1 Comment

Published on: April 26, 2011

News Flash

The SQL Community is about to get stronger. Why? There is a great new initiative that was recently launched. Steve Jones and Andy Warren are teaming up to conduct this initiative. As they aptly named it, the new initiative is called “The Mentoring Experiment.”

How many times have you wished you had somebody to show you the ropes? Have you ever started a new job and been thrown to the wolves to try and figure things out on your own? Many of us wish we had a mentor at some stage or another in our career – even if it is somebody more experienced with the current systems or applications in your current environment.

What is a mentor?

A mentor is defined as 1) a wise and trusted counselor or teacher, or 2) an influential senior sponsor or supporter. A mentor might also be referred to as a guide, counselor, master, or adviser.

A mentor is a person, in other words, that can be trusted to give you good advice and information. This is a person to whom you should be able to bounce questions off of in order to learn more. This person is somebody with more experience and/or knowledge, in the given domain, than you currently have. And a mentor is a person who is willing to give a little service or volunteers to give of him/herself for the betterment of another individual.

What is the Goal?

Much the same as a mentor should have a Goal in regards to the padawan to help that person get better, Andy and Steve have a goal of improving mentoring in the SQL Server community. There is a plan in place for this experiment. Furthermore, there are multiple stages to this experiment. The first stage is focused on a smaller group of mentors/mentees and the relationship that evolves between the two.

From the information gathered, they hope to be able to provide better information to mentors to determine if maybe there is something that a mentor is missing and needs to learn as well.

You can become involved in this project by submitting a short application. Not everybody will be selected, but you can’t be selected if you don’t try. I hope there will be plenty of community support for this experiment. I look to provide support where I can.

SQLPeople Interview

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Community, SQLPeople

Comments: No Comments

Published on: April 25, 2011

Have you heard about this new project out there called SQLPeople? It is a new (relatively) project that is the brainchild of Andy Leonard. Andy is trying to help the community to get to know each other better than they already do.

Part of this initiative is to conduct interviews and post them to SQLPeople.net on a regular basis. Well, Friday April 22nd was the day that my interview was published. It’s a standard set of questions to be answered by each person on the site. You can read about me and my answers here.

«page 23 of 57»

May 2012
M	T	W	T	F	S	S
« Apr
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

Content

Now Reading

Planned books:

Throne of Fire, the by Rick Riordan
Seeds of Rebellion (Beyonders) by Brandon Mull

Current books:

ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running by Danny Dreyer, Katherine Dreyer
Advanced Marathoning – 2nd Edition by Peter Pfitzinger, Scott Douglas
SQL Server MVP Deep Dives by Nielsen Paul, Delaney Kalen, Machanic Adam, Tripp Kimberly, Randal Paul, Low Greg
A World Without Heroes (Beyonders) by Brandon Mull