Category Archives: SQLServerPedia Syndication

SQL Saturday #63 – Dallas: Wrap-up

SQL Saturday #63 – Dallas was a great event. The venue was great, although parking was a little unclear (I had to walk all the way around the building to get in.) Breakfast was good, but I’m always disappointed when there are no breakfast juices, just coffee, cola or water.

I Attended Rob Sullivan’s (blog | twitter) session on ORMs. I always like having a different perspective of topics I talk about. He did a good job of showing what to watch out for, but not demonizing ORMs as is often done. David Stein’s (blog | twitter) talk on Zombie Databases was excellent. David was very entertaining to listen to. If you ever get a chance to hear him present, do so.

After lunch it was time for my session, “So What can I do with Powershell ?” (Resources) I got off to a good start: [qtweet 54249436800483328] I was trying to make a clean joke and this came out instead. Oops. Other than that misstep, my session went really well. I was getting plenty of questions and interaction. I think this session was better since I used feedback given by Paul Randal (blog | twitter) when I gave a short presentation in front of him. Stay in front of the audience, not your computer and don’t block your mouth with your hands. I found I’m a much better presenter when I’m standing as it corrects both of those issues.

The gelato breaks were awesome!

I spent an hour talking with Idera about their interaction with the community and it sounds like they’re starting to get it. Quest, Redgate, Confio, and SQL Sentry have been doing community for a while and it’s nice to see the other big vendor get in the game as well.

During the closing ceremonies and raffle, I won a freaking iPad 1 being given away by Fusion-io. Before I had sat down from receiving my prize, I had won the Telerik Ultimate Collection. Talk about lucky, since “I never win anything!!”. I do have intentions to give away the Telerik collection to someone who’ll really use them, after all, what’s a DBA going to do with RAD controls. If you or someone you know does UI development, or forced to do UI development without any help, let me know.

Thanks to NTSSUG (blog | twitter) for putting on a great event.

Change SQL Startup Parameters with PowerShell

Backups Abound

The Annoyance

Sometimes when we’re trying to track down a problem and looking through SQL’s Logs we have to dig through hundreds of backup successful events just to find what we’re looking for. Unfortunately, SSMS doesn’t have an exclusion filter when viewing the logs, so I can’t filter them out that way. We also don’t care about having successful backups in these logs as the info is available elsewhere, including our management database.

The Solution

Trace flag 3226 will turn off logging of successful backups to the SQL Server error log, in addition to the Windows system event log. There are two ways to turn this on. Using the T-SQL command DBCC TRACEON(3226,-1), you immediately enable it, but only until SQL restarts as flags set this way do not survive a service restart. The other way to set it is by using startup parameters for the SQL service.

Don't Forget the Semicolons

In order to change SQL’s startup parameters, you need to:

  1. Load SQL Server Configuration Manager
  2. Right-click and bring up the Properties for the service you want to edit
  3. Click the Advanced tab
  4. Edit the Startup Parameters property by appending -T3226, remembering to separate with semicolons
  5. Click OK

The Better Solution

I wanted to do this quick and easy without all the clicks. I knew I wouldn’t just do this on one server, but maybe tens if not a hundred. So I opened up PowerShell ISE and started cranking out a script to this for me. My first hurdle was how are these startup parameters stored and how can I actually get to them to edit them. It turns out, they are just stored as registry keys. PowerShell works with the registry real well. After finding the key locations, doing some logic to make sure I change all instances on that server, I had my script. I made it generic enough so you could add any startup parameter to SQL, not just trace flags.

Calling the script to update all instances on the current computer is as easy as

PS> .\Add-SqlServerStartupParameter.ps1 '-T3226'

Caveats

The current version does not support SQL 2000, though I plan to release an update that will. I also plan to release a version that supports remote servers. If you are changing a clustered instance, make sure to change it on node the instance is running on as clustered instances overwrite registry settings with whatever their last values were when the instance was running. It’s best to change all nodes in a cluster at once, to be on the safe side.

Download the script: Add-SqlServerStartupParameter

NOLOCK for Developers

What is NOLOCK?

NOLOCK is a table hint for SQL Server that tells the engine to not take locks and ignore any locks when querying that table. This is equivalent to the READUNCOMMITTED table hint.

From MSDN:

Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all). For more information about dirty reads, nonrepeatable reads, and phantom reads, see Concurrency Effects.

So you might be thinking that this NOLOCK thing is great and will solve all your performance problems…

There’s a Trade-off…

What’s the cost of using the NOLOCK hint or setting the Isolation Level to READ UNCOMMITTED everywhere? Well, you cannot guarantee the accuracy of your queries anymore. If the queries can read data in flight, you might read data that never gets committed. You might read data twice. You might read data that’s in the process of being updated or deleted. There’s a lot of mights in there. You need to ask yourself and the person that depends on this query, do they value accuracy or speed.

NOLOCK is a Last Resort

Many of the problems cause by locking and blocking, which NOLOCK gets around, can be solved by basic performance tuning. Limiting the data you are working with by using appropriate WHERE clauses is a good first step. SQL Server by default tries to lock data at the row level and only escalates to the table level if it determines it would be cheaper to do so. It might escalate quicker than you think though. If  two queries are operating on different rows in the same table, they will not block each other unless escalation has occurred. Note that I mention rows, not columns. If two queries are making changes to different columns in the same row, one will block the other until done. Adding appropriate indexes or modifying existing indexes would be a second step. These can speed up readers which let writers in quicker.

Another alternative to using NOLOCK is to use some level of row versioning within SQL Server such as READ_COMMITTED_SNAPSHOT and / or ALLOW_SNAPSHOT_ISOLATION. This allows more concurrency at the cost of increased tempdb usage. It does this by storing old version(s) of a row in tempdb until it is no longer needed. If you’re doing a quick read with no other connection trying to alter that data, then the lifespan in tempdb is very short. If more concurrent operations happen on that row, the lifespan can grow. Before implementing either of these levels, be sure to have a good understanding of the trade-offs of row versioning as it might cause more problems that it solves.

The NOLOCK Resort

Earlier I said to use NOLOCK as a last resort… in transactional workloads. NOLOCK might make a good fit for when you’re reporting out of your transactional database. Reporting is better done from a reporting copy of the data. Sometimes we don’t have that luxury and reporting must be done against the live data. In most cases, we want the transactional workload to take priority over the reports. Telling our reports not to take locks allows the transactions to continue unhindered by locks, though they still might be fighting for CPU and I/O resources. The same rules apply about speed vs accuracy, but usually reports don’t have to be to the second or penny accurate. In this case, NOLOCK may make sense. Just make sure your report consumers are aware that there might be a margin of error.

Further Reading:

Kendra Little (blog | twitter) made up a poster that helps visualize the different isolation levels. She also put together a great list of links about NOLOCK and the other isolation levels.

Paul Randal (blog | twitter) has some great content on locking. He also does a good job of explaining locking & blocking in this video.

Kimberly Tripp (blog | twitter) has more info about indexing.

Presenting for the PASS PowerShell Virtual Chapter

On Wednesday, February 16 at 2:00pm CST I’ll be presenting “.NET Powers Activate! Form of PowerShell!” to the PASS PowerShell Virtual Chapter.

PowerShell’s cmdlets can be pretty powerful on their own, but that’s only part of the power of PowerShell. The .NET Framework is the basis of the PowerShell environment. As such, you have a vast library of operations at your disposal. Learn to use its abilities and save the day without needing a monkey with a bucket.

Update: Here are the files to go along with the presentation. .NET Powers Activate! Form of PowerShell!

Download Video (wmv)

SQL Saturday #57 Slides, Code & Video

This past weekend I spoke at SQL Saturday #57 in Houston, TX.

I want to thank the local PASS chapter HASSUG, the organizers Nancy Wilson (blog | twitter), Jonathan Gardner (blog | twitter), Malik Al-Amin (twitter), the volunteers, the other speakers, and the attendees. It was a great event with a great venue.

I especially want to thank those attendees who chose my sessions out of the many other great ones.

Slides, Code & Video

So What Can I Do With PowerShell?

ORMs for the DBA


Download Video (mp4)
*Thanks to the MidnightDBAs for lending me their mic for this recording.

Automate Downloading SQL Cumulative Updates

Those of us admins that download each SQL Cumulative Update when it comes out may find this script handy. When you request a CU from Microsoft, you get this plain text email with links and passwords. Each link is to a different self-extracting zip file with the corresponding password below it.

When a CU has many files, it becomes a long series of point-and-click to download them all. Then you have to manually put in the password to each file in order to extract it. I wanted an easier solution.

Continue reading

Download the MCM Readiness Videos with PowerShell: Now with BITS

I was playing around with BITS transferring backups between servers and discovered that BITS has PowerShell cmdlets. I was previously using the BITSAdmin Tool which was easy, but I wanted to find out more about the PoSh cmdlets. After reading the docs, I decided to use these cmdlets in my MCM video download script.

BITS stands for Background Intelligent Transfer Service. It is a service built into windows since XP designed to transfer files in the background without interrupting normal user network usage. Windows Update uses BITS to download all the patches that need to be applied to your computer. As stated above, you too can also use BITS to transfer files around. If you regularly transfer large files over the network and get annoyed by how it affects your computer experience, look into replacing the normal copy, xcopy, robocopy commands with it.

My first attempt queued all the files into one BITS transfer. The upside is only one BITS job to manage. The downside is the filenames aren’t written until the whole job completes. Long download jobs often get cancelled and the user would want to be able to listen to or watch the files that had already finished. I wanted the opposite, so I went for one job per file.

The BITS cmdlets require PowerShell v2.0

Download the script