#sqlrally Thursday notes-a-plenty

14 May

The Dirty Dozen: PowerShell Scripts for the busy DBA by Aaron Nelson

I learned a lot at this first session on Thursday but that was mostly because I didn’t have much up there to begin with when it comes to powershell. The syntax reminds me of perl and makes me want to vomit. I had been avoiding it until I heard about the vmware scripts released and the extensive sharepoint administration scripts that are written and available.

Aaron first made it clear that ps isn’t quite finely tuned for SQL yet. So here are my notes


The PS development environment is ISE, from here you can run and debug.

Profiles are important and portable

Stick to a verb-noun naming convention

Get-Help works for most commands


$vars are typeless :/ and can be defined inline

Cmdlets are .net code addins

SQL servers can be added and are represented like drive paths

Specify “default” or the instance name

Format-table –auto is useful when viewing results

-force will show all the information which some is randomly hidden from users

Tip: ctrl+shift will run as admin, need elevated privs for service control etc…

Can save profiles to share and import them automagically

Ps sends email with send-mail


Troubleshooting Performance Problems by Reading the Waits by Eddie Wuerch

This was probably one of the most valuable sessions I attended all SQLRally. One of our worst performing systems is our datawarehouse, but we have developers that have access to the SERVER STATE. They have handled the wait monitoring which has caused me to delay learning about this topic. The value set in when Eddie told us that this is what he dives into first when he gets the 3am call. I have looked at waits in activity monitor but never understood them. I attempted to translate BOL to English but failed. Eddie did an excellent job at this translation and included common problems with their fixes.
One of the keys is signal wait time, this is CPU wait after the thread has acquired all of locks and other resources. Subtracting signal wait time can help you focus on the database waits. Eddie didn’t mention this but I connected the dots from his explanation and what a co-worker has said months ago.

LCK_M_X extra blocking

Pagelatch – hotspotting (update counters records), lots of #tables
These occur in mem

Can identify the wait resource (##:####:####) dbid:fileid:pageid. Once you get the page you can do DBCC Page(Pageid) and find out what might be on this thing.

Trace on 3604

PageIOLatch – disk waits, usually always a majority of waits because this is the slowest component

WriteLog – tranlog problems, maybe isn’t on right drives or you have VLF problem (I’ve seen 3million vlf’s before, VLF problems might be seen when a database takes a long time to recover after a reboot)

Resoruce Semaphore – mem issue

Sleep_bpool_flush – mem issue, lots of dirty pages being flushed

Sos_shced_yield – CPU

ASYNC_NET_IO – network problems


Wesley Brown has an awesome and hilarious session on storage systems. I dived out of this one because it is not a weakness of mine. I have troubleshot SAN slowness and identified bottlenecks in HBAs. I did get the opportunity to chat with him in the birds of a feather session. Great guy and lots of gut busting jaw dropping stories from his IT vast experience.

I dropped into the DEV session Avoid common Mistakes in T-SQL programming by Alex Kuznetsov. He has a slight accent but the guy is a machine. Incredibly knowledgeable and showed me mistakes in a very organized fashion which I appreciate.

When a query has multiple where clauses they do not evaluate left to right. The optimizer picks which order to evaluate these clauses.

Mistake: select Name, DOB from tblCust where if ISDATE(DOB) and Cast(DOB, varchar(20))

– The cast would fail because it could be processed before the isdate
– To fix this common mistake use CASE WHEN ISDATE(DOB) then cast(DOB)

The settings that are defined can change query results. Such as English date conversions

Queries do not have a default order. TOP n queries can return different results. Index changes can cause code to have issues because that defines how data is laid out.
– To fix use the order by cause

TYPE conversions are common errors because they work with the tested values but production values can be larger or more unique cases

Small int would overflow if the value was 40,000. Varchar(5) would concat (awesomesause) to (aweso) without an error

Small date has rounding inconsistancies, close to midnight might round up to next day (or year on 12/31)

Be careful on the definitions inside of stored procs, same issues. Also for SPs, explicity specify which var you are passing in to the SP, because order can change.

Decimal rounding has strange results,
– To fix ABS(@dec-)

Select is faster that SET

Using decimal variables for loops

Always use aliases when using multiple tables

Alex gave some great examples of concurrency issues that arise because testing often lacks multiple users

A banking example was used with two query windows or “users” BEGIN TRAN on one side and before the first was COMMIT then second tran would attempt to aquire locks but would fail with deadlocks because the resource was accessed in different order.
– Possible fix would be to set read committed snapshot isolation on, this way writes don’t block reads but you have a lot more tempdb activity.


I then attended Jennifer McCown’s session on T-SQL Code Sins: The Worst Things We Do to Code and Why. Jen’s auctioneer style of presentation keeps you on your toes. My main takeaways where:

– NamingConventions: use good names. I laughed on the inside because DB2 on mainframes are limited to all caps 8 char names which is deciphered by a third party product to make useful names.
– GUID primary keys are a waste of valuables space.
– No FKs for performance (bad idea)
– Can query referencing_entities for cascading deletes
– GO n will run queries “n” times
– Don’t repeat selects, use one query to get all the data needed
– No docs or testing


I planned on going to another dev session but was a bit burnt out on t-sql. I felt a responsibility to go to the only security session in both days of sessions. The session was Data Encryption and Key Management in SQL by Said Salomon.

R2 generates a self signed Service Master Key and there is one per instance and is stored in protected memory(? Was a bit confused on this)


Said discounted using Asymmetric keys because of 100x performance hit.

The Service master key is used to encrypt sub keys. These sub keys actually encrypt your data and different algorithms can be used when generating these keys.

There are encrypt and decrypt functions that turn data into varbinary data. The user running these functions needs VIEW DEF and other key privs to de/encrypt data. Create a role for this priv

Can regenerate the SMK without breaking child keys because SQL manages these keys.

The child keys are stored in the database


I hopped over to the lightning talks instead of the DR session I had planned on going to. Again, I made the correct choice. Exactly what I needed was a thrilling 5 minute Scary DBA rant about backups and restores. Grant was spitting mad and describe lazy DBAs who do not fully test their restores. I am one of those people. I have done restore testing but I need to do this all the time. I have luckily avoided RGE’s or “resume generating events” but luck will no longer be a factor going forward.

Jen also did another auctioneer style 5 minute intro to T-SQL. This was very impressive and a good refresher.

The professional development speaker also had great advice on how to become a lynchpin in your organization.


All of this information is trumped by the value of networking with my peers and mentors. A solid team is far greater than any individual.

Leave a comment

Posted by on May 14, 2011 in PASS, SQL Admin, SQL Dev


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: