Every SQL DBA knows the importance of having a strong understanding of TSQL queries. In this article, we’ll explore some of the most commonly used TSQL queries for DBAs, how to use them effectively, and the benefits they offer. We’ll also touch on how to optimize TSQL queries for better performance. Whether you’re a beginner or an experienced DBA, this article will have something for you. Let’s dive in!
As a SQL DBA, you can use TSQL queries to get insights into your database that you wouldn’t be able to get any other way. You can use TSQL queries to troubleshoot performance issues, identify trends, and even find security vulnerabilities. In addition, TSQL queries can help you automate tasks and make your job easier.
There are many benefits of using TSQL queries, but here are just a few:
1. Get insights into your database
2. Troubleshoot performance issues
3. Automate tasks
4. Make your job easier
1 2 |
--Run below script to any database, you will get result for all the database DBCC SQLPERF(LOGSPACE) |
1 2 |
--Run the below script to get the free space on the disks. exec xp_fixeddrives |
1 |
SELECT @@VERSION AS SQLVersion |
1 |
SELECT sqlserver_start_time from sys.dm_os_sys_info |
1 2 3 4 5 |
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) |
1 2 3 4 5 6 7 8 9 |
USE [AdventureWorks2017] GO 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; GO |
1 |
select * from sys.dm_exec_sessions; |
1 |
exec sp_who2; |
1 |
SELECT * FROM sys.sysprocesses WHERE blocked <> 0; |
1 |
exec sp_who2; |
1 2 |
SELECT name, database_id, create_date,* FROM sys.databases where state_desc = 'offline'; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, d.name AS 'DatabaseName', d.database_id as ID, d.state_desc as Status, MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupDate END) AS 'Full DB Backup Status', MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupDate END) AS 'Differential DB Backup Status', MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupDate END) AS 'Transaction DB Backup Status', CASE d.recovery_model WHEN 1 THEN 'Full' WHEN 2 THEN 'Bulk Logged' WHEN 3 THEN 'Simple' END RecoveryModel FROM MASTER.sys.databases d LEFT OUTER JOIN (SELECT database_name, TYPE, MAX(backup_start_date) AS LastBackupDate FROM msdb.dbo.backupset GROUP BY database_name, TYPE) AS bu ON d.name = bu.database_name GROUP BY d.Name, d.recovery_model,d.database_id, d.state_desc |
1 |
exec sp_helpindex '[dbo].[ErrorLog]' -- Put table name here |
1 2 3 4 |
SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('[Person].[EmailAddress]'); -- Put table name here |
1 2 3 4 5 6 7 8 9 10 |
use [AdventureWorks2017] -- Put database name here GO 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' GO |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 ORDER BY FRAGMENT_PERCENT desc |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 |
1 2 3 4 5 6 7 |
SELECT percent_complete ,r.estimated_completion_time/1000 "estimated_completion_time_in_second",* FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id and s.login_name not like '%BeheraH' and s.status='running' |
1 2 3 4 5 6 |
Use [AdventureWorks2017] -- Put database name here Go select s.name,l.name from msdb..sysjobs s left join master.sys.syslogins l on s.owner_sid = l.sid |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Use [AdventureWorks2017] -- Put database name here Go 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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%' |
1 2 3 4 |
-- Check Size: select name,SUM(size)*1.0/128 AS [Size in MB] from tempdb.sys.database_files group by name; exec sp_spaceused; |
1 2 3 4 |
-- If tempdb is not releasing space, then execute below two commands.: a) DBCC FREEPROCCACHE b) dbcc shrinkfile (tempdev, 'target size in MB') |
1 2 3 4 5 6 7 |
-- 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 |
1 2 3 4 5 6 7 8 9 |
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 |
1 2 3 4 5 |
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 |
1 2 3 4 5 |
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 |
1 2 |
select spid,loginame,DB_NAME(dbid) AS DB_NAME ,status from sys.sysprocesses where DB_NAME(dbid)='DB_Name'; |
1 2 |
select * from sys.configurations where name ='user connections' |
1 2 |
select * from sys.dm_os_performance_counters where counter_name ='User Connections'; |
1 2 3 4 5 6 7 8 9 10 |
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 |
1 2 3 4 5 6 |
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 |
1 2 3 4 5 6 7 8 9 10 |
USE master GO ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO EXEC master..sp_renamedb 'DBName','DBNewName' GO ALTER DATABASE DBNewName SET MULTI_USER |
1 2 3 4 5 |
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 |
1 2 3 4 5 6 |
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) |
1 |
DBCC INPUTBUFFER (session id) |
1 2 3 4 5 6 7 8 9 10 |
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 |
1 2 3 4 5 6 7 8 |
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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 |
1 2 3 4 5 6 7 8 9 10 11 12 |
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 |
1 2 3 4 5 6 7 8 9 10 11 12 |
--Step: 1 ALTER DATABASE MODIFY FILE ( NAME = , FILENAME = 'E:\MSSQL.1\MSSQL\Data\datafile1.mdf'); ALTER DATABASE MODIFY FILE ( NAME = , FILENAME = 'F:\MSSQL.1\MSSQL\Log\Logfile.ldf'); --Step 2: ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE; --Step 3: Copy data file/log file to new location. --Step 4: ALTER DATABASE SET ONLINE; |
1 2 3 4 5 6 7 8 9 10 |
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 |
1 |
select * from sys.dm_os_process_memory; |
1 2 3 4 5 6 7 8 9 |
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; |
1 2 |
Select * from sys.dm_tran_locks; Select * from sys.dm_os_wait_stats; |
1 |
ALTER LOGIN <login Name> WITH PASSWORD = '<PasswordHere>'; |
1 2 3 4 5 |
SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('dbo.account') GO |
1 2 3 |
SELECT OBJECT_NAME(OBJECT_ID),definition FROM sys.sql_modules WHERE definition LIKE '%' + 'AC__CUST_ADDR' + '%' order by 1; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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 |
1 |
select * from syscomments where text like '% select * into SQLlist%' |
1 2 3 4 |
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:\%' |
1 2 3 4 5 6 7 8 9 10 11 12 |
sp_CONFIGURE 'show advanced', 0 GO RECONFIGURE GO sp_CONFIGURE GO sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE GO |
1 |
ALTER DATABASE [DB Name] SET OFFLINE WITH ROLLBACK IMMEDIATE; |
1 2 |
SELECT * FROM sys.schemas WHERE principal_id = USER_ID('id) ALTER AUTHORIZATION ON SCHEMA::db_datareader TO db_datareader; |
1 |
EXEC ('DBCC USEROPTIONS WITH NO_INFOMSGS') |
1 2 |
ALTER DATABASE OurTechIdeasDB -- DB Namne SET READ_COMMITTED_SNAPSHOT ON |
Script:
1 2 3 4 5 6 7 8 9 10 |
SELECT is_read_committed_snapshot_on ,name FROM master.sys.databases WHERE name NOT IN ( 'master' ,'model' ,'tempdb' ,'msdb' ) AND is_read_committed_snapshot_on <> 1 |
Output:
Script:
1 2 3 4 5 6 7 8 9 10 |
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 |
Output:
Script:
1 2 |
<span class="SQLKeyword">SELECT</span> <span class="SQLOperator">*</span> <span class="SQLKeyword">FROM</span> sys<span class="SQLOperator">.</span>dm_os_sys_info |
Output:
Script:
1 2 3 4 5 6 7 8 |
SELECT 'alter database ' + name + ' set offline with rollback immediate;' FROM sys.databases WHERE name NOT IN ( 'master' ,'msdb' ,'tempdb' ,'model' ); |
Output:
1 2 3 4 5 6 7 |
alter database AdventureWorks2008 set offline with rollback immediate; alter database AdventureWorks2008R2 set offline with rollback immediate; alter database AdventureWorks2012 set offline with rollback immediate; alter database AdventureWorks2014 set offline with rollback immediate; alter database AdventureWorks2016 set offline with rollback immediate; alter database AdventureWorks2017 set offline with rollback immediate; alter database OurTechIdeasDB set offline with rollback immediate; |
There are a number of ways to troubleshoot TSQL queries. Here are some tips:
-Read the error message carefully and try to understand what it is saying.
-Check the syntax of your query carefully. Make sure all keywords are spelled correctly and that all parentheses and quotation marks are in the right places.
-If you’re using variables, make sure they are declared correctly and that they have the right data type.
-Make sure the table you’re querying exists and that you have permission to access it.
-If you’re querying a remote server, check that the connection is working and that you have the right permissions.
-Check that the data you’re expecting is actually in the table you’re querying. Sometimes data can be missing or incorrect.
-Try running your query in a different environment, such as in a test database, to see if that makes any difference.
-If all else fails, ask for help from a more experienced DBA or developer.
Great , Good Job .. Thanks for sharing
Thanks
Actually when someone doesn’t know after that its up to other visitors that they will help, so here
it happens.
Thanks so much pertaining to giving us an update on this subject
matter on your web-site. Please know that if a brand-new post appears or in the event any adjustments occur with the current posting, I would want to consider reading
more and learning how to make good usage of those tactics
you write about. Thanks for your efforts and consideration of others by making this website available.
Ahaa, its nice discussion about this article at this place at this website, I have read all that,
so now me also commenting here.
Hi there would you mind stating which blog platform you’re
using? I’m looking to start my own blog in the
near future but I’m having a difficult time deciding between BlogEngine/Wordpress/B2evolution and Drupal.
The reason I ask is because your design and style seems different then most blogs and I’m
looking for something unique. P.S My apologies for getting off-topic but I had to ask!
What’s up, its good article about media print, we all
be familiar with media is a wonderful source of facts.
Thanks
I believe you have remarked some very interesting details,
thank you for the post.
Thanks
Good One
Thank you !! 🙂
Google
Very handful of internet websites that come about to become detailed below, from our point of view are undoubtedly properly worth checking out.