Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

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 »

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 »