Resource Database Read-only hidden database that contains all the system information Can’t back up the Resource database Must copy paste
Continue readingAuthor:
How to find When was your database last used or accessed ?
Use < your_DatabaseName> GO SELECT DB_NAME() as DatabaseName,s1.sql_handle,(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,( (CASE WHEN statement_end_offset = -1THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)ELSE statement_end_offset END) – statement_start_offset) /
Continue readingFind service account information in SQL Server
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 readingHow 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 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 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 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 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 reading