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