Archive for the ‘PASS’ Category

TSQL Tuesday #96: Folks Who Have Made a Difference

November 14, 2017 1 comment

T-SQL Tuesday
I started working as a SQL Server DBA in November of 2007. In November of 2010, I attended my second PASS Summit in Seattle. At the time I was working for a large insurance company, and our Microsoft sales rep had scheduled a dinner for my team at 6pm. The afternoon of the dinner, I went to see Paul Randal (b|t) do a 90-minute Spotlight Session called DBA Mythbusters scheduled until 6:15. About 15 minutes into Paul’s session, I texted my boss to let him know I would be late to dinner. The information Paul presented and the way in which he presented made me not want to miss a minute of the session. Walking out of the Convention Center that night, I remember thinking, “I want to do that! I want to teach people about SQL Server!”

The following March, I attended my first SQL Saturday in Chicago. For the session after lunch, I wanted to see Brent Ozar (b|t) present about SQL Server storage. By the time I got to the room for Brent’s presentation, it was SRO. Two doors down, Jes Borland (b|t) was giving a presentation called Make Your Voice Heard! In it, Jes provided advice on blogging, presenting, and leveraging social media to increase visibility within the community.

Attending Jes’s presentation introduced me to the force of nature known as Jes Borland. Her unbridled enthusiasm for the SQL community gave me the motivation to start teaching people about SQL Server. Within a year I started this blog and started presenting at PASS Local Chapters. I am blessed to have become friends with Jes. She continues to inspire me both professionally and personally.

In April of 2011, I attended SQL Skills Immersion Event on Performance Tuning and Optimization – Part 1, taught by Paul Randal and Kimberly Tripp. Since then I have been fortunate to become friends with the two of them. Their involvement in the community never ceases to amaze me. To close I will share my favorite story about the strength of the SQL community.

I attended PASS Summit 2015 with a co-worker who was a first timer. At the Tuesday Welcome Reception, I saw Paul and Kimberly across the room. My colleague and walked over, and as we were waiting to say hello, Paul turned, looked at my colleagues badge and said, “Michael, first timer, how are you! I’m Paul Randal!” Paul welcoming nature and openness to new members of the community is something that I try and emulate on a daily basis.

I volunteer with PASS because I will never be able to give back as much as I’ve gotten. Paul and Jes are two individuals that have given me an enormous amount through the years and I thank them for it.

Categories: PASS, SQL Server Tags: , ,

Monitoring and Alerting for Availability Groups and Why I Love PASS Summit

October 31, 2016 Leave a comment

I have returned from my eighth PASS Summit and, as in years past, they just keep getting better.  I am amazed and humbled that I am a part of the PASS community.  PASS has given me the opportunity to meet and learn from many of the best and brightest in the SQL world.  When I meet first-timers, I tell them to talk to people, no matter who they are.  If you share an interest, they will share with you.  If you have never been to PASS Summit, I recommend it.  If you cannot make Summit, get involved with your local PASS Chapter or SQL Saturday.  Virtual Chapters are another great way to find out what PASS has to offer.

One of the highlights of this year’s Summit was Shawn Meyers’ (t) presentation on Monitoring and Alerting of Availability Groups. (You will need to be logged in to the PASS website to access the link.)  I have worked with Availability Group since its release in SQL Server 2012 and Shawn provided the best solution for monitoring and alerting.

Shawn suggested implementing three alerts:

  • 1480 – AG Role Change
  • 35264 – AG Data Movement Suspended
  • 35265 – AG Data Movement Resumed

Additionally, Shawn provided an MSDN link with recommendations for using Policy-Based Management (PBM) to monitor AGs.  The custom policies monitor the recovery time objective (RTO) and recovery point objective (RPO) for your availability groups.  Microsoft recommends 600 seconds, or 10 minutes, for RTO, and 3600 seconds, or 60 minutes, for RPO. You can set your own values when defining the policy conditions.

I have scripted out the PBM conditions, policies, and SQL Server Agent alerts.  The conditions and policies can be used as-is, because they use system schedules.  The alerts need to updated to replace the TestOperator with an SQL Agent operator of your own.

I hope you can make use of these scripts.  And I encourage you to get involved with PASS. You will be amazed at what it can do for you.


Presenting – The Odyssey Continues

As I’ve mentioned before, I set a goal at the beginning of the year to average a presentation a month in 2014. My presentation, Designing a Recovery Strategy, at the Wisconsin SQL Server User Group this month put me halfway there.

In June, I am presenting Designing a Recovery Strategy again at SQL Saturday #307 in Iowa City on the 7th. And then, for the first time ever, I will be presenting twice on the same day at SQL Saturday #286 in Louisville on the 21st. I’ll be doing Designing a Recovery Strategy and Interrogating the Transaction Log (now with 2014!) If you are close to either event, I highly recommend attending. It is a great opportunity to get a free day of training and meet other members of the SQL community.

I close out the month by presenting Interrogating the Transaction Log at MADPASS June 25th. It has been a wild ride, but I’ve enjoyed every minute of it. Presenting is a great way to give back to the SQL community and I learn more than I thought possible every time I put a presentation together.

sp_AutomateDBRestore – Now With Table-Valued Parameters!

After several weeks of procrastination, I have finished the latest version of sp_AutomateDBRestore. The previous versions of the procedure built the restore statements using sys.database_files for the restored database, msdb.dbp.backupset, and msdb.dbo.backupmediafamily. This process works great if the instance is up and running, but if the instance is unavailable it won’t work.

To handle this circumstance, I have added a table-valued parameter to the procedure allowing a list of backup files and backup file types to be passed in. This way, you can generate a restore statement as long as you have the backup files available. The proc uses RESTORE FILELISTONLY and RESTORE HEADERONLY statements to retrieve the physical and logical file names. Additionally, I’ve added parameters @backupfilepath and @logfilepath to allow a restore of the database to a different physical file than the source database. Here is the code to create a user-defined table type, followed by a call using the new parameters:

USE [master]

CREATE TYPE [dbo].[backupfiletype] AS TABLE(
	[backupfilename] [varchar](255) NULL,
	[backupfiletype] [varchar](30) NULL
DECLARE @backuptvp AS backupfiletype

INSERT INTO @backuptvp

DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_automateDBRestores]
		@sourcedbname = multifile,
		@restoredbname = multifile,
		@droprestoredb = 0,
		@noexec = 1,
		@datafilepath = 'C:\testrestore\',
		@logfilepath = 'C:\testrestore\',
		@TVP = @backuptvp

SELECT	'Return Value' = @return_value


You can find the code for the updated procedure at my Presentation Slides and Scripts page under Wisconsin SQL Server User Group. As usual, feel free to leave any questions or comments in the comments section for the post.

Categories: PASS, SQL Server, T-SQL Tags: , ,

SQL Saturday #291

Speaker dinner at the Schnitzel Platz (MarkV is an instigator) -Thanks to Dave Mattingly for the photo

Speaker dinner at the Schnitzel Platz (MarkV is an instigator) -Thanks to Dave Mattingly for the photo

SQL Saturday #291 took place this past Saturday at DeVry University in Addison, Illinois. It was my eighth SQL Saturday, and it was the first one I helped run. I want to thank Bill Lescher (t), Wendy Pastrick (b|t), Bob Pusateri (b|t), Jamie Samsel, and Andy Yun (b|t) for all of their hard work. I had a great time and have heard positive feedback from all of the attendees I’ve spoken with.

The hard work these folks put in wouldn’t mean anything without the speakers who volunteer their time and pay their own way to attend these events. I especially want to thank Jim Dorame (b|t), Merrill Aldrich (b|t), Eric Boyd (b|t), and Grant Fritchey (b|t). With only 15 minutes’ notice, Jim stepped in to replace a speaker who did not arrive, moving from his scheduled noon slot to 9:15. Then Jim, Merrill, Eric, and Grant held a PowerShell panel in Jim’s original slot. Their willingness to take on extra responsibility epitomizes the SQL community for me, and went a long way to making the event such a success.

Finally, I want to thank everyone who attended SQL Saturday #291. Without you, the SQL community would not exist. I spoke to a number of first-time attendees, and I hope they all come back again. The SQL community has given me more than I could ever give back. I’ve made great friends, learned from world-renowned experts, and grown personally and professionally. I met a current co-worker at SQL Saturday Chicago last year and my work in the community had a lot to do with my getting that job.

If you attended SQL Saturday and got something out of it, consider giving back. Presenting, blogging, volunteering, and answering questions on forums are all great ways to give back. If you haven’t already, register for your local PASS user group. If you don’t have a local user group, consider starting one. I’ve run the Chicago SQL Server User Group since July of 2012 and have had a blast. Feel free to contact me if you have any questions.

Strange Results from sys.fn_physlocformatter

October 17, 2013 2 comments

I’m writing from Charlotte, North Carolina where I’m attending my fifth PASS Summit. I hope to write a series of posts recapping my experiences, but I wanted to write about something I learned in Paul White’s (b|t) pre-conference session, Understanding the Optimizer and Interpreting Execution Plans. The session was amazing and I think I’ll spend the next year going through his scripts.

If you’ve read my blog you know I love internals. Paul introduced me to a function called sys.fn_physlocformatter. In SQL Server 2008 and beyond, %%physloc%% is a virtual column that returns the file, page and slot of each row of a result set in byte-reversed, hex format. That means it is hard to read. If you don’t believe me, here is an example of the query and its result:


SELECT %%physloc%% AS [physloc],*
FROM MYTestTable


physloc results


fn_physlocformatter takes that hex value and formats it into a colon-delimited value that is much more readable. Here’s the query above, replacing %%physloc%% with fn_physlocformatter:

SELECT sys.fn_PhysLocFormatter(%%physloc%%),*
FROM MYTestTable


fn_physlocformatter results


After Paul showed us the function, I did a search to get more information and came across this post. Using the example provided, I wrote the following query to return a count of the number of rows written to each page:

SELECT SUBSTRING(sys.fn_PhysLocFormatter(%%physloc%%),4,(CHARINDEX(':',sys.fn_PhysLocFormatter(%%physloc%%),4) - 4)) [Page ID], COUNT(*) AS [Row Count]
FROM MYTestTable
GROUP BY SUBSTRING(sys.fn_PhysLocFormatter(%%physloc%%),4,(CHARINDEX(':',sys.fn_PhysLocFormatter(%%physloc%%),4) - 4))
ORDER BY [Row Count]

The sample code from the link above contains code to insert nine rows to MYTestTable2 at a time. I’ve listed the table definition and insert code below.


NAME CHAR(500), 


Because the rows are 1008 bytes long, seven rows will fit on one 8kb data page. After the initial insert of nine rows, I expected to see a page containing seven rows followed by a page with two rows. Here are the results after the initial insert:


Initial Insert Results


I continued running the nine inserts, to confirm that my code was working as expected. And for the next five runs, it did. The strange results didn’t show up until the seventh run, where I saw this:


strange results


After the seventh insert, something was causing seven pages to be allocated each with a single row inserted. This didn’t make any sense to me, so I continued investigating. I’ll show you the results of those investigations in my next post, sys.fn_physlocformatter – The Mystery Deepens!

T-SQL Tuesday #42 – The Long and Winding Road

This month’s T-SQL Tuesday is hosted by Wendy Pastrick (b|t) and the topic is change in our work lives. Thanks to Wendy for hosting and to Adam Machanic (b|t) for putting this installment of T-SQL Tuesday together.

Fifteen years ago, I was working in a bookstore at the University of Illinois-Chicago. I’d graduated a year earlier with a degree in history. I wasn’t sure what I wanted to do, but I was confident it didn’t involve grad school or retail.

In November of 1988, I enrolled in the Computer Career Program at DePaul University. The program was geared to turning non-technical people into mainframe programmers. It had been around since the early ’80’s, but saw a surge in enrollment during the run up to Y2K. After three months, I was certified to code in COBOL and JCL and these skills landed me my first real job*.

Where I started out...

Where I started out…

I spent the next seven years writing code for the mainframe and learning to work with DB2 databases. I was fortunate to learn from a number of extremely talented programmers and DBAs during that time. I got my first exposure to internals as well, a love that has stuck with me to this day.

In 2007, a position opened on the SQL Server DBA team at my company and I made the switch. Once again, I was fortunate to work with a number of experienced and talented people who took the time to teach me what they knew.

About that time, I started attending the local PASS user group and began to meet others who were as passionate about databases as I was. I went to my first Summit in 2009 and this year will be my fifth. I am continually amazed at the willingness of PASS members to share their experience and knowledge. I took over the Chicago SQL Server User Group (b|t) last July with Aaron Lowe (b|t), and it has been great. I’ve presented at three user group meetings and two SQL Saturdays in the past year. Meeting members of the local group and getting the chance to work with other chapter leaders and mentors has expanded my horizons.

In October of last year, I started a new job at a new company. It has been a good experience, and though there have been challenges I am working to view those challenges as opportunities.

Going forward, my plan is for my path to lead me to expanding my knowledge of SQL Server. I want to keep presenting to share what I know with others. The path I’m on has given me an opportunity to grow personally, professionally and intellectually, and I want to provide those things to others.

Where I am today...

Where I am today…

* A job where I wasn’t paid hourly