This article gives an overview of Instant File Initialization and its benefits for SQL Server database creation, restoration and file growth.Read more »
In this article, we will study in detail about the how SQL Server Index Fill factor works.Read more »
Have you ever noticed SET NOCOUNT ON statement in T-SQL statements or stored procedures in SQL Server? I have seen developers not using this set statement due to not knowing it.Read more »
In this article, we will learn usage details of the sp_updatestats built-in store procedure which helps to update all statistics in a SQL Server database. First of all, we will take a glance at the statistics concept in SQL Server.Read more »
SQL Server Query Store is a performance monitoring tool that helps us evaluate the performance of a SQL query in terms of several different performance metrics such as CPU and Memory Consumption, execution time and the I/O cycles consumed by the query. Query store is similar to the windows “Task Manager”. A task manager provides information about the CPU, Memory, Network and Disc consumption of a process. Similarly, the Query Store provides insight to similar information.Read more »
In-memory technologies are one of the greatest ways to improve performance and combat contention in computing today. By removing disk-based storage and the challenge of copying data in and out of memory, query speeds in SQL Server can be improved by orders of magnitude.Read more »
Index strategies overview
This article is about techniques for optimizing the SQL Server indexes strategy. It is an appendix of the SQL index overview and strategy article in which I covered different areas like what indexes actually do, how to create them, and I briefly mentioned some index design guidelines. Furthermore, I also presented an example of how to design them by tuning and optimizing queries, so I’ve really tried to cover all but there is always more when it comes to SQL Server indexes.Read more »
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 »
Introduction to SQL Server Statistics
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 »
CPU pressure slowing down the server
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 »
Memory pressure slowing down queries
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 »
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 twoRead more »