Take care with dangerous SQL Server builds

Category: Reliability Item: Dangerous build of SQL Server Why should you care about your build version of SQL Server? There are known bugs and threats in specific build versions of SQL Server. You may experience data corruption in the clustered indexes and high-security issues. The online index rebuild can cause index corruption or data loss when it

Disabling Windows automatic updates

Category: Reliability Item: OS Updates settings Why should you care about them? Windows updates are set to a non-optimal setting. Updates can start downloading at an unpredictable time and cause hard to trace performance issues. How can I check it? Go to Start, and search for “Windows Update settings”: Go to “Choose how updates get

How can you find the SQL Server jobs that start simultaneously?

Category: Performance Item: SQL Agent jobs starting simultaneously. Why should you care about them? Multiple SQL Server Agent jobs are configured to start simultaneously, which will not perform as fast as if jobs would not overlap. Your SQL Server may be under heavy load for short bursts of time. How can I find the SQL Server jobs

Finding user tables in SQL Server system databases

Category: Reliability Item: User tables in sys DBs Why should you care about them? User datatables  should not be  in the system databases (master, msdb). In case of failure, there is a high chance that those tables would be lost. In the case of migration, these tables may be forgotten and not restored. It shows that some

When was the last time your SQL Server wait stats was cleared?

Category: Performance Item: Wait statistics What are SQL Server wait statistics? They are data about all the waits encountered by SQL Server threads at the instance level. Using the DMV sys.dm_os_wait_statistics, you can return information that SQL Server is permanently tracking – Why execution threads have to wait. Why should you care about them? You should not

Finding heap tables in your SQL Server database

Category: Performance Item: Active tables without clustered IX What is a Heap in SQL Server table? A table without a clustered index is called Heap. It consists only of data pages. Note: You can have one or more nonclustered indexes this type of table. Why should you care about them? On tables without clustered and nonclustered indexes,

Are your SQL Server generating dump files?

Category: Reliability Item: SQL Server memory dumps What are the SQL Server dump files? They are memory dumps files generated when SQL hits a bug, or something “unexpected” happens. The size and contents of which are dependent on the problem that occurred. Note: Memory dump files may contain sensitive information. Should you care about SQL Server dumps?

How many CPUs are your SQL Server able to use?

Category: Performance Item: CPU schedulers offline What is a CPU Scheduler in SQL Server? A query that you want to execute represents a task, your work. The execution context of your work is called a worker. A task is assigned to a worker, which is assigned to a scheduler for execution on the CPU. The manager of

Why should you care about the database compatibility level in SQL Server?

What is the database compatibility level? A compatibility level is an option associated with each database. It controls some database behaviors to be compatible with a particular version of SQL Server. Why should you care about the compatibility level? In addition to the new features, there are changes associated with database compatibility levels and cardinality estimator versions when

Why should you care about CLR integration in SQL Server?

What is the CLR integration on SQL Server? Included in SQL Server 2005, the CLR is the heart of the Microsoft .NET framework. The common language runtime (CLR), also know as SQLCLR, allows you to create database objects (functions, triggers, store procedures, etc.) using .Net Framework (managed code). CLR integration offers the potential for improved

Best practices to keep your SQL Server backups safe

Did somebody drop a database in production instead of development? Did you run the wrong DELETE statement? Let’s follow these best practices and recommendations for SQL Server backups to make the crisis easier when the catastrophe strikes. Set up a proper maintenance plan Use a proper recovery mode, understand the business needs of your application.

Improve your SQL Server transaction log performance using Delayed Durability

What is the Delayed Durability in SQL Server? This is a dangerous feature introduced in SQL Server 2014. Also known as lazy commit, It is a storage optimization that returns a successful commit before transaction logs are saved to a drive. In some cases, the feature can make a significant performance difference when correctly –

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.