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
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
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
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
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
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,
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?
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
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
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
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.
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 –