Some Notes, Some Scripts
These are the noted taken for searching solutions and some scripts to identify the problem
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:
Sysadmin can run any scripts on SQL Server. What if S/he runs scripts with bad results. Can you detect it? Yes, Just Alter your Audit specification: ALTER SERVER AUDIT [Audit-xxxxxx] WHERE ([object_name]=’sysadmin’)
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 […]
Categories:
Do not spread clustered indexes, and nonclustered indexes, or large object (LOB) data across multiple filegroups. This rarely helps with performance and may introduce issues in cases of database corruption.
Categories:
Create multiple data files in filegroups with volatile data. Make sure that all data files have the same sizeand auto-growth parameters specified in MB; enable the AUTOGROW_ALL_FILES option.
Categories:
When this option is enabled, SQL Server periodically shrinks the database and releases unused free space from the files to the OS. But in case of first INSERT database tries to extend depending on growth size you had […]
Categories:
Set MAXDOP to 1/4 of the number of available CPUs in OLTP and half those in Data Warehouse systems. Do not exceed the number of CPUs in the NUMA node.
Categories:
This configuration option controls how SQL Server caches execution plans of ad-hoc (nonparameterized)queries. When it is disabled (by default), SQL Server caches full execution plans of those statements, which may significantly increase plan cache memory usage. As the […]