Most Active Speaker

Edward Pollack

Edward Pollack

Data Architect | Microsoft Data Platform MVP

Albany, New York, United States

Ed Pollack is a Microsoft Data Platform MVP with a passion for learning how the Microsoft Data Platform works and sharing that knowledge with the community. His experiences in data architecture, database design, performance optimization, and data security are motivation for public speaking, writing, coding, and other community activities.

Ed has spoken at SQL Saturday events, SQL Bits, PASS Summit, EightKB, and many other regional and international events. Ed is the organizer of the Capital Area SQL Server Group and SQL Saturday Albany, as well as a co-organizer of SQL Saturday New York City, and Future Data Driven. He has published a number of books, including "Dynamic SQL: Applications, Performance, and Security in Microsoft SQL Server", "Expert Performance Indexing in Azure SQL and SQL Server 2022", and "Analytics Optimization with Columnstore Indexes in Microsoft SQL Server: Optimizing OLAP Workloads". Ed is also an active contributor of content to SimpleTalk.

In his free time, Ed enjoys video games, traveling, cooking exceptionally spicy foods, and hanging out with his amazing wife and sons.

Awards

Area of Expertise

  • Information & Communications Technology
  • Media & Information

Topics

  • Microsoft SQL Server
  • Data Analytics
  • Data Architecture
  • Big Data
  • T-SQL
  • Database Administration
  • Software Development

All About Data Retention

How long should data be kept for? How should it be handled as it moves from actively used hot data to seldom used cool data? What tactics can be used to optimize computing resources when handling data of different types, from different systems, and of different ages.

Retention is a seldom-discussed topic, but one that can be used to reduce cloud hosting bills, improve performance, improve predictability, and ensure that an application remains in compliance with legal and contractual obligations,

This session dives into data retention from all angles and provides a wide variety of tactics for creating retention policies and implementing technical solutions.

SQL Server Data Storage: Pages, Extents, and More!

Data in SQL Server is stored on pages, but what does that mean? What are pages and how are they structured? What are the different types of pages and how are they used to organize data? What are extents and how are they used to organize pages?

This session is a dive into SQL Server storage fundamentals and how this knowledge can be used to make better decisions with regards to files, filegroups, tables, indexes, and more! What starts as a review of pages and their function will quickly evolve into viewing (and understanding) the contents of pages. Also discussed will be corruption, what causes it, and how knowledge of SQL Server data storage can assist in resolving corruption!

Query Optimization Crash Course

Do you have users complaining that applications are running "too slow"? Do your database traces look like a short story written by monkeys at typewriters?

This is a fun, fast-paced walkthrough of the most effective strategies for query tuning. We'll investigate the most common causes of sub-optimal queries (and some quite unusual ones) while building a foundation for solving any optimization problem that the application throws your way!

Query optimization is rooted heavily in data architecture, therefore this session will include both performance tuning techniques and development best-practices to build apps that are less likely to generate bad queries.

MY Favorite New T-SQL in SQL Server 2022

SQL Server 2022 adds a variety of new T-SQL functions, as well as some excellent upgrades to existing functions.

Want to generate a list on the fly without the need for a tally table? Tired of repeating window function syntax over and over? Ever wish you could return the MAX or MIN value from a set of columns? How about more easily generating starting points for date parts?

This is a fast-paced, demo-filled party of new features that will save time, resources and make for cleaner and happier code!

My Favorite DMVs

Dynamic Management Views are a beautiful gift from Microsoft. With them you can gather immense amounts of information about your SQL environment, optimize performance problems, and solve problems you didn't even know you had!

This is a speedy demo of some of my favorite and most used dynamic management views, complete with demos, explanations and how they can make your life easier!

Memory-Optimized Table Variables in 10 Minutes

In every area of data management, there is a need to collect, process, and return data from temporary objects. Traditional temporary tables and table variables are stored in TempDB and come with the same performance limitations that disk-based tables do.

Using Memory-Optimized Table Variables allows for the creation, management, and disposal of temporary data completely within memory, without the need to store data on disk. This can reduce activity in TempDB while also speeding up processes that operate heavily on, or in-between temporary objects. Combining this feature with In-Memory OLTP and Natively Compiled Stored Procedures can allow for processes to operate completely in memory.

In this lightning talk, Memory-Optimized Table Variables will be introduced, discussed, and demonstrated. The most common use-cases for this feature will be introduced, as well as how they can allow you to overcome some of the limitations found in Natively Compiled Stored Procedures.

Memory-Optimized Data Structures in SQL Server

With each version of SQL Server, more features become available that allow for data or metadata to reside in memory. These improvements can greatly reduce contention, improve query performance, and reduce the computational overhead of frequently executed T-SQL.

This session discusses different features that allow for the use of memory-optimized data and the ideal use-cases for each. Topics include:
• Memory-optimized tables
• Natively-compiled stored procedures
• Memory-optimized TempDB metadata
• Memory-optimized table variables
• Memory-optimized table-valued parameters
• Data durability
• And more!

Effective use of memory-optimized data structures can greatly reduce locking, latching, blocking, and other common hallmarks of OLTP contention. This session will help you take control of your most critical and time-sensitive transactional processes by taking full advantage of memory-optimized data in SQL Server!

Improve Query Performance with Intelligent Query Processing in SQL Server 2022

Intelligent Query Processing (IQP) allows SQL Server to make dynamic, smarter decisions at runtime when processing queries. What’s more, these features are on by default when SQL Server is upgraded to a compatible version! Even better, each version of SQL Server adds more components to this suite of performance enhancements.

SQL Server 2022 debuted many new features to Intelligent Query Processing, including:
Cardinality Estimation Feedback
Degree of Parallelism Feedback
Approximate Percentile Calculations
Feedback Persistence
Parameter Sensitive Plan Optimization
Optimization Replay

This session will dive into each new feature, how to make use of it, and demonstrating how it works. This is intended to provide the information needed to take full advantage of IQP when adopting SQL Server 2022.

As SQL Server evolves, the need to incorporate runtime metrics into execution plans will continue to grow. Taking advantage of Intelligent Query Processing in your current SQL Server versions will allow for easy adoption of new query processing enhancements in future versions.

Improve Query Performance by Writing Better T-SQL!

Poor query performance can be caused by a wide variety of challenges ranging from simple to quite complex.

This session dives into the most common code patterns that lead to poorly performing queries, such as iteration, poor filtering, or choosing the wrong data types.

Not all performance optimization challenges can be solved easily, but this is an opportunity to look for many that can!

GREATEST and LEAST: New Functions to Improve our Messy T-SQL!

Added in SQL Server 2022, GREATEST and LEAST provide a huge boost to queries that need to compare values in a simple, set-based fashion.

Gone are the endless CASE statements or other hacks that litter our code! This session dives into these two new T-SQL functions discussing how they work, when to use them, and how to refactor our old, messy code to use them.

Effective Writing in Tech

The ability to document ideas, facts, and plans into writing is key to success in any technical field. Electronic communication, whether by chat, text, or essays forms a basis for much of how we share information in our professional lives. Writing is a critical life skill that can be learned, trained, and perfected. Many of the first impressions of others are made via email, cover letters, or proposals.

Persuasion and our ability to sell ideas while maintaining positive relationships with others is a talent that is challenging to learn, and near impossible to master.

This session focuses on communication through writing, with a strong focus on its use in highly technical fields. Included are tips and best practices of ways to improve written skills, as well as the most common ways in which writing can fail us (and how to avoid or recover from them). This is a professional development session that is structured and presented as a technical session, including demonstrations, personal examples, and all the embarrassing stories that come along with them!

The impact of diversity on our writing skills will be included, as lacking a face-to-face element can easily result in miscommunications. Strategies for minimizing these situations while using diversity to our advantage allows for more effective communication while maintaining respect for others, regardless of their backgrounds.

Effective writing goes beyond spelling and grammar. Communicating intent, staying focused, and using both data and organizational needs to convey problems and outline solutions will be the difference between success and failure.

Conquering Time Zones and Daylight Savings

Dates and times can be some of the most challenging data to work with. Most organizations start small and store data in a local time zone, only to grow and need to operate reliably across many.

This session dives into both best practices for managing date and time data while also providing solutions for how to handle dates/times when conversions and normalization are required in the future.

This is a hands-on session with demonstrations of effective schema design, a brief intro to calendar tables, and formulas and queries to convert between time zones, even when the data is historical in nature.

Columnar Data Storage: A Deep-Dive into Parquet, Delta Lake, Columnstore Indexes, and More!

Analytic data storage on the Microsoft data platform has evolved greatly over the years. From the early days of PowerPivot and SQL Server Analysis Services to the advent of columnstore indexes and the eventual adoption of the parquet format as the de-facto storage standard for analytic data in Azure, a lot has happened in the past fifteen years.

This session is a deep-dive into how columnstore technologies work, including:
• Overview and effectiveness of columnstore storage formats
• Encoding and compression algorithms
• Columnstore indexes in SQL Server
• Parquet file format
• Delta Parquet file format
• Vertipaq (row order) optimization

Understanding how analytic data is stored can allow for optimizations to be made to queries and the decisions made when architecting data structures. These improvements can decrease data size, speed-up analytics performance, and reduce computational overhead, thereby reducing Azure hosting costs.

These technologies will continue to evolve as data grows larger and organizational needs become more complex. Working effectively with these data storage formats will allow for fast querying of large amounts of data, both now and in the future.

My Favorite SQL Server System Views and Tables

Built into SQL Server are a growing set of exceptionally useful system views and tables that provide insight into many server and database components.

This demo-heavy session provides a wide array of queries and tools that can leverage system data to understand how different systems work, including:
Wait stats
Windows/System info
Performance counters
Index/Columnstore index structure
Registry entries
Database files, size, and metadata
Query stats
...and more!

Using these tools, database maintenance can be better automated and validated and application and analytics code can make smarter use of system metadata when making important processing decisions.

These are my favorite applications of system data for you to enjoy!

Predict Application Problems with Database Metrics

There are many database metrics that can be collected to help us understand database, server, and application performance. This data can include a wide variety of statistics, such as row counts, waits, IO, backup sizes, and more. These numbers are often used to plan server resources and capacity or to retroactively troubleshoot a problem-query.

Database metrics can also be used to proactively analyze changes in data over time and subsequently predict application problems before they become emergencies. In this session, we will discuss a variety of database metrics and how they can be used to get ahead of performance problems and application bugs. The primary goal of this analysis is to increase uptime, reduce critical application bugs, and prevent the frantic late-night emergencies that often result from them.

Parameter Sniffing: Everything You Have Been Told is Wrong!

Execution plan reuse is one of the most misunderstood aspects of SQL Server. While it serves us well 99.99% of the time, it is very easy to trip up when parameter sniffing results in performance problems.

In this session, we will thoroughly dissect how plan reuse works, discuss parameter sniffing, and the many potential solutions available to resolve it. We will examine the many poor solutions that are found on blogs and web pages all over the internet and why applying a quick-fix may result in long-term performance headaches if not thoroughly researched prior to implementation.

Memory-Optimized TempDB Metadata in SQL Server 2019

TempDB is a natural fit for memory-optimized technology in SQL Server. The data within TempDB is 100% transient, vanishing when no longer needed or when a restart occurs.

As a common bottleneck for intermediary operations, TempDB’s contention can be broken into data and schema contention. Memory-Optimized TempDB Metadata moves some of the most common system tables for temporary objects into memory. This eliminates latching on these tables while greatly reducing or eliminating waits on them.

This feature is an easy win that requires little memory and can greatly improve SQL Server performance where TempDB is a common bottleneck. In ten minutes we will discuss how it works, how to use it, and why it is worth using!

Memory-Optimized Table Variables in 10 Minutes

In every area of data management, there is a need to collect, process, and return data from temporary objects. Traditional temporary tables and table variables are stored in TempDB and come with the same performance limitations that disk-based tables do.

Using Memory-Optimized Table Variables allows for the creation, management, and disposal of temporary data completely within memory, without the need to store data on disk. This can reduce activity in TempDB while also speeding up processes that operate heavily on, or in-between temporary objects. Combining this feature with In-Memory OLTP and Natively Compiled Stored Procedures can allow for processes to operate completely in memory.

In this lightning talk, Memory-Optimized Table Variables will be introduced, discussed, and demonstrated. The most common use-cases for this feature will be introduced, as well as how they can allow you to overcome some of the limitations found in Natively Compiled Stored Procedures.

Improving Analytics with Window Functions in SQL Server

Window functions are amazing tools that allow statistics, analytics, and mathematical functions to be performed across a data set using less IO and code than with other methods. Not only is this a powerful toolset, but it is one that improves with each new version of SQL Server, adding new parameters, functions, and ways of managing analytic data.
This session will introduce window functions, their syntax, and demo many ways to use them in common analytic scenarios with a goal of providing hands-on experience and code that can be studied, tweaked, and used to solve real-world problems!

Improving Analytics Performance With Columnstore Indexes

Administrators and analysts often struggle with the storage and maintenance of reporting and analytics data. Traditional rowstore tables do not scale well when data is measured in billions of rows.

Columnstore indexes provide a speedy and convenient way to store OLAP data within SQL Server. Compression allows the data to be much smaller than other storage methods, allowing for faster reads. Column metadata provides the tools needed for SQL Server to be able to service queries over massive data sets quickly and efficiently.

The basics of columnstore indexes allow an administrator to improve the performance of analytics, but there is much more to learn! Understanding the internals of columnstore indexes allows querying to be even more efficient.

Columnstore indexes have evolved greatly since their introduction in SQL Server 2012. This presentation explores this feature in SQL Server 2017 and SQL Server 2019 and how its newest features can turbo-charge your analytics workloads!

Finding Islands, Gaps, and Clusters in Complex Data

Being able to group data into logical and meaningful sets can be extremely useful. When the data involved is a non-repeating sequence of integers, grouping is not too difficult. Most real data, though, is made up of dates, times, decimals, and rarely can guarantee non-repeating, non-null data sets.

This is a fast-paced session that delves into methods that can be applied to any data. Want to know how many production incidents make up a single common problem? Can we track sales success by the number of consecutive weeks that an agent has exceeded their goal? How about the longest home run streak hit by Derek Jeter at night games on Tuesdays?

Discussion will include performance, CTEs, recursion, set-based vs. iterative analysis, and some pretty hairy TSQL!

Effective Public Speaking in Tech

Public speaking is a critical life skill that benefits anyone in any field. The ability to effectively communicate ideas in meetings, presentations, or interviews is necessary to make the most of our technical skills. The greatest ideas in history would have been meaningless if their inventors were unable to share them with others effectively.

In the world of tech, this skill is even more pronounced as the knowledge gap between what we do and how it appears to others is far greater. This session dives into public speaking, focusing on how we as technical experts can best improve and use this skill to its fullest. Tips, tricks, and best practices will be shared that will help make speaking an experience that is more fun and less scary. This is intended to feel more like a technical session, with demos and embarrassing stories to illustrate the importance of this topic.

In addition, group facilitation skills will be discussed, focusing on the need to include audience engagement in any speaking endeavor. Knowing how to best gain and maintain the attention of a group is as important as speaking clearly and articulately.

The scope of this session goes well beyond presenting sessions and will help in any scenario where information sharing with other team members, organizations, and even friends is involved. Level up your speaking skills here!

Dynamic SQL: Writing Efficient Queries on the Fly

Dynamic SQL can be a powerful tool that allows you to write fast & efficient queries when the specifics may not be known until run-time. It can also be complex, difficult to read and debug, and the source of some of the most confusing TSQL you'll ever see.

Explore the many uses for dynamic SQL as well as best practices for writing, testing, debugging, and maintaining it, with the bonus of helping to improve the sanity of your coworkers (and your own) in the process!

Database Design Fundamentals

The best way to have data sources that are secure, perform well, and are easy to use is to design and architect them well the first time around.

With more data storage technologies available than ever, deciding where to put data and how to model it can be overwhelming!

This session is an overview of the tools available, and some best practices for determining how and where to store and secure your data!

Creating and Using Calendar Tables

There is a common need in analytics and reporting to aggregate data based on date attributes. These may include weekdays, holidays, quarters, or specific times of the year. Crunching these metrics on-the-fly can be slow and inefficient.

Calendar tables allow the complex conversions, definitions, and date-related metadata to be calculated ahead of time and stored in a static, reliable structure. We can then use this table to avoid the need to perform any date math on the fly. This not only saves time and computing resources, but also allows more complex analytics to be performed that would otherwise be very challenging.

This session delves into the design, implementation, and use of calendar tables, providing plenty of demos that illustrate their value and just how much fun they are!

Calendar Tables and Why You Should Use Them!

There is a common need in analytics and reporting to aggregate data based on date attributes. These may include weekdays, holidays, quarters, or specific times of the year. Crunching these metrics on-the-fly can be slow and inefficient.

Calendar tables allow the complex conversions, definitions, and date-related metadata to be calculated ahead of time and stored in a static, reliable structure. We can then use this table to avoid the need to perform any date math on the fly. This not only saves time and computing resources, but also allows more complex analytics to be performed that would otherwise be very challenging. Equally important, code becomes more maintainable as date calculations are performed once in a single location, rather than repeatedly throughout an application.

This session speeds through the design, implementation, and use of calendar tables, providing demos along the way of their creation and use.

Columnstore Index Compression in a Nutshell

Compression is one of the key optimizations that make columnstore indexes fast.

How does it work? What are the encoding algorithms used? What in the world is Vertipaq optimization?

This session is a speedy blast through the not-so-basic details of how columnstore indexes compress their data and how you can use that knowledge to make your analytic processes faster!

Building a Data Dictionary

As data grows and becomes more complex, documentation is required to develop, maintain, and share it. The task of documentation is one that is often left as an afterthought, to be dealt with when upgrades, releases, and other projects are complete.

A data dictionary is an effective way to document the contents of a database in a way that is maintainable, easy to share, and inexpensive.

This session defines a data dictionary and dives into how to build one from the ground up. From business discussions to defining data attributes to publishing a data dictionary schema, this is an opportunity to discuss a highly effective method of documentation and how it can benefit any organization that is in need of data documentation.

Approximate Query Processing in SQL Server

Sometimes, an estimate is all that is needed for analytic, troubleshooting, or general sizing purposes. SQL Server has many new and existing features that can be used to allow for exceptionally fast row count and query result estimates.

This speedy session provides a look into numerous methods to approximate results in SQL Server, providing ways to save resources and time while still being able to collect important result sets when needed.

Estimates are especially valuable when the output is being used in visualizations where the source row counts are very high, but the size of the target screen is relatively small. This is an opportunity to improve analytic performance using tools already at your disposal that are neither complex or require extensive knowledge to leverage!

All About Temporary Objects

Database development and administration require temporary objects for ETL, staging, data processing, and many other common tasks.

In this session, we will delve into temporary tables, table variables, and memory-optimized table variables, as well as memory-optimized TempDB metadata, discussing their differences, how they work, and the optimal scenarios for using each of them.

All About SQL Injection

SQL injection has consistently ranked as one of the top security threats in software development. Businesses have experienced massive data breaches and have even been forced to close as a result of security holes related to SQL injection.

Proactive solutions are key to preventing SQL injection and ensuring that hackers don't see your application as an easy target. We will demonstrate common mistakes that lead to SQL injection and a variety of ways which can safeguard our data against attacks.

While illustrating the ways in which hackers probe and attack our systems, we will show that SQL injection is not only the result of bad TSQL, but also bad code. By securing our data from the application and database tiers, we can greatly decrease their attractiveness to hackers and prevent attacks that could cripple a business permanently.

Advanced OLAP Optimization with Columnstore Indexes

Columnstore indexes provide an excellent solution to storing large analytic data in SQL Server. They can tame tables with millions or billions of rows with ease.

Understanding their architecture, compression, and best practices can allow for significantly faster workloads while saving computing resources.

This session dives into the details of how columnstore indexes work. Topics discussed will include:
Columnstore encoding, optimization, and compression algorithms
Segment and rowgroup elimination
Optimization of secondary indexes
Best practices for data load processes

This is an advanced dive into columnstore indexes that will allow anyone interested in this technology to make the most of its features in SQL Server 2019+

DATA BASH '23 Sessionize Event

November 2023

#DataWeekender 6.5 Sessionize Event

November 2023

New Stars of Data #6 Sessionize Event

October 2023

SQL Saturday Boston 2023 Sessionize Event

October 2023 Burlington, Massachusetts, United States

Data Platform Diversity, Equity, and Inclusion Virtual Group User group Sessionize Event

October 2023

SQL Friday Season 7 (September - December 2023) User group Sessionize Event

September 2023

Kansas City SQL Server Users Group Speakers User group Sessionize Event

August 2023

SQL Friday Season 6 (January - June 2023) User group Sessionize Event

March 2023, June 2023

SQL Saturday New York City 2023 Sessionize Event

May 2023 New York City, New York, United States

SQLBits 2023 - General Sessions Sessionize Event

March 2023 Newport, United Kingdom

TechDay Pakistan- Mar 2023 Sessionize Event

March 2023

Rhode Island Data Platform User Group User group Sessionize Event

February 2023

PASSMN Monthly Meeting 2022/2023 User group Sessionize Event

January 2023

SQL Saturday Nepal 2022 (#1025) Sessionize Event

December 2022

SQLSaturday MN 2022 Sessionize Event

December 2022

Malta Community: Query Optimization in SQL Server 2022: From Zero to Hero Sessionize Event

December 2022

Triangle Area SQL Server User Group (TriPASS) User group Sessionize Event

October 2022

SQL Saturday Boston 2022 Sessionize Event

October 2022 Burlington, Massachusetts, United States

PASS Data Community Summit DEI Panel 2022 Sessionize Event

October 2022

Future Data Driven Summit 2022 Sessionize Event

September 2022

Sql Friday Season 5 (August - December 2022) User group Sessionize Event

August 2022

EightKB August 2022 Sessionize Event

August 2022

Sql Friday Season 4 (Feb - Jun 2022) Sessionize Event

February 2022

DataMinutes #2 Sessionize Event

January 2022

PASS Data Community Summit 2021 Sessionize Event

November 2021

#DataWeekender v4.2 Sessionize Event

November 2021

DataSaturdays #13 - Minnesota - Oct 16 2021 Sessionize Event

October 2021

Future Data Driven Sessionize Event

September 2021

Dativerse #1 Sessionize Event

August 2021

Data Geeks Saturday Conference Sessionize Event

August 2021

Virtual Symposium - SQL Server & Azure SQL Sessionize Event

May 2021

Edward Pollack

Data Architect | Microsoft Data Platform MVP

Albany, New York, United States