Manage-SQLServer

Analyzing I/O performances in SQL Server

2017-01-24T07:20:00.001-08:00

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

2017-01-01T15:17:00.000-08:00

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'

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'

Find the current value of identity columns in the entire database

2017-01-01T14:44:00.000-08:00

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

2017-01-01T14:17:00.000-08:00

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

2016-12-30T15:48:00.002-08:00

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

2016-12-30T12:08:00.002-08:00

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

sp_configure 'xp_cmdshell',1

reconfigure

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

2016-12-30T11:42:00.000-08:00

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.

Code to generate all foreign keys of SQL Server database

2016-12-30T06:42:00.000-08:00

How to draft all the foreign keys of SQL Server database

DBA do end up in a situation where we need to copy all the foreign keys of the SQL Server to perform certain operations.

Like:

Archiving activity (When data is not consistent and gives constraint error when deleting records)

Recreating database due to fail over

Multiple publications going under re snapshot due to a release work

Any other application demand due to which FK's have to remove and then recreated.

Generally we do take a full backup of the database for any of the activities that are mentioned above, but that would should be last scenario where have to use the full backup restore method as it is time consuming.

Take the backup of entire FK's is better approach and then compare it once any of the above activity is done. We can easily put back the missing FK's so that application logic is undisturbed.

Please use the below code to generate all the FK's in the entire database. A simple action keyword will create two kinds of scripts.

DECLARE @schema_name sysname;
DECLARE @table_name sysname;
DECLARE @constraint_name sysname;
DECLARE @constraint_object_id int;
DECLARE @referenced_object_name sysname;
DECLARE @is_disabled bit;
DECLARE @is_not_for_replication bit;
DECLARE @is_not_trusted bit;
DECLARE @delete_referential_action tinyint;
DECLARE @update_referential_action tinyint;
DECLARE @tsql nvarchar(4000);
DECLARE @tsql2 nvarchar(4000);
DECLARE @fkCol sysname;
DECLARE @pkCol sysname;
DECLARE @col1 bit;
DECLARE @action char(6);

--SET @action = 'DROP';
--SET @action = 'CREATE';

DECLARE FKcursor CURSOR FOR
select OBJECT_SCHEMA_NAME(parent_object_id)
, OBJECT_NAME(parent_object_id), name, OBJECT_NAME(referenced_object_id)
, object_id
, is_disabled, is_not_for_replication, is_not_trusted
, delete_referential_action, update_referential_action
from sys.foreign_keys
order by 1,2;

OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name
, @referenced_object_name, @constraint_object_id
, @is_disabled, @is_not_for_replication, @is_not_trusted
, @delete_referential_action, @update_referential_action;

WHILE @@FETCH_STATUS = 0
BEGIN
IF @action <> 'CREATE'
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
+ ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name) + ';';
ELSE
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
+ CASE @is_not_trusted
WHEN 0 THEN ' WITH CHECK '
ELSE ' WITH NOCHECK '
END
+ ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name)
+ ' FOREIGN KEY ('

SET @tsql2 = '';

DECLARE ColumnCursor CURSOR FOR
select COL_NAME(fk.parent_object_id, fkc.parent_column_id)
, COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc
on fk.object_id = fkc.constraint_object_id
where fkc.constraint_object_id = @constraint_object_id
order by fkc.constraint_column_id;
OPEN ColumnCursor;
SET @col1 = 1;
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@col1 = 1)
SET @col1 = 0
ELSE
BEGIN
SET @tsql = @tsql + ',';
SET @tsql2 = @tsql2 + ',';
END;
SET @tsql = @tsql + QUOTENAME(@fkCol);
SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
END;
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;

SET @tsql = @tsql + ' ) REFERENCES ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@referenced_object_name)+ ' (' + @tsql2 + ')';
SET @tsql = @tsql+ ' ON UPDATE ' +
CASE
@update_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+ ' ON DELETE ' +
CASE
@delete_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+ CASE
@is_not_for_replication
WHEN 1 THEN ' NOT FOR REPLICATION '
ELSE ''
END
+ ';';
END;

PRINT @tsql;

IF @action = 'CREATE'
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
+ CASE @is_disabled
WHEN 0 THEN ' CHECK '
ELSE ' NOCHECK '
END
+ 'CONSTRAINT ' + QUOTENAME(@constraint_name)
+ ';';
PRINT @tsql;
END;

FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name
, @referenced_object_name, @constraint_object_id
, @is_disabled, @is_not_for_replication, @is_not_trusted
, @delete_referential_action, @update_referential_action;

END;

CLOSE FKcursor;
DEALLOCATE FKcursor;

Enable SQL Account to execute XP_CMDSHELL

2016-12-29T06:23:00.000-08:00

How to Provide execute privileges to the SQL Account on XP_CMDSHELL procedure

xp_cmdshell procedure is an extended stored procedure of the system which cannot be directly called by the SQL authenticated user. This stored procedure can perform work on the areas where SQL Server directly cannot get involved.

You can actually perform the windows operation using the command like.

1. Copy/Move/Create/Delete files on the disk

2. Create/Modify/Exec the Windows level jobs and other application related tasks

Above two are just some sort of examples. You can actually write your code to implement any kind of windows operation from SQL Server using this stored procedure.

As this SP is doing windows work, only WINDOWS Authenticated user can perform this operation who has access to the server on which SQL Server is installed. This kind of access is usually restricted at DBA level and application teams do face some issues when trying to use this functionality.

We have to perform the below operations so that SQL Authenticated user can access the XP_CMDSHELL extended stored procedure.

USE master
GO
/*-- To allow advanced options to be changed.*/
EXEC sp_configure 'show advanced options', 1
GO
/*-- To update the currently configured value for advanced options.*/
RECONFIGURE
GO
/*-- To enable the feature.*/
EXEC sp_configure 'xp_cmdshell', 1
GO
/*-- To update the currently configured value for this feature.*/
RECONFIGURE
GO
EXEC sp_xp_cmdshell_proxy_account 'domainname\User','UserPwd';
/*--Above domain account should be existing in the sql server and has required access (controlserver permission)*/
Go
Create user [sql_authenticated_login_name] for [sql_authenticated_login_name];
/*--SQL Login which needs to access xp_cmdshell should have user in master database*/
GO
Grant EXEC on XP_CMDSHELL to [sql_authenticated_login_name];
Go

What is going on in SQL Server now

2016-04-12T11:06:00.000-07:00

Finding what is going on now in SQL Server

Generally being a DBA, we are usually asked with one questions "what is going on" with database server whenever any performance issue or any thing happens in application.

DBA will jump in and try to find out

1. Who all are logged in
2. What all they doing
3. Since how long a session is running or what is the longest running session
4. Who is consuming more memory
5. Who is blocking who
6. If it is system task (dbcc, backup, restore etc) then how long will it run
7. What is the query being fired
8. How many reads or writes being done by a session
9. who owns which session
10.What is the hostname

I generally create a view in the maintenance database so that i don't need waste my time much in finding above parameters.

In panic situation many develop tendency to forget things and we do lot of messy things instead of finding the value in a simple.

Create the below view in any one the maintenance database and just select it whenever demand arises to see the current statistics.

Create view Whatisgoingon
as
SELECT
OBJECT_NAME(objectid) as ObjectName
,SUBSTRING(stateText.text, (statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(stateText.text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS statement_text
,DB_Name(database_id) as DatabaseName
,req.cpu_time AS CPU_Time
,cast(cast(DATEDIFF(second, last_request_start_time, getdate()) as float)/60 as decimal(10,3)) AS RunningMinutes
,req.Percent_Complete
,sess.HOST_NAME as RunningFrom
,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy
,sess.session_id AS SessionID
,req.blocking_session_id AS BlockingWith
,req.reads
,req.writes
,sess.[program_name]
,sess.login_name
,sess.status
,sess.last_request_start_time
,req.logical_reads

FROM
sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id
AND sess.is_user_process = 1
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS stateText

When ever demand arises just query:

Select * from whatisgoingon

Hope it helps.

How to telnet multiple windows server

2016-04-12T10:53:00.000-07:00

How do we telnet multiple windows server

In my recent project i got a requirement to set up a shared SQL Server from where i have to connect to multiple servers and perform the task related to day to day DBA activities.

This was a newly built server and therefore i have to struggle alot to find on what ports my database servers are working and whether they are accessible from this shared server or not ?

This was the biggest huddle to cross, so that i can install SQL Client and start working from it.

We can check the port connectivity by using below command.

CMD > Telnet HOSTNAME PORT_NUMBER

Eg: Telnet 191.10.10.1 1433

By doing above operation we can find out whether port 1433 is accessible to connect 191.10.10.1 server from the source location where we are executing it. It's a command prompt task.

Now how to achieve this for more than 500 servers ? We cannot do manually for hours together and get irritated.

I did lot of googling on this and came up with below solution. This is best way to proceed.

By using Powershell scripting.

Step 1: Login to the Shared server from where you want to check connectivity

Step 2: Open a notepad and copy the below script by changing "Servername" and port 1433 as per your need.

$test = @('servername1:1433','servername2:1433','servername3:1433')

Foreach ($t in $test)
{
$source = $t.Split(':')[0]
$port = $t.Split(':')[1]

Write-Host "Connecting to $source on port $port"

try
{
$socket = New-Object System.Net.Sockets.TcpClient($source, $port)
}
catch [Exception]
{
Write-Host $_.Exception.GetType().FullName
Write-Host $_.Exception.Message
}

Write-Host "Connected`n"
}

Step 3: Now save the above script as "Powershell_script.ps1"

Don't forget to change the server names and port numbers as per your need.

Step 4: Go to Command prompt and run the below script

CMD > powershell -File Pwoershell_script.ps1

Above script will give result like

Connecting to Servername on port 1433

Connected

If you want to log the complete output, especially when you are checking for hundreds of servers.

Use below command.

CMD > powershell -File Pwoershell_script.ps1 >>Output.log

You will find the output file in the same path where your command prompt is prompting now.

Hope it helps.

Capture current session details in SQL Server

2015-10-22T10:56:00.001-07:00

Find the active sessions in SQL Server Instance in detailed

When ever performance of SQL Server has started degrading very first thing a DBA should do is

Check the current active sessions and their details like

a. CPU usage

b. Memory Usage

c. Query being execution

d. how long query is being executed

e. Any locks on sessions

f. who is the user of session

g. From where the connection is coming

h. When session is started

i. Reads / writes caused

j. session id

Above all the details can be retrieved easily from the DMV's of SQL Server. We just need to join in a very simple to accomplish the task.

Below is the query which i use in my day to day operational work to figure out bottle necks.

SELECT  OBJECT_NAME(objectid) AS ObjectName  ,SUBSTRING(stateText.TEXT, (statement_start_offset/2)+1,
((CASE statement_end_offset  WHEN -1 THEN DATALENGTH(stateText.TEXT)  ELSE statement_end_offset  END - statement_start_offset)/2) + 1) AS statement_text  ,DB_NAME(database_id) AS DatabaseName  ,req.cpu_time AS CPU_Time  ,CAST(CAST(DATEDIFF(second, last_request_start_time, GETDATE()) AS FLOAT)/60 AS DECIMAL(10,3)) AS RunningMinutes  ,req.Percent_Complete  ,sess.HOST_NAME AS RunningFrom  ,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy  ,sess.session_id AS SessionID  ,req.blocking_session_id AS BlockingWith  ,req.reads  ,req.writes  ,sess.[program_name]  ,sess.login_name  ,sess.status  ,sess.last_request_start_time  ,req.logical_reads
  FROM  sys.dm_exec_requests req  INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id  AND sess.is_user_process = 1  CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS stateText

I usually create a view using the above code in the DBA technical support database and query it easily when ever is needed.

Hope it will help.

Complete Database details of SQL Server

2015-10-22T07:21:00.001-07:00

Find the complete details of all the databases hosted in SQL Server Instance

Some times to find the database details we need to run multiple queries.

Below one query will retrieve the following information which helps in quick understanding of the databases hosted in a particular SQL Server Instance.

1. DB Name

2. Logical File Name

3. File Size

4. Physical name and Path

5. Size

6. Percentage of Growth

7. Occupied Space

8. Free Space

9. Status

10. Recovery model


DECLARE @DBInfo TABLE

( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName SYSNAME,
PhysicalFileName NVARCHAR(520),
Status SYSNAME,
Updateability SYSNAME,
RecoveryMode SYSNAME,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
Autogrow VARCHAR(100),

PollDate DATETIME) DECLARE @command VARCHAR(5000)SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sys.database_files.size/128.0 AS int) AS FileSize,
sys.database_files.name AS LogicalFileName, sys.database_files.physical_name AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sys.database_files.size/128.0 - CAST(FILEPROPERTY(sys.database_files.name, ' + '''' +
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sys.database_files.size/128.0 -CAST(FILEPROPERTY(sys.database_files.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sys.database_files.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
CASE WHEN is_percent_growth = 0  THEN LTRIM(STR(growth * 8.0 / 1024,10,1)) + '' MB,''         
ELSE ''By '' + CAST(growth AS VARCHAR) + '' percent, ''    END +     
CASE WHEN max_size = -1 THEN ''unrestricted growth''    
ELSE ''restricted growth to '' +  LTRIM(STR(max_size * 8.0 / 1024,10,1)) + '' MB''     
END AS Autogrow,
GETDATE() as PollDate FROM sys.database_files' INSERT INTO @DBInfo
   (ServerName,
   DatabaseName,
   FileSizeMB,
   LogicalFileName,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct, 
   Autogrow, 
   PollDate)  EXEC sp_MSforeachdb @command SELECT
   ServerName,
   DatabaseName,
   FileSizeMB,
   LogicalFileName,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct,
   Autogrow,
   PollDate  FROM @DBInfo

WHERE DatabaseName NOT IN ('Master','Model','tempdb')

ORDER BY
   ServerName,
   DatabaseName

Size of all the tables in a database of SQL Server

2015-10-21T07:24:00.001-07:00

How to find the size of all the tables residing in a database

Gathering the table wise size data is very important in DBA support life cycle.

Being a DBA, we need to have command on the database growth and table wise growth to get the proper estimates as part of capacity management.

Below query helps us in find the complete details of the tables in particular database.

It will provide the number of rows and size of the table along with index size.

SET NOCOUNT ON
DECLARE            @tablename VARCHAR(250)

DECLARE            db_cursor CURSOR FOR
SELECT         table_schema+'.'+table_name

FROM           INFORMATION_SCHEMA.TABLES
WHERE          table_type='BASE TABLE'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0

BEGIN   
           DECLARE @sqltableusage 
           TABLE(name VARCHAR(250),rows INT,reserved VARCHAR(20), 
           data VARCHAR(20), index_used VARCHAR(20), unused VARCHAR(20))
           INSERT INTO @sqltableusage
           EXEC sp_spaceused @tablename
       
  FETCH NEXT FROM db_cursor INTO @tablename

  END
CLOSE db_cursor

DEALLOCATE db_cursor

SELECT

name,rows,REPLACE(reserved,'KB','') Reserved_KB,REPLACE(data,'KB','') Data_KB,REPLACE(index_used,'KB','') Index_Used_KB,REPLACE(unused,'KB','') Unused_KB,CONVERT(DECIMAL(14,2),(REPLACE(reserved,'KB','')))/1024 AS Reserved_MB,CONVERT(DECIMAL(14,2),(REPLACE(data,'KB','')))/1024 AS Data_MB,CONVERT(DECIMAL(14,2),(REPLACE(index_used,'KB','')))/1024 AS Index_Used_MB,CONVERT(DECIMAL(14,2),(REPLACE(unused,'KB','')))/1024 AS Unused_MB

FROM @sqltableusage

Find Database size details in SQL Server

2015-10-21T07:12:00.003-07:00

How to find current database size details in SQL Server

Every database will have free space in it depending upon the growth style and type of activities going on the database.

If database has more delete operations on it and growth is depending upon the percentage then some times we may see huge free space in the database.

Below query will give the details.

SELECT     GETDATE() Report_Date ,a.FILE_ID AS [FileId], 
           a.name AS [LogicalName], 
           CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) AS [FileSize(MB)], 
           CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,''
           +'SpaceUsed'+'')/128.000,2)) AS [SpaceUsed(MB)], CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,''
           +'SpaceUsed'+''))/128.000,2)) AS [FreeSpace(MB)], ROUND((CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,''
           +'SpaceUsed'+'')/128.000,2))/ CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))*100,2) AS [Pct_SpaceUsed], 
           ROUND((CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,''
           +'SpaceUsed'+''))/128.000,2))/ CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))*100,2) AS [Pct_FreeSpace] FROM       sys.database_files a LEFT OUTER JOIN sys.data_spaces b ON         a.data_space_id = b.data_space_id

RAM occupied by SQL Server Database

2015-10-21T06:00:00.002-07:00

How much size of RAM is occupied by each database in SQL Server Database

Memory one of the important bottle neck in troubleshooting the performance of SQL Server.

It is very evident that the pages which are retrieved from RAM directly will increase the performance of any application rather than pages retrieving from DISK to RAM.

Our first preference is to find out how much space is being occupied by each database in the buffer cache (which is nothing but physical RAM).

Below is the query to find the space details occupied by database in RAM.


DECLARE        @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters

WHERE      RTRIM([object_name]) LIKE '%Buffer Manager'   AND counter_name = 'Total Pages';
;WITH src AS

(SELECT database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors

GROUP BY    database_id)SELECT     [db_name] = CASE [database_id] WHEN 32767       
           THEN  'Resource DB'        ELSE DB_NAME([database_id]) END,
           db_buffer_pages,   db_buffer_MB = db_buffer_pages / 128,   
           db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer)

FROM src
ORDER BY db_buffer_MB DESC;

After identifying the database which is consuming more space in RAM, now it's turn to identify the table which is taking more space in that particular database.

USE [DB_NAME];
GO
WITH src AS(

SELECT       [Object] = o.name,       [Type] = o.type_desc,       [Index] =                  COALESCE(i.name, ''),[Index_Type] = i.type_desc,       p.                        [object_id],       p.index_id,       au.allocation_unit_id

FROM         sys.partitions AS p
INNER JOIN    sys.allocation_units AS au
ON p.hobt_id = au.container_id
INNER JOIN   sys.objects AS o       ON p.[object_id] = o.[object_id]

INNER JOIN    sys.indexes AS i       ON o.[object_id] = i.[object_id]       AND               p.index_id = i.index_id

WHERE au.[type] IN (1,2,3) AND o.is_ms_shipped = 0)

SELECT       src.[Object],   src.[Type],   src.[Index],   src.Index_Type,   
             buffer_pages = COUNT_BIG(b.page_id),

buffer_mb = COUNT_BIG(b.page_id) / 128
FROM         src
INNER JOIN    sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id=b.allocation_unit_id
WHERE        b.database_id = DB_ID()
GROUP BY      src.[Object],   src.[Type],   src.[Index],   src.Index_Type
ORDER BY      buffer_pages DESC;

Now it will be clear regarding which table pages are more in physical RAM.
Obviously this table is being retrieved more than any other table in the database.

Now it's up to you how you proceed for further troubleshooting. You need to check what type of queries being fired from the application or users.

Question yourself

1. Is this table retrieving more than it is needed
2. Why do we need all the column data in the RAM
3. Can we create the index and extract only those columns data which is needed.

Now proceed the way where your question takes you to solve the issues.

Please post your issues or concerns so that together we can work and solve it.

Find the Missing Indexes in SQL Server Database

2015-10-13T11:40:00.002-07:00

Missing Index Analysis in SQL Server

I am not here to describe what is index and what is its impact on SQL Server.

There will be missing indexes in any SQL Server database if proper indexes are not created or any application related queries are deployed in the system.

Below Query helps in finding the missing indexes of a particular database.

Use [DBNAME]
GO

SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,Ind.[name] AS IndexName,SUBSTRING(
( SELECT ', ' + AC.name FROM sys.[tables] AS T INNER JOIN sys.[indexes] I

ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND
I.[index_id] = IC.[index_id] INNER JOIN sys.[all_columns] AC ON
T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] WHERE
Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id AND
IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR XML PATH('')), 2, 8000) AS KeyCols,
SUBSTRING(( SELECT ', ' + AC.name FROM sys.[tables] AS T INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id] INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR XML PATH('')), 2, 8000) AS IncludeCols
FROM sys.[indexes] Ind INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]
ORDER BY TableName
GO

Above query will give the proposed indexes on the database to improve the performance.

Confused how to put the indexes in create statement syntax. Here is the simplest way to create all the missing indexes in one shot.

Below Query will help in finding the missing indexes and create the syntax as well.

--Create Missing Indexes---
-----------------------------------

SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]
    ,db.[database_id] AS [DatabaseID]
    ,db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
    ,gs.[avg_user_impact] AS [AvgUserImpact]
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]

FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)

INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
    ON gs.[group_handle] = ig.[index_group_handle]

INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
    ON ig.[index_handle] = id.[index_handle]

INNER JOIN [sys].[databases] db WITH (NOLOCK)
    ON db.[database_id] = id.[database_id]

WHERE id.[database_id] = 6 -- Remove this to see for entire instance

ORDER BY [IndexAdvantage] DESC
OPTION (RECOMPILE);

Being a DBA, my suggestion is to go through the proposed indexes and analyze whether it will seriously help or not. More indexes may kill the performance some times. Pay double attention when doing this activity.

Get back to me in case of the script doesn't work or if needed help :)

OPTIMIZED INDEXING IN SQL SERVER

2015-10-13T02:58:00.001-07:00

OPTIMIZED INDEXING IN SQL SERVER

Indexing is an important task to keep the database performance good. But there are many issues with indexing which we being a DBA have to pay attention.

Points to remember indexing:

1. Indexing regularly increases the i/o on disk. More disk activity is always dangerous for the CPU.

2. Choosing correct fill factor is mandatory for the performance.

3. Fragmentation percentage should be rightly considered for reorganizing or rebuilding indexes.

4. All the indexes cannot be rebuild with ONLINE operation. Blob indexes should come under offline indexes.

5. Online Indexing comes under Enterprise\Developer edition.

6. Page count of indexes is important to consider for large tables.

7. Logging of data is important to know which index has taken more time for getting rebuild or reorg.

If a table is very large then huge amount of fragmented pages also do not come under more than 20% fragmentation percentage.

Keeping above point in view, Below code is built in such a way

1. It will keep the logging of every index in msdb database.

2. Depending upon edition of SQL Server, code will be intelligent enough to run in any edition.

3. BLOB indexes will be reindex'd offline only.


---NEW INDEXING CODE (LIVE)----
------------------------------------------------

DECLARE @C1 CURSOR
DECLARE @Tname NVARCHAR(MAX)

DECLARE @Iname NVARCHAR(MAX)

DECLARE @Frag INT
DECLARE @PCount INT
DECLARE @FCount INT
DECLARE @Obj_id bigint

DECLARE @Ind_id INT
DECLARE @dt1 DATETIME
DECLARE @dt2 DATETIME
DECLARE @edition NVARCHAR(100)

DECLARE @version NVARCHAR(100)
DECLARE @SQL1 NVARCHAR(MAX)
DECLARE @SQL2 NVARCHAR(MAX)
DECLARE @SQL3 NVARCHAR(MAX)
DECLARE @SQL4 NVARCHAR(MAX)
DECLARE @dbname NVARCHAR(MAX)
DECLARE @schname NVARCHAR(MAX)
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sys.objects WHERE name LIKE 'Index_Operation')

BEGIN
CREATE TABLE msdb.dbo.Index_Operation (DB_NAME NVARCHAR(MAX),Obj_id bigint,Ind_id INT,Index_name NVARCHAR(MAX),Frag_Percent INT,Page_Count INT,Fragments_Count INT,Ind_Operation VARCHAR(20),Start_time DATETIME,End_time DATETIME,Time_Taken_Sec FLOAT,TYPE CHAR(1))

END

SELECT * INTO #indices FROM (SELECT 
DISTINCT OBJECT_NAME(a.OBJECT_ID) Tname,b.name Iname,SUM(a.avg_fragmentation_in_percent)/COUNT(index_level) Frag,DB_NAME(a.database_id) dbname,OBJECT_SCHEMA_NAME(a.OBJECT_ID) schname,a.OBJECT_ID,a.index_id,a.avg_fragment_size_in_pages,page_count,fragment_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,NULL, NULL,NULL) AS a JOIN sys.indexes AS b WITH(nolock) ON a.OBJECT_ID = b.OBJECT_ID AND a.index_id = b.index_id WHERE a.database_id = DB_ID() AND b.name IS NOT NULL
AND a.avg_fragmentation_in_percent <>0 AND a.page_count>1000 AND fragment_count>100 AND b.allow_page_locks=1 AND b.name NOT LIKE '%merge%'  GROUP BY a.OBJECT_ID,b.name,a.database_id,a.index_id,a.avg_fragment_size_in_pages,page_count,fragment_count
 ) ind
  SET @C1= CURSOR FOR SELECT Tname,Iname,Frag,dbname,schname,[object_id],index_id,page_count,fragment_count FROM #indices

SELECT * INTO #indcolumns FROM (SELECT OBJECT_ID(A.TABLE_NAME) [OBJECT_ID],A.TABLE_SCHEMA,A.TABLE_NAME,A.COLUMN_NAME,A.DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS A WHERE DATA_TYPE IN (LOWER('FILESTREAM'),LOWER('XML'),LOWER('VARBINARY'),LOWER('TEXT'),LOWER('NTEXT'),LOWER('IMAGE')) 
OR
(DATA_TYPE IN (LOWER('VARCHAR'),LOWER('NVARCHAR')) AND CHARACTER_MAXIMUM_LENGTH = -1)) A

SET @edition= (SELECT CONVERT(VARCHAR(100),SERVERPROPERTY('Edition')))
SET @version= (SELECT CONVERT(NVARCHAR(100),SUBSTRING(@@version,1,CHARINDEX('-',@@version)-1)))
OPEN @C1 FETCH next FROM @C1 INTO @Tname,@Iname,@Frag,@dbname,@schname,@Obj_id,@Ind_id,@PCount,@FCount

WHILE (@@FETCH_STATUS=0)
   
   BEGIN
       IF(@FCount>500)
           BEGIN
               IF ( @edition LIKE '%Enterprise%' AND @version LIKE '%2008%')
                   BEGIN               
                       IF EXISTS (SELECT [OBJECT_ID] FROM #indcolumns WHERE [OBJECT_ID]=@Obj_id)
                           BEGIN
                               BEGIN TRY
                               SET @SQL1='ALTER INDEX ['+@Iname +'] on  ['+@dbname+'].['+@schname+'].['+@Tname+'] REBUILD'
                               PRINT @SQL1
                               SET @dt1=GETDATE()
                               EXEC sp_executesql @SQL1
                               SET @dt2=GETDATE()
                               INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'REBUILD',@dt1,@dt2,CAST(DATEDIFF(MILLISECOND,@dt1,@dt2) AS FLOAT)/1000,'B')
                               END TRY
                               BEGIN CATCH
                               SET @dt1=GETDATE()
                               SET @dt2=GETDATE()
                               INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'FAILED',@dt1,@dt2,NULL,'B')
                               END CATCH
                           END
                       ELSE
                           BEGIN
                               BEGIN TRY
                               SET @SQL2='ALTER INDEX ['+@Iname +'] on  ['+@dbname+'].['+@schname+'].['+@Tname+'] REBUILD with (FILLFACTOR= 80 , SORT_IN_TEMPDB = ON, ONLINE = ON) '
                               PRINT @SQL2
                               SET @dt1=GETDATE()
                               EXEC sp_executesql @SQL2
                               SET @dt2=GETDATE()
                               INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'REBUILD',@dt1,@dt2,CAST(DATEDIFF(MILLISECOND,@dt1,@dt2) AS FLOAT)/1000,'I')
                               END TRY
                               BEGIN CATCH
                               SET @dt1=GETDATE()
                                   SET @dt2=GETDATE()
                               INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'FAILED',@dt1,@dt2,NULL,'I')
                               END CATCH
                           END
                   
                   END
               ELSE 
                   BEGIN               
                       BEGIN TRY
                       SET @SQL3='ALTER INDEX ['+@Iname +'] on  ['+@dbname+'].['+@schname+'].['+@Tname+'] REBUILD with (FILLFACTOR= 80 , SORT_IN_TEMPDB = ON) '
                       PRINT @SQL3
                       SET @dt1=GETDATE()
                       EXEC sp_executesql @SQL3
                       SET @dt2=GETDATE()
                       INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'REBUILD',@dt1,@dt2,CAST(DATEDIFF(MILLISECOND,@dt1,@dt2) AS FLOAT)/1000,'I')
                       END TRY
                       BEGIN CATCH
                       SET @dt1=GETDATE()
                       SET @dt2=GETDATE()
                       INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'FAILED',@dt1,@dt2,NULL,'I')
                       END CATCH
                   END
               
           END
       ELSE
           BEGIN
               BEGIN TRY
               SET @SQL4='ALTER INDEX ['+@Iname+'] on  ['+@dbname+'].['+@schname+'].['+@Tname+'] REORGANIZE  '
               PRINT @SQL4
               SET @dt1=GETDATE()
               EXEC sp_executesql @SQL4
               SET @dt2=GETDATE()
               INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'REORGANIZE',@dt1,@dt2,CAST(DATEDIFF(MILLISECOND,@dt1,@dt2) AS FLOAT)/1000,'I')
               END TRY
               BEGIN CATCH
               SET @dt1=GETDATE()
               SET @dt2=GETDATE()
               INSERT INTO msdb.dbo.Index_Operation VALUES(@dbname,@Obj_id,@Ind_id,@Iname,@Frag,@PCount,@FCount,'FAILED',@dt1,@dt2,NULL,'I')
               END CATCH
           END
       FETCH next FROM @C1 INTO @Tname,@Iname,@Frag,@dbname,@schname,@Obj_id,@Ind_id,@PCount,@FCount
   END

CLOSE @C1

DELETE FROM msdb.dbo.Index_Operation WHERE End_time<=GETDATE()-31
DROP TABLE #indices
DROP TABLE #indcolumns
DEALLOCATE @C1

Create Users script from a database

2014-06-24T13:16:00.004-07:00

USER CREATE SCRIPT FROM DATABASE

We need to preserve the users information from a database before refresh/restore task.

Below script will be used to generate a script out the database having all the user information. This script will generate "User Creation script".

It will be conditional script. If user is already existing in the destination or refreshed database then it will skip else it will create the user.

Declare @C1 Cursor
Declare @Uname nvarchar(200)
Declare @Lname nvarchar(200)
Declare @type char(1)
set @C1 = Cursor for
select name,SUSER_SNAME(sid) AS LOGIN_NAME,type from sys.database_principals
where type in ('U','S','G') and SUSER_SNAME(sid) is NOT NULL and principal_id>4
and name not like '#%' and name not like 'NT AUTHORITY%'

OPEN @C1
FETCH NEXT FROM @C1 into @Uname,@Lname,@type
WHILE (@@FETCH_STATUS=0)
Begin
PRINT 'IF NOT EXISTS (Select name from sys.sysusers where name='''+@Uname+''') create User ['+@Uname+']'+' FOR LOGIN ['+@Lname+'];'

If (@type='S')
BEGIN
PRINT 'IF EXISTS (Select name from sys.sysusers where name='''+@Uname+''') exec sp_change_users_login ''update_one'',['+@Uname+'],['+@Lname+'];'
END
FETCH NEXT FROM @C1 into @Uname,@Lname,@type
End
Close @C1
Deallocate @C1

Find Users with privileges other than database roles

2014-06-21T13:58:00.002-07:00

USERS HAVING PRIVILEGES ON DATABASE OBJECTS

Privileges can be provided to users of a database in 3 ways.

1. By assigning the user to a fixed database role

2. By assigning the user to a User defined database role

3. By granting privilege to a user directly on the database objects.

In this area of blog, we will cover the third part. The other two points are also covered in the blog.

Below is the query provided to track down the privileges on database objects for all the users in a particular database of SQL Server.

This will be very helpful in case of database refresh and database restore activity to retain the privileges of Users on a database.

Script will be generated in the last column of the query output. Do save it :)

SELECT
USER_NAME(p.grantee_principal_id) AS principal_name,
SUSER_SNAME(dp.sid) AS LOGIN_NAME,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS [object_name],
p.permission_name,
p.state_desc AS permission_state_desc,
'grant '+p.permission_name +' on object::['+OBJECT_NAME(p.major_id)+'] to '+USER_NAME(p.grantee_principal_id)+'' as 'script'
from
sys.database_permissions p
inner JOIN
sys.database_principals dp
on
p.grantee_principal_id = dp.principal_id
where dp.type_desc <>'DATABASE_ROLE' and
dp.principal_id>4 and OBJECT_NAME(p.major_id) is not null

Find user defined database roles and its privileges

2014-06-21T13:35:00.000-07:00

HOW TO FIND USER DEFINED DATABASE ROLES AND THE PRIVILEGES ASSOCIATED WITH THE ROLE

There can be many user defined database roles in a database which are created by a DBA depending upon the requirement from support teams.

Instead of granting a particular access to any user , it is always to better to create a database role and grant the privileges to the role. DBA will have more control on security when lots of users want similar kind of access.

Challenge:

Challenge for a DBA comes when refresh of a database takes place having lots of user defined roles and backup used is the old one which doesn't have any. We have to make sure after refresh all the roles and Users are intact as it was before the refresh.

To achieve the above defined goal, we should save the db roles with its respective privileges by scripting them in order to create them once the refresh/restore is performed by a DBA.

Below query is used to find the user defined roles with its respective privileges and also provide the script to be used as next course of action.

select
name as principal_name,
SUSER_SNAME(sid) AS LOGIN_NAME ,
principal_id,
type_desc,
'NULL' [Class Desc],
'NULL' [Object Name],
'NULL' [Permission Name],
'NULL' [Permission Type],
'sp_addrole ['+name+']' collate SQL_Latin1_General_CP1_CI_AS AS [Script]
from sys.database_principals
where type='R' and is_fixed_role =0 and name not like 'public' and
principal_id in
(select distinct grantee_principal_id from sys.database_permissions)
UNION ALL
SELECT
USER_NAME(p.grantee_principal_id) AS principal_name,
SUSER_SNAME(dp.sid) AS LOGIN_NAME,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS [object_name],
p.permission_name,
p.state_desc AS permission_state_desc,
Case
when OBJECT_NAME(p.major_id) is not null
then 'grant '+p.permission_name +' on object::['+OBJECT_NAME(p.major_id)+'] to '+USER_NAME(p.grantee_principal_id)+''
when OBJECT_NAME(p.major_id) is null
then 'grant '+p.permission_name +' to '+USER_NAME(p.grantee_principal_id)+''
End as 'script'
from sys.database_permissions p
inner JOIN sys.database_principals dp on
p.grantee_principal_id = dp.principal_id where dp.type_desc like
'DATABASE_ROLE' and dp.principal_id>0
GO

Find the relational tables

2014-06-21T12:59:00.000-07:00

TABLES HAVING RELATION WITH OTHER TABLES IN SQL SERVER

In a good relational database having normal forms being implemented will have relational tables.

This is possible by creating primary and foreign keys between the tables.

Below Query is used to find the tables having primary key and foreign key relations.

SELECT OBJECT_NAME (referenced_object_id) AS Parent_Table , OBJECT_NAME(parent_object_id) AS Child_Table , COL_NAME(referenced_object_id,referenced_column_id) AS Parent_Column , COL_NAME(parent_object_id,parent_column_id) AS Child_Column , OBJECT_NAME(constraint_object_id) AS FK_Name FROM
sys.foreign_key_columns
ORDER BY Parent_Table, Child_Table

How to Fix the Orphan users of a database

2014-06-21T12:25:00.000-07:00

FIX THE ORPHAN USERS OF DATABASE

Orphan Users are those users whose login exist in the SQL Server but mapping would have lost due to refresh of database.

When ever refresh of database is performed or restore of database on another SQL Server Instance chances of Orphan users arise.

Below Query will fix all the Orphan users at one go.

set nocount on

declare @Action varchar(10) -- '' (default) / UPDATE
set @Action=''

declare @ret int,
@cfixesupdate int, -- count of fixes by update
@loginsid varbinary(85),
@txtloginsid varchar(256),
@name sysname,
@binpwd varbinary(256),
@txtpwd sysname,
@tmpstr varchar (256),
@status int

-- SET INITIAL VALUES --
select @cfixesupdate = 0

-- CHECK PERMISSIONS --
if not is_member('db_owner') = 1
begin
raiserror(15247,-1,-1)
return
end

-- PREPARE CURSOR FOR SYSUSERS TABLE IN CURRENT DATABASE --
DECLARE login_curs CURSOR FOR
select [name], sid, [password] from sysusers
where issqluser = 1
and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
and [name] <> ('dbo')
order by [name]

OPEN login_curs

FETCH NEXT FROM login_curs INTO @name, @loginsid, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN
END

-- Create a temp table to hold any database login scripts that need to be generated
-- and manually run by the user.
if exists (select * from tempdb..sysobjects where name like '#sqlstmts%') drop table #sqlstmts
create table #sqlstmts
(
sqlid int,
sqlname varchar(50),
sqlstmt varchar(1000)
)

-- PROCESS ALL USERS IN CURSOR --
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

set @cfixesupdate = @cfixesupdate + 1 -- Increment the number of successfully repaired users.

-- NOTE: SYSLOGINS is a view for the SYSXLOGINS table.
set @ret = null -- init return value
select @ret = 1 from master.dbo.syslogins where
loginname = @name -- match login name
and isntname = 0 -- cannot use nt logins

--print 'User: ' + cast(@name as varchar(25)) + ' -- @RET: ' + cast(@ret as varchar(10))
if @ret is not null -- database login exists
begin
-- REPAIR THE USER LOGIN (SID) --
if upper(@Action) = 'UPDATE'
begin -- Attempt to repair the user login.
exec @ret = sp_change_users_login 'update_one', @name, @name
if @ret = 0
print @name + ': repaired '
else
print @name + ': failed '
end -- if UPDATE...
else -- Only generate the EXEC statements. Users must execute these statements manually.
begin
--print 'exec sp_change_users_login ''update_one'', ''' + @name + ''', ''' + @name + ''''
set @tmpstr = 'exec sp_change_users_login ''update_one'', ''' + @name + ''', ''' + @name + ''''
insert into #sqlstmts values (0, @name, @tmpstr)
end -- else
end -- If @ret is not null...

NextUser: -- Get the next user
FETCH NEXT FROM login_curs INTO @name, @loginsid, @binpwd

end
end

CLOSE login_curs
DEALLOCATE login_curs

print ''
print '---------------------------------------'
print 'Total users: ' + cast(@cfixesupdate as varchar(8))
print '---------------------------------------'

-- Dump the SQL statements created by this script.
select sqlid as 'Seq', sqlname as 'User', sqlstmt as 'Run Me' from #sqlstmts order by sqlid, sqlname
drop table #sqlstmts

GO

We have to run the above query in the database where restore or refresh is performed.

The above script will generate a query in 'Run Me' Column

Copy all consolidated script at once and execute in the database to fix the Orphan users

Thanks :)

TRUNCATE DATA FROM TABLES

2014-06-20T14:15:00.001-07:00

TRUNCATE ALL THE DATA FROM TABLES

We do get the requirement some time to clean up all the data from the tables of a relational database.

We cannot simply truncate the data table by table in relational database due to relationship between primary key tables and foreign key tables.

Best thing to do in this scenario is to drop all the constraints from the database and truncate the data. We can later of create the constraints to make sure the metadata remains the same even after removing the complete data from the database tables.

So, how to proceed now:

Step 1: COPY ALL THE CONSTRAINTS

Below Script is used for creating a script for "Create constraints" as well as "Dropping Constraints", Please focus on the red coloured code in between and remove to comment for which ever action you wanted to perform. Copy both the action scripts output and execute the "Drop" action.

DECLARE @schema_name sysname;

DECLARE @table_name sysname;

DECLARE @constraint_name sysname;

DECLARE @constraint_object_id int;

DECLARE @referenced_object_name sysname;

DECLARE @is_disabled bit;

DECLARE @is_not_for_replication bit;

DECLARE @is_not_trusted bit;

DECLARE @delete_referential_action tinyint;

DECLARE @update_referential_action tinyint;

DECLARE @tsql nvarchar(4000);

DECLARE @tsql2 nvarchar(4000);

DECLARE @fkCol sysname;

DECLARE @pkCol sysname;

DECLARE @col1 bit;

DECLARE @action char(6);

--SET @action = 'DROP';

--SET @action = 'CREATE';

DECLARE FKcursor CURSOR FOR

select OBJECT_SCHEMA_NAME(parent_object_id)

, OBJECT_NAME(parent_object_id), name, OBJECT_NAME(referenced_object_id)

, object_id

, is_disabled, is_not_for_replication, is_not_trusted

, delete_referential_action, update_referential_action

from sys.foreign_keys

order by 1,2;

OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name

, @referenced_object_name, @constraint_object_id

, @is_disabled, @is_not_for_replication, @is_not_trusted

, @delete_referential_action, @update_referential_action;

WHILE @@FETCH_STATUS = 0

BEGIN

IF @action <> 'CREATE'

SET @tsql = 'ALTER TABLE '

+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

+ ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name) + ';';

ELSE

BEGIN

SET @tsql = 'ALTER TABLE '

+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

+ CASE @is_not_trusted

WHEN 0 THEN ' WITH CHECK '

ELSE ' WITH NOCHECK '

END

+ ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name)

+ ' FOREIGN KEY ('

SET @tsql2 = '';

DECLARE ColumnCursor CURSOR FOR

select COL_NAME(fk.parent_object_id, fkc.parent_column_id)

, COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)

from sys.foreign_keys fk

inner join sys.foreign_key_columns fkc

on fk.object_id = fkc.constraint_object_id

where fkc.constraint_object_id = @constraint_object_id

order by fkc.constraint_column_id;

OPEN ColumnCursor;

SET @col1 = 1;

FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

WHILE @@FETCH_STATUS = 0

BEGIN

IF (@col1 = 1)

SET @col1 = 0

ELSE

BEGIN

SET @tsql = @tsql + ',';

SET @tsql2 = @tsql2 + ',';

END;

SET @tsql = @tsql + QUOTENAME(@fkCol);

SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);

FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

END;

CLOSE ColumnCursor;

DEALLOCATE ColumnCursor;

SET @tsql = @tsql + ' ) REFERENCES ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@referenced_object_name)

+ ' (' + @tsql2 + ')';

SET @tsql = @tsql

+ ' ON UPDATE ' + CASE @update_referential_action

WHEN 0 THEN 'NO ACTION '

WHEN 1 THEN 'CASCADE '

WHEN 2 THEN 'SET NULL '

ELSE 'SET DEFAULT '

END

+ ' ON DELETE ' + CASE @delete_referential_action

WHEN 0 THEN 'NO ACTION '

WHEN 1 THEN 'CASCADE '

WHEN 2 THEN 'SET NULL '

ELSE 'SET DEFAULT '

END

+ CASE @is_not_for_replication

WHEN 1 THEN ' NOT FOR REPLICATION '

ELSE ''

END

+ ';';

END;

PRINT @tsql;

IF @action = 'CREATE'

BEGIN

SET @tsql = 'ALTER TABLE '

+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

+ CASE @is_disabled

WHEN 0 THEN ' CHECK '

ELSE ' NOCHECK '

END

+ 'CONSTRAINT ' + QUOTENAME(@constraint_name)

+ ';';

PRINT @tsql;

END;

FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name

, @referenced_object_name, @constraint_object_id

, @is_disabled, @is_not_for_replication, @is_not_trusted

, @delete_referential_action, @update_referential_action;

END;

CLOSE FKcursor;

DEALLOCATE FKcursor;

-----------------------------------------------------

Step 2 : Once constraints are dropped, truncate the tables using the below script

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

-----------------------------------------------------------------

Step 3: Create constraints

Now create back the constraints using the "Create Constraints" script generated at step 1.

We are done with the activity now.

Thank You

Endpoints in SQL Server

2013-07-25T05:09:00.000-07:00

What is an ENDPOINT in SQL SERVER

ENDPOINT is a gateway to enter into the SQL SERVER.

Each and every connection established to the SQL SERVER has to come through an ENDPOINT.

For Eg: Assume SQL SERVER as a GATED COMMUNITY where you have only one main entrance to enter. That is through Main Gate. This Gate is called as ENDPOINT to the GATED COMMUNITY.

Similary, SQL Server will have ENDPOINTS through which it allows client connections to enter.

ENDPOINTS is establised on PORT NUMBERS which are the GATEWAY from the firewall to enter into the WINDOWS SERVER where SQL Server is installed.

We have to have a clear distinguishing idea on ENDPOINTS / PORTS / PROTOCOLS.

PROTOCOL is a WAY to connect to SQL Server.

More information on protocols can be explored below

http://manage-sqlserver.blogspot.in/2012/08/find-protocol-used-by-session-to.html

PORT NUMBER is dedicated number provided by INTERNET AUTHORITY to each and every different Application/Software.

If we are supposed to create any dynamic ports for SQL SERVER or any new endpoints, good practise is to consider port above 50000.

Port details as per given server can be find:

http://manage-sqlserver.blogspot.in/2012/10/find-ports-information-in-server.html

Query to find the ENDPOINTS:

Select * from sys.endpoints

Select * from sys.tcp_endpoints

/* to find TCP ENDPOINTS, this will include the dynamically created endpoints as well */

Creating a dynamic Endpoint:

Create endpoint endpointname
state=started
as TCP
(Listener_port= portnumber , Listener_ip=All)
for TSQL();
GO

/* Changes needed in the above syntax are
endpointname = Valid ENDPOINT name
portnumber = give any port number that is up (check with NETWORK TEAM) Give above 50000 to avoid any confusion
Listener_ip = ALL (will accept all connections from outside), Particular IP = Will accept only that IP connection.
*/

Above syntax will create a dynamic endpoint.

Hope the above information helps