mattypenny

Clustering Conundrum - Allan Hirt

Running some HA classes in Europe.

Loose definition - networked servers.

Used in different contexts in sqlserver - clusterered indexes, SETI type stuff, windows clusters, NLB, WSFC

• Windows - network load balance, windows server failover cluster(wsfc)
• Sqlserver - failover cluster instance

Can’t have the latter without the former

Sqlserver built on WSFC

75% of problems tend not to be SS

Resource group - like a folder of resources

Resources are often interdependent

Quorum - what you need to have to be up or down

Has become much easier from 2008 onwards.

Availability Groups - are sql-level

www.sqlha.com/2012/01/0… www.sqlha.com/2013/04/2…

Need to get involved with the Windows very heavily - can’t just do the sql bit. <— big takeaway

DBAs tend to become expert in Windows clustering

Can’t convert a standalone instance to cluster. Has to be new install.

Can do a 2-node cluster at Standard Edition.

2008 needs to be Windows Enterprise. 2012 onwards can be standard or datacenter editions

You get a ‘tranport error’ during failover, but SSMS, at least, automatically failsover.

Got to have AD

Only one default instance per WSFC, but can have multiple instances on one WSFC

Keys to success Learn WSFC Validation can show up problems Need to have test clustered instances Need to be an WSFC expert

www.sql ha.com MS Cluster Team blog KBs: find the slide

Hands-on fundamentals for good SQL programming - Kevin Kline

Test harness needs: dbcc freeproccache dbcc dropcleanbuffers set statistics time set statistics io set showplan text|xml

sys.dm_os_performance counters sys.dm_os_wait_stats

sqlskills.com - Glenn Berry

Scan count - number of scans! Reads - in 8K pages Read-ahead - it dynamically goes multi-page read (i.e. multiblock reads)

set showplan_text on - does the command line style explain plan

clustered index scan is equivalent of an Oracle FTS, because, by default tables are held in clustered indexes

Can set statistics time and i/o from within the toolbar

Worth enabling:

• estimated execution plan and

• actual execution plan

• it shows up differences, which can show problems with statisitcs etc

Sys.dm_os_wait_stats

• shows bottlenecks

Sys.dm_exec_query_stats

• query performance stats

sys.dm_db_index_usage_stats

sys.dm_io_virtual_file_stats

Queries from SQLAgent man ?

Spools are red flags.

As are, scans, parallelism

Kimberly Tripps articles on ‘indexes from every angle’

Window Function Fundamentals - Christina Leo

Stuff like:

SELECT empid, ordermonth, qty, SUM(qty) OVER(PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runqty FROM Sales.EmpOrders;

Indexing segment and sequence operators in Explain Plan

Index should be POC

• Partition
• Ordering
• Columns

Even if this doesn’t improve the elapsed time of the query it will eat up less memory.

The where clause happens before the select, so the filter happens before the Window function kicks in

christinaleo.net

CTE - the view thing at the top of the query with the WITH

Powerpivot - Susan O'Connell

Free add-in in 2010, native in 2013

Very worthwhile to leeave stuff out of the data model, both for performance and so as to simplify from a user perspective.

There are options to hide stuff from the users anyway.

Needs 8G of RAM on client

Data Internals - Bradley Balls

@sqlballs

Why learn internals - it conveys the idea that you know what you’re talking about!

Byte swapping - swaps hex pairs to protect proprietry data

dbcc ind dbcc page dbcc traceon(3604) -output goes to smss window

Record structure

• Tag bytes
• null bitmap
• fixed length
• null bitmap
• variable offset attay
• variable length columns

forwarding records - only in heaps like chains in Oracle

• only occurs in heaps

Text records

• LOBS > 8000 bytes (or sometimes 4000 bytes)
• 24 byte pointer to LOB

Ghost record

• deleted record left in place for undo optimization
• when in snapshot isolation mode

Versioned Records

• when Version Store os used. e.g. got online index rebuilds

MCM Video Series - especially

Record structure

• Tag bytes
• null bitmap
• fixed length
• null bitmap
• variable offset attay
• variable length columns

In the bitmap, 1 is null, 0 is not null

Record structure

• Tag bytes
• null bitmap
• fixed length
• null bitmap
• variable offset attay
• variable length columns

Pages

Data page header 96 bytes data 8096 bytes Row offest array (aka slot array) at the bottom

Index page leaf non-leaf

Text text tree lob text mox slob

sort

boot page 1:9 in any database dbcc page (‘db name’ , 1, 9, 3) - shows lots of boot info about database <— worth doing

Extents

• grouping of 8 pages

Allocation bitmaps

• free pages and allocated pages

Structure is: page is allocated is mixed allocation page is iam page page has ghosted rows space indicator - 3 bits show whether over 10% full, 30% full etc

IAM page

Storage and Virtualization - Denny Cherry

With Fusion IO, disk i/o is no longer the bottleneck

OLTP systems don’t much use the SAN cache because they don’t tend to use the same data twice. Data warehouses get more value because of pre-fetching.

Implies San cache is more about pre-cacheing than re-use

Force flushing effectively disables the cache until it’s finished. Don’t want to do it Low-water mark should be around 20% - you don’t want to be flushing immediately High-water mark 90% - because force flushes are bad

Power failure - most arrays have internal batteries.

Planned outage - MUST shut down sql server before the cache flushes.

Disk alignment matters

Big Data Visualization for Business Intelligence Professionals - @JenStirrup

Powerview - anti-spiderman. great power, but no responsibility for the data

Powermap -3d excel plugin. Formerly known as geo flow

Hortonworks sandbox- free download with hadoop stuff

Database Design - @DataAudrey

Availabilty Groups - Joe Dantoni

ex-Entergy chap!

Valid DR plan - we can do without system X, but not system Y.

Dilbert Our Disaster Recovery Plan Goes Something Like This <— google this

HA DR

Clustering works well from windows/sql 2008 on, but it’s not a DR solution.

Most cloud providers don’t charge for inbound data.

RPO - recovery point objective. How much data can we afford to lose? RTO - recovery time objective. How long can we do without a system?

Everything should be written down, and executive signed off.

Free Azure capacity with MSDN ? are we DR-ing Reporting Services?

Software as a service - gmail, salesforce.com

Infrastructure as a service - Azure, Amazon

platform as a service - Azure SQL, Amazon Relational Database Service

Sql azure now knwon as Windows Azure Sql Database (WASD).

Security in the Cloud

• are your security standards as good as Microsoft or Amazons

MS compliant with SOX and European regulations

Always On Availability Groups

• doesn’t need shared storage
• can go across IP subnets
• can therfore be a DR solution

Is based on log shipping(?)

Availability Groups is standard only, but there are ways of doing log-shipping. No failover capacity in standard.

Dont quite get why you nedd wsfc

Scalability - Glenn Berry

Common issues:

• overuse of query generators
• ad-hoc sql from the application
• lots of xml columns
• user-defined functions
• too much business logic in the database
• overuse of DML triggers
• overuse of CLR assemblies

Glenn has sql server diagnostic information queries <—-worth downloading

Intel tick-tock release strategy

Servers are like pets - scale up Servers are like cattle - commodity