Eric Humphrey

Yesterday I was trying to drop some hypothetical indexes in production against a fairly active table. I started causing blocking so I had the bright idea of disabling the indexes first, then dropping. Well, that didn’t help, even when setting DEADLOCK_PRIORITY to LOW. I ended up waiting until early morning to remove those indexes to prevent from blocking other users.

Finding no info on the web about the locks taken during the process of dropping or disabling an index, I set about doing this small bit of research.

The queries I used:
In Window 1 (Execute first)

BEGIN TRAN
SELECT TOP 100 * FROM myTable WITH (TABLOCKX)

* I use TABLOCKX to simulate many updates going to this table.

In Window 2

DROP INDEX [ix_myTable_testIndex] ON myTable

Using Adam Machanic’s (blog | twitter) sp_WhoIsActive I was able to get the lock details easily.

sp_whoisactive @get_locks = 1

When you disable an index, you end up trying to acquire a Sch-M or Schema Modification lock.

<Database name="Sandbox">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="myTable" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

Then the next select query comes along and trys to grab an IS or Intent Shared lock

<Database name="Sandbox">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="myTable" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

So at this point yesterday, I was thinking, maybe I can disable the index and then drop it. Well, ALTER INDEX … DISABLE also waits on a Sch-M lock before it can proceed. I’m not any better off. Even after the disabling the index, SQL Server still tries to grab a Sch-M lock to drop the disabled index. All these rules also seem to apply to hypothetical indexes. I’m sure there’s some internals reason why it does this, but why worry about locks when dropping a disabled or hypothetical index since neither are used by active queries?

Lesson learned, drop indexes when the table usage is low.

*Update

@lotsahelp Yup - all index ops need a schema-mod lock at some point to make plans recompile and bump major schema version number.

-@SQLskills (SQLskills.com), 12-5-2011 01:45:26

I had a need today to disable all of our admin jobs while I moved our admin database to another file location. We didn’t want to get a lot of alerts or job failures during the move, even though it took less than 5 minutes. This script runs best in sqlps or by adding the appropriate snap-ins to your regular console.

cd SQLSERVER:\SQL\[server]\[instance]\JobServer\Jobs
dir | ?{$_.Name.StartsWith('!Admin')} | %{$_.IsEnabled = $false; $_.Alter()} #rerun with $true to enable

A quick script to grab all the SERVERPROPERTY() values from a SQL instance in a table. Useful when you forget what the propertyname options are. Properties obtained from http://msdn.microsoft.com/en-us/library/ms174396.aspx.

DECLARE @props TABLE (propertyname sysname PRIMARY KEY)
INSERT INTO @props(propertyname)
SELECT 'BuildClrVersion'
UNION
SELECT 'Collation'
UNION
SELECT 'CollationID'
UNION
SELECT 'ComparisonStyle'
UNION
SELECT 'ComputerNamePhysicalNetBIOS'
UNION
SELECT 'Edition'
UNION
SELECT 'EditionID'
UNION
SELECT 'EngineEdition'
UNION
SELECT 'InstanceName'
UNION
SELECT 'IsClustered'
UNION
SELECT 'IsFullTextInstalled'
UNION
SELECT 'IsIntegratedSecurityOnly'
UNION
SELECT 'IsSingleUser'
UNION
SELECT 'LCID'
UNION
SELECT 'LicenseType'
UNION
SELECT 'MachineName'
UNION
SELECT 'NumLicenses'
UNION
SELECT 'ProcessID'
UNION
SELECT 'ProductVersion'
UNION
SELECT 'ProductLevel'
UNION
SELECT 'ResourceLastUpdateDateTime'
UNION
SELECT 'ResourceVersion'
UNION
SELECT 'ServerName'
UNION
SELECT 'SqlCharSet'
UNION
SELECT 'SqlCharSetName'
UNION
SELECT 'SqlSortOrder'
UNION
SELECT 'SqlSortOrderName'
UNION
SELECT 'FilestreamShareName'
UNION
SELECT 'FilestreamConfiguredLevel'
UNION
SELECT 'FilestreamEffectiveLevel'
 
SELECT propertyname, SERVERPROPERTY(propertyname) FROM @props

Yesterday, I was tasked with helping a team move their BizTalk databases to another server. These are my notes for anyone else in this predicament.

In our case we had 6 databases to move:

• BizTalkDTADb
• BizTalkHwsDb
• BizTalkMgmtDb (This is the management database that tells BizTalk where everything else is.)
• BizTalkMsgBoxDb
• BizTalkRuleEngineDb
• SSODB

We had originally moved just 5 of these, not realizing that SSODB was part of the pack. Once the dbs were moved, the engineer worked on getting the service talking to the management database. We were still getting errors about connecting. After a little Googling of MSDN, I found there was some changes that needed to be made to the registry and database. The engineers handled the registry, I handled the data changes. Here’s a script of all the data changes I had to make.

USE BizTalkMgmtDb;
GO
 
UPDATE dbo.adm_Group
SET TrackingDBServerName = 'newServerInstance'
    ,SubscriptionDBServerName = 'newServerInstance'
   --More columns exist in this table for other servers
   --but these are just the two we needed to change
 
UPDATE dbo.adm_OtherDatabases
SET ServerName = 'newServerInstance'
 
UPDATE dbo.adm_OtherBackupDatabases
SET ServerName = 'newServerInstance'
 
UPDATE dbo.adm_MessageBox
SET DBServerName = 'newServerInstance'

There was a lot more to this move than just what I’ve put down, but this is the extent of the database changes.

Thomas LaRock (Blog | Twitter) has started a community blog series call Meme Monday.

Here’s my entry:
Community is nice, but displaces work if you are not disciplined.

I would tag others, but I think everyone I would have tagged has already posted.

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: 

OH @lotsahelp: I left my cannon in my other pants #SQLSat63

-@midnightFreddie (Jim Nelson), 2-4-2011 18:30:48

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.

I had the opportunity to present my “What Can You Do With PowerShell?” session in Dallas at SQL Saturday #63. Here are the resources for that talk.

So What Can I Do 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

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.

Overview

Today was the fifth and final day of the Internals & Performance immersion event. I have really enjoyed learning from not only Paul and Kimberly, but my classmates as well. There were many smart people in that room and hearing each others’ perspectives is a part of the class that I’m glad is there. I want to thank Paul and Kimberly for doing these events. I also want to thank my employer for sending me.

We spent the morning finishing up the Statistics module. More interesting stuff about helping the query optimizer pick the best plan. Indexing strategies was even more info about how to provide the optimizer the best info to get a good plan. It’s amazing how much difference this can make.

The last part of the day was Paul rushing through Index Fragmentation. Sadly, the Indexing for Performance modules took up a lot of time, thus this module was shorten. Fortunately, I had seen the videos and what was skipped is in the practice virtual machine image that we got on the first day.

To finish up, Kimberly did a quick presentation on the affects GUIDs have on clustered indexes. If you haven’t seen this presentation or read the blog post, do so. You’ll thank yourself if you are able to make the changes or prevent implementation in the first place.

Tonight was another night at the hotel bar, but it was my first time staying for it and socializing. It’s great when instructors will stick around and socialize. You learn more about their experiences that way and you get to tell your own stories. Now I regret not sticking around the previous nights.

There’s another event in Dallas in March which I probably won’t get to go to. I really want to go to their HA & DR class in Bellvue in August. Hopefully that will work out.

Indexing for Performance (Statistics)

• As of 2005, stat updates are based on column change counter. This means that a column has to change 20% before stats are auto updated.
• (col = @v1 OR @v1 IS NULL) is bad. WITH RECOMPILE won’t help
• Stored-Procedure-Parameters-giving-you-grief-in-a-multi-purpose-procedure.aspx
• sp_executesql = force statement caching
• Inaccurate stats can cause problems, filtered stats over ranges can help
• Have jobs to run scheduled maintenance on filtered stats
• Steve Kass has some interesting math background info for stats
• Beware uneven data distributions
• Don’t trust the query estimate cost comparisons

Indexing for Performance (Indexing Strategies)

• Don’t index to the query plan, index to the query
• OR is similar to UNION… you may be able to get better performance with UNION, UNION ALL even better if you can ensure no dups
• Hash => tempdb
• sp_updatestats will update stats if even 1 row has changed
• ola.hallengren.com
• Relational data warehouse, heavily consider indexed views

Index Fragmentation

• Clustered / Non-clustered fragmentation is the same
• Binary search used for singleton lookup on a page
• Readaheads need logically ordered pages
• 4MB largest readahead possible in 2008
• http://support.microsoft.com/kb/329526
• Narrow index keys = shallow index tree = faster lookups
• Intra query parallelism deadlock