Category: Performance Tuning
-
Counters for Monitoring SQL Server Performance
Below are the top few counters which we need to monitor to know SQL Server performance. On the basis of optimal values of the counters, we can identify performance of SQL Server… They are… 1. SQLServer: Buffer Manager: Buffer Cache hit ratio 2. SQLServer: Buffer Manager: Page life expectancy 3. SQLServer: SQL Statistics: Batch Requests/Sec…
//
-
Database Indexes and Maintenance
SQL Server B-Tree Indexes Root Level Intermediate Level Leaf Level Index Column Selection DMV for analysing Index sys.dm_db_index_physical_stats(null,null,null,null,null) Clustered Index Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can…
//
-
Measure IOPs from SQL Server
Measure IOPs from SQL Server Investigating performance problems, We may need to look at I/O stats as well. Find how the Monitoring I/O on storage system is performing. Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on…
//
-
Top SQL Server Memory Pressure Counters…
Below are the top SQL Server Memory Pressure Counters and their recommended values. Page Life Expectancy – It is an age of a data page in seconds in the buffer cache or buffer memory after querying the tables with the loading data page into the buffer memory. Page Life Expectancy value indicates the memory pressure in allocated…
//
-
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…
//
-
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…
//
-
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…
//
-
Cluster node was removed from the active failover cluster membership
Below was the cluster setup where the issue came… Error messages which logged in Cluster error log… Cluster node ‘ABCD-xyz05’ was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster.…
//
-
Make delete operation fast using CTE.
–drop table t1 create table t1 (a int primary key, b char (100)) declare @i int set @i=1 SET NOCOUNT ON while (@i<100000) begin insert into t1 values (@i,’x’); set @i=@i+1 end ———————————————————- ———————————————————– set statistics io on set statistics time on delete from t1 where a in (select top (10000) a from t1 order by a); set statistics time off set statistics io off ———————————————————– set statistics io on set statistics time on ;with d as ( select top (10000) a from t1 order by a ) delete from d; set statistics time off set statistics io off
//
-
Why are spids in suspended state.
SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, ”) AS login_name ,COALESCE(es.host_name,”) AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,’MISCELLANEOUS’) AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,’MISCELLANEOUS’) AS lastwaittype ,COALESCE(er.wait_resource,”) AS waitresource ,coalesce(db_name(er.database_id),’No Info’) as dbid ,COALESCE(er.command,’AWAITING COMMAND’) AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN ‘Unspecified’ WHEN 1 THEN ‘Read Uncommitted’ WHEN 2 THEN ‘Read Committed’ WHEN 3 THEN ‘Repeatable’ WHEN 4 THEN ‘Serializable’ WHEN 5 THEN ‘Snapshot’ END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,”) AS program_name ,es.login_time FROM sys.dm_exec_sessions es LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1 and es.session_id <> @@spid ORDER BY es.session_id
//
-
SQL Server Black Box Analysis
“SQL Server Black Box Analysis” script give you a MRI report of SQL Server. Haha ha… Script… create table #ServerStats (create_time datetime,component_type sysname,component_name sysname, state int,state_desc sysname, data xml) insert into #ServerStats execute sp_server_diagnostics — Overview — Note: this events component will always show a status of unknown select create_time as “Date”, component_name as “Component”, state_desc as “Status” from #ServerStats — System select ‘System’ as “System”, data.value(‘(/system/@systemCpuUtilization)[1]’,’bigint’) as “System CPU”, data.value(‘(/system/@sqlCpuUtilization)[1]’,’bigint’) as “SQL CPU”, data.value(‘(/system/@nonYieldingTasksReported)[1]’,’bigint’) as “Non-yielding Tasks”, data.value(‘(/system/@pageFaults)[1]’,’bigint’) as “Page Faults”, data.value(‘(/system/@latchWarnings)[1]’,’bigint’) as “LatchWarnings” from #ServerStats where component_name like ‘system’ — Memory select ‘Memory’ as “Memory”, data.value(‘(/resource/memoryReport/entry[@description=”Working…
//