Monthly Archives: August 2013

SQL Saturday #223 – Mouth Wide Shut: Coherent Interviewing Part 2 with Sean and Jen McCown

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


  • 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!!!

SQL Saturday #223 – SMO Internals for High Performance Powershell with Ben Miller

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


Default fields – name

Interesting Fields –

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


Default fields – schema, name, ID

Interesting Fields –

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


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 –


New way (sort of) –

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

Other new way –

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

Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=, 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.

SQL Saturday #223 – Beginning Powershell for DBAs 1.0 with Sean McCown

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


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”



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


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

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

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

Carrier method

SQL Saturday #223 – Unraveling Tangled Code with Jen McCown


  • 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


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


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

SQL Saturday #223 – Storage Tuning Deep Dive with Wes Brown Joh Sterrett

Intro to table partitioning

  • Enterprise Edition required
  • LOTS of data – highly transactional tables with 50GB+

How can it help

  • reduce maintenacne tasks – indexing, restores/backups
  • improves purging/archiving

Partition Table -> Partition scheme -> Partition function

Selecting Parition Colums

  • You only get one – pick wisely!
  • Highly used filter – review indexes; review missing indexes/stats; review queries
  • column must be pasrt of clustered index or PK

When partitioning on dates, rounding can affect the date and possibly what patition your data will be in

Improving maintenance tasks

  • backup/restore filegroups based on priority
  • index maintenance by partition
  • new in SQL 2014 – rebuild index by partition; update statistics by partition; can also be done online in 2014


Partition Elimination – view in execution plan

How does it improve archiving and purging – can reduce the time processes take from hours to minutes by utilizing the sliding window

Meta dataswap – requires schema lock; alter table <blah> switch partition 2 to <blah_staging>

Sliding window goals

  • split and merge emplty partitions
  • switch to do metadata swaps
  • minimize physical data movement

Sliding Window Steps

  1. Create partition swap
  2. Insert partition swap meta data
  3. Create staging table
  4. Meta data swap
  5. Merge partitions 1 and 2
  6. Mark next used
  7. Split to create new partition
  8. Update processed partition swap meta data


SQL Saturday #223 – SQL Security Best Practices and Shrinking Your Attack Surface with Matthew Brimer

How to break into Sql Server in 15 min – he can do it in 3 min

Windows authentication – rename/disable sa

Use GPO or PBM to restrict access times to logins that should not be doing things after hours

Testing permissions – should do but not everyone does

Permissions to backup and data files – if someone can access your backup/data they can create their own instance with the backup or data files

Separating user accounts

Data integrity – data is accurate and reliable; has not been changed or tampered with by an unauthorized person

Common forms of data integrity-

  • hashing/checksums
  • source control locking
  • isolation levels
  • data modeling/types constraints

C2 – just say NO! Only if there is a business reason!

Build your own auditing with sp_trace_create

fn_trace_gettable – auditing reports

Geo clustering – seamless Site2Site failover; one connection string; apps un-aware of failover; $$$$ to implement; SAN level replication or 3rd party too to handle bit level replication.

Always On (SQL Server 2012) – requires availability groups; one/multiple connection strings; can leverage multiple servers at the same time to offset reads/writes on different servers; offload backup overhead to another server on another site; user databases only so permissions managed per instance.

Database Mirroring – user databases with 1 mirror max; synchronous/asynchronous; automatic failover for witness – depending on where can report false positve of failover; permissions on both servers; repairs torn pages and send changes back to principal.

Log Shipping – only as good as your last log backup; requires file shares – permissions?

Replication – “picture” of the database from a point in time; can be applied to multiple servers; snapshot/merge/transactional; no mention of intializing through backup.

Secuity Best Practices

  • change default port – network enumeration/fingerprinting?
  • revoke connect from guest – any database permission can see any other database
  • Disable xp_cmdshell
  • Disable un-needed protocols (VIA,TCP/IP,shared memory, named pipes)
  • Enable Common Criteria Compliance (standard in 2008)
  • Disable SQL Browser – not possible with named instance or cluster
  • Hide instance – will not respond to eneumeration requests but will respond to telnet requests
  • Minimal Service Install – no BIDS in production; separate dev from prod
  • Baselines – Baseline Analyzer; know your sysadmins; SQL permissions/users; databases
  • No DMZ, IIS or Domain Controllers
  • Dev/Test Data – mask sensitive data; MSSQL.DataMask


Starting with something weird

For my first post I thought I would start things out with a bang and detail an issue that I recently encountered:

Location: “qstopsrt.cpp”:404
Expression: fFalse
SPID: 51
Process ID: 2908
Location: qxcntxt.cpp:1221
Expression: cref == 0
SPID: 51
Process ID: 2908
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

In case you were wondering that CHECKDB was a waste of time (in this situation). Had never seen something like this before, but who doesn’t like a good challenge?

If you have seen this error, and tried to research it, you know there is not much out there. Most of what I found had to do with issues with the msdb or master databases. Those did not apply to my situation.

Anyways, on to the code:

[personid] as [personid],
[shoeid] as [shoeid],
[shoe_name] as [shoe_name],
DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) as [days_since_purchase],
where [Paid_in_full] = '1'
ORDER BY [days_since_purchase] desc

[personid] as [personid],
[shoeid] as [shoeid],
[shoe_name] as [shoe_name],
[shoe_type] as [shoe_type],
[shoe_size] as [shoe_size],
DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) as [days_since_purchase],
dateadd(day,datediff(day,0,delivey_date),0) as [delivery_date],
CASE WHEN [Paid_in_full] = 1 THEN 'YES' ELSE 'NO' END as [Paid],
[purchase_date] as [purchase_date]
WHERE 0 = 0
AND ((((1=1))))
AND ( ( ( (
([shoe_type] = 'Stiletto')
AND ([Paid_in_full] = '1')
AND ( (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '23')
OR (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '24')
OR (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '25') )
AND ( ([shoe_rating] < '100') OR ([shoe_rating] IS NULL))) OR ( ([Paid_in_full] = '1') AND ( (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '23') OR (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '24') OR (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '25') ) AND ( ([shoe_rating] < '50') OR ([shoe_rating] IS NULL ) ) AND ( ([shoe_type] = 'mule') OR ([shoe_type] = 'athletic') OR ([shoe_type] = 'flat') ) ) ) ) ) ORDER BY [days_since_purchase] asc

Other useful background info:

  • Both queries produce the same error.
  • 3rd party application code - dynamically generated; cannot be changed.
  • Happend on a production reporting server.
  • View queries two tables with a union; tables are refreshed hourly.
  • Not all data is pulled from the tables - some fields are defined in the view.

Researched the error, but like I said - didn't find much that was of any use. Picked someone else's brain on the issue - he said there was an additional thing that could be added to the TOP 50 - WITH TIES. I changed the first query to a TOP 1 WITH TIES and got five records back. Kept working with both queries, trying different things. Eventually found that if I left the Paid_in_Full field out of the where statement the query would complete.

Examined the view - Paid_in_Full was defined as '1' in the view for one table and '0' for the other. Surmised from this that the implied data type could be the cause of the problem.

Solution that resolved the issue - modified the process that populates the tables to include this field (meaning the datatype would now be explicit).

Funny how something so simple could cause an issue so obscure.

SQLKitten is finally live

Finally got set up. More to come shortly.