Presenting at PASS Summit Next Week
Next week I will be presenting on Database Corruption on Friday afternoon at Pass Summit. The session is called “When Database Corruption Strikes.” This session… Read More »Presenting at PASS Summit Next Week
Next week I will be presenting on Database Corruption on Friday afternoon at Pass Summit. The session is called “When Database Corruption Strikes.” This session… Read More »Presenting at PASS Summit Next Week
This Friday, October 14th I will be presenting at the Centricity Healthcare Users Group (CHUG) conference in Austin TX. The session is on “Tune, Monitor… Read More »Presenting this Friday at the CHUG Conference
How often should I reboot SQL Server? It is a common belief with computers that rebooting cleans things up in memory and that they run… Read More »Don’t Reboot SQL Server So Often
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.
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.
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.
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 »Instant File Initialization (IFI)
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.
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… Read More »Episode 59: Mirroring, Availability Groups, Replication, and Log shipping
There is a great deal of confusion about the WITH NOLOCK query hint. The following is a transcription of a conversation that I had on the SQL Data Partners Podcast #57 about the WITH NOLOCK hint, and some of the misconceptions about it.
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.
This weeks SQL Data Partners podcast, Episode 55, is on (SSRS) Reporting Services Changes in SQL 2016. This episode features Jessica Moss, and the discussion is… Read More »This weeks podcast featuring Jessica Moss
In episode 53 of the SQL Data Partners podcast Carlos Chacon and I chat with Argenis Fernandez, storage guru and PASS Director-at-Large. We talk storage options,… Read More »SQL Data Partners – Podcast with Argenis Fernandez