2017 | SansSQL

Sunday, June 25, 2017

SQL Coding Best Practices and Design Considerations

As the business demand the applications to be more flexible and user friendly, the data access layers become more critical. For the applications to be flexible and quick responsive, the database reads and writes should be at optimum performance levels leaving the developers and DBA's an mandatory option to following the coding standards and best practices.

Superior coding techniques and programming practices are hallmarks of a professional programmer. The bulk of programming consists of making a large number of small choices while attempting to solve a larger set of problems. How wisely those choices are made depends largely upon the programmer's skill and expertise.

The readability of source code has a direct impact on how well a developer comprehends a software system. Code maintainability refers to how easily that software system can be changed to add new features, modify existing features, fix bugs, or improve performance. Although readability and maintainability are the result of many factors, one particular facet of software development upon which all developers have an influence is coding technique. The easiest method to ensure that a team of developers will yield quality code is to establish a coding standard, which is then enforced at routine code reviews.

This post and the underlying presentation aims at the fundamentals of SQL Coding Best Practices and Design Considerations. To read further, download the copy of presentation from here.

Saturday, June 24, 2017

T-SQL to find Memory Used by Database and its Objects

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 and we use this for calculating the memory used by the Databases and its objects.

-- Memory Used by All Databases
 CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsedByAllDB's(MB)]
,CAST (COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsedByAllDB's(GB)]
FROM sys.dm_os_buffer_descriptors

-- Memory Used by Individual Databases
 CASE WHEN database_id = 32767 
      THEN 'ResourceDB' 
      ELSE DB_NAME(database_id) 
 END AS [DatabaseName]
,COUNT(1) AS [CachedPages]
,CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsed(MB)]
,CAST(COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsed(GB)]
,CASE WHEN is_modified = 1 
      THEN 'Dirty Page' 
      ELSE 'Clean Page' 
 END AS [PageState]
GROUP BY database_id

-- Memory Used by Objects within a Database. Change the <<database name>> to your desiged Database Name
USE <<database name>>
 CASE WHEN database_id = 32767 
      THEN 'ResourceDB' 
      ELSE DB_NAME(database_id) 
 END AS [DatabaseName],
,COUNT(1) AS [CachedPages]
,CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsed(MB)]
,CAST(COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsed(GB)]
,CASE WHEN is_modified = 1 
      THEN 'Dirty Page' 
      ELSE 'Clean Page' 
 END AS [PageState]
sys.dm_os_buffer_descriptors SBD INNER JOIN 
SO.name AS [ObjectName],
SO.type_desc AS [ObjectType],
SI.name AS [IndexName],
SI.type_desc AS [IndexType]
FROM sys.allocation_units SAU INNER JOIN sys.partitions SP
ON SAU.container_id = SP.partition_id INNER JOIN sys.objects SO
ON SP.object_id = SO.object_id INNER JOIN sys.indexes SI
ON SO.object_id = SI.object_id
WHERE SO.is_ms_shipped=0
) AS InnerData
ON SBD.allocation_unit_id = InnerData.allocation_unit_id
WHERE DB_NAME(SBD.database_id) = DB_NAME()
GROUP BY database_id

Monday, May 29, 2017

Find Isolation Level of transaction and Database

Find Isolation level of a transaction
SELECT  session_id AS SessionID, 
  program_name AS ProgramName,
  DB_NAME(database_id) AS DatabaseName, 
  CASE transaction_isolation_level 
   WHEN 0 THEN 'Unspecified' 
   WHEN 1 THEN 'ReadUncommitted' 
   WHEN 2 THEN 'ReadCommitted' 
   WHEN 3 THEN 'Repeatable' 
   WHEN 4 THEN 'Serializable' 
   WHEN 5 THEN 'Snapshot' 
  END AS Transaction_Isolation_Level
FROM sys.dm_exec_sessions

Changing the Transaction Isolation level
Using the below statement the isolation level of a transaction can be changed.

Isolation Level
  • Read uncommitted
  • Read committed
  • Repeatable read
  • Serializable
  • Read committed snapshot
  • Snapshot

Find Isolation level of a Database
USE <<Database Name>>
DECLARE @UserOptions TABLE ([Set Option] NVARCHAR(50), [Value] NVARCHAR(50))
SELECT * FROM @UserOptions WHERE [Set Option] = 'isolation level'

You can download the script from here.

Thursday, April 13, 2017

T-SQL to List all Permissions for a Given Role

This script lists all the permissions for a given role.

SELECT DB_NAME() AS DatabaseName
      ,DatabasePrincipals.name AS PrincipalName
      ,DatabasePrincipals.type_desc AS PrincipalType
      ,DatabasePrincipals2.name AS GrantedBy
      ,DatabasePermissions.permission_name AS Permission
      ,DatabasePermissions.state_desc AS StateDescription
      ,SCHEMA_NAME(SO.schema_id) AS SchemaName
      ,SO.Name AS ObjectName
      ,SO.type_desc AS ObjectType
  FROM sys.database_permissions DatabasePermissions LEFT JOIN sys.objects SO
    ON DatabasePermissions.major_id = so.object_id LEFT JOIN sys.database_principals DatabasePrincipals
    ON DatabasePermissions.grantee_principal_id = DatabasePrincipals.principal_id LEFT JOIN sys.database_principals DatabasePrincipals2
    ON DatabasePermissions.grantor_principal_id = DatabasePrincipals2.principal_id
WHERE DatabasePrincipals.name = 'Test' -- Change the Role Name

You can download the script from here.

Thursday, April 6, 2017

T-SQL to get Get Database Files Usage information

Here is an handy T-SQL script to get Database files usage Information
DECLARE @FreeSpaceThreshold int
SET @FreeSpaceThreshold = 20 -- Change this Threshold Value. Any number between 0 to 100

 ServerName nvarchar(100),
 DatabaseName nvarchar(100),
 LogicalFileName nvarchar(100),
 FileType nvarchar(10),
 FileLocation nvarchar(max),
 [FileSize(MB)] decimal (15,2),
 [UsedSpace(MB)] decimal (15,2),
 [FreeSpace(MB)] decimal (15,2),
 [UsedSpace(%)] decimal (15,2),
 [FreeSpace(%)] decimal (15,2)

EXEC sp_MSforeachdb 'Use [?];
    DB_NAME() AS DatabaseName,
    name AS LogicalFileName, 
    type_desc AS FileType, 
    physical_name AS FileLocation,
    CAST(size/128.0 AS decimal(15,2)) AS [FileSize(MB)],
    CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [UsedSpace(MB)],
    CAST(size/128.0 AS decimal(15,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [FreeSpace(MB)],
    CAST(((FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [UsedSpace(%)],
    CAST((((size/128.0) - FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [FreeSpace(%)]
FROM sys.database_files'

SELECT * FROM #GetDetails Where [FreeSpace(%)] < @FreeSpaceThreshold
DROP TABLE #GetDetails

Download this script from https://gallery.technet.microsoft.com/T-SQL-to-Get-Database-f16f60c7?redir=0

Sunday, March 12, 2017

T-SQL script to get Backup Details of a Database and the Sequence it was taken

Here is an handy script which give the details of the list of backup(s) taken of a database and the sequence it was taken.
DECLARE @db_name VARCHAR(100)
SELECT @db_name = '<DB Name>'

 BS.server_name AS [Server Name]
,BS.database_name AS [Database Name]
,BS.recovery_model AS [Recovery Model]
,BMF.physical_device_name [Location]
,(CAST(BS.backup_size / 1000000 AS INT)) AS [Size of Backup (MB)]
,CASE BS.[type] WHEN 'D' THEN 'Full'
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log'
  END AS [Type of Backup]
,BS.backup_start_date AS [Backup Date]
,BS.first_lsn AS [First LSN]
,BS.last_lsn AS [Last LSN]
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE BS.database_name = @db_name
ORDER BY backup_start_date DESC , backup_finish_date

Download the script from https://gallery.technet.microsoft.com/T-SQL-script-to-get-Backup-5a9b029b?redir=0

Monday, January 16, 2017

Kernel for SQL Database - Product Review - A guest Post by Andre Williams

Most of the organizations that handle large amount of data is very much dependent on MS SQL Server for their operations. However, an MS SQL database corruption, can cause a lot of troubles to the organization owing to the criticality of the data contained in the database. Fortunately, many SQL Server recovery tools are available in the market. We will try to understand the features, working, and benefits & drawbacks of a popular, SQL recovery tool—the Kernel for SQL Database.

Product information
  • Name - Kernel for SQL Database
  • Description – MDF file recovery software (recovers SQL data from corrupt databases)
  • Developers – Lepide Software
Company information 
Lepide Software, the developers of Kernel range of products, is known for sophisticated IT security, management, and recovery solutions. Its Kernel range, designed and developed for small and mid-sized organizations offers cost-effective solutions for most of the day-to-day issues faced by IT organizations. These products help organizations in email migration, email recovery, data recovery, file repair, and more. The company has a loyal customer-base spanning across the world owing to its quality products and dedicated customer support.

Notable features of the software
  • Automatically detects the SQL version of the database
  • Fixes almost all types of SQL corruption issues
  • Recovers all type of SQL data—tables, triggers, views, stored procedures, constraints, indexes, rules, and user defined functions/data types
  • Works even with large SQL databases
  • Recovers data even when the SQL Server is live
  • Supports MS SQL 2000, 2005 and 2008, 2008R2 and 2012 versions
Kernel for SQL Database - versions available
  • Demo version
    The demo version, meant educate users on SQL recovery process, is completely free. But it is not equipped with the saving facility.
  • Licensed version
    Licensed version is the fully featured version of the software and is available on purchase only. It can save all the recovered data.
How to use Kernel for SQL Database to repair corrupt MDF files?
The hallmark of Kernel for SQL Database is the simplicity of its operation. The steps are simple, the interface is intuitive, and recovery is quick. The major steps of the SQL database recovery process are outlined here:
  1. Selecting MDF file for recovery
    The Open button (in the main page of the software) opens the Select SQL Database dialogue box. In this dialogue box, one can browse for the corrupt database. If users are sure about the SQL server version, the can select the version. Otherwise, they can use the auto detect option. Finally, the recovery process can be initiated by clicking the Recover button.
  2. Previewing the recovered data
    Once the software displays the recovered objects, the users can click an object on the left tree to preview its data.
  3. Saving the recovered data
    Two saving options are available—saving to SQL Server and saving to batch file. The first option allows to save directly to an SQL Server either through server authentication or through Windows authentication. In the Batch File option, the data is stored in script files in the desired location (which can be copied to a new database).
Benefits and drawbacks of Kernel for SQL Database
The important benefits of Kernel for SQL Database are:
  • Friendly interface
    The software interface itself provides information on how to execute the recovery process. So even non-technical users can perform recovery without any help. 
  • Preview facility
    Everything recovered can be viewed on the software’s preview pane before saving. Previewing helps users to know about the recoverability of the data.
  • Automatic detection of SQL Version
    If the users know the version of the SQL, they can select it. But if they do not know, the software can automatically detect the SQL version.
  • Multiple saving options
    The software saves the recovered MDF file files to SQL Server directly. In addition, it can save files in batch file, which users can copy to a new database when required.

The major drawback of Kernel for SQL Database is:
  • No saving option in the trial version
    The trial version of Kernel for SQL Database cannot save the data. With this version, users can only preview the data. 

Final verdict
Kernel for SQL Database is a great tool for SQL recovery. As we understand, users are happy with the product. Still the product can be improved in some areas. Also, it is good if Lepide can add some limited saving facility to the trial version. https://www.nucleustechnologies.com/sql-recovery.html 

Performance rating
Overall rating for the software is 4/5

About Andre Williams
Andre Williams, with more than three year's experience in SQL related technologies, contributes articles, blogs, and how-to tips regularly.

Social Media Profiles