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

Threadpool - A deadly poison wait for SQL Server (The What, When and How)

Introduction Threadpool is a poison wait. Yes, I mean it. Its poison for SQL Server, its poison for the Business and of course, the end-users! The most devastating thing about threadpool is you hardly recognize it because it comes in disguise, meaning you see no memory or cpu pressure in the system, yet you cannot run any query, it seems like your SQL Server is frozen solid. That scary, isn't it?

SQL Schema Compare with Visual Studio (A complete Guide)

Introduction When you're working on your Dev Database, an urgent issue comes along, and you instantly solve it by changing Scheme in the Staging Database or Production Database :3, few more these type of patching and you're completely out of sync! A lot of paid alternatives are there like SQL Data Compare by RedGate, but my first choice is Visual Studio's SQL Data Tools. In the following article, I tried to image-describe the steps for SQL Data Tool. Like I said before, there are lots of handly DBAtools out there to compare Schema between two DB Sources. I would like to discuss how you can compare two SQL Server DB with Visual Studio. Make sure you have SQL Server Data tools checked while installing Visual Studio.

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...

Intelligent Query Processing in SQL Server 2019 Big Data

SQL Server 2019: Intelligent Query Processing SQL Server 2019 ships with some brand-new features. Many of these features are targeted for Big Data Solutions. No wonder in that, since the world is moving faster towards Big Data and it is absolutely necessary to cope up with that. Today we will discuss one such feature called Approximate Query Processing. Approximate Query Processing SQL Server ships with Intelligent Query Processing out of the box with SQL Server 2019 installation. Approximate Query processing is a part of Intelligent Query Processing. Things we will be covering in this article – Understand the need for Approximation with Case Study Case Study 1: Railway Case Study 2: e-commerce How to use Approximate Query Processing Demo Code for Comparing Performance Results Limitations When to avoid Approximate Query Processing Understand the need for Approximate Query Processing Before using any technological feature, we must understand why we should use it? Should we jus...

How to generate C# Class from SQL Server Table

C# Class from SQL Database Table There are multiple ways you can generate a C# class from your Database Table. We will be covering the following topics in today’s article. Generate Class with foreign key relation Generate Class with only entities Generate Class with foreign key relations For this we can simply use Entity Frameworks EDMX update feature which will generate our C# class from Database Tables. The output from EDMX will contain foreign key relations which we can see from the virtual interfaces like this - public partial class AssetItem { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public AssetItem() { this .AssetItemDepreciations = new HashSet<AssetItemDepreciation>(); this .AssetTaxMappings = new HashSet<AssetTaxMapping>(); this .AssetVatMappings = new HashSet<AssetVatMapping>(); ...

6 Letters for SQL Disaster Emergencies : RPO and RTO

SQL Data Tools - Compare Data

Compare Data between two tables SQL Server Database with the same schema architecture can differ in different environments like Dev, Staging, and Production, especially in configuration tables. Let's see how we can easily sync the data in two different tables.

SQL Server Performance : OR vs UNION ALL

When writing queries, we seem to care less about the performance issue at first. Our first goal is to make the output right. When we get the correct output, we then move on to the next phase we call performance tuning. Today we will try to understand what happens when you write OR in your query. We will do the same thing with UNION ALL and try to understand which one seems to perform better.

SQL Server Pain Relief

Search This Blog