There are few topics so widely misunderstood and that generates such frequent bad advice as that of the decision of how to index a table. Specifically, the decision to use a heap over a clustered index is one where misinformation spreads quite frequently.
Read more »
SQL Server Statistics are an essential part of query performance in SQL Server. They help the query optimizer to prepare a query plan using the distribution of values in participating rows. If we do not have updated statistics, it might lead to resource intensive query execution plan. For example, for a large table having a billion records, SQL may choose to have an index scan instead of an index seek.
Read more »
This article is the sequel in a series about SQL Server monitoring tools and common performance issues. Before reading this piece, it advisable to read the previous two articles about monitoring tools for disk I/O and memory performance:
Read more »
There are a variety of ways of managing data to insert into SQL Server. How we generate and insert data into tables can have a profound impact on performance and maintainability! This topic is often overlooked as a beginner’s consideration, but mistakes in how we grow objects can create massive headaches for future developers and administrators.
Read more »
This article is the sequel in a series about SQL Server monitoring tools and common performance issues. The first article SQL Server monitoring tools for disk I/O performance is about how to detect and solve high input/output on hard disk subsystems when doing too much work during peak or maintenance times.
SQL Server trace flags are configuration handles that can be used to enable or disable a specific SQL Server characteristic or to change a specific SQL Server behavior. It is an advanced SQL Server mechanism that allows drilling down into a hidden and advanced SQL Server features to ensure more effective troubleshooting and debugging, advanced monitoring of SQL Server behavior and diagnosing of performance issues, or turning on and off various SQL Server features
The goal of this article is to get familiar with SQL Server monitoring tools and identify what some of the most common SQL Server performance problems are.
Read more »
One of the main DBA responsibilities is to ensure databases to perform optimally. The most efficient way to do this is through indexes. SQL indexes are one of the greatest resources when it comes to performance gain. However, the thing about indexes is that they degrade over time.
Read more »
In SQL Server, we normally use user-defined functions to write SQL queries. A UDF accepts parameters and returns the result as an output. We can use these UDFs in programming code and it allows writing up queries fast. We can modify a UDF independently of any other programming code.
In this article, we will explore SQL Server Profiler in Azure Data Studio in detail including download and installation, execution plans, coding T-SQL and more.
Read more »
This article will cover managing a SQL Server database upgrade using new features in SQL Server Management Studio 18 including the query tuning assistant wizard, database upgrade feature, query store and more
Database administrators are used to dealing with query performance issues. As part of this duty, it is an important aspect to identify the query and troubleshoot the reason for its performance degradation. Normally, we used to enable SET STATISTICS IO and SET STATISTICS TIME before executing any query.
In the previous articles of this series, SQL Server Execution Plans overview , SQL Server Execution Plans types and How to Analyze SQL Execution Plan Graphical Components, we discussed the steps that are performed by the SQL Server Relational Engine to generate the Execution Plan of a submitted query and the steps performed by the SQL Server Storage Engine to retrieve the requested data or perform the requested modification operation.
Read more »
Of the many ways in which query performance can go awry, few are as misunderstood as parameter sniffing. Search the internet for solutions to a plan reuse problem, and many suggestions will be misleading, incomplete, or just plain wrong.
Read more »
Monitoring databases for optimal query performance, creating and maintaining required indexes, and dropping rarely-used, unused or expensive indexes is a common database administration task. As administrators, we’ve all wished, at some point, that these tasks were simpler to handle.
Read more »
In this article, we’re going through many of the tools we can use for monitoring SQL Server performance. SQL Server is chock-full of lots of good reports that allow a DBA to quickly spot whether there is any current performance bottleneck on the SQL Server. Many of these sit on top of DMVs but they give us a visually interactive way to look and work with the data. We’re going to start with SQL Server Performance Dashboard Reports.
Let’s jump into SQL Server Management Studio (SSMS) and the first thing we’re going to take you through out-of-the-box dashboard reports of all levels. They can be found by right-clicking the SQL Server instance in Object Explorer, and from the context menu, you’ll find Reports > Standard Reports:
Read more »
Intended audience
This document is intended for database administrators who would like to be prepared or who experience performance issues with one or more Linked Server(s) to an Oracle Database instance using the OraOLEDb Linked Server provider.
Read more »
When maintaining SQL Server, it is essential to get an accurate perception of how busy it is. Two metrics that are often considered as indicators of how busy SQL Server is are Batch Requests/sec and Transaction/sec. When those metrics trend higher, they often affect all other metrics and make them go higher as well. While they could look similar, they are using a different type of starting point for measurement; the batches and transactions. So, to correctly understand those important metrics, lets first try to understand what the batches and transactions in SQL Server are and what are the differences between the two
Read more »
I would like to share one curios case that I recently came across.
Long story short:
This bug may lead to incorrect results if you use a partitioned table and the FORCESCAN hint.
Consider the following example, let’s keep it simple.
Read more »
Sometimes people use nolock hint as a “turbo” button for their queries, assuming that not taking locks will speed up the query execution. There are many good articles describing all the dangerous moments of this approach, because of the read uncommitted isolation level. However, the focus of this article is a performance problem that you may encounter using nolock hint in some cases.
Let us setup some test data first.
Read more »
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 »
In this note, I’m going to discuss one of the most useful and helpful cardinality estimator enhancements – the Ascending Key estimation.
We should start with defining the problem with the ascending keys and then move to the solution, provided by the new CE.
Read more »
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 »
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 »
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 »© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy