Targeting Wait Statistics with Extended Events and DMVs

 

About the Presentation

SQL Server Wait Statistics - The Top 5 Waits You Should KnowWait statistics are a great tool for troubleshooting performance tuning problems in SQL Server. However, there are so many different wait types in SQL Server, it can be challenging to determine what to focus on and what can be ignored. Even after discovering that SQL Server is waiting for something, most DBAs don’t know where to look next. In this session, we’ll examine 5 of the most frequently seen SQL Server wait statistics and how to troubleshoot each of them. You’ll learn how to determine the root cause of a particular wait type, and how to avoid common mistakes made when troubleshooting with SQL Server wait statistics.

Session Level: 300

Download Presentation

Targeting Wait Statistics with Extended Events and DMVs

Presentations (Upcoming & Past)

 

Want to Learn More About SQL Server Performance Tuning?

If you'd like to learn more about how UpSearch can support your SQL Server performance tuning initiative, visit SQL Server Performance Tuning or contact us today.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

David Maxwell Named Principal Consultant

We are pleased to announce David Maxwell has been promoted to Principal Consultant.

“David has been an outstanding team member and driving force behind UpSearch’s Remote DBA Service,” stated Shawn Upchurch, Founder & CEO of UpSearch. “He regularly earns client praise for his ability to scope complex projects, serve as a resident subject matter expert, and effectively engage in executive-level technology decision maker discussions.”

David has also been extremely willing to support UpSearch’s strategic partnerships and Microsoft Partner enablement efforts.

 

About David Maxwell

David Maxwell Named Principal Consultant

In 2016, David became a Charter Member of Microsoft’s Certified Solutions Expert: Data Management and Analytics. He is a frequent presenter at local and national Professional Association for SQL Server (PASS) events, including the PASS Summit. David serves on the Columbus PASS chapter board and blogs at https://dmmaxwell.wordpress.com.

“We recognize David for his unique combination of technical know-how, presence, and professionalism,” stated Shawn Upchurch.

Learn more about David Maxwell here.

Shawn Upchurch,
Founder & CEO

Congratulations David!

Fun Facts

David will make two presentations at PASS Summit 2017:

 

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

DBA Basics – Intro to Database Maintenance

 

About the Presentation

Performance tuning, index design, etc… these are great skills to have, but there are four basic tasks that every DBA should know how to do backwards and forwards without even thinking about it. They are, in this order: Restore, Backup, Index Maintenance, and Consistency Checking. After attending this session, you will be able to restore a database in a few different ways, take the right backups in order to restore that database, know how and when to maintain the indexes and statistics, and check for database corruption.

DBA Basics - Intro to Database Maintenance

Presentations (Upcoming & Past)

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

Time Travel with SQL Server Temp Table

 

About the Presentation

Time Travel with SQL Server Temp TableIn many industries, it is necessary for data professionals to be able to show what a data set looked like at a previous point in time. Whether it’s calculating tax or insurance rates based on a contract date, quickly comparing data changes over time for trend analysis or just recovering from an accidental data change, it’s useful to be able to look back in time through our data. Solutions like Change Data Capture or trigger-based audit tables are usually slow, and require a lot of extra code to be effective. Fortunately, SQL Server 2016 introduces system-versioned tables, also known as Temporal Tables, which track data through time with far less overhead than other solutions. In this session, you’ll learn how to create temporal tables, how SQL Server manages data within those tables, and how to query data at a specific point in time using several real-world examples. Attendees will leave with a fundamental understanding of practical use cases for temporal tables and the knowledge to implement them.

SQL Server Temp Table

Presentations (Upcoming & Past)

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQLStarter – Intro to DBA

 

About the Presentation

Session in the SQLStarter beginner track to introduce the database administrator career. Will discuss aspects of what this job entails and what day to day could look like in this career path. We’ll go over important skills and where to find training information.

Session Level: Beginner

Download the Presentation

SQLStarter - Intro to DBA

Presentations (Upcoming & Past)

 

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Wait Statistics – The Top 5 Waits You Should Know

 

About the Presentation

SQL Server Wait Statistics - The Top 5 Waits You Should KnowWait statistics are a great tool for troubleshooting performance tuning problems in SQL Server. However, there are so many different wait types in SQL Server, it can be challenging to determine what to focus on and what can be ignored. Even after discovering that SQL Server is waiting for something, most DBAs don’t know where to look next. In this session, we’ll examine 5 of the most frequently seen SQL Server wait statistics and how to troubleshoot each of them. You’ll learn how to determine the root cause of a particular wait type, and how to avoid common mistakes made when troubleshooting with SQL Server wait statistics.

Session Level: Beginner

View Webinar & Download Presentation

Download contains:

  • Webinar Recording – 60 Minutes
  • PowerPoint Presentation
  • Scripts

SQL Server Wait Statistics - The Top 5 Waits You Should Know

Presentations (Upcoming & Past)

 

Want to Learn More About SQL Server Performance Tuning?

If you'd like to learn more about how UpSearch can support your SQL Server performance tuning initiative, visit SQL Server Performance Tuning or contact us today.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

Understanding and Handling Database Corruption

 

About the Presentation

This presentation was formerly titled “DBA 911 – Database Corruption.”

Database CorruptionDatabase corruption is simply a fact of a DBA’s life – if you haven’t seen it yet, you will. Success or failure depends on having a solid plan to deal with different types of corruption. In this session, we will discuss usage of related DBCC commands such as DBINFO, CHECKDB and PAGE. You’ll learn how to monitor for corruption with SQL Server Agent Alerts and Jobs, and when it’s time to restore instead of repair. After attending this session, you will be able to check for issues such as page corruption or data purity problems, and take the appropriate measures to deal with them.

Session Level: Beginner

View Webinar & Download Presentation

Download contains:

  • Webinar Recording – 60 Minutes
  • PowerPoint Presentation
  • Scripts

Understanding and Handling Database Corruption

Presentations (Upcoming & Past)

 

Want to Learn More About SQL Server Disaster Recovery?

If you'd like to learn more about how UpSearch can support your SQL Server disaster recovery initiative, visit SQL Server Disaster Recovery or contact us today.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

The Usual SQL Server SUSPECT: When Good Databases Go Bad

 

About the Presentation

The Usual SQL Server SUSPECT: When Good Databases Go BadYou just finished patching your SQL Server. After rebooting it, though, some of your databases aren’t coming online. What do you do? In this session we’ll cover some of the various states that a SQL Server database can be in. We’ll look at useful states like OFFLINE and STANDBY, as well as damaged states like SQL Server SUSPECT or RECOVERY_PENDING. You’ll learn how databases get into those states, and the proper techniques to bring your data safely back online.

Session Level: Beginner

Download the Presentation

The Usual SQL Server SUSPECT: When Good Databases Go Bad

Presentations (Upcoming & Past)

 

Want to Learn More About SQL Server Disaster Recovery?

If you'd like to learn more about how UpSearch can support your SQL Server disaster recovery initiative, visit SQL Server Disaster Recovery or contact us today.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

Turbo-Charged SQL Transaction Logs

 

About the Presentation

SQL Transaction Logs | UpSearchThe transaction log is one of the most critical, yet often misunderstood components of SQL Server. From out of control log growth to very long start-up times, problems with the transaction log can cause a lot of pain. In Turbo-Charged SQL Transaction Logs, we look at how the transaction log works and how to optimize its performance. You will learn how to determine the right settings for maximum throughput, and what to do when things get out of control.

Session Level: Beginner

Download the Presentation

Turbo-Charged SQL Transaction Logs

Presentations (Upcoming & Past)

Want to Learn More About SQL Server Performance Tuning?

If you'd like to learn more about how UpSearch can support your SQL Server performance tuning initiative, visit SQL Server Performance Tuning or contact us today.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Performance Tuning Script – Can My Databases Grow?

SQL Server Performance Tuning Script - Can My Databases Grow?Originally published on DMMaxwell.WordPress.com.

SQL Server Performance Tuning Script – Can My Databases Grow?

Most of us try to proactively manage our available disk space as much as we can, but sometimes you’re not able to predict when a file will grow. We usually leave auto-grow on as a safeguard for this. One of the problems I’ve run into in the past is having a very large growth setting or lack of disk space create a situation where there isn’t enough space on disk for a file to grow. There may be enough “free space” on the disk for whatever your monitoring threshold is, but maybe not enough for a growth or two.

Here’s a stored procedure you can use to check to see if any of the databases on your server would fail to auto-grow. It requires dbmail be set up. Also note that for this, and most scripts I will usually post, I have a separate administrative database that I put this in. Let me know if you have any feedback on this, or if you get any use out of it.

Thanks for reading.

-David.

USE [SQLAdmin];
GO

CREATE PROCEDURE AutogrowWarning
/*******************************************************************************
AUTHOR:
David M Maxwell

DATE:
Apr 24 2012

DESCRIPTION:
Checks to see if sufficient disk space is available for autogrow. If not, sends a warning.

DEPENDENCIES:
None

TEST EXEC:

EXEC dbo.AutogrowWarning
@dbaemail = 'david.maxwell@companyname.com'
GO

TO DO LIST:
1. Nothing at this time.

NOTES:
Please send any comments or suggestions to david.maxwell@companyname.com.

CHANGE HISTORY:
Date - Author - Change
Apr 24 2012 - David M Maxwell - Initial Version

*******************************************************************************/
@dbaemail varchar(500) = NULL -- DBA email address to receive warnings.
AS

SET NOCOUNT ON;

/* Test Parameters.  Run everything from here down to test. */
--DECLARE @dbaemail varchar(500), @msgquery nvarchar(4000)
--SELECT
--  @dbaemail = 'david.maxwell@companyname.com'

/* Parameter check */
IF (SELECT @dbaemail) IS NULL
BEGIN
RAISERROR('ERROR: Email recipient address must be provided.',16,1)
--GOTO EXITPROC
END

/* Create temp tables for comparison. */
IF (SELECT object_id('tempdb.dbo.#DiskFree')) IS NOT NULL
DROP TABLE #DiskFree

CREATE TABLE #DiskFree (
DriveLetter char(1) NOT NULL,
FreePages int NOT NULL
);

IF (SELECT object_id('tempdb.dbo.#NextAutoGrow')) IS NOT NULL
DROP TABLE #NextAutoGrow

CREATE TABLE #NextAutoGrow (
DBName varchar(100) NOT NULL,
LogicalName varchar(100) NOT NULL,
FileType CHAR(10) NOT NULL,
DriveLetter char(1) NOT NULL,
Growth int NOT NULL
);

IF (SELECT object_id('tempdb.dbo.#AutoGrowFail')) IS NOT NULL
DROP TABLE #AutoGrowFail

CREATE TABLE #AutoGrowFail (
DBName varchar(100) NOT NULL,
LogicalName varchar(100) NOT NULL,
FileType CHAR(10) NOT NULL,
DriveLetter char(1) NOT NULL,
Growth int NOT NULL,
FreePages int NOT NULL
);

/* Create perm table, if it does not exist. */

IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'AutoGrowWarnings')
BEGIN
CREATE TABLE AutoGrowWarnings (
WarningMessage nvarchar(max)
)
END

TRUNCATE TABLE dbo.AutoGrowWarnings;

/* Get the amount of disk space free on each volume. */
INSERT INTO #DiskFree
EXEC xp_fixeddrives;

/* Convert to pages for easier comparison, since master_files.size
is in pages. */
UPDATE #DiskFree
SET FreePages = FreePages * 128;

/* Get the next autogrow for each DB per drive letter. */
INSERT INTO #NextAutoGrow
SELECT
db_name(database_id),
name,
type_desc,
substring(physical_name,1,1),
CASE is_percent_growth
WHEN 1 THEN (size / growth)
ELSE growth
END AS Growth
FROM master.sys.master_files
WHERE Growth > 0

/* Compare the next autogrow to the disk free and create
a list of files that autogrow would fail on, due to
insufficient disk space. */
INSERT INTO #AutoGrowFail
SELECT
NAG.DBName,
NAG.LogicalName,
NAG.FileType,
NAG.DriveLetter,
NAG.Growth,
DF.FreePages
FROM #NextAutoGrow NAG
INNER JOIN #DiskFree DF
ON NAG.DriveLetter = DF.DriveLetter
WHERE NAG.Growth > DF.FreePages;

/* If the list is not empty, email it to the DBA. */
IF (SELECT COUNT(*) FROM #AutoGrowFail) > 0
BEGIN

INSERT INTO dbo.AutoGrowWarnings
SELECT 'WARNING: As of ' + CONVERT(varchar(24),GETDATE(),120) + ',' + DBName + '.'
+ LogicalName + ' requires ' + CONVERT(nvarchar(24),growth)
+ ' pages of free space for autogrow and only ' + CONVERT(nvarchar(24),FreePages)
+ ' are available on drive ' + DriveLetter + '.'
FROM #AutoGrowFail

EXEC msdb.dbo.sp_send_dbmail
@recipients = @dbaemail,
@subject = 'WARNING: Possible Auto-Grow Failure',
@query = 'SELECT * FROM SQLAdmin.dbo.AutoGrowWarnings'
END

EXITPROC:

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.