Archive

Archive for March, 2014

Upcoming Presentations

March 24, 2014 Leave a comment

I'm a presenting fool!

I’m a presenting fool!


I am continuing to work toward my goal of averaging a presentation a month this year. So far I’ve presented Designing a Restore Strategy at I-380 PASS, QCPASS, and IndyPASS. I’m presenting it at the Chicago SQL Server User Group and SQL Saturday 291 – Chicago in April. The presentation centers around my stored procedure sp_AutomateDBRestores, which I hope to have documented fully this week. You can download the code here. If you are interested in attending the user group, you can RSVP here. If you haven’t registered for SQL Saturday Chicago, I recommend it highly. It is a wonderful opportunity to get a day’s worth of free training and network with your peers.

If you are looking for additional training, take a look at the pre-conference sessions being offered by Adam Machanic (b|t) and David Klee (b|t). Adam is the author of sp_whoisactive, which I can’t recommend highly enough, and is presenting Better Performance Through Parallelism. David is an expert in virtualizing SQL Server, so it is fitting that he is presenting Virtualization for SQL Server DBAs.

sp_exec_whoisactive

March 17, 2014 Leave a comment

I’ve posted a procedure that I wrote some time ago to make Adam Machanic’s sp_whoisactive easier to execute. It dumps the results of sp_whoisactive to a temporary table and selects the contents of that table. I’ve set EXEC sp_exec_whoisactive as one of my keyboard shortcuts in Management Studio. This makes it really easy to execute the proc repeatedly. If you aren’t using sp_whoisactive, I would advise stopping what you are doing and downloading it now. It is the most useful piece of code I’ve ever come across.

USE [master]
GO

--Drop procedure if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_exec_whoisactive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_exec_whoisactive
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Frank Gill
-- Create date: 2014-03-17
-- Description:	This procedure executes Adam Machanic's sp_whoisactive, dumps the results to a temp table, and then selects the contents
--				'EXEC sp_whoisactive' can be set up as a keyboard shortcut to allow easy execution of the proc 
-- =============================================
CREATE PROCEDURE sp_exec_whoisactive
AS
BEGIN

--Drop temp table if it exists
IF OBJECT_ID('tempdb..#WhoIsActive') IS NOT NULL 
BEGIN
	SELECT 'Dropping'
	DROP TABLE #WhoIsActive
END

--Create temp table to hold the results of sp_whoisactive
CREATE TABLE #WhoIsActive
([dd hh:mm:ss.mss] VARCHAR(20)
,[dd hh:mm:ss.mss (avg)] VARCHAR(20)
,[session_id] SMALLINT
,[sql_text] XML
,[sql_command] XML
,[login_name] SYSNAME
,[wait_info] NVARCHAR(4000)
,[tran_log_writes] NVARCHAR(4000)
,[CPU] VARCHAR(30)
,[tempdb_allocations] VARCHAR(30)
,[tempdb_current] VARCHAR(30)
,[blocking_session_id] SMALLINT
,[blocked_session_count] VARCHAR(30)
,[reads] VARCHAR(30)
,[writes] VARCHAR(30)
,[physical_reads] VARCHAR(30)
,[query_plan] XML
,[used_memory] VARCHAR(30)
,[status] VARCHAR(30)
,[tran_start_time] DATETIME
,[open_tran_count] VARCHAR(30)
,[percent_complete] VARCHAR(30)
,[host_name] SYSNAME
,[database_name] SYSNAME
,[program_name] SYSNAME
,[start_time] DATETIME
,[login_time] DATETIME
,[request_id] INT
,[collection_time] DATETIME)
		

--Execute sp_whoisactive with #WhoIsActive set as the @destination_table parameter	
EXEC master.dbo.sp_WhoIsActive
@get_plans = 2, 
@get_outer_command = 1, 
@get_transaction_info = 1, 
@get_avg_time = 1, 
@find_block_leaders = 1,
@destination_table = #WhoIsActive

--Select the contents of #WhoIsActive, ordered by the blocked_session_count
--The ORDER BY clause can be removed or modified as necessary
SELECT * FROM #WhoIsActive
ORDER BY blocked_session_count DESC

END

Adam is teaching an all-day pre-conference seminar entitled Better Performance Through Parallelism on Friday, April 25th. It is the day before SQL Saturday #291 Chicago. This is a great opportunity to get a day’s worth of training from the author of sp_whoisactive for a very reasonable price. You can get more information and register here.

Categories: Uncategorized

Working From Home – Two Weeks In

March 7, 2014 Leave a comment

I’ve been working for Apparatus for three weeks now, the last two of them from home.  The work is interesting and I’ve finally achieved my goal of getting an Availability Group set up on my laptop.  Plus, I know more about SPNs than I thought possible. And working from home has been a great experience.

The standing desk I built using plans from this site has worked out really well.  I was afraid it wouldn’t be tall enough, but it has worked out perfectly.  

My standing desk

My standing desk

I have also been eating healthier.  More fruit, fewer fries.  Hopefully, the combination of those things will let me get closer to my fighting weight, as seen here.

The other thing that has made it fun is I’d forgotten how much I love listening to music through speakers.  That, and the fact I have the time and ability to listen to music on vinyl.  

Some many albums, so little time...

Some many albums, so little time…

I even listened to the first album I bought with my own money the other day.  It sounds as good as it did in 1981.

Have you seen Junior's grades?

Have you seen Junior’s grades?

My thanks to Kyle Neier (b|t) and everyone else at Apparatus for bringing me on board.  And thanks to Jes Borland (b|t) and everyone else who has offered advice about working from home.