Steve Stedman

Statistics IO for Performance Tuning in SSMS

by SteveStedman
August 8, 2015August 8, 2015
Performance, Performance Tuning

Statistics IO and Statistics Time is another SQL Server Performance Tuning Tip to help you better understand performance bottlenecks.

Have you ever wanted to know exactly how long it took for a query to run? Have you ever wondered how many I/O reads or writes were caused by your query? With Statistics IO and Statistics Time you can understand both of these.

Statistics IO for Performance Analysis

This post is on using statistics IO to analyze query performance. There is another post on using Statistics Time for performance tuning.

I usually prefer to use Statistics IO over Statistics Time because the Statistics IO option gives better details on what exactly the query is doing.

Let’s take the following query as an example:

Using Statistics Time for Performance Tuning in SSMS

by SteveStedman
August 8, 2015August 8, 2015
Performance, Performance Tuning

Statistics IO and Statistics Time are another SQL Server Performance Tuning Tip to help you better understand performance bottlenecks.

Statistics Time for Performance Analysis

This post is on using statistics TIME to analyze query performance. There is another post on using Statistics IO for performance tuning.

Another SQL Server Performance Tuning Tip to help you better understand performance bottlenecks.

This is a simple step to get performance statistics on about any query you are writing in SSMS.

Let’s take the following query as an example:

Understanding your Wait Statistics

by SteveStedman
August 3, 2015December 1, 2022
2 Comments
Database Health, Performance, Server Health

SQL Server Performance Tuning Tips – Wait Statistics

Wait statistics are commonly overlooked ways to quickly find out what is causing your SQL Server to be slow. One of the reasons is it’s difficult to see how they are trending over time.

>>> It is not difficult with a monitoring tool. <<<

When someone reports a slow database yesterday at 2:00pm, do you know how to determine what’s causing it? You can check the logs, you can look at the history of running jobs, and you might even ask around to see if anyone was doing anything unusual at that time. If you are tracking wait statistics, it is quick and easy to zoom in on a point in time and see exactly what queries were slow and why they were slow.

You might be thinking the following:

Did someone run a slow ad-hoc query?
Was there something wrong with the network?
Did someone run an unscheduled SSIS ETL package?
Was an index being rebuilt?
Did someone change application code?
Was there a hardware failure of some kind?
Was DBCC CheckDB running?

What are Wait Statistics

Whenever SQL Server is waiting for something to happen it logs that information as a WAIT.

What type of things get logged:

If your SQL Server is waiting on I/O.
If it’s waiting on network traffic to another SQL Server.
If your database is waiting on a transaction to complete.

Most everything that SQL Server waits on gets logged. However this information doesn’t stay around for long.

by SteveStedman
July 28, 2015November 30, 2022
1 Comment
Other

While watching a movie tonight, the Amazon Prime video system had an outage, no Amazon videos would play on my Amazon Fire TV Device. Netflix… Read More »System Outage… How do you communicate with your customers?

DBA on Vacation… Share your stories

by SteveStedman
July 27, 2015July 27, 2015
1 Comment
DBA

Being the middle of the summer in the northern hemisphere, and with the Database Corruption Challenge over, I thought I would cover something a bit lighter, and not as hard core as database corruption.

Share Your DBA on Vacation Stories

If you have been a DBA for more than a year or two you probably have some story about going on vacation, and getting the call from the office with some database problem. It might have been that someone forgot their password, or something more serious like a corrupt database. Either way, the simple or more complex issues are not why you went on vacation.

Here is a picture of a DBA on vacation in Hawaii. Notice the empty chair… They should be sitting in the sun sipping a tropical drink, but instead they are back in the hotel room with a VPN connection to the office dealing with some emergency (or perceived emergency) SQL Server issue instead of enjoying the beach.

DBA on Vacation (photo by Steve Stedman)

Solution to Week 10 Part 1 Database Corruption Challenge

by SteveStedman
July 22, 2015July 22, 2015
Corruption

If you are looking for more info on the corrupt database with Week 10 of the Database Corruption Challenge, you can take a look at the week 10 Challenge Details.

With this being the final competition of the Database Corruption Challenge, I had to make it more difficult than the others, so I added 3 different types of corruption. This challenge was about the equivalent of any 3 of the other challenges.

I have split the solution to the Week 10 Challenge into 3 different posts, with each post explaining how to fix one of the three specific corrupt areas.

Corruption in the Orders Table

WARNING: DBCC WritePage is a dangerous command, that should never be used on any production database. It may invalidate your ability to get support from Microsoft on issues that arise with that database going forward. It is not my intention to encourage anyone to use DBCC WritePage ever. This is just what I used to create a corrupt database, and since creating corrupt databases is not part of the role of most DBAs, you should not use DBCC WritePage. Consider yourself warned.

Patrick Flynn Interview – Database Corruption Challenge

by SteveStedman
July 22, 2015July 21, 2015
2 Comments
Corruption

After 10 weeks (or almost weekly) of corrupt databases, missing data and a challenging competition the Database Corruption Challenge finally comes to an end. As part of the last week of the challenge I created a short blog interview for the participants. This interview is Patrick Flynn.

Patrick placed in third with a 3 way tie for third overall in the the Database Corruption Challenge scoring 21 points.

Here are the overall statistics for Patrick in the Database Corruption Challenge

Overall Rank: #3 (3 way tie)
Points Scored: 21
Number of Challenges First to Solve (winning): 1
Number of Challenges Solved: 9
Country: Australia

Neil Abrahams Interview – Database Corruption Challenge

by SteveStedman
July 22, 2015July 21, 2015
Corruption

Neil placed in third place with a 3 way tie for third overall in the the Database Corruption Challenge scoring 21 points. He was one of only 3 participants to complete all 10 challenges in the contest, the other two were André Kamman and Rob Farley.

Here are the overall statistics for Neil in the Database Corruption Challenge

Overall Rank: #3 (3 way tie)
Points Scored: 21
Number of Challenges Solved: 10
Country: UK

André Kamman Interview – Database Corruption Challenge

by SteveStedman
July 21, 2015July 21, 2015
Corruption

After 10 weeks (or almost weekly) of corrupt databases, missing data and a challenging competition the Database Corruption Challenge, finally comes to an end. As part of the last week of the challenge I created a short blog interview for the participants. This interview is André Kamman.

André placed second overall in the the Database Corruption Challenge scoring 22 points which pushed hi AndreKamman m above a 3 way tie for third place. He was one of only 3 participants to complete all 10 challenges in the contest, the other two were Rob Farley and Neil Abrahams.