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:
when EventSourceConnection.InstanceName IS NULL
else EventSourceConnection.ServerName + '\' + EventSourceConnection.InstanceName
end as SqlServerInstanceName,
PerformanceAnalysisSqlFile.Name as LogicalName,
PerformanceAnalysisSqlFile.FileTotalSize/1024/1024 as 'Size in GBs'
inner join dbo.EventsourceConnection
on EventsourceConnection.ID = PerformanceAnalysisSqlFile.EventSourceConnectionID
inner join dbo.Device
on Device.ID = EventSourceConnection.DeviceID
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
--USING FIND/REPLACE UNDER OPTIONS SELECT USE REGULAR EXPRESSIONS
--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
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.