SQL Server Insight:Highlights from PASS 2015

The team here at Fortified is pretty passionate about keeping our skills current and staying up to date on new tech and trends.  We’re also passionate about our #SQLFamily!  As such, we attend Pass Summit to keep our skills sharp and reconnect with our SQL Server DBA peers around the country.  2015’s Pass Summit was filled with great content, and as always, great people.  This post breaks down some of my favorite sessions, focusing on the ones I have already begun integrating into my everyday work life.

SQL Sentry Performance Boot Camp

The presentation that I’ve gotten the most out of was the one about how to interrogate the SQL Sentry repository.  For those of you who don’t know SQL Sentry, check it out.  Sentry is a complete server performance monitoring and optimization solution for SQL Server, SSIS, SSRS and Windows.  Modules within Sentry, like Performance Advisor and Event Manager, show you where your performance problems exist for faster resolution.

I have been able to use what I learned in this session to pull datafile size information for one of our clients. By joining tables in the repository with the query below, it is very easy to get a picture of the client’s entire SQL Server environment.  The script I used is below:

select
 case
 when EventSourceConnection.InstanceName IS NULL
 then EventSourceConnection.ServerName
 else EventSourceConnection.ServerName + '\' + EventSourceConnection.InstanceName
 end as SqlServerInstanceName,
 PerformanceAnalysisSqlFile.Name as LogicalName,
 PerformanceAnalysisSqlFile.[FileName],
 PerformanceAnalysisSqlFile.FileTotalSize/1024/1024 as 'Size in GBs'
from
 dbo.PerformanceAnalysisSqlFile
 inner join dbo.EventsourceConnection
 on EventsourceConnection.ID = PerformanceAnalysisSqlFile.EventSourceConnectionID
 inner join dbo.Device
 on Device.ID = EventSourceConnection.DeviceID
order by
 Device.FullyQualifiedDomainName,
 SqlServerInstanceName

Monster Text Manipulation: Regular Expressions for the DBA

I have also begun using the knowledge gained from this session, led by Sean McCown.  I can now take a column list of database names and use regular expressions to create a script rather than manually create one. For example, I have a list of databases I want to change into single user mode. I could change them one by one but instead I can do the following:

--drop temp tables
TEMP_BIGBIRD
TEMP_OSCAR
TEMP_BERT
TEMP_ERNIE
--USING FIND/REPLACE UNDER OPTIONS SELECT USE REGULAR EXPRESSIONS
--FIND: {:i+}
--REPLACE: DROP TABLE \1;
DROP TABLE TEMP_BIGBIRD;
DROP TABLE TEMP_OSCAR;
DROP TABLE TEMP_BERT;
DROP TABLE TEMP_ERNIE;

More Useful SQL Server Sessions

Another two sessions that I thoroughly enjoyed were David Klee and Ed Leighton-Dick’s “How to Build A Virtual Test Lab for Sql Server” and Bob Ward’s “Inside Wait Types, Latches and Spinlocks”.

I believe I am going to use the information from the first to set an environment up where I can test out what Bob Ward was doing.

I was completely taken by Bob Ward’s presentation.  I was able to relate back to my undergraduate studies in Computer Engineering as he demonstrated how to utilize the debugger to get a clearer picture of what spin locks were doing at the processor level. He also showed how latches are not in any way a light-weight lock.  I linked the text above to the Youtube of his presentation, so do check it out.