Best practices for the SQL Server ERRORLOG  

Category: Performance Item:  SQL Server Error logs not optimally What’s the SQL Server error log? It’s a file used by SQL server to write data about events that occurred as backups, stack dumps, and a lot of other informational events. Why should you care about it? When the SQLError log gets too big, it’s harder

Best practices for the SQL Server TempDB

Category: Performance Item:  SQL Server Error logs not optimally configured. What is the TempDB database? Tempdb is one of the system databases in Microsoft SQL Server. It’s a global resource that holds temporary user objects, row versioning information and internal objects that are created by the SQL Server engine. SQL Server creates a clean copy of

Why should you care about SQL Agent job’s owner?

Category: Security Item:  SQL Agent jobs owned by non SA account Users leave the organization. When login is disabled, deleted or Active Directory is not available – the job may stop working. The best practices recommend you set all job owners to SA account. Note: You might have a different approach, such as creating an account or

SQL Server – priority boost option

Category: Reliability Item: Priority boost is ON What does priority boost option do? The option will make SQL Server run the sqlservr.exe process and threads as High Priority. It has the potential to drain resources from essential OS & networking functions, resulting in problems and shutting down SQL Server. Microsoft’s best practices do not recommend to enable

Best practices for SQL Server statistics

Category: Performance Item: Auto-update statistics What are statistics in SQL Server? They are large binary objects (BLOBs) that contain information about the correlation and distribution of values in one or more columns of a table or indexed view. The Query Optimizer uses statistics to create query plans that improve query performance. Enable Auto-update It’s a

Optimal Window’s page file settings for SQL Server

Category: Performance Item: Windows OS page file not optimal What is the Windows OS page file? This file is a form of virtual memory. When your memory ram becomes full, Windows moves data from your memory RAM to your hard drive, placing it in the page file (also known as the swap file). You never want your SQL Server

What is the MAXDOP setting in SQL Server?

Category: Performance Item: Default Max degree of parallelism The max degree of parallelism option is used by SQL Server to limit the number of processors to use for parallel plan execution. By default, SQL uses all available CPUs during query execution. Defaults are not good. While this is great for large queries, it can cause

How high VLF counts kill your SQL Performance

Category: Performance Item:  High VLF count What is a Virtual Log File (VLF)? The transaction log file is physically divided internally into several virtual log files (VLFs). This number can grow base on how often the active transactions write to the disk and the auto-growth settings for the log file. With high VLF counts, backups

SQL Server startup parameters

Category: Reliability Item: SQL Engine startup settings What are SQL Server startup parameters? When you install SQL Server, setup writes a set of default startup options in the Microsoft Windows registry that influences how it is started. You can use these startup parameters to specify an alternate path to specific file locations needed during startup

Trace flags in SQL Server

Category: Performance Item:  Trace flags What is a Trace Flag in the SQL server? SQL Server trace flags are configuration handles that can enable or disable a specific SQL Server characteristic or to change a particular SQL Server behavior. Trace flags turn on/off certain hidden SQL Server features that have no User Interface components. Trace

SIMPLE recovery model in SQL Server

Category: Reliability Item:  SIMPLE recovery model usage What is the SIMPLE recovery model in SQL Server? Simple Recovery Model is the basic recovery model available in SQL Server. You cannot back up to an exact given point of time using simple recovery, only till the last Full or Differential backup. The Full Recovery mode, when managed

SQL Server page verification CHECKSUM

Category: Reliability Item:  Page Verification not optimal   What is Page verify in SQL Server? Page Verify is a database option that defines the mechanism used by SQL Server to verify pages consistency when it is written or read from disk. This reduces the potential of corrupting the database and as a good practice should be set to CHECKSUM.

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.