Chad Boyd

Invoke-Nz - Powershell script similar to Nz VBA function

2008-10-14T14:33:00Z

I got a few pings about the Restore-SqlDb script requiring the Invoke-Nz script to execute - here it is. Similar functionality to that of the Nz function in VBA - basically pass along 2 values and the function will return the first value if it is present (i.e. non-null), otherwise the second (could also say it's similar to the isnull() tsql function, or a coalesce with only 2 parameters). Sorry I missed this with the original Restore-SqlDb posting. Invoke-Nz.ps1 Thanks to everyone who reminded...(read more)

Restore-SqlDb - Automate a Database Restore (improved with Powershell)

2008-10-13T04:01:00Z

UPDATE: This script requires the Invoke-TryCatch.ps1 and the newly posted Invoke-Nz.ps1 scripts as well - thanks to all who pinged me about the Inovke-Nz.ps1 being required. A while back I posted on Automating a Database Restore and it was quite a popular post, I also received multiple emails with requests for other features in the script such as allowing a restore to a different instance, extending support for SQL 2008 (if you try that script on a 2008 instance you'll notice an error occurs...(read more)

Try/Catch/Finally with Powershell

2008-10-05T00:10:00Z

I ran across Adam Weigert's posting/script that provides a PoSh script that allows try/catch/finally like behavior - very cool. I started using the script myself and found that as much as I loved it, I wanted to add some additional 'features' to what it provides. I was looking for pipeline support (including the ability to throw 'soft' errors in the pipeline that would allow the pipeline to continue processing), some optional verbose debugging/error related information (particularly...(read more)

Estimating Data Compression ratios for all...

2008-09-22T22:41:00Z

One of my favorite features with SQL 2008 has been Data and Backup compression (which I discuss in more detail technically here ) - this is not only because of the actual functionality it brings to the table, but also because of all the technical intricacies that it involves and the impact it can have on many other fun topics (fragmentation, storage, internals, etc.). Of course, the functionality is pretty cool too... One customer of mine was asking how they can get an idea of the level of compression...(read more)

Automate a Database Restore

2008-07-30T01:19:00Z

NOTE: See an updated version of this functionality using Powershell and supporting some additional functionality here . It's quite common to see automated/custom procedures for backing up a database/log - nearly everywhere I go companies have custom backup procedures and processes to handle backups, logging of backups, naming standards, locations, etc. However, it's rare that I find processes/procedures in place to automate the restore of database/log backups - so rare in fact that I can...(read more)

Installing Clustered SQL Servers - Outline, Checklists, Document Sheets

2008-06-30T06:01:25Z

A high percentage of my interactions with clients revolve around clustering SQL Server in some manner or another - could be to review a high-availability solution, could be to stand up a new clustered instance of SQL, could be to troubleshoot a system that is having stability issues, etc. In all cases, I start with a set of checklists and informational-gathering sheets and tools to both review the existing state of the system, and also to understand the system we are working with - once we have all...(read more)

Katmai (Sql 2008) - Transaction Logging Enhancements

2008-05-21T06:36:00Z

Given some of the "larger" features being introduced with Sql 2008 which are getting lots of coverage and attention (understandably so), there are actually quite a few "smaller" features that are included which will provide a great deal of benefit to SQL Server users everywhere - one of those "smaller" features that hasn't been getting as much attention is improvements made in the database engine for bulk-logging / minimal logging of standard INSERT INTO statements...(read more)

SSD and SQL - Fragmentation Impact

2008-04-07T20:40:00Z

In the last post on Solid State and it's impact on SQL Server operations , we looked at a variety of different IO patterns and sizes on multiple systems to see where and when SSD would help out and when it wouldn't. If you read the post and analyzed the data, you could clearly see the huge gains Solid State provides with random read IO over traditional spindles - writing and large sequential reads seem to still favor traditional spindles. So, assuming you are in a system that performs lots...(read more)

Partitioning Data for Query Performance - Where's the benefit?

2008-03-19T20:08:00Z

I spend a lot of time interfacing with SQL engineers of all types (developers, dba's, architects, ETL engineers, etc.) and something I hear very frequently is "I want to horizontally partition my data to improve my query response times to customers". This usually makes me follow up the statement(s) with questions of my own such as "what types of queries are you trying to improve response times on by partitioning your data?" or "what systems are you thinking of employing...(read more)

SSD and SQL - SQLIO performance

2008-03-16T21:37:33Z

Let's start the SSD blogs off with a comparison of the SQLIO tool running on my laptop and compare the results to some other traditional spindle based systems. First, let's outline the systems we'll be comparing throughout the SSD blogs: First, my laptop. This is a loaded Dell XPS M1330 laptop running Windows Vista Ultimate x64. Specs include a 2.2Ghz Intel Core2Duo T7500 processor (dual-core, 800MHz FSB, 4MB L2 cache), 4gb DDR2 SDRAM @ 667Mhz, and a single 64gb Solid State Drive (Samsung...(read more)

SSD and Me

2008-03-13T00:15:00Z

I recently purchased 2 laptops with Solid State Drives in them (my loaded Dell XPS 1330 and my wife's Apple MacBook Air ), and I have to say, I will never, ever again own a computer that doesn't have a Solid State Drive in it (at least not until they make something even better). These suckers are great, well worth the money IMHO (of course, I am a techie and find huge value in having the latest and greatest of technology to work with, so that may impact things I'm sure). Of things to...(read more)

Custom Index Defrag / Rebuild Procedures

2008-03-09T15:10:00Z

I often get asked for custom procedures to help with index maintenance, including things such as reorganizing, rebuilding, stats updates, etc. There are quite a variety of these out there today (a simple google search will get you a bunch), and all have their pros/cons. The big thing I generally stress to customers when asked about this type of thing is to do what is best for their environment - a custom procedure that works perfectly for me in my environment(s) might be completely wrong for your...(read more)

Fragmentation Station - Stop #8 - Hands On Experience

2008-03-01T19:34:00Z

In our final stop for the fragmentation series we're going to walk through a complete script that will cover almost everything we've discussed in the series, including: New object creation and allocation (heaps, clustered, non-clustered indexes) Impact of parallel operations vs. serial, sort_in_tempdb, etc. Full scan, range scan, and singleton seek performance tests against cold and warm caches with: No fragmentation Logical fragmentation (unordered pages with high page density and low page...(read more)

Fragmentation Station - Stop #7 - How to address it

2008-02-23T15:12:00Z

In our 2nd to last post in the Fragmentation series , we'll discuss our options on addressing and removing/correcting fragmentation. In our next and final post in the series, we'll end with a full-fledged SQL script that will walk you through all the different things we've talked about in the series to give you some up close and personal experience with what we've gone through. Once you've detected that you have fragmentation , understand what it impacts in your workload , and...(read more)

Fragmentation Station - Stop #6b - Custom Detection Procedure, Page Linkage

2008-02-22T21:41:00Z

In post #6 of the series, I mentioned that I often get asked for alternative methods of looking at fragmentation, a way to view the page chain or linkage, and ways to get insight into which pages are out of order in a given structure. There is nothing super graceful to be honest, but you can make use of a DBCC statement (DBCC IND()) to get page linkage and order data, then using some relatively complex querying against the data captured you can see some of this type of information. This post has...(read more)