Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for the ‘Common’ Category

Auto DB Deployment basic – SQL Angeles

Posted by Simon Cho on 06/21/2017

Please download Presentation and Demo script.

 

Auto DB deployment basic

(Free DBA work from DB deployment, just put the click button to DevOps.)

SQL server Deployment /배포/패치/점검 자동화 기초

부제 – DBA 잠좀 자게 해주세요. F5 클릭은 컴터가 알아서

 

아직도  DB 서버 배포를 손으로 하나요?

SQL package 를 이용해서 수십대의 DB에 자동으로 빠르게 배포하는방법에 대해서 살펴보겠습니다.

 

날짜

06/21/2017 03:00~05:00 (UTC)

06/21/2017 00:00~02:00 (EDT)

06/21/2017 22:00~24:00 (CDT)

06/20/2017 20:00 ~ 22:00 (PDT)

06/21/2017 12:00 ~ 14:00 (KOR)

 

장소 : 222 N Sepuveda Blvd, El Segundo, CA, 90245

온라인 링크 :   https://meet.lync.com/sqlpass365-sqlpass/sqlangeles/MMGM08Y1 (이어폰 준비)

페에스북 라이브 방송 : https://www.facebook.com/sqlmvp (스터디 시간에 맞춰서 방송합니다.)

카카오톡 오픈 채팅 : SQL Angeles 로 검색해서 채팅 참여 가능 합니다.

주의 사항 :  스피커로 청취시 반드시 마이크는 음소거로 설정.

 

Simon Cho

Database Engineer\DevOps Engineer

VISA DBA

SQL Saturday Speaker

SQL Angeles Co-founder

 

Email : simon@simonsql.com

Blog : https://simonsql.com/

LinkedIn : https://www.linkedin.com/in/simonsql/

 

[주차 안내]

길건너 해빗 버거 주차 후 도보로 이동 (약 3분 거리)

 

[Sponsored]

Posted in Common | Tagged: , | Leave a Comment »

SQLSaturday in Los Angeles 6/10

Posted by Simon Cho on 05/31/2017

We are quickly approaching 6/10.
http://www.sqlsaturday.com/640/eventhome.aspx

Top 5 reasons why you have to attend SQL Saturday in LA event

Excellent panel of 40+ Speakers:

2 x Past PASS Presidents + 1 PASS Regional Mentor

2 x Microsoft Certified Masters (MCM)

5 x Microsoft Certified Trainers (MCT)

6 x PASS Speakers

7 x Microsoft Most Valuable Professionals (MVP)

9 x Microsoft Certified Solutions Experts (MCSE)

6 x Microsoft Certified Solutions Associate (MCSA)
Free education – Please see schedule for more details

Free food – we will have coffee, orange juice and pastries for breakfast; pizza, beverages and fresh fruit for lunch

Two beautiful venues with enough room for 300 attendees

A chance to win great prizes

 

P.S. One more reason to attend a SQL Saturday in LA – attend and get a FREE e-book by O’Reilly Media!

Posted in Common | Tagged: | Leave a Comment »

SQL Saturday #613 – Redmond Presentation

Posted by Simon Cho on 04/15/2017

Today is the first day I visit the Microsoft Headquarters in Redmond.

Thank you for coming in SQL Saturday in Redmond.

 

Here are my presentation file and the demo script.

Presentation file

Demo

Posted in Common | Tagged: | Leave a Comment »

SQL Saturday #611 – Orange County Presentation

Posted by Simon Cho on 04/03/2017

Finally, I’ve completed my presentations at SQL Saturday – Orange County!

There were only 3 audiences when I did the 2nd presentation at Orange county 3 years ago.

Today, I got full audiences.

I really appreciated who came to my session and SQL Saturday.

It really encourages me and I get lots of comments from people.

 

Here is the presentation file and Demo file.

 

  1. Build ETL efficiently (10x) with Minimal Logging
    1. Presentation file is here.
  2. Myths and Truths about SQL Server Transaction
    1. Presentation file is here.
    2. Demo file is here.

 

Thank you for attending.

 

http://www.sqlsaturday.com/611/Sessions/Schedule.aspx

 

 

 

 

 

Posted in Common | Tagged: , | Leave a Comment »

SSMS 2016 Release tracking

Posted by Simon Cho on 03/07/2017

https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-changelog-ssms

Posted in Common | Tagged: | Leave a Comment »

Start SQL Server in Single-User Mode

Posted by Simon Cho on 03/06/2017

It’s really helpful in a very rare case.

The application name is case sensitive.

 

net start mssqlserver /m”Microsoft SQL Server Management Studio – Query”

net start mssqlserver /m”sqlcmd”

 

https://msdn.microsoft.com/en-us/library/ms188236.aspx

Posted in Common | Tagged: | Leave a Comment »

sys.sysobjvalues

Posted by Simon Cho on 02/23/2017

 

https://jongurgul.com/blog/tag/sysobjvalues/

Posted in Common | Tagged: , | Leave a Comment »

SQL Angeles Meeting 2/14 – Los Angeles

Posted by Simon Cho on 02/15/2017

SQL Angeles,

We had a meeting at 2/14, Los Angeles.

Here are the presentation file and Demo script.

Topic : Transaction Management and the Transaction Log

DownLoad

SQLPassNew February : sqlpass_news_chapter-deck-february-2017-2

 

SQL Angeles is the Local Chapter based on Los Angeles area.

Our community is for Korean SQL people.

So, we do speak in Korean during the presentation.

This time is the first try with Skype Business Online.

 

We had 5 people from Local and 9 people joined in Skype Business and shared the session together.

Thank you very much for participated in.

 

Here is the detail information how to register and join us.

http://sqlmvp.kr/220936150336

 

 

Posted in Common | Tagged: , | Leave a Comment »

Delayed Durability

Posted by Simon Cho on 02/14/2017

  • ALTER DATABASE SET DELAYED_DURABILITY = {option}
  • Disabled –Normal behavior durability guaranteed.(Default)
  • Allowed –Allowed at the DB Level, Transaction has to specify durability options, default is a durable transaction.
  • COMMIT TRAN……. WITH (DELAYED_DURABILITY=ON)
  • FORCED –Changes default durability for the DB to “delayed”. Can be useful for Applications bottlenecked on Log IO, that can tolerate some Data loss on a failure.

 

https://msdn.microsoft.com/en-us/library/dn449490(v=sql.120).aspx

 

http://info.tricoresolutions.com/blog/delayed-durability-diminishes-i/o-throughput-to-improve-performance-in-the-sql-query

Posted in Common | Tagged: | Leave a Comment »

parameter sniffing recompile options

Posted by Simon Cho on 02/06/2017

https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

Posted in Common | Tagged: , | Leave a Comment »

SQLCMD mode in SSMS

Posted by Simon Cho on 02/01/2017

SQLCMD mode in SSMS

Here are some commands very useful. Things help to deploy complicated queries.

 

:CONNECT localhost
:setvar DBA “DBA”

:on error exit
GO

USE [$(DBA)]
go
SELECT DB_NAME()

print ‘abc.sql – Simon’

:r c:\temp\abc.sql
GO
print ‘abcdef.sql – Simon’
:r c:\temp\abcdef.sql
GO

Posted in Common | Tagged: | Leave a Comment »

Source Control in SQL Server Management Studio (SSMS)

Posted by Simon Cho on 01/31/2017

enable the Visual studio ssms team explorer : C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\ssms.pkgundef

 

https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/21/source-control-in-sql-server-management-studio-ssms/

Posted in Common | Tagged: | Leave a Comment »

SQL Server Column store row group investigation

Posted by Simon Cho on 01/10/2017

https://blogs.msdn.microsoft.com/sqlcat/2015/08/17/sql-2016-columnstore-row-group-merge-policy-and-index-maintenance-improvements/

https://msdn.microsoft.com/en-us/library/dn935008.aspx

 

Posted in Common | Tagged: | Leave a Comment »

SQLAngeles presentation – 12/6/2016

Posted by Simon Cho on 12/08/2016

안녕하세요. Simon Cho입니다.

발표자료 Download : link

Demo script Download : link

SQL Angeles 커뮤니티는 SQL PASS의 공식 회원이며, LA Chapter 그룹으로 PASS 커뮤니티중 유일하게 한국어로 진행되는 모임입니다.

 

2016-12-08_17-32-58

SQL Angeles PASS 공식 홈페이지 : http://SQLAngeles.com / http://sqlangeles.sqlpass.org/

 

SQL Angeles 커뮤니티는 정기적으로 화요일 8PM ~ 10PM (2시간) 스터디를 진행하며(장소 및 시간은 공식 홈페이지를 통해 공지 합니다.) SQL Server를 함께 공부하고 다양한 IT 트렌드를 공유하는 기술 및 네트워크를 공유하는 모임 입니다. SQL Angeles 스터디에 참여하고 싶은 분들은 카카오톡(ID : SQLMVP), 페이스북(https://www.facebook.com/sqlmvp) 메신저, email(jevida@naver.com) 등으로 연락 주시기 바랍니다. 스터디 장소의 출입이 자유롭지 못한 관계로 반드시 사전에 협의가 되어야 합니다.

 

스터디는 회원제로 운영되며 월회비($20)가 있습니다. 불성실 회원의 경우 회칙에 따라 참여 또는 기타 활동이 제한될 수 있습니다.

 

오늘의 주제는 [Build ETL efficiently (10x) with Minimal Logging]으로 제가(Simon Cho)  발표 하였습니다. 오늘 스터디는 총 7분이 참여주셨습니다.

 

kakaotalk_20161208_134934740 kakaotalk_20161208_134935919 kakaotalk_20161208_134936882

Posted in Common | Tagged: , | Leave a Comment »

AG Listener setting

Posted by Simon Cho on 12/02/2016

https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/

http://windowsitpro.com/sql-server-2012/use-sql-server-alwayson-listener

https://msdn.microsoft.com/en-us/library/hh213417.aspx

 

 

Read-only routing refers to the ability of SQL Server to route incoming connections to an availability group listener to a secondary replica that is configured to allow read-only workloads. An incoming connection referencing an availability group listener name can automatically be routed to a read-only replica if the following are true:

  • At least one secondary replica is set to read-only access, and each read-only secondary replica and the primary replica are configured to support read-only routing. For more information, see To Configure Availability Replicas for Read-Only Routing, later in this section.
  • The connection string references an availability group listener, and the application intent of the incoming connection is set to read-only (for example, by using the Application Intent=ReadOnly keyword in the ODBC or OLEDB connection strings or connection attributes or properties). For more information, see Read-Only Application Intent and Read-Only Routing, later in this section.

Posted in Common | Tagged: , , | Leave a Comment »

Memory troubleshooting

Posted by Simon Cho on 11/30/2016

 

 

https://blogs.msdn.microsoft.com/sqlmeditation/2013/01/01/memory-meditation-the-mysterious-sql-server-memory-consumer-with-many-names/

 

https://sqlserverperformance.wordpress.com/2010/10/08/sql-server-memory-related-queries/

 

https://technet.microsoft.com/en-us/library/ms175037.aspx

 

https://msdn.microsoft.com/en-us/library/cc293624.aspx

 

https://blogs.msdn.microsoft.com/sqlqueryprocessing/2010/02/16/understanding-sql-server-memory-grant/

What is RESOURCE_SEMAPHORE_QUERY_COMPILE?

Troubleshooting SQL Server Memory

Fun with Locked Pages, AWE, Task Manager, and the Working Set…

Posted in Common | Tagged: | Leave a Comment »

SQL server 2016 -T1117 and -T1118

Posted by Simon Cho on 11/28/2016

SQL server 기본 세팅이던 T1117 과 T1118이 SQL 2016에서 변경 되었습니다.

기본적으로 SQL server 2016 Install 시에 둘다 enable이 되는데요.

이를 확인하기 위해서는 다음 script를 실행하시면 됩니다.

Is_Mixed_Page_Allocation_on : 0 =  T1118(Enable)

Is_autogrow_all_files : 1 =  T1117(Enable)

tempdb_setting

 

 

 

SELECT name, is_mixed_page_allocation_on
FROM sys.databases
WHERE name=’Tempdb’

SELECT is_autogrow_all_files
FROM tempdb.sys.filegroups

SQL 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases

Posted in Common | Tagged: , , , | Leave a Comment »

SOS_SCHEDULER_YIELD due to SpinLock issue.

Posted by Simon Cho on 11/18/2016

First of all we can check SQL server version since there is patch regarding bucket count.

https://support.microsoft.com/en-us/kb/3026082

https://support.microsoft.com/en-us/kb/3026083

https://support.microsoft.com/en-us/kb/3175883

 

http://www.sqlskills.com/blogs/paul/identifying-queries-with-sos_scheduler_yield-waits/

http://www.sqlskills.com/blogs/paul/advanced-performance-troubleshooting-waits-latches-spinlocks/

http://www.sqlskills.com/blogs/paul/new-whitepapers-on-latches-and-spinlocks-published/

https://blogs.msdn.microsoft.com/repltalk/2011/02/08/what-does-high-wait-in-sos_scheduler_yield-mean/

 

 

And here is really good blog about this issue.

Spinlocks, When To Worry About Them and Solutions To Common Problems

 

Posted in Common | Tagged: , | Leave a Comment »

[LA DB 스터디] 2016-11-15 LA 한인 SQL Angeles 스터디 모임

Posted by Simon Cho on 11/16/2016

안녕하세요. Simon 입니다.

어제 발표 했던 Index 자료 공유 합니다.

멀리서도 참여 해 주셔서 많은 힘이 되었네요.

저희 SQL Angeles 잘 함 진행해 보아요.

강성욱씨 post : http://sqlmvp.kr/220863249821

Script and Presentation download link.

 

kakaotalk_20161116_003650730

 

kakaotalk_20161116_003648391

Posted in Common | Tagged: , | Leave a Comment »

Global ErrorLog SP example

Posted by Simon Cho on 11/07/2016

CREATE PROCEDURE [dbo].[P_AddErrorLog]
@IsRaiseErrorOn BIT = 1
, @IsRaiseErrorWithLogOn BIT = 0 — Ignored where @IsRaiseErrorOn = 0
, @IsRollBackTranOn BIT = 1 — Ignored where XACT_STATE() = -1. It will be Rollback anyway.(Recommanded always turn on “@IsRollBackTranOn=1” since error logging will be rollback as well in parent SP.
, @CustomMessage NVARCHAR(4000) = NULL — Anyother message want to store such as parameter and e.t.c..
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @intReturnValue int;

DECLARE
@nvcErrorMessage nvarchar(4000),
@intErrorNumber int,
@intErrorSeverity int,
@intErrorState int,
@intErrorLine int,
@nvcErrorProcedure nvarchar(128);

/**_# Log the error.*/
/**_## Assign variables to error-handling functions that capture information for RAISERROR.*/
SET @intErrorNumber = ERROR_NUMBER();
SET @intErrorSeverity = ERROR_SEVERITY();
SET @intErrorState = ERROR_STATE();
SET @intErrorLine = ERROR_LINE();
SET @nvcErrorProcedure = ERROR_PROCEDURE();
SET @nvcErrorMessage = ISNULL(ERROR_MESSAGE(),’NULL’);

/**_# If there is no error information to log, return 0.*/
IF @intErrorNumber IS NULL BEGIN
SET @intReturnValue = 0;
GOTO Done;
END
ELSE BEGIN
SET @intReturnValue = @intErrorNumber;
END
/**_# Rollback and return if inside an uncommittable transaction.*/
IF XACT_STATE() = -1 BEGIN
BEGIN TRAN –Trick to remove this error: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
ROLLBACK TRANSACTION;
SET @nvcErrorMessage = ‘[System : uncommittable transaction] ‘+ @nvcErrorMessage;
END

/**_# Rollback and return if @IsRollBackTranOn and Trancount>0.*/
IF @IsRollBackTranOn = 1 AND @@TRANCOUNT > 0
BEGIN
BEGIN TRAN –Trick to remove this error: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
ROLLBACK TRANSACTION;
END

/**_## Log the error that occurred.*/
INSERT dbo.ErrorLogs (LoginName, HostName, ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage, CustomMessage, Registered_DateTime)
VALUES (CAST(ORIGINAL_LOGIN() AS nvarchar(128)), CAST(HOST_NAME() AS nvarchar(128)), @intErrorNumber, @intErrorSeverity, @intErrorState, @nvcErrorProcedure, @intErrorLine, @nvcErrorMessage, @CustomMessage, GETDATE());

/**_# Rethrow the error.*/
IF @IsRaiseErrorOn = 1 BEGIN
SET @nvcErrorMessage = N’Error %d, Level %d, State %d, Procedure %s, Line %d, Message: ‘ + @nvcErrorMessage;

IF @IsRaiseErrorWithLogOn = 1 BEGIN
RAISERROR (@nvcErrorMessage, @intErrorSeverity, 1, @intErrorNumber, @intErrorSeverity, @intErrorState, @nvcErrorProcedure, @intErrorLine) WITH LOG;
END ELSE BEGIN
RAISERROR (@nvcErrorMessage, @intErrorSeverity, 1, @intErrorNumber, @intErrorSeverity, @intErrorState, @nvcErrorProcedure, @intErrorLine);
END
END

Done:
RETURN @intReturnValue;

END

Posted in Common | Leave a Comment »