Database Corruption Overview: Database corruption is one of those things that you can only plan for by practicing your response plan. 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.
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.
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 ›
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]
CREATE NONCLUSTERED INDEX [dta123123123_LastName] ON [dbo].[Customer]
Two indexes on exactly the same column. There is nothing to be gained here.
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.
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.
There 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.
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:
Get involved with PASS events
Participate or engage the community in some way
Teach someone else
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.
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
Instance settings – Processors tab
SQL Server Log File Viewer
Sql Server Logs file history options
Data Tuning Advisor
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!