Some Notes, Some Scripts

These are the noted taken for searching solutions and some scripts to identify the problem

Categories:

SELECTCONVERT(DECIMAL(12,3),SUM(user_object_reserved_page_count) / 128.)AS [User Objects (MB)],CONVERT(DECIMAL(12,3),SUM(internal_object_reserved_page_count) / 128.) AS [Internal Objects (MB)],CONVERT(DECIMAL(12,3),SUM(version_store_reserved_page_count) / 128.) AS [Version Store (MB)],CONVERT(DECIMAL(12,3),SUM(unallocated_extent_page_count) / 128.) AS [Free Space (MB)]FROMtempdb.sys.dm_db_file_space_usage WITH (NOLOCK);

Categories:

Steps are: Suspend your database to close data movements Change Readable secondary Nodes to “NO” Move Your Datafiles Alter database <database_name> MODIFY FILE (NAME = <logical file_name>, FILENAME = <physical_file_name>) Close SQL Service Move Datafiles to specified path […]

Categories:

SELECT total_physical_memory_kb / 1024 AS [Physical Memory (MB)],available_physical_memory_kb / 1024 AS [Available Memory (MB)],total_page_file_kb / 1024 AS [Page File Commit Limit (MB)],available_page_file_kb / 1024 AS [Available Page File (MB)],(total_page_file_kb – total_physical_memory_kb) / 1024AS [Physical Page File Size (MB)],system_cache_kb […]

Categories:

This performance counter shows the amount of memory SQL Server currently uses.

Categories:

Start with 50, and keep on checking (scripts to detect CPU expensive queries will help)

Categories:

DECLARE @now BIGINT;SELECT @now = cpu_ticks / (cpu_ticks / ms_ticks)FROM sys.dm_os_sys_info WITH (NOLOCK);;WITH RingBufferData([timestamp], rec)AS(SELECT [timestamp], CONVERT(XML, record) AS recFROM sys.dm_os_ring_buffers WITH (NOLOCK)WHEREring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’ ANDrecord LIKE N’%%’),Data(id, SystemIdle, SQLCPU, [timestamp])AS(SELECTrec.value(‘(./Record/@id)[1]’, ‘int’),rec.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’,’int’),rec.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’,’int’),[timestamp]FROM RingBufferData)SELECT TOP 256dateadd(MS, -1 * (@now […]

Categories:

SQL Server captures and persists runtime statistics and execution plans of the queries in the database. It shows how the execution plans perform and how they evolve over time. Finally, it allows you to forcespecific execution plans to […]

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:

SELECT parent_node_id ,COUNT(*) as [Schedulers] ,SUM(current_tasks_count) as [Current] ,SUM(runnable_tasks_count) as [Runnable] FROM sys.dm_os_schedulers WHERE status = ‘VISIBLE ONLINE’ GROUP BY parent_node_id;

Categories:

There are four different types of checkpoints: Internal checkpointsInternal checkpoints occur during some SQL Server operations, such as starting database backup or creating a database snapshot.Manual checkpointsManual checkpoint occur manually, as the name indicates, when users trigger them […]