Fabric, Power BI, Power Platform, Data Platform: June 2012

Wednesday, June 27, 2012

MSTechEd Europe 2012 Day 1: Optimizing your BI Semantic Model for performance and scale

I joined my first session on tuesday by Dave Wickert. Here's my short version of the session.

VertiPaq is all about Performance, Performance, Performance!
The VertiPaq engine chooses query performance over processor performance.
What the VertiPaq engine does:

Encoding (per column): creates a dictionary with value or hash encoding (self-deciding)
VertiPaq storage

dictionary
column segment
hierarchy
relationships
partitions

Processing

Process Full
Process Data
Process Recalc (includes calculated columns)

Advanced Processing

Process Defrag: updates the dictionary
Parallel processing
Error handling

Server Memory Map

Databases
Formula engine (DAX, MDX)
VertiPaq caches
Storage engine

Defaults that can be adjusted (be careful):
VertipaqMemoryLimit - 60%
TotalMemoryLimit - 80%
VertipaqPagingMode

MSTechEd Europe Keynote Day 2

We started day 2 with another keynote session from Antoine LeBlond with a focus on Windows 8.Antoine showed some cool demos of what Windows 8 is capable of, including a demo on a tablet and laptop. Features included were Swipe in, semantic (smart) zoom, quick cycling through apps (the old alt+tab).
A new feature for the laptop is that the touchpad on it also has smart touch capabilities! You can also use semantic zoom and swipe in on the touchpad for example!

Another cool thing was taking your work environment on a 32 GB USB-drive and run it on your desktop at home, where a reboot and login with BitLocker showed a secured and protected environment, locked away from the physical hard drives of the desktop. Then suddenly he unplugged the USB-device, you then have 60 seconds to plug it back in to not loose any data. The video he was playing just continued after a flick of a second.. Nicely done!

Further demoing Windows 8 development with Windows Runtime (WinRT).
Multiple monitor support has much improved. You can set different backgrounds or just stretch it over 2 monitors. The taskbar can also be adjusted, e.g. when you drag and drop an application over to the 2nd monitor the shortcut on the taskbar also moves with it.

More sessions later on today on SQL Server and Spatial Data and of course the famous BI Power Hour, can't wait to have seen that one!

Tuesday, June 26, 2012

MSTechEd Europe 2012 Pre-Con: SQL Server Performance Tuning and Optimization - It all depends...

Yesterday I was at the Pre-conference Day at the MSTechEd Europe. I learned a lot during the session and tried to make some useful notes so I can use them for later reference. Specially the part on DMVs was very useful, I didn't use them before and certainly am checking them in the next weeks!

By Denny Cherry and Thomas LaRock

1. Identifying performance bottlenecks

Tools used:

- Performance Monitor

- Activity Monitor

- DB Tuning Advisor (DTA)

- Data collection

- Profiler

- SQLTrace

- Dynamic Management Views (DMVs)

- Extended Events (xEvents)

Blocking processes / wait events (running, runnable, waiting) aren't shown in Profiler (= SQLTrace + visualization). Those can be shown with the xEvents Wizard under Management in SSMS. Always tune it to your needs, never use a default (See PAL).

Summary:

- Define the problem

- Decide for tool with the best metrics and least (perf.) cost

- Consider the time needed to analyze

- It depends..

2. Index optimization

1. Indexing

- Started at design time

- Data types matter!

- Changes over time

- First line of defense for (quick) perf. Tuning

Do we have the right indexes?

- It depends..

- Follow best practices

- Use DMVs for missing indexes

- Focus on Logical IO (Inside SQL Server 2005: T-SQL Querying by Itzik Ben-Gan)

SQL Diagramming > table with highest selectivity of data

Index seek is better than Index scan.

If an index has only writes and no reads > delete the index

2. Execution Plans

Look for:

- Correlation with wait events

- Examine first operator (Select | Update | ..)

- Warnings (missing index)

- "More costly" operators: LIKE > Spool uses tempdb

- "Fat fingers": thick arrows means lots of rows

- Looking at the #rows in

□ An index > update statistics

□ Hash match > input vs output rows (do nothing)

3. Filtered Indexes

An index with a filter (where clause), but no extended logic (e.g. OR)

4. ColumnStore Indexes

- Based on the xVelocity in-memory analytics engine (a.k.a. Vertipaq)

- CREATE COLUMNSTORE INDEX [COLUMNS]

- Downside: the table is read-only, workarounds:

□ Delete index > load data > create index

□ Use 2 tables, 1 read-only and 1 read/write table

3. Storage optimization

1. Table partitioning

- Reduce maintenance window

- Improve query response time

- Reduce recovery time

Create filegroups, a partition function and a scheme referencing the function.

Business (logic) knowledge is essential before starting partitioning!

When moving data: meta data (page headers) is updated

When deleting data:

- Move partition to another table > then truncate (faster and save)

- Also getting data back that shouldn't be deleted is faster. Let the data stay in the other table for a few days before actually deleting it.

2. AlwaysOn (Enterprise feature)

- No need for shared storage

- Offload backups

- Scaling out read-only activity

4. Troubleshooting

- Are all queries running slow?

- Use xEvents with DMVs

- Is a subset of queries running slow?

- Use Executions plans with DMVs

Useful DMVs:

- Sys.dm_exec_query_stats

- Sys.dm_exec_query_plan

- Sys.dm_exec_cached_plans

STATISTICS IO ON

STATISTICS TIME ON

Using GO 10 after a SQL statement will execute the statement 10 times, useful when inserting data.

Resources mentioned can be found @ http://mrdenny.com/res/teched-2012-precon

Thursday, June 21, 2012

Ready for MSTechEd Europe 2012

I'm getting ready for the TechEd in Amsterdam RAI next week. Registration is completed, the hotel is booked and I've build up my agenda with the most important sessions I certainly do not want to miss.

I've watched several videos on Channel 9 from the MSTechEd North America, those were all very promising for what's to come. Earlier I also wrote a summary of the Day 1 Keynote by Satya Nadella.

I hope I can update my blog next week with new and exciting things from the TechEd!

Monday, June 18, 2012

Free E-Book Gallery for Microsoft Technologies

Just a quick post to share some nice E-books found in a TechNet Wiki article by Monica Rush!
A few days ago I came across this link where you can find existing and new content for ASP.NET, Office, SQL Server, Windows Azure, and other Microsoft technologies in e-book formats.

The gallery can be found here.

For anyone who's interested in an overview of SQL Server 2012, I can also recommend the Books Online for SQL Server 2012 on MSDN.

Enjoy reading!

Wednesday, June 13, 2012

MS SQL DateTime convert examples

As I'm always searching the web with datetime conversions in SQL, I thought I'd post them here finally for reference. The function convert is used here, also found on MSDN.
The beneath functions are for string to datetime conversions.

For datetime to string conversions use varchar instead, e.g.:
SELECT convert(varchar, datetimeField, 105)

-- MSSQL Server string to date conversion - datetime string format sql server

-- MSSQL string to datetime conversion - convert char to date sql server

SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000

-- Without century (yy) string date conversion - convert string to datetime

SELECT convert(datetime, 'Oct 23 12 11:01AM',     0) -- mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000

-- Convert string to datetime sql - convert string to date sql - sql dates format

-- T-SQL convert string to datetime - SQL Server convert string to date 

SELECT convert(datetime, '10/23/2016',          101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23',          102) -- yyyy.mm.dd

SELECT convert(datetime, '23/10/2016',          103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016',          104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016',          105) -- dd-mm-yyyy

-- mon types are nondeterministic conversions, dependent on language setting

SELECT convert(datetime, '23 OCT 2016',         106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016',        107) -- mon dd, yyyy

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '20:10:44',            108) -- hh:mm:ss

-- 1900-01-01 20:10:44.000

-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)

-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016',          110) -- mm-dd-yyyy

SELECT convert(datetime, '2016/10/23',          111) -- yyyy/mm/dd

SELECT convert(datetime, '20161023',            112) -- yyyymmdd

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm

-- 2016-10-23 11:02:07.577

SELECT convert(datetime, '20:10:25:300',             114) -- hh:mm:ss:mmm(24h)

-- 1900-01-01 20:10:25.300

SELECT convert(datetime, '2016-10-23 20:44:11',      120) -- yyyy-mm-dd hh:mm:ss(24h)

-- 2016-10-23 20:44:11.000

SELECT convert(datetime, '2016-10-23 20:44:11.500',  121) -- yyyy-mm-dd hh:mm:ss.mmm

-- 2016-10-23 20:44:11.500

SELECT convert(datetime, '2008-10-23T18:52:47.513',  126) -- yyyy-mm-ddThh:mm:ss.mmm

-- 2008-10-23 18:52:47.513

-- Convert DDMMYYYY format to datetime

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

-- 2016-01-31 00:00:00.000

-- SQL string to datetime conversion without century - some exceptions

SELECT convert(datetime, '10/23/16',          1)                  -- mm/dd/yy

SELECT convert(datetime, '16.10.23',          2)                  -- yy.mm.dd

SELECT convert(datetime, '23/10/16',          3)                  -- dd/mm/yy

SELECT convert(datetime, '23.10.16',          4)                  -- dd.mm.yy

SELECT convert(datetime, '23-10-16',          5)                  -- dd-mm-yy

SELECT convert(datetime, '23 OCT 16',         6)                  -- dd mon yy

SELECT convert(datetime, 'Oct 23, 16',        7)                  -- mon dd, yy

SELECT convert(datetime, '20:10:44',          8)                  -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9)

SELECT convert(datetime, '10-23-16',          10)                 -- mm-dd-yy

SELECT convert(datetime, '16/10/23',          11)                 -- yy/mm/dd

SELECT convert(datetime, '161023',            12)                 -- yymmdd

SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13)        -- dd mon yy hh:mm:ss:mmm

SELECT convert(datetime, '20:10:25:300',        14)           -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11',20)            -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)       -- yyyy-mm-dd hh:mm:ss.mmm

Fabric, Power BI, Power Platform, Data Platform

Pagina's