query performance Archives - {coding}Sight

Query Optimization Techniques in SQL Server: 5 Best Practices to Boost Queries Performance

Edwin Sanchez — Wed, 31 Mar 2021 16:48:05 +0000

What is query optimization in SQL Server? It’s a big topic. Each technique or problem needs a separate article to cover the bases. But when you’re just starting to level up your game with queries, you need something simpler to rely on. This is the goal of this article. You might say your queries are …

The post Query Optimization Techniques in SQL Server: 5 Best Practices to Boost Queries Performance appeared first on {coding}Sight.

9 Best Practices for Writing SQL Queries

Ben Richardson — Mon, 29 Mar 2021 12:31:11 +0000

If you are reading this article, most probably, you are already familiar with SQL. You know how to write basic SQL queries. There are many ways to execute a SQL query to get desired results on your database. However, not all SQL queries are created equal. Most can be optimized to follow the SQL query …

The post 9 Best Practices for Writing SQL Queries appeared first on {coding}Sight.

Virtual Columns and Functional Indexes

Tomas Helgi Johannsson — Wed, 13 Jan 2021 10:06:26 +0000

Much too often, we see poorly written complex SQL queries running against the database tables. Such queries may take a very short or a very long time to execute, but they consume a huge amount of CPU and other resources. Nevertheless, in many cases, complex queries provide valuable information to the application/person. Therefore, it brings …

The post Virtual Columns and Functional Indexes appeared first on {coding}Sight.

SQL Server Indexes Management Using Index Manager for SQL Server

Ahmad Yaseen — Thu, 29 Oct 2020 16:01:38 +0000

SQL Server Index Overview When talking about SQL Server performance tuning and queries enhancement, the first thing to consider is the SQL Server Index. It serves to accelerate reading data from underlying tables by providing quick access to the requested rows. Thus, it won’t need to scan all the table’s records. The SQL Server index …

The post SQL Server Indexes Management Using Index Manager for SQL Server appeared first on {coding}Sight.

3 Nasty I/O Statistics That Lag SQL Query Performance

Edwin Sanchez — Thu, 24 Sep 2020 08:35:31 +0000

“But it ran fine on our development server!” How many times did I hear it when SQL query performance issues occurred here and there? I said it myself back in the day. I presumed that a query running in less than a second would run fine in production servers. But I was wrong. Can you …

The post 3 Nasty I/O Statistics That Lag SQL Query Performance appeared first on {coding}Sight.

Parameter Sniffing Primer

Kenneth Igiri — Fri, 28 Jun 2019 05:00:33 +0000

Introduction Developers are often told to use stored procedures in order to avoid the so-called ad hoc queries which can result in unnecessary bloating of the plan cache. You see, when recurrent SQL code is written inconsistently or when there’s code that generates dynamic SQL on the fly, SQL Server has a tendency to create …

The post Parameter Sniffing Primer appeared first on {coding}Sight.

Auto Create Statistics and Auto Update Statistics

Nisarg Upadhyay — Sun, 23 Jun 2019 23:12:14 +0000

Statistics comprises lightweight objects that are used by SQL Server Query optimizer to determine the optimal way to retrieve data from the table. SQL Server optimizer uses the histogram of column statistics to choose the optimal query execution plan. If a query uses a predicate which already has statistics, the query optimizer can get all …

The post Auto Create Statistics and Auto Update Statistics appeared first on {coding}Sight.

Advanced SQL: CROSS APPLY and OUTER APPLY

Nisarg Upadhyay — Thu, 14 Mar 2019 15:23:46 +0000

In this article, we’ll look into the “APPLY” operator and its variations – CROSS APPLY and OUTER APPLY along with examples of how they can be used. In particular, we will learn: the difference between CROSS APPLY and the JOIN clause how to join the output of SQL queries with table-evaluated functions how to identify …

The post Advanced SQL: CROSS APPLY and OUTER APPLY appeared first on {coding}Sight.

Essentials and usage of NOLOCK hint in SQL Server

Esat Erkeç — Thu, 17 Jan 2019 17:20:40 +0000

The main idea of the SQL Server locking mechanism is that it controls the consistency of transactions. According to this principle, if a process wants to perform insert, delete or update operations, SQL Server engine locks the row or rows and does not allow another process until the transaction is completed. Under some circumstances, this …

The post Essentials and usage of NOLOCK hint in SQL Server appeared first on {coding}Sight.

Query Store: Showing the Impact of Indexes on Inserts

Kenneth Igiri — Wed, 16 Jan 2019 12:54:47 +0000

Introduction It is common knowledge in database circles that indexes improve query performance either by satisfying the required result set entirely (Covering Indexes) or acting as lookups which easily direct the Query Engine to the exact location of the required data set. However, as experienced DBAs know, one should not be too enthusiastic about creating …

The post Query Store: Showing the Impact of Indexes on Inserts appeared first on {coding}Sight.