SQL Server – Partial Database Restores – Benefits & Impact

Lineage

In our last couple of posts we focused our lenses on partial database restores.

Those posts are here:

  1. SQL Server – Database Restore – “Defunct State”
    here
  2. SQL Server – Database Restore – Skipping File Groups
    here

 

Current

In this post we will look a bit more at the benefits and costs of restoring just a portion of database.

 

Reviews

GUI

Database

File Groups

databaseProperties-FileGroups (cropped-up)

 

Files

databaseProperties-Files

 

 

Tables

dbo.ten95B

tableCount-Table1

 

Storage
  1. Table Name :- dbo.ten95B
  2. File Group :- PRIMARY
  3. Text filegroup :- fgLOB
  4. Data Space
    • 1370 MB
    • or 1.4 GB
  5. Row Count :- 4800

 

Query

Database

Database – Files

Let us query the sys.database_files system table to get the name, type, state, and size of the individual files that are in our current database.

SQL

select 
		  tblSDF.[file_id]
		, tblSDF.[name]
		, tblSDF.[physical_name]
		, tblSDF.[type_desc]
		, tblSDF.[state_desc]
		, tblSDF.size
--		, tblSDF.*
from   sys.database_files tblSDF

Image

Database – Show File Stats

Let us issue “DBCC ShowFileStats” to gather information about our database files.

SQL
dbcc showfilestats

Image

Table

Let us try to read data from table

 

Query Table for Data – All Columns

dbo.ten95C
SQL

select top (1000) *
from [dbo].[ten95C]

Image
filegroupThatCanNotBeAccessed

 

Textual

Large object (LOB) data for table “dbo.ten95C” resides on an offline filegroup (“fgLOB”) that cannot be accessed

 

Query Table for Data – Specific Columns

On the other hand, we are still able to query for specific columns.

When we restrict our column set to those filegroups that were included in our restore, things are good.

dbo.ten95C
SQL

select top (10)
 
   tblT.[ten95C_id]
 , tblT.[ten95C_view_cnt]
 , tblT.[ten95C_view_last]

from [dbo].[ten95C] tblT

Image

Summary

From a cursory look, Microsoft’s design and implementation of Partial database restore is well thought out and solid.

Its preserves all the niceties of having access to the original database design ( database groups and files), metadata for all concerns.

And, rightfully sacrifices storage of the actual data and thus we gain the benefits of not having to provide and maintain storage for uneeded data.

Transact SQL – Identify In-Memory Objects

Introduction

Let us identify In Memory Objects in our SQL Server Instance.

 

User Tables – sys.tables

Overview

In the sample code below, we identify In Memory Objects by checking the is_memory_optimized column in the sys.tables DMV.

Code



select
		 [table]
			= quotename(tblSS.[name])
				+ '.'
				+ quotename(tblST.[name])

from   sys.tables tblST

inner join sys.schemas tblSS

		on tblST.[schema_id] = tblSS.[schema_id]

where  tblST.is_memory_optimized = 1

order by
		[table] asc


Output

identifyinmemorytables-systables

User Tables & Indexes – Based on FileGroups

Overview

In the sample code below, we identify In Memory Objects by seeking out objects that are located on In-Memory Filegroups.

Code



select 

		  [object]
			= tblSS.[name]
				+ '.'
				+ tblSO.[name]

		, [objectType]
			= tblSO.[type_desc]


		, [indexName]
			= tblSI.[name]


		, [isPrimaryKey]
			= case
				when tblSI.[is_primary_key] = 1 then 'Yes'
				else 'No'
			  end

		, [indexType]
			= tblSI.[type_desc]
	
		, [fileGroup]
			= tblSFG.[name]

		, [fileGroupType]
			= tblSFG.[type_desc]

from   sys.objects tblSO

INNER JOIN sys.schemas tblSS

	ON tblSO.schema_id = tblSS.schema_id

inner join sys.indexes tblSI

	on tblSO.object_id = tblSI.object_id

inner join sys.partitions tblSP

	on  tblSI.object_id = tblSP.object_id
	and tblSI.index_id = tblSP.index_id

		
INNER JOIN sys.allocation_units tblSAU

	on tblSAU.container_id = tblSP.hobt_id
		 
INNER JOIN sys.filegroups tblSFG
 
	ON tblSFG.data_space_id = tblSAU.data_space_id 

where tblSFG.[type] = 'FX'



Output

identifyinmemoryobjects

 

 

User Tables

DML Stats

Overview

Review Statistics of DML Operations against In-Memory Tables.

Code


SELECT
	  [object]
		= quoteName(tblS.[name])
		   + '.'
		   + quoteName(tblO.[name])

        , tblXTPOS.*

FROM sys.dm_db_xtp_object_stats tblXTPOS

INNER JOIN sys.objects tblO

	ON tblXTPOS.object_id = tblO.object_id

INNER JOIN sys.schemas tblS

	ON tblO.schema_id = tblS.schema_id


 

Output

dmlstats

 

Index Stats

Overview

Review Statistics of DML Operations against In-Memory Tables.

Code





SELECT
        [object]
        = quoteName(tblS.[name])
           + '.'
           + quoteName(tblO.[name])
 
		, [index]
			= tblSI.[name]

		, [indexType]
			= tblSI.[type_desc]

		, [isPrimaryKey]
			= case
				when ( tblSI.[is_primary_key] = 1 ) then 'Y'
				else 'N'
			  end

        , [scansStarted]
			= tblXTPIS.scans_started	

		, [scansRetried]
			= tblXTPIS.scans_retries
			
		, [rowsReturned]
			= tblXTPIS.rows_returned

		, [rowsTouched]
			= tblXTPIS.rows_touched	

		/*

		--- Expiring -----
		, tblXTPIS.rows_expiring
		, tblXTPIS.rows_expired
		, tblXTPIS.rows_expired_removed	
		--- Expiring -----

		-- Phantom ----
		, tblXTPIS.phantom_scans_started	
		, tblXTPIS.phantom_scans_retries	
		, tblXTPIS.phantom_rows_touched	
		, tblXTPIS.phantom_expiring_rows_encountered	
		--, phantom_expired_rows_encountered	
		, tblXTPIS.phantom_expired_removed_rows_encountered	
		, tblXTPIS.phantom_expired_rows_removed
		-- Phantom ----

		*/

FROM sys.dm_db_xtp_index_stats tblXTPIS
 
INNER JOIN sys.objects tblO
 
    ON tblXTPIS.object_id = tblO.object_id

INNER JOIN sys.indexes tblSI
 
    ON  tblXTPIS.object_id = tblSI.object_id 
    AND tblXTPIS.index_id = tblSI.index_id 

INNER JOIN sys.schemas tblS
 
    ON tblO.schema_id = tblS.schema_id

order by
		  tblS.[name]
		, tblO.[name]
		, tblSI.[name]

Output

imtabledmlindexstats

Explanation:

  1. In-Memory tables does not support Clustered Indexes as all data is stored in memory
    • If Clustered,then memory will have to be continuously shuffled to ensure proper sequence
  2. It makes sense to review ScanStarted
    • Doing so is informative in terms of which indexes are actually being used
    • And, to gauge popularity
  3. The RowsReturned Column
    • Reviewing the RowsReturned column is also important to follow trend in terms of access method

Memory Usage Stats

Memory Usage Stats – Object

Overview

Review Memory allocated and in-use by In-Memory Tables.

Code


SELECT

	[object]
		= quotename(tblSS.name)
			+'.'
			+ quotename(tblSO.name)

    , [allocatedMB]
		= SUM(tblXTPMC.[allocated_bytes]) / (1024* 1024)

	, [usedMB]
		= SUM(tblXTPMC.[used_bytes]) / (1024 * 1024)

FROM  sys.objects tblSO

INNER JOIN sys.schemas tblSS
 
	ON tblSO.schema_id = tblSS.schema_id 

INNER JOIN sys.indexes tblSI
 
	ON tblSO.object_id = tblSI.object_id 

INNER JOIN sys.dm_db_xtp_memory_consumers tblXTPMC 

	ON  tblSI.object_id = tblXTPMC.object_id
	AND tblSI.index_id  = tblXTPMC.index_id

GROUP BY 
		  quotename(tblSS.name)
		, tblSO.schema_id
		, tblSO.object_id
		, tblSO.[name]
		
ORDER BY 
		  quotename(tblSS.name)
		, tblSO.[name]
;


Output

memoryallocatedandinusedbyobject

Memory Usage Stats – Index

Overview

Review Memory allocated and in-use by In-Memory Table Indexes.

Code


SELECT

	[object]
		= quotename(tblSS.name)
			+'.'
			+ quotename(tblSO.name)

	, [index]
		= tblSI.name

	, [indexType]
		= tblSI.[type_desc]

    , [isPrimaryKey]
        = case
            when tblSI.[is_primary_key] = 1 then 'Yes'
            else 'No'
            end

    , [allocatedMB]
		= SUM(tblXTPMC.[allocated_bytes]) / (1024* 1024)

	, [usedMB]
		= SUM(tblXTPMC.[used_bytes]) / (1024 * 1024)

FROM  sys.objects tblSO

INNER JOIN sys.schemas tblSS
 
	ON tblSO.schema_id = tblSS.schema_id 

INNER JOIN sys.indexes tblSI
 
	ON tblSO.object_id = tblSI.object_id 

INNER JOIN sys.dm_db_xtp_memory_consumers tblXTPMC 

	ON  tblSI.object_id = tblXTPMC.object_id
	AND tblSI.index_id  = tblXTPMC.index_id

GROUP BY 
		  quotename(tblSS.name)
		, tblSO.schema_id
		, tblSO.object_id
		, tblSO.[name]
		, tblSI.name
		, tblSI.[type_desc]
		, tblSI.[is_primary_key]
		
ORDER BY 
		  quotename(tblSS.name)
		, tblSO.[name]
		, tblSI.name


Output

memoryallocatedandinusedbyobjectandindex

Programmable Objects – sys.modules

Overview

In the sample code below, we identify In Memory Objects by checking the is_memory_optimized column in the sys.tables DMV.

Code



/*

	sys.sql_modules

		execute_as_principal_id

			Value of -2 indicates that the batch submitted does not depend on implicit name resolution and can be shared among different users. 
			This is the preferred method. Any other value represents the user ID of the user submitting the query in the database.

*/

select 
		  [object]
			= quoteName(tblSS.[name])
				+ '.'
				+ quoteName(object_name(tblSSM.[object_id]))

		, [type]
			= tblSO.type_desc

		, [createDate]
			= tblSO.[create_date]

		, [isSchemaBound]
			= case
					when tblSSM.is_schema_bound = 1 then 'Y'
					else 'N'
			  end	

		, [principal]
			= case
			 
					when ( tblSSM.execute_as_principal_id is null ) 
						then tblSS.[name]
			  
					when  ( tblSSM.execute_as_principal_id = -2) 
						then '--Shared--'

					else user_name(tblSSM.execute_as_principal_id)

						  
			  end			 
		      
		, [definition]
			= tblSSM.[definition]

from  sys.objects tblSO

inner join sys.schemas tblSS

		on tblSO.[schema_id] = tblSS.[schema_id]

inner join sys.sql_modules tblSSM

		on tblSO.[object_id] = tblSSM.[object_id]

where tblSSM.[uses_native_compilation] = 1

Output

identifynativecompiledobjects

Loaded Modules – sys.dm_os_loaded_modules

Overview

In the sample code below, we identify In Memory Objects by reviewing loaded OS Modules.

The relevant DMVs are sys.dm_os_loaded_modules and sys.dm_os_virtual_address_dump.

 

Code



; with cteOSLM
(
	  [modulename]
	, [description]
    , [base_address]
	, [filenameFull]
	, [filename]
	, [filenameMassaged]
)
as
(

	SELECT
			  [name]

			, [description]

			, [base_address]

			, [filenameFull] = tblOSLM.[name]

			, [filename]
				= reverse(left(reverse(tblOSLM.[name]),
                    charindex('\',reverse(tblOSLM.[name]), 1) - 1))

			, [filenameMassaged]
				= replace(
							replace(
									 reverse(left(reverse(tblOSLM.[name]),
										charindex('\',reverse(tblOSLM.[name]), 1) - 1))
										, '_'
									, '.'
									)
							, '.dll'
							, ''
						)				

	
	FROM   sys.dm_os_loaded_modules tblOSLM 

	WHERE  tblOSLM.[description] = 'XTP Native DLL'

	
)

, cteOSLMObject
(
	  [modulename]
	, [description]
    , [base_address]
	, [filenameFull]
	, [filename]
	, [filenameMassaged]

	, [objectID] 
	, [databaseID] 
	, [objectType]

)
as
(

	SELECT 

		  	  [modulename]
			, [description]
			, [base_address]
			, [filenameFull]
			, [filename]
			, [filenameMassaged]


			, [objectID] 
				= PARSENAME([filenameMassaged], 1)

			, [databaseID] 
				= PARSENAME([filenameMassaged], 2)
			
			, [objectType]
				= case PARSENAME([filenameMassaged], 3)
						when 't' then 'Table'
						when 'p' then 'Procedure'
						when 'f' then 'Function'
						else PARSENAME([filenameMassaged], 3)
				  end 

	from   cteOSLM

)
, cteVirtualAddress
(
	  [region_allocation_base_address]
	, [regionSizeInBytes]
)
as
(
	select 
			  tblOSVAD.[region_allocation_base_address]
			, [regionSizeInBytes]
				= sum(tblOSVAD.region_size_in_bytes)
	
	from   sys.dm_os_virtual_address_dump tblOSVAD
	
	group by
	 
			tblOSVAD.[region_allocation_base_address]

)	 
SELECT 

		  tblOSLM.[description]

		, tblOSLM.[modulename]

		, tblOSLM.[filename]


		, [database]
			= case
				when tblOSLM.[databaseID] = 32767 then 'Resource DB'
				else db_name(tblOSLM.[databaseID])
			   end

		, [objectName]
			= quoteName
				(
				    object_schema_name
					(
					  tblOSLM.objectID
					, tblOSLM.databaseID
					)
				)
				+ '.'
				+ quoteName
				(
					object_name
					(
					   tblOSLM.objectID
					 , tblOSLM.databaseID
					)
				)

		, tblOSLM.[objectType]

		, [sizeInKB]
			= (tblOSVAD.[regionSizeInBytes])
				/ ( 1024 )

FROM   cteOSLMObject tblOSLM 

INNER JOIN cteVirtualAddress tblOSVAD

		on tblOSLM.[base_address] = tblOSVAD.[region_allocation_base_address]

order by

			  [database]
			, [objectName]


Output

osloadedmodules

 

Explanation

  1. We can see that we can the SQL Server Engine generates Database Object specific modules (dlls)
  2. The modules are aptly named once one know where to look
  3. And, they are very small in size

 

Summary

In summary, to identify In Memory Objects we have a couple of tracks we can take.

Those pathways includes:

  1. Checking Table – sys.tables
    • Column :- is_memory_optimized
  2. Checking objects that are sitting on Memory Optimized Filegroup by relying on the sys.filegroups.

We reviewed user usage by digging into the sys.dm_db_xtp_object_stats & sys.dm_db_xtp_index_stats.

We also touched on memory consumed by In Memory Objects and Indexes by exploring the sys.dm_db_xtp_memory_consumers view.

To identify natively compiled objects we narrow in on the uses_native_compilation column of the sys.sql_modules view

To dig a bit deeper and identify OS Modules that are produced once In-Memory tables are compiled, we look into sys.dm_os_loaded_modules and to size up them up we check the region_size_in_bytes column in the sys.dm_os_virtual_address_dump table.

 

Source Code Repository

GitHub

Blogs are not where to keep code, and so for easier and more polish consumption shipped out to GitHub.

Here is the Repository.

 

Dedicated

Again, the good thing about blogging is that one publicly acknowledges the source of Information.

Here is mine:

  1. SqlHints.com, Basavaraj Biradar
    • Working with In-Memory OLTP (a.k.a. Hekaton) enabled Databases, Memory Optimized Tables and Natively Compiled Stored Procedures and it’s Internals with extensive list of Examples – Sql Server 2014
      Link
  2.  Help: SQL Server, Balmukund
    • A-Z of In-Memory OLTP : Behind the scenes
      Link

 

References

Microsoft

    1. Dynamic Management Views and Objects
      • Transact-SQL Reference (Database Engine) > System Views (Transact-SQL) > Dynamic Management Views and Functions (Transact-SQL)
        Memory-Optimized Table Dynamic Management Views (Transact-SQL)
        Link

        • sys.dm_db_xtp_memory_consumers (Transact-SQL)
          Link
        • sys.dm_db_xtp_index_stats ( Transact SQL )
          Link
        • sys.dm_db_xtp_object_stats ( Transact-SQL )
          Link
        • sys.memory_optimized_tables_internal_attributes ( Transact-SQL )
          Link
    2. Developer Network
      • Database Features > In-Memory OLTP (In-Memory Optimization)  > Memory-Optimized Tables
        Native Compilation of Tables and Stored Procedures
        Link

SQL Server – Server Principals / Logins

 

Background

In this post we will cover some of the principals that are created upon SQL Server Installation.

In some cases full sysadmin permissions are granted.

And, in some cases, through separation of duties, minimal permissions are granted.

 

Server Principals

Matrix

Name SID Type Create Date Usage
 ##MS_PolicyEventProcessingLogin##  0x51D095ECDC6C164C9B6047F5838CAAA0 SQL_LOGIN  [Install Date] Policy Based Management
 ##MS_PolicyTsqlExecutionLogin##  0xA9EEB439F4762546BD90D163703F6DA1 SQL_LOGIN 2014-02-20 20:49:46.837  Policy Based Management
 NT AUTHORITY\SYSTEM  0x010100000000000512000000  WINDOWS_LOGIN  [Install Date]  Local System
NT SERVICE\MSSQLSERVER  0x010600000000000550000000E20F4FE7B15874E48E19026478C2DC9AC307B83E  WINDOWS_LOGIN  [Install Date] Database Engine / Default Instance
NT Service\MSSQL$[Instance-Name]  WINDOWS_LOGIN  [Install Date]  Database Engine / Named Instance
 NT SERVICE\ReportServer  0x010600000000000550000000214401ACF066EA342187301080455260EB684BA2  WINDOWS_LOGIN  [Install Date] Reporting Services / Default Instance
 NT SERVICE\ReportServer$[Instance-Name]  WINDOWS_LOGIN  [Install Date] Reporting Services / Named Instance
NT SERVICE\SQLSERVERAGENT 0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357  WINDOWS_LOGIN [Install Date] SQL Server Agent
NT SERVICE\SQLAgent$[Instance-Name]  WINDOWS_LOGIN  [Install Date]  SQL Server Agent / Named Instance
 NT SERVICE\SQLWriter  0x010600000000000550000000732B9753646EF90356745CB675C3AA6CD6B4D28B  WINDOWS_LOGIN  [Install Date]  Database Backup
 NT SERVICE\Winmgmt  0x0106000000000005500000005A048DDFF9C7430AB450D4E7477A2172AB4170F4  WINDOWS_LOGIN  [Install Date]  Windows Management

 

 

 

Categories

  1. Policy Based Management
    • ##MS_PolicyEventProcessingLogin##
    • ##MS_PolicyTsqlExecutionLogin##
  2. Local System Account
    • NT AUTHORITY\SYSTEM
  3. NT SERVICE\MSSQLServer
    • Database Engine
  4. NT SERVICE\ReportServer
    • The account specified during setup is provisioned as a member of the RSExecRole database role.
      Link
  5. NT SERVICE\SQLServerAgent
    • SQL Server Agent
      • Link
        The per-service SID of the SQL Server Agent service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.
  6. NT Service\SQLWriter
    • SQL Writer Service
      • Link
        The SQL Writer service must run under the Local System account. The SQL Writer service uses the NT Service\SQLWriter login to connect to SQL Server. Using the NT Service\SQLWriter login allows the SQL Writer process to run at a lower privilege level in an account designated as no login, which limits vulnerability. If the SQL Writer service is disabled, then any utility which in relies on VSS snapshots, such as System Center Data Protection Manager, as well as some other 3rd-party products, would be broken, or worse, at risk of taking backups of databases which were not consistent. If neither SQL Server, the system it runs on, nor the host system (in the event of a virtual machine), need to use anything besides Transact-SQL backup, then the SQL Writer service can be safely disabled and the login removed. Note that the SQL Writer service may be invoked by a system or volume level backup, whether the backup is directly snapshot-based or not. Some system backup products use VSS to avoid being blocked by open or locked files. The SQL Writer service needs elevated permissions in SQL Server because in the course of its activities it briefly freezes all I/O for the instance of SQL Server.
  7. NT Service\Winmgmt
    • WMI
      Link
      Windows Management Instrumentation (WMI) must be able to connect to the Database Engine. To support this, the per-service SID of the Windows WMI provider (NT SERVICE\winmgmt) is provisioned in the Database Engine.
      The SQL WMI provider requires the following permissions:

      • Membership in the db_ddladmin or db_owner fixed database roles in the msdb database.
      • CREATE DDL EVENT NOTIFICATION permission in the server.
      • CREATE TRACE EVENT NOTIFICATION permission in the Database Engine.
      • VIEW ANY DATABASE server-level permission.
      • SQL Server setup creates a SQL WMI namespace and grants read permission to the SQL Server Agent service-SID.

 

 

References

Microsoft Developer Network

  1. Database Engine > Database Engine Features and Tasks  > Security Center for SQL Server Database Engine and Azure SQL Database
    Link
  2. Database Engine Features and Tasks > Database Engine Instances (SQL Server)  > Manage the Database Engine Services
    Link

 

Blogs

  1. Database Dave
    • Dont delete ##MS_PolicyEventProcessingLogin## or ##MS_PolicyTsqlExecutionLogin##
      Link

SQL Server – Review LOB Data – File groups & Allocated Storage

Background

Wanted a quick look at our LOB data.

The filegroups they are stored in and their allocated storage.

Code




-- To see lob_data filegroups
SELECT 
		  objectName 
			=  
				 tblS.[name]
				+ '.'
				+ OBJECT_NAME(tblO.object_id)

		, [indexName]
			= tblSI.[name]

		, [indexType]
			= tblSI.[type_desc]

		, fileGroupName 
			= FILEGROUP_NAME(tblAU.data_space_id)

		, tblAU.[type_desc]

		, [totalMB]
			= (tblAU.total_pages* 8 ) / 1024

		, [usedMB]
			= (tblAU.used_pages* 8 ) / 1024

FROM sys.objects tblO

INNER JOIN sys.schemas tblS

		on tblO.schema_id = tblS.schema_id

INNER JOIN sys.indexes tblSI

		on tblO.object_id = tblSI.object_id


INNER JOIN sys.partitions tblP

		on  tblSI.object_id = tblP.object_id
		and tblSI.index_id = tblP.index_id

JOIN sys.allocation_units tblAU
		on tblP.partition_id = tblAU.container_id

/*
	Object Type is User Table
*/
WHERE tblO.[type] = 'U'

/*
	Allocation Type is LOB Data
*/
AND   tblAU.[type_desc] = 'LOB_DATA'


SQL Server – Memory Allocated/Unallocated per Database

Background

Still on the trail of high TempDB Allocation in our Memory.

 

Glossary

Here is a quick outline of terms that we will cover.

DMV Description Note
 sys.dm_os_buffer_descriptors Returns information about all the data pages that are currently in the SQL Server buffer pool.  The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.
sys.allocation_units Contains a row for each allocation unit in the database When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases.

 

 

Code


; with cte
(
	  [database_id]
	, [cnt]
	, [allocatedMB]
	, [isCleanMB]
	, [isDirtyMB]
	, [unallocatedMB]
	, [%unallocated]

)
as
(

	select
	          tblDOSBD.[database_id]

			, [cnt]
				= ( count(*))

			, [allocatedMB]
				= (
					sum
						(
							case
								when ( tblSAU.[allocation_unit_id] is not null ) then 1
								else 0
							end
						) * 8
				  )	
				  / 1024

			, [isCleanMB]
				= (
					sum
						(
							case
								when 
									( 
										    ( tblSAU.[allocation_unit_id] is not null ) 
										and ( tblDOSBD.is_modified = 0 )
									) then 1
								else 0
							end
						) * 8
				  )	
				  / 1024

			, [isDirtyMB]
				= (
					sum
						(
							case
								when 
									( 
										    ( tblSAU.[allocation_unit_id] is not null ) 
										and ( tblDOSBD.is_modified = 1 )
									) then 1
								else 0
							end
						) * 8
				  )	
				  / 1024
				  	
			, [unallocatedMB]
				= (
					sum
					(
						case
							when ( tblSAU.[allocation_unit_id] is null ) then 1
							else 0
						end
					) * 8

				  ) / 1024		

			, [%unallocated]
				= cast
					(
						sum
						(
							case
								when ( tblSAU.[allocation_unit_id] is null ) then 1
								else 0
							end
						) * 100.00
						/
						count(*)
						as decimal(10,2)
					)

	from  sys.dm_os_buffer_descriptors tblDOSBD

	left outer join sys.allocation_units tblSAU

		on tblDOSBD.allocation_unit_id = tblSAU.[allocation_unit_id]

	group by

		tblDOSBD.[database_id]
)
select 

	  [database]
		= case
				when ( cte.database_id = 32767) then 'Resource DB'
				else db_name(cte.[database_id])
          end

	, cte.[allocatedMB]

	, cte.[isCleanMB]
	, cte.[isDirtyMB]	
	
	, cte.[unallocatedMB]

	, cte.[%unallocated]

from   cte

order by
      	 cte.[unallocatedMB] desc

 

Output

memroyallocatedandunallocated-ayso-prod-20161205_1037am

 

Summary

In follow-up postings we will delve more into why we have such a high percentile for unallocated data compared to Allocated data.

Again, keep in mind that unallocated seems to mean that the data is in memory, but corresponding entries do not seem to be present on disk.

 

SQL Server – Script – Login

Preface

Need a script that allows me to script SQL Server Logins.

Not at the database level just yet, just at the SQL Server Instance Level.

Metadata

The relevant views are under the following section :-

System Views (Transact-SQL) \ Catalog Views (Transact-SQL)  \ Security Catalog Views (Transact-SQL)

And, here they are:

System View Description Link
sys.server_principals Contains a row for every server-level principal. Link
sys.server_permissions Returns one row for each server-level permission. Link
sys.sql_logins Returns one row for every SQL Server authentication login. Link 
sys.server_role_members Returns one row for each member of each fixed and user-defined server role. Link 

 

 

Code

Reference

Our code will be based on Bill Graziano’s published here:

Scripting out SQL Server Logins
Link

 

Function – Scaler – [dbo].[fn_hexadecimal]

Documentation

INFO: Converting Binary Data to Hexadecimal String
Link

Code


USE [master]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[fn_hexadecimal]') is null
begin

	exec('create function [dbo].[fn_hexadecimal]() returns varchar(256) as begin return 1/0 end ')

end
go

ALTER FUNCTION [dbo].[fn_hexadecimal] 
(
    -- Add the parameters for the function here
     @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF' 
    WHILE (@i <= @length) 
    BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END
    return @charvalue

END
GO

grant execute on [dbo].[fn_hexadecimal]  to [public]
go

 

SP – dbo.sp_ScriptServerPrincipal



use master
go


if object_id('dbo.sp_ScriptServerPrincipal') is null
begin

	exec('create procedure [dbo].[sp_ScriptServerPrincipal] as ')

end
go

alter procedure [dbo].[sp_ScriptServerPrincipal] 
(
	   @serverPrincipal			sysname = null
     , @overwritePassword		bit = 1
)
as
begin

	/*

		2016-10-10 dadeniji

			To Avoid:
				Msg 33020, Level 16, State 1, Line 22
				A HASHED password cannot be set for a login that has CHECK_POLICY turned on.
			
			Set:
				CHECK_POLICY=OFF


		2016-10-10 dadeniji

			To Avoid:
	
				Msg 15122, Level 16, State 1, Line 1
				The CHECK_EXPIRATION option cannot be used when CHECK_POLICY is OFF.

			Set:
				CHECK_EXPIRATION = OFF
	*/
	set nocount on;

	PRINT '-----------------------------------------------------------------------------'
	PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
	PRINT '-- SQL Instance :- ' + cast(serverproperty('servername') as sysname)
	PRINT '-----------------------------------------------------------------------------'

	declare @FORMAT_LOGIN_WINDOWS  nvarchar(4000)
	declare @FORMAT_LOGIN_WINDOWS_HEADER_CREATE varchar(600);
	declare @FORMAT_LOGIN_WINDOWS_HEADER_ALTER  varchar(600);
	declare @FORMAT_LOGIN_WINDOWS_BODY          varchar(600);

	declare @FORMAT_LOGIN_SQL			nvarchar(4000)
	declare @FORMAT_LOGIN_SQL_HEADER_CREATE varchar(600)
	declare @FORMAT_LOGIN_SQL_HEADER_ALTER  varchar(600)
	declare @FORMAT_LOGIN_SQL_BODY			nvarchar(4000)
	declare @FORMAT_LOGIN_SQL_PASSWORD		nvarchar(600)
	declare @FORMAT_LOGIN_SQL_SID			varchar(600)
	declare @FORMAT_LOGIN_DISABLED			nvarchar(4000)
	declare @FORMAT_LOGIN_CONNECT_SQL		nvarchar(4000)
	declare @FORMAT_ROLE_SQL				nvarchar(4000)

	declare @CHAR_TAB						char(1)

	declare @PERMISSION_TYPE_CONNECT_SQL    varchar(4)

	declare @tblLogin TABLE
	(
		  [id]		   int not null identity(1,1)
		, [principalID]  int not null
		, [name]	   sysname
		, [password]   varchar(256)
		, [type]	   char(1) not null
		, [database]   sysname
		, [language]   sysname	
		, [sid]		   varchar(256)

		, [isDisabled] bit
		, [isDisabledLiteral]
				as case [isDisabled]
						when 1 then ' DISABLE '
						else ' ENABLE '
				  end
		, [permissionStateCONNECTSQL] varchar(30)

		, [isExpirationChecked] bit

		, [isExpirationCheckedLiteral]
				as case [isExpirationChecked]
						when 0 then ' OFF '
						else ' ON '
				  end

		, [isPolicyChecked] bit
		, [isPolicyCheckedLiteral]
				as case [isPolicyChecked]
						when 0 then ' OFF '
						else ' ON '
				  end

	)

	declare @tblServerRole TABLE
	(
		  [id]    int not null identity(1,1)
		, [login] sysname not null
		, [role]  sysname not null
	)

	declare @sql							nvarchar(4000)
	declare @sqlLoginDisabled				nvarchar(4000)
	declare @sqlLoginPermissionToDBEngine	nvarchar(4000)

	declare @id int
	declare @idMax int

	declare @name		sysname
	declare @password   varchar(256)
	declare @type		char(1)
	declare @database	sysname
	declare @language	sysname
	declare @sid		sysname

	declare @isDisabled	bit
	declare @isDisabledLiteral varchar(60)

	declare @permissionStateCONNECTSQL varchar(60)

	declare @isExpirationChecked		bit
	declare @isExpirationCheckedLiteral varchar(30)

	declare @isPolicyChecked	    bit
	declare @isPolicyCheckedLiteral varchar(30)

	declare @role		sysname

	declare @clauseBegin     varchar(30)
	declare @clauseEnd	     varchar(30)
	declare @clauseElse      varchar(30) 
	declare @clauseWith	     varchar(30)
	declare @clauseComma	 varchar(30)

	set @CHAR_TAB = char(9)

	set @PERMISSION_TYPE_CONNECT_SQL = 'COSQ'

	set @clauseBegin = ' BEGIN ';
	set @clauseEnd = ' END ';
	set @clauseElse = ' ELSE ';
	set @clauseWith = ' WITH ';
	set @clauseComma = ' , ';

	set @FORMAT_LOGIN_WINDOWS_HEADER_CREATE 
		=  'CREATE LOGIN [@varLogin] from WINDOWS'
			 

	set @FORMAT_LOGIN_WINDOWS_HEADER_ALTER 
		=  'ALTER LOGIN [@varLogin]'
			 
	set @FORMAT_LOGIN_WINDOWS_BODY
		= 	 
		    + @CHAR_TAB + @clauseWith
			+ @CHAR_TAB + '      DEFAULT_DATABASE = [@varDatabase] '
			+ @CHAR_TAB + '    , DEFAULT_LANGUAGE = [@varLanguage] '
			;


	set @FORMAT_LOGIN_WINDOWS 
			= 'IF EXISTS (SELECT * FROM master.sys.server_principals where [name] = ''@varLogin'') '
				+ @clauseBegin
				+ @CHAR_TAB + @FORMAT_LOGIN_WINDOWS_HEADER_ALTER
				+ @CHAR_TAB + @FORMAT_LOGIN_WINDOWS_BODY
				+ @clauseEnd
				+ @clauseElse
				+ @clauseBegin
				+ @CHAR_TAB + @FORMAT_LOGIN_WINDOWS_HEADER_CREATE
				+ @CHAR_TAB + @FORMAT_LOGIN_WINDOWS_BODY
				+ @clauseEnd
				+ @CHAR_TAB + ' -- WINDOWS '


	set @FORMAT_LOGIN_SQL_HEADER_CREATE 
		=  'CREATE LOGIN [@varLogin]'
			 

	set @FORMAT_LOGIN_SQL_HEADER_ALTER 
		=  'ALTER LOGIN [@varLogin]'
			 
	set @FORMAT_LOGIN_SQL_BODY
		= 	 
		    + @CHAR_TAB + ' '
			+ @CHAR_TAB + '      DEFAULT_DATABASE = [@varDatabase] '
			+ @CHAR_TAB + '    , DEFAULT_LANGUAGE = [@varLanguage] '
			+ @CHAR_TAB + '    , CHECK_EXPIRATION = @varcheckExpiration '
			+ @CHAR_TAB + '    , CHECK_POLICY = @varcheckPolicy '
	


	set @FORMAT_LOGIN_SQL_PASSWORD
	             = @CHAR_TAB
	               + ' PASSWORD = @varPassword  HASHED '
				   ;


	set @FORMAT_LOGIN_SQL_SID
	             = @CHAR_TAB
	               + ' , SID = @varSID '
				   ;

	set @FORMAT_LOGIN_SQL 
			= 'IF EXISTS (SELECT * FROM master.sys.sql_logins where [name] = ''@varLogin'') '
				+ @clauseBegin
			    + @FORMAT_LOGIN_SQL_HEADER_ALTER
				+ @clauseWith 
				+ @FORMAT_LOGIN_SQL_BODY
				+ case @overwritePassword
						when 1 then ' , ' + @FORMAT_LOGIN_SQL_PASSWORD
						else ''
                  end
				+ @clauseEnd
				+ @clauseElse
				+ @clauseBegin
				+ @FORMAT_LOGIN_SQL_HEADER_CREATE
				+ @clauseWith 
				+ @FORMAT_LOGIN_SQL_PASSWORD
				+ @clauseComma
				+ @FORMAT_LOGIN_SQL_BODY
			    + @FORMAT_LOGIN_SQL_SID
				+ @clauseEnd
				+ @CHAR_TAB + ' -- SQL '


	set @FORMAT_LOGIN_DISABLED
			= 'ALTER LOGIN [@varLogin] [@varEnableOrDisable]; ';

	set @FORMAT_LOGIN_CONNECT_SQL
			= '[@varGrantOrDeny] CONNECT SQL TO [@varLogin]; '

	set @FORMAT_ROLE_SQL
			= 'EXEC master..sp_addsrvrolemember @loginame = [@varLOGIN], @rolename = [@varROLENAME]; '

			

	insert into @tblLogin
	(
		  [principalID]
		, [name]
		, [password]
		, [type]
		, [database]
		, [language]
		, [isDisabled]
		, [permissionStateCONNECTSQL]
		, [sid]
		, [isExpirationChecked]
		, [isPolicyChecked]
	)
	SELECT 
			  tblSQL.[principal_id]
			, tblSQL.[name]
			, [password]
				= case
					when (tblSSL.password_hash is null) then null
					--else [master].[dbo].[fn_hexadecimal](tblSSL.password_hash)
					else [master].[dbo].[fn_varbintohexstr]
					        (tblSSL.password_hash)
                  end
			, tblSQL.[type]
			, tblSQL.[default_database_name]
			, tblSQL.[default_language_name]
			, tblSQL.is_disabled
			, [permissionStateCONNECTSQL]
				= isNull(tblSSP.[state_desc], 'GRANT')
			, [sid]
				= case 
				     when (tblSQL.[sid] is null) then null
					 --else [master].dbo.[fn_hexadecimal](tblSQL.[sid])
					 --else [master].[sys].[fn_varbintohexstring]
					 else [master].[dbo].[fn_varbintohexstr]
					        (tblSQL.[sid])
                  end 
			, tblSSL.is_expiration_checked
			, tblSSL.is_policy_checked

	FROM   [master].[sys].[server_principals] tblSQL

	LEFT OUTER JOIN [master].[sys].[sql_logins] tblSSL

		on tblSQL.principal_id = tblSSL.principal_id

	LEFT OUTER JOIN [master].sys.server_permissions AS tblSSP
		on  tblSSP.grantee_principal_id = tblSQL.[principal_id]
		and tblSSP.[type] = @PERMISSION_TYPE_CONNECT_SQL

	where  tblSQL.[type_desc] in
				(
					  'SQL_LOGIN'
					, 'WINDOWS_LOGIN'
					, 'WINDOWS_GROUP'
				)
		
	and   tblSQL.[name] not in 
					(	
						  'sa'
						, 'guest'
					)

	--and   tblSQL.[name] not like '##%'

	and   tblSQL.[type] in ('U', 'G', 'S', 'C', 'K') 
	AND   tblSQL.principal_id not between 101 and 255 
	--AND   tblSQL.[name] <> N'##MS_AgentSigningCertificate##'

	and   tblSQL.[name] = isNull(@serverPrincipal, tblSQL.[name])


	insert into @tblServerRole
	(
		  [login]
		, [role] 
	)
	select 
			  [login] = l.[name]
			, [role] = r.[name]

	from master.sys.server_role_members rm

	join master.sys.server_principals r 
		on r.principal_id = rm.role_principal_id
	
	join master.sys.server_principals l 
		on l.principal_id = rm.member_principal_id

	where l.[name] not in ('sa')
	AND   l.[name] not like 'BUILTIN%'
	and   l.[NAME] not like 'NT AUTHORITY%'
	and   l.[name] not like '%\SQLServer%'

	and   l.[name] = isNull(@serverPrincipal, l.[name])

	/*
		Process Logins
	*/
	set @id = 1
	set @idMax = ( select max([id]) from @tblLogin)

	print '--Logins'
	print '------'
	while (@id <= @idMax)
	begin

		set @sql = null
		set @sqlLoginDisabled = null
		set @sqlLoginPermissionToDBEngine = null;

		select
				  @name       = [name]
				, @password   = isNull([password], '')
				, @type       = isNull([type], '')
				, @database   = isNull([database], '')
				, @language   = isNull([language], '')

				, @isDisabled = isNull(isDisabled, 0)
				, @isDisabledLiteral = isNull(isDisabledLiteral, 'ENABLE')

				, @permissionStateCONNECTSQL
					  = isNull([permissionStateCONNECTSQL], 'GRANT')


				, @sid		  = isNull([sid], '')

				, @isExpirationChecked 
							 = isNull(isExpirationChecked, '')

				, @isExpirationCheckedLiteral
							= isNull([isExpirationCheckedLiteral], 'OFF')

				, @isPolicyChecked 
							= isNull(isPolicyChecked, '')

				, @isPolicyCheckedLiteral
							= isNull([isPolicyCheckedLiteral], 'OFF')

		from    @tblLogin tblL

		where   tblL.[id] = @id

		if (@overwritePassword = 1)
		begin

			set @isPolicyCheckedLiteral = 'OFF'
			set @isExpirationCheckedLiteral = 'OFF'

		end	

		if (
				( @type in ('U', 'G')) 
		   )
		begin

			set @sql = @FORMAT_LOGIN_WINDOWS 


		end		   	
		else if 
			(
				( @type in ('S')) 
			)
		begin

			set @sql = @FORMAT_LOGIN_SQL

		end		   	

		set @sql = replace(@sql, '@varLogin', @name) 
		set @sql = replace(@sql, '@varPassword', @password) 
		set @sql = replace(@sql, '@varDatabase', @database) 
		set @sql = replace(@sql, '@varLanguage', @language) 
		set @sql = replace(@sql, '@varSID', @sid) 
		set @sql = replace(@sql, '@varcheckExpiration', @isExpirationCheckedLiteral) 

		set @sql = replace(@sql, '@varcheckPolicy', @isPolicyCheckedLiteral) 

		--Login Disabled
		set @sqlLoginDisabled = @FORMAT_LOGIN_DISABLED;
		set @sqlLoginDisabled
					= replace(@sqlLoginDisabled, '@varLogin', @name)
		set @sqlLoginDisabled
					= replace
						(
							  @sqlLoginDisabled
							, '[@varEnableOrDisable]'
							, @isDisabledLiteral
						)

		--Login Grant or Deny
		set @sqlLoginPermissionToDBEngine = @FORMAT_LOGIN_CONNECT_SQL

		set @sqlLoginPermissionToDBEngine
					= replace
						(
							  @sqlLoginPermissionToDBEngine
							, '[@varGrantOrDeny]'
							, @permissionStateCONNECTSQL
						)

		set @sqlLoginPermissionToDBEngine
					= replace(@sqlLoginPermissionToDBEngine, '@varLogin', @name)



		print isNull(@sql, '---')

		print @CHAR_TAB + isNull(@sqlLoginDisabled, '---Login Disabled')

		print @CHAR_TAB + isNull(@sqlLoginPermissionToDBEngine, '-- Login Deny')
							
		set @id = @id + 1

	end



	/*
		Process Server Roles
	*/
	print ''; print ''
	print '--Server Roles'
	print '------------'
	set @id = 1
	set @idMax = ( select max([id]) from @tblServerRole)

	while (@id <= @idMax)
	begin

		set @sql = null
		set @sqlLoginDisabled = null

		select
				  @name = [login]
				, @role = [role]
		from    @tblServerRole
		where   [id] = @id


		set @sql = @FORMAT_ROLE_SQL

		set @sql = replace(@sql, '@varLOGIN', @name) 
		set @sql = replace(@sql, '@varROLENAME', @role) 

		print isNull(@sql, '---')
			
		set @id = @id + 1

	end


end
go

EXEC sys.sp_MS_marksystemobject '[dbo].[sp_ScriptServerPrincipal]'
go


Invoke


declare @serverPrincipal sysname

set @serverPrincipal = 'compass'

exec [dbo].[sp_ScriptServerPrincipal] 
	@serverPrincipal = @serverPrincipal


Output

sqllogin