Find out which database is using the most IO

Find out which database is using the most IO

 

When there are many databases on one drive and you need to find out  which one is causing IO issue or slowing down others then you can use below scripts to find out problematic databases. You can either separate them or fix long running queries  to fix IO issues.

 

--Queries taking longest elapsed time:
SELECT TOP 10
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_seconds DESC;

--Queries doing most I/O:

SELECT TOP 10
(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_IO DESC;

-------------The most busiest db find the most read write and io_stall

select db_name(mf.database_id) as databaseName, mf.physical_name,
       num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes,
       num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes
from sys.dm_io_virtual_file_stats(null,null) as divfs
         join sys.master_files as mf
              on mf.database_id = divfs.database_id
                 and mf.file_id = divfs.file_id
                  order by num_of_reads desc


 

Powershell - Start or Stop SQL service on remote server

 You can use below script to start and stop remote SQL services.

* Just for one computer

(Get-Service -ComputerName YOURCOMPUTERNAME -Name 'SQLAgent').Stop()
(Get-Service -ComputerName YOURCOMPUTERNAME -Name 'SQLAgent').Start() 

* For multiple computer

$ServerList = Get-Content "DRIVE\ServerNames.txt"

ForEach ($ServerName in $ServerList)
{
    Write-Host $ServerName
    (Get-Service -ComputerName YOURCOMPUTERNAME -Name 'SQLAgent').Stop()
    (Get-Service -ComputerName YOURCOMPUTERNAME -Name 'SQLAgent').Start()

}

Powershell - Run SQL Scripts on Multiple Servers

 You must need SqlServer module is the current PowerShell module to use below script.

The SqlServer module comes with:

• PowerShell Providers, which enables a simple navigation mechanism similar to file system paths. You can build paths similar to file system paths, where the drive is associated with a SQL Server management object model, and the nodes are based on the object model classes. You can then use familiar commands such as cd (alias for Set-Location) and dir (alias for Get-ChildItem) to navigate the paths similar to the way you navigate folders in a command prompt window. You can use other commands, such as ren (alias for Rename-Item) or del (alias for Remove-Item), to perform actions on the nodes in the path.

• A set of cmdlets that support actions such as running a sqlcmd script containing Transact-SQL or XQuery statements.

• The AS provider and cmdlets, which before they were installed separately.

   

  YOU NEED TO  PUT ALL SQL SERVER INSTANCE NAMES IN INSTANCENAME.txt file in order to run below script. I use  this scripts a lot to update or pull many information from different servers.
  

   

  $ServerList = Get-Content "DRIVE\InstanceName.txt"
  $SourceDatabase = "master"
  $Script1 = "select @@servername"
  
  ForEach ($ServerName in $ServerList)
  {
      Write-Host $ServerName
      invoke-sqlcmd -ServerInstance $ServerName -Database $SourceDatabase -Query $Script1
  
  }
  

   

   Comment your thought regarding this script. Let me know if you need to generate any scripts for your project I will be happy to help you out. 

  
  

Generate Object Level Permission

Run below script to generate users and permission in any databases.
Mostly it helps to generate users and its permission before refreshing from prod database or migrating to another server.




SELECT 'CREATE USER [' + name + '] for login [' + name + ']' from sys.database_principals
-- where
 --Type = 'U'
 --and
 -- name not like '%'
 --------------------------------------
 SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' from sys.database_principals users inner join sys.database_role_members link on link.member_principal_id = users.principal_id inner join sys.database_principals roles on roles.principal_id = link.role_principal_id
 -----------------------------------
 if object_id('dbo.tempPermissions') is not null Drop table dbo.tempPermissions Create table tempPermissions(ID int identity , Queries Varchar(255)) Insert into tempPermissions(Queries) select 'GRANT ' + dp.permission_name collate latin1_general_cs_as + ' ON ' + s.name + '.' + o.name + ' TO ' + '[' +dpr.name + ']' FROM sys.database_permissions AS dp INNER JOIN sys.objects AS o ON dp.major_id=o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id WHERE dpr.name NOT IN ('public','guest') declare @count int, @max int, @query Varchar(255) set @count =1 set @max = (Select max(ID) from tempPermissions) set @query = (Select Queries from tempPermissions where ID = @count) while(@count < @max) begin exec(@query) set @count += 1 set @query = (Select Queries from tempPermissions where ID = @count) end select * from tempPermissions drop table tempPermissions

SQL - Find out database restored history

Find the history of SQL database backups.

You need to add database name where I put DBNAME in this script. It will give you full, differential and log backup history. I know how important it is in DBA's daily routine.
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name



FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE


--(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)

--and
msdb.dbo.backupset.database_name = 'DBNAME'
and msdb..backupset.type = 'D'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date desc

Find out from SQL Management studio - Who is part of network group id?

I have script which tells you who is part of groupid. It helps to find some login issue.


declare @DomainGroupName varchar(50)

select @DomainGroupName = 'domain\GroupIDName'

exec xp_logininfo @DomainGroupName,'members'