Performance

Dmitry Piliugin

MTVF and CE Model Variation

April 5, 2018 by

This is a note about multi-statement table valued functions (MTVF) and how their cardinality is estimated in the new CE framework.

In the old CE framework the MTVF had fixed estimate of one row, in the new one the estimate is still fixed, however, now it is 100 rows. That’s the whole story. =)

Read more »
Dmitry Piliugin

Overpopulated Primary Key and CE Model Variation

April 4, 2018 by

In this blog post, we are going to talk about another cardinality estimation model enhancement in SQL Server 2014 – Overpopulated Primary Key (OPK).

Consider a fact table that contains information about some sales, for example, and a date dimension table. Usually, a fact table contains the data about the current year and past years, but a dimension table usually contains the data for the next few years also.

Read more »
Dmitry Piliugin

Join Containment Assumption and CE Model Variation

April 3, 2018 by

In this post we are going to talk about one of the model assumptions, that was changed in the new cardinality estimation mechanism in SQL Server 2014 – Join Containment Assumption.

You may find some information about this assumption in the Ian Jose’s blog post: Query Processor Modelling Extensions in SQL Server 2005 SP1, there you may find the description of the so-called simple assumption and base assumption. Another source of available information is a white paper from Joseph Sack Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.

Read more »
Dmitry Piliugin

Filtered Stats and CE Model Variation

April 2, 2018 by

In this blog post, we are going to view some interesting model variation, that I’ve found while exploring the new CE.

A model variation is a new concept in the cardinality estimation framework 2014, that allows easily turn on and off some model assumptions and cardinality estimation algorithms. Model variations are based on a mechanism of pluggable heuristics and may be used in special cases. I think they are left for Microsoft support to be able to address some client’s CE issues pointwise.

Today we are going to view some interesting model variation, that creates filtered statistics on-the-fly. I should give a disclaimer here.

Read more »
Dmitry Piliugin

Cardinality Estimation Framework Version Control

March 30, 2018 by

This is a small post about how you may control the cardinality estimator version and determine which version was used to build a plan.

The version of the cardinality framework is determined by the query database context, where the database has a specific compatibility level.

When you create a database in SQL Server 2014 it has the latest compatibility level equals 120 by default. If you issue a query in that database context, the new cardinality version will be used. You may verify this by inspecting the plan property “CardinalityEstimationModelVersion” of the root language element (the one with the green icon), SELECT, for example.

Read more »
Dmitry Piliugin

Cardinality Estimation Process

March 29, 2018 by

In this post, we are going to take a deeper look at the cardinality estimation process. We will use SQL Server 2014, the main concepts might also be applied to the earlier versions, however, the process details are different.

Calculators

The algorithms responsible for performing the cardinality estimation in SQL Server 2014 are implemented in the classes called Calculators.

Read more »
Dmitry Piliugin

Cardinality Estimation Concepts

March 28, 2018 by

In this blog post we are going to talk about the principles and the main concepts which are used by the optimizer to perform an estimation, and also, we will do a little bit math, so be prepared.

Base Statistics

A cardinality estimation mechanism, as a first step, usually uses base statistics to estimate the expected number of rows that should be returned from the base table. You may look at these statistics using DBCC command – DBCC SHOW_STATISTICS.

Read more »
Dmitry Piliugin

Cardinality Estimation Place in the Optimization Process

March 27, 2018 by

In this blog post, I’m going to look at the place of the Cardinality Estimation Process in the whole Optimization Process. We’ll see some internals, that will show us, why the Query Optimizer is so sensitive to the cardinality estimation. To understand that we should observe the main steps that a server performs when the query is sent for execution.

Plan Construction Process

Below you may see the picture of the general plan-building process in SQL Server from the moment when a server receives a query till the storage engine is ready to retrieve the data. Take a quick look first, and next I’ll provide explanations.

Read more »
Dmitry Piliugin

Cardinality Estimation Role

March 26, 2018 by

This post opens a series of blog posts dedicated to my observations of the new cardinality estimator in SQL Server 2014. But, before we jump to the new features, I’d like to provide some background, to make the next posts clearer.

We’ll start by discussing the role of Cardinality Estimation in SQL Server, trying to answer – what is it and why it is needed.

Read more »
Ahmad Yaseen

SQL Server table hints – WITH (NOLOCK) best practices

February 14, 2018 by

SQL Server table hints are a special type of explicit command that are used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index or query processing operation, such index seek or table scan, to be used by the SQL Server query optimizer to build the query execution plan. The table hints can be added to the FROM clause of the T-SQL query, affecting the table or the view that is referenced in the FROM clause only.

Read more »
Prashanth Jayaram

Top 8 new (or enhanced) SQL Server 2017 DMVs and DMFs for DBAs

January 17, 2018 by

Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.

SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.

Read more »
Esat Erkec

How to use sargable expressions in T-SQL queries; performance advantages and examples

December 22, 2017 by

The challenge

One of the main tasks of a SQL Server database administrator is performance tuning. Sometimes, though, coders or developers don’t always prioritize database performance or query optimization. Here is a typical scenario

  • Imagine that developers create a new table and then insert some records in a test environment and test their queries to retrieve data from it
  • The query executed successfully and does not exhibit any symptoms of performance problems
  • The developer team release this table and query into production
  • One day you take a telephone from your colleague and he says my report is very slow
  • Bingo! In production, this table contains a lot of records and this is resulting in performance bottlenecks when querying it
Read more »
Neeraj Prasad Sharma
C:\Users\Neeraj\Desktop\TOP\IMAGES\FinalSerialNL.JPG

SQL Server TOP clause performance problem with parallelism

April 13, 2017 by

Basics of TOP Operator

The TOP keyword in SQL Server is a non-ANSI standard expression to limit query results to some set of pre-specified rows. As an argument, it takes 0 to positive Bigint (9223372036854775807) and anything beyond or less gives an error message. TOP without an order by clause, in production, seems buggy because it can produce results in any order depending on the current schema and execution plan. You can also specify a percent with TOP expression which returns only the expression percent of rows from the result set. Float expressions used in TOP percent are rounded up to the next integer value.

Read more »

Some uncommon but useful T-SQL and Database Engine Enhancements in SQL Server 2016

March 20, 2017 by

SQL Server 2016 is the most advanced version of Microsoft’s Data Platform released yet. This is obviously my favorite one as it has tremendous capabilities and enormous features. These new additions not only enhance the productivity of its users (Database Developer, DBA or Application Developer), but also enable the enterprise to use its data more effectively and efficiently.

Read more »
Mustafa EL-Masry

How to analyze Storage Subsystem Performance in SQL Server

February 23, 2017 by

Introduction

To improve performance, it is common for DBAs to search in each aspect except analyzing storage subsystem performance even though in many times, issues are, in fact, caused by poor storage subsystem performance. Therefore, I want to give you some tools and recommendation that you can use it to prevent your storage subsystem from being a performance issue for you.

Read more »
Ahmad Yaseen

Indexing SQL Server Temporary Tables

January 31, 2017 by

SQL Server Temporary tables are a special type of tables that are written to the TempDB database and act like regular tables, providing a suitable workplace for intermediate data processing before saving the result to a regular table, as it can live only for the age of the database connection.

Read more »
Ed Pollack

SQL Server Job Performance – Reporting

January 19, 2017 by

Description

Once collected, job performance metrics can be used for a variety of reporting needs, from locating jobs that are not performing well to finding optimal release windows, scheduling maintenance, or trending over time. These techniques allow us to maintain insight into parts of SQL Server that are often not monitored enough and prevent job-related emergencies before they become emergencies.

Read more »
Ed Pollack

SQL Server Job Performance – Tracking

January 19, 2017 by

Description

Keeping track of our SQL Server Agent jobs is a very effective way to control scheduling, failures, and understand when undesired conditions may be manifesting themselves. This is a brief journey towards a solution that provides us far greater insight into our jobs, schedules, and executions than we can glean from SQL Server by default.

Read more »
Page 1 of 212