Quickie, based off an earlier post. (http://thebakingdba.blogspot.com/2012/02/tuning-statistics-when-were-they.html)
Get the last 4 stat updates for every statistic based on an index. The filter is on the auto_created; flip that to get all the system-created (aka _WA_Sys_00000003_53D770D6) stats, or index-based.
Wednesday, August 10, 2016
Wednesday, August 3, 2016
[Powershell] some basic blocks of code written this week.
Update 2016/08/10 since I need an anterior brain, and this blog is it, I'm now making this post my "living powershell" document. I'll update it as I run into problems. Today: $a vs "$a" vs $a.name (see end)
Mike Fal, a Powershell DBA extraordinaire, would probably shake his head at these. But given the hard lessons learned this week, I thought I'd share. I had to rewrite a bash script in Powershell, and some of the things I hadn't done before, so there was definitely a learning curve. And we'll see how the error trapping is - Idera has a webinar covering error-handling in Powershell next week.
----------------------------
First up, a basic powershell function that writes to a file. The encoding isn't strictly necessary here, but I use the code elsewhere. For whatever reason, standard out-file adds a couple of low-ascii characters at the beginning of the file, which other utilities such as psftp choke on. (http://stackoverflow.com/
"mget TESTAREA/$FL" |out-file -append -encoding ascii $CMDS
I should just have the function accept a parameter. And for whatever reason, when it writes to the log, it doesn't include newlines.
#Write whatever is in "$logmsg" to the log file $LOGFILE
function logstamp(){
$loginfo = get-date -format "yyyyMMdd.HH:mm:ss" #MM is MONTH, mm is minute
"$loginfo $logmsg" |out-file -append -encoding ascii $logfile
$loginfo=""
$logmsg="" }
------------------------------
I needed to parse out a Unix filename from a file. The original "ls" looked like this:
-rw-rw-r-- 1 psabcderfg data 646621 Jul 19 16:25 myfile16071901.Z
The unix command to parse it would
cat $fl |awk '{print $9}'
And the powershell equivalent is:
$newfiles = get-content $fl | foreach {$_.split(" ")[8]}
(the number is 8 instead of 9 because powershell arrays start at 0)
(2016/09/07 but I've run into times when it IS 9. WTF?)
------------------------------
A basic unix2dos-style file change. Found at https://sus42.wordpress.com/
get-content $thisfile |% {$_.replace("`n", "`r`n")} | out-file -encoding ascii -filepath $thatfile -Force
Since it outputs to a new file, the timestamps won't match, but you could use set-filetimestamps -doc $doc -date $date to change them.
------------------------------
This next part is weird but also very handy. Because of the way Powershell handles messages returned from applications, as well as standard error, you have to do use a trick to get out all the messages. I needed this because I wanted all the messages from the application, 7zip here, but also stuff like psftp.
$logmsg = cmd /c 7za.exe e -aoa $thisfile 2>&1 | %{ "$_" }
if ($LASTEXITCODE -ne 0)
{ logstamp; $logmsg = "7-zip Command to extract $thisfile returned exit code $LASTEXITCODE"; logstamp; throw "7-zip Command to extract $thisfile returned exit code $LASTEXITCODE" }
else { logstamp; $logmsg = "7-zip Command to extract $thisfile finished successfully"; logstamp }
First, we're invoking it with cmd instead of start-process or just a dot invocation. That's to make sure we get the messages out, but also to prevent powershell from choking if the app throws an error. Next up is getting all the messages out from the app and stderr, which is the code from "2>&1" until the end of the line.
http://stackoverflow.com/
So now, we have a couple different things to look at. $logmsg contains the entirety, both error messages from 7zip, and standard error.
$LASTEXITCODE returns the result code from the APP CALLED. When it couldn't find the file to zip, $lastexitcode returned was 1. Otherwise was 0 (success).
Next is $error[0].TargetObject, which I believe is the stderr from the app.
-----------------------------------------
Was running into a problem invoking cpio. I was having to make a unix-formatted file, and the way I'd check is by opening it in notepad. Except...
$ldclist = get-childitem * -include *out.Z
I wanted to write those out to a file. So I thought I'd do this to test:
$ldclist
That does indeed give me the directory info on each file.
But I just want the name of each file and didn't think about $myvarname.myfieldname.
So I ran this:
"$ldclist"|out-file -append -encoding ascii listoffiles
Which, superficially looks right. My script auto-converted it to unix-format, and then when I open it in notepad, it's all on one line. Denoting LFs, right? Nope. Using "$myvar" means it returns it as a list, space-separated.
Just to make it more fun, let's try this:
$ldclist.name|out-file -append -encoding ascii a
"$ldclist.name"|out-file -append -encoding ascii b
Those should be close, right? We now know one would return the space-separated list (the quotes converting it to a string, IIRC), and the other the correct list? Nope. The first one returns just the base filenames. The second one, in the quote, returns the full filename including path, space-separated.
*sigh*
-----------------------------------------
Additionally, problems arise when trying to do something like this:
$logmsg = cmd/c 7za.exe -e -aoa $thisfile -o"$newdirectory"
It just doesn't work. It actually dumped them into a folder called $newdirectory.
I wound up having to do the below, though I still couldn't use double-quotes. I know there's gotta be a way, but at this point I give up.
$command = "7za.exe -e -aoa $thisfile -o$newdirectory"
$logmsg = cmd /c $command 2>&1 | %{ "$_" }
---------------------------------------------
2016/09/29 hey, a new installment! I have a ton more ,but this one drove me up a wall today, so here we go.
This time - powershell helping with small arrays.
I was trying to make sure I can hit a server and pull back some server info.
One thing I wanted to look at was the number of drives the server had. Which worked great - on some servers. On the others, blank. Not 0, just... blank.
Here's the command I was running:
$drives = (gwmi -cn $compname -Class win32_logicaldisk -ErrorAction silentlycontinue | where {$_.Size -gt 0}).count
What's different? Well, some of the servers only have 1 drive.
Turns out, if there's only 0 or 1 members in what's returned, it assumes it doesn't need to be an array. Which means that count, for instance, won't work on it properly.
Fix:
$drives = gwmi -cn $compname -Class win32_logicaldisk -ErrorAction silentlycontinue | where {$_.Size -gt 0}|measure
Mike Fal, a Powershell DBA extraordinaire, would probably shake his head at these. But given the hard lessons learned this week, I thought I'd share. I had to rewrite a bash script in Powershell, and some of the things I hadn't done before, so there was definitely a learning curve. And we'll see how the error trapping is - Idera has a webinar covering error-handling in Powershell next week.
----------------------------
First up, a basic powershell function that writes to a file. The encoding isn't strictly necessary here, but I use the code elsewhere. For whatever reason, standard out-file adds a couple of low-ascii characters at the beginning of the file, which other utilities such as psftp choke on. (http://stackoverflow.com/
"mget TESTAREA/$FL" |out-file -append -encoding ascii $CMDS
I should just have the function accept a parameter. And for whatever reason, when it writes to the log, it doesn't include newlines.
#Write whatever is in "$logmsg" to the log file $LOGFILE
function logstamp(){
$loginfo = get-date -format "yyyyMMdd.HH:mm:ss" #MM is MONTH, mm is minute
"$loginfo $logmsg" |out-file -append -encoding ascii $logfile
$loginfo=""
$logmsg="" }
------------------------------
I needed to parse out a Unix filename from a file. The original "ls" looked like this:
-rw-rw-r-- 1 psabcderfg data 646621 Jul 19 16:25 myfile16071901.Z
The unix command to parse it would
cat $fl |awk '{print $9}'
And the powershell equivalent is:
$newfiles = get-content $fl | foreach {$_.split(" ")[8]}
(the number is 8 instead of 9 because powershell arrays start at 0)
(2016/09/07 but I've run into times when it IS 9. WTF?)
------------------------------
A basic unix2dos-style file change. Found at https://sus42.wordpress.com/
get-content $thisfile |% {$_.replace("`n", "`r`n")} | out-file -encoding ascii -filepath $thatfile -Force
Since it outputs to a new file, the timestamps won't match, but you could use set-filetimestamps -doc $doc -date $date to change them.
------------------------------
This next part is weird but also very handy. Because of the way Powershell handles messages returned from applications, as well as standard error, you have to do use a trick to get out all the messages. I needed this because I wanted all the messages from the application, 7zip here, but also stuff like psftp.
$logmsg = cmd /c 7za.exe e -aoa $thisfile 2>&1 | %{ "$_" }
if ($LASTEXITCODE -ne 0)
{ logstamp; $logmsg = "7-zip Command to extract $thisfile returned exit code $LASTEXITCODE"; logstamp; throw "7-zip Command to extract $thisfile returned exit code $LASTEXITCODE" }
else { logstamp; $logmsg = "7-zip Command to extract $thisfile finished successfully"; logstamp }
First, we're invoking it with cmd instead of start-process or just a dot invocation. That's to make sure we get the messages out, but also to prevent powershell from choking if the app throws an error. Next up is getting all the messages out from the app and stderr, which is the code from "2>&1" until the end of the line.
http://stackoverflow.com/
So now, we have a couple different things to look at. $logmsg contains the entirety, both error messages from 7zip, and standard error.
$LASTEXITCODE returns the result code from the APP CALLED. When it couldn't find the file to zip, $lastexitcode returned was 1. Otherwise was 0 (success).
Next is $error[0].TargetObject, which I believe is the stderr from the app.
-----------------------------------------
Was running into a problem invoking cpio. I was having to make a unix-formatted file, and the way I'd check is by opening it in notepad. Except...
$ldclist = get-childitem * -include *out.Z
I wanted to write those out to a file. So I thought I'd do this to test:
$ldclist
That does indeed give me the directory info on each file.
But I just want the name of each file and didn't think about $myvarname.myfieldname.
So I ran this:
"$ldclist"|out-file -append -encoding ascii listoffiles
Which, superficially looks right. My script auto-converted it to unix-format, and then when I open it in notepad, it's all on one line. Denoting LFs, right? Nope. Using "$myvar" means it returns it as a list, space-separated.
Just to make it more fun, let's try this:
$ldclist.name|out-file -append -encoding ascii a
"$ldclist.name"|out-file -append -encoding ascii b
Those should be close, right? We now know one would return the space-separated list (the quotes converting it to a string, IIRC), and the other the correct list? Nope. The first one returns just the base filenames. The second one, in the quote, returns the full filename including path, space-separated.
*sigh*
-----------------------------------------
Additionally, problems arise when trying to do something like this:
$logmsg = cmd/c 7za.exe -e -aoa $thisfile -o"$newdirectory"
It just doesn't work. It actually dumped them into a folder called $newdirectory.
I wound up having to do the below, though I still couldn't use double-quotes. I know there's gotta be a way, but at this point I give up.
$command = "7za.exe -e -aoa $thisfile -o$newdirectory"
$logmsg = cmd /c $command 2>&1 | %{ "$_" }
---------------------------------------------
2016/09/29 hey, a new installment! I have a ton more ,but this one drove me up a wall today, so here we go.
This time - powershell helping with small arrays.
I was trying to make sure I can hit a server and pull back some server info.
One thing I wanted to look at was the number of drives the server had. Which worked great - on some servers. On the others, blank. Not 0, just... blank.
Here's the command I was running:
$drives = (gwmi -cn $compname -Class win32_logicaldisk -ErrorAction silentlycontinue | where {$_.Size -gt 0}).count
What's different? Well, some of the servers only have 1 drive.
Turns out, if there's only 0 or 1 members in what's returned, it assumes it doesn't need to be an array. Which means that count, for instance, won't work on it properly.
Fix:
$drives = gwmi -cn $compname -Class win32_logicaldisk -ErrorAction silentlycontinue | where {$_.Size -gt 0}|measure
So the "measure" counts the number of items, even if it's only 1.
Then when I query it, I use $drives.count, which gives me the proper number/.
[Presenting] SQL Saturday San Antonio just around the corner! #SQLSatSA
Just a friendly reminder. SQLSaturday San Antonio 2016 is next weekend (occurs August 13th), at Texas A&M University San Antonio!
http://www.sqlsaturday.com/550/EventHome.aspx
I'm looking forward to this. I was trying to pick courses to take, and it's difficult. Courses on Hekaton, Clusters, Performance Tuning, 2016 features, Plan Cache... even one on Service Broker, one of my favorite hidden features! Some "big names" there, too - Kalen Delaney, who also has a pre-con! Speakers all the way from Pittsburg, Seattle, Albuquerque and more.
I'll be speaking on the same thing I've done a bunch - Event Notifications. Same speech, a few new tricks and tips, but mostly it's about DBA empowerment. If you don't know what code was changed 3 weeks ago, what hope do you have for tracking down "why did this stop working"? Be the Overlord your end-users need.
Subscribe to:
Posts (Atom)