Wednesday, Oct 28th
Started the off the day wandering into a breakfast that had a demo of EMC's XtreamIO for SQL Server. It was a really cool technology... that I'll never get to play with. All SSD and used diffs when copying databases to different environments to save space. It had some really cool compression technology built in as well.
The Keynote
The Keynote was the real winner that morning. I was able to get a pretty sweet front row spot, as seen in the panorama below.
They started the show with a video predicting that soon we will be generating 40ZB of data a year, or some stat like that. Sounds great for job security, but I'd be interested to see where that number came from. The Summit had 5,500 registrations from 58 countries and 2000 companies!
The Keynote speaker showed a video about ImagineCare which pulled data from healthcare devices (heart rate monitors, glucose monitors, ect. great example of IoT) into the cloud and ran some ML algorithms to try and predict if someone needed to see a doctor or if they were likely to visit an ER. Then they showcased a company called DocuSign, which has doubled it's data footprint every year. Glad to see that they can keep up.
The rest of the time they demoed SQL Server 2016 and it looked great! Although just about anything would on an HP SuperDome machine (240 core (480HT) and 12 TB RAM). A lot of cool things are built into 2016, Mobile BI, integration with R, In-Memory OLTP updates, Polybase, JSON support, Real Time Operational Analytics, Always Encrypted. They demoed most of these and it was very cool.
Eating the Elephant
This was an intro session to Hadoop for DBA's I was really hoping for a demo here, but here are my takeaways.
SQL Server releases every 2-4 years
Hadoop "official" release about every 6 months
5V's of "Big Data" (Big Data is a poorly chosen moniker btw, we had big data problems when files were to big for floppy disks (you know the save icon))
Volume
Velocity
Variety
Variability
Value
Data Lake is like the junk drawer of data.
There's something called a Lambda Architecture. Not real sure what this is but it sounds really cool.
Word of the day is Polyschematic, sounds better than unstructured. Because everything got some structure to it.
You can use the HIVE ODBC driver with SSIS to import/export data.
Paul Randal's DBA Mythbusters
This one was really cool. Mostly because it was Paul Randal, but he had around 25 or so myths he busted, no demos but was able to explain what happens under the hood. He's got a bunch of blog posts about this http://www.sqlskills.com/blogs/paul/myths-and-misconceptions-60-page-pdf-of-blog-posts/.
Database Design Throwdown
This was a pretty fun session. The two speakers would take a side of a topic (even if it was ridiculous) and try to get the audience to share their views on the issue.
Hadoop is beneficial when we have large data sets (Terabytes in size), "Unstructured data", data stored for Auditing and Archive, Log and Sensor Data, The Internet of Things (IoT), Data Mining, Machine Learning, Pattern Recognition
Stored data as ORC format, a column store format for Hive
Used Sqoop to stage data into Hadoop
and HiveQL to transform the data into Dimensions and Facts.
The main idea here was dubbed "Fog Computing" or edge computing, and was primarily concerned with extracting data from these sensors and pushing them to the cloud. The work would be done by an aggregator, that would take in a stream of data from a device and based on the results of queries on that stream of data would send it up to the cloud infrastructure. Once in the cloud the data could be used in applications, analytic solutions, ect.
Hive & Hadoop High Performance Data Warehousing
This was a really cool session. Mostly talked about how Hadoop can be a way to supplement the Data Warehouse and how Hive is a great way to get started since we already know SQL.Hadoop is beneficial when we have large data sets (Terabytes in size), "Unstructured data", data stored for Auditing and Archive, Log and Sensor Data, The Internet of Things (IoT), Data Mining, Machine Learning, Pattern Recognition
Stored data as ORC format, a column store format for Hive
Used Sqoop to stage data into Hadoop
and HiveQL to transform the data into Dimensions and Facts.
Thrusday, Oct 29th
Morning Keynote
I didn't get the awesome seat like I did the day before, in fact I almost over slept the keynote forgot to set an alarm. (BTW if you create an alarm in Android, it's a one time alarm by default). This keynote lacked the demos that we saw from the day before, but was still very interesting. Some people from Microsoft came in and talked about the Internet of Things and some of the challenges it creates. They had some crazy stats like there are 12.5 billion devices connected and we should see that double to 25 billion by 2020.The main idea here was dubbed "Fog Computing" or edge computing, and was primarily concerned with extracting data from these sensors and pushing them to the cloud. The work would be done by an aggregator, that would take in a stream of data from a device and based on the results of queries on that stream of data would send it up to the cloud infrastructure. Once in the cloud the data could be used in applications, analytic solutions, ect.
Analyzing Performance & Scalability for OLTP/DW Solutions
Testing was all based on the HP Integrity Superdome X-platform
8 blades w/ 30 cores and 1 TB of Ram
3Par 7440c SAN
224 SSD - 480GB each... you can do the math
8 500GB databases as test databases, spread across the storage array in RAID 1+0
Reference KB article 2964518 for recommendations for scaling up SQL Server 2014 (Here)
May want to consider for our environment.
Test included 22 queries, running individually.
May be good to devise a benchmark for our system.
Raising Code Quality w/ Automated Testing
The cost of fixing code after it has been released into production is > 150x the cost of doing it during development.tSQLt was the framework used for testing
Outline for Tests
Assemble - create the test cases and load the appropriate tables
Act - execute the code we want to test.
Assert - check that something is true from the test, if not the test has failed.
Shouldn't need 100K+ rows of data
Just enough to cover specific scenarios (test cases)
Create a separate schema or database and have an sp that loads the data
script out the data, keep the script in source control
write a test for every new piece of code/bug
Wait, Latches, and Locks
Use Extended Events, system_health is a good oneAsync_Network_IO is based on an application timeout. Very rare that it's the network
SOS_Scheduler_Yield
SQL is a cooperative system, so tasks will give up the processor to other tasks after a quantum of time has expired. quantum is 4ms.
The SOS_Scheduler_Yield wait happens when a task doesn't "naturally" wait.
High count/High wait could be CPU bound
THREADPOOL
Happens when a login can't get a worker thread from the threadpool, too many "people" on the machine. You'll only see it in stats and tasks, may need to use the DAC to see it live.
IO_COMPLETION
Happens when you spill into TempDB for sorting.
Memory waits
Resource_Semaphore - limited memory
Resource_Semaphore_query_compile
CMEMTHREAD - Trace Flag 8084 may help
Preemptive waits
Non-Yielding scheduled, linked queries calling external code
will leave task in running status
CXPacket
Used to synchronize parallel query workers (just means we have parallel queries)
Tune queries as needed
Will be a thread w/o CXPacket, could be the real problem
Understanding ColumnStore Indexing
Architecture
Split data into Row Groups 1-1045678 rows
A deleted bitmap is a structure storing information about rows that have been deleted in a row group
Updates are Delete + Insert
Row groups w/Status: Open, Closed, Hidden, Tombstone
Hidden is seen during bulk load
Tombstone is a compressed Delta-Store (you can ignore it)
Segment elimination
Only columns needed are used
min and max are used as heuristics to determine which row groups are needed
only works for certain types
Predicate Pushdown
where clause is passed into the storage engine so fewer rows are read off disk.
Phrase of the Day: "blazingly slow"
When rebuilding the cci use MAXDOP = 1, after building a clustered index (keyed on the most frequently used predicate), parallel will not help w/ segment alignment, unless the table is partitioned.
Need to use partitioning w/ columnstore
He used the resource governor to starve the rebuild of memory to create smaller row groups. Having many row groups can help w/ parallel queries, and is less data in a single group.
Extended Events for Memory Monitoring
Clustered_columnstore_index_rebuild
column_store_index_low memory
column_store_index_throttle
column_store_index_build_process_segment
Row group trimming
Alter index reorganize
Memory Pressure
Dictionary Pressure
Dictionaries
Global and Local
Global is for all row groups
Local is for only one row group
Dictionary pressure can/will occur with strings in the row group
Avoid strings in the fact tables
Friday, Oct 30th
Spark SQL
Performance - In Memory (Spark is an in memory technology that can run on YARN)
Spark is the 2014 Sort Benchmark winner
100TB in 23 mins 206 node system
Azure Data Lake has an offering for Spark Platform
Zeppelin - Spark SQL interpreter
Spark can work w/ Tableau
Fast and general cluster computing system, interoperable w/ Hadoop
Improves efficiency through In-Memory computing primitives, general computation graphs
Improves usability through API's in R, Scala, Python, and Java, unified interface to read/write
Resilient Distributed Datasets - collection of objects that can be stored in memory or disk across a cluster, parallel functionality
Data frames are a collection of rows and columns
Demo had crime data w/ 5.5 million rows, flight data with 744 million rows, and a larger set of flight data w/ 1.5TB 11 billion rows. All queries ran in ~1 sec. 32 node 8 core 55GB per node.
Date Dimension
Use an INT for the DateKey, much faster with joins.
Very descriptive columns
First day of month, last day of month, last day of last month, ect.
use extended properties to set descriptions
Separate Time dimension
uses the time data type
AM/PM description and flags
just one day of time
PK is an INT based on HHMMSS like the datekey
consider including an offset from UTC
Data Loading Performance
Loading w/ loopsfirst 18 secs
second 1 sec - this one was done inside a transaction.
Delayed_Durability database setting = FORCED
writes only to log buffer
async write to disk for commit
Narrow Index key
Static
Ever-Increasing - prevents page splits (creates latching problems when trying to load into one page)
SQLQueryStress
Could use In-Memory OLTP
Non-Sequential Key to avoid the latching problem but causes fragmentation (rebuild the index after loading)
Hash Partitioning
minimal logging
Only allocations are logged (simple or bulk-logged only)
Bulk Load
Stream of row- continuous, work over tds
saves a lot of network activity
BCP, BULK INSERT, SSIS
INSERT... SELECT
BULK API
Methods
Load to HEAP, build index later
Use Tablock when doing this
Generally the fastest way to load data, but building the index takes time
use a bigger network packet size 32KB
Load into a clustered table
Do NOT use Tablock in this scenario
Use TF 610
Has to sort the data w/o TF610
pass order hint to bcp
BatchSize
each batch is a separate transaction
prevents tempdb spills when loading index table
prevents lock escalation
allows reading from table
Loading into a Partitioned table
Switch out, bulk load, switch in
Large Updates and Deletes
Delete (Partitioned)
- bulk load needed rows into outer table
- switch out current partition
- switch in outer table
Update (Partitioned)
-BulkLoad update rows into outer table
- switch out all rows to outer table
- merge into new table
- switch in new table
CPU Utilization
Generally one loading file per core
Watch out for SOS_SCHEDULER_YIELD
PFS Contention
PFS - Page Free Space (bitmap tells where free space is in db)
all processes have to go there, just add more files.
Jumbo Frames - Bigger TCP/IP packets
Larger Network Packets
Disk Layout - Pre Size data and log files, enable IFI, put files in different luns
Modern Hardware and RAID (Generally RAID 1+0 or RAID 1 for logs and RAID 5 for data if RAID 1+0 isn't available)
Make sure the interface to the SAN is fast enough.
Compression
Types of Compression
Row - Reduces metadata overhead per row
PAGE
COLUMNSTORE
Row
Compresses fixed data types that contain variable length data
PAGE compression
Row compress the data first
Prefix compression, compresses column based on prefix of values in column
Dictionary compression, compresses similar prefix compressed values
What to compress
find w/ sys.dm_db_index_operational_stats
sp_estimated_data_compression_savings
deduplication is what PAGE Compression does.
CPU will have to decompress the data. There is a trade off here, storage for CPU
Data Integration w/ MDS
Master Data Management
Methodology for managing Master Data for the org
Manage the data across many different systems
Architectures
Registries
Consolidation
Coexistance
Model is a container for the information
Entity is a set of records within a model (like a table in a database)
Members are records of an entity
MDS Staging
stage tables are created for each entity
import types define operations for a staging table
processing done w/ batch tags and store procs
SSIS used to stage data.
