Category Archives: TSQL

ACID Properties

In the context of database transaction processing, the acronym ACID refers to the four key properties of a transaction:

Atomicity
• Consistency
• Isolation
• Durability

Atomicity : All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.

Consistency :  data is in a consistent state when a transaction starts and when it ends.For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

Isolation :  The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized.For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.

Durability : After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.

Advertisements

Best Practices while creating Stored Procedures

1. SET NOCOUNT ON: Always use ‘SET NOCOUNT ON’ statement at the begening of your code inside the SP to reduce unnecessary network round trips.

2. PARAMETER SNIFFING: Do not use SP parameters directly within the WHERE clause of SQL statements. This may cause the case of Prameter Sniffing. To avod this assign the parameter values to local variables and then use them with SQL queries.

3. Use table variables: Try to use table variables instead of temporary tables inside the SP to cache small record sets whenever possible.

4. Use of Temp Tables: If you think that the temporary record set may exceed upto millions of rows, then try using temporary table instead of table variables. Also try creating indexes on temporary tables if required.

5. Avoid using CURSORS: This will make your SP slow and degrade the performance. Try using more effecient SET based approaches instead of using CURSORS.

6. Use TRY-CATCH block: for Better error handling.

7. Using Transactions: Use Transactions by using BEGIN TRANSACTION and COMMIT/ROLLBACK TRANSACTION blocks to make sure that your actions follow ACID properties.
But keep the transctions as short as possible to create less blocking in database and thus avoiding deadlocks.

8. Aliasing objects: Do not use SQL Server reserve keywords while naming SQL Server objects, like for: tables, columns, views, etc. Although it allows most of the time, but its better to use other and better naming conventions.

9. Avoid GOTO stmt: Do not use GOTO statements in your code as it is considered a bad programming practice (and in every other programming language). Try comming up with a better approach/logic, use conditional IF-ELSE logic, WHILE loops and other programming constructs.

10. Avoid ‘sp_’ prefix : Do not prefix the SP name with ‘sp_’. If it begins with ‘sp_’ then the compiler will first search it in master database and then in the current one, thus delaying the execution.

11. Use fully qualified objects name: in your SQL queries, this helps in quickly finding the cached/compiled plan. Also execute the SP by using its fully qualified name, like: EXEC dbo.SPname

12. Use WITH ENCRYPTION: You can also use ‘WITH ENCRYPTION’ option while creating SP to hide the code.

13. Add Comments: Try to put comments wherever possible to give details and idea to other developers what actually your code is doing.

14. Use BEGIN-END code block: Try to bind your whole SP code within BEGIN-END block. Although it is optional but it looks good and gives a better picture.

16. Beautify your code: Well indent your code by uisng TABs, Spaces and New line (ENTER). Try going vertically downwards with your code rather expanding horizontally. This will make your code easy to read and more understandable to other developers.

Windows Disk Space Alert From SQL SERVER JOB

In SQL server when the auto growth option is set to true and when we have defined the autogorwth in MB, some times we may face the issue like windows disk space is full, during that time SQL Server cannot increase the auto growth size of the mdf or ldf files.

As Best practice to have the alert for the drives free space. We can create a job for the below script. schedule it for every 15 or 30 minutes based on your application usage / load.

declare @svrName varchar(255)
declare @sql varchar(400)
set @svrName = @@SERVERNAME
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--creating a temporary table
CREATE TABLE #output
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql
--script to retrieve the values in MB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
into #SpaceGB
from #output
where line like '[A-Z][:]%'
order by drivename
--script to drop the temporary table
--</code>

DECLARE @lowdrives VARCHAR(100)
set @lowdrives= NULL

SELECT @lowdrives = COALESCE(@lowdrives + ' ', '') + drivename + '  freespace(GB):  ' + convert(varchar(50),[freespace(GB)])  FROM #SpaceGB where  [freespace(GB)] <= 50  <span style="color: #ff0000;">--replace your Limit GB</span>
select @lowdrives
if @lowdrives is not NULL
begin

EXEC msdb.dbo.sp_send_dbmail
@profile_name =<span style="color: #ff0000;"> '    ', --- replace with profile name</span>
@recipients = '<span style="color: #ff0000;">  ', --- replace with receiver Mail id</span>
@subject = 'Emergency ** SQL SERVER ** DRIVE SPACE ISSUE' ,
@body =  @lowdrives
end

drop table #output
drop table #SpaceGB

How to Find SQL Server Database Size

if convert(varchar(20),SERVERPROPERTY(‘productversion’)) like ‘8%’

SELECT [name], fileid, filename, [size]/128 AS ‘Total Size in MB’,
[size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’,
CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’,
(100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’
FROM sysfiles
else
SELECT [name], file_id, physical_name, [size]/128 AS ‘Total Size in MB’,
[size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’,
CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’,
(100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’
FROM sys.database_files

 

dbspace

How to Find SQL Server Started date & Time – 4 Scripts to Find SQL Server Started Time

There  are so many way of finding the sql server started date time.

1. SQL Server Log

2.  using DMV

3. Using TempDB Creation date (as it is recreated when SQL Server is started every time)

4. Start Time of Default Trace

Examples : 

1. SQL Server Log :

SQL Server Management Studio –> Management –> SQL Server Logs –> We need to Search for the “SQL SERVER Starting….Image

or

 sp_readerrorlog 0,1,’Copyright (c)’

 

Image

 

2. Using DMV :

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

 

3. Using TempDb Creation Date

select crdate from sys.sysdatabases where name=’tempdb’

4. Default Trace :

 

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1;