Some Tips and Scripts

These are notes taken to solve problems and some very usefull scripts to detect problems or troubleshootings. 

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.) […]
Steps are: Suspend your database to close data movementsChange Readable secondary Nodes to “NO”Move Your DatafilesAlter database <database_name> MODIFY FILE […]
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many […]
;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. * […]
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 […]
This performance counter shows the amount of memory SQL Server currently uses.
This performance counter indicates the ideal amount of memory SQL Server should consume. It depends on configuration settings, the total […]
Start with 50, and keep on checking (scripts to detect CPU expensive queries will help)
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 […]
The more compilations SQL Server performs, the more load the Query Store must handle. In particular, the Query Store may […]