Wednesday, 20 June 2018

SQL DBA hands-on queries used in day-to-day activities

Some TSQL hands-on queries used by SQL DBA in day-to-day activities.

    1.To get SQL Server version installed in your server?

   SELECT @@VERSION AS SQLVersion;

    2.How to get when your SQL server was last restarted?

  SELECT sqlserver_start_time from sys.dm_os_sys_info;

    3.How to check User database size in SQL Server?

SELECT DB_NAME(database_id) AS DatabaseName,
SUM((size*8)/1024.0/1024.0) SizeGB
FROM sys.master_files
WHERE DB_NAME(database_id) in (select name  from sys.databases where name not in ('tempdb','master','msdb','model'))
group by DB_NAME(database_id)

    4.How to check free space and space used by database data files and log files in SQL Server?


             SELECT DB_NAME() AS DbName,
      name AS FileName,
      size/128.0 AS CurrentSizeMB,
      size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
      FROM sys.database_files;

     5.How to get session details in SQL Server?


              select * from sys.dm_exec_sessions;
       exec sp_who2;

     6.How to get blocking query in SQL Server?


      SELECT * FROM sys.sysprocesses WHERE blocked <> 0;
      exec  sp_who2;

     7. How to get list of databases which are offline in SQL Server?

SELECT name, database_id, create_date,*
FROM sys.databases where state_desc = 'offline';

    8. How to get database backup history details in SQL Server?


  SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.compressed_backup_size /1048576 AS NUMERIC(14,2)) AS VARCHAR(14) ) + ' '  AS "bkSize in MB", /*s.backup_size --use this if not compressed*/
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'Transaction Log Backup'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name in (select name  from sys.databases where name not in ('tempdb','master','msdb','model'))
and s.backup_start_date>=DATEADD(day,-1,GETDATE())
ORDER BY s.database_name,backup_start_date DESC, backup_finish_date

    9.How to get all indexes of a table in SQL Server?


select object_name(object_id),name "Index" from sys.indexes where object_id in (select object_id from sys.objects where name in 
('Table1','Table2')) ;

exec sp_helpindex 'account';

   10. how to get last index statistics updated date in SQL Server?


SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('dbo.table1');

11.How to get statistics of all objects of an entire database in SQL Server?


select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time
from sys.sysindexes as a
inner join sys.objects as b
on a.id = b.object_id
where b.type = 'U'

12.   How to get table/index fragmentation in SQL Server?

             SELECT  OBJECT_NAME(DMV.object_id) AS TABLE_NAME ,
      SI.NAME AS INDEX_NAME ,
      avg_fragmentation_in_percent AS FRAGMENT_PERCENT ,
      DMV.record_count
      FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED')
      AS DMV
      LEFT OUTER JOIN SYS.INDEXES AS SI ON DMV.OBJECT_ID = SI.OBJECT_ID
                                         AND DMV.INDEX_ID = SI.INDEX_ID
      WHERE   avg_fragmentation_in_percent > 10
      AND index_type_desc IN ( 'CLUSTERED INDEX', 'NONCLUSTERED INDEX' )
      AND DMV.record_count >= 2000
      ORDER BY TABLE_NAME DESC;



13.   How to get indexes with column on which index is created in SQL Server?

 SELECT 
OBJECT_NAME(ind.object_id) AS ObjectName,ind.name AS IndexName,  ind.is_primary_key AS IsPrimaryKey, ind.is_unique AS IsUniqueIndex,col.name AS ColumnName, ic.is_included_column AS IsIncludedColumn
     
FROM    sys.indexes ind
        INNER JOIN sys.index_columns ic
            ON ind.object_id = ic.object_id
               AND ind.index_id = ic.index_id
        INNER JOIN sys.columns col
            ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
        INNER JOIN sys.tables t
            ON ind.object_id = t.object_id
 WHERE   t.is_ms_shipped = 0
 AND t.name in('ACCOUNT','ACCOUNT_VIEW')

 ORDER BY OBJECT_SCHEMA_NAME(ind.object_id) --SchemaName
      , OBJECT_NAME(ind.object_id) --ObjectName
      , ind.is_primary_key DESC
      , ind.is_unique DESC
      , ind.name --IndexName
      , ic.key_ordinal

14.   How to get % completion of a currently running  job in SQL Server?

  SELECT percent_complete ,r.estimated_completion_time/1000    "estimated_completion_time_in_second",* 
FROM sys.dm_exec_sessions
INNER JOIN sys.dm_exec_requests
ON s.session_id = r.session_id 
and s.login_name not like '%BeheraH'
and s.status='running'

15.   How to get jobs and respective owner details in SQL Server?

select s.name,l.name
from  msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid


16.   To get job run history --run_duration column(HHMMSS format) 
in SQL Server?

   select
   j.name as 'JobName',
   run_date,
   run_time,
   msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
   run_duration
   From msdb.dbo.sysjobs j
   INNER JOIN msdb.dbo.sysjobhistory h
   ON j.job_id = h.job_id
  where j.enabled = 1  --Only Enabled Jobs
  order by JobName, RunDateTime desc

17.   How to know whether a job is running slow in SQL Server?

SELECT
    job.Name, job.job_ID
    ,job.Originating_Server
    ,activity.run_requested_Date
    ,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
FROM
    msdb.dbo.sysjobs_view job
        INNER JOIN msdb.dbo.sysjobactivity activity
        ON (job.job_id = activity.job_id)
WHERE
    run_Requested_date is not null
    AND stop_execution_date is null
    AND job.name like 'Adhoc_Maintenance.Subplan_1%'


18.   How to manage Tempdb issue in SQL Server?

    Check Size:

      select name,SUM(size)*1.0/128 AS [Size in MB] from tempdb.sys.database_files group by name;
      exec sp_spaceused;

  If tempdb is not releasing space, then execute below two command.:

    a) DBCC FREEPROCCACHE
    b) dbcc shrinkfile (tempdev, 'target size in MB')

Temp DB Usage:

select reserved_MB= convert(numeric(10,2),round((unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count +internal_object_reserved_page_count+mixed_extent_page_count)*8/1024.,2)) ,unallocated_extent_MB =convert(numeric(10,2),round(unallocated_extent_page_count*8/1024.,2)),user_object_reserved_page_count,user_object_reserved_MB =convert(numeric(10,2),round(user_object_reserved_page_count*8/1024.,2))from sys.dm_db_file_space_usage

Monitor tempdb growth:

select *
from sys.dm_db_session_space_usage spu
join sys.dm_exec_sessions s on s.session_id = spu.session_id
join sys.dm_exec_requests r on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(sql_handle) t
order by internal_objects_alloc_page_count desc


19.   How to get un used indexes in SQL Server?

SELECT object_name(i.object_id) AS TableName, i.name AS [Unused Index]
 FROM sys.indexes i
 LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id
       AND i.index_id = s.index_id
       AND s.database_id = db_id()
 WHERE objectproperty(i.object_id, 'IsIndexable') = 1
 AND objectproperty(i.object_id, 'IsIndexed') = 1
 AND s.index_id is null
 OR (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0) ORDER BY object_name(i.object_id) ASC
20. How find DB wise open connection?
SELECT DB_NAME(dbid) as "Database", COUNT(dbid) as "Number Of Open Connections",
loginame as "LoginName"
FROM sys.sysprocesses
WHERE dbid >4
GROUP BY dbid, loginame

21.   How check active connection in SQL Server?


SELECT DB_NAME(dbid) as "Database", COUNT(dbid) as "Number Of Open Connections",
loginame as "LoginName"
FROM sys.sysprocesses
   WHERE dbid > 0
GROUP BY dbid, loginame



22.   How to check connections for a particular DB in SQL Server?

    select spid,loginame,DB_NAME(dbid) AS DB_NAME ,status
    from sys.sysprocesses where     DB_NAME(dbid)='DB_Name';

23.   How to get maximum user connection setting in SQL Server?

           select * from sys.configurations
           where name ='user connections'

24.   To know current actual connection settings in SQL Server?

          select * from sys.dm_os_performance_counters
          where counter_name ='User Connections';

25.   To get table name having particular data type in SQL Server?

SELECT OBJECT_NAME(c.object_id) as table_name
    , c.name

    , t.name
    , c.precision
    , c.scale
FROM sys.columns c
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE t.name IN ('numeric')
AND c.scale <> 0
ORDER BY 1, 2

26.   How to get DB Users and their role privileges in SQL Server?

SELECT (select name from sys.databases where name='359STG') DBNAME,m.NAME USERNAME, p.NAME DBROLE

FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id

27.   How to Rename a database in SQL Server?

       USE master
      GO
     ALTER DATABASE <DB Name>
     SET SINGLE_USER
     WITH ROLLBACK IMMEDIATE
     GO
    EXEC master..sp_renamedb 'DBName','DBNewName'
    GO
    ALTER DATABASE DBNewName
   SET MULTI_USER

28.   How to Rename logical data file/log file name in SQL Server?

USE [master];
GO
ALTER DATABASE [DB Name] MODIFY FILE ( NAME = oldname, NEWNAME = naewname );
ALTER DATABASE [DB Name] MODIFY FILE ( NAME = odname_Log, NEWNAME = newname_Log );
GO

29.   How to get Query from currently running session in SQL Server?

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = (select session_id from sys.dm_exec_sessions where status='running' and login_name='LoginName')
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)

30.   How get SQL Text using session id in SQL Server?

       DBCC INPUTBUFFER (session id)

31.   How to get expensive queries in database in SQL Server?

query-1
SELECT TOP 20
       st.text,
       qp.query_plan,
       qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
Where st.dbid=9 and qs.last_execution_time>'2017-07-03 21:00:29.583'--and qs.last_execution_time< '2017-07-01 00:00:29.583'
ORDER BY total_elapsed_time DESC
GO

query-2
SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

32.   How to get currently running query in SQL Server?


SELECT sqltext.text,
      req.session_id,
      req.status,
      req.command,
      req.cpu_time,
     req.total_elapsed_time
FROM   sys.dm_exec_requests req
CROSS apply sys.Dm_exec_sql_text(sql_handle) AS sqltext

33.   How to get long running Query in SQL Server?


SELECT TOP 5 t.text
             AS
             'SQL Text',
             st.execution_count,
             Isnull(st.total_elapsed_time / st.execution_count, 0)
             AS 'AVG Excecution Time',
             st.total_worker_time / st.execution_count
             AS 'AVG Worker Time',
            st.total_worker_time,
             st.max_logical_reads,
             st.max_logical_writes,
             st.creation_time,
             Isnull(st.execution_count / Datediff(second, st.creation_time,
                                        Getdate()), 0) AS
             'Calls Per Second'
FROM   sys.dm_exec_query_stats st
       CROSS apply sys.Dm_exec_sql_text(st.sql_handle) t
ORDER  BY st.total_elapsed_time DESC

34.   How to get SQL Txt of recently executed queries in SQL Server?

DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate='2017-02-08 20:02:29.007', @EndDate='2017-02-08 23:42:29.007'
SELECT
    deqs.last_execution_time AS [Time],
   SUBSTRING( dest.TEXT,1,200) AS [Query]
 FROM
    sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
   
    where deqs.last_execution_time >=@StartDate AND   deqs.last_execution_time<=@EndDate
ORDER BY
    deqs.last_execution_time DESC

35.   How to Move Data files or log files to different physical location in SQL Server?


 Step: 1
ALTER DATABASE <DB Name> MODIFY FILE ( NAME = <logical Name>, FILENAME = 'E:\MSSQL.1\MSSQL\Data\datafile1.mdf');
ALTER DATABASE <Database Name> MODIFY FILE ( NAME = <Logical log file name>, FILENAME = 'F:\MSSQL.1\MSSQL\Log\Logfile.ldf');

Step 2:
ALTER DATABASE<Database Name > SET OFFLINE WITH ROLLBACK IMMEDIATE;

Step 3:
Copy data file/log file to new location.

Step 4:
ALTER DATABASE <Database Name > SET ONLINE;

36.   How to get from which backup database was restored in SQL Server?


SELECT  [rs].[destination_database_name] ,
        [rs].[restore_date] ,
        [bs].[backup_start_date] ,
        [bs].[backup_finish_date] ,
        [bs].[database_name] AS [source_database_name] ,
        [bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM    msdb..restorehistory rs
        INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
        INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

37.   How to know memory usage in SQL Server?

a.         select * from sys.dm_os_process_memory;


b.       select
      total_physical_memory_kb/1024 AS total_physical_memory_mb,
      available_physical_memory_kb/1024 AS available_physical_memory_mb,
      total_page_file_kb/1024 AS total_page_file_mb,
      available_page_file_kb/1024 AS available_page_file_mb,
      100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3)))
      AS 'Percentage_Used',
      system_memory_state_desc
from  sys.dm_os_sys_memory;

38.   How to get some insight of Locking and blocking sessions in SQL Server?


Select * from sys.dm_tran_locks;
Select * from sys.dm_os_wait_stats;

39.   How to reset of login in SQL Server?


ALTER LOGIN <login Name> WITH PASSWORD = '<PasswordHere>'; 

40.   How to get statistics collection date in SQL Server?

SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('dbo.account')
GO


41.   How to find dependent objects based on columns name in SQL Server?

SELECT OBJECT_NAME(OBJECT_ID),definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'AC__CUST_ADDR' + '%' order by 1;


42.   How to Add data file or log file in SQL Server?


ALTER DATABASE [DB NAME]
    ADD FILE
    (
    NAME = [Data4],
    FILENAME = 'K:\MSSQL\Data\Data4.mdf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [PRIMARY]



ALTER DATABASE [DB NAME]
ADD LOG FILE
(
NAME = DellTest_Log,
FILENAME = 'K:\MSSQL\Log\DB_Log.ldf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5MB
);
Go

43.   How to get list of objects pinned to memory in SQL Server?

SELECT * FROM sysobjects WHERE objectproperty(id, 'TableIsPinned') = 1

44.   How to list all partition tables?

select distinct t.name
from sys.partitions p
inner join sys.tables t
on p.object_id = t.object_id
where p.partition_number <> 1

45.   How see all partitions of a particular table in SQL Server?


  SELECT * FROM sys.dm_db_partition_stats  
      WHERE object_id = OBJECT_ID('Partition Table name’);

46.   How to verify partition function in SQL Server?


   SELECT PF.[name], RV.boundary_id, RV.[value]
FROM sys.partition_range_values AS RV
 JOIN sys.partition_functions AS PF
  ON RV.function_id = PF.function_id
WHERE PF.[name] = '<Function Name>'

47.    How to see partitions and range values in SQL Server?


SELECT psch.name as PartitionScheme,
prng.value AS ParitionValue,
prng.boundary_id AS BoundaryID
FROM sys.partition_functions AS pfun
INNER JOIN sys.partition_schemes psch ON pfun.function_id = psch.function_id
 INNER JOIN sys.partition_range_values prng ON prng.function_id=pfun.function_id
WHERE pfun.name = 'RangePF1'

48.   To check data of a particular partition in SQL Server?


SELECT * FROM score_general_data
WHERE $PARTITION.F_SG(Hash_Part) = 4 ;

49.   How to identify an object  using some code SQL txt  in SQL Server?


select * from syscomments where text like '% select * into SQLlist%'

50.   Bulk insert syntax in SQL Server.

BULK
INSERT JKH_OMG_Main_Acct_Tagging
FROM 'C:\Users\454048\Desktop\John\OMG2_main_account_tagging_20170521\main_file.txt' --location with filename
WITH
(
FIRSTROW=2,----starting form 2nd row.
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
GO
51.   Searching database name by data files in SQL Server?

select DB_NAME(database_id) AS "DB_NAME",name AS "Logical_Name", physical_name from sys.master_files where DB_NAME(database_id) not in
('master','model','msdb','tempdb')
and physical_name like '%E:\%'

52.   How to check DB features enabled in your SQL server instance ?

sp_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO
sp_CONFIGURE
GO

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE
GO

53.   How to take database offline immediately in SQL Server?

ALTER DATABASE [DB Name] SET OFFLINE WITH ROLLBACK IMMEDIATE;

54.   Schema authorization change in SQL Server?

SELECT * FROM  sys.schemas WHERE principal_id = USER_ID('id)
ALTER AUTHORIZATION ON SCHEMA::db_datareader TO db_datareader;

55.   How to get DB isolation level  in SQL Server?


EXEC('DBCC USEROPTIONS WITH NO_INFOMSGS')

56.   How to Change transaction level in SQL Server?

ALTER DATABASE <DB Namne> SET READ_COMMITTED_SNAPSHOT ON

57.   How to see Isolation level enabled or not using T-SQL?


select is_read_committed_snapshot_on, name
   from DBSWERMDB60.master.sys.databases
where name not in ('master','model','tempdb','msdb')
and is_read_committed_snapshot_on <>1

58. How to generate a script for killing multiple sessions in SQL Server?

SELECT DB_NAME(dbid) as "Database", COUNT(dbid) as "Number Of Open Connections",
loginame as "LoginName",'kill '+convert(varchar,spid)+';'
FROM sys.sysprocesses
WHERE dbid > 0
and DB_NAME(dbid)='<DB Name>'
GROUP BY dbid, loginame,spid

59. How to check SQL Server restart time in SQL Server?

select * from sys.dm_os_sys_info

60. How to generate a script to take multiple databases off line in SQL Server?

select 'alter database ' +name+ ' set offline with rollback immediate;' from sys.databases where name not in ('master','msdb','tempdb','model');