Category Archives: SQL Saturday

How to find a good DBA

You are stupid until proven otherwise

Creating Job Wrecks (Requirements) – Be reasonable, honest, concise specific

Reading resumes – look for job hoppers; red flags things that are impossible; look for language that are trying too hard; Beware “experts”

After 10+ years – if they are listing things like index tables and other basic things, they are trying too hard – they should be doing a lot more than this.

Expert, Proficient, Master – key words to explore what they claim to be an expert in

Question – How are you continuing your education?

Phone screens – initial litmus; measure communication skills and basic knowledge; decide whether or not to bring them in.

Passive interviewing – less you talking, letting them think they should keep talking; when you are being interviewed you can turn this around.

Interview pre-questions – first impressions; small talk before the actual interview; can be used to trip them up later

Questions –

• So what SQL sites you go to?
• If you were going to consider yourself an expert in something what would it be?
• What would you be if you were not a DBA?
• What is it you like about databases?
• Are you self-starter?
• Do you follow any people on twitter?

You can get on a call with 70 users and say anything, but you’d better be right.

Before everything else – explain the format

• What you’ll do
• What you expect
• If there will be labs
• Helpful hints

Say the answer and shut up; If you don’t know say you don’t know.

Question types

• Experience – How long have you been in databases? Are you an expert in anything? Have you put together an HA scenario?
• Knowledge – what is the diffence between delete and truncate? What is a cte?
• Probing – Tell me exactlly how that works

Labs

• Short, discrete pieces of work (1-2 min)
• Nothing that requires specific in-house knowledge
• Something that truly common knowledge

Good: Labs incorportated into the interview

Bad: they have to sit in a room and do stuff

Contract-to-hire is your friend!!!

SMO – Shared Management Objects

Managment Studio uses SMO

SMO emits TSQL

Powershell is a scripting language

Objects – live in memory; have properties and methods.

How does SMO work? memory to engine and engine to memory; once the request is made there are no other requests – the data from that point in time is stored.

Server object contains the connection information

Default happenings –

• get database engine type
• compatability
• database name

Database

Default fields – name

Interesting Fields –

• ID (group)
• Size (retrieved)
• Owner (group)
• PageVerify (group)
• SpaceAvailable (retrieved)
• CompatibilityLevel (retrieved)
• CreateDate (group)
• Many more…

Table

Default fields – schema, name, ID

Interesting Fields –

• IndexSpaceUsed (retrieved)
• IsSystemObject (group)
• Rowcount (group)
• Createdate (group)
• LastModified (group)
• Many more…

Columns

Default fields – Name, ID

Interesting Fields –

• DataType (group)
• Identity (group)
• Nullable (group)
• InPrimaryKey (group)
• Others…

What now?

• Iteration with ForEach
• GetDefaultInitFields
• SetDefaultInitFields
• Use Profieler to continue to fish
• Be specific if performance is key (one byte at a time)
• Be aware of the pipline

Load assemblies

Old way –

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

New way (sort of) –

Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

Other new way –

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=Neutral,...."

Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, Culture=Neutral,...."

Powershell memory considerations – depending on what you are pulling back, and whether or not you have already retrieved the data, the time it takes for the command to be processed will vary.

Different ramifications with foreach.

Powershell is EXCITING!!!

Write a single line of text to a file – It takes whole mess of VB Script to do what Powershell will do in ONE LINE.

“Book another holiday.” | out-file ./myfile.txt -append

List all the processes on the box – lots of VB code vs a single call to get-process in Powershell.

get-service | out-file ./myservices.txt

get-processes

psdrive – allows you to access the registry and sql server like you would any other drive.

Concatenate string – “My name is: $z” – will concatenate irregardless of data type of $z

$s = “localhost”

ping $s

$array = “server1”, “server2”, “server3”

$array

$array[2]

get-service | get-member (or use gm)

get-process | gm

get-service | format-table (FT) <list of columns, comma delimited>

FL – format list

get-service |%{$_.Kill()} – will kill all services and reboot your machine

invoke-sqlcmd

$server | %{invoke-sqlcmd -serverInstance $_ query “select * from ….”}

$server | %{invoke-sqlcmd -serverInstance $_  -Inputfile}

dir | %{$_.script()} -out-file ./mytables.txt

Carrier method

Organize

• Format – when you change the formatting and then alter your procedure this will mess with your plan cache; SQL Refactor by Apex; SQLPrettifier
• Comment –  Pseudocode; this should more of a description of what you are doing rather than the code itself

Breakdown

• Structure plan of attack – sketch your query
• Main query -> subquery ->subquery

Streamline

• Obvious issues – extra rows, columns, unnecessary work, non-SARGable where clauses (cannot be indexed)
• Streamline joins
• Similar subqueries
• Other – functions, etc.