Monthly Archives: September 2015

Growing a community is not easy……

…… But it is a lot of fun!

Last Friday I put on a small community event in my home town (#SQLGrillen). It was no SQLSaturday, with just four sessions and only 2 months preparation time, but it was a lot of fun!

Why would you do this? Why for free?!

I’ve heard those two questions a lot recently in the run up to the event itself, but also over the last year since founding the Emsland SQL Server User Group.

It seems to confuse people greatly that I would want to spend my private/personal time on “work”. I have wondered if this is a regional bias (I live in a quite rural part of Germany), or maybe a cultural one (many people I have met in Germany strictly separate work from private life). If the cultural reasoning holds true, then it would explain some of the difficulties of getting attendees for the user group. Either way, I don’t consider it to be “work”. Of course there is an overlap, it is a SQL Server user group and I work with SQL Server. However, I find the social aspect of a user group/event to be almost as important as the technical side. I probably enjoy exchanging ideas/war stories with fellow data professionals more than the technical content – somehow, even though I’m an awkward geek, the human interaction is the source of greater fulfillment.

Why would I do this for free? Why do people run marathons? Why do they join various other clubs in their spare time? Do they get paid to do this volunteer work? I think this goes back to the work/private life separation thing and people not understanding how I can mix the two.

Why would I not do this?

I have made some great friends and had some fantastic experiences as part of the community and hope to “infect” other people with the community virus. I also gained a lot in my career through the things I have learned (that I would never have been able to see in my normal work day) and have gained so many connections that I now don’t need to really look for new opportunities. If I wanted to swap jobs, I just need to explore the ones that get sent my way on a daily basis.

I am not that good that people want to hire me all the time, but being out there in the community makes me visible to companies/recruiters like nothing else.

We want you!

I realise that I am preaching to the choir when writing here (if you are reading a blog on SQL Server, you are already investing time that 99% of others don’t), but I’d still like to suggest that you get yourself involved in the community somehow. There are a wide range of tasks that User Groups, Events or Associations have that need doing and are too much for their current members/helpers to achieve. Even if you don’t want to speak, there is plenty of opportunity to get involved in another way.

So please, for the altruistic side of you, or for the “get ahead in my career” side of you; get in touch with your local User Group or Association and see if you can help out in some way. You will not regret it, I assure you.

P.S. Thanks to Rob Sewell ( b | t ) for the reminder: A few places for you to start would be finding your local PASS Chapter, or if there isn’t one where you are, there are also the Virtual Chapters.

Your presentation design matters more than you think

….. So give the design the attention it deserves!

I have been speaking/presenting at events around Europe for a few years now and have learned a lot about the art of speaking: Repeating questions back to the audience, surpressing “um” and “err” sounds, making eye contact with the audience etc. These are all well communicated best-practices across all industries when it comes to presenting information to both small and large audiences.

Something that is less written and talked about is the style or design of the supporting content.

Many people think of a presentation and the dreaded

  • Death
  • by
  • Bulletpoint
  • presentation

We’ve all been in one of those presentations before, unfortunately this happens more often than it should. The most regular (ab)users of this presentation method are our bosses/managers, who may be great managers but can be the worst presenters out there! Basically throwing up walls of text in PowerPoint and reading the words back to the audience.

After initially falling into this trap (though not quite as text-book fail as just outlined), I have tried to change my presentation style to engage the audience more and use PowerPoint for what it should be used; a support tool for the ideas I am trying to communicate, rather than a display for the content itself. The idea being, the viewer should see the slide and listen to me speaking and merge the two sources of input into their own understanding of the topic. When the audience member reviews the slides at a later date, they should be able to recall the topic and their ideas and continue to benefit from the session.

While trying out these ideas, I have searched online for inspiration and spoke with friends/colleagues about how they approach their presentations. This included some great discussions with Boris Hristov ( b | t ) and Cathrine Wilhelmsen ( b | t ) at the SQLKonferenz 2015 in Darmstadt, Germany. They gave me some ideas to help simplify my slide content and generally improve my presentations and make them more useful for the audience.

After these discussions, Boris obviously saw an opportunity to explore the area of presentation design as a service (I claim the trademark on PDaaS!). If you have ever seen Boris present, you will agree with me that Boris is the perfect person to tackle this idea. His presentations are so clear and to the point, that it seems obvious that he should offer his skills to people and companies that want to stand out from the crowd when presenting. He has proved this with his excellent Pluralsight course on the topic of presentation design.

Boris’ new venture is called 356labs and is focused purely on designing top-notch presentations and training people in presentation design, so that they can produce their own high quality content. Go and take a look at the portfolio page and you will see what I’m talking about. Boris has an eye for presentation design, which takes some complex topics and provides simplistic supporting slides that allow the viewer to concentrate on the topic at hand. So if you are looking for mentoring or training on presentation skills, be sure that 356labs with its top rated international speakers will be able to help you out.

I’m looking forward to what Boris has in store with 356labs and am sure that a lot of people will benefit from the service and advice he has to offer.

Good luck Boris!

Who blew up my TempDB?

Who blew up my TempDB?

Who hasn’t experienced a server all of a sudden running out of TempDB space and seeing the following error message?

Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The difficulty about investigating a TempDB issue like this is that in general the solution chosen to fix a full TempDB is to restart the SQL Server instance. Seeing as how the instance has quite often locked up completely and the customer wants their server to work again ASAP, then a restart is almost inevitable.

A restart is an option to combat the symptom, but not the cause. It also (in)conveniently removes the evidence of the transaction that caused TempDB to fill up in the first place.

To see how to deal with TempDB (or other databases) filling up/filling up the disk it is on please take a look at this blogpost.

Returning to the question at hand: Who filled my TempDB?

One solution I looked into, and have implemented for this exact reason, uses Extended Events.

Extended Events is an event processing framework built inside SQL Server since SQL 2008. It allows you to run very lightweight background monitoring of system events, and in this case can capture information about which command/transaction caused a file growth event to occur.

For a full introduction to Extended Events, please take a look at the 31 Days of Extended Events series from Jonathan Kehayias ( b | t ).

Catch the culprit

To be able to catch the offending process/user, we will create an Extended Events session on the server and the following code does just that:

CREATE EVENT SESSION [Database_Growth_Watchdog] ON SERVER
ADD EVENT sqlserver.database_file_size_change (
 ACTION ( sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.session_nt_username, sqlserver.sql_text )
 WHERE ( [database_id] = ( 2 ) ) -- We filter on database_id=2 to get TempDB growth only
)
ADD TARGET package0.event_file ( SET filename = 'D:\Temp\Database_Growth_Wathdog.xel',
 max_file_size = ( 10 ) )
WITH ( MAX_MEMORY = 4096 KB,
 EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY = 1 SECONDS,
 MAX_EVENT_SIZE = 0 KB,
 MEMORY_PARTITION_MODE = NONE,
 TRACK_CAUSALITY = OFF,
 STARTUP_STATE = ON )
GO
ALTER EVENT SESSION [Database_Growth_Watchdog] ON SERVER STATE = START

Some things to note about the code:

  • We are monitoring for data file and log file growth. The event sqlserver.database_file_size_change fires for any file size change. We do this because we want to be informed of any and all file growth just to make sure we don’t miss anything.
  • If you have multiple data files for TempDB (like you may for a multi-core environment) you will see one event fire for each file that is growing. E.g. You have 4 data files and the database grows, you will see 4 entries in the extended events output.
  • The session is set to flush events to the output file in 1 second intervals (MAX_DISPATCH_LATENCY). This is done to ensure we lose as few entries to the output file as possible. If TempDB fills up, the entire instance can often stop working completely. We want to catch as much information as possible before this happens, so we flush to the output file in very short intervals.
  • We start the session at instance startup (STARTUP_STATE). This ensures we have the event active immediately on server startup. As this is monitoring file growth events, we should remain very lightweight so not have to worry about swamping our system with extended events processing.
  • We limit the output file to 10MB and allow it to deliver to a total of 5 files. This means we have to have 50MB for the files in the output directory and won’t spam the folder with too much data.
  • When the event fires, we collect information about the query/command that caused the file growth to occur. This includes NT username, Hostname, origin database of the query, command text and application name.

The information collected by this session can be vital in pinpointing the cause for the TempDB filling up. However, there is the possibility of false positives in this setup. TempDB may have been almost completely filled by another previous transaction and the transaction causing the growth event is an innocent bystander. This is an unavoidable situation, but needs to be kept in mind when analysing the data. If you don’t catch the exact transaction this way, you are still on the right track.

Analyse the data

Once the data has been collected, we need to load and parse the output files to make sense of what has happened. The following code will parse the xml output that is in the (up to) 5 files.

DECLARE @TraceFileLocation NVARCHAR(255)= N'D:\Temp\Database_Growth_Watchdog*.xel';
WITH FileSizeChangedEvent
 AS (
 SELECT object_name Event,
 CONVERT(XML, event_data) Data
 FROM sys.fn_xe_file_target_read_file(@TraceFileLocation, NULL, NULL, NULL)
 )
 SELECT Data.value('(/event/@timestamp)[1]', 'DATETIME') EventTime,
 Data.value('(/event/data/value)[7]', 'BIGINT') GrowthInKB,
 Data.value('(/event/action/value)[2]', 'VARCHAR(MAX)') ClientUsername,
 Data.value('(/event/action/value)[4]', 'VARCHAR(MAX)') ClientHostname,
 Data.value('(/event/action/value)[5]', 'VARCHAR(MAX)') ClientAppName,
 Data.value('(/event/action/value)[3]', 'VARCHAR(MAX)') ClientAppDBName,
 Data.value('(/event/action/value)[1]', 'VARCHAR(MAX)') SQLCommandText,
 Data.value('(/event/data/value)[1]', 'BIGINT') SystemDuration,
 Data.value('(/event/data/value)[2]', 'BIGINT') SystemDatabaseId,
 Data.value('(/event/data/value)[8]', 'VARCHAR(MAX)') SystemDatabaseFileName,
 Data.value('(/event/data/text)[1]', 'VARCHAR(MAX)') SystemDatabaseFileType,
 Data.value('(/event/data/value)[5]', 'VARCHAR(MAX)') SystemIsAutomaticGrowth,
 Data
 FROM FileSizeChangedEvent;

Please take note of the variable @TraceFileLocation. The example uses a wildcard to allow loading/parsing of multiple files, this is particularly useful if you really do rollover into multiple files.

The results from the query provide a range of information regarding to who the offending process was, what command they had submitted last and a set of information about the client.

So, now we have a starting point to discover who/what is causing TempDB to grow and can discuss this with application owners.

Other ideas

It is extremely simple to to extend the session to monitor all databases and run a separate monitoring solution to inform you of such growth events. In fact, I saw Gianluca Satori ( b | t ) talk about streaming Extended Event data and processing them in near real time at his SQLSat Oslo session. I am certain that this would be a fantastic way of doing some sort of real time monitoring of growth events.

Some homework for you: What could you use this Extended Events session for? How could you modify/improve on it to help with your system monitoring and administration? Let us know in the comments.