Skip to content

SQL Server

Patrick Flynn Interview – Database Corruption Challenge

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

Read More »Patrick Flynn Interview – Database Corruption Challenge

Neil Abrahams Interview – Database Corruption Challenge

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 Neil Abrahams.NeilAbrahams

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

Read More »Neil Abrahams Interview – Database Corruption Challenge

Raul Gonzalez Interview – Database Corruption Challenge

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 with Raul Gonzalez.

Raul placed third (in a tie) overall in the Database Corruption Challenge behind Rob Farley, and André Kamman. Raul Gonzalez Interview

Here are the statistics for Raul in the Database Corruption Challenge

Raul Gonzalez Interview

Read More »Raul Gonzalez Interview – Database Corruption Challenge

SQL Server Tables – BTree or Linked List?

Is a clustered table in SQL Server a BTree or Linked List?

When you first learn about the structure behind clustered indexes in SQL Server, you find out that the clustered index is structured as a type of B+Tree where queries that make use of the tree structure to find the rows that you are looking for. However it is not the common belief that that table with the clustered index also happens to be a doubly linked list, and that the B+Tree can be completely skipped when running a query that uses the clustered index via a clustered index scan.

Take the following table and query as an example:

CREATE TABLE [dbo].[Orders](
	[id] [int] IDENTITY(-2147483647,1) NOT NULL,
	[orderDate] [DATETIME] NOT NULL,
	[customerId] [int] NULL,
	[shippingType] varchar(100),
	[orderDetails] varchar(max),
	[totalPrice] DECIMAL(12, 2)
	CONSTRAINT [PK_Revenue] PRIMARY KEY CLUSTERED ([id] ASC, [orderDate])
);

Then insert a bunch of rows, and run a query.

Read More »SQL Server Tables – BTree or Linked List?

Database Corruption Challenge #9 – How I Corrupted the Database.

I was asked how I caused the corruption in Database Corruption Challenge #9. Here is how I did it.

To cause the corruption, I used the undocumented DBCC WritePage, however the same thing could have been accomplished by detaching the database, opening it with a hex editor, and then re-attaching the db.

DBCC WritePage

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.

Read More »Database Corruption Challenge #9 – How I Corrupted the Database.

Database Health Monitor – Historic Waits

With the recent release of Database Health Monitor Version 2.0 I have decided to focus on of blogging about the features and benefits of the Database Health Monitor application. There are some incredibly valuable features that are often overlooked. The purpose of this blog series is to present some of the features of the product.

If you haven’t tried Database Health Monitor, you can download it at http://DatabaseHealth.com/download. It is completely free.

Historic Waits

The Historic Waits section of Database Health monitor is in my opinion the single most valuable part of the entire product. Other vendors sell products similar the Historic Waits feature for $1500 to $2000 per SQL Server instance, making it cost prohibitive for many.

The way that Historic Waits works is that it installs a small monitoring database on a SQL Server, that can be the same SQL Server you are monitoring, or it can be a separate SQL Server just to keep track of performance.

Over time data is collected in this monitoring database that allows you to then step back in time to see what was happening with the SQL Server at a specific point in time. For instance, if you have Historic Monitoring enabled, if someone comes to you and says “The SQL Server was slow and having problems at 2:00am yesterday”, you have the ability to track down what was happening at that point in time.

The main Historic overview page shows Waits, Plan Cache Hit Ratio, Page Life Expectancy and CPU Load over time.

HistoricOverview1

Read More »Database Health Monitor – Historic Waits

Database Corruption Challenge #9

Welcome to the ninth competition of ten in the Database Corruption Challenge. This week I have created a database and then corrupted one or more parts of that database, and your goal is to fix all of the corruption without losing any data. There is a total of 4 points available this week:

  • One point for being the first person to provide a correct solution with no data loss. Won by Eduardo Rezende.
  • One point to all who get a correct solution with no data loss.
  • One point extra if you get the correct answer before any hints are given.
  • One point extra if your solution is correct the first time you submit it to me. (check your work)

For information on scores take a look at the current scores page.

Challenge9

Corruption Challenge General Info

The challenge will be to download the corrupt or somehow damaged database and attempt to recover it. If you can recover it, please send me the steps you used to recover the database, along with some proof that the database has been recovered. The goal each week will be the following:

Read More »Database Corruption Challenge #9

Interview with Brent Ozar

Almost a month ago Brent Ozar did a blog interview with me regarding the Database Corruption Challenge so I thought I would return the gesture and do the same for him. I ended up going to the Bellingham SQL Server Users Group (PASS Chapter) to build the list of questions. The members of the group helped build the questions, and here is what we came up with, including responses from Brent.

Brent is a Microsoft MVP, Microsoft Certified Master of SQL Server, and published author. He lives in Chicago and is the founder of Brent Ozar Unlimited.

Question: For the self-taught or accidental DBA where do you suggest they start to improve their SQL Server Skills?Brent Ozar

Brent: DBAs get in the most trouble when they assume the backups are working. They’re probably not. Go set up a development server or VM, and restore last night’s backups onto it. Figure out what it would take to go live on that newly restored server. Rehearse it a few times, get confident in it, and then build a little checklist with the length of time it will take. Show it to your manager as proof that you’re working to keep the company’s data safe.

After that, it’s so tempting to get caught up just poking around in metrics. Go to your manager or your top power user and ask, “What do you want to do, but the database is holding you back?” These are the people who will give you a raise, and these are the reasons they’ll give it to you. Help them help you.

Read More »Interview with Brent Ozar

Database Health Monitor Version 2 Almost Done

Over the last 4 years I have been working on the Database Health Monitor. I am about a week away from releasing Version 2, which will finally be out of the beta process. In preparation for the release I am sharing some of the history of this program.

In 2011 version 1 was released as a set of my favorite monitoring queries that had been formatted and made available through SSRS. As people started to try it out in late 2011 and early 2012, I quickly discovered that the process for distributing shared queries via SSRS reports wasn’t very easy for the people trying to use them. Many people quickly discovered the difficulty of installing these reports. The set of reports looked something like this.

OnetimeUseQueries

 

There were 10 reports that were linked from the top panel in the SSRS user interface. Although SSRS has some great features, it was challenging to switch database connections and monitor multiple servers.

In April of 2012 I gave up on the concept of using SSRS reports, and converted all the queries into a windows application and started enhancing the program. In September of 2012, I finally had the Beta 1 release of Database Health Reports out. It was a bit rough in the beginning, but from 2012 to 2015 through a series of almost 20 beta releases labeled names like Beta 1, Beta 8.2, Beta 10.1, and so forth I kept enhancing the program. Here is an early screenshot of somewhere around Beta 1 or Beta 2.

Read More »Database Health Monitor Version 2 Almost Done

Database Corruption Challenge #8 has Ended

With many participants and 16 who correctly solved Corruption Challenge week 8, the challenge has come to an end. The overall winner was Rob Farley, and there were several new participants this week. The scores page has been updated.

Some participants called this the toughest challenge yet which makes my job even harder to come up with something more challenging for next time.  There were a couple interesting twists in this one. One was the way the corruption caused issues with DBCC Page. On SQL Server 2014 DBCC Page on the corrupt page with the output parameter of 3 didn’t work, and the only output option that worked was option 2. However on SQL Server 2008R2 and 2012, none of the DBCC Page output options worked on the corrupt page, which forced some to open the database file with a hex editor to pull the data in from the corrupt page.

The one thing that I didn’t judge anyone badly with was the capitalization of the person in the corrupt record.  I accepted EMMA, E WILLIAMS, and Emma, E Williams, and Emma, E WILLIAMS all as correct answers, as to figure out any of those it took the same amount of work.

The solution by Rob Farley:

Read More »Database Corruption Challenge #8 has Ended