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: , , , , , ,

This weeks podcast featuring Jessica Moss

Download PDF

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 on some of the great new features in SSRS 2016.

SSRS 2016

One of the key take-aways from the session is how Microsoft has broken the report groups into 4 categories.

  • Paginated – Like what we are used to with older SSRS. Picture perfect page based reports.
  • Interactive reports – the analytical to dig in to answer a question.
  • Mobile reports – quick glance at things for a high level KPI.
  • Analytical reports and charts – excel.

With no major changes top SSRS from SQL 2008 to SQL Server 2014, the enhancements to SSRS 2016 are a very welcome improvement. Also in SQL Server 2016 – SSDT has been incorporated with the business intelligence version of Visual Studio.  You download one set of tools that gives you all the database and business intelligence tools.

For more information on this podcast see http://sqldatapartners.com/jessica

About Jessica Moss

Jessica M Moss is an architect with New Market Corporation in Richmond, Virginia.  She is a Microsoft SQL Server MVP, is a well-known practitioner, author, and speaker in Microsoft SQL Server business intelligence. Jessica has created numerous data warehousing solutions for companies in the retail, internet, health services, finance, and energy industries and authored technical content for multiple magazines, websites, and technical books. Jessica enjoys working with the central Virginia community and speaks regularly at user groups, code camps, and conferences.

Posted in Podcast Tagged with: , , , , ,

SQL Data Partners – Podcast with Argenis Fernandez

Download PDF

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, LUNs, IOPs, and why he thinks we all need to approach storage a lot differently.  This was a fun interview, I had a chance to learn quite a bit from Argenis. This might be one of those podcast episodes that I need to listen to twice for all that great information to soak in.

Some of the things we covered with Argenis…

  • Why Argenis thinks that drive letters are outdated… and what you should use instead
  • The “Argenis definition” of a LUN, IOPS, and more…
  • The information Argenis thinks all DBAs should know
  • The difference between LUNs, mount points, and data stores
  • How to partition your data files and logs in physical storage vs. in virtualized environments
  • How IOPS fits into the SQL Server storage environment
  • What you should worry about instead of IOPs

I love the reference to spinning storage (traditional hard drives) as spinning rust compared to flash storage.

Spinning Rust

Spinning Rust

So check out this podcast and the other episodes from SQL Data Partners.

Listen now…

Related Links:

Posted in Uncategorized Tagged with: , , , ,

Speaking at PASS Summit 2016

Download PDF

This year in October, I again have the privilege to speak at PASS Summit in Seattle. One of my favorite topics, Database Corruption was the session that was selected.

Here are the details.PASS Summit 2016

When Database Corruption Strikes

You database is running fine month after month with no problems. Suddenly someone reports that their query won’t run. They get an error stating “SQL Server detected a logical consistency-based I/O error”, or something even scarier. Do you know what to do now?

We will walk through three corrupt databases exploring ways to go about finding and fixing the corruption. More importantly we will explore how to prevent further data loss at the time corruption occurs. Learn what things you can do to protect yourself when corruption strikes. Learn how to avoid making things worse, and how to protect your data if things do get worse. You will leave with a checklist of steps to use when you encounter corruption. By the end of this session you will be ready to take on corruption, one database at a time.

 

This is going to be a fun session to present.

If you are going to PASS Summit 2016, please check out my session.

Posted in PASS Summit Tagged with: ,

Podcast Episode 52 – The R Programming Language

Download PDF

Today we released Episode 52 on the SQL Data Partners podcast on the R Programming Language. You might remember a few weeks ago the announcement that I was joining Carlos Chacon as the co-host of the weekly podcast.

the R Programming Language R Revolution Analytics

Episode 52 on the SQL Trail with the SQL Data Partners podcast is with Ginger Grant (@DesertIsleSQL on Twiter) and the topic is the R Programming Language and Revolution Analytics in SQL Server.

This weeks podcast was a fun one for me. I have been hearing lots about R for a while, and I wasn’t quite sure what it is or how it integrates with SQL Server. I learned a bit along the way, and want to thank Ginger for sharing her knowledge of R with us.

Listen to learn more about the R programming language

  • How R got started and why it’s built the way it is
  • How Revolution Analytics transformed R into a useful data science tool
  • How the open source version differs from the Revolution Analytics version in SQL Server
  • How to use R alongside SQL Server

If you are a regular listener I think you will enjoy this episode. If you are not a regular listener, or haven’t heard the podcast before, give it a try it is very educational, and a great way to keep on on SQL Server topics.

After listening to the podcast is is now challenging to think about R without thinking about pirate jokes.

pirate1

Related Links:

Posted in Podcast Tagged with: , ,

DBCC CloneDatabase

Download PDF

The Service Pack 2 release of SQL Server 2014 introduces a new DBCC command called DBCC CloneDatabase.

Books online states:

DBCC CLONEDATABASE should be used to create a schema and statistics only copy of a production database in order to investigate query performance issues. “

Which sounds really interesting. Get all the schema and statistics but no data.

Some immediate uses come to mind:

  • Using in a development or test environment to use production statistics to view query plans.
  • When dealing with Database Corruption, a way to spin up an empty copy of a database for troubleshooting work.
  • A great way to archive the schema without the data.
  • Spinning up multiple developer copies of a database on a test server.

 

 

To clone the database QueryTraining to a new database called AttackOfClones.


DBCC CloneDatabase (QueryTraining, AttackOfClones)

Clone1

Here is the output when you run CloneDatabase.

Database cloning for 'QueryTraining' has started with target as 'AttackOfClones'.
Database cloning for 'QueryTraining' has finished. Cloned database is 'AttackOfClones'.
Database 'AttackOfClones' is a cloned database. A cloned database should be used for diagnostic 
purposes only and is not supported for use in a production environment.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

Note the bit about not supported for use in a production environment.

 

Notice that after running DBCC CloneDatabase, and refreshing the object explorer that the cloned database is read only.

Clone2

 

If you want to set the database read write, and no longer be read only, you can do the following.


ALTER DATABASE [AttackOfClones] SET  READ_WRITE WITH NO_WAIT;

Clone3

 

Here is a list of tables in the cloned database compared to the original database.

clone4

 

I still have some investigation to do here, but this looks interesting.

Posted in SQL 2014 Tagged with: ,

Database Health Monitor – July 2016 Release

Download PDF

I have just released version 2.4.3 the July 2016 version of Database Health Monitor. You can download it now at the Database Health Monitor website. http://DatabaseHealth.com/download

Since the releases have been coming about monthly lately, I have decided to add the month and year to each release so that it has a bit more meaning. The next update should be sometime in August.

This update was about a month in the making, a few new features, a few bug fixes, and overall improvements.

My favorite new feature in this release is the addition of a “Go Back” feature when viewing reports. When you click from one report to another, and then another, and you realize that you want to jump back to that previous report… well you can now, just use the Backspace key on your keyboard from anywhere in database health monitor. Hit it multiple times to jump back further in your history of reports.

New Features in 2.4.3

  • Backspace button to go to the previous report viewed.
  • A new report for SSRS report servers that shows the speeds of the SSRS reports being run. You can find your slow SSRS reports with this.

Fixes in 2.4.3

  • With multiple historic monitoring instances, the CPU load by hour report was only showing the CPU load for the server doing the monitoring, not for the server being monitored.
  • Fixed a bug causing the database overview panel to crash for database instances that with a name that started with a space, or ended with a space. Example: [ DatabaseName ]  Note the spaces inside of the brackets.  It works now.

Download now at http://DatabaseHealth.com/download

Star_of_life

Posted in Database Health Tagged with: , , , , ,

SQL Server 2016 Developer Edition

Download PDF

SqlDeveloper2016

With previous versions of SQL Server to get the developer edition, it was usually available for around $50 USD at Amazon.com. With the introduction of SQL Server 2016 Microsoft has made the SQL Server 2016 Developer Edition a free download. It does however require registration with Visual Studio Online.

To get the SQL Server 2016 Developer Edition, follow these steps.

  1. Visit the Visual Studio online page, and sign up for the Visual Studio Dev Essentials free program.
  2. Once you join, you will get an email confirming your membership.
  3. Login and click on the downloads link. Find the SQL Server 2016 Developer Edition and download it.

Its that easy.  Here is the link to the visual studio online page:

https://www.visualstudio.com

 

The SQL Server 2016 Developer Edition has the same features as the full Enterprise edition of SQL Server, the licensing however prohibits you from using it in a production environment. It’s great for developers and DBAs to use on their individual workstations or laptops.

There are many new features available in the SQL Server 2016 and with the Developer Edition, you can explore all of these.

This free download is a bit challenging to find on the Microsoft Visual Studio site, but once you find it at the link above you will be set.

Posted in SQL Server 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