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

How to check for updates in SQL Server Management Studio (SSMS)?

Category: reliability Item: SSMS missing updates Microsoft has made SQL Server Management Studio (SSMS) a product on its own and is providing updates directly for the product. It is a good practice to keep your SQL Server Management Studio (SSMS) healthy by updating when needed. Updates are essential, especially from a security point of view.

Should you use the SQL Server change tracking feature?

What is the SQL Server change trackings feature? Change tracking was introduced in SQL Server 2008 to help you sync data. It allows relatively easy access to new, changed, and deleted data, eliminating the need for brute-force comparisons or other costly means of change detection. Should you use Change Tracking? Change Tracking may save you

Indexes can hamper the SQL Server performance too

You’ve probably heard: “indexes are not free”. They are right! Indexes can greatly improve queries performance in the SQL server, but they may hamper the performance too. There are costs in storage (disk space) as well as resources to the maintenance required on DML operations. So, it’s very important to understand your overall workload and

Best practices to enable SQL Server Query Store for a database

What is the Query Store? The SQL Server Query Store is a feature introduced in SQL Server 2016. It provides you with insight on query plan choice and performance against a specific database. Why should I enable it? This information helps in identifying performance issues even after SQL Server restart or upgrade. All data that SQL Server

Case study – 2307x faster!

Description: Problem: Stored procedure takes 38 seconds to execute. It times out on the application front-end. It was performing multiple scans and reading too much data. Solution:  The Stored procedure re-written. Also, added a second select with a UNION ALL, this was necessary to divide the WHERE clause selectivity. Other notes: Result set for both

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.