You may increase your SQL performance enabling optimize for ad hoc workloads

What is the optimize for ad hoc workloads option? When you write a query in SQL Server, the query optimizer needs to create a “Plan” of how it is going to execute that query. Usually, SQL Servers save that plan in the plan cache to be reused again at a later date. Optimize for Ad Hoc

Best practices for disk storage on SQL Server

The disk is the slowest part of the computer. Proper configuration storage systems are critical to the optimal performance and operation of SQL Server. Below are some of the most common best practices for storage that Microsoft recommends to improve SQL Server performance, reliability, and security. Disk Hardware  Over the past five decades, Hard Disk

Keep your jobs safe using SQL Agent notifications

Category: Reliability Item:  SQL Agent Jobs without notifications SQL Server Agent permits you to run a wide variety of tasks within SQL Server. Its built-in notification system allows you to define operators and contact them when a job fails. Being able to resolve quickly when an Agent job failed is better than having someone else

Is your SQL Server database transaction log too Large?

It is not common to have files larger than data files. Usually, transaction logs have 10-60% of the size of the data files. This may indicate some issues: A possible bad maintenance plan (Backups are not being performed correctly). Log-based replication can be broken (Database mirroring, replication, or AlwaysOn Availability Groups); Someone begins a transaction

Untrusted foreign keys and constraints can degrade your SQL Server performance

Disable keys and constraints to improve the performance of load data is a common practice in large data warehouses and other environments with big tables. This technique is relatively more natural and safer than dropping and recreating all the keys and constraints if you don’t forget to enable them after the work has been done.

Disabling Windows visual effects to boost performance

Category: Performance Item:  Windows visual Effects not optimal By default,  Windows is optimized for a beautiful display, but this is not very important for a server. These visual effects may cause a virtual machine to appear to run slower. They are not required and consume CPU resources that would be spent in your SQL Server

Improve SQL Server performance enabling Instant File Initialization

Category: Performance Item: Instant File Initialization access right What’s Instant file initialization (IFI)? Instant file initialization is a Windows feature that enables your  SQL Server to skip the zero-writing step and begin using the allocated space immediately for data files. IFI allows SQL Server to reduce database creation required time (including TEMPDB at server startup), data

Never use the full recovery model without a backup

Category: Reliability Item:  Full Recovery Mode without backup logs Why should you care about it? If you are using a database is in Full Recovery Model or Bulk Logged Recovery Model, SQL Server do not free up the log file when your transactions finish. Your server may run out of disk space if your SQL Server database

Be careful with queries forcing index, order, and join hints

Why should you care about SQL Server query hints? Hints in SQL queries is like a double-edged sword. While you might find a better query plan based on the current data, you are taking away SQL Server’s ability to adapt to changes. Every time enough data in one of the tables has changed, SQL Server,

How to find slow SQL queries using DMVs in SQL Server?

What is Dynamic management views (DMVs)? Introduced within SQL Server 2005, Dynamic management views (DMVs) are significant features of SQL Server. There are several DMVs that provide data about execution plans, query stats, recent queries and so on. These can be used together to determine what is going on with a SQL Server instance. In

Why should you care about users with elevated permissions in SQL Server?

The security model offered by Microsoft SQL Server is highly configurable and very robust when all security best practices are followed. Why should you care about it? The least-privileged user account (LUA) approach is an essential part of a defensive, in-depth strategy for countering security threats. LUA says that a user must be granted only

Improve your SQL Server performance enabling data compression

What is the data compression feature in SQL Server? It is a feature introduced in SQL Server 2008 Enterprise Edition. Later, Microsoft added to Standard Edition of SQL Server 2016 SP1 and higher. Data compression can improve your SQL server performance, reducing I/O. It support tables, clustered index, non-clustered index. Compression requires a bit more

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.