Enrique | SQL Conjuror

No comments

SQL Server- To See Number of Connections from Each Server Connecting to the Database

By Enrique 18/01/2016 03/05/2020 SQL SERVER, Database Administration

SELECT DB_NAME(dbid) AS DBName,

COUNT(dbid) AS NumberOfConnections,

loginame AS LoginName,

nt_domain AS NT_Domain,

nt_username AS NT_UserName,

hostname AS HostName

FROM sys.sysprocesses

WHERE dbid > 0

GROUP BY dbid,

hostname,

loginame,

nt_domain,

nt_username

ORDER BY NumberOfConnections DESC;

SQL Server – DMV to View Missing Indexes

By Enrique 13/11/2015 03/05/2020 SQL SERVER, Database Administration, Performance Optimization

--DMV to view missing indexes

SELECT

FROM

sys.dm_db_missing_index_group_stats AS igs

JOIN

sys.dm_db_missing_index_groups AS ig ON igs.group_handle = ig.index_group_handle

JOIN

sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle

SQL Server – Get List of Index Information

By Enrique 13/11/2015 06/05/2020 SQL SERVER, Performance Optimization

-- Ensure a USE statement has been executed first.

SELECT [DatabaseName]

,[ObjectId]

,[ObjectName]

,[IndexId]

,[IndexDescription]

,CONVERT(DECIMAL(16, 1), (SUM([avg_record_size_in_bytes] * [record_count]) / (1024.0 * 1024))) AS [IndexSize(MB)]

,[lastupdated] AS [StatisticLastUpdated]

,[AvgFragmentationInPercent]

FROM (

SELECT DISTINCT DB_Name(Database_id) AS 'DatabaseName'

,OBJECT_ID AS ObjectId

,Object_Name(Object_id) AS ObjectName

,Index_ID AS IndexId

,Index_Type_Desc AS IndexDescription

,avg_record_size_in_bytes

,record_count

,STATS_DATE(object_id, index_id) AS 'lastupdated'

,CONVERT([varchar](512), round(Avg_Fragmentation_In_Percent, 3)) AS 'AvgFragmentationInPercent'

FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'detailed')

WHERE OBJECT_ID IS NOT NULL

AND Avg_Fragmentation_In_Percent <> 0

) T

GROUP BY DatabaseName

,ObjectId

,ObjectName

,IndexId

,IndexDescription

,lastupdated

,AvgFragmentationInPercent

ORDER BY AvgFragmentationInPercent DESC

SQL Server – Get Index Sizes

By Enrique 13/11/2015 04/05/2020 SQL SERVER, Database Administration

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

i.index_id AS IndexID,

8 * SUM(a.used_pages) AS 'Indexsize(KB)'

FROM sys.indexes AS i

JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY i.OBJECT_ID,i.index_id,i.name

ORDER BY [Indexsize(KB)] desc

SQL Server – View Fragmentation Level

By Enrique 10/11/2015 06/05/2020 SQL SERVER, Performance Optimization

SELECT

OBJECT_NAME(ix.OBJECT_ID) AS TableName,

ix.name AS IndexName,

ixs.index_type_desc AS IndexType,

ixs.avg_fragmentation_in_percent

FROM

sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ixs

INNER JOIN

sys.indexes ix ON ix.object_id = ixs.object_id AND ixs.index_id = ixs.index_id

WHERE

ixs.avg_fragmentation_in_percent > 30

ORDER BY

ixs.avg_fragmentation_in_percent DESC

SQL Server – Identifying Locks

By Enrique 10/11/2015 04/05/2020 SQL SERVER, Database Administration

--Stored procedure to display current locks/process

exec sp_lock

--Stored procedure to display current activity/process

exec sp_who2

--DMV to view locking information

SELECT * FROM sys.dm_tran_locks

--DMV to view blocked transactions

SELECT * FROM sys.dm_exec_requests WHERE status = 'suspended'

--Trace flag to log deadlocks

DBCC TRACEON (1222, -1)

SQL Server – List All Functions

By Enrique 06/11/2015 04/05/2020 SQL SERVER, Database Administration

select name, definition, type_desc FROM sys.sql_modules m

INNER JOIN sys.objects o ON m.object_id=o.object_id

where type_desc like '%function%'

ORDER BY Name

--OR

SELECT *

FROM sys.objects

WHERE RIGHT(type_desc, 8) = 'FUNCTION'

SQL Server -DMV to View Index Fragmentation

By Enrique 06/11/2015 04/05/2020 SQL SERVER, Database Administration

--DMV to View Index fragmentation

SELECT

OBJECT_SCHEMA_NAME(i.object_id),

OBJECT_NAME(i.object_id),

FROM

sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL) ips

JOIN

sys.indexes i ON ips.OBJECT_ID = i.OBJECT_ID AND ips.index_id = i.index_id

SQL Server – TOP 5 Queries with Avg CPU Time

By Enrique 04/11/2015 06/05/2020 SQL SERVER, Performance Optimization

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY total_worker_time/execution_count DESC;

SQL Server – DMV’s

By Enrique 04/11/2015 04/05/2020 SQL SERVER, Database Administration

--sys.dm_os_ = SQL Server OS Information

SELECT * FROM sys.dm_os_wait_stats

--sys.dm_io_ = i/O Information

SELECT * FROM sys.dm_io_pending_io_requests

--sys.dm_exec_ = Session & Query Information

SELECT * FROM sys.dm_exec_sessions

SELECT * FROM sys.dm_exec_requests

--sys.dm_db__ = Database & Index Information

SELECT * FROM sys.dm_db_task_space_usage

SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')

Facebook

Google

Twitter

Author: Enrique

SQL Server- To See Number of Connections from Each Server Connecting to the Database

SQL Server – DMV to View Missing Indexes

SQL Server – Get List of Index Information

SQL Server – Get Index Sizes

SQL Server – View Fragmentation Level

SQL Server – Identifying Locks

SQL Server – List All Functions

SQL Server -DMV to View Index Fragmentation

SQL Server – TOP 5 Queries with Avg CPU Time

SQL Server – DMV’s

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud