Presentation Nerves

My previous post on interviews and a number of conversations this year inspired me to write this post. I am lucky enough to have been selected to speak at numerous events over the past few years and I am really lucky because I thoroughly enjoy doing them. The feedback I receive from those sessions has been wonderful and it seems that in general most people really enjoy them.

This leads to some misconceptions though. Recently people have said to me “Oh I am not like you, I get far to nervous to do a session” and also “I am so glad that you get just as nervous as me before presenting I thought it was just me” even though I have blogged about this before. I think it is important for newer speakers as well as more established ones to know that more presenters than you realise get very nervous before they speak.

Many don’t publicise this (which is fine) but I will. I get nervous before I speak. I know that it doesn’t show when I start my presentation but it is there. My stomach does back flips, my hands shake, I forget to bring things to the room. I worry that I will make a catastrophic mistake or that I’ll open my mouth and nothing will come out.

It’s ok. It doesn’t last very long, it’s gone at the moment I start speaking. Other speakers need a few moments into their session before they stop really feeling those nerves but it goes.

Whenever I am involved in a conversation about nerves and presentations on twitter I respond in the same way

I love this quote by Joan Jett (young people link) To me it means that you should be nervous before speaking because that energy will ensure that you give a good presentation. If you get up to do a presentation and you are blasé or complacent about it this will be obvious to your audience and not in a good way.

So what to do?

Practice

You can’t just approach a presentation knowing that you will be nervous and expect it to be ok. You need to have a background of confidence that your presentation will turn out ok.

You need to practice.

You need to practice your presentation.

You need to practice your presentation out loud.

You need to practice your presentation out loud more than once.

You have to get used to hearing your own voice when presenting. It can be off-putting hearing yourself blathering on and you don’t want that to surprise you or interrupt your flow. This will also help with projecting away from your screen and into the room if you practice correctly. Imagine all the people in the room and try to speak in their direction with your head up and not pointing down at the screen.

You also need to practice your timings, so that you know that your session will fit in the allocated time. Make notes of your timings at certain points in your presentation so that when you are presenting your session you can be aware of whether you are still on your expected time. Some people will speak faster in their actual session than the practice and some slower. As you practice and learn you will understand your own rhythm and cadence and be able to alter it if required. This will help you to build that confidence that your presentation will be ok.

More Practice

You need to practice.

You need to practice your demos.

You need to practice your demos more than once.

Being able to reset your demos and run them through will teach you more skills. Using Pester to make sure your environment is in place correctly will help.

Run your demos with your machine set up as it will be for the presentation. If you need to have PowerPoint, SSMS, Visual Studio, Visual Studio Code and three SQL instances running then practice with them all running. You should do this so that your timings when running your demos are the same as when you actual present your session. This is even more important if you are doing a webinar as that software will require some of your machines resources which may slow your demo down.

Knowing that your demos are consistently repeatable and how long they will take will also help to give you the confidence that your presentation will be ok.

Deal with them

If we accept that you will have nerves and that’s not a bad thing you have to be able to deal with them, to use them to make your presentation rock.

This is a distinctly personal thing and I have no idea what will work for you. You will have to try some things and see if they work or not. Recently I found a new way for myself

Normally I like to be in the room I will be presenting in before I do my session as this gives me something that I can listen to, I can see and feel the layout of the room and also usually prepare my laptop with the correct programmes and run Pester to make sure all is as it should be for my demos. In Portugal I was chatting with someone and missed the start of the session and because of the room layout I did not want to disturb the presenter before me. Slava Oks was giving a presentation which I started to watch and it was so mind-melting I completely forgot that I was presenting in the next time slot! Surprisingly, I had almost no time to be nervous and for this time that was a good thing. The fact that I had already opened my presentation and run my Pester tests also helped.

Some speakers like to be amongst the hustle and bustle of a common area. Some like the peace and quiet of a speaker room or work area. Some put their headphones on. Some go outside. Some pace up and down. Some sit quietly. Many sit in a session in the room. Find the one that works for you.

A few deep breaths

Then just before you are giving your presentation take a few deep breathes, reassure yourself that it’s all good and go and be amazing.

Deep breaths will also be useful if you start to feel nervousness overtaking you during your session. Stop, take a deep breath and carry on.

Incidentally, during a presentation in Exeter at my first SQL Saturday I felt decidedly light-headed and as if I was going to pass out. I had literally forgotten to breathe!

What about…… ?

Don’t forget to leave time for questions at the end. Don’t practice to fill all of the allotted time with your presentation. You will need some time for the audience to ask you questions about your presentations.

Having people ask you questions is a good thing. It means that people are engaged in your presentation and interested in what you have shared. Well done, you have achieved what you set out to do and this is some validation

Repeat the question

Repeating the question that you are asked is recommended best practice for presentations but it has another advantage to you. It allows you a little thinking time to organise your thoughts and calm your nerves if needed.

I don’t know

It’s ok to answer a question with I don’t know. Follow up by asking if anyone in the audience can add some value or say I will research that and find out for you come and give me your contact details afterwards.

Feedback

Some events will provide you with feedback from your attendees. You can also ask your friends or other friendly community members for feedback on your session. Use this to improve. Don’t take all the feedback to heart. Look for trends in the data. Don’t let the poor feedback get you down and don’t let the good feedback go to your head (Remember the complacent quote at the top of this post!)

On a side note, whilst providing a score for feedback is useful, what is more useful is some reasoning behind the score. Remember also that the speaker is a human being with feelings. Be kind whilst being constructive.

Your knowledge

Don’t let worry about nerves prevent us from hearing the great knowledge and experience that you have to share. You wont be alone in feeling nervous and you can help yourself to overcome those nerves and get as much out of speaking as I do.

You will find members of the SQL community wiling to help you if you visit the SQL Community Slack you can ask questions in #presentingorspeaking

 

Using Twitter with VS Code

So today I saw this tweet from Mathias in reply to Stefan and Amanda

01 - tweet.PNG

 

That looks cool. Twitter in VS Code, one less programme to open Here how it works

Open VS Code and hit CTRL + P and type ext install twitter or hit CTRL + SHIFT + X to open the extensions and search

02 - install.PNG

 

Hit install and then reload

03 - reload

 

Accept the prompt

04 - prompt.PNG

and you will have a Twitter button in the bar at the bottom

05 - bar

If you click it then the top bar will change to the set up wizard

06 - forst time.PNG

Follow it along and create a Twitter App.

07 - want to continue

08 - create an appWhich will open up the website.

 

09 - create an app.PNG

Just fill in the blanks

010 - fil in the blanks.PNG

and then  you will have this window

011 - app settings.PNG

Click on keys and Access tokens

012 - settings and appl

Check the App Permissions are set to read and write

014 - app permissions

and then click create my access token

Then go back to VS Code and click

016 - settings details

Now you are shown the settings.json (which you can always find by File –> Preferences –> Settings )

017 - settingsjson.PNG

This bit, the wizard doesn’t explain very well (hence this post) If you have already some settings between the curly braces, you will need to put a comma and then paste the below code. If you do not then paste the below code between the curly braces

"twitter.consumerkey":"",
"twitter.consumersecret":"",
"twitter.accesstokenkey":"",
"twitter.accesstokensecret":"" 

and then paste the relevant keys and tokens from your twitter app between the double quotes

THEN PRESS CTRL + S to save the settings.json – Its in caps as when I showed someone they were too excited and didn’t save it!!

Now its all set up you can use the extension. Click the twitter button in the bar and

018 - Twitter actions

Now you can have your Home timeline in Code

019 - twitter home.PNG

Yes there are still a few seats left for the Europe PowerShell Conference You can search, see your mentions, your user page

020- mentions.PNG

and post 🙂 You can just press F1 and start typing twitter to get the commands

021 - comands

022 - tweet.png

and

 

023 - tweet.PNG

You can also message people using D message username. You can see the extension repo on GitHub which will be a good place to raise issues, bugs, feature requests

I’m not suire it will Increase my productivity !! but it really pleases the nerd in me!

 

 

Generating T-SQL Randomly with Powershell

I have a lab on my laptop running various servers so that I can problem solve and learn and recently I wanted to add several months of data into a database. I had created a stored procedure to take some parameters perform some logic and insert the data.

To execute the stored procedure in T-SQL I simply run this

EXECUTE [dbo].[usp_Insert_DriveSpace] 'Server1','C','2014-11-05','100','25'

which uses the server name, drive letter, date, capacity and free space to add the data

In my wisdom I decided to create some data that was more ‘real-life’ I was interested in storing drive space data and will be learning how to write reports on it. To do this I had pre-populated some tables in the database with 10 Server Names each with 5 drives so I needed 10*5*90 or 4500 statements

I wanted to populate this with about 3 months of data as if it had been gathered every day. I read this post about using CTEs to create sequences and I am sure it can be done this way but I don’t have the T-SQL skills to do so. If someone can (or has) done that please let me know as I am trying to improve my T-SQL skills and would be interested in how to approach and solve this problem with T-SQL

I solved it with Powershell in this way.

Created an array of Servers and an array of Drives to enable me to iterate though each.

$Servers = 'Server1','Server2','Server3','Server4','Server5','Server6','Server7','Server8','Server9','Server10'
$Drives = 'C','D','E','F','G'

Set the drive capacity for each drive. To make my life slightly easier I standardised my ‘servers’

$CDriveCapacity = 100
$DDriveCapacity = 50
$EDriveCapacity = 200
$FDriveCapacity = 200
$GDriveCapacity = 500

I needed to create a date. You can use Get-Date to get todays date and to get dates or times in the future or the past you can use the AddDays() function. You can also add ticks, milliseconds, seconds, minutes, hours, months or years

(Get-Date).AddDays(1)

I then needed to format the date. This is slightly confusing. If you just use Get-Date to get the current date (time) then you can use the format or uformat switch to format the output

Get-Date -Format yyyyMMdd
Get-Date -UFormat %Y%m%d

However this does not work once you have used the AddDays() method. You have to use the ToString() method

 $Date = (get-date).AddDays(-7).ToString('yyyy-MM-dd')

To replicate gathering data each day I decided to use a while loop. I set $x to –95 and pressed CTRL and J to bring up Snippets and typed w and picked the while loop. You can find out more about snippets in my previous post I started at –95 so that all the identity keys incremented in a real-life manner oldest to newest.

$x = -98
while ($x -le 0)
{
    $Date = (get-date).AddDays($x).ToString('yyyy-MM-dd')

    foreach($Server in $Servers)
    {
        foreach ($Drive in $Drives)
        {

I could then use the while loop to generate data for each day and loop through each server and each drive and generate the T-SQL but I wanted more!

I wanted to generate some random numbers for the free space available for each drive. I used the Get-Random cmdlet If you are going to use it make sure you read this post to make sure that you don’t get caught by the gotcha. I decided to set the free space for my OS,Data and Log Files to somewhere between 70 and 3 Gb free as in this imaginary scenario these drives are carefully monitored and the data and log file sizes under the control of a careful DBA but still able to go below thresholds.

if($Drive -eq 'C')
            {
            $Free = Get-Random -Maximum 70 -Minimum 3

I set the TempDB drive to have either 4,7 or 11 Gb free so that i can try to colour code my reports depending on values and if one field only has three values it makes it simpler to verify.

I set the Backup Drive to somewhere between 50 and 0 so that I will hit 0 sometimes!!

Here is the full script. It generated 4500 T-SQL statements in just under 16 seconds

$Servers = 'Server1','Server2','Server3','Server4','Server5','Server6','Server7','Server8','Server9','Server10'
$Drives = 'C','D','E','F','G'
$CDriveCapacity = 100
$DDriveCapacity = 50
$EDriveCapacity = 200
$FDriveCapacity = 200
$GDriveCapacity = 500

$x = -98
while ($x -le 0)
{
    $Date = (get-date).AddDays($x).ToString('yyyy-MM-dd')

    foreach($Server in $Servers)
    {
        foreach ($Drive in $Drives)
        {
            if($Drive -eq 'C')
            {
            $Free = Get-Random -Maximum 70 -Minimum 3
            Write-Host "EXECUTE [dbo].[usp_Insert_DriveSpace] '$Server','$Drive','$Date','$CDriveCapacity','$Free'"
            }
            elseif($Drive -eq 'D')
            {
            $Free = Get-Random -InputObject 4,7,11
            Write-Host "EXECUTE [dbo].[usp_Insert_DriveSpace] '$Server','$Drive','$Date','$DDriveCapacity','$Free'"
            }
            elseif($Drive -eq 'E')
            {
            $Free = Get-Random -Maximum 70 -Minimum 3
            Write-Host "EXECUTE [dbo].[usp_Insert_DriveSpace] '$Server','$Drive','$Date','$EDriveCapacity','$Free'"
            }
            elseif($Drive -eq 'F')
            {
            $Free = Get-Random -Maximum 70 -Minimum 3
            Write-Host "EXECUTE [dbo].[usp_Insert_DriveSpace] '$Server','$Drive','$Date','$FDriveCapacity','$Free'"
            }
            elseif($Drive -eq 'G')
            {
            $Free = Get-Random -Maximum 50 -Minimum 0
            Write-Host "EXECUTE [dbo].[usp_Insert_DriveSpace] '$Server','$Drive','$Date','$GDriveCapacity','$Free'"
            }
        }
    }
    $X++
}

Once it had run I simply copied the output into SSMS and was on my way

Emailing Disk Space Alerting With Powershell

 

A DBA doesn’t want to run out of space on their servers, even in their labs! To avoid this happening I wrote a Powershell script to provide some alerts by email.

This is the script and how I worked my way through the solution. I hope it is of benefit to others.

The script works in the following way

  • Iterates through a list of servers
  • Runs a WMI query to gather disk information
  • If the free space has fallen below a threshold, checks to see if it has emailed before and if not emails a warning
  • Resets if free space has risen above the threshold
  • Logs what it does but manages the space the logs use

As you will have seen before I use a Servers text file in my scripts. This is a text file with a single server name on each line. You could also use a query against a DBA or MDW database using Invoke-SQLCMD2, which ever is the most suitable for you.

 $Servers = Get-Content 'PATH\TO\Servers.txt' foreach($Server in $Servers) { 

The WMI query is a very simple one to gather the disk information. I format the results and place them in variables for reuse

 $Disks = Get-WmiObject win32_logicaldisk -ComputerName $Server | Where-Object {$_.drivetype -eq 3} $TotalSpace=[math]::Round(($Disk.Size/1073741824),2) # change to gb and 2 decimal places $FreeSpace=[Math]::Round(($Disk.FreeSpace/1073741824),2)# change to gb and 2 decimal places $UsedSpace = $TotalSpace - $FreeSpace $PercentFree = [Math]::Round((($FreeSpace/$TotalSpace)*100),2)# change to gb and 2 decimal places 

Use a bit of logic to check if the freespace is below a threshold and see if the email has already been sent

 # Check if percent free below warning level if ($PercentFree -le $SevereLevel) { # if text file has been created (ie email should already have been sent) do nothing if(Test-Path $CheckFileSevere) {} # if percent free below warning level and text file doesnot exist create text file and email else { 

If it has not create a unique named text file and create the email body using HTML and the values stored in the variables

 New-Item $CheckFileSevere -ItemType File #Create Email Body $EmailBody = '' $EmailBody += " " 

and then send it

 $Subject = "URGENT Disk Space Alert 1%" $Body = $EmailBody $msg = new-object Net.Mail.MailMessage $smtp = new-object Net.Mail.SmtpClient($smtpServer) $smtp.port = '25' $msg.From = $From $msg.Sender = $Sender $msg.To.Add($To) $msg.Subject = $Subject $msg.Body = $Body $msg.IsBodyHtml = $True $smtp.Send($msg) 

If the freespace is above all of the warning levels, check for existence of the text file and delete it if found so that the next time the script runs it will send an email.

 if(Test-Path $CheckFile) { Remove-Item $CheckFile -Force

To enable logging create a log file each day

 $Logdate = Get-Date -Format yyyyMMdd $LogFile = $Location + 'logfile' + $LogDate+ '.txt' # if daily log file does not exist create one if(!(Test-Path $LogFile)) { New-Item $Logfile -ItemType File 

And write the info to it at each action

 $logentrydate = (Get-Date).DateTime $Log = $logentrydate + ' ' + $ServerName + ' ' + $DriveLetter + ' ' + $VolumeName + ' ' + $PercentFree +' -- Severe Email Sent' Add-Content -Value $Log -Path $Logfile

Making sure that you clean up after

 # any logfiles older than 7 days delete Get-ChildItem -Path $Location *logfile* |Where-Object {$_.LastWriteTime -gt (Get-Date).AddDays(7) }|Remove-Item -Force 

I run the script in a Powershell Step in an SQL Agent Job every 5 minutes and Now I know when my servers in my lab are running out of space with an email like this

image

You can find the script here

Number of VLFs and Autogrowth Settings Colour Coded to Excel with PowerShell

So you have read up on VLFs

No doubt you will have read this post by Kimberly Tripp and this one and maybe this one too and you want to identify the databases in your environment which have a large number of VLFs and also the initial size and the autogrowth settings of the log files.

There are several posts about this and doing this with PowerShell like this one or this one. As is my wont I chose to output to Excel and colour code the cells depending on the number of VLFs or the type of Autogrowth.

There is not a pure SMO way of identifying the number of VLFs in a log file that I am aware of and it is simple to use DBCC LOGINFO to get that info.

I also wanted to input the autogrowth settings, size, space used, the logical name and the file path. I started by getting all of my servers into a $Servers Array as follows

Whilst presenting at the Newcastle User Group, Chris Taylor b | t asked a good question. He asked if that was the only way to do this or if you could use your DBA database.

It is much better to make use of the system you already use to record your databases. It will also make it much easier for you to be able to run scripts against more specific groups of databases without needing to keep multiple text files up to date. You can accomplish this as follows

I then create a foreach loop and a server SMO object (Did you read my blog post about snippets? the code for a SMO Server snippet is there) returned the number of rows for DBCC LOGINFO and the information I wanted.

It’s not very pretty or particularly user friendly so I decided to put it into Excel

I did this by using my Excel Snippet

and placed the relevant bits into the foreach loop

I had to use the ToString() method on the Type property to get Excel to display the text. I wanted to set the colour for the VLF cells to yellow or red dependant on their value and the colour of the growth type cell to red if the value was Percent. This was achieved like this

I also found this excellent post by which has many many snippets of code to work with excel sheets.

I used

although I had to move the Title so that it was after the above line so that it looked ok.


image

You can find the script here. As always test it somewhere safe first, understand what it is doing and any questions get in touch.

PowerShell Snippets A Great Learning Tool

When I talk to people about Powershell they often ask how can they easily learn the syntax. Here’s a good tip

Open PowerShell ISE and press CTRL + J

image

You will find a number of snippets that will enable you to write your scripts easily.  Johnathan Medd PowerShell MVP has written a good post about snippets on the Hey, Scripting Guy! blog so I will not repeat that but suggest that you go and read that post. It will show you how quickly and easily you will be able to write more complex Powershell scripts as you do not have to learn the syntax but can use the snippets to insert all the code samples you require.

Not only are there default snippets for you to use but you can create your own snippets. However there isn’t a snippet for creating a new snippet so here is the code to do that

I frequently use the SQL Server SMO Object in my code so I created this snippet

I also use Data Tables a lot so I created a snippet for that too

Denniver Reining has created a Snippet Manager which you can use to further expand your snippets usage and it is free as well.

If you have further examples of useful snippets please feel free to post them in the comments below

Edit 16/12/2014

I am proud that this article was nominated for the Tribal Awards. Please go and vote for your winners in all the categories

http://www.sqlservercentral.com/articles/Awards/119953/

Personally in the article category I will be voting for

Gail Shaw’s SQL Server Howlers

Find Out Which Indexes are on which Filegroups using PowerShell And How To Find Other Information

 

A short post today to pass on a script I wrote to fulfil a requirement I had.

Which indexes are on which filegroups. I found a blog post showing how to do it with T-SQL but as is my wont I decided to see how easy it would be with PowerShell. I also thought that it would make a good post to show how I approach this sort of challenge.

I generally start by creating a SQL Server SMO Object You can use the SMO Object Model Diagram or Get-Member to work out what you need. As we are talking indexes and filegroups I will also create a Database object

Then by piping the database object to Get-Member I can see the properties

image

Lets take a look at the table object in the same way

image

 

I can see the indexes object so I pipe that to Get-Member as well

image

Now I have enough to information to create the report. I will select the Name, Table, Type and Space Used of the Indexes and format them nicely

and here are the results

image

However, you may want the results to be displayed in a different manner, maybe CSV,HTML or text file and you can do this as follows

image

 

image

image

Hopefully this has shown you how easy it can be to use PowerShell to get all of the information that you need from your SQL Server and how to approach getting that information as well as several ways to display it