Author Archives: lotsahelp

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'


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.

SQLskills Immersion Event on Internals and Performance – Day 5


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
  • 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
  • Narrow index keys = shallow index tree = faster lookups
  • Intra query parallelism deadlock

SQLskills Immersion Event on Internals and Performance – Day 4


We just finished the fourth day of SQLskills learning. Today was all on indexes and statistics. We learned about choosing a clustering key, what makes a good one, what makes a bad one. We also have to keep in mind the consequences to non-clustered indexes when choosing the clustered index. We learned about why SQL will choose to use a certain index and why it might use one we wouldn’t expect.

Tipping points are interesting. These are the point at which SQL says, it’s cheaper to do a scan with sequential IOs instead of a lookup with random IOs. I was amazed at how small a percentage is required before it tips to a scan. The narrower the table, the quicker the tip. This isn’t necessarily a bad thing, but you have to be aware of it. Covering indexes aren’t affected by tipping points.

Which brings up covering indexes. Be careful of creating too many and too narrow of covering indexes. You really need to consider the entire workload before creating any new indexes. You should also periodically review indexes for consolidation.

There seems to be many misconceptions hanging around or old best practices that have been superseded when 7.0 / 2000 came out. For example, you don’t necessarily have to list the most selective column in a NC index. List in order of use frequency.

Tonight Paul & Kimberly opened the floor to anyone wanting to present a 20 minute topic. Kendra Little (blog | twitter), Brian D. Knight (twitter), Trevor Barkhouse (blog | twitter), Martin Hill (blog | twitter) and myself presented. I know I flubbed my presentation by going down a rabbit hole. Twenty minutes isn’t enough to go down those holes. I need better presentation discipline and posture.

Today was a bit more difficult staying focused. I think it’s just being in the same environment for almost a week. Hopefully I’ll be more focused tomorrow.

Indexing for Performance (Index Internals)

Indexing for Performance (Internals & Data Access)

  • Have an idea of each table’s tipping points, they can be smaller than you think
  • Dense tables will have smaller tipping points, possibly < 1%
  • Forced parameterization could conflict with filtered indexes / stats
  • Always explicitly specify session options
  • Filtering indexes / stats can offer widely covering indexes when highly selective
  • Most selective column shouldn’t necessarily be first in index
  • If equality based, order columns in index by frequency used

Indexing for Performance (Statistics)

  • Query predicates can be affected by order, for large numbers of predicates
  • Histograms are usually key
  • Update stats before rebuilding indexes as it affects parallelization
  • sp_createstats ‘indexonly’ to create stats on non-statted columns of indexes
  • Out of date stats can affect query plan query cost relative to batch

SQLskills Immersion Event on Internals and Performance – Day 3


Today was mostly about Table Design, Partitioning, with a little of Indexing. Partitioning is a big, complex topics with a lot of intricate details and interactions. If you are thinking about implementing partitioning using either partitioned tables or partitioned views, do your homework. Don’t just implement either without knowing the background of both. This is not a simple subject.

Another key take away is, be smart when choosing between heaps and clustered indexes. Heaps have their place, but good CIs are the better choice in most cases. Also understand the effect that choosing certain CIs will have on your non-clustered indexes. Beware choosing a bad CI.

After hours, SQL Sentry did a demo on their Event Manager & Performance Adviser products. Practical experience behind the products. The disk info they give is amazing. Tracking down blocking and deadlocks look really easy using their tool.

Table Design Strategies & Partitioning

  • Partitioned views more likely than partitioned tables
  • Neither solves all problems by itself
  • A single large table toes NOT have to be one table
  • Partitioned tables – EE only
  • Partitioned views – Any edition
  • Staging filegroups, once clean, Rebuild Clustered Index on destination FG
  • PV: check constraints after load
  • PV: nothing protects you from creating different indexes on participating tables
  • PV: make sure indexes match
  • Re-enabling constraints should be done WITH CHECK
  • PT Function = Logical, Scheme = Physical
  • Do Partition Lab, then read Kim’s 2005 WP, then read Ron’s 2008 WP using DATE, then Online Ops lab
  • Using functions in defining the partition function is a one time calculation upon creation
  • Switching out tables is easier than switching in
  • Requirements are checked when switching in
    • Set next used file group
    • Constraint
    • SPLIT Function
    • SWITCH

Indexing for Performance

  • SQL Server has an error 666, if you exhaust a uniquifier (INT) on a poorly chosen clustered index

SQLskills Immersion Event on Internals and Performance – Day 2


Day two of the SQLskills Immersion Event is over and I have listed the interesting parts below. The food, again, was great, except for the lack of danishes during breakfast (I really like those). Today I got see Kimberly’s teaching style. She’s a fast talker, so you better keep up. Sadly I was a bit more sleepy in class today, though that’s not a reflection on Kimberly. I keep waiting for Kimberly to go into a Joan Cusack type rant, she just has those mannerisms. To tell you how much this couple cares about teaching and the value of whiteboards… they shipped their own 2 boards down here from Washington state, even through what seemed like a pretty big hassle.

Tonight also saw SQL Sentry demo their free Plan Explorer tool. A really cool tool that’s worth checking out. I learned a few tips for using it that I didn’t know before.

Logging, Recovery, and Log File Maintenance

  • Updates request update locks on objects, when all locks are granted locks change to eXclusive as they are updated.
  • There is no such thing as a non-logged operation (in a user database)
  • fn_dblog()
  • Crash recovery is logged as well (compensation records)
  • Fixed width columns are grouped in a log record
  • Variable width columns get individual log record
  • *Committed* transaction log records are always written to disk
  • Uncommitted transaction log records *could* be buffered. Checkpoint causes these to flush to disk.
  • Nested COMMIT TRANSACTION does nothing other than decrement @@TRANCOUNT
  • Nested ROLLBACK rolls back everything
  • Don’t use nested transactions, use save points instead
  • BULK_LOGGED recovery model is kind of useless, your save log space, but not log backup space.
  • If you want to use it, BACKUP LOG, switch to BULK_LOGGED, do op, switch back to FULL, BACKUP  LOG.

Locking & Blocking

  • Fix your code
  • Optimize your indexes
  • Watch out for more than one incompatible lock on an object.
  • UPDATE … @var = col1 = col1-@debit (added in 6.0) Really neat way of updating a row and getting a value back in one query
      INSERT INTO testNum VALUES(5)
      DECLARE @id INT
      UPDATE testNum
      SET @id = id = id + 5
      WHERE id = 5
      PRINT @id
  • UPDATE … OUTPUT (to table variables)
  • No such thing as a nested transaction
  • KB 271509 – sp_blocker_pss08

Nested transactions (no such thing. Though you’ll want to learn about save points)

        SAVE TRAN T3
    COMMIT TRAN T2 ==&gt;@@TRANCOUNT = 1

Snapshot Isolation

  • If you use row versioning, use a FILLFACTOR to prevent page splits
  • Versioning will double your writes (once to data file, once to tempdb) and likely increase tempdb space, although not necessarily.

Table Design Strategies & Partitioning

  • Vertical partitioning could be useful depending on the access patterns and page density
  • Get your data types right
  • If LOBS aren’t used often, considering pushing them off row

PowerShell Script for Paul’s Survey: how is your tempdb configured?

Paul Randal (b | t) posted a survey how is your tempdb configured? Since I just happen to be at a SQLskills event this week I went ahead and created a PowerShell script to get the info across all your servers.

You’ll need a list of servers in a text file ($serverfile), one per line. It will output the list to ‘sqlskills – tempdb proc, file count results.txt’ in the directory from which it ran. Send that file, or the results to Paul.

$serverfile = 'SQL_Servers_PROD.txt'
$query = "SELECT os.Cores, df.Files
   (SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,
   (SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df;"
$dt = $null
#Get list of servers from SQL_Servers_PROD.txt
#Exclude any lines that start with # or are blank
gc $serverfile | ?{-not $_.StartsWith("#") -and $_ -ne "" -and $x -lt 2 } | %{
    $tempdbstats = Invoke-Sqlcmd -Query $query -ServerInstance $_ -SuppressProviderContextWarning
    if ($dt -eq $null) {
        $dt = , $tempdbstats
    } else {
        $dt += $tempdbstats
$dt | ft -AutoSize | Out-File 'sqlskills - tempdb proc, file count results.txt' -Encoding UTF8

The output looks like this:

Cores Files
----- -----
   16     1
   16     8

SQLskills Immersion Event on Internals and Performance – Day 1

Sonny Bryans Smokehouse Bbq would make an interesting classroom

Today was the first day of my first SQLskills event. The event is 5 8 hour days of intense, master level SQL training. This particular event is on Internals & Performance. Lucky for me, it’s being held in Dallas so I don’t have to travel for it.

I plan on blogging my impressions for the day every evening. We’ll see if I can stick to that. But without further adieu, my first day.

General Impressions

Paul (blogtwitter) and Kimberly (blog | twitter) are great facilitators. Paul taught all of today, so I cannot really talk to Kimberly’s teaching… yet. Kimberly handled logistics today.  I was having admin issues with the hotel wireless that she helped solve. Paul & Kimberly both would field any question that was presented… as long as it involved sheep. I was also curious if Paul ever tired of the sheep jokes. He doesn’t, so keep ’em coming.

Session Impressions

Breakfast was great. So many conferences or events are catered mediocre at best. The selection for breakfast was a nice variety of food… including the all important bacon. They also more than just coffee and water which is nice since I don’t drink coffee. Thanks to SQL Sentry for sponsoring the food.

The first half of the day was all about data structures. It had a good, quick pace, until we got into viewing hex dumps of data pages. I know it’s important to learn, just tedious. Data structures are all about how data rows are physically manifested on disk. Also how they are logically built. What the structure hierarchy is. How data compression / sparse columns work.

Lunch was again good food. I hope the food stays this good for the rest of the week. The quality of the food makes a difference in how you feel during the next few hours.

The afternoon was a little more data structures and then data file internals. Again, good quick pace. More about why shrink is bad. Suggestions on where to place data / log files. How SQL Server allocates space. And tempdb. Interesting thing I learned this afternoon was that there are objects in SQL Server that you can only query if you are using the dedicated admin connection (DAC).


Having already viewed the related MCM videos, today was a lot of refreshment / reinforcment of what I had previously watched. The difference being the depth and time taken on each topic. I really like rapid pace classes. It gives me less chance to get sleepy, which is usually a problem for me in a classroom setting.  As Brian D Knight (twitter), not that Brian Knight, said: “The whiteboard instruction alone at the #sqlskills Immersion Event is worth the price of admission over just watching the MCM videos.”


I wanted to add a neat demo to my .NET Powers Activate! Form of PowerShell! presentation that would really demonstrate the use of .NET in PowerShell. I decided to have PowerShell create a PowerPoint presentation from code for my opening demo.

It uses New-Object -ComObject to instantiate a PowerPoint.Application COMobject. The code then proceeds to create a presentation and add two slides, with content, to the presentation. Finally it applies a theme to the presentation. I obfuscated the slide text just for fun by encoding to Base64 and storing the encoded string in the variables at the top. The text gets decoded when getting posted to the slides. The code to encode the text is commented at the bottom for completeness.

#Script my PowerPoint slides
#Theme requires PowerPoint 2007 (v12)
$title = 'Lk5FVCBQb3dlcnMgQWN0aXZhdGUhIEZvcm0gb2YgUG93ZXJTaGVsbCE='
$subtitle = 'RXJpYyBIdW1waHJleQ=='
$contactInfo = 'd3d3LmVyaWNodW1waHJleS5jb20KQGxvdHNhaGVscAp3d3cubGlua2VkaW4uY29tL2luL2xvdHNhaGVscAplcmljLmh1bXBocmV5QGdtYWlsLmNvbQ=='
$app = New-Object -ComObject PowerPoint.Application -strict -property @{visible=$true}
$ppt = $app.Presentations.Add($true)
$slide = $ppt.Slides.Add(1, 1)
$slide.Shapes.Title.TextFrame.TextRange = [System.Text.Encoding]::ASCII.GetString([Convert]::FromBase64String($title))
$slide.Shapes | ?{$_.Name -eq 'Subtitle 2'} | %{$_.TextFrame.TextRange = [System.Text.Encoding]::ASCII.GetString([Convert]::FromBase64String($subtitle))}
$slide2 = $ppt.Slides.Add(2, 2)
$slide2.Shapes.Title.TextFrame.TextRange = 'Contact Me'
$slide2.Shapes |
    ?{$_.Name -eq 'Text Placeholder 2'} |
    %{$_.TextFrame.TextRange = [System.Text.Encoding]::ASCII.GetString([Convert]::FromBase64String($contactInfo))}
if(Test-Path 'C:\Program Files (x86)' -PathType Container) {
    $ppt.ApplyTheme("C:\Program Files (x86)\Microsoft Office\Document Themes 12\Module.thmx")
} else {
    $ppt.ApplyTheme("C:\Program Files\Microsoft Office\Document Themes 12\Module.thmx")
#Command used to encode strings to Base64

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)