-
Free List Stall per Second
Free List Stall per Second: Its a counter available in SQL Buffer Manager. If SQL Server don’t have free pages in buffer cache then a processing request goes in stall and it must wait until pages get free in buffer cache. The recommended value of Free List per Seconds counter is below 2. If there…
-
SQL Server Services
Below are the SQL Server services which might come after SQL installation based on your selection… • SQL Server • SQL Server Agent • Other SQL Services • SQL Server Browser • SQL Server Full Text Search • SQL Server Integration Services • SQL Server Reporting Services • SQL Server Analysis Services
-
SQL Server Database Transaction Log Truncation
Transaction Log Truncation If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees space in the logical log for reuse by the transaction log. Except when delayed for some reason, log truncation occurs automatically…
-
T-SQL For SQL Server Memory Details…
SQL Server Buffer Cache details:- ———————————————————————————————- select [DateTimePerformed] = SYSDATETIMEOFFSET() , Server_Physical_Mem_MB = os.[Server Physical Mem (MB)] — SQL2012+ only , c.[Min_Server_Mem_MB] , c.[Max_Server_Mem_MB] , p.PLE_s , p.Stolen_Mem_MB , ‘Churn (MB/s)’ = cast((p.Total_Server_Mem_GB)/1024./NULLIF(p.PLE_s,0) as decimal(19,2)) , Server_Available_physical_mem_GB = (SELECT cast(available_physical_memory_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_sys_memory) , SQL_Physical_memory_in_use_GB = (SELECT cast(physical_memory_in_use_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_process_memory) , p.Total_Server_Mem_GB –May be more or less than memory_in_use because it , p.Target_Server_Mem_GB from ( select InstanceName = @@SERVERNAME , Target_Server_Mem_GB = max(case counter_name when ‘Target Server Memory (KB)’ then convert(decimal(19,3), cntr_value/1024./1024.) end) , Total_Server_Mem_GB = max(case counter_name when ‘Total Server Memory…
-
Important DMVs for Database Mirroring…
Below are few important DMVs for Database mirroring…. select * from sys.dm_db_mirroring_connectionsselect connection_id,* from sys.dm_exec_connectionsselect * from sys.database_mirroringselect * from sys.database_mirroring_endpointsselect * from sys.database_mirroring_witnesses
-
SQL Server AlwaysON Health Check…
–Below SQL Script can we use for check SQL Server AG HEALTH USE [TempDB] SET NOCOUNT ON GO SELECT CurrentTime_UTC = SYSUTCDATETIME() DECLARE @XELTarget VARCHAR(MAX); DECLARE @XELPath VARCHAR(MAX); DECLARE @XELFile VARCHAR(max); IF EXISTS(SELECT name FROM sys.dm_xe_sessions WHERE name = ‘AlwaysOn_Health’) BEGIN SELECT @XELTarget = cast(xet.target_data AS XML).value(‘(EventFileTarget/File/@name)[1]’, ‘VARCHAR(MAX)’) FROM sys.dm_xe_sessions xes INNER JOIN sys.dm_xe_session_targets xet ON xes.address = xet.event_session_address WHERE xet.target_name = ‘event_file’ and xes.name = ‘AlwaysOn_Health’ SELECT @XELPath = REVERSE(SUBSTRING(REVERSE(@XELTarget), CHARINDEX(‘\’, reverse(@XELTarget)), LEN(@XELTarget)+1- CHARINDEX(‘\’, REVERSE(@XELTarget)))) SELECT @XELFile = @XELPath + ‘AlwaysOn_health*.xel’ IF @XELFile IS NULL BEGIN PRINT ‘Unable to find XEVent target files for AlwaysOn_Health XEvent session’ PRINT ‘Expected AOHealth XEvent files in this location:’ PRINT @XELPath RETURN END END ELSE BEGIN PRINT ‘No AlwaysOn Health XEvent…
-
SQL Server tempDB usage…
–Below script is for check tempDB usage ——————————————————————————– use tempdb go select * from Sys.dm_db_file_space_usage –Is the version store taking up a lot of space? SELECT version_store_Gb = (SUM(version_store_reserved_page_count)*8/1024./1024.) , total_tempdb_Gb = sum(total_page_count)*8/1024./1024. , pct_of_tempdb_dedicated_to_version_store = round((sum(version_store_reserved_page_count)*1./sum(total_page_count)*1.)*100.,2) FROM sys.dm_db_file_space_usage; –If version store is very large, need to find out what transactions are holding onto it. –It is not possible to associate versionstore allocation to individual sessions –But these queries…
-
SQL Server Installation Type on a Server
There are basically two types of installation in SQL Server… In Stand alone Installation again there are two different ways… SQL Server Services Configuration Manager Tool view for different installation…
-
Basic Tools in SQL Server
Basic Tools in SQL Server