Some Notes, Some Scripts
These are the noted taken for searching solutions and some scripts to identify the problem
Categories:
SELECT TOP 50qs.creation_time AS [Cached Time],qs.last_execution_time AS [Last Exec Time],SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(qt.text)ELSE qs.statement_end_offsetEND – qs.statement_start_offset)/2)+1) AS SQL,qp.query_plan AS [Query Plan],qs.execution_count AS [Exec Cnt],CONVERT(DECIMAL(10,5),IIF(datediff(second,qs.creation_time,qs.last_execution_time) = 0,NULL,1.0 * qs.execution_count /datediff(second,qs.creation_time,qs.last_execution_time))) AS [Exec Per Second],(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count […]
Categories:
When indirect checkpoints are enabled on a database receiving a large number of DML operations, the background writer can start aggressively flushing dirty buffers to disk to ensure that the time required to perform recovery is within the […]
Categories:
sys.dm_os_performance_counters views counters with values.
Categories:
SELECTer.session_id,er.request_id,DB_NAME(er.database_id) as [database],er.start_time,CONVERT(DECIMAL(21,3),er.total_elapsed_time / 1000.) AS[duration],er.cpu_time,SUBSTRING(qt.text,(er.statement_start_offset / 2) + 1,((CASE er.statement_end_offsetWHEN -1 THEN DATALENGTH(qt.text)ELSE er.statement_end_offsetEND – er.statement_start_offset) / 2) + 1) AS [statement],er.status,er.wait_type,er.wait_time,er.wait_resource,er.blocking_session_id,er.last_wait_type,er.reads,er.logical_reads,er.writes,er.granted_query_memory,er.dop,er.row_count,er.percent_complete,es.login_time,es.original_login_name,es.host_name,es.program_name,c.client_net_address,ib.event_info AS [buffer],qt.text AS [sql],TRY_CONVERT(XML,p.query_plan) as [query_plan]FROMsys.dm_exec_requests er WITH (NOLOCK)OUTER APPLY sys.dm_exec_input_buffer(er.session_id, er.request_id) ibOUTER APPLY sys.dm_exec_sql_text(er.sql_handle) […]
Categories:
select *from sys.dm_os_wait_statsWHERE [wait_type] NOT IN (N’CLR_SEMAPHORE’, N’LAZYWRITER_SLEEP’,N’RESOURCE_QUEUE’, N’SQLTRACE_BUFFER_FLUSH’,N’SLEEP_TASK’, N’SLEEP_SYSTEMTASK’,N’WAITFOR’, N’HADR_FILESTREAM_IOMGR_IOCOMPLETION’,N’CHECKPOINT_QUEUE’, N’REQUEST_FOR_DEADLOCK_SEARCH’,N’XE_TIMER_EVENT’, N’XE_DISPATCHER_JOIN’,N’LOGMGR_QUEUE’, N’FT_IFTS_SCHEDULER_IDLE_WAIT’,N’BROKER_TASK_STOP’, N’CLR_MANUAL_EVENT’,N’CLR_AUTO_EVENT’, N’DISPATCHER_QUEUE_SEMAPHORE’,N’TRACEWRITE’, N’XE_DISPATCHER_WAIT’,N’BROKER_TO_FLUSH’, N’BROKER_EVENTHANDLER’,N’FT_IFTSHC_MUTEX’, N’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,N’DIRTY_PAGE_POLL’, N’SP_SERVER_DIAGNOSTICS_SLEEP’)order by wait_time_ms desc;
Categories:
Don’t consolidate database with higher risk and having mission critical issues. Because Auditing and Encyption issues requires more performance issues and affects others…
Categories:
Don’t consolidate OLTP and reporting databases on the same server.
Categories:
Make sure that Page Verify option is set to CHECKSUM. This will detect consistency errors more efficiently and helps to resolve database corruption cases.
Categories:
Auto Close, controls how SQL Server caches data from the database. When it’s enabled, SQL Server removes data pages from the buffer pool and execution plans from the plan cache when the databasedoes not have any active connections. […]