SQL Server Agent job configuration basics

This article will cover SQL Server Agent capabilities and features as well as tools to extend and enhance these core capabilities.

SQL Server provides us with the ability to schedule the different types of administrative tasks, in order to execute it automatically with customizable configurations. These tasks can be simple queries, database maintenance tasks, such as database backup, integrity check, re-indexing or statistics update, command prompt application, Microsoft ActiveX script, Analysis Services command or complex SQL Server Integration Service Packages. The Windows service that is responsible for scheduling and executing these tasks is called the SQL Server Agent Service.

January 17, 2019

How to baseline SQL Server wait statistics

A quick intro into SQL Server wait statistics

Starting with SQL Server 2005, Microsoft introduced the ability of SQL Server to track executed queries executed by measuring and logging the time that executed query has to wait for various resources such as other queries, CPU, memory, I/O, etc. This performance metric is referred to as a SQL Server wait statistic. Wait statistics are most precise and the most reliable way for tracking and identifying SQL Server performance problems as it allows measuring each step the query has taken during execution.

January 9, 2019

SQL Server data and structure recovery tool use cases

When a disaster hits, recovering lost data and structure is usually paramount. With ApexSQL Recover, the information inside the database (MDF) and transaction log (LDF) files is leveraged in order to create recovery scripts or to perform recovery tasks directly into a database and negate the data/structure loss. Once the recovery task is initiated, inadvertent or malicious changes can be identified, including the who made them and when, and then rolled back to the original state before they were ‘damaged’. Additionally, in many situations users need to extract information from huge backup files which is not always an easy task, and this is where ApexSQL Recover comes into play and simply extracts the data or structure from the backup without requiring or using a tremendous amount of resources used by the restore job which would otherwise be required.

November 2, 2018

SQL Server transaction log reader use cases

One of the most common tasks for ApexSQL Log is to be used as part of a post-incident investigation, usually involving inadvertently or maliciously changed or damaged data to determine who made the changes, what exactly was changed, when and how. With ApexSQL Log, users can dig into the transaction log files and see the exact values before and after the change has occurred as well as review the complete history of changes on the affected rows.

August 24, 2018

Top things you need in a SQL Server performance monitoring tool (from deadlocks monitoring to alerting and index monitoring)

The initial article, Top things you need in a SQL Server performance monitoring tool (from performance metrics to wait statistic and query performance monitoring) covers the most important “things” that any DBA expects to find in an SQL Server performance monitoring application and how those are covered in ApexSQL Monitor. This sequel will cover another set of critical and essential information that any successful SQL Server performance monitoring tool should be able to monitor and present in a manner that is easy to understand and interpret, and how those are covered in ApexSQL Monitor

August 16, 2018

SQL Server database Forensic auditing including row history and before-and-after auditing

Prevention is one of the most powerful approaches against any inadvertent or malicious events. But it is not always possible to prepare for all different calamities which may take place in SQL Server environments. Inadvertent changes, SQL injection attacks, malicious manipulations are some of the most frequent disasters SQL Server DBAs and engineers encounter. While the recovery process for the specific events which has negatively impacted the database is paramount, the next critical task in all situations is to find out who or what made the changes, what was changed and when. Gathering this information is a key for preventing other similar events which can affect SQL Server databases and endanger the data, as well as the business itself.

August 14, 2018

Load testing: Recording production transactions and replaying on a test server

Load testing not always the same as stress testing. Stress testing seeks to maximize the load on a server to see when and where it breaks. In theory, you can escalate the load until you isolate those failure points, even to a point where the stress exerted on the server may far exceed anything that would be experienced on production

August 13, 2018

Hands free, no-coding SQL Server database replication of a reporting database

ApexSQL Log is an amazing tool for reading the transaction log and reading the transaction log can form the basis of a lot of great features, functionality. In this case, we’ll look at how you can use ApexSQL Log for transactional replication to keep a reporting database up to date with your production system.

August 7, 2018

Top things you need in a SQL Server performance monitoring tool (from performance metrics to wait statistic and query performance monitoring)

SQL Server is an excellent platform to use as a backend for various database applications. It offers a fast and stable basis that allows the application to run fast, increasing the end user productivity and faster turnaround for a company. Coupled with a reliable tool with a graphical interface such as SQL Server Management Studio it makes creating tables, data manipulation, functions and stored procedures development, resources optimization, etc., to be performed in no time at all. 

August 6, 2018

What is SQL Server database continuous integration (CI)? – a high level conceptual overview and walk thru of the steps in a SQL database CI pipeline

If you’ve never automated builds in SQL Server before and but are still CI-curious, then this quick high-level overview, should introduce to you the basics and maybe even lead to you wading into the water a little deeper

First, continuous integration means that you will rapidly iterate changes, so that each time a change is formally made, usually by a source control commit, then the target system, a database in our case is automatically destroyed and recreated with the change. Furthermore, the entire test environment is recreated with a combination of static and test data, any automated tests and/or reviews are conducted, and the build is fully documented.

July 26, 2018

SQL execution plan analysis tool must have’s

SQL execution plans AKA SQL explain plans are one of the greatest assets when it comes to tuning, optimizing, and troubleshooting our queries. Transact-SQL is a declarative language that can be used to query data without requiring a human interaction to specify an exact SQL execution plan to be followed. This simply means that we are not telling SQL Server how to go and fetch the data, we’re just telling it what data we want it to get. So, think of an execution plan in SQL Server as a map. It is a map that the query optimizer is drawing that represents an efficient SQL execution plan to retrieve the data.

July 13, 2018

How to troubleshoot SQL Server performance problems using the SSMS Performance Dashboard

When it comes to SQL Server performance, you might not think of it as a big deal because of today’s modern hardware but every once in a while, your office phone will start ringing off the hook, your email inbox will start blowing up… SQL Server has been brought to its knees. What happened?

That’s what we are going to look at in this article. The main goal is to identify what some of the common problems are and take a close look at the SSMS Performance Dashboard tool. This tool is available out of the box for DBAs allowing to diagnose and resolve their SQL Server performance issues quickly and easily for all supported versions of SQL Server from SQL Server 2008 to SQL Server 2017.

May 17, 2018

How to master SQL Server performance baselining to avoid false positives and/or missing alerts

Baselining SQL Server metrics have significant advantages over traditional predefined alert thresholds when monitoring SQL Server performance. What’s more, when tracking some wait types statistics, it is a must have as there is no other way to interpret collected performance data correctly otherwise. A good start on how to detect performance issues in a real-world situation is the How to detect SQL Server performance issues using baselines – Part 3 article. However, to be able to learn how to use performance baselining and to understand its full potential some advanced knowledge will be presented first

May 11, 2018

How to implement compliance with GDPR for SQL Server

Starting May 25, 2018 all worldwide organizations and companies that process, store or in any other way handle personal information of EU citizens have to comply with the General Data Protection Regulation (GDPR) or face heavy annual fines and penalties. In order to comply with GDPR standard, organizations will have to protect and empower all EU citizens data privacy and reshape their approach to ensure that the security of the personal information is met as per the requirements and have full transparency on any data access, processing or more.

April 20, 2018

How to create and optimize SQL Server indexes for better performance

Indexes are paramount to achieving good database and application performance. Poorly designed indexes and a lack of the same are primary sources of poor SQL Server performance. This article describes an approach for SQL server index optimization to increase query performance.

March 19, 2018

How to optimize SQL Server query performance – Statistics, Joins and Index Tuning

Have you ever gotten a new computer, hooked it up and said: “this computer is blazing fast, I love it”? I have. A year from then, I was like “this computer is so slow, I need a new one”.

Performance is a big deal and this was the opening line in an article that was written on How to optimize SQL Server query performance. The initial article shows not only how to design queries with the performance in mind, but also shows how to find slow performance queries and how to fix the bottlenecks of those queries. I’d highly recommend reading the above article first because this one would give a lot more meaning but also because it’s an appendix to this topic.

February 28, 2018