Monthly Archives: October 2013

Back from SQL Summit

Quick post – I am back at work this week from the SQL PASS Summit and I have to say I think it was the best conference I have ever been to. From the sessions to the people and the events, everything was crazy fun. I now see what people mean by #sqlfamily. Even though it was my first time, everyone was so welcoming that it didn’t feel that way. I am looking forward to more of the same next year in Seattle. Coming up soon is SQL Saturday #255 – Dallas (well, actually, it is in Arlington…hmmm) and then SQL Saturday #233 – Washington DC. Heck, I might even make it to the after events this time. If you want to connect with me at either of these events hit me up on twitter – @texasamy.

Optimizing SQL Server Performance in a Virtual Environment with Denny Cherry

T-SQL performance tuning – no differences between virtual and physical

Check host and GPU numbers – look outside of the VM at the host.

Remote Perfmon users – group you can be added to for viewing performance monitor information for the server; you can be added to this group on the server where your VM is hosted.

ESX Top – task manager for UNIX; command line utility. Check for CPU thrashing – looking for percent used.

Check host and guest for disk IO latency.

Balloon memory driver – should be enabled; used by host os to request memory back from the guest os; if this is turned off – prevents host os from paging physical memory to the hosts swap file; paging will occur and you will have random slowness that will be difficult to diagnose.

Reserved memory setting – How much memory you want and how much memory you really need to have (reserved).

Memory deduplication – looking for and stripping out duplicate instances of processes in memory; great for OS memory. Doesn’t work for all SQL Server – unless multiple SQL Servers have the same pages in cache.

Storage configuration options – IO is the same if the disks are physical or virtual; automatic tier adjusting technology if possible. Storage slow? Get faster storage ($$$). Keep OS, data, logs tempdb on separate disks if possible.

Storage deduplication – can greatly improve overall performance; deduplicating the OS virtual disks = save much less data to the array.

VMWare Paravirtualization Driver – optional driver for vSphere Virtual Machines; recommended for high IO workloads; config via VM Properties – select SCSI controller, change type.

Monitoring – look at more levels of the environment; SQL Server, guest OS, hypervisor, etc.

Counters –
reads/writes per sec – correlated counter at the host level
seconds / reads and writes
disk queue
page life expectancy
system processor queue
VM Disk
VM Memory

Timer inside the VM lies – ugly rumor and nothing more.

Multidimensional Array from Array Data in Parameter

While I am not new to the world of coding (and not just T-SQL) I am somewhat new to Powershell. I cobbled together a monitor process that works with GoldenGate a while back and just recently made it less ugly and improved its performance. Beyond that, I am still on the green side.

Enter my current project – automating the SQL portion of our deployments using Powershell. Before this I was taking the list of items to be deployed and generating my own scripts to run using the Red Gate SQL Compare tool. Then came the day when I called one of the scripts from a developer in Powershell. There was no going back. I was hooked. I immediately saw how amazing this could be. And also how my work load would drop.

While I am doing my part in Powershell, there is also some other development going on with the end the developers use – in other words, things are in flux. I have code for the old way they do things and the new way. The new is better in that some of the things I need are built-in to certain aspects so I am able to grab pieces of information and get the things from that I need. With the old, this is not the case. Instead I am passing that information in through parameters.

I had the idea of passing in strings that would then be broken up and inserted into a multidimensional array. With that, I could loop through the array one element at a time, have all the things I needed for each deployment and just sit back and watch the magic happen.

Uhhhhh…did I mention I was green in Powershell?

I had another array already going so I thought I had this. I don’t know if it was because it was late in the day, or if I was a little too confident in my array abilities, but the darn thing was not happening the way I had envisioned. I set the code aside to revisit later with a fresh pair of eyes and a brain that wasn’t so full.

Fast forward to the following week – went back to the same code and FIGURED OUT HOW TO DO IT IN 5 MINUTES. Not only that, but it was far less code than I had previously attempted before.

Here is how I have handled this. Obviously, I would be replacing the text in the strings with information that is relevant to my deployments but the concept is the same. Once I have the array created, I have all the elements I need and it is just a matter of knowing where they are in the array and accessing them.

$strings = @("The cat in the"
,"hat ate a rat"
,"and then sat and"
,"got fat from the rat")

$array = New-Object System.Collections.ArrayList
$array.Clear();

foreach($item in $strings)
{
$array.Add(@($item.Split(" "))) | out-null;
}

for ($i = 0;$i -lt ($array | Measure-Object).count;$i++)
{
Write-Host $array[$i][0]
}

For the sample I am writing the first element. For my project the first element might be the server name for the deployment or some other relevant piece of information and it would be in the same place throughout the array. Since variables and their contents persist in Powershell I added the statement to clear the array before it is loaded. I am also wrapping the code up in a function to keep things tidy.

If you find this useful let me know. Enjoy. 🙂