Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

[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

Advertisements

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 »

SQL Saturday Session #563 – Dallas 9/24/2016

Posted by Simon Cho on 09/24/2016

Topic : Build ETL efficiently (10x) with Minimal Logging

Presentation File Download Link

 

2016-08-27_SQLSat_Oklahomajpg 2016-09-24_22-05-59

 

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

Service Broker may not work after Failover in AG.

Posted by Simon Cho on 09/22/2016

We need to check a couple of setting.

  1. Service broker Enable : By default disabled even restored from Primary
    1. Doesn’t allow enabled broker in AG setting. It’s required remove first.
    2. First failover only.
  2. Trustworthy On : By default off even restored from Primary.
    1. First failover only.
  3. Encryption/Decryption issue
    1. Database master key encrypted by “Service Master key”.
    2. So secondary instance can’t encrypt/decrypt master key since Service master key created at the first installation.
    3. Temporary solution : alter master key with current instance service master key
      1. Once failover, it need to execute.

 

#Service Master key

 

Script

 

— #1 : First failover only. Remove AG required. After run the script we can rejoin it.
SELECT name FROM sys.databases WHERE is_broker_enabled=1

— If you don’t see your DB. need to run below command.
ALTER DATABASE <ServiceBrokerDB> SET ENABLE_BROKER
GO

— #2 : Concorrunt connection may be interuppted. First failover only.
SELECT name FROM sys.databases WHERE is_trustworthy_on=1
— If you don’t see your DB. need to run below command.
ALTER DATABASE <ServiceBrokerDB> SET TRUSTWORTHY ON
GO
— #3 : Need to execute it every failover. More detail please see “Service Master Key” document.
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘x’ — Should be encrypted! See below
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

 

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

Service Master Key issue on AG

Posted by Simon Cho on 09/22/2016

https://blogs.msdn.microsoft.com/mattm/2012/09/19/ssis-with-alwayson/

http://help.k2.com/kb001572

https://technet.microsoft.com/en-us/library/ms182754%28v=sql.110%29.aspx

Temporary solution : Alter master key again with service master key

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘x’ — Should be encrypted! See below
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

 

Here is SQL server 2016 AG setting.

It seems like working with SQL 2014 as well.

SQL Server 2016 AlwaysOn Availability Group Enhancements: Support for Encrypted Databases

 

 

Posted in Common | Tagged: | 1 Comment »

Saturday Night SQL VC 9/10 – Build ETL efficiently (10x) with Minimal Logging

Posted by Simon Cho on 09/12/2016

Build ETL efficiently (10x) with Minimal Logging

Here is the Presentation file. Link

http://saturdaynightsql.sqlpass.org/Home.aspx?EventID=5824

I wish to have 2-3 hour presentation length.

1 hour is short to share all those information.

 

Posted in Common | Leave a Comment »

SQL Saturday – oklahoma city

Posted by Simon Cho on 08/27/2016

I spoke at Oklahoma City 8/27/2016. This is first visit at Oklahoma City.

It was an awesome experience.

You can download my presentation at here.

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

2016-08-27_SQLSat_Oklahomajpg

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

Bulk recovery model – Concerning about Point-in-time recovery.

Posted by Simon Cho on 08/01/2016

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

Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.

However, the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.

 

Solution : We can run Log backup more frequently during a minimal logging operation.

https://technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx

For a database that uses full recovery, switching to the bulk-logged recovery model temporarily for bulk operations improves performance. However, point-in-time recovery is not possible with bulk-logged model. Therefore, if you run transactions under the bulk-logged recovery model that might require a transaction log restore, these transactions could be exposed to data loss. To maximize data recoverability in a disaster-recovery scenario, we recommend that you switch to the bulk-logged recovery model only under the following conditions:

  • Users are currently not allowed in the database.
  • All modifications made during bulk processing are recoverable without depending on taking a log backup; for example, by re-running the bulk processes.

If you satisfy these two conditions, you will not be exposed to any data loss while restoring a transaction log that was backed up under the bulk-logged recovery model.

We recommend that:

  • Before switching to the bulk-logged recovery model, you back up the log.This is important because, under the bulk-logged recovery model, if the database fails, backing up the log for bulk operations requires access to the data.
  • After performing the bulk operations, you immediately switch back to full recovery mode.
  • After switching back from the bulk-logged recovery model to the full recovery model, you back up the log again.

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

7/23/2016 SQL Saturday at Sacramento

Posted by Simon Cho on 07/23/2016

http://www.sqlsaturday.com/540/EventHome.aspx

It was the first session in the moring.

Thank you for joining.

 

Presentation File

 

SQLSaturday_Sancramento

 

 

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

SQL.LA presentation file 7/21.

Posted by Simon Cho on 07/22/2016

Thank you all.

Here are the presentation file and script.

 

http://sql.la/

2016-07-22_12-14-12

Posted in Common | Tagged: | Leave a Comment »

SSIS Catalog Execution with T-SQL

Posted by Simon Cho on 06/10/2016

I create one SP to execute SSIS Catalog using T-SQL.

It sometimes helps to execute SSIS package dynamically.

Full script download link.

 

exec [dbo].[USP_EXEC_SSIS_Catalog]
@folder_name NVARCHAR(128)
, @project_name NVARCHAR(128)
, @package_name NVARCHAR(260)
, @Multi_CustomValue VARCHAR(MAX)= NULL –“<ParamName>|#|<ParamValue>|##|<ParamName>|#|<ParamValue>… Ex. “SyncBack_Period_DD|#|-14|##|BackupFolder|#|X:\Test”
, @reference_id BIGINT = NULL
, @use32bitruntime BIT = 0
, @execution_id BIGINT = NULL OUTPUT

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

I’m going to Speak at South Florida, 6/18 – Update Download link

Posted by Simon Cho on 06/10/2016

Please register it on SQL Saturday page

http://www.sqlsaturday.com/524/eventhome.aspx

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

@sqlsatSoFla #sqlsat524 #sqlsaturday

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

SQL Server 2016 installation

Posted by Simon Cho on 06/03/2016

SQL Server 2016 is released at 6/1.

https://www.microsoft.com/en-us/server-cloud/products/sql-server/

 

SQL Server 2014/2016 Developer edition are free after joining  Visual Studio Dev Essentials.

https://www.microsoft.com/en-us/server-cloud/products/sql-server/

 

Before install Please read release note. They ask VC runtime update before installation.

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


 

Install Patch Requirement (GA)

Issue and customer impact: Microsoft has identified a problem that affects the Microsoft VC++ 2013 Runtime binaries that are installed as a prerequisite by SQL Server 2016. An update is available to fix this problem. If this update to the VC runtime binaries is not installed, SQL Server 2016 may experience stability issues in certain scenarios. Before you install SQL Server 2016, check to see if the server needs the patch described in KB 3138367 – Update for Visual C++ 2013 and Visual C++ Redistributable Package.

KB 3138367 is required if the build version of msvcr120.dll is not 12.0.40649.5 or higher. To check the file build version:

  1. Open Windows Explorer.
  2. Navigate to %SystemRoot%\system32\msvcr120.dll.
  3. Right-click the file and then click Properties.
  4. Click the Details tab.
  5. Verify the file version is 12.0.40649.5 or higher.

If the build version of msvcr120.dll is not 12.0.40649.5 or higher you need to install KB 3138367.

Resolution: To install this required update, download and run the appropriate vcredist_*.exe based on your system language and architecture from KB 3138367.

If you have SQL Server 2016 installed on a computer that needs KB 3138367, do the following steps in order:

  1. Download the appropriate vcredist_*exe.
  2. Stop the SQL Server service for all instances of the database engine.
  3. Install KB 3138367.
  4. Reboot the computer.

 

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

“Myths and Truths about SQL Server Transaction” at Saturday Night SQL, 5/7 6:00 PM PST.

Posted by Simon Cho on 05/02/2016

http://saturdaynightsql.sqlpass.org/

Presentation file download link.

Script file download link.

This is all about SQL error handling and nested transaction issue.

 

 

I’ll do a presentation for “Myths and Truths about SQL Server Transaction” at Saturday Night SQL, 5/7 6:00 PM PST.

This is Virtual Chapter. So, you can join on online.

Posted in Common | Leave a Comment »

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads

Posted by Simon Cho on 04/04/2016

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

 

Must read who is using SQL Server 2012 or SQL Server 2014.

Posted in Common | Leave a Comment »

4/9 Silicon Valley to speak on SQL Saturday.

Posted by Simon Cho on 03/30/2016

Presentation file is here.
20160409_BestPracticeForETL-MinimalLogging

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

I’m going to Silicon Valley to Speak on SQL Saturday.

Topic: “Best Practice for ETL – How to use minimal logging, and when? “

Location : Microsoft Technology Center, 1065 La Avenida, Mountain View, California, 94043, United States

SiliconValley_speak

Posted in Common | Leave a Comment »

checkDB detail

Posted by Simon Cho on 01/25/2016

http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-complete-description-of-all-checkdb-stages/

 

http://www.sqlskills.com/blogs/paul/corruption-bug-that-people-are-hitting-msg-8914-pfs-free-space/

 

Thanks, Paul

Posted in Common | Leave a Comment »

understading status column of sysfiles and sysaltfiles view

Posted by Simon Cho on 01/04/2016

First of all, let’s take a look system view

sp_helptext ‘sys.sysfiles’
sp_helptext ‘master.dbo.sysaltfiles’

<Sys.sysfiles – SQL2014>

CREATE VIEW sys.sysfiles AS
SELECT
fileid = convert(smallint, fileid & 0x7fff),
groupid = convert(smallint, grpid),
size = isnull(FilePropertyById(fileid, ‘size’), size),
maxsize, growth,
status = convert(int,
case filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (status & 8) * 2 — FCB_READONLY_MEDIA
+ (status & 16) * 256 — FCB_READONLY
+ case filestate when 6 then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (status & 256) * 2097152 — FCB_SPARSE_FILE
+ (status & 32) * 32768), — FCB_PERCENT_GROWTH
perf = convert(int, 0),
name = lname,
filename = pname
FROM sys.sysprufiles
WHERE filetype IN (0, 1) — x_eft_SQLData, x_eft_SQLLog (bwkcmpt types)
AND filestate NOT IN (1, 2, 3) — x_efs_Dummy, x_efs_Dropped, x_efs_DroppedReusePending

 

<master.dbo.sysaltfiles – SQL2014>

CREATE VIEW sys.sysaltfiles AS
SELECT fileid = convert(smallint, f.fileid & 0x7fff),
groupid = convert(smallint, f.grpid),
f.size, f.maxsize, f.growth,
status = convert(int,
case f.filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (f.status & 8) * 2 — FCB_READONLY_MEDIA
+ (f. status & 16) * 256 — FCB_READONLY
+ case when f.filestate in (1, 2, 3, 6) then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (f.status & 256) * 2097152 — FCB_SPARSE_FILE
+ (f.status & 32) * 32768), — FCB_PERCENT_GROWTH
perf = convert(int, 0),
dbid = convert(smallint, f.dbid),
name = f.lname, filename = f.pname
FROM master.sys.sysbrickfiles f
WHERE f.filetype IN (0, 1) AND has_access(‘MF’, 1) = 1 — x_eft_SQLData, x_eft_SQLLog (bwkcmpt types)

 

You can find out the source code looks like below for status

status = convert(int,
case filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (status & 8) * 2 — FCB_READONLY_MEDIA
+ (status & 16) * 256 — FCB_READONLY
+ case filestate when 6 then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (status & 256) * 2097152 — FCB_SPARSE_FILE
+ (status & 32) * 32768), — FCB_PERCENT_GROWTH

 

This is bit operation. So, it need to convert back using same operation.

SELECT *
, status
, status & 2 AS [FCB_DSK_DEVICE]
, status & 66 AS [FCB_LOG_DEVICE]
, status & (8 * 2) AS [FCB_READONLY_MEDIA]
, status & (16 * 256) AS [FCB_READONLY]
, status & (268435456) AS [OFFLINE, FCB_OFFLINE]
, status & (256 * 2097152) AS [FCB_SPARSE_FILE]
, status & (32 * 32768) AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

This query equivalent with below query

/*
SELECT CONVERT(VARBINARY(8), 2)
SELECT CONVERT(VARBINARY(8), 66)
SELECT CONVERT(VARBINARY(8), (8 * 2))
SELECT CONVERT(VARBINARY(8), (16 * 256))
SELECT CONVERT(VARBINARY(8), 268435456)
SELECT CONVERT(VARBINARY(8), (256 * 2097152))
SELECT CONVERT(VARBINARY(8), (32 * 32768))
*/
SELECT *
, status
, status & 0x2 AS [FCB_DSK_DEVICE]
, status & 0x42 AS [FCB_LOG_DEVICE]
, status & 0x10 AS [FCB_READONLY_MEDIA]
, status & 0x1000 AS [FCB_READONLY]
, status & 0x10000000 AS [OFFLINE, FCB_OFFLINE]
, status & 0x20000000 AS [FCB_SPARSE_FILE]
, status & 0x100000 AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

BOL saying “0x40 = Log file.” since this is bit operation.

I realized it. It’s due to “FCB_DSK_DEVICE” included. So, 0x40+2 = 0x42.

So, 0x40 is for FCB_LOG_DEVICE

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

 

Final query

SELECT *
, status
, CONVERT(BIT, status & 0x2) AS [FCB_DSK_DEVICE]
, CONVERT(BIT, status & 0x40) AS [FCB_LOG_DEVICE]
, CONVERT(BIT, status & 0x10) AS [FCB_READONLY_MEDIA]
, CONVERT(BIT, status & 0x1000) AS [FCB_READONLY]
, CONVERT(BIT, status & 0x10000000) AS [OFFLINE, FCB_OFFLINE]
, CONVERT(BIT, status & 0x20000000) AS [FCB_SPARSE_FILE]
, CONVERT(BIT, status & 0x100000) AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

 

 

 

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

Between 4096 and 512 sector size issue.

Posted by Simon Cho on 11/16/2015

There are 2 issues in case of primary server has 512 sector size, and secondary server has 4096 sector size

  • You may see below message in the SQL server error log

There have been 170613760 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file

  • If you are using Log Shipping with standby mode on secondary server, standby mode may fail frequently.

Msg 9004, Level 16, State 6, Line 7
An error occurred while processing the log for database ‘MessageExchange_Sanpedro’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

 

Please check 4k and 512 basic concept in below url.

http://blogs.msdn.com/b/saponsqlserver/archive/2014/10/02/message-misaligned-log-ios-which-required-falling-back-to-synchronous-io-in-sql-server-error-log.aspx

http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

http://blogs.msdn.com/b/psssql/archive/2013/05/15/sql-server-storage-spaces-vhdx-and-4k-sector-size.aspx

 

Solution

https://support.microsoft.com/en-us/kb/3009974 : I don’t know how it work internally. It might force the primary to 4K alignment. Please fully test it out and apply it.

Note After you apply this hotfix, you have to turn on the trace flag 1800 to make this hotfix work correctly.

https://support.microsoft.com/en-us/kb/2987585 : This one is great. Need to turn on trace flag 3057

 

Thanks Bob ward and Robert Dorr for investigation.

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

Speak at Huntington Beach 4/11/2015

Posted by Simon Cho on 08/20/2015

download link

SQLSaturday_HuntingtonBeach_20150411

Posted in Common | Leave a Comment »