SQL Server T SQL Code Standards

 

USE [dbname]

GO

/****** Object:  StoredProcedure [dbo].[SPInsertCollectionCallDetails]    Script Date: 02/23/2016 11:14:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author:        <Hari Ere>

— Create date: <01/17/2014>

— Description:   This procedure input the package process steps

 

— Excute SP

/*USE [dbname]

GO

DECLARE     @return_value int

EXEC  @return_value = dbo.spinsert

@id = 1,

@ProcessStepInfo = NULL,

@Status = NULL

SELECT      ‘Return Value’ = @return_value

GO

*/–Edits History:

— =============================================

BEGIN TRY

— code here

SELECT  ‘hello world’

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT  @ErrorMessage = ERROR_MESSAGE() ,

@ErrorSeverity = ERROR_SEVERITY() ,

@ErrorState = ERROR_STATE();

 

EXEC sp_send_dbmail @profile_name = ‘Mail_ Profile’,

@recipients = ‘myemail@libertyutilities.com’,

@subject = ‘Error from procedreuname ‘, @body = @ErrorMessage;

END CATCH;

GO


If code required in Transaction, please make sure SET XACT_ABORT ON(rolls back the current transaction when a Transact-SQL statement raises a run-time error )

SET XACT_ABORT ON;

GO

BEGIN TRANSACTION;

code

COMMIT TRANSACTION;

GO

 

 

 

TSQL best practices.

 

  • To improve code readability, use proper indention for the statements.
  • To understand code easily, please add comments with each begging of the logic.
  • Use CAPS for all Sql Server Keywords ( Ex: SELECT,ORDER BY).
  • Use SET NOCOUNT ON option at the top of procedure to avoid messages.
  • Do not use SELECT * all columns, instead specific column names in TSQL.
  • Write object names with fully qualified names.(Databasename.schema.objectname).
  • SYNONYMs provide a layer of abstraction over the referenced object with a simplified alias as a same server resident object.
  • Declare variables in required size, which eliminates consuming memory buffers.
  • Never create stored procedure name with SP, SP is reserved for system SQL server.
  • Try to avoid using cursors, use While loops.
  • Create views if the quires are complex.
  • Avoid sub quires and use CTE, CTE’S are reside on memory for faster access.
  • Use ORDER BY,DISTINCT,TOP only when required.
  • Avoid the temp table in the stored procedure. Stored procedures are usually use a cached execution plan to increase the performance. When you use the temp table it will do the compilation every time.
  • Use the sp_executesql stored procedure instead of the EXECUTE statement.(ex: EXECUTE sp_executesql @Query)
  • Keep the transaction as short as possible, with SET XACT_ABORT ON. This will roll back when error raise.
  • USE TRY CATCH BLOCK to trap the errors, and send alerts.
  • Use table variables inside the SP, for small datasets. If the data sets are larger, use temp tables and add index for better performance.
  • Create backup tables into a separate database, it will help DBA’s for cleaning.
  • To avoid deadlocks Always access tables in the same order in all your Stored Procedures and triggers consistently, keep your transactions as short as possible.

Never, ever wait for user input in the middle of a transaction.

  • Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime.
  • Dropping a temp table
  • Ex:

IF OBJECT_ID(‘tempdb..#CpuCounters1’) IS NOT NULL

    DROP TABLE #CpuCounters1

GO

 

 

 

CREATE OR ALTER Supported in Service Pack 1 of SQL Server 2016

New CREATE OR ALTER support makes it easier to modify and deploy objects like Stored Procedures, Triggers, User–Defined Functions, and Views. This feature is introduced in SQL Server 2016 SP1.

 

OLD syntax Example

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = ‘spMyProcedure’ AND TYPE = ‘P’)
BEGIN
DROP PROCEDURE spMyProcedure
END
GO
CREATE PROCEDURE spMyProcedure (@Name VARCHAR(100))
AS

DECLARE @MyVar VARCHAR(10)
SET @MyVar = ‘Geek!’
BEGIN
SELECT @Name +’ Is a ‘ + @MyVar
END
GO

New syntax Example

 

CREATE OR ALTER PROCEDURE spMyProcedure (@Name VARCHAR(100))
AS

DECLARE @MyVar VARCHAR(10)
SET @MyVar = 'Geek!!!!'
BEGIN
 SELECT @Name +' Is a ' + @MyVar
END
GO

Sql Server Upgrade Plan

there are two types of Upgrade strategy

In-Place Upgrading.

Side-by-side Upgrading.

In-Place Upgrade:  An in-place approach allows to upgrade existing SQL Server instance to a higher version. the main benfit of this approach is you can still use existing hardware,instance name. there is no worry on application connection string configuration, logins,users,SQL agent jobs creation.

This approach requires downtime as SQL server instance is offline during upgrade, in place upgrade is not supported for all components and if any issue occur complex and manual rollback strategy .

 

Side-by-Side Upgrade : This approach quite simple as name suggest, a new sql server instance can be installed on the same server or different new sever. which means server can have SQL2005,2008,2008R2,2012,2014 on same box. one instance is default and others are named instance.

 

This approach has more granular control over upgrade component-level.

you can run sql server side by side for testing.

you can have rollback strategy  because the the original system is still intact.

 

 

 Pre-Upgrade Steps:

performing an upgrade can be a complex and critical process .to mitigating the risk of a failed upgrade or unexpected post-upgrade behavior, a proper research can be perform on instance using available tools.

SQL Server Upgrade Tools:  SQL server 2014 Upgrade Advisory is a free download available as part of MS 2014 and also available in install media.

the purpose of this tool is to identify known upgrade issues and provide workarounds for fixes.(https://msdn.microsoft.com/en-us/library/ee210480(v=sql.120).aspx)

identify backward compatibility and discontinued features.

perform impact analysis on applications and inform to the application owners, this is very critical steps. some vendor applications may not be compatible , this is required further analysis on workarounds

Upgrade Execution Process:

Document each step of upgrade process, This  includes upgrade steps,document locations,server IP,Resources information,Application owners.

Attache tests results on Dress rehearsals and analyze the ares of issues identified during upgrading on test servers.

communicate with the vendors if any alarms are raised.

Post-Upgrade Process: 

Document all post upgrade tasks, user creation,user access, connection string configurations with third party applications,backups and maintenance plans,job scheduling etc.

sanity checks with QA before system handover to business for validations.

 

once the above process is completed and tested on development/QA/UAT servers and time to go for Production upgrade in a smooth way.

i did not touch on hardware selection inthis article as name implies it is only for upgrade steps.

 

 

 

 

 

 

 

 

 

 

SQL Server Installation Planing,configurations and checks

Base Line of current work loads.

Number of user,logins,connections at a given point.

Estimated growth in work load.

Minimum software and Hardware Requirements.

Storage system sizing and I/O requirements.

Choosing SQL Server editions/License.

Service Accounts Selection.

Password Policy.

Limit using of SA during Upgrades with third party vendors.

Hardening systems with company security policy.

Isolate drives for system databases,Temp db and user data and log files for better performance.

The number of allocated data files in the TempDB database depends on the logical or physical CPUs number on your server.

Configure Min and Max SQL Server Memory, initially sql server acquires minimum memory.  as work load requests from users it will consume memory to max limit.

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

define Databases default paths.

Configure MAX Degree of parallelism according to your work needs, please refer Microsoft Best Practices. (https://support.microsoft.com/en-ca/kb/2806535)

configure Sql Server error logs,

Configure Database Mail with public and private profiles.

Configure SQL Server agent operator for Alerts purpose.

Create default alerts for severities (16-25).

Database maintenance,backup and recovery steps.

Offsite backup plans.

Minimum up time and SLA’s.

Store SA/Service accounts password in vaults/key pass.

Create complete document if High available solution is required (Cluster,Always on groups, Log shipping).

Disaster Recovery Strategy.

Configure Daily health checks Sql server Systems. If required buy third party tools.

Configure Performance Monitoring tools. Store DMV’s in a single location of network share for peridoic helath checks by DBA’s

Create scripts for Daily refresh on DEv and QA environments.

Secure Backups by enforcing Encryption.

Develop plan on periodic backup validationand admin passwords rest

install Service packs and CU’s

for more information please refer to the Microsoft

https://msdn.microsoft.com/en-us/library/bb500442(v=sql.120).aspx

 

 

SQL Server System Databases

SQL Server system Databases are very critical. Lets talk one at a time.

Resource Database.

Resource database is read-only database and that contains all the system objects. like sys.objects…

it does not contain any user data or user meta data. Resource database makes u[grading to a new version much easier and faster procedure. Resource database is located under

<drive>:\programfiles\Microsoft SQL server\MSSQL\<version>.<instance_name>\mssql\binn  (mssqlsystemresource.mdf)

SQL Server cannot backup and restore resource database. you can perform only file based copy. Resource database should be modified only with the direction of Microsoft support.

 

The id of the resource database is 32767.

 

/*Resource Database*/

–To determine the version of the resource database

SELECT SERVERPROPERTY(‘ResourceVersion’)
GO;
— To determine twhen the resource databse was last updated

SELECT SERVERPROPERTY(‘ResourceLastUpdateDateTime’)
GO;
— To access SQL definations of system objects

SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.objects’))

 

master database:

the master database contains information about your databases logins,configurations,file locations and information about the instance.

main difference between master and resource db’s are. master database contains information about the instance and resource contains information about schema and stored procedures that needed to run your instance.

do not create any objects in master database.

temp Database:

temp database is used to store user temporary objects needed by the database engine and the rwo version. indexing sorting.

model database: 

model database is a system database that creates a template when ever sql server creates a database. but this will not make an entry when database is restored or attached.

 

msdb databse:

msdb datasbe contains inforation  on sql agent jobs,agent job history,log shipping,SSIS and backup restore information., job alerts and policies.

 

master,msdb,model database should be backup regularly.

 

 

 

 

 

DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2

Microsoft released SQL Server 2014 Service Pack 2, which contains a new command: DBCC CLONEDATABASE. This new command creates a clone of a database’s schema and statistics (not the data of tables). Since it has the statistics, it is useful for troubleshooting, investigating, and diagnosing performance issues.

This will eliminate your production database to diagnose query performance tuning on problematic procs or TSQL.

 

in the following example, I have cloned adventureworks 2014.

Syntax: DBCC CLONEDATABASE (source_database_name, target_database_name)

dbcc clonedatabase([AdventureWorks2014],[AdventureWorks2014_Clone])

for more information please read Microsoft KB article.

https://support.microsoft.com/en-us/kb/3177838

 

dbcc clone

Parse Sql Extended event for Login Audits

SELECT
CONVERT(VARCHAR(19),DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
xevents.event_data.value(‘(event/@timestamp)[1]’,’datetime2′)),120) AS [timestamp] ,
–xevents.event_data.value(‘(event/action[@name=”session_id”]/value)[1]’, ‘int’) AS [session_id],
DB_NAME(xevents.event_data.value(‘(event/action[@name=”database_id”]/value)[1]’, ‘int’)) AS [database_id],
–xevents.event_data.value(‘(event/action[@name=”server_principal_name”]/value)[1]’, ‘nvarchar(max)’) AS [server_principal_name],
xevents.event_data.value(‘(event/action[@name=”client_app_name”]/value)[1]’, ‘nvarchar(128)’) AS [client_app_name],
xevents.event_data.value(‘(event/action[@name=”client_hostname”]/value)[1]’, ‘nvarchar(max)’) AS [client_hostname],
xevents.event_data.value(‘(event/action[@name=”nt_username”]/value)[1]’, ‘nvarchar(128)’) AS [nt_username],
xevents.event_data.value(‘(event/action[@name=”username”]/value)[1]’, ‘nvarchar(max)’) AS [username]
FROM sys.fn_xe_file_target_read_file
(‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\Log\LoginAccounts_*.xel’,
NULL,null, null) f
CROSS APPLY (select CAST(event_data as XML) as event_data) as xevents
ORDER BY DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
xevents.event_data.value(‘(event/@timestamp)[1]’,’datetime2′)) DESC

SELECT event_time,action_id,statement,database_name,server_principal_name
FROM fn_get_audit_file( ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\Log\LoginAccounts_*.xel’ , DEFAULT , DEFAULT);