Monthly Archives: January 2017

SSISDB – Package Execution Time in Minutes and Average Execution Time

–By Lori Brown   @SQLSupahStah

I have a client who has a lot of SSIS packages in the SSIS catalog that we are migrating to a new server. Of course there is lots of testing going on. So the question came up about how to more easily see package runtimes and comparing to the overall average runtime rather than using the Dashboard or the reports that come with Management Studio. After a little searching, I found that the SSISDB.internal.execution_info table contains what I needed.

— Filter by date and package name

DECLARE @begindate DATE = GETDATE() – 7

DECLARE @pkgname VARCHAR(100) = ‘Your Package Name— Ex: Patient_Update.dtsx

 

— Package execution time

SELECT folder_name, project_name, package_name,

CAST(start_time AS datetime) AS start_time,

DATEDIFF(MINUTE, start_time, end_time) AS ‘execution_time[min]’

FROM SSISDB.internal.execution_info

WHERE start_time >= @begindate

AND package_name = @pkgname — Comment out this line for all packages

ORDER BY start_time

 

— Average package execution time

SELECT folder_name, project_name, package_name,

AVG(DATEDIFF(MINUTE, start_time, end_time)) AS ‘Avg execution_time[min]’

FROM SSISDB.internal.execution_info

WHERE start_time >= @begindate

AND package_name = @pkgname — Comment out this line for all packages

GROUP BY folder_name,project_name,package_name

You can also use the catalog.executions view to get the same data but since I saw the internal table, I jumped in. This was tested on SQL 2016 but I think it will still work in earlier versions. Hope this helps someone out!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Find Currently Executing Jobs Running Longer Than 30 Day Average

–By Lori Brown   @SQLSupahStah

I needed to update some of our long running job monitoring code to improve it from the version that we have right now. I like this version because it uses msdb.dbo.syssessions (https://msdn.microsoft.com/en-us/library/ms175016.aspx) to validate that a job is actually running. I also wanted to know the percent difference between the current run duration versus an average duration per job from the past 30 days. I decided to place the calculated average into a table variable and then join on it to get my results. I also used the IIF function (https://msdn.microsoft.com/en-us/library/hh213574.aspx) to help me avoid a divide by zero error that comes up when the average duration equals 0.

I also learned about a function that I had never heard of before, msdb.dbo.agent_datetime. Interestingly, I cannot find documentation of this function from MSDN. However, this had apparently been around for a while. I don’t feel too bad about not knowing about this one since even super smart Pinal Dave had only recently found it too (https://blog.sqlauthority.com/2015/03/13/sql-server-interesting-function-agent_datetime/)

— currently executing jobs running longer than 30 day avg runtime

declare @startdt date

declare @enddt date

declare @JobsAvgDuration table (job_id uniqueidentifier, JobName varchar(250), ThirtyDayAvgDurMin int)

 

set @startdt = getdate()-30

set @enddt = getdate()

 

insert into @JobsAvgDuration

select j.job_id, j.name as JobName,

AVG((h.run_duration/10000*3600 + (h.run_duration/100)%100*60 + h.run_duration%100 + 31 ) / 60) as ThirtyDayAvgDurMin

from msdb.dbo.sysjobs j

join msdb.dbo.sysjobhistory h on (j.job_id = h.job_id)

where j.enabled = 1

and msdb.dbo.agent_datetime(h.run_date, 0) between @startdt and @enddt

group by j.job_id, j.name

order by j.name

 

select j.name as LongRunningJobName,

datediff(mi, a.start_execution_date, getdate()) as MinsRunning,

d.ThirtyDayAvgDurMin,

(datediff(mi, a.start_execution_date, getdate())-d.ThirtyDayAvgDurMin)*100/IIF(d.ThirtyDayAvgDurMin=0,1,d.ThirtyDayAvgDurMin) as PercentDiff

from msdb.dbo.sysjobs j

join msdb.dbo.sysjobactivity a on (j.job_id = a.job_id)

join @JobsAvgDuration d on (d.job_id = j.job_id)

where a.session_id in (select session_id from msdb.dbo.syssessions) — active session

and (a.start_execution_date IS NOT NULL and a.stop_execution_date IS NULL)

and datediff(mi, a.start_execution_date, getdate()) > d.ThirtyDayAvgDurMin

I had to force one of my monitoring jobs to run long so I could show you the results.

blog_20170119_1

All that is left is to put the results into a table or a report and I will be done. Let me know if you have a unique way of monitoring for jobs that run extraordinarily long. We would love to see other solutions!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

SQL 2016 SP1 USE HINT

–By Lori Brown   @SQLSupahStah

After installing Service Pack 1 for SQL 2016, I ran across USE HINT and thought I would put out what I could find to document it. Here’s where you find info from Microsoft: https://msdn.microsoft.com/en-us/library/ms181714.aspx and here is a link for all of the bugs that are fixed in SP1: https://support.microsoft.com/en-us/kb/3182545

USE HINT ( hint_name ) Provides one or more additional hints to the query processor as specified by a hint name inside single quotation marks. Hint names are case-insensitive. USE HINT can be utilized without having to be a member of the sysadmin server role.

The following hint names are supported:

  • ‘DISABLE_OPTIMIZED_NESTED_LOOP’ Instructs the query processor not to use a sort operation (batch sort) for optimized nested loop joins when generating a query plan. Equivalent to trace flag 2340.
  • ‘FORCE_LEGACY_CARDINALITY_ESTIMATION’ Forces the query optimizer to use Cardinality Estimation model of SQL Server 2012 and earlier versions. Equivalent to trace flag 9481 or Database Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION=ON.
  • ‘ENABLE_QUERY_OPTIMIZER_HOTFIXES’ Enables query optimizer hotfixes (changes released in SQL Server Cumulative Updates and Service Packs). Equivalent to trace flag 4199 or Database Scoped Configuration setting QUERY_OPTIMIZER_HOTFIXES=ON.
  • ‘DISABLE_PARAMETER_SNIFFING’ Instructs query optimizer to use average data distribution while compiling a query with one or more parameters, making the query plan independent of the parameter value which was first used when the query was compiled. Equivalent to trace flag 4136 or Database Scoped Configuration setting PARAMETER_SNIFFING=OFF.
  • ‘ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES’ Causes SQL Server to generate a plan using minimum selectivity when estimating AND predicates for filters to account for correlation. Equivalent to trace flag 4137 when used with cardinality estimation model of SQL Server 2012 and earlier versions, and has similar effect when trace flag 9471 is used with cardinality estimation model of SQL Server 2014 or higher.
  • ‘DISABLE_OPTIMIZER_ROWGOAL’ Causes SQL Server to generate a plan that does not use row goal adjustments with queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords. Equivalent to trace flag 4138.
  • ‘ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS’ Enables automatically generated quick statistics (histogram amendment) for any leading index column for which cardinality estimation is needed. The histogram used to estimate cardinality will be adjusted at query compile time to account for actual maximum or minimum value of this column. Equivalent to trace flag 4139.
  • ‘ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS’ Causes SQL Server to generate a query plan using the Simple Containment assumption instead of the default Base Containment assumption for joins, under the query optimizer Cardinality Estimation model of SQL Server 2014 or newer. Equivalent to trace flag 9476.
  • ‘FORCE_DEFAULT_CARDINALITY_ESTIMATION’ Forces the Query Optimizer to use Cardinality Estimation model that corresponds to the current database compatibility level. Use this hint to override Database Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION=ON or trace flag 9481.

The list of all supported USE HINT names can be queried using the dynamic management view sys.dm_exec_valid_use_hints. More this view can be found here: https://msdn.microsoft.com/en-us/library/mt791356.aspx

Some USE HINT hints may conflict with trace flags enabled at the global or session level, or database scoped configuration settings. In this case, the query level hint (USE HINT) always takes precedence. If a USE HINT conflicts with another query hint or a trace flag enabled at the query level (such as by QUERYTRACEON), SQL Server will generate an error when trying to execute the query.

Example:

DECLARE @qty INT

SET @qty = 4

SELECT h.OrderDate, h.Freight, d.OrderQty, d.UnitPrice

FROM Sales.SalesOrderDetail d

JOIN Sales.SalesOrderHeader h ON (d.SalesOrderID = h.SalesOrderID)

WHERE d.OrderQty > @qty

OPTION (USE HINT(‘DISABLE_PARAMETER_SNIFFING’,‘DISABLE_OPTIMIZED_NESTED_LOOP’));

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Synchronize Logins on AlwaysOn Replicas

–By Lori Brown   @SQLSupahStah

At one of my clients who has an AlwaysOn failover cluster, I noticed as I was checking up on things on the secondary replica that there were several fewer Windows and SQL logins on the secondary than on the primary. I really did not want to tediously compare the logins on each so instead came up with a script that can be run periodically to script out any logins that need to be synched up between the replicas. I actually found most of the script that I needed on the SQLSoldier’s blog (http://www.sqlsoldier.com/wp/) in a stored procedure that was intended to synch logins between mirrored partners. Since AlwaysOn is Mirroring on steroids I thought that it would work and it did. I did put my own touches on the proc by turning it into a standalone script that uses OPENROWSET to query the AG primary replica and also changed it from automatically executing the statements for missing logins to simply printing them into a script that can be applied as needed.

And now….here is what you came here for….

— Original logic from http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

— Sync Logins to AlwaysOn Replicas

—     Inputs: @PartnerServer – Target Instance (InstName or Machine\NamedInst or Instname,port)

—     Output: All Statements to create logins with SID and Password for both Windows and SQL logins

—             Will also add logins to server roles

—     Person executing this must be sysadmin

—     Ad Hoc Distributed Queries must be enabled for this to work without setting up a linked server

/*

— Turn on Ad Hoc Distributed Queries so we don’t have to set up a linked server

sp_configure ‘show advanced options’, 1

GO

reconfigure with override

go

sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

reconfigure with override

go

*/

 

Use master;

Go

Declare @MaxID int,

@CurrID int,

@PartnerServer sysname,

@SQL nvarchar(max),

@LoginName sysname,

@IsDisabled int,

@Type char(1),

@SID varbinary(85),

@SIDString nvarchar(100),

@PasswordHash varbinary(256),

@PasswordHashString nvarchar(300),

@RoleName sysname,

@Machine sysname,

@PermState nvarchar(60),

@PermName sysname,

@Class tinyint,

@MajorID int,

@ErrNumber int,

@ErrSeverity int,

@ErrState int,

@ErrProcedure sysname,

@ErrLine int,

@ErrMsg nvarchar(2048)

 

SET @PartnerServer = ‘InstName’

 

Declare @Logins Table (LoginID int identity(1, 1) not null primary key,

[Name] sysname not null,

[SID] varbinary(85) not null,

IsDisabled int not null,

[Type] char(1) not null,

PasswordHash varbinary(256) null)

 

Declare @Roles Table (RoleID int identity(1, 1) not null primary key,

RoleName sysname not null,

LoginName sysname not null)

 

Declare @Perms Table (PermID int identity(1, 1) not null primary key,

LoginName sysname not null,

PermState nvarchar(60) not null,

PermName sysname not null,

Class tinyint not null,

ClassDesc nvarchar(60) not null,

MajorID int not null,

SubLoginName sysname null,

SubEndPointName sysname null)

 

Set NoCount On;

 

If CharIndex(‘\’, @PartnerServer) > 0 — Check for Named Instance

Begin

Set @Machine = LEFT(@PartnerServer, CharIndex(‘\’, @PartnerServer) – 1);

End

Else If CharIndex(‘,’, @PartnerServer) > 0 — Check for Instance with port in connection string

Begin

Set @Machine = LEFT(@PartnerServer, CharIndex(‘,’, @PartnerServer) – 1);

End

Else

Begin

Set @Machine = @PartnerServer;

End

 

— Get all Windows logins from principal server

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select P.name, P.sid, P.is_disabled, P.type, L.password_hash

             From master.sys.server_principals P

             Left Join master.sys.sql_logins L On L.principal_id = P.principal_id

             Where P.type In (””U””, ””G””, ””S””)

             And P.name <> ””sa””

             And P.name Not Like ””##%””

             And CharIndex(””’ + @Machine + ‘\””, P.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)

Exec sp_executesql @SQL;

 

— Get all roles from principal server

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select RoleP.name as RoleName, LoginP.name as LoginName

             From master.sys.server_role_members RM

             Inner Join master.sys.server_principals RoleP

             On RoleP.principal_id = RM.role_principal_id

             Inner Join master.sys.server_principals LoginP

             On LoginP.principal_id = RM.member_principal_id

             Where LoginP.type In (””U””, ””G””, ””S””)

             And LoginP.name <> ””sa””

             And LoginP.name Not Like ””##%””

             And RoleP.type = ””R””

             And CharIndex(””’ + @Machine + ‘\””, LoginP.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Roles (RoleName, LoginName)

Exec sp_executesql @SQL;

 

— Get all explicitly granted permissions

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select P.name Collate database_default,

                    SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,

                    SubP.name Collate database_default,

                    SubEP.name Collate database_default

             From master.sys.server_principals P

             Inner Join master.sys.server_permissions SP

             On SP.grantee_principal_id = P.principal_id

             Left Join master.sys.server_principals SubP

             On SubP.principal_id = SP.major_id And SP.class = 101

             Left Join master.sys.endpoints SubEP

             On SubEP.endpoint_id = SP.major_id And SP.class = 105

             Where P.type In (””U””, ””G””, ””S””)

             And P.name <> ””sa””

             And P.name Not Like ””##%””

             And CharIndex(””’ + @Machine + ‘\””, P.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)

Exec sp_executesql @SQL;

 

Select @MaxID = Max(LoginID), @CurrID = 1

From @Logins;

 

While @CurrID <= @MaxID

Begin

Select @LoginName = Name,

@IsDisabled = IsDisabled,

@Type = [Type],

@SID = [SID],

@PasswordHash = PasswordHash

From @Logins

Where LoginID = @CurrID;

 

If Not Exists (Select 1 From sys.server_principals

Where name = @LoginName)

Begin

Set @SQL = ‘Create Login ‘ + quotename(@LoginName)

If @Type In (‘U’, ‘G’)

Begin

Set @SQL = @SQL + ‘ From Windows;’

End

Else

Begin

Set @PasswordHashString = ‘0x’ +

Cast( As XML).value(‘xs:hexBinary(sql:variable(“@PasswordHash”))’, ‘nvarchar(300)’);

 

Set @SQL = @SQL + ‘ With Password = ‘ + @PasswordHashString + ‘ HASHED, ‘;

 

Set @SIDString = ‘0x’ +

Cast( As XML).value(‘xs:hexBinary(sql:variable(“@SID”))’, ‘nvarchar(100)’);

 

Set @SQL = @SQL + ‘SID = ‘ + @SIDString + ‘;’;

End

 

Print @SQL;

 

If @IsDisabled = 1

Begin

Set @SQL = ‘Alter Login ‘ + quotename(@LoginName) + ‘ Disable;’

Print @SQL;

End

End

Set @CurrID = @CurrID + 1;

End

 

Select @MaxID = Max(RoleID), @CurrID = 1

From @Roles;

 

While @CurrID <= @MaxID

Begin

Select @LoginName = LoginName,

@RoleName = RoleName

From @Roles

Where RoleID = @CurrID;

 

If Not Exists (Select 1 From sys.server_role_members RM

Inner Join sys.server_principals RoleP

On RoleP.principal_id = RM.role_principal_id

Inner Join sys.server_principals LoginP

On LoginP.principal_id = RM.member_principal_id

Where LoginP.type In (‘U’, ‘G’, ‘S’)

And RoleP.type = ‘R’

And RoleP.name = @RoleName

And LoginP.name = @LoginName)

Begin

Print ‘Exec sp_addsrvrolemember @rolename = ”’ + @RoleName + ”’, @loginame = ”’ + @LoginName + ”’;’;

End

Set @CurrID = @CurrID + 1;

End

 

Select @MaxID = Max(PermID), @CurrID = 1

From @Perms;

 

While @CurrID <= @MaxID

Begin

Select @PermState = PermState,

@PermName = PermName,

@Class = Class,

@LoginName = LoginName,

@MajorID = MajorID,

@SQL = PermState + space(1) + PermName + SPACE(1) +

Case Class When 101 Then ‘On Login::’ + QUOTENAME(SubLoginName)

When 105 Then ‘On ‘ + ClassDesc + ‘::’ + QUOTENAME(SubEndPointName)

Else End +

‘ To ‘ + QUOTENAME(LoginName) + ‘;’

From @Perms

Where PermID = @CurrID;

 

If Not Exists (Select 1 From sys.server_principals P

Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id

Where SP.state_desc = @PermState

And SP.permission_name = @PermName

And SP.class = @Class

And P.name = @LoginName

And SP.major_id = @MajorID)

Begin

Print @SQL;

End

Set @CurrID = @CurrID + 1;

End

 

Set NoCount Off;

GO

 

/*

— Turn off Ad Hoc Distributed Queries

sp_configure ‘Ad Hoc Distributed Queries’, 0

GO

reconfigure with override

go

*/

On my test AG replicas, you can see that there are two users that are not present on the secondary. I need to add them so that if a failover occurs then there will not be any login failures.

blog_20170105_1

Using the script above on SQLSRVTST2, I get the following output and can use that to create my users with their correct permissions. I had to set the @PartnerServer variable to SQLSRVTST1 in the script and enable Ad Hoc Distributed Queries.

blog_20170105_2

……

blog_20170105_3

blog_20170105_4

The nice thing is that the password and SID’s are also scripted for you. There is no need to install sp_hexadecimal and sp_help_revlogin in master. If you have AG replicas with a lot of logins, this can be a real timesaver for you. Hope you enjoy and if you make any improvements, please share them with us!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!