SQL

• How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT)

  http://blogs.msdn.com/b/psssql/archive/2014/04/29/how-it-works-behavior-of-a-1-trillion-row-index-build-gather-streams-from-sort.aspx

• Memory and IO in Tempdb: SQL 2014 Improvement

  http://www.brentozar.com/archive/2014/04/memory-ssd-tempdb-temp-table-sql-2014/

     

• SQL Server Quickie #13 ? Hash Join Operator

  http://www.sqlpassion.at/archive/2014/04/29/sql-server-quickie-13-hash-join-operator

     

• SQL SERVER ? Working with Change Data Capture ? Part 2

  http://raresql.com/2014/04/29/sql-server-working-with-change-data-capture-part-2/

     

• Five tips to avoid a performance bottleneck or other SQL Server snares

  http://searchsqlserver.techtarget.com/tip/Five-tips-to-avoid-a-performance-bottleneck-or-other-SQL-Server-snares

     

• How to grab multiple parent/child elements from XML Data Source

  http://blogs.msdn.com/b/psssql/archive/2014/04/29/how-to-grab-multiple-parent-child-elements-from-xml-data-source.aspx

     

LINKS

• Last Weeks Top "Reading" Links #81

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/28/last-weeks-top-reading-links-81.aspx

SQL

• Delayed Durability in SQL Server 2014

  http://www.sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014

     

• The default autogrowth settings do NOT come from the Model database.

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/28/the-default-autogrowth-settings-do-not-come-from-the-model-database.aspx

     

• PowerShell-Input-Validation Scripts

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/28/powershell-input-validation-scripts.aspx

     

• The Performance of Traversing a SQL Hierarchy

  https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/

     

• Last week in Azure SQL Database ? Part 1

  http://www.sqlskills.com/blogs/bobb/last-week-azure-sql-database-part-1/

     

LINKS

• Log Buffer #368, A Carnival of the Vanities for DBAs

  http://www.pythian.com/blog/log-buffer-368-a-carnival-of-the-vanities-for-dbas/

     

Other

• OOM relation to vm.swappiness=0 in new kernel

  http://www.mysqlperformanceblog.com/2014/04/28/oom-relation-vm-swappiness0-new-kernel/

     

• Installing and Running HDP 2.1 on Windows

  http://www.sqlskills.com/blogs/bobb/installing-running-hdp-2-1-windows/

     

• ICYMI: Data platform momentum

  http://blogs.technet.com/b/dataplatforminsider/archive/2014/04/28/icymi-data-platform-momentum.aspx

SQL

• Video: SQL Server Performance Tuning with Wait Statistics (CXPACKET)

  http://www.connectsql.com/2014/04/video-sql-server-performance-tuning_27.html

     

• SQLAuthority News ? Microsoft Whitepaper ? Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

  http://blog.sqlauthority.com/2014/04/26/sqlauthority-news-microsoft-whitepaper-optimizing-your-query-plans-with-the-sql-server-2014-cardinality-estimator/

     

• Introduction to Extended Events (video)

  http://www.brentozar.com/archive/2014/04/introduction-extended-events/

     

• Whaddayaknow 'bout #SQLServer Trace Flag 4134?

  http://sql-sasquatch.blogspot.kr/2014/04/whaddayaknow-bout-sqlserver-trace-flag.html

     

• Excessive logging pre-SQL 2012 when changing variable length columns from NULL to NOT NULL

  http://sqlscope.wordpress.com/2014/04/26/excessive-logging-pre-sql-2012-when-changing-variable-length-columns-from-null-to-not-null/

     

• SQL Injection: How it Works and How to Thwart it

  https://www.simple-talk.com/sql/database-administration/sql-injection-how-it-works-and-how-to-thwart-it/

     

• How to Create a Database Master Key

  http://sqlity.net/en/2373/create-database-master-key/

     

• 10 reasons why I HAte you!

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/25/10-reasons-why-i-hate-you.aspx

     

• Database Performance Investigation/Intervention, MTPoD, Last Resorts

  http://sql-sasquatch.blogspot.kr/2014/04/database-performance.html

     

• SQL Server Reporting Services Basics: Building SSRS Reports

  https://www.simple-talk.com/sql/reporting-services/sql-server-reporting-services-basics-building-ssrs-reports/

     

• MDX Cell Properties Supported By Excel

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/25/mdx-cell-properties-supported-by-excel.aspx

     

• What does PCI DSS mean for your SQL Server environment?

  http://www.mssqltips.com/sqlservertip/3216/what-does-pci-dss-mean-for-your-sql-server-environment/

     

LINKS

• Friday Followup For April 25, 2014

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/25/friday-followup-for-april-25-2014.aspx

     

     

Other

• The other side of Big Data. Interview with Michael L. Brodie.

  http://www.odbms.org/blog/2014/04/side-big-data-interview-michael-l-brodie/

     

• Managing farms of MySQL servers with MySQL Fabric

  http://www.mysqlperformanceblog.com/2014/04/25/managing-farms-of-mysql-servers-with-mysql-fabric/

     

• 왜 일이 예상대로 끝나지 않는가?

  http://www.mimul.com/pebble/default/2014/04/25/1398387387079.html

SQL

• Version 9.04.0013 of the RML Utilities for x86 and x64 has been released to the download center

  http://blogs.msdn.com/b/psssql/archive/2014/04/24/version-9-04-0013-of-the-rml-utilities-for-x86-and-x64-has-been-released-to-the-download-center.aspx

     

• "If You Can't Do ____, You're Not a Production DBA"

  http://www.brentozar.com/archive/2014/04/youre-not-a-dba-if-you-cant-do-this-one-trick/

     

• Prevent multiple users from running the same SQL Server stored procedure at the same time

  http://www.mssqltips.com/sqlservertip/3202/prevent-multiple-users-from-running-the-same-sql-server-stored-procedure-at-the-same-time/

     

• Most Recent Cumulative Updates for SQL Server 2008-2014

  http://www.sqlskills.com/blogs/glenn/most-recent-cumulative-updates-for-sql-server-2008-2014/

     

LINKS

• (SFTW) SQL Server Links 25/04/14

  http://www.johnsansom.com/sftw-sql-server-links-250414/

     

     

Other

• 빠르게 훝어 보는 node.js - #10 Socket.IO 2/4 API 요약

  http://bcho.tistory.com/899

     

• Encrypted and incremental MySQL backups with Percona XtraBackup

  http://www.mysqlperformanceblog.com/2014/04/24/encrypted-and-incremental-mysql-backups-with-percona-xtrabackup/

SQL

• SQL Server bitmap operators, bitmasks and bit arrays

  http://dataidol.com/tonyrogerson/2014/04/23/sql-server-bitmap-operators-bitmasks-and-bit-arrays/

• Top Clause and Other Factors in Problematic Execution Plans

  http://sqlblog.com/blogs/joe_chang/archive/2014/04/23/top-clause-and-other-factors-in-problematic-execution-plans.aspx

• The Shared Global Allocation Map (SGAM)

  http://sqlity.net/en/2364/sgam/

     

• 6 Essential Tips for Managing SQL Server

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/23/6-essential-tips-for-managing-sql-server.aspx

     

• Developer's Guide to Understanding DBAs [Video]

  http://www.brentozar.com/archive/2014/04/developers-guide-understanding-dbas-video/

     

• SQL Server Partitioning 101

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/23/sql-server-partitioning-101.aspx

     

• Yes, you can install SQL Server 2014 Books Online locally

  http://sqlblog.com/blogs/aaron_bertrand/archive/2014/04/23/yes-you-can-install-sql-server-2014-books-online-locally.aspx

     

• Introducing the Microsoft Analytics Platform System ? the turnkey appliance for big data analytics

  http://blogs.technet.com/b/dataplatforminsider/archive/2014/04/23/introducing-the-microsoft-analytics-platform-system-the-turnkey-appliance-for-big-data-analytics.aspx

     

• Database Normalization Basics

  https://www.simple-talk.com/sql/t-sql-programming/database-normalization-basics

     

• How Do I Show Memory Usage From A Columnstore Index in SQL Server?

  http://joedantoni.wordpress.com/2014/04/23/how-do-i-show-memory-usage-from-a-columnstore-index-in-sql-server/

     

• RS, SharePoint and Forefront UAG Series ? Intro

  http://blogs.msdn.com/b/psssql/archive/2014/04/23/rs-sharepoint-and-forefront-uag-series-intro.aspx

     

• SSAS Deployment Strategies

  http://www.sql-server-performance.com/2014/ssas/

     

LINKS

• Last Weeks Top "Reading" Links #80

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/22/last-weeks-top-reading-links-80.aspx

     

Other

• Azure Automation

  http://www.scarydba.com/2014/04/23/azure-automation/

     

• 사이드 프로젝트의 효용성 그리고 중요성

  http://www.mimul.com/pebble/default/2014/04/23/1398212595197.html

UPDATE

• SQL Server 2014 Cumulative Update #1 is Available

  http://sqlblog.com/blogs/aaron_bertrand/archive/2014/04/21/sql-server-2014-cumulative-update-1-is-available.aspx

• KB Article: KB #2931693
• 114 fixes listed at time of publication (121 total)
• Build number is 12.0.2342
• Relevant for @@VERSION 12.0.2000 through 12.0.2341

• April 2014 Cumulative Update for SQL Server 2008 R2 SP2        

  http://sqlblog.com/blogs/aaron_bertrand/archive/2014/04/21/april-2014-cumulative-update-for-sql-server-2008-r2-sp2.aspx        

• KB Article: KB #2938478
• 13 fixes listed at time of publication
• Build number is 10.50.4305
• Relevant for @@VERSION 10.50.4000 through 10.50.4304        

     

SQL

• The Repeatable Read Isolation Level- Paul White

  http://www.sqlperformance.com/2014/04/t-sql-queries/the-repeatable-read-isolation-level

• Cardinality Estimation for Correlated Columns

  http://www.sqlpassion.at/archive/2014/04/22/cardinality-estimation-for-correlated-columns

     

• SQL Server XML Questions You Were Too Shy To Ask

  https://www.simple-talk.com/sql/learn-sql-server/sql-server-xml-questions-you-were-too-shy-to-ask/

     

• SQL Server: Win some, learn some... try a whole buncha

  http://sql-sasquatch.blogspot.kr/2014/04/sql-server-win-some-learn-some-try.html

     

• Dangers of giving dbo in MSDB

  http://jongurgul.com/blog/dangers-giving-dbo-msdb

     

• DAX: Always get the Latest date for a slicer

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/22/dax-always-get-the-latest-date-for-a-slicer.aspx

     

• Microsoft Azure SQL Database Security - Firewall Configuration

  http://www.databasejournal.com/features/mssql/microsoft-azure-sql-database-security-firewall-configuration.html

     

Other

• Using mysqldump and the MySQL binary log ? a quick guide on how to backup and restore MySQL databases

  http://scriptingmysql.wordpress.com/2014/04/22/using-mysqldump-and-the-mysql-binary-log-a-quick-guide-on-how-to-backup-and-restore-mysql-databases/

SQL

• SQL Server Diagnostic Information Queries for April 2014

  http://sqlserverperformance.wordpress.com/2014/04/18/sql-server-diagnostic-information-queries-for-april-2014/

• Delayed durability: performance boost for certain workloads

  http://sqlscope.wordpress.com/2014/04/21/delayed-durability-performance-boost-for-certain-workloads/

     

• Disabling An Index Removes Index Data

  http://shaunjstuart.com/archive/2014/04/disabling-an-index-removes-index-data/

     

• New Book: SQL Server 2014 Query Tuning & Optimization

  http://www.benjaminnevarez.com/2014/04/new-book-sql-server-2014-query-tuning-optimization/

     

• Unexpected View Performance

  http://www.sqlservercentral.com/articles/Views/71787/

     

• Auto Fix Orphaned Users

  http://sqlity.net/en/2353/auto-fix-orphaned-users/

     

• The SQL Server Encryption Hierarchy

  http://sqlity.net/en/2357/encryption-hierarchy/

     

• Web Services And POST Requests In Power Query

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/19/web-services-and-post-requests-in-power-query.aspx

     

• Collation: Temporary objects and variables: Demonstration

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/21/collation-temporary-objects-and-variables-demonstration.aspx

     

• Central Management Studio CMS

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/21/central-management-studio-cms.aspx

     

• Automating Database Restores

  http://www.sqlservercentral.com/articles/Backup/108907/

     

LINKS

• Friday Followup For April 18, 2014

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/18/friday-followup-for-april-18-2014.aspx

     

Other

• MySQL Partitioning ? A Quick Look at Partitioning ? Separate Your Data for Faster Searches

  http://scriptingmysql.wordpress.com/2014/04/18/mysql-partitioning-a-quick-look-at-partitioning-separate-your-data-for-faster-searches/

     

• R and the weather in the local news

  http://blog.revolutionanalytics.com/2014/04/r-and-the-weather-in-the-local-news.html

     

• 무료 node.js 개발툴

  http://bcho.tistory.com/895

     

• 빠르게 훝어 보는 node.js - #9 Socket.IO 1/2

  http://bcho.tistory.com/896

안녕하세요.

13차 캠프에 참가하게 된 주디 아줌마입니다.

2014년 4월 1일 SQL2014 릴리즈 되었습니다. 이번 캠프는 신 기능 체험 이므로 아래의 기능 중 궁굼해했던 기능들을 테스트 해보려고 합니다.

• Native Backup Encryption
• sys.dm_exec_query_profiles
• SQL Server 2014 Incremental Statistics
• Delayed Durablilty
• Memory optimization Advisor
• SELECT INTO 병렬처리 개선

접속 방법은 이전 캠프 참여 내용이 중복됨으로 여기 참고하시기 바랍니다. 개인적으로 In-Memory 아키텍쳐에 대해 관심이 많아 인-메모리 기능 위주로 살펴 보려고 했었습니다.

아뿔싸~~ 체험 버전이 Standard Edition이었습니다. 릴리즈되면서 In-Memory 기능은 Ent 기능으로 확정되었죠. 이번 체험에 목표로 했던 여러 기능 체험은 물 건너 가버렸습니다. 다음 캠프 때 다시 도전해봐야겠네요.

가) Memory optimization Advisor

간단하게 데이타베이스 생성 후 일반 테이블을 생성했습니다. 이 기능은 MSDN 디스크 기반 테이블을 인-메모리 OLTP에 마이그레이션하는데 도움을 줄 수 있습니다.

테이블을 선택 후 오른쪽 마우스 버튼으로 Memory Optimization Advisor를 선택합니다.

엔터프라이즈 기능임으로 이후 단계 진행하고 싶었으나, 다음 단계 진행을 위한 버튼이 비활성화되었네요.

나) Native Backup Encryption
 

SQL2012 버전에 들어오면서 백업하면서 암호를 걸 수 있는 기능이 없어졌습니다. 왜 없어졌나 했더니 SQL2014 들어오면서 백업에 대한 보안 강화 기능을 추가하려고 했나 보네요.

  a.마스터 키 생성

 b.인증서 생성

 c.백업
   알고리즘 AES_256을 이용해 백업과 압축을 진행하며, 서버 인증서 Certificate_SQLTAG를 이용하여 보호를 합니다. 백업 시 Warning이 발생하는데 인증서와 개인키 관련 백업이 안 되었다고 알려주네요.

 d. 마스터키 백업

  e.인증서 백업

f. 백업 파일 확인

위와 같은 기능을 UI 상에서도 가능합니다.

a. Encrypt Backup에서 백업에 사용할 알고리즘와 인증서를 선택

b. 데이타베이스 메디아 옵션을 선택

c.백업을 합니다.

이상 주디아줌마였습니다.

SQL

• MSDN Whitepaper: More Cowbell?Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

  http://www.sqlserver.co.uk/index.php/msdn-whitepaper-more-cowbell-optimizing-your-query-plans-with-the-sql-server-2014-cardinality-estimator/

• SSMS Auto Recovery

  http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/04/17/ssms-auto-recovery.aspx

     

• XML Schema validation in SQL Server

  http://www.sqlservercentral.com/articles/XML/108041/

     

• SQL Server 2014 Backup to the Cloud

  http://sqlblog.com/blogs/john_paul_cook/archive/2014/04/17/sql-server-2014-backup-to-the-cloud.aspx

     

• Use Extended Events to Get More Information About failed SQL Server Login Attempts

  http://www.mssqltips.com/sqlservertip/3214/use-extended-events-to-get-more-information-about-failed-sql-server-login-attempts/

     

• The Global Allocation Map (GAM)

  http://sqlity.net/en/2351/gam/

     

LINKS

• Log Buffer #367, A Carnival of the Vanities for DBAs

  http://www.pythian.com/blog/log-buffer-367-a-carnival-of-the-vanities-for-dbas/

     

Other

• How to find bugs in MySQL

  http://www.mysqlperformanceblog.com/2014/04/17/how-to-find-bugs-in-mysql/

     

• Real MariaDB 출간

  http://cafe.naver.com/realmysql/614

  http://www.yes24.com/24/goods/12653486?scode=029        -- 예판

• Q&A: Common (but Deadly) MySQL Development Mistakes

  http://java.dzone.com/articles/qa-common-deadly-mysql

병렬 쿼리 테스트할 때 시작 파라메터에 -P값으로 코어 수 조정했었는데, DBCC OPTIMIZER_WHATIF를 사용하면 세션 단위로  시뮬레이팅할 수 있는 DBCC 명령어가 있다.

/*
	

	DBCC OPTIMIZER_WHATIF
	http://devondba.blogspot.kr/2014/03/dbcc-optimizerwhatif.html

	Parallelism in SQL Server Query Tuning - Itzik Ben-Gan
	http://sqlmag.com/sql-server/parallelism-sql-server-query-tuning

Using Optimizer_WhatIF and StatsStream to Simulate a Production Environment
https://www.simple-talk.com/sql/database-administration/using-optimizer_whatif-and-statsstream-to-simulate-a-production-environment/


-- SQL2012에 추가된 항목
	LowCEThresholdFactorBy10
	HighCEThresholdFactorBy10
	CEThresholdFactorCrossover
	DMLChangeThreshold

*/


-- 결과를 클라이언트(SSMS)에 출력하기 위해 플래그 활성화
DBCC TRACEON (3604)

-- Whatif 현재 상태  정보 반환
DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSGS;

/*
Optimizer what-if status (10가지 항목)
---------------------------------------------------------
property_number current_value default_value property_name
---------------------------------------------------------
         1                  0             0 CPUs
         2                  0             0 MemoryMBs
         3                  0             0 Bits
*/

-- Reset 명령어
-- DBCC OPTIMIZER_WHATIF(ResetAll) WITH NO_INFOMSGS;

-- 10가지 항목으로 시뮬레이팅 할 수 있는데, 그중 CPU와 메모리 값을 변경해 보자.
-- 설정 : set the CPUs to 6 and the RAM to 32GB:
DBCC OPTIMIZER_WHATIF(CPUs, 6) WITH NO_INFOMSGS;
DBCC OPTIMIZER_WHATIF(MemoryMBs, 324768) WITH NO_INFOMSGS;


-- 변경된 항목 확인
DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSGS;
/*
---------------------------------------------------------
Optimizer what-if status
---------------------------------------------------------
property_number current_value default_value property_name
---------------------------------------------------------
         1                  6             0 CPUs
         2             324768             0 MemoryMBs
*/

-------------------------------------------------------------------------------------
-- cpu, memory에 따른 실행 계획 비교
-------------------------------------------------------------------------------------

-- CASE 1 )코어 6개, 메모리 32G의 경우 실행계획 보기
SELECT *
FROM Sales.SalesOrderDetail s
       INNER JOIN Production.Product p
       ON s.ProductID = p.ProductID
ORDER BY p.Style
OPTION (RECOMPILE);  --REALLY IMPORTANT OTHERWISE IT MAY USE A CACHED PLAN



-- CASE 2 ) 코어 1개
--USE DBCC OPTIMIZER_WHATIF Change to 1 core
DBCC OPTIMIZER_WHATIF(ResetAll) WITH NO_INFOMSGS;
DBCC OPTIMIZER_WHATIF(CPUs, 1) WITH NO_INFOMSGS;

--confirm status again
DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSGS;


SELECT *
FROM Sales.SalesOrderDetail s
       INNER JOIN Production.Product p
       ON s.ProductID = p.ProductID
ORDER BY p.Style
OPTION (RECOMPILE);  --REALLY IMPORTANT OTHERWISE IT MAY USE A CACHED PLAN