Paging Doctor Powershell

  • WI-DBA

    SSCrazy

    Points: 2110

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

    Cheers
    http://twitter.com/widba
    http://widba.blogspot.com/

  • SQLRNNR

    SSC Guru

    Points: 281243

    Nice article and useful tool.

    Thanks

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

  • WI-DBA

    SSCrazy

    Points: 2110

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

    Cheers
    http://twitter.com/widba
    http://widba.blogspot.com/

  • dawaller

    SSC Veteran

    Points: 211

    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.

    Thanks,

    David

  • WI-DBA

    SSCrazy

    Points: 2110

    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.

    Cheers
    http://twitter.com/widba
    http://widba.blogspot.com/

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    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

  • WI-DBA

    SSCrazy

    Points: 2110

    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{

    param(

    [string]$ServerInstance,

    [string]$Query

    )

    $QueryTimeout=30

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

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

    $conn.ConnectionString=$constring

    $conn.Open()

    if($conn){

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

    $cmd.CommandTimeout=$QueryTimeout

    $cmd.ExecuteNonQuery() | out-null

    $conn.Close()

    }

    }

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

    Cheers
    http://twitter.com/widba
    http://widba.blogspot.com/

  • Aaron Street

    SSC Enthusiast

    Points: 199

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

  • WI-DBA

    SSCrazy

    Points: 2110

    This is probably what you are looking for http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

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

    Cheers
    http://twitter.com/widba
    http://widba.blogspot.com/

  • Aaron Street

    SSC Enthusiast

    Points: 199

    How could you exclude certain error codes in array string?

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

    Ex.

    $IgnoreErrorCode=@(

    '1608',

    '17832',

    '17824',

    '18456';

    )

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

    try{

    $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"

    }

  • paulo.morgado

    SSC Journeyman

    Points: 77

    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 11 (of 11 total)

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