Some Notes, Some Scripts

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

Categories:

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled […]

Categories:

;WITH BufPoolStatsAS(SELECTdatabase_id,COUNT_BIG() AS page_count ,CONVERT(DECIMAL(16,3),COUNT_BIG() * 8 / 1024.) AS size_mb,AVG(read_microsec) AS avg_read_microsecFROMsys.dm_os_buffer_descriptors WITH (NOLOCK)GROUP BYdatabase_id)SELECTDB_NAME(database_id) AS [DB],size_mb,page_count,avg_read_microsec,CONVERT(DECIMAL(5,2), 100. * (size_mb / SUM(size_mb) OVER()))AS [Percent]FROMBufPoolStatsORDER BYsize_mb DESCOPTION (MAXDOP 1, RECOMPILE);

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:

This performance counter indicates the ideal amount of memory SQL Server should consume. It depends on configuration settings, the total amount of memory available to the OS, and a few other factors.

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:

The more compilations SQL Server performs, the more load the Query Store must handle. In particular, the Query Store may not work very well in systems that have a very heavy, ad-hoc, non-parameterized workload.

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 […]