Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for November, 2016

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…

Advertisements

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 »