Database Snapshot and InMemory Tables

Database Snapshots are very useful when you need to test some quick changes without having to take backup and, also, they provide way to access the mirrored database to serve READ-ONLY queries.

However, if you have InMemory FileGroup, which is required for InMemory Tables, you cannot use Database Snapshot. I tired this on SQL 2016 RTM version.

USE [Master]
Go
CREATE DATABASE [InMemoryDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'InMemoryDB', FILENAME = N'C:\Temp\InMemoryDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
FILEGROUP [InMemoryDB] CONTAINS MEMORY_OPTIMIZED_DATA
( NAME = N'InMemoryFile', FILENAME = N'C:\Temp\InMemoryFile' )
LOG ON
( NAME = N'InMemoryDB_log', FILENAME = N'C:\Temp\InMemoryDB_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

--Creating Snapshot Errors Out
CREATE DATABASE InMemory_Snap ON
( NAME = InMemoryDB, FILENAME =
'C:\Temp\InMemory_Snap.ss' )
AS SNAPSHOT OF InMemoryDB;
GO

Below is the error you will get:

Capture

Cannot update primary key in InMemory Tables

InMemory Tables are introduced in SQL 2014 and they are lot improved in SQL 2016.

While the surface area has improved in SQL 2016 compared to 2014 such as Foreign key constraints can be defined between Inmemory Tables, Indexes can have NULL columns, table can be altered etc.

There are still some restrictions in what you can do with SQL 2016 InMemory Tables and one such thing is, modifying the primary key of the table. Typically, we should not be modifying the primary key of the table but if there is ever a need to do that, it cannot be done in SQL 2016 InMemory Tables.

USE [Master]
Go
CREATE DATABASE [InMemoryDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'InMemoryDB', FILENAME = N'C:\Temp\InMemoryDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
FILEGROUP [InMemoryDB] CONTAINS MEMORY_OPTIMIZED_DATA
( NAME = N'InMemoryFile', FILENAME = N'C:\Temp\InMemoryFile' )
LOG ON
( NAME = N'InMemoryDB_log', FILENAME = N'C:\Temp\InMemoryDB_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

USE InMemoryDB
Go
Create Table InMemoryTable(Sno int not null primary key nonclustered hash with (bucket_count=1000),sname varchar(20))
with (memory_optimized=ON,Durability=Schema_ONLY)

Insert into InMemoryTable
Values (1,'SomeName')

---Errors out
Update InMemoryTable set sno=2 where Sno=1
You will get error as below:
Capture

Date Range along with Week Number in SQL

I needed to get work week date range along with corresponding week number in sql and below is the TSQL code I came up with.
NOTE: I had to do this on SQL 2008, so, I could not use window functions available in SQL 2012 and later.

declare @StartDate date
declare @EndDate date
set @StartDate ='12/31/2015'
Set @EndDate='12/31/2016'

;with CTE as (Select datename(dw,dateadd(day,number,@StartDate)) as [Day],
cast(datename(week,dateadd(day,number,@StartDate)) as int) as [WeekNo],
dateadd(day,number,@StartDate) as [Date],number
from master.dbo.spt_values
where type='P' and number>0
and datename(dw,dateadd(day,number,@StartDate)) not in ('Saturday','Sunday')
and dateadd(day,number,@StartDate)<=@EndDate)

Select convert(varchar(20),min(Date),106)+ ' - ' +convert(varchar(20),max(Date),106) as [Date Range],
'Week '+cast(Weekno as varchar) as [WeekNo]
from Cte
group by Weekno
order by cast(Weekno as int)

Disk information using powershell

One of the things DBA’s need to make sure is that database server disks do not run out of space. So, the disk free space should be monitored, however, if you manage multiple servers, it is not always convenient to log into every server. The below powershell code will return the disk information including available free space.

$ServerList ="ServerA","ServerB"
foreach ($Server in $ServerList)
{
#Eliminates System Volume and DriveType=5 (Media Drive).
get-wmiobject "win32_volume" -computerName $Server | Where-Object {$_.SystemVolume -like "False" -and $_.DriveType -ne 5}|
Select-Object SystemName,Name,Label,Capacity,FreeSpace,BlockSize|Format-Table -AutoSize
}

Limitations on ColumnStore Indexes

ColumnStore index is a new type of index available in SQL 2012 and has been enhanced in SQL 2014 version. ColumnStore index stores the data column wise instead of a regular row based B tree format and hence, it has better ability to compresses the data. When it was first released in SQL 2012, it has several limitations and some of those have been addressed in later versions of SQL(SQL 2014).

Quick pointers on limitations on ColumnStore Indexes in SQL 2012 and 2014:

  • NonClustered columnstore does not require the table to have a clustered index.
  • In SQL 2014, Clustered ColumnStore index is the only index that can exist on the table.
  • In SQL 2014, Clustered ColumnStore Index is updateable. Data can be inserted,updated and deleted from the table.
  • In SQL 2012/14 – NonClustered ColumnStore Index are NOT updateable. One solution is, to disable the columnstore Index and do the data load and then re enable the columnstore index.
  • Varchar(max),nvarchar(max),xml, ntext,text,image etc are not suitable datatypes for Columnstore index.
  • Foreign Key, Unique constraints cannot be defined.
  • In SQL 2012, there is no clustered columnstore option only NonClustered ColumnStore index.
  • In SQL 2014, we can create clustered columnstore or NonClustered ColumnStore index. However, if the table has clustered columnstore index, we cannot create any other index.

I will add more points as I come across them.

Deny vs Grant – which one supersedes?

There can be situations where a database user is part of multiple groups and if one of the group the user is part of has deny permissions and the other group has grant permissions on a object – which permissions would go through?

In common sense, you would think deny should supersede the grant permissions, because you want to have preventive protection and SQL Server works in the same way. Deny will override the Grant permissions.

Below sample code will demonstrate the same:
--create a test login
USE Master
GO
CREATE LOGIN wsxcde1 with password ='wsxcde1'

--create a test database to play with
CREATE DATABASE TESTING
GO
USE Testing
GO

--Create sample table
Create Table Test(sno int)
Create table Test2(sno int)

--Create user for the logins and assign data reader role for the users.
CREATE USER [wsxcde1] FOR LOGIN [wsxcde1]
GO
EXEC sp_addrolemember N'db_datareader', N'wsxcde1'
GO

--Create a custom role and add the user to this role and Grant Read permissions to the role.
Create Role myCustomRole
GO
EXEC sp_addrolemember N'myCustomRole', N'wsxcde1'
GO
EXEC sp_addrolemember N'db_datareader', N'myCustomRole'
GO

--Deny select permissions on table test2 to the custom role
Deny select on test2 to mycustomrole

At this point, database user wsxcde1 has read permissions on the database directly as the user itself and indirectly through the database role. However, the user was denied permissions on table dbo.test2 explicitly for the custom role.

--Now check if select will go through
Execute as user ='wsxcde1'
Select * from dbo.test2
Select suser_name()
Revert

--Clean up
USE Master
GO
Drop database testing
Drop login wsxcde1

TSQL to get Actual & Estimated Execution Plan


Create Table myTable(sno int identity(1,1),sname varchar(20),scity varchar(30))
Insert into myTable values('SomeName','SomeCity')
Create NonClustered Index NC_sname on myTable(sname)
GO

--GETS THE ESTIMATED EXECUTION PLAN AND THE QUERY IS NOT EXECUTED
Set SHOWPLAN_XML ON
GO
Select SName from myTable WHERE SName='SomeName'
GO
Set SHOWPLAN_XML OFF
GO

--GETS THE ACTUAL EXECUTION PLAN AND THE QUERY IS EXECUTED
Set Statistics XML ON
Select SName from myTable WHERE SName='SomeName'
Set Statistics XML OFF
GO
Drop Table myTable

Query to find when last full backup was taken

The below query returns last time a full backup was taken on the database.

select database_name as [Database],a.name [BackupName],user_name as [BackupTakenBy],
backup_start_date,backup_finish_date,backup_size as [BackupSize],is_copy_only,
has_backup_checksums,b.physical_device_name as [BackupLocation]
from msdb.dbo.backupset a inner join msdb.dbo.backupmediafamily b on a.media_set_id=b.media_set_id
where backup_set_id in
(select max(backup_set_id) from msdb.dbo.backupset where type='D' and is_copy_only in (1,0)
group by database_name)

If you do not want to consider copy_only backups, replace “is_copy_only in (1,0)” with “is_copy_only in (0)”.

Maintenance Plan History

The below query returns the maintenance plan history along with the details on the tasks present in the plan. To log the actual command and task details,”log extended information” under Reporting and Logging option should be checked in the maintenance plan.
Select @@SERVERNAME [servername],
case when D.Succeeded=1 then 'Success' when D.succeeded=0 then 'Failed' End as Result,
A.name,B.subplan_name,D.line1,D.line2,D.line3,D.line4,
D.line5,D.start_time,D.end_time,D.command
From msdb.dbo.sysmaintplan_plans a inner join msdb.dbo.sysmaintplan_subplans b on a.id=b.plan_id
inner join msdb.dbo.sysmaintplan_log c on c.plan_id=b.plan_id and c.Subplan_id=b.subplan_id
inner join msdb.dbo.sysmaintplan_logdetail d on d.task_detail_id=c.task_detail_id
Order By D.start_time DESC