Slow SQL Server : What we should NOT do

Try to list the best practices of SQL Server. It will require a heck of a time.

Try to list the Bad Practices and it will require more than the best practice list, of course, probably you’ll end up getting frustrated. (seeing all the oops configurations and its effect on SQL Server)

Figure: Bad practices can make your SQL Server go Slow.

Let me try and list some of the common bad practices before getting frustrated.

Using SELECT *

When you don't need al the columns and
When there are fewer columns and you need all of them. You may be wondering What's wrong in selecting all columns if I have only 2/3 columns? See Why not use Select * in SQL Server for more details.

Keeping the MaxDop Settings as default (0). See Maxdop and Cost theshold for parallelism SQL Server to get the optimum maxdop settings for your server.

What if you use default maxdop? Your DB requests are going to use as many processors it needs or thinks it needs for better execution ending up eating up your resources for other requests.
You are probably going to face Threadpool - A deadly poison wait for SQL Server (The What, When and How) . It's deadly because you’ll feel like your server is frozen solid but you cant see memory or CPU pressure in the server!

MDF, LDF, tempdb initial size and autogrowth at default : By default SQL Server gives you a 8MB of autogrowth. Imagine What Can happen When you are facing heavy data flow in the server and the mdf, ldf and tempdb files need more space, so they request Server PC to call an autogrowth event and till then your requests are suspended until server allocates your SQL Server with more space. You’ll see more of preemptive wait types. See How to deal with SQL Server Autogrowth - The Smart Way
SQL Server files are not excluded in Antivirus Scan: This is very scary.

We were once preparing for Database Stress test heavy read-write operations. Just before we were going to start the test, we found that all our SQL Files (mdf,ldf) was going through read writes. (we were inspecting performance monitor of Windows server). The culprit process was sfc.exe and we understood it’s our Antivirus Program.

The list can grow very long, just one piece of advice, don't just go blindly with SQL Server default Configurations, understand your system and your needs properly before setting up your SQL Server. Think like the SQL Server engine and that will help you relieve its pain!

Comments

Most Loved Posts

How to encrypt an entire excel file using AES-128 encryption

What Is AES? AES stands for Advanced Encryption Standard. It is a symmetric block cipher that is used by the U.S. government to protect classified information. AES is used worldwide to protect classified data around the world. AES is essential in cybersecurity, electronic data protection, and computer security. Variations of AES AES is used in three block cipher versions namely AES 128, AES 192, AES 256. AES 128 uses 128-bit key length to encrypt and decrypt block messages. This is a symmetric secret key which means it uses same secret key for encrypting and decrypting message blocks. AES Encryption of Excel File Today we will AES 128 to encrypt an Excel File. The excel file looks like the following image. To encrypt this excel file, we will use C#. First, we need to create a console project. Let's name the project ExcelEncryption. First, we try to understand what we need to achieve. We need a KEY and Initialization Vector (IV) pair for AES 128 encryption. Let’s say, th...

SQL Insider 01 : An Anatomy of SELECT

Introduction When we write queries, we tend to think about the internals very little. In the new series of SQL Insider, I shall try to demonstrate what your SQL Server has to go through when you write a specific query, more specifically a specific operator. In the series, we shall try to cover all the important operators in SQL. Our today's SQL participant in SELECT. SELECT With the SELECT query, we can select one, some, or all the columns of a SQL table. The typical syntax for SELECT is like this SELECT * FROM Sales.SalesOrderDetail SELECT sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail sod Please note that we will not be dealing with WHERE clause in today's episode. Database We will be using AdventureWorks2019 Database for the demonstration Important Configuration We will be setting STATISTICS IO ON like this - SET STATISTICS IO ON; SET STATISTICS IO ON ; We will turn on Actual Execution Plan to examine the query SQL Insider Let's sta...

How to Backup SQL server like Batman: The Ultimate SQL DBA Guide

How to deal with Slow SQL Server due to Autogrowth issue

Why you should not stick to SQL Server’s default Initial file size and autogrowth We hear a lot of these statements : My SQL Server is running slow My Production DB was fine when we started, But it is staggeringly slow now My Business end users are frustrated to wait too long Well, there are lots of reasons why your SQL Server might be slow. Setting the Autogrowth option to default is definitely one of the vital ones which we seem to ignore most of the time. Slow SQL Server and Tortoise SQL Server provides you with some default settings for autogrowth when you install it for the first time. These default cases are defined with increment by 8MB or by 10%. You need to change it to suit your own needs. For Small application, this default value might work but as soon as your system grows, you feel the impact of it more often. What Happens SQL Server Files needs more space SQL Server Requests the Server PC for more space The Server PC takes the request and asks the SQL request...

SQL Server Pain Relief

Search This Blog