Expert insights. Personalized for you.
Bob Ward and I worked with our SQL Server Tool developers (thanks David) to enable ‘Q uick XE Trace ’ capabilities. The feature is available in the latest SQL Server Management Studio (SSMS) release. Here is an example using the SQL Server SSMS 2017 against my SQL 2016 server. MORE
Before going through the index design procedure, let us revise the SQL Server index concept. SQL Server index overview SQL index is considered as one of the most important factors in the SQL Server performance […]. MORE
In this article, we will describe SQL Server Profiler and how it can be used. Introduction SQL Server Profiler is a tracing tool provided by Microsoft since SQL Server 2000 version. MORE
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. Migration Performance SQL Server 2019 MORE
php” file that sits quietly until it is invoked by a top-level web server like Nginx+PHP-FPM or Apache mod_php. How does this Peak Perform? Running a PHP application offers a wholly unique experience compared to running applications in other programming languages like Java or Golang. MORE
Introduction Once we connect to a SQL Server instance, it assigns a unique identification number to each connection. It is the Server Process ID and commonly known as SPID. SPID 1 to 50 is reserved for SQL Server […]. MORE
This article targets the beginners and gives an introduction of the clustered index in SQL Server. Introduction An index plays a crucial role in SQL Server query performance. Indexes Performance SQL Server Management Studio (SSMS MORE
I've been teaching and writing about common SQL Server mistakes for many years. This article will expand on my previous article and point out how these apply to SQL Server , Azure SQL Database , and Azure SQL Managed Instance. SQL Server Agent alerts. SQL Server Agent Alerts. MORE
Many database administrators find themselves having to support instances of SQL Server Reporting Services (SSRS), or at least the backend databases that are required for SSRS. These topics apply to both SQL Server Reporting Services as well as Power BI Report Server. MORE
SQL Server BDC Hints and Tips: The node’s Journal can be your best friend If you are new to Kubernetes (K8’s), like I am, you quickly learn that the servicing node’s journal holds a wealth of information. For example, my master-0, SQL Server, pod was getting evicted and restarted. SQL Server BDC Hints and Tips BDC journal k8s MORE
SQL Server BDC Hints and Tips: TEMPDB Disk Usage I was troubleshooting a master-0 pod eviction due to disk pressure. Looking at the disk metrics I was able to map the time of the disk space increase to the startup of SQL Server, specifically TEMPDB. SQL Server BDC Hints and Tips BDC k8s tempdb MORE
SQL Server Books Online documents Instant File Initialization and the associated security considerations. SQL Server performs the following API calls when creating or extending (growing) data and log files. Bob Dorr – Principal Software Engineer SQL Server. MORE
SQL Server relies on Forced-Unit-Access (Fua) I/O subsystem capabilities to provide data durability, detailed in the following documents: SQL Server 2000 I/O Basic and SQL Server I/O Basics, Chapter 2. Microsoft SQL Server Database Engine Input/Output Requirements. MORE
I was asked to validate the SQL Server behavior in light of the error condition involving fsync/fdatasync outlined in this article. SQL Server does NOT do this, in fact I made sure SQL Server on Linux does not allow this to occur. SQL Server 2017 How It Works SQL Server on Linu MORE
I was looking at a SQL Server which had generated ten(10) SQLDump####.txt We introduced a new feature in SQL Server 2019 to save disk space for mini/crash dumps which can skip the suspension and capture for identical dump signatures, saving disk space and reducing the impact on the SQL Server instance. Each time the SQL Server instance is started a hash table is created to track crash dump signatures. How It Works Dumps SQL Server 2019 MORE
The SQL Server Host Extension provides file level integrity. Bob Dorr – Principal Software Engineer SQL Server. SQL Server 2017 How It Works SQL Server on LinuxWhen Creating, Renaming or Deleting (remove/unlinking) a file, Linux requires the direct parent directory to be synchronized. As documented in the manpage for fsync core changes to the directory require the directory itself to be synchronized. MORE
In this article, we will discuss a few very common questions that you may be asked during a SQL Server administrator or developer technical job interview. Q1: What is a SQL Server Execution Plan? MORE
SQL Server 2017 introduced a small change to SQL Server’s default worker thread calculation, accounting for smaller environments. When running on smaller environments SQL Server reduces the worker target. For an X64 installation, using the sp_configure ‘max worker threads’ value of 0 SQL Server uses the following calculation. On small environments SQL Server always uses 256 as the target. SQL Server 2017 MORE
However, discarding a server like this is expensive and inefficient. In this blog post, we will explain an approach to detect and fix transactions on the crashed MySQL master server in a semisynchronous replication setup, and how to re-slave it back into your master-slave setup. Re-Slaving a Crashed #MySQL Master Server in Semisynchronous Replication Setup Click To Tweet. Remember that you need to retain the UUID of this server as before. In a MySQL 5.7 MORE
Failures occur from remote clients or sqlcmd executed directory on the server (/opt/mssql-tools/bin). SQL Server 2017 CU10 does not show behavior. · SQL Server 2017 CU12 exhibits the behavior. Server is a Superdome with lots of CPUs and RAM. MORE
In traditional row-mode execution plans, SQL Server may introduce a Bitmap operator as part of performing early semi join reduction before a parallel hash or merge join. There have been major improvements since the first appearance of the batch mode execution engine in SQL Server 2012. MORE
SQL Server 2017 leverages up to 4 log writer workers, on hidden schedulers, to assist in transaction log processing activities. You may find the number of log write workers has been increased to 8 when running newer releases of SQL Server to accommodate larger systems. During SQL Server startup the number of log writer workers is determined. SQL Server 2017 General How It Works It Just Runs Faster NUMA Performance Scheduler MORE
The partitioned memory and latch objects protect in-memory structures for short durations and can be sized according to available memory for SQL Server. Bob Dorr – Principal Software Engineer SQL Server MORE
When starting SQL Server on Linux why are there two (2) sqlservr processes? systemctl status mssql-server mssql-server.service – Microsoft SQL Server Database Engine … CGroup: /system.slice/mssql-server.service ??85829 For those signals that default to capturing a full process CORE DUMP, SQL Server on Linux installs alternate signal handlers. Bob Dorr – Principle SQL Server Software Engineer. Linux SQL Server 2017 SQL Server on Linux MORE
One initial, easy step to moving your SQL Server on-premises workloads to the cloud is using Azure VMs to run your SQL Server workloads in an infrastructure as a service (IaaS) scenario. The post Azure Virtual Machines for SQL Server Usage appeared first on SQLPerformance.com. MORE
When I joined Microsoft in 1993, I would embark on a 25 year journey working only on Windows laptops and Windows Server computers. Last October we released SQL Server 2017 including support for Linux and Docker Containers. docker pull microsoft/mssql-server-linux:2017-latest. MORE
In an article, An overview of the SQL table variable, we explored the usage of SQL table variables in SQL Server in comparison with a temporary table. Development Performance SQL Server 2019 MORE
As I embark on my first PASS Summit in a few years, it is hard to contain my excitement about the newest version of SQL Server. I wrote about this in a post entitled, " A Big Deal : SQL Server 2016 Service Pack 1." SQL Server 2019 Enterprise Edition Standard Edition MORE
Standby Server Tests. Reboot the server. patronictl list did not display this server. Master/Primary Server Tests. Reboot the server. Failover happened and one of the standby servers was elected as the new master after obtaining the lock. One of the standby servers acquired the DCS lock and became the master by promoting itself. Network-isolate the master server from other servers. PostgreSQL was demoted on the master server. MORE
This article on SQL Server Always On Listeners includes an overview and various connection configurations for specifying MultiSubnetFailover including SSMS, ODBC, ADO.NET, as well as MultiSubnetFailover limitations and how to connect to all IP addresses via RegisterAllProvidersIP. MORE
In this article, we will review how to set up auto-failover groups in Azure SQL Server and how failover group is different from active geo-replication in Azure. MORE
A close monitoring of the hardware enthusiast community, including many of the most respected hardware analysts and reviewers paints an even more dire picture about Intel in the server processor space. Intel has been stuck at 14nm in the server space since the Broadwell release in Q4 of 2016. MORE
In the web hosting service industry, it is commonly used for balancing the HTTP traffic across multiple servers which act together as a web front-end. A Load Balancer allows the users to distribute the traffic to a single IP across several servers by using a set of different protocols. The processing load can be shared across different nodes, rather limiting it to a single server. The load balancer decides the servers, which can handle the traffic. MORE
This article gives an introduction of the non-clustered index in SQL Server using examples. Introduction In a previous article Overview of SQL Server Clustered indexes, we explored the requirement of an index and clustered indexes in SQL Server. 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. MORE
I have been making the same recommendations about tempdb since I started working with SQL Server over 15 years ago, when I was working with customers running version 2000. From the customer side, this has been the limit of what can be done*, until SQL Server 2019. MORE
MySQL Server generates several logs that can help you monitor the activities of the server. Setting Up MySQL Server Logging. Managing MySQL Server Logs: Rotate, Compress, Retain & Delete Click To Tweet. Check out these other posts to learn more about optimizing your MySQL deployments: Calculating InnoDB Buffer Pool Size for your MySQL Server. MySQL Tutorial – Configuring and Managing SSL On Your MySQL Server. MORE
Earlier this week, Microsoft announced SQL Server 2019 Cumulative Update #1. To update your local Docker image: docker pull mcr.microsoft.com/mssql/server: 2019 -latest. The post SQL Server 2019 Cumulative Update #1 appeared first on SQLPerformance.com. MORE
Microsoft, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and/or other countries. KB sectors run on smaller sectors 14 System and sample databases 15 Determining the formatted sector size of database 15 What sector sizes does SQL Server support? Restart the server. Restart the server. MORE
Let’s walk through the steps involved in installing and configuring the Prometheus server to store and display the metrics, an exporter (MySQL Exporter in this case) to collect the metrics and relay them to the Prometheus server, and Grafana to create dashboards. The MySQL Exporter tool can be installed locally on a MySQL server or centrally on the Prometheus server. Prometheus requires an exporter for collecting MySQL server metrics. MORE
Microsoft SQL Server I/O Basics Author: Bob Dorr, Microsoft SQL Server Escalation Published: December, 2004 SUMMARY: Learn the I/O requirements for Microsoft SQL Server database file operations. To ensure data integrity for our customers who are running SQL Server, it is important that I/O environments provide appropriate functionality. The order of the I/O operations associated with SQL Server must be maintained. MORE
This article discusses the Forwarded Records and its performance issues for heap tables in SQL Server. Heap introduction and performance issues A page of 8KB is the smallest unit of storage in SQL Server. SQL Server stores the data […]. MORE
On August 7, 2019, AMD finally unveiled their new 7nm EPYC 7002 Series of server processors, formerly code-named "Rome" at the AMD EPYC Horizon Event in San Francisco. Despite that, you will need a new model server to be able to use PCIe 4.0 Single-Socket Database Servers. MORE
This is not to say, however, that any mid-level developer will have much difficulty finding and handling one of many available open-source servers. performance load balancing failover server architecture sip webrtc development asterisk voip development asterisk voip voip server open source MORE
Servers Related Topics 
Scalegrid
MAY 13, 2019
MySQL Server generates several logs that can help you monitor the activities of the server. Setting Up MySQL Server Logging. Managing MySQL Server Logs: Rotate, Compress, Retain & Delete Click To Tweet. Check out these other posts to learn more about optimizing your MySQL deployments: Calculating InnoDB Buffer Pool Size for your MySQL Server. MySQL Tutorial – Configuring and Managing SSL On Your MySQL Server.
SQL Performance
AUGUST 2, 2019
I have been making the same recommendations about tempdb since I started working with SQL Server over 15 years ago, when I was working with customers running version 2000. From the customer side, this has been the limit of what can be done*, until SQL Server 2019.
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
SQL Performance
JANUARY 10, 2020
Earlier this week, Microsoft announced SQL Server 2019 Cumulative Update #1. To update your local Docker image: docker pull mcr.microsoft.com/mssql/server: 2019 -latest. The post SQL Server 2019 Cumulative Update #1 appeared first on SQLPerformance.com.
Scalegrid
DECEMBER 9, 2019
However, discarding a server like this is expensive and inefficient. In this blog post, we will explain an approach to detect and fix transactions on the crashed MySQL master server in a semisynchronous replication setup, and how to re-slave it back into your master-slave setup. Re-Slaving a Crashed #MySQL Master Server in Semisynchronous Replication Setup Click To Tweet. Remember that you need to retain the UUID of this server as before. In a MySQL 5.7
SQL Shack
DECEMBER 23, 2019
This article targets the beginners and gives an introduction of the clustered index in SQL Server. Introduction An index plays a crucial role in SQL Server query performance. Indexes Performance SQL Server Management Studio (SSMS
SQL Shack
NOVEMBER 25, 2019
In an article, An overview of the SQL table variable, we explored the usage of SQL table variables in SQL Server in comparison with a temporary table. Development Performance SQL Server 2019
SQL Performance
JUNE 21, 2019
I've been teaching and writing about common SQL Server mistakes for many years. This article will expand on my previous article and point out how these apply to SQL Server , Azure SQL Database , and Azure SQL Managed Instance. SQL Server Agent alerts. SQL Server Agent Alerts.
SQL Server According to Bob
JANUARY 17, 2020
SQL Server BDC Hints and Tips: TEMPDB Disk Usage I was troubleshooting a master-0 pod eviction due to disk pressure. Looking at the disk metrics I was able to map the time of the disk space increase to the startup of SQL Server, specifically TEMPDB. SQL Server BDC Hints and Tips BDC k8s tempdb
SQL Performance
NOVEMBER 1, 2019
As I embark on my first PASS Summit in a few years, it is hard to contain my excitement about the newest version of SQL Server. I wrote about this in a post entitled, " A Big Deal : SQL Server 2016 Service Pack 1." SQL Server 2019 Enterprise Edition Standard Edition
SQL Server According to Bob
SEPTEMBER 9, 2019
The partitioned memory and latch objects protect in-memory structures for short durations and can be sized according to available memory for SQL Server. Bob Dorr – Principal Software Engineer SQL Server
SQL Shack
JULY 10, 2019
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. Migration Performance SQL Server 2019
SQL Shack
SEPTEMBER 20, 2019
In this article, we will discuss a few very common questions that you may be asked during a SQL Server administrator or developer technical job interview. Q1: What is a SQL Server Execution Plan?
SQL Performance
SEPTEMBER 17, 2019
Many database administrators find themselves having to support instances of SQL Server Reporting Services (SSRS), or at least the backend databases that are required for SSRS. These topics apply to both SQL Server Reporting Services as well as Power BI Report Server.
SQL Shack
JANUARY 16, 2020
This article gives an introduction of the non-clustered index in SQL Server using examples. Introduction In a previous article Overview of SQL Server Clustered indexes, we explored the requirement of an index and clustered indexes in SQL Server.
SQL Performance
DECEMBER 17, 2019
One initial, easy step to moving your SQL Server on-premises workloads to the cloud is using Azure VMs to run your SQL Server workloads in an infrastructure as a service (IaaS) scenario. The post Azure Virtual Machines for SQL Server Usage appeared first on SQLPerformance.com.
SQL Server According to Bob
APRIL 24, 2018
When I joined Microsoft in 1993, I would embark on a 25 year journey working only on Windows laptops and Windows Server computers. Last October we released SQL Server 2017 including support for Linux and Docker Containers. docker pull microsoft/mssql-server-linux:2017-latest.
SQL Performance
AUGUST 23, 2019
In traditional row-mode execution plans, SQL Server may introduce a Bitmap operator as part of performing early semi join reduction before a parallel hash or merge join. There have been major improvements since the first appearance of the batch mode execution engine in SQL Server 2012.
SQL Server According to Bob
FEBRUARY 10, 2019
Failures occur from remote clients or sqlcmd executed directory on the server (/opt/mssql-tools/bin). SQL Server 2017 CU10 does not show behavior. · SQL Server 2017 CU12 exhibits the behavior. Server is a Superdome with lots of CPUs and RAM.
SQL Shack
JANUARY 13, 2020
Before going through the index design procedure, let us revise the SQL Server index concept. SQL Server index overview SQL index is considered as one of the most important factors in the SQL Server performance […].
DZone
JANUARY 15, 2020
This is not to say, however, that any mid-level developer will have much difficulty finding and handling one of many available open-source servers. performance load balancing failover server architecture sip webrtc development asterisk voip development asterisk voip voip server open source
SQL Server According to Bob
FEBRUARY 11, 2019
SQL Server 2017 leverages up to 4 log writer workers, on hidden schedulers, to assist in transaction log processing activities. You may find the number of log write workers has been increased to 8 when running newer releases of SQL Server to accommodate larger systems. During SQL Server startup the number of log writer workers is determined. SQL Server 2017 General How It Works It Just Runs Faster NUMA Performance Scheduler
SQL Server According to Bob
JANUARY 11, 2020
Microsoft SQL Server I/O Basics Author: Bob Dorr, Microsoft SQL Server Escalation Published: December, 2004 SUMMARY: Learn the I/O requirements for Microsoft SQL Server database file operations. To ensure data integrity for our customers who are running SQL Server, it is important that I/O environments provide appropriate functionality. The order of the I/O operations associated with SQL Server must be maintained.
SQL Server According to Bob
JANUARY 11, 2020
Microsoft, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and/or other countries. KB sectors run on smaller sectors 14 System and sample databases 15 Determining the formatted sector size of database 15 What sector sizes does SQL Server support? Restart the server. Restart the server.
SQL Shack
DECEMBER 5, 2019
This article discusses the Forwarded Records and its performance issues for heap tables in SQL Server. Heap introduction and performance issues A page of 8KB is the smallest unit of storage in SQL Server. SQL Server stores the data […].
SQL Server According to Bob
JANUARY 15, 2020
SQL Server BDC Hints and Tips: The node’s Journal can be your best friend If you are new to Kubernetes (K8’s), like I am, you quickly learn that the servicing node’s journal holds a wealth of information. For example, my master-0, SQL Server, pod was getting evicted and restarted. SQL Server BDC Hints and Tips BDC journal k8s
SQL Server According to Bob
DECEMBER 18, 2018
I was asked to validate the SQL Server behavior in light of the error condition involving fsync/fdatasync outlined in this article. SQL Server does NOT do this, in fact I made sure SQL Server on Linux does not allow this to occur. SQL Server 2017 How It Works SQL Server on Linu
DZone
SEPTEMBER 20, 2019
php” file that sits quietly until it is invoked by a top-level web server like Nginx+PHP-FPM or Apache mod_php. How does this Peak Perform? Running a PHP application offers a wholly unique experience compared to running applications in other programming languages like Java or Golang.
SQL Shack
AUGUST 19, 2019
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.
SQL Server According to Bob
DECEMBER 18, 2018
The SQL Server Host Extension provides file level integrity. Bob Dorr – Principal Software Engineer SQL Server. SQL Server 2017 How It Works SQL Server on LinuxWhen Creating, Renaming or Deleting (remove/unlinking) a file, Linux requires the direct parent directory to be synchronized. As documented in the manpage for fsync core changes to the directory require the directory itself to be synchronized.
Scalegrid
JANUARY 3, 2020
Let’s walk through the steps involved in installing and configuring the Prometheus server to store and display the metrics, an exporter (MySQL Exporter in this case) to collect the metrics and relay them to the Prometheus server, and Grafana to create dashboards. The MySQL Exporter tool can be installed locally on a MySQL server or centrally on the Prometheus server. Prometheus requires an exporter for collecting MySQL server metrics.
SQL Server According to Bob
DECEMBER 10, 2018
SQL Server Books Online documents Instant File Initialization and the associated security considerations. SQL Server performs the following API calls when creating or extending (growing) data and log files. Bob Dorr – Principal Software Engineer SQL Server.
Scalegrid
AUGUST 22, 2019
Standby Server Tests. Reboot the server. patronictl list did not display this server. Master/Primary Server Tests. Reboot the server. Failover happened and one of the standby servers was elected as the new master after obtaining the lock. One of the standby servers acquired the DCS lock and became the master by promoting itself. Network-isolate the master server from other servers. PostgreSQL was demoted on the master server.
DZone
JULY 31, 2019
In the web hosting service industry, it is commonly used for balancing the HTTP traffic across multiple servers which act together as a web front-end. A Load Balancer allows the users to distribute the traffic to a single IP across several servers by using a set of different protocols. The processing load can be shared across different nodes, rather limiting it to a single server. The load balancer decides the servers, which can handle the traffic.
SQL Shack
AUGUST 21, 2019
Introduction Once we connect to a SQL Server instance, it assigns a unique identification number to each connection. It is the Server Process ID and commonly known as SPID. SPID 1 to 50 is reserved for SQL Server […].
SQL Performance
AUGUST 27, 2019
On August 7, 2019, AMD finally unveiled their new 7nm EPYC 7002 Series of server processors, formerly code-named "Rome" at the AMD EPYC Horizon Event in San Francisco. Despite that, you will need a new model server to be able to use PCIe 4.0 Single-Socket Database Servers.
SQL Shack
JULY 18, 2019
In this article, we will review how to set up auto-failover groups in Azure SQL Server and how failover group is different from active geo-replication in Azure.
SQL Shack
DECEMBER 9, 2019
In this article, we will describe SQL Server Profiler and how it can be used. Introduction SQL Server Profiler is a tracing tool provided by Microsoft since SQL Server 2000 version.
SQL Shack
MAY 13, 2019
This article on SQL Server Always On Listeners includes an overview and various connection configurations for specifying MultiSubnetFailover including SSMS, ODBC, ADO.NET, as well as MultiSubnetFailover limitations and how to connect to all IP addresses via RegisterAllProvidersIP.
SQL Server According to Bob
DECEMBER 26, 2017
Bob Ward and I worked with our SQL Server Tool developers (thanks David) to enable ‘Q uick XE Trace ’ capabilities. The feature is available in the latest SQL Server Management Studio (SSMS) release. Here is an example using the SQL Server SSMS 2017 against my SQL 2016 server.
SQL Server According to Bob
FEBRUARY 10, 2019
SQL Server 2017 introduced a small change to SQL Server’s default worker thread calculation, accounting for smaller environments. When running on smaller environments SQL Server reduces the worker target. For an X64 installation, using the sp_configure ‘max worker threads’ value of 0 SQL Server uses the following calculation. On small environments SQL Server always uses 256 as the target. SQL Server 2017
SQL Server According to Bob
DECEMBER 10, 2019
I was looking at a SQL Server which had generated ten(10) SQLDump####.txt We introduced a new feature in SQL Server 2019 to save disk space for mini/crash dumps which can skip the suspension and capture for identical dump signatures, saving disk space and reducing the impact on the SQL Server instance. Each time the SQL Server instance is started a hash table is created to track crash dump signatures. How It Works Dumps SQL Server 2019
SQL Server According to Bob
JULY 17, 2018
When starting SQL Server on Linux why are there two (2) sqlservr processes? systemctl status mssql-server mssql-server.service – Microsoft SQL Server Database Engine … CGroup: /system.slice/mssql-server.service ??85829 For those signals that default to capturing a full process CORE DUMP, SQL Server on Linux installs alternate signal handlers. Bob Dorr – Principle SQL Server Software Engineer. Linux SQL Server 2017 SQL Server on Linux
SQL Performance
JULY 16, 2019
A close monitoring of the hardware enthusiast community, including many of the most respected hardware analysts and reviewers paints an even more dire picture about Intel in the server processor space. Intel has been stuck at 14nm in the server space since the Broadwell release in Q4 of 2016.
52 
Let's personalize your content