Database Corruption Overview for Beginners

Download PDF

Database Corruption Overview: Database corruption is one of those things that you can only plan for by practicing your response plaDatabase Corruption Overviewn. Out of all of the things that can happen to your SQL Server this is the one that you are most likely going to want to ask for help when you encounter it.

What is Corruption?

Database corruption refers to corrupt pages in the database that are incorrectly formatted. This could be as simple as a single bit, or as huge as the entire file. Sometimes this type of corruption prevents the database from starting, other times it may prevents queries from running. Sometimes it may go undetected for some time, and may present as missing or incorrect data.

Read more ›

Posted in Corruption Tagged with: , , , , , , ,

Cortana Intelligence Suite – Podcast Episode #62.

Download PDF

This weeks latest podcast is an interview with Melissa Coates, on the topic of Cortana Intelligence Suite.

From this weeks podcast:

The Cortana Intelligence Suite is actually a huge collection of services in Azure for the purposes of providing big data and analytical solutions. So the suite consists of: Azure data factory, data catalog, Azure SQL data warehouse, Azure data lake which is actually a composite of three services, azure machine learning, stream analytics, event hub, PowerBI, cognitive services, the bot framework, and finally the Cortana digital assistant like you just mentioned. So although they named the suite after Cortana, the digital assistant is just one small part. She originated as a character in Halo, as a smart artificial intelligence character that can learn and adapt. And then she was the inspiration for the digital assistant in Windows. And now this suite of tools is named after her because she symbolizes the contextualized intelligence they hope to achieve with the suite of tools.

Listen to the podcast now at http://sqldatapartners.com/cortana

 

The performance review at the end of the episode was on Instant File Initialization.

Recent Episodes

Cortana Intelligence Suite

Posted in Podcast Tagged with:

Instant File Initialization (IFI)

Download PDF

One way to improve performance on SQL Server is with IFI or Instant File Initialization.

Normally files are zeroed out on a database growth during an AUTOGROW, RESTORE, CREATE DATABASE or ALTER DATABASE. This is done by SQL Server when the file grows, it runs through that file and writes zeroes to the entire new allocation in the file. The zeroing process can take a great deal of time, the Instant file initialization process skips this zeroing, and just allocates the file. This works since SQL Server will just write each 8k page to disk as they are used, thus overwriting the uninitialized file.

Running some tests on a local virtual machine running SQL Server 2012 (similar results tested on SQL Server 2014 and SQL Server 2016), here is what I found.

  • Creating a new database (10GB file size) took 2 minutes and 6 seconds, with IFI enabled, it took just 4 seconds.
  • Autogrow of a data file witha 1GB growth size took 13 seconds without IFI, with IFI it took less than a second.

 

For IFI to work, the user account that SQL Server is running as needs the “Perform volume maintenance tasks” policy to be enabled. Read more ›

Posted in Performance, Performance Tuning Tagged with: , , , , , , ,

Duplicate Indexes, What a Waste

Download PDF

Today on the tuning minute on the SQL Data Partners Podcast, we discussed duplicate indexes, which lead me to think more about and and write this post.

You know there are many different ways of doing things in SQL Server, and often times you can argue that one way or the other is better, and given the right situation anything might be a good idea. However duplicate indexes are a different story.

When I talk about duplicate indexes, what I mean is 2 or more indexes on the same table that are exactly the same columns.  Something like this:


CREATE NONCLUSTERED INDEX [IX_LastName] ON [dbo].[Customer]
(
 [Lastname] ASC
);

CREATE NONCLUSTERED INDEX [dta123123123_LastName] ON [dbo].[Customer]
(
 [Lastname] ASC
);

Two indexes on exactly the same column. There is nothing to be gained here.

Read more ›

Posted in Indexing Tagged with: , , , , ,

Database Corruption Video – HA DR Chapter

Download PDF

The following Database Corruption Video is from the High Availability Disaster Recovery Virtual Chapter Presentation on September 13, 2016.

Today I had the opportunity to present at the High Availability Disaster Recovery Virtual Chapter meeting on the topic of Database Corruption.  Here is a recording of the presentation.

Database Corruption Video

If you liked this presentation, please come to my session on Database Corruption at PASS Summit 2016 in Seattle.

 

You can download todays presentation and the sample code here:  hadr_chapter_presentation_2016.zip This includes the corrupt databases files as well as the sql scripts to fix them and the presentation material from todays session.

 

Database Corruption Challenges

Related Links

Need help with Database Corruption… That is one of my specialties. Contact me I can help resolve your corruption issues.

Posted in Corruption Tagged with:

Podcast Episode 60 : U-SQL

Download PDF

I can’t believe that this was the 11th episode that I have been part of with the SQL Data Partners podcast with Carlos Chacon. Starting at Episode 50 we have been through some great episodes.

Here is a list of the episodes that I have been part of.SQL Data Partners Podcast

 

The topics this week was U-SQL. What is U-SQL you might ask? As part of the Cortana Intelligence Suite, it plays a role in the streaming analytics space–primarily in the Azure environment.

Listen to Learn…

  • The common use cases for U-SQL
  • How U-SQL relates to T-SQL and C#
  • The connection between U-SQL and big data
  • How to scale data using U-SQL
  • Environment requirements to get started

Listen to Episode 60 on U-SQL today. 

Posted in Podcast Tagged with: , , ,

Episode 59: Mirroring, Availability Groups, Replication, and Log shipping

Download PDF

This week on the SQL Data Partners Podcast Episode #59 we discussed Mirroring, Availability Groups, Replication and Log Shipping. There were 4 of us on the podcast, two guests Mariano Kovo and Andy Mallon, along with Carlos Chacon and I. Each of us discussed one of the 4 options for having your data in multiple locations which are Mirroring, Availability Groups, Replication, and Log Shipping.

Some of the things we covered were:

  • How to use Mirroring to save money for your shop
  • The difference between Mirroring and Availability Groups
  • Why would I consider using Mirroring
  • The unique benefit of Replication
  • Why Log Shipping and Disaster Recovery work well together

This episode was a bit longer than our typical interview discussion, but we covered lots of interesting information, and discussed some of the differences between these topics. It was a fun and interesting show.

About Mariano Kovo
Mariano Kovo is an Argentinian SQL Server professional with Precision IT in Argentina. Read his blog (in Spanish) on SQLPass.org. Follow him on Twitter @Mariano_Kovo.

About Andy Mallon
Andy Mallon is a SQL Server DBA. He’s also the co-organizer of SQL Saturday Boston and co-leader for the New England SQL Server User Group. Follow him on Twitter @AMtwo and LinkedIn. His blog The Impatient DBA covers DBA careers, SQL Server, and T-SQL topics.

Related Links:

Posted in Podcast Tagged with: , , , , , , ,

The Use of WITH NOLOCK Query Hint

Download PDF

TWITH NOLOCKhere is a great deal of confusion about the WITH NOLOCK query hint. The following is a transcription of a conversation that I had with Carlos Chacon on the SQL Data Partners Podcast #57 about the WITH NOLOCK hint, and some of the misconceptions about it. If you haven’t heard the SQL Data Partners Podcast you might want to check it out.

WITH NOLOCK Hint

Carlos: So the next on is the WITH NOLOCK hint and I think this is on the list, ultimately, because there’s a lot of misinformation out there.

Steve: Absolutely, yes. The NOLOCK hint is one of my peeves on SQL Server, actually. I see it used a lot and really 99 percent of the time I see it used, people think that it’s doing something different than what it does. So, I heard the statement, “But I want to run a query in the production system but not impact or block anyone else. Shouldn’t I just use NOLOCK?” And the answer there is NO. The NOLOCK hint tells SQL Server to ignore other people’s query locks. Basically, to read dirty or uncommitted data at that point, which can lead to missing rows or phantom rows or data showing up in results. But it doesn’t do anything to stop the locking or the blocking in any way on the query that’s calling it. So it’s the equivalent of saying read uncommitted on a specific table that it’s referencing.

Read more ›

Posted in Performance, Podcast Tagged with: ,

Podcast Episode 58: Four Ways to Stay Sharp with Technical Learning

Download PDF

This week on the SQL Data Partners podcastPodcast Episode #58, we focused on Four Ways to Stay Sharp with Technical Learning. Learning

 

I can’t believe this is the 8th in a row of the SQL Data Partners podcast that I have had the opportunity to co-host. This week we didn’t have a scheduled guest so it was just Carlos Chacon and I discussing the ways to keep up on your technical learning.

Part of the focus is that technical learning is so much more than just picking up a book, or going to a class.

The areas we focused on were:

  1. Get involved with PASS events
  2. Participate or engage the community in some way
  3. Teach someone else
  4. Network and reach out

One of my favorite quotes from the podcast regarding learning at PASS event.

And it’s not just getting database people together. It’s getting fun database people together. I’ve been to other conferences where it’s not a lot of fun. But if you go to PASS events and Summit and SQL Saturday, everyone’s having a great time. You get to know people, and you can make some lifelong friends there perhaps.

Another great way to learn is to listen to all of the episodes in the SQL Data Partners podcast. I know that I have listened to all 58 of the episodes so far, and every time I learn something new.

Related Links:

Posted in Podcast Tagged with: , ,

Podcast Episode 57: the SQL Server Hit List – Our Least Favorite Features

Download PDF

For this weeks SQL Data Partners podcast, Carlos Chacon and I followed up on one of the #sqlfamily questions that we usually ask at the end of the podcast.

If you could change one thing about SQL Server, what would it be?

The list that we came up with was:

  • Default file autogrowth settings
  • Default maintenance plans
  • Shrink database or Auto Shrink
  • NOLOCK Hints
  • Table Variables
  • Instance settings – Processors tab
  • SQL Server Log File Viewer
  • Sql Server Logs file history options
  • Spatial Data
  • Data Tuning Advisor
  • Activity Monitor

There was no guest this week, just Carlos and I discussing why we think these 11 things are problematic with SQL Server and should be changed, or better documented in one way or another. Check out Episode 57 today!

Related Links:

Posted in Podcast Tagged with: , , , , , ,

SQL Server Performance Tuning

Need help with SQL Server Performance Tuning, contact Steve at Stedman Solutions, LLC for help, or take a look at the performance tuning page at the SteveStedman.com blog. We can help with tuning a single query or with figuring out why your SQL Server is running slow. Free 30 minute consultation.

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions

StedmanSolutionsRemoteSkilledDBA

Archives