Measuring Your DBA Skills

Over the last 9 weeks I took you on a journey of skills and career topics related to being a SQL Server DBA. We looked at the Production DBA. We saw skills and career topics from the beginning to mid-career to Senior DBA. Then we looked at the Development DBA and their skills and career development needs. Finally there was a wrap up post.

To make it easier for everyone to get to these posts, I decided to bring them all together on a single page.

measuring your skills

 Production DBA Skills Years 0-2

Production DBA Skills Years 2-4

Production DBA Skills Years 4-7

Senior Production DBA Skills Years 7+

Development DBA Skills Years 0-2

Development DBA Skills Years 2-4

Development DBA Skills Years 4-7

Senior Development DBA Skills Years 7+

Series wrap-up

If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

Insufficient System Memory – Failed Allocate Pages

 

Unable to Start SQL Server

In my own local SQL Server I ran across a problem starting the SQL instance. I went to SQL Server configuration Manager and manually started the SQL Server instance. The UI showed the instance had started. I opened SSMS and tried to connect. And I waited, waited and waited some more until it didn’t connect and threw an error.

How To Locate the SQL Server Error Log

I knew I could look at the SQL Server Error Log to get more information on what had gone wrong.  I browsed to the directory where the log file is. For any system that is found by going to the drive where the binaries have been installed and go to \Program Files\Microsoft SQL Server\SQLServerVersionNumber.InstanceName\MSSQL\Log. In that location you will look for files like Errorlog, errorlog.1 through errorlog.6 The file with no number at the end is the most recent Error Log. Double click it and open in your favorite viewer. As I read through the file, I came across the below.

Insufficient System Memory – Failed Allocate Pages

 

Failed Allocate Pages - Insufficient System Memory

Also, you can read the SQL Server Error Log using the extended stored procedure xp_readerrorlog. Here is an example that shows the memory related messages from the SQL Server Error log. Of course, you can only do that once your SQL Server is running.

xp_readerrorlog 0, 1, "memory"

Then the next thing was, how do I investigate this since I can’t start the SQL Server?

Startup Parameters for SQL Server

SQL Server has startup parameters that can be added to the SQL Server Configuration Manager to control what happens when SQL Server starts. I was looking for something that would help me get SQL Server started and let me poke around. At first I tried -m for single user mode but SQL Server still wouldn’t start. Then I found the -f parameter and it sounded like just what I needed.

“Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode. For more information, see the description for -m that follows.”

Now that I had that information I opened SQL Server Configuration Manager. I located the SQL Server instance I couldn’t start and right clicked it. I then chose properties and typed -f in the Startup Parameters tab.

I then clicked Add to the right of the startup parameter I just added. I clock Ok and I’m prompted that I need to restart the SQL Server service to make this change effective. So, I restart SQL Server and attempt my connection again, and I’m in!

SQL Server Configuration Manager Startup Parameters

 

Because this was an insufficient memory error, I right clicked the name of my SQL Server instance and selected Properties then Memory. I see I only have 1024 MB assigned to the SQL instance for Max Server Memory. I increased it to 3072 MB and clicked Ok.

I went back to SQL Server Configuration Manager and removed the -f startup parameter and restarted SQL Server. Now I am able to start up SQL Server and connect with no issues.

What To Do Next

  1. Go back to the link on SQL Server Startup Parameters and familiarize yourself with what is in there. You never know when you might need functionality from a startup parameter to get you past a problem.
  2. Do some research on Max Server Memory.
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

 

How Do I Measure My DBA Skills Part 9

Production and Development DBA Skills and Things We Didn’t Touch On

We’ve covered a lot of ground for Production and Development DBAs in terms of the skills and applicable career topics. We’ve mentioned necessary skills with T-SQL, backup/restore, automation, PowerShell, troubleshooting, SSIS, SSRS, patching, Query Store, HA/DR, leadership and emotional intelligence. That’s quite a list! However, looking back at the series, there are still a number of things that weren’t mentioned.

Other skills and career topics not discussed along the way:

  1. OS skills – This once used to be just Window skills. However, with SQL Server on Linux, and depending on what is in use in your environment, you may need to learn the basics of the Linux platform as well as Windows.
  2. I also didn’t touch on the topic of SQL Server Wait Statistics. This is a tried and true method for diagnosing server level issues that can lead to further investigation about what ails your SQL Server.
  3. What about the cloud and virtualization? Azure, AWS, Google Cloud Platform, VMware, Hyper-V. These are all virtualization platforms that are in use these days, so you may encounter these and need to know, or might want to know, something about how they work.
  4. What about certifications? Are they worthwhile? Should you pursue one? If yes, which one?

Then there is also a third career title I didn’t even mention. What about the DBA whose focus or specialty is in BI? You are the person who takes care of the SQL Servers, but maybe you also write a lot of reports for the business using SSRS, Power BI, maybe SSAS, Tableau, Qlik or some other reporting platform. This may really just be a specialty within the Development DBA role, but it could be separate as well.

Beyond the Senior levels of the types of DBA careers discussed, you’re likely to end up moving into management. That will open up an entirely different set of needed skills and career topics that you will need to explore.

Next Steps To Take

Planning - To Do List

  1. If you’ve read through this series and followed the instructions about building a training plan for skills you would like to develop, then you likely have a significant list of skills to work on. Don’t focus on how long the list is. Select one thing and work on it for however long it takes for you to feel at least semi-comfortable with it. Then, cross it off the list and pick something else. Repeat the process. Within a few weeks or months, you will see a lot of progress.
  2. Remember to keep your skills/career development plan somewhere that allows you to see the plan every day. This will help keep you on track and your To Do list prioritized.
  3. You can go to kand.io and find a variety of graded skills test. There is one for Database Administrators and one for Database Developers. Both were written by the fine people at SQLSKills.com.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

 

Introduction To PowerShell For The DBA Part 3

Hello dear reader! This is the third post in a series to help you get started automating things with PowerShell. You will be looking at how to start and stop SQL Server using PowerShell as well as setting the Max Server Memory setting, measuring free disk space and more.

This series is just an introduction to PowerShell and basic functionality you might be interested in as a SQL Server Database Administrator or as a System Administrator responsible for SQL Server. Because it is introductory I don’t have examples of long scripts that do a series of complex things as part of these posts. What the series does have are one or two line scripts that are still powerful and help you explore what is available to you in PowerShell.

With that in mind, let’s delve into a few more PowerShell commands that can help you with common tasks.

How To Start And Stop SQL Server Using PowerShell

Open PowerShell as an Administrator and run these commands. These are simple ways to search for commands related to the SQL Server services. Since we’re looking for information related to services, the first command searches for cmdlets containing the word “service”.  Since we know we want to start or stop the service, we’re looking for commands containing “start” or “stop” in those second and third commands.

GCM *service* -Module DBATools, SQLServer

GCM *start* -Module DBATools, SQLServer

GCM *stop* -Module DBATools, SQLServer

Service Related PowerShell Commands

PowerShell Commands with Start

Commands With Stop in PowerShell DBATools SQLServer

I’m showing you how to find interesting commands related to what you’re trying to accomplish, but I also want you to see how much else you can learn about the capabilities of the DBATools and SQLServer PowerShell modules. As you can see from the output, DBATools has Stop-DBAService, Start-DBAService, Restart-DBAService. The SQLServer module has Stop-SqlInstance and Start-SqlInstance.

I encourage you to look through the output of the screenshots, or in your own PowerShell session, to see what else you can do.  Particularly inside the DBATools these Get-Command outputs show many things are available. For example, you can start a SQL Agent job, start a trace, start an extended events session, you can stop an endpoint or process as well as do several other things. By the way, there is no Stop-DbaAgentJob because you can actually do that from the Start-DbaAgentJob cmdlet in DBATools using a switch.

So, now that you know the types of commands available how are you going to find out what they do? Remember your friend Get-Help or its alias Help?

Help Get-DBAService -Full
Help Restart-DbaService -Full

The output of these commands is too much to put into the post. So, I’ll just point out a few things about each one.

For Get-DBAService notice that you can pass it a computer name value to get the services for all SQL Server instances installed on a given computer.  This will be useful if you know your environment tends to do what’s called “instance stacking.” This is were 2 or more SQL instances are installed on the same computer. If this is done in your environment, you will want to know that information before you start sending commands to restart SQL Server services or you could end up restarting more SQL Server instances than you planned on. That sounds like unplanned down time and you want to avoid that!

<#Returns all SQL Server instances on a given computer. Default columns 
show things like the computerhname, instancename, service name and whether
the current status of the service.
#>
Get-DBAService -ComputerName MyComputerNameHere

Setting Max Server Memory With PowerShell

We’re going to look at Set-DBAMaxMemory from the DBATools module. If you would like more information about SQL Server memory settings. I have a blog post that describes the topic in more detail.

Here is what the DBATools website has to say about this cmdlet in their documentation.

“Inspired by Jonathan Kehayias’s post about SQL Server Max memory (http://bit.ly/sqlmemcalc), this uses a formula to determine the default optimum RAM to use, then sets the SQL max value to that number.”

Here are some examples from the documentation from DBATools and from the Help commands available in PowerShell.

<#
If you have a Central Management Server for you SQL environment, consider using this command to loop through all the SQL Servers and set the Max Server Memory where it is set to something larger than the total amount of RAM assigned to the server.
#>
Get-DbaRegServer -SqlInstance sqlserver | Test-DbaMaxMemory | Where-Object { $_.MaxValue -gt $_.Total } | Set-DbaMaxMemory

<#
If you have a Central Management Server for you SQL environment, consider using this command to loop through all the SQL Servers and set the Max Server Memory to this accepted formula created by a SQL Server expert.
#>

Get-DbaRegServer -SqlInstance sqlserver | Test-DbaMaxMemory | Set-DbaMaxMemory

<#
If you don't have a registered server then just use the below
#>

Test-DbaMaxMemory -SQLinstance SQLServerInstanceNameHere | Set-DbaMaxMemory

Measuring Free Space with PowerShell

DBAs should have some way of knowing and tracking free space in the database files and for the drives of the SQL Server machines. The DBATools module has two commands for doing just that. Check out the below examples and be sure to use the Help cmdlet on these to look at other examples.

<#Gets just certain parts of the output of get-dbaDbspace. Can add | Out-File C:\DBATools\SpaceOutput.txt 
to the end of this to output this information to a file for review.
#>
Get-DbaDbSpace -SqlInstance SQLinstanceNameHere | SELECT Database, FileName, UsedSpace, FreeSpace, PercentUsed, AutoGrowth

#Resturns drive letters, total space and free space on drives on a computer.
Get-dbadiskspace -ComputerName MyComputerNameHere

Detecting IO Latency With PowerShell

One of the things that DBAs want to check for is IO performance of the storage as seen by SQL Server.  This is often done using sys.dm_io_virtual_file_stats. You can view this information in the DBATools cmdlet Get-DbaIoLatency.

The DBATools output from the Help command says that the output of this commands is based on two articles by Paul Randal of SQLSkills.com. Those articles are listed below. If you don’t know who Paul Randal is, you need to find out. Your career with SQL Server will be greatly enhanced by reading his stuff.

How to examine IO subsystem latencies from within SQL Server

Capturing IO latencies for a period of time

I also have a post on finding queries experiencing waits related to SQL Server IO latency.

So here are a couple of examples of how to run this PowerShell cmdlet.

#Outputs the IO latency information of two different SQL Servers

Get-DbaIoLatency -SqlInstance SQLServerInstance1, SQLServerInstance2

#Outputs the IO latency to a GridView UI for visual examination
$output = Get-DbaIoLatency SQLServerInstance1 | Select-Object * | Out-GridView -PassThru

#Writes the output to a file.
Get-DbaIoLatency SQLServerInstance1 | Select-Object * | Out-File C:\DBATools\IOLatency.txt

 

Next Steps To Take

  1. On a Dev environment, practice using the DBATools commands related to stopping and starting SQL Server. This gives you another option when things go south and you have to restart the SQL instance. If the machine suddenly has issues showing the MMC snap-ins then SQL Server Configuration Manager may not be available. And yes, this has happened to me!
  2. Use the Test-DBAMaxMemory command to see if your SQL Servers are misconfigured in terms of the RAM allocated to them. Once you’ve reviewed the output of the command and are comfortable with it, then use the PowerShell examples in here to make the changes.
  3. Consider how you can leverage the cmdlets about diskspace, database space and IO latency to examine your environment. Write some test scripts and try them out.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.