Paging Doctor Powershell

  • Comments posted to this topic are about the item Paging Doctor Powershell


  • Nice article and useful tool.


    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks. If folks have ideas on things to add to it, let me know and I can work them in and share the script.


  • This looks like some great stuff, however I am new to powershell. Do you have any step by step instructions for running the scripts? I would like to try it out in my test servers.



  • I don't have anything step by step - but I could easily create a step by step article or a set of blog posts that cover it. Is that something people would like to see? It could be articles or I could just blog it in small bites.

    The script I uploaded should be able to be run as long as you have Powershell 2 installed without any issues. (you shouldn't need any experience) Just give it a server name and a few hours and let it run.


  • nice article, going to have to look at it. right now i have perfmon running on a server dumping data to a central database where i run reports from. this is a lot lighter, but i was going to use the perfmon data for historical baselines as well. one thing i don't like about powershell is that you can't seem to dump the data into SQL

  • You can dump directly to SQL, I do that for gathering stats for our VM admins. I don't like looking at csvs or text files anymore than the next DBA.

    Once you have a list of counters (note I have some extra properties in my object collection) - you could simply call a proc (or just directly insert them)

    $CounterCollection | Where-Object{$_.Path -ne $null} | ForEach-Object{

    $qry = "EXEC .dbo.pr_Add_Perf_Collection "

    $qry = $qry + " @server='" + [string]$_.Server + "'"

    $qry = $qry + ", @performance_metric='" + [string]$_.Path + "'"

    $qry = $qry + ", @performance_value=" + [decimal]$_.CookedValue

    $qry = $qry + ", @date_sampled='"+ $_.Timestamp + "'"

    $qry = $qry + ", @samples_taken=" + $_.Samples

    $qry = $qry + ", @sample_interval=" + [int]$_.SampleInterval

    Invoke-SqlNonQuery -ServerInstance $SQLServerToStore -Query $qry


    function Invoke-SqlNonQuery{






    $conn=new-object System.Data.SqlClient.SQLConnection

    $constring = "Server=" + $ServerInstance + ";Integrated Security=True"




    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)


    $cmd.ExecuteNonQuery() | out-null




    Hope this helps. I will see about writing a perfmon metric gather/storage article if SSC wants to publish it.


  • Examples would be helpful how to pipe data directly to sql server database.

  • This is probably what you are looking for

    Note it uses bulkcopy and there are some restrictions with that.


  • How could you exclude certain error codes in array string?

    The below example does not seem to filter errors from my array.








    # Write-Host "Reading SQL Log for $sqlServerName"


    $sqlServer.ReadErrorLog() | Where{$_.LogDate -is [datetime] } |

    Where-Object{$_.LogDate -gt $DatetoCheck } |

    Where-Object{$_.Text -like "*Error*" -or $_.Text -like "*Fail*"} -and ($_.Text -notcontains $IgnoreErrorCode) |

    Select-Object LogDate,Text |

    Out-GridView -Title "$sqlServerName Log Errors"

    } catch {

    Write-Host "Error Reading $sqlServer.Name"


  • As a norm, I only use double quotes when I intend the string to be interpolated. When I want to use the string as it is, I use single quotes. It's not much, but why waste CPU and time for nothing?

    You can filter the WMI instances in the query:

    Get-WmiObject -Class Win32_Service -Filter 'Name like "%SQL%"'

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply