SQL 2016 DB engine features Compress and Decompress functions

SQL server added new function Compress and Decompress scalar valued function that take char/varchar as input and output a GZIP compressed varbinary data. This feature allows us to use column level compression of char/varchar data and reduces the cost of IO.

sql2016 compress
 

we can see we achieved almost 99% please note these results varies when data is stored in a table (row in flow)

Lets look at this in a detail example.

sql2016 compress2

 

sql2016 compress3

 

 

IO and time test

sql2016 compress4

 

Summary of comparision matrix between compress function and regular table we achieve 99% savings on storage on certain situations and achieve even more space when used in conjunction with row and page compression.

sql2016 compress5

Standard SQL Server Error log procedure

we spend lot of time trying to look at log files when we run any particular SQl files have a standard frame work to collect these errors and store in table, amkes the application management simple at same time provides some insights into exceptions over the time

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'wm_ErrorLog')
DROP TABLE [wm_ErrorLog]
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'WMSp_ErrorLog_Ins_Error' AND type ='P')
DROP PROCEDURE [WMSp_ErrorLog_Ins_Error]
CREATE TABLE [wm_ErrorLog] (
    [Error_id] INT IDENTITY(1,1)
	,[Error_Number] [INT] NOT NULL
	,[Error_procedure] VARCHAR(max) NOT NULL
	,[Error_Message] [VARCHAR](MAX) NULL
	,[Error_Line] INT
	,[Error_State] INT 
	,[Error_Severity] INT 
	,[SPID] [INT] NULL
	,[Program_Name] [VARCHAR](255) NULL
	,[Client_Address] [VARCHAR](255) NULL
	,[Authentication] [VARCHAR](50) NULL
	,[Error_User_Application] [VARCHAR](100) NULL
	,[Error_Date] [DATETIME] NULL
	,[Error_User_System] [sysname] NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE wm_ErrorLog ADD CONSTRAINT [dfltErrorLog_error_date] DEFAULT(GETDATE())
FOR [Error_Date]
GO

ALTER TABLE wm_ErrorLog ADD CONSTRAINT [dfltErrorLog_error_user_system] DEFAULT(SUSER_SNAME())
FOR [Error_User_System]
GO

ALTER TABLE [wm_ErrorLog] ADD CONSTRAINT wmpk_wm_errorlog_id PRIMARY KEY (error_id)

GO


Create  PROCEDURE [WMSp_ErrorLog_Ins_Error] (
	 @Error_Number INT = NULL
	,@Error_procedure SYSNAME = 'Ad hoc sql'
	,@Error_Message VARCHAR(4000) = NULL
	,@UserID INT = NULL
	)
AS
BEGIN
	BEGIN TRY
		INSERT INTO wm_ErrorLog (
			[Error_Number]
			,[Error_procedure]
			,[Error_Message]
			,[Error_line]
			,[Error_state]
			,[Error_severity]
			,[SPID]
			,[Program_Name]
			,[Client_Address]
			,[Authentication]
			,[Error_User_System]
			,[Error_User_Application]
			)
		SELECT [Error_Number] = ISNULL(@Error_Number, ERROR_NUMBER())
			,[Error_procedure] = COALESCE(ERROR_PROCEDURE(),@Error_procedure)
			,[Error_Message] = ISNULL(@Error_Message, ERROR_MESSAGE())
			,[Error_line] = ERROR_LINE()
			,[Error_state] =ERROR_STATE()
			,[Error_severity] = ERROR_SEVERITY()
			,[SPID] = @@SPID
			,[Program_Name] = ses.program_name
			,[Client_Address] = con.client_net_address
			,[Authentication] = con.auth_scheme
			,[Error_User_System] = SUSER_SNAME()
			,[Error_User_Application] = @UserID
		FROM sys.dm_exec_sessions ses 
		LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
		WHERE ses.session_id= @@SPID
	END TRY

	BEGIN CATCH
		-- We even failed at the log entry so let's get basic  
		INSERT INTO wm_ErrorLog (
			ERROR_NUMBER
			,ERROR_procedure
			,ERROR_MESSAGE
			)
		VALUES (
			- 100
			,OBJECT_NAME(@@PROCID)
			,ERROR_MESSAGE()
			)
	END CATCH
END
GO

Above scripts creates two objects a table and procedure

Here is how we should plan to use

BEGIN TRY
RAISERROR(‘error’,16,1)
END TRY
BEGIN CATCH
EXECUTE [WMSp_ErrorLog_Ins_Error]
END CATCH

SELECT * FROM wm_errorlog

Sql error log

How to FIX: Manage Orphaned users in SQL SERVER with a simple script

Using below scripts

 

  • Fixes all orphaned users in the current database if a login exists
  • If a login does not exist in thesql server instance
    •  User will be removed  — @fix = 0
      • If a user is a schema owner then the schema is transferred to DBO and then user is removed from DB
    •  or A login will be created  — @fix = 1

 


DECLARE @t TABLE (

id INT IDENTITY(1, 1)

,username SYSNAME

,usersid VARBINARY(85)

);

DECLARE @username SYSNAME = ;

DECLARE @loopid INT = 0D

ECLARE @DeleteCmd NVARCHAR(1000) = 

DECLARE @fix BIT = 0; — if it is set 0 then all the orphans will be removed 

— if its is set 1 orphans will be created and mapped to the user with a default passwordINSERT INTO @t (

username

,usersid

)

EXEC sp_change_users_login @Action = ‘Report’;SET @loopid = @@IDENTITY;WHILE @loopid > 0

BEGIN

SET @username = (

SELECT username

FROM @t

WHERE id = @loopid

)

IF EXISTS (

SELECT 1

FROM master.dbo.syslogins

WHERE NAME = @username

)

BEGIN

EXEC sp_change_users_login @Action = ‘update_one’

,@UserNamePattern = @username

,@LoginName = @username;

PRINT CONVERT(VARCHAR, @username) + ‘User updated succesfully ‘

END

ELSE

BEGIN

IF @fix = 0

BEGIN

SELECT @DeleteCmd = @DeleteCmd + (

CASE

WHEN s.NAME IS NULL

THEN 

ELSE ‘ALTER AUTHORIZATION ON SCHEMA::’ + ‘[‘ + s.NAME + ‘] TO dbo’ + ‘;’

END

) + ‘DROP USER ‘ + ‘[‘ + dp.NAME + ‘]’ + ‘;’

FROM sys.database_principals dp

LEFT OUTER JOIN sys.schemas s ON s.principal_id = dp.principal_id

WHERE type IN (

‘s’

,‘G’

,‘U’

)

AND dp.principal_id > 4

AND dp.NAME = @username

PRINT CONVERT(VARCHAR, @username) + ‘User Removed succesfully ‘

END

ELSE

BEGIN

EXEC sp_change_users_login ‘Auto_Fix’

,@username

,NULL

,‘Auto_Fix_Pass’;

PRINT CONVERT(VARCHAR, @username) + ‘ User has added to sql server with password Auto_Fix_Pass succesfully ‘

END

END

SET @loopid = @loopid  1;

ENDPRINT @DeleteCmdEXEC sp_executesql @DeleteCmd

— Print un resolved db accounts EXEC sp_change_users_login @Action = ‘Report’;

How to fix: Access to the remote server is denied because the current security context is not trusted SQL Server

You are seeing this error because you are trying to impersonate or  switching the execution context using EXECUTE AS clause and Executing a RPC on a linked server

 

LInked server

 

If you have a procedure on Server X

USE ServerXDB

Create procedure proc

execute as ‘TESTUSER’

Execute ServerY.TestDB.dbo.test

Select * from [ServerY] .TestDb.dbo.testtable

To Fix this you have two options
  1. Enable Trusty worthy on ServerXDB database on Serverx
  2. Remove the execute as clause in the procedure and grant execute privileges to user we used to set up the linked server

 

you can also try by signing a certificate on the proc procedure but I havent tried it

One of the best SQL Server Index Internals: A Deep Dive

its from Tim Chapman, one of the best index internal presentation

I will try to highlight the things he talk through this presentation

Indexes

  • Clustered vs Non clustered and filtered indexes
  • Non clustered indexes and include column effect on index structure
  • Heap
  • Heap vs Clustered
  • Various DMV utilization to understand index internals
  • How to detect fragmentation
  • Logical fragmentation and effects on query perfromance
  • Page splits , Page density, page fullness

Statistics

  • How to view statistics
  • Effect of statistics on query performance
  • effect of stats from index rebuild and reorg

Log usage

  • Index rebuild vs reorg
  • Log usage for Index rebuilds and reorganize

Over all must watch

 

Sql server datacollector issue specified @collector_type_uid is not valid in this data warehouse

agent error collector upload error

Executing the query “{call [core].[sp_create_snapshot](?, ?, ?, ?, ?, ?…” failed with the following error: “The specified @collector_type_uid (14AF3C12-38E6-4155-BD29-F33E7966BA23) is not valid in this data warehouse.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

if we look sp_create_snapshot procedure in Management_dataware_house  it accepts six parameters  @collection_set_uid   ,@collector_type_uid   ,@machine_name  , @named_instance  ,  @log_id   ,  @snapshot_id

this procedure creates a snapshot in the management datawarehouse for a collection instance one parameter is Collecter_type_id

Note: Sql Server Data collector can collect data from Dynamic management views , SQL trace and Windows performance counters using TSQL and WMI query’s

We can get list of availanle data collector types by querying

SELECT

[collector_type_uid],[name]

,[parameter_schema]

,[parameter_formatter]

,[schema_collection]

,[collection_package_name]

,[collection_package_folderid]

,[upload_package_name]

,[upload_package_folderid]

,[is_system]

FROM [msdb].[dbo].[syscollector_collector_types_internal]

Collector types

insertinto [core].[supported_collector_types_internal](collector_type_uid)

values (’14AF3C12-38E6-4155-BD29-F33E7966BA23′)

 

collector suscces full