Analyzing I/O performances in SQL Server

Analyzing I/O performances in SQL Server

One of the most common reason behind performance degradation is deficient amount of storage subsystem performance. There are lot of reasons for low performance in storage, but calculating it and knowing what needs to be controlled and handled is always an essential task. Therefore, there are three major measures, which are quite necessary for improving performance of I/O subsystem. In the following section, we are going to discuss these three metrics and proceed further with analyzing I/O performance for SQL server.

Description of Three Metrics

Latency

           

●     It is the first metric, which is the time required by I/O to complete and is considered as service or response time.                    

●     The working of this measurement starts working when the OS gets initialized, sends request to the disk controller, and stops when drive finishes the request processing.        

●     The Reads are finished when OS receives data and writes are accomplished when drive informs OS, which has received the data.

●     Depending upon the caching policy and hardware support, the data might still be present in DRAM cache for writes. Thus, write-back caching is more faster than that of write-through caching. However, this will require a backup of battery for a disk controller.    

Input/Output Operations per Second (IOPS)

●     Understand the concept of this second metric with help of following example:

“Suppose there is a constant latency of 1 ms. It means that a drive will be able to process one thousand IOs in a second with a 1 queue depth”

This denotes that as many IOs are appended in the queue, correspondingly the latency also gets increased.

●     One of the major advantage of flash storage is that it will be able to read/write multiple NAND channels in parallel format, which will be in absence of electro-mechanical rotating parts for slowing down disk access.     

●     If a person is knowing queue depth and size of transferring then, he/she can transfer IOPS to MB/sec and then MB/sec to latency.

●     IOPS   is equal to depth divided by the latency and IOPS itself is not considered as size of transferring for a single disk.

Sequential Throughput

           

●     It is the rate with which one can transfer data in the server. Its unit is MB/sec or GB/sec and if the value is in MB/sec then, it is equal to IOPS times of transfer size.          

●     In an enterprise storage, sequential disk often gets short-changed both by storage vendors and administrators.          

●     Sequential throughput is vital for many database activities related to the server. This activity can involve backups and restores, creation and rebuilds of indexes, etc.

Workload Metrics of the SQL Server I/O

Talking about performance of the SQL server and I/O, there are multiple facts that users should monitor over time. One should be aware about read vs write ratio for their workload of all files of a user database and tempdb. Remember that, this ratio is not unique for all types of the SQL server and workloads.

The server user should view typical rate of I/O for sequential throughput and IOPS. One can determine IOPS by read/sec and write/sec of the Windows Performance Monitor (PerfMon) and on the other hand, one can view sequential throughput by disk read bytes/sec and disk write bytes/sec. One should operate PerfMon to calculate average disk sec/read and average sec/write, which are read and write latency at the level of disk.

Conclusion

Analyzing I/O performance for SQL server is a best practice to maintain the performance speed of the server. Each and every server user must be aware about the metrics used for measuring the performance of the application. This will also help in monitoring the server performance and hence, reduce any interruption while working.

 --

Author : Daniel Jones

Email Id: Daniel.jones.0543@gmail.com

Compare two table structures in SQL Server

How to compare two tables structure wise

Comparing two table structures is an important work when we have multiple environments for a application and it uses all the environments at different time or in different zones.

This is important when some applications give issues in one environment and doesn't give in another environment. 

 This is very easy if we buy some tools like Redgate compare which makes the life of DBA easy by comparing the complete objects of a database.

In the below I have shown a very simple way to compare the two table structures.

In the below query, search for "Table1" and "Table2" and change it with your respective table names.

select * from (

SELECT

[column].COLUMN_NAME AS [Column_Name],

[column].DATA_TYPE AS [datatype],

[column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],

[column].NUMERIC_PRECISION AS Numeric_precision,

[column].IS_NULLABLE AS [nullable]

FROM 

INFORMATION_SCHEMA.TABLES [table] INNER JOIN 

INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME

WHERE

[table].TABLE_TYPE = 'BASE TABLE'

AND [table].TABLE_NAME ='Table1'

)A

except

select * from (

SELECT

[column].COLUMN_NAME AS [Column_Name],

[column].DATA_TYPE AS [datatype],

[column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],

[column].NUMERIC_PRECISION AS Numeric_precision,

[column].IS_NULLABLE AS [nullable]

FROM 

INFORMATION_SCHEMA.TABLES [table] INNER JOIN 

INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME

WHERE

[table].TABLE_TYPE = 'BASE TABLE'

AND [table].TABLE_NAME ='Table2'

)B

Find the current value of identity columns in the entire database

Script to find the current value of identity columns in the entire SQL Server database

Identity constraints plays a vital role in populating the data into the tables from application. This becomes more vital when we have heavy transaction oriented tables and that too participating in some sort of replication as part of high availability.

Identity constraints data population is also related to the data type of column.

For example, if there is any column having data type as 'int' then the maximum value for that column would be 2147483647. We have to keep looking at the value of that column so that it doesn't reach the max value and application doesn't starve.

Please create an alert of the below script and ensure to get the results periodically to monitor it. 

SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +  QUOTENAME(t.name) AS TableName, 

c.name AS ColumnName,

IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) AS CurrentIdentityValue,

IDENT_INCR (SCHEMA_NAME(t.schema_id) + '.' + t.name) as Identity_increment

FROM    sys.columns AS c 

INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id]

WHERE   c.is_identity = 1 

-- write here the table name if you want to filter for specific table

and t.name like '%TABLE_NAME%'

Enable or Disable triggers of SQL Server tables

Script to Enable or Disable triggers of SQL Server tables

DBA need a script to enable or disable triggers on one or multiple tables of SQL Server depending upon the demand from application or development teams.

Usually we disable triggers when we archive data or do a bulk insert into a particular table.

Please find the below code to generate enable or disable trigger script. In the below code, 

three steps are commented.

To enable triggers, uncomment line 2.

To disable triggers, uncomment line 3

To put a condition for a particular table, uncomment line 8.

Declare @input varchar(10)

--set @input ='ENABLE'

--set @input='DISABLE'

select +@input+' TRIGGER [' +DB_NAME()+'].['+SCHEMA_NAME(a.schema_id)+

'].['+b.name+'] ON [' +DB_NAME()+'].['+SCHEMA_NAME(a.schema_id)+'].['+a.name+']'

from sys.objects a inner join sys.triggers b 

on a.object_id=b.parent_id

--where a.name like 'objectname/tablename'

order by a.name

Backup all the databases of a SQL Server Instance

Backup all the databases of SQL Server Instance 

Code to backup all the databases of SQL Server Instance using the features of SQL Server like compression.

Code is written in order to check the version and use the compression intelligently.

Declare @C1 Cursor

Declare @path nvarchar(250)

Declare @name nvarchar(100)

Declare @dt nvarchar(14)

Declare @sql1 nvarchar(250)

Declare @sql2 nvarchar(250)

Declare @sql3 nvarchar(250)

set @path='D:\testing\'

set @C1=CURSOR For

select distinct name from sys.databases where name not like 'tempdb' and state_desc like 'ONLINE'

open @c1

set @dt= (select CONVERT(varchar(10),getdate(),112)+replace(CONVERT(varchar(10),getdate(),108),':',''))

fetch next from @c1 into @name

while @@FETCH_STATUS=0

Begin

if (@@VERSION like '%2000%' or (@@VERSION like '%2005%') or (@@version like '%Express%'))

Begin

Print 'Starting database backup '+@name

set @sql1='backup database '+@name+' to disk ='''+@path+@name+'_'+@dt+'.bak'' with stats=1,description=''backup database before patching activity'''

Print(@sql1)

Exec (@sql1)

Print @name+' Database  backup completed successfully'

Print '#####################################################'

End

Else

Begin

Print 'Starting database backup '+@name

set @sql1='backup database '+@name+' to disk ='''+@path+@name+'_'+@dt+'.bak'' with stats=1,compression,description=''backup database before patching activity'''

Print(@sql1)

Exec (@sql1)

Print @name+' Database  backup completed successfully'

Print '#####################################################'

End

Fetch next from @c1 into @name

End

close @C1

deallocate @c1

find disk space details from SQL Server

Finding disk occupancy details from SQL Server

To find disk space details we have straight forward extended procedure which gives details very quickly.

That is XP_fixeddrives stored procedure which gives the below output.

To get a better picture so that you can present the output directly to business or users, please use the below code which shows output in much fashion.

Ensure below step is performed first.

Use master

go

sp_configure 'xp_cmdshell',1

go

reconfigure

go

Actual code for finding the disk space details.

create table #temp1 (Drive Char(1),[MB Free] bigint,[MB Total] bigint)
insert into #temp1(Drive,[MB Free]) exec master..xp_fixeddrives
Declare @var varchar(1)
Declare @sql nvarchar(1000)
Declare @c1 cursor
set @c1 = CURSOR For select Drive from #temp1
open @c1
fetch  next from @c1 into @var
while @@fetch_status =0
Begin

create table #temp2(output nvarchar(100))
set @sql = 'insert  #temp2 exec master..xp_cmdshell ''fsutil volume diskfree '+ @var +':'''
 execute sp_executesql @sql
update #temp1
set [MB Total] = (select cast((SUBSTRING(output,Len(LEFT(output,CHARINDEX(':',output)))+1,LEN(output))) as bigint)/1024/1024 as Total_space_MB from #temp2 where output is not null
and output like 'Total # of bytes%')
where Drive = @var
drop table #temp2
fetch  next from @c1 into @var


End
Close @c1
Deallocate @c1
Select Drive,[MB Free],[MB Total],cast(([MB Free]*100/[MB Total]) as decimal(4,2)) 'Percent of Free Space' from #temp1
Drop table #temp1

Above code will give the below output.

truncate all the data in the tables of a database

How to truncate all the tables in a database

We do get scenarios where we get request to truncate all the data in the tables and provide only the structure of it. These kind of scenarios do arise at times of testing or building any project.

Truncating all the data is not so easy as there might be some relational tables which makes the job tough. We have to delete the child table data first then handle the parent table to ensure we don't get any errors while deleting/truncating the data.

Other way around is drop the foreign keys in the database by taking the backup of them and then delete or truncate the data. Recreate the foreign keys to ensure the structure of the RDBMS is intact.

Below are the steps to proceed further.

1. Take the backup of Foreign keys in the database by following the below link.

http://manage-sqlserver.blogspot.co.uk/2016/12/code-to-generate-all-foreign-keys-of.html

2. Now run the below code to truncate the data.

  a. You can modify the code by if you want to truncate or delete specific tables or objects by applying the proper conditions in where clause.

 b. I have commented the execute step in the below cursor. First check the code and see the output as i have given the print output option. Remove the commented lines and then re-execute the code to truncate the data.

Declare @c1 cursor
declare @name nvarchar(100) 
declare @sql nvarchar(150)
set @C1 = CURSOR for 
select distinct '['+SCHEMA_NAME(schema_id)+'].['+name+']' from sys.objects where type = 'U' 
open @C1
fetch next from @C1 into @name
while (@@fetch_status=0)
Begin
set @sql= 'Truncate table '+@name
--exec (@sql)
Print @name+' truncated successfully '
fetch next from @C1 into @name
End
close @C1
Deallocate @C1

3. Now re-create all the foreign keys as stated in the 1st step.

Thanks.