Ed Pollack

Ed Pollack
Execution plan when using a system DMV instead of COUNT.

SQL Server Database Metrics

October 2, 2019 by

Summary

There is a multitude of database metrics that we can collect and use to help us understand database and server resource consumption, as well as overall usage.

This data can include hardware statistics, such as measures of CPU or memory consumed over time. We can also examine database metadata, including row counts, waits, and deadlocks.

Read more »
Row Count Metrics

Using SQL Server Database Metrics to Predict Application Problems

September 27, 2019 by

Summary

Database metrics can be collected, maintained, and used to help predict when processes go awry so problems can be resolved before they become severe. Understanding when an application or process misbehaves is not always easy. We are often left waiting until a server, application, or service breaks or enters an undesirable state before we know something is wrong. At that point, we are forced to rush and resolve a production problem quickly, before its impact becomes severe.

Read more »
Sample form with validation errors that protect the underlying code from SQL injection attacks.

SQL Injection: Detection and prevention

August 30, 2019 by

Summary

With an understanding of what SQL injection is and why it is important to an organization, we can shift into a discussion of how to prevent it. We ultimately want systems where SQL injection is impossible or very difficult to pull off. We then want systems where exploiting bugs is slow, laborious, and likely to raise monitoring alarms within an organization when attempted. The trio of layered security, prevention, and alerting can provide an immense advantage against not only SQL injection, but other data security threats.

Read more »
Attempting to locate a SQL injection vulnerability by entering special characters into form fields.

SQL Injection: What is it? Causes and exploits

August 30, 2019 by

Summary

Many security vulnerabilities are discovered, patched, and go away forever. Some linger and continue to plague software development and will continue to do so for years to come. Setting aside social engineering and non-technical attacks, SQL injection remains one of the top security threats to our data, as well as one of the most misunderstood.

Read more »

Methods to Insert Data into SQL Server

April 10, 2019 by

Summary

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 »

Generating Schedules with SQL Server Agent

January 15, 2019 by

Summary

SQL Server Agent allows us to create jobs and assign any number of schedules to them. This allows for great flexibility when deciding when jobs should run. Over time, as the number of jobs and schedules increase on a given SQL Server, it can become challenging to fully understand when and how often jobs run during a given span of time.

Read more »

Troubleshooting Database Mail Failures

April 25, 2018 by

Description

Database Mail is a convenient and easy way to send alerts, reports, or data from SQL Server. Failures are not obvious to the us though, and developing a process to monitor these failures alongside other failures will save immense headaches if anything ever goes wrong.

Database Mail: a (very) brief overview

Database Mail is a component of SQL Server that is available in every edition, except for Express. This feature is designed to be as simple as possible to enable, configure, and use.

Database Mail relies on SMTP to send emails via a specified email server to any number of recipients. When configuring, you provide a mail server, credentials (if needed), and then the service is ready to use. We’ll be focusing here on failure reporting and not configuration. If you need help setting up or configuring this feature, check out some of the references at the end of this article.

Read more »

Reporting and alerting on job failure in SQL Server

March 12, 2018 by

SQL Server Agent can be used to run a wide variety of tasks within SQL Server. The built-in monitoring tools, though, are not well-suited for environments with many servers or many databases.

Removing reliance on default notifications and building our own processes can allow for greater flexibility, less alerting noise, and the ability to track failure conditions that are not typically tracked by SQL Server!

Introduction

At the heart of the SQL Server Agent service is the ability to create, schedule, and customize jobs. These jobs can be given schedules that determine at what times of day a task should execute. Jobs can also be given triggers, such as a server restart or alert to respond to. Jobs can also be called via TSQL from anywhere that has the appropriate access and permissions to SQL Server Agent.

Read more »

Everything you wanted to know about SQL Saturday (but were afraid to ask)

February 7, 2018 by

Description

Technical training is an important part of our careers. Meeting professionals, learning about new technologies, and making the most of the time and resources that we have can make a profound difference on the work that we do.

SQLSaturday provides a quality, inexpensive, and fun way to learn and get involved in the SQL Server community. It is a free, full day of technical presentations from experts that will travel from around the world to help make these events a success!

Read more »

How to quickly generate a large number of dimension tables for reporting applications

January 19, 2018 by

Description

When building reporting structures, we typically have the need to build fact and dimension tables to support the apps that will consume this data. Sometimes we need to generate large numbers of dimension tables to support application needs, such as in Tableau, Entity Framework, or Power BI.

Creating this schema by hand is time-consuming and error-prone. Automating it can be a way to improve predictability, maintainability, and save a ton of time in the process!

Read more »

How to solve the SQL Identity Crisis in SQL Server

November 14, 2017 by

Description

SQL Identity columns provide a convenient way to auto-number an ID column within a table without the need to manage the sequence. This convenience can save immense amounts of time, but also presents a single challenge: What happens when an identity column runs out of space within the data type chosen?

Read more »

SQL Server reporting – SQL Server Index Utilization

August 24, 2017 by

Understanding indexing needs allows us to ensure that important processes run efficiently and that our server hardware is not being over-taxed by poorly performing queries.

Collecting metrics on SQL Server index usage and missing index needs is critical to making smart decisions. To be truly proactive, though, we need to create a framework that allows us to quickly, efficiently, and regularly report on changes in indexing needs. With a system in place that can let us know when changes are needed, we can stay ahead of the optimization game and keep our customers happy!

Read more »

Collecting, aggregating, and analyzing missing SQL Server Index Stats

June 27, 2017 by

Description

Indexing is key to efficient query execution. Determining what indexes are needed to satisfy production needs is often a game of cat and mouse in which we are forced to react to performance problems after they are brought to our attention. Being able to proactively monitor index needs and respond effectively before complaints are received can save us immense time while preventing costly performance messes.

Read more »

Implementing and Using Calendar Tables

March 24, 2017 by

Introduction

There is a common need in reporting to aggregate or return data that is crunched based on date attributes. These may include weekdays, holidays, quarters, or time of year. While any of this information can be calculated on the fly, a calendar table can save time, improve performance, and increase the consistency of data returned by our important reporting processes. In my previous article, you could learn about designing of a calendar table.

Read more »

Designing a Calendar Table

March 22, 2017 by

Introduction

There is a common need in reporting to aggregate or return data that is crunched based on date attributes. These may include weekdays, holidays, quarters, or time of year. While any of this information can be calculated on the fly, a calendar table can save time, improve performance, and increase the consistency of data returned by our important reporting processes.

Read more »