Installing Clustered SQL Servers - Outline, Checklists, Document Sheets</h1> <article> <h1>Installing Clustered SQL Servers - Outline, Checklists, Document Sheets</h1> <p>Chad Boyd — Mon, 30 Jun 2008 06:01:25 GMT</p> 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...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/06/29/installing-clustered-sql-servers-outline-checklists-document-sheets.aspx">read more</a>) </article> <article> <h1>Katmai (Sql 2008) - Transaction Logging Enhancements</h1> <p>Chad Boyd — Wed, 21 May 2008 06:36:00 GMT</p> 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...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/05/20/katmai-sql-2008-transaction-logging-enhancements.aspx">read more</a>) </article> <article> <h1>SSD and SQL - Fragmentation Impact</h1> <p>Chad Boyd — Mon, 07 Apr 2008 20:40:00 GMT</p> 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...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/04/07/ssd-and-sql-fragmentation-impact.aspx">read more</a>) </article> <article> <h1>Partitioning Data for Query Performance - Where's the benefit?</h1> <p>Chad Boyd — Wed, 19 Mar 2008 20:08:00 GMT</p> 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...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/03/19/partitioning-data-for-query-performance-where-s-the-benefit.aspx">read more</a>) </article> <article> <h1>SSD and SQL - SQLIO performance</h1> <p>Chad Boyd — Sun, 16 Mar 2008 21:37:33 GMT</p> 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...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/03/16/ssd-and-sql-sqlio-performance.aspx">read more</a>) </article> <article> <h1>SSD and Me</h1> <p>Chad Boyd — Thu, 13 Mar 2008 00:15:00 GMT</p> 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...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/03/12/ssd-and-me.aspx">read more</a>) </article> <article> <h1>Custom Index Defrag / Rebuild Procedures</h1> <p>Chad Boyd — Sun, 09 Mar 2008 15:10:00 GMT</p> 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...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/03/09/custom-index-defrag-rebuild-procedures.aspx">read more</a>) </article> <article> <h1>Fragmentation Station - Stop #8 - Hands On Experience</h1> <p>Chad Boyd — Sat, 01 Mar 2008 19:34:00 GMT</p> 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...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/03/01/fragmentation-station-stop-8-hands-on-experience.aspx">read more</a>) </article> <article> <h1>Fragmentation Station - Stop #7 - How to address it</h1> <p>Chad Boyd — Sat, 23 Feb 2008 15:12:00 GMT</p> 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...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/02/23/fragmentation-station-stop-7-how-to-address-it.aspx">read more</a>) </article> <article> <h1>Fragmentation Station - Stop #6b - Custom Detection Procedure, Page Linkage</h1> <p>Chad Boyd — Fri, 22 Feb 2008 21:41:00 GMT</p> 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...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/02/22/fragmentation-station-stop-6b-custom-detection-procedure-page-linkage.aspx">read more</a>) </article> <article> <h1>Fragmentation Station - Stop #6 - How to detect it</h1> <p>Chad Boyd — Fri, 22 Feb 2008 21:12:39 GMT</p> In our 6th post in the fragmentation series (I now know we are going to have 8 total) we are going to talk about the different ways to determine if you have fragmentation, and what type of fragmentation you have. This type of analysis will help you determine if you are being impacted by it for your given types of workloads and try to determine if you need to address it . In the final 2 posts coming up in the series we'll talk about how to address it and then finally provide an all-in-one script...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/02/22/fragmentation-station-stop-6-how-to-detect-it.aspx">read more</a>) </article> <article> <h1>Fragmentation Station #5 - What it impacts (performance), and what it doesn't</h1> <p>Chad Boyd — Mon, 18 Feb 2008 04:15:03 GMT</p> In post #4 in the series, we talked about ways to avoid each type of fragmentation - in this 5th post in the series we'll discuss how each type of fragmentation impacts performance, what to expect in terms of impacted operations, and when you possibly might not see any impact from fragmentation at all. As with earlier posts again, we will not be covering things we discussed from each of the 4 prior posts, so consider reviewing posts 1 , 2 , 3 , and 4 if anything seems fuzzy. So, potentially the...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/02/17/fragmentation-station-5-what-it-impacts-performance-and-what-it-doesn-t.aspx">read more</a>) </article> <article> <h1>Fragmentation Station - Stop #4 - How to avoid it</h1> <p>Chad Boyd — Wed, 13 Feb 2008 22:31:59 GMT</p> In the prior post , we discussed the major causes for each type of fragmentation, which followed posts covering storage basics and access methods and what fragmentation is and the types there are . In this 4th post in in the series, we'll discuss what can be done to avoid each type of fragmentation to the extent possible. Also, as in prior posts, we won't talk about things in this post that have been covered in the prior posts, so take a look back to posts 1 , 2 , and 3 as needed. LOGICAL...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/02/13/fragmentation-station-stop-4-how-to-avoid-it.aspx">read more</a>) </article> <article> <h1>Fragmentation Station - Stop #3 - What causes it</h1> <p>Chad Boyd — Thu, 07 Feb 2008 21:49:02 GMT</p> In the prior post ( http://chadhoc.net/post/2008/01/Fragmentation-Station---Stop-2---What-it-is%2c-what-types-there-are.aspx ), we got through discussing the different types of fragmentation, and showed some diagrams of what they would conceptually look like under the covers. In this 3rd post, we'll dig into the different types of fragmentation a bit further to try and understand some of the different causes of each type of fragmentation as well as discuss what measures can be taken to avoid...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2008/02/07/fragmentation-station-stop-3-what-causes-it.aspx">read more</a>) </article> <article> <h1>Fragmentation Station - Stop #2 - What it is, what types there are</h1> <p>Chad Boyd — Wed, 28 Nov 2007 15:27:54 GMT</p> In the prior post ( http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/11/12/fragmentation-station-stop-1-storage-basics-and-access-methods.aspx ), we discussed some basics around storage structures and access methods that will play a key role in understanding the effects of fragmentation as we move forward with this series. In this 2nd post, we'll briefly talk about what fragmentation is, and the different types of fragmentation that exist in Sql Server specifically. In the following posts...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/11/28/fragmentation-station-stop-2-what-it-is-what-types-there-are.aspx">read more</a>) </article> <article> <h1>Fragmentation Station - Stop #1 - Storage basics and Access Methods</h1> <p>Chad Boyd — Mon, 12 Nov 2007 20:20:00 GMT</p> Lots of times I get customers and non-customers talking about fragmentation - everything from what it is, to how it impacts performance, to what objects can be fragmented, to how to check for fragmentation. Quite often (almost always) the discussion inevitably includes lots of points that are not 100% accurate, or open to interpretation depending on what exactly someone is referring to using a particular terminology. There are also quite a few misnomers, or myths even, that exist regarding fragmentation...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/11/12/fragmentation-station-stop-1-storage-basics-and-access-methods.aspx">read more</a>) </article> <article> <h1>Katmai (Sql 2008) - MERGE - More than UPSERT (more advanced uses)</h1> <p>Chad Boyd — Thu, 01 Nov 2007 20:23:00 GMT</p> So, more than likely almost everyone has already heard that Sql 2008 will introduce a MERGE syntax, making it much easier to deal with scenarios where you need to perform 'upsert' type logic (i.e., you have a source data set and you want to put records from that source set into a target set (table, updateable view), but some of the records already exist in the target, and some don't - for those that exist, you want to perform an update, for those that don't exist, you want to insert...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/11/01/katmai-sql-2008-merge-more-than-upsert-more-advanced-uses.aspx">read more</a>) </article> <article> <h1>Katmai (Sql 2008) - consume output from OUTPUT (directly)</h1> <p>Chad Boyd — Wed, 31 Oct 2007 18:13:53 GMT</p> Most of you are aware that Sql Server 2005 introduced the OUTPUT clause, which provided functionality to stream records affected by a write-based statement (i.e. insert/update/delete) into a table variable, which you could then use for other purposes (perhaps to log, or to archive data from a non-partitioned table in a single statement vs. a read/insert/delete operation). This was a great addition to the server, however one thing you didn't have the ability to do was directly consume the OUTPUT...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/31/katmai-sql-2008-consume-output-from-output-directly.aspx">read more</a>) </article> <article> <h1>Monitoring replication latency automatically using tracer tokens (sp_replCheckLatency_allPubs)</h1> <p>Chad Boyd — Wed, 24 Oct 2007 17:10:00 GMT</p> Tracer tokens were introduced with Sql Server 2005 transactional replication as a way to monitor the latency of delivering transactions from the publisher to the distributor and from the distributor to the subscriber(s) (and as a side-effect, you can also get the time from the publisher to the subscriber(s)). A tracer token is basically a small piece of data that is inserted into the transaction log for the given publication just as if it were data for a standard database transaction (i.e. a new...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/24/monitoring-replication-latency-automatically-using-tracer-tokens-sp-replchecklatency-allpubs.aspx">read more</a>) </article> <article> <h1>PAE and /3GB and AWE oh my...</h1> <p>Chad Boyd — Tue, 16 Oct 2007 06:45:00 GMT</p> Many customers (and non-customers) are often confused about all the different memory configurations and options available on 32bit systems (64bit systems do not have so many considerations thanks to the large flat memory space and a VAS size of 16 terabytes as of my typing this)...so, here's an attempt to address each of these a bit and simplify everyone's understanding of them. Before we get started, much of what is going to be discussed is vastly over-simplified for sake of brevity and...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/15/pae-and-3gb-and-awe-oh-my.aspx">read more</a>) </article> <article> <h1>sp_who for Sql 2005 (sp_who2k5)</h1> <p>Chad Boyd — Tue, 16 Oct 2007 06:34:00 GMT</p> I get many requests from customers for something like sp_who/sp_who2 for Sql 2005 with new columns/features/etc. specific to Sql 2005 (sp_who/sp_who2 are both included in Sql 2005, but they return exactly the same information as they did in Sql 2000). Well, here's one version of it - I've included the code below. You'll find a UDF that can be used to query inline with standard Sql statements (I call it "fn_getProcessData") that includes 3 parameters (@activeOnly, @includeSqlText...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/15/sp-who-for-sql-2005-sp-who2k5.aspx">read more</a>) </article> <article> <h1>Katmai (Sql 2008) - Group By Sets</h1> <p>Chad Boyd — Tue, 16 Oct 2007 06:33:00 GMT</p> With Sql 2008 (in the current CTP you have in your hand), thanks to a new extension to the group by clause referred to as 'grouping sets', you now have the ability to use sets of grouping columns in your group by clauses, allowing you to define basically multiple groupings in the same single query - i.e., instead of providing a single group by column-set, you can define multiple 'sets' of grouping columns, and have the resultset include the concatenated results for each set (think...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/15/katmai-sql-2008-group-by-sets.aspx">read more</a>) </article> <article> <h1>Katmai (Sql 2008) - The List of New Features</h1> <p>Chad Boyd — Tue, 16 Oct 2007 06:32:00 GMT</p> So, I've been getting quite a few pings about providing a list of new features to look for in Katmai (Sql 2008) - well, I put together a list below, and this isn't even everything (though it's a lot of the major stuff) - I even tried to categorize it (and some things may appear in multiple categories). I'll be blogging/presenting/etc. on many, many of these in the coming months, so stay tuned: Security/Auditing - Transparent Data Encryption (encryption while data is 'still'...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/15/katmai-sql-2008-the-list-of-new-features.aspx">read more</a>) </article> <article> <h1>Katmai (Sql 2008) - Data Compression (including Backup Compression)</h1> <p>Chad Boyd — Tue, 16 Oct 2007 06:31:00 GMT</p> This post will cover an initial overview of the data compression feature that will be included in Sql Server 2008, and I'll also cover briefly some information on the Backup Compression feature as well (note that these are 2 totally separate and distinct features, since they are very different, as you'll hopefully see by the end of this post). This will actually be a fairly lengthy post, but I'd rather it be that way to ensure I cover as much as possible completely. NOTE that this feature...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/15/katmai-sql-2008-data-compression-including-backup-compression.aspx">read more</a>) </article> <article> <h1>Katmai - Did you know? Inline variable initialization and Compound assignment</h1> <p>Chad Boyd — Tue, 16 Oct 2007 06:30:00 GMT</p> In Sql 2008 (Katmai), a couple of the 'smaller' features that are currently in the latest CTP include inline variable initialization and compound assignment (something you App Dev folks have had for years). So, the following types of code now work in Sql 2008: declare @d datetime = getdate(), @i int = 1, @s varchar(100) = 'test'; -- Show the values... select @d, @i, @s -- Increment i, append to s... select @i += 1, @s += 'ing'; -- Show the new values... select @i, @s; These...(<a href="http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/15/katmai-did-you-know-inline-variable-initialization-and-compound-assignment.aspx">read more</a>) </article> </main></body></html>