Shawn Melton

SQL Server and PowerShell

SQL Server Activity Monitor Script

Posted by Shawn Melton on 2014/07/30

You ever wonder what goes in the background of Activity Monitor? Me neither, I just used it every so often when I wanted a quick peek at what was going on for a server. Microsoft has actually made changes to Activity Monitor in SQL Server 2012 Service Pack 1 that can cause errors, and requires you to modify OS level permissions [Reference]. I work over VPN supporting clients remotely and it never been that useful to me.

I liked all the information it showed but just did not like having to use the UI it provided to filter and such. So I did a trace against an instance while I opened Activity Monitor to pull out what query it was running to populate the active sessions.

So this post is pretty much just a bookmark for the script:

SELECT [Session ID] = s.session_id
	,[User Process] = CONVERT(CHAR(1), s.is_user_process)
	,[Login] = s.login_name
	,[Database] = ISNULL(db_name(p.dbid), N'')
	,[Task State] = ISNULL(t.task_state, N'')
	,[Command] = ISNULL(r.command, N'')
	,[Application] = ISNULL(s.program_name, N'')
	,[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0)
	,[Wait Type] = ISNULL(w.wait_type, N'')
	,[Wait Resource] = ISNULL(w.resource_description, N'')
	,[Blocked By] = ISNULL(CONVERT(VARCHAR, w.blocking_session_id), '')
	,[Head Blocker] = CASE 
		WHEN r2.session_id IS NOT NULL
			AND (
				r.blocking_session_id = 0
				OR r.session_id IS NULL
				)
			THEN '1'
		ELSE ''
		END
	,[Total CPU (ms)] = s.cpu_time
	,[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024
	,[Memory Use (KB)] = s.memory_usage * 8192 / 1024
	,[Open Transactions] = ISNULL(r.open_transaction_count, 0)
	,[Login Time] = s.login_time
	,[Last Request Start Time] = s.last_request_start_time
	,[Host Name] = ISNULL(s.host_name, N'')
	,[Net Address] = ISNULL(c.client_net_address, N'')
	,[Execution Context ID] = ISNULL(t.exec_context_id, 0)
	,[Request ID] = ISNULL(r.request_id, 0)
	,[Workload Group] = N''
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT JOIN sys.dm_os_tasks t ON (
		r.session_id = t.session_id
		AND r.request_id = t.request_id
		)
LEFT JOIN (
	SELECT *
		,ROW_NUMBER() OVER (
			PARTITION BY waiting_task_address 
ORDER BY wait_duration_ms DESC ) AS row_num FROM sys.dm_os_waiting_tasks ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1 LEFT JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id) LEFT JOIN sys.sysprocesses p ON (s.session_id = p.spid) WHERE s.is_user_process = 1 ORDER BY s.session_id;

Posted in Scripts, SQL Server | Leave a Comment »

SSMS 2012–Object Reference issue with Maintenance Plans

Posted by Shawn Melton on 2014/07/21

I am amazed at some of the information you can find on the Internet when searching for an issue. Especially with forum posts that mention your issue (or flat out are the exact same), but do not include any final resolution. The best one I came across was this one on SQLTeam’s forum site, the post by “prett”, and note that this is posted back in 2013:

The Maintenance Plan is actually built with few services which Microsoft releases such as SSIS and SQL Server Job Agent, hence if you want to schedule the maintenance plan then your server needs to have SSIS in order to build the maintenance plan, and Job Agent in order to run at regular periods.
This error message “Object reference not set to an instance of an object” occurs because of one of the required component was not available on the server.

SQL Server 2005 started out requiring SSIS to be installed for maintenance plans to run, however when Service Pack 2 was released they removed that requirement. As far as I know after that any release of SQL Server kept that same functionality.

Well I came across this issue on my VMWare View desktop that I utilize for one particular client. I was getting this pretty little box every time I tried to create a new maintenance plan on a newly built SQL Server 2012 Failover Cluster Instance:

image

The version of SSMS I was using:image

I thought I would apply Service Pack 2 to try and resolve it and noted during that installation it was showing “SQLExpress”. Which did bring to mind that I had installed SQL Server 2012 Management Studio for Express when it was announced that it was the full version of SSMS now. I confirmed this by going to the “Setup Bootstrap” and checking the Summary files for the previous installations on the desktop. I found this:

image

So I went to Programs and Features and removed it. I then located the installation media for SQL Server 2012 Standard Edition and installed the Management Tools. Then for good measure applied Service Pack 2 for SQL Server 2012:

image

Low and behold, that solved my issue. I hope those in the future can find this bit of information useful.

Posted in SQL Server | Tagged: , , , | Leave a Comment »

PS C:\Check-InstanceConfig (part 2)

Posted by Shawn Melton on 2013/12/09

In the previous post I discussed the data table that holds the default values for the configuration options in SQL Server 2000 up to SQL Server 2014.

In this post I will go over the function(s) that are included in the script. I will keep the same format as I did in the previous post:

  • How it was written
  • What it contains
  • A few examples

How it was written

If you have already downloaded the script you notice it had one function. I was attempting to make the output similar to what Mike did in his T-SQL script with the text output. After looking at it a bit more and thinking on best practices for PowerShell, I chose a different route. I always try to ensure whatever my functions output, you can do something with them down the pipeline. In order to keep that going I opted to break this up into separate functions. I also included a function that allows you to just pull the raw configuration options from the instance as you wish.

What it contains

There are 4 functions included in the current version of the script:

  • Pull-SqlConfig – Just pull the configuration options information on the instance
  • Check-SqlRunConfig – Compare the running value to the configured value
  • Check-SqlRunDefault – Compare the running value to the default value
  • Check-SqlBadDefaults – Compare the running value to the default value for specific options.

I decided to also include help text with each function so loading the script into your PowerShell session you can simply use the Get-Help cmdlet and it will show you the information on each function. Adding the “-full” switch to the command will output more detailed information as well.

image

A few examples

As mentioned above the function help information also includes a few examples of each function. So if you want to see them you can simply use the Get-Help function along with the “–Examples” switch.

image

Here is the full script. This link will always point to the current version of the script. My next blog post will explain the function in the script.

Posted in PowerShell, SQL Server | Tagged: , , | Leave a Comment »

PS C:\Check-InstanceConfig (part 1)

Posted by Shawn Melton on 2013/12/04

I was reading a blog post Mike Walsh published on Linchpin People last week: SQL Server Configuration Check Script.

In that script he is, as the title says, checking the configuration of a SQL Server instance for particular things like those options not set to default values and a few particular ones that still are set to default. I was intrigued by this and by the time I got to the end of the blog post was like “bet I can do that in PowerShell too”. I had a slow weekend with the holidays so thought I would sit down and see what I could come up with.

Well I came up with two specific things that I wanted to accomplish with this script:

  1. Provide a reference point to the configuration options for each version of SQL Server
  2. Provide a function that will take that reference point and validate it against any instance

If I tried to put all the information I want to help explain the script this blog post would be longer than you have the time to read through. I decided to just break it up into two parts based on the list above. So this post will just be on the reference point I wrote and hopefully by the end you will have learned the following things:

  • How it was written
  • What it contains
  • A few examples

A few caveats about the script: I did not do anything special in PowerShell that is specific to any particular version. This script should work on PowerShell 2.0 and higher. You will notice if you open this script up in the PowerShell ISE 3.0 or higher I did use regions, just a formatting feature in the ISE. It is treated as normal comment lines outside of the ISE.

One more thing: There is more than one way to do things in PowerShell, this is just the way I decided to go. As well I did not cut corners in this script. I wanted to make sure every command and variable was understood. I tried to not use any aliases with commands (e.g. “?” instead of where or where-object).

How it was written

The first section I put in the script was a comment block that contains the BOL links for the “Server Configuration Options (SQL Server)” page of each version:

image

I went back and forth a few times on how to actually build out a list of the configuration option names and default values based on the current versions of SQL Server that are available. I knew I needed the information in order to validate it against a SQL Server instance, no matter what version I might try. In Mike’s script he simply scripted out temporary tables with this information. So I kept to the same concept and decided to use a data table (System.Data.DataTable).

What it contains

I tried to make the columns self-explanatory so the column naming convention I chose:

  • ConfigOption – name of the configuration option in sp_configure or sys.configurations with new versions of SQL Server. The list contains those configuration options available from SQL Server 2014 to SQL Server 2000, as stated in the associated links in BOL for each.
  • Default_<4 digit year of SQL Version> – there is a column for each version of SQL Server the script will support (e.g. Default_2012). If a ConfigOption name is not supported or included in any particular version this value is set to –9 (negative nine).

image

An example of the –9 usage, “access check cache bucket count” is a configuration option only available in SQL Server 2008 and higher:
image

A few Examples

Ok, I am going to assume the reader is already familiar with how to run PowerShell scripts. I prefer the dot sourcing method in most cases. You can also simply copy and past this into your profile if you desire. Remember, Out-GridView is your friend.

  • To list out the complete data table execute this command:
    $sqlConfigDefaults | Out-GridView
    image
  • I have variables already in the script to list out each version (reference lines 723-729 in the script). So if you wanted to list out the options for SQL Server 2014:
    $sql2014_Defaults | Out-GridView
    image
    I could have filtered out the columns for the other versions, but I thought it to be a nice reference point to see those options that are still around from lesser versions. You can adjust this if you wish, but note that these variables are utilized in the function as well.

Here is the full script. This link will always point to the current version of the script. My next blog post will explain the function in the script.

Posted in PowerShell, SQL Server | Tagged: , , , | 2 Comments »

PS C:\> Calc-MaxSample

Posted by Shawn Melton on 2013/11/13

I am working on building a function that will allow me to gather performance counter data across multiple servers. I knew this had done by someone in the SQL Server family and went to search the inter-web. I came across Aaron Bertrand’s (blog | twitter) blog post here.

This gave me a starting point, however I also noticed that with the use of the basic Get-Counter cmdlet you provide the –MaxSamples value to tell PowerShell how long you want to collect counter information.

You would want to calculate this based on the –SampleInterval value. A few examples of this can be found in the comments on Aaron’s blog post. If you want to cover a 2 hour period and get a sample every 30 seconds, you would set MaxSamples to 240.

This is a mathematical equation that I did not want to have to keep doing every time. So I decided to build a small function that I added to my PowerShell ISE profile. I don’t use the the console all that much anymore with PowerShell 4.0. The below function includes a bit of help information as well. So, once you load this function into the console or your profile you can use Get-Help cmdlet if needed.

<#
.Synopsis
   Assist calculating the max sample for Get-Counter
.DESCRIPTION
   Calculates from the sampling value and the hours desired what the max sample value should be for the Get-Counter cmdlet
.EXAMPLE
   Cal-MaxSample -secSample 15 -hours 3
   This example based on the SampleInterval and the number of hours desired output what the MaxSample value should be
.PARAMETER secSample
    The SampleInterval value to be used in Get-Counter command, Int32 value between 2 - 60. The default value for
    Get-Counter command is 1 (one)
.PARAMTER hours
    The number of hours desired for capturing performance counter data with Get-Counter command
#>
function Calc-MaxSample
{
    [CmdletBinding()]
    [OutputType([int])]
    Param
    (
        [Parameter(Mandatory=$true,
                   Position=0)]
        [ValidateRange(2,60)]
        [int32]
        $secSample,
        [Parameter(Mandatory=$true,
                    Position=1)]
        [int]
        $hours
    )
    [int]$calValue = (($hours * 60) * 60) / $secSample
    Write-Host "Based on following calculation: (($hours * 60) * 60) / $secSample)" -ForegroundColor Yellow -BackgroundColor Red
    Write-Host "MaxSample value = $calValue" -ForegroundColor Yellow -BackgroundColor DarkGray
}

Posted in PowerShell | Tagged: , | Leave a Comment »

PowerShell Tidbit #492

Posted by Shawn Melton on 2013/11/05

I do remote support work now as a DBA and have clients that provide my password for VPN login or Active Directory login. I usually access their network through a central remote server that I will use to manage their SQL Server environment. As some of you may know there is no way to change your password through remote desktop until it expires and prompts you to upon login. Depending on the version of Active Directory they are running you may get a little bubble down by the clock that you can use as well, but I tend to miss that most of the time.

So I wanted an alternative so I could change it when I needed to, as some clients do not have as strict a policy regarding password changes as I would like. I took the web and came across a nice post by the Scripting Guys here.

I was not really interested in using this as a function, just needed the commands to use. This article has the exact thing you need so go read it…

The one thing I needed to add was knowing the full AD path of my user account. You all know what yours is right?

Well you can use “gpresult /R” to get that information. All I did was put a bit of PowerShell flare into it. If you are not familiar with the results of this command go ahead and issue it on your computer, if you are part of a domain. It will give you the domain information about the computer and your current login. I like looking at this every so often to check the policies that are applied in the domain.

Anyway there are two AD paths that are returned with this command, I only want the second one. It always returns the computer account first and then your login. So this command provides me that and puts in the clipboard for me:

gpresult /R | Where {$_ -match "CN="} | select -Skip 1 | clip

Combine this with the commands from the article:

$acct = [adsi]"LDAP://<paste result here>"
$acct.psbase.invoke("SetPassword","MyNewPassword")
$acct.psbase.CommitChanges()

You may normally see me write functions for this, but for this particular area I like to see it as it works, just to be safe. You may also see the “invoke” command take a while on some domains, just depends on the architecture I guess.

Once you hit enter on the CommitChanges command your password has been updated.

Posted in PowerShell | Tagged: , , | Leave a Comment »

PS C:\> Create-ServerDirectory

Posted by Shawn Melton on 2013/10/30

If you have to do it more than once, script it and blog about it…

function Create-ServerDirectory
{
    [CmdletBinding()]
    Param
    (
        # server name or instance name of SQL Server to run assessment against
        [Parameter(Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        [string]$location,
 
        [Parameter(Mandatory=$true,Position=1,
            ValueFromPipeline=$true,
            HelpMessage='Provide a single or comma separated list of server names.' )]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        [string[]]$server
    )
 
    foreach ($s in $server) {
        $dirName = $s.Replace("\","_")
        $fullPath = "$location\$dirName"
        if(Test-Path $fullPath) {
            Write-Warning "The path $fullPath already exist"
        }
        else {
            New-Item -Path $fullPath -ItemType Directory | Out-Null
            if(Test-Path $fullPath){
                Write-Host "$fullPath created successfully"
            }
            else {
                Write-Warning "Some issue occurred creating the directory"
            }
        }
    }
}

So a few examples:

Create-ServerDirectory –location c:\temp –server “Server1″,“Server2″,“Server1\Instance1″,“Server3″

OR

$list = “Server1”,”Server2”,”Server1\Instance1”,”Server3”
Create-ServerDirectory –location c:\temp –server $list

OR

<Some cmdlet that passes your server name down pipeline> | foreach {Create-ServerDirectory –location c:\temp –server $_}

Carry on…

Posted in PowerShell | Tagged: , , | Leave a Comment »

With PowerShell comes great things

Posted by Shawn Melton on 2013/10/23

I have been working with PowerShell since sometime around 2010 I think. I don’t recall the exact date because it started somewhat slowly. The previous employers I worked for were not environments I could really take advantage of it very well. I now work as a consultant so I have gotten much more involved in learning the advanced side of PowerShell.

I have developed and continue to evolve a PowerShell script that is utilized for making assessment reports on clients SQL Server environment. I have been looking to advance these scripts and have been searching out training to raise my PowerShell understanding and knowledge.

Oh boy, did I find a good one!!!

Microsoft created the Microsoft Virtual Academy back in 2011. They have gathered a decent amount of courses that you can work through, all for free!!! Now don’t go canceling your Pluralsight account or anything, they don’t have that many courses.

The jewel PowerShell course I found is right here: Advanced Tools & Scripting with PowerShell 3.0 Jump Start. This course is taught by Jason Helmick (Blog|Twitter) and Jeffrey Snover (Blog|Twitter). Now if you have been in PowerShell for a decent amount of time and researched the background you may recognize Jeffrey’s name.

Throughout the course Jeffrey’’ inserts little tidbits of information on the decision making made by the PowerShell team in designing PowerShell and version 3.0. That alone was the best part to me. However the second best part is the advanced level concepts that are taught.

If you want to step it up then take the time to go through the course.

Posted in Learning, PowerShell | Tagged: , , | Leave a Comment »

To the future…

Posted by Shawn Melton on 2013/10/17

I decided to jump into playing with Windows Azure today. I heard on Twitter yesterday that Azure had SQL Server 2014 CTP2 available. After waiting about 30 minutes I was able to login and see this:
image

Well the first thing I wanted to check out was the SQLPS module. Fired up PowerShell and fired off this command:

Import-Module SQLPS –Verbose

After which I ran the following command, to get a full list of “cmdlets” that are available:

Get-Command -Module SQLPS | Out-GridView

This will give you a nice list of commands to start getting familiar with:

CommandType Name ModuleName
Function SQLSERVER: SQLPS
Cmdlet Add-SqlAvailabilityDatabase SQLPS
Cmdlet Add-SqlAvailabilityGroupListenerStaticIp SQLPS
Cmdlet Add-SqlFirewallRule SQLPS
Cmdlet Backup-SqlDatabase SQLPS
Cmdlet Convert-UrnToPath SQLPS
Cmdlet Decode-SqlName SQLPS
Cmdlet Disable-SqlAlwaysOn SQLPS
Cmdlet Enable-SqlAlwaysOn SQLPS
Cmdlet Encode-SqlName SQLPS
Cmdlet Get-SqlCredential SQLPS
Cmdlet Get-SqlDatabase SQLPS
Cmdlet Get-SqlInstance SQLPS
Cmdlet Get-SqlSmartAdmin SQLPS
Cmdlet Invoke-PolicyEvaluation SQLPS
Cmdlet Invoke-Sqlcmd SQLPS
Cmdlet Join-SqlAvailabilityGroup SQLPS
Cmdlet New-SqlAvailabilityGroup SQLPS
Cmdlet New-SqlAvailabilityGroupListener SQLPS
Cmdlet New-SqlAvailabilityReplica SQLPS
Cmdlet New-SqlBackupEncryptionOption SQLPS
Cmdlet New-SqlCredential SQLPS
Cmdlet New-SqlHADREndpoint SQLPS
Cmdlet Remove-SqlAvailabilityDatabase SQLPS
Cmdlet Remove-SqlAvailabilityGroup SQLPS
Cmdlet Remove-SqlAvailabilityReplica SQLPS
Cmdlet Remove-SqlCredential SQLPS
Cmdlet Remove-SqlFirewallRule SQLPS
Cmdlet Restore-SqlDatabase SQLPS
Cmdlet Resume-SqlAvailabilityDatabase SQLPS
Cmdlet Set-SqlAuthenticationMode SQLPS
Cmdlet Set-SqlAvailabilityGroup SQLPS
Cmdlet Set-SqlAvailabilityGroupListener SQLPS
Cmdlet Set-SqlAvailabilityReplica SQLPS
Cmdlet Set-SqlCredential SQLPS
Cmdlet Set-SqlHADREndpoint SQLPS
Cmdlet Set-SqlNetworkConfiguration SQLPS
Cmdlet Set-SqlSmartAdmin SQLPS
Cmdlet Start-SqlInstance SQLPS
Cmdlet Stop-SqlInstance SQLPS
Cmdlet Suspend-SqlAvailabilityDatabase SQLPS
Cmdlet Switch-SqlAvailabilityGroup SQLPS
Cmdlet Test-SqlAvailabilityGroup SQLPS
Cmdlet Test-SqlAvailabilityReplica SQLPS
Cmdlet Test-SqlDatabaseReplicaState SQLPS
Cmdlet Test-SqlSmartAdmin SQLPS

Now go forth and conquer.

Posted in PowerShell, SQL Server | Tagged: , , | Leave a Comment »

XML, glad that is over

Posted by Shawn Melton on 2013/10/10

XML can be one of those things you come across and say “ah, there has got to be a better way”. It is something I have held off working with in SQL Server. However with things for SQL Server performance (e.g. query plans) and such it is hard to stay away from it, if you are a true DBA. My particular desire to understand it more came about after find the deadlock graphs accessible from the System Health session in SQL Server 2008 R2. [Side note about the link, Microsoft I found only wrote up an article on it for SQL Server 2012 but it has been there since SQL Server 2008.]

So how do you pull it out? I think ever one is lead to this article on SQLServerCentral, by Jonathan Kehayias (blog | twitter). So that is how I normally pull it out. imageHowever that just provides a list of rows with the full XML document in each. Which if you only get a few and you never get them again, you might be alright with this…not so much for me. I wanted more information and a pretty table that parsed it all out. This is where I needed to figure out the XML stuff. I looked around the Internet trying to see if folks had already done this type of script, no luck.

I am actually pretty shocked that I could not find a script already, maybe my Bing-fu or Google-fu is off. Most of my searches pointed me toward parsing the XML graphs from Profiler…not going there.

Well I let this subject die down for a while, put it on the back burner (much like my blogging schedule) until a few days ago. I was perusing through previous PASS Summit Sessions and came upon Kendal Van Dyke’s (blog | twitter) session on “Working with XML In SQL Server”, here. [You will need a login to access this presentation, just go ahead and sign up because it is worth it.]. After watching that a few times it provided a greater understanding of what you can do with XML documents.

That led me to try to find a script that parses through the Profiler deadlock graphs since I understood the XML querying stuff a bit more. I could get one that provided output similar to what I wanted and just whittle it down to what I need. I came across a script by Wayne Sheffield right here.

After a bit of work going through it I ended up with the output:

image

Now the script to get the output can be downloaded here: SystemHealth_Parsed_DeadlockInfo.sql.

Posted in documentation, Learning | Tagged: , , , | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 26 other followers