SELECT servicename, startup_type_desc, status_desc, last_startup_time, service_account, is_clustered, cluster_nodename, filename, startup_type, status, process_idFROM sys.dm_server_services AS
Continue readingCategory: SQL Server -DBA
SQL Server -DBA, SQL Server Services, who dropped or altered tables,procedures or databases in SQL Server, What in SQL Server
How Many CPU Support By SQL Server Web edition ?
SQL Server Web edition supports only 4 processors.
Continue readingTOP 5 CPU-CONSUMING Queries In SQL SERVER
SELECT TOP 5 qs.total_worker_time/(qs.execution_count*60000000) as [Avg CPU Time in mins], qs.execution_count, qs.min_worker_time/60000000 as [Min CPU Time in mins], –qs.total_worker_time/qs.execution_count,
Continue readingHow to find longest running query In Sql Server
SELECT DISTINCT TOP 3 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, –ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime, s.creation_time
Continue readingTop 10 worst performing Queries in SQL SERVER
SELECT TOP 5 obj.name, max_logical_reads, max_elapsed_time FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd INNER JOIN sys.sysobjects obj on hnd.objectid
Continue readingPerformance Tuning :- How to identify blocking And Lead blocker in SQL Server
During normal processing in a database, it is blocked. Blocking is “by design” because your database is on ACID.
Continue readingHow to find index usage in Sql Server
SELECT OBJECT_NAME(ddius.[object_id], ddius.database_id) AS [object_name] , ddius.index_id , ddius.user_seeks , ddius.user_scans , ddius.user_lookups , ddius.user_seeks + ddius.user_scans + ddius.user_lookups
Continue readinghow to find unused database indexes in SQL SERVER ?
SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] , i.name FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
Continue readingHow to find who are directly connected to the SQL Server instance ?
SELECT dec.client_net_address , des.host_name , dest.text FROM sys.dm_exec_sessions des INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id CROSS APPLY
Continue reading