SQL Server Pro News Articles

Partitioned Tables v. Partitioned Views–Why are they even still around?

Kimberly L. Tripp — Thu, 02 Feb 2012 20:23:21 GMT

By Kimberly L. Tripp
Question: Partitioned tables were the new shiny feature in SQL Server 2005 – why are partitioned views even still available? Are there any benefits that they provide? Answer: This is actually a question I get at almost every event at which I speak. It’s a common question and it’s actually VERY complex to fully describe. I suspect it might take me more than one post to tackle all of the issues but I’ll start with the basics here. First and foremost, I’ll start by saying that partitioning is CRITICAL for VLT. What is VLT? It’s about as descriptive as VLDB and it means very large table. (Yes, I just made it up as a new TLA (three-letter acronym) that I’m planning to start using more. However, I’m also going to quantify it a bit more.) Most people speak of VLDBs (very large databases) and they define that as databases that are 100s of gigabytes (many would say that a database that’s 1TB or larger is a VLDB). For me, and in my experience, *many* customers run into problems long before their databases reach 1 TB; their problems tend to start when they have even just one table that starts to get well into double-digit gigabytes. Think about it, a single table that’s 60 GB presents a variety of problems. And, to highlight where the problems occur – think about these questions in the context of your larger tables: Is all of the data recent? How old is the oldest data in your 5 largest tables? How much of that data changing? Not including the new data coming in – what percentage of the older data needs to be modified? How often are you accessing the older data? How long are your maintenance processes against it? Are you replicating it? Do you have enough memory to fit the table (and all of its indexes) in cache? Do you really need to have indexes on ALL of that data? Or, does your data have different access patterns (which might warrant different indexing strategies)? For many, these questions start to pose many concerns at table sizes in the mi

My SAN admin wants to put my transaction logs on FAST storage. Should we?

Denny Cherry — Wed, 01 Feb 2012 10:00:00 GMT

By Denny Cherry
No... Oh you wanted more information than that? OK Here goes...

SQL Server Database Corruption Part II: Simulating Corruption

Michael K. Campbell — Mon, 30 Jan 2012 20:19:48 GMT

By Michael K. Campbell
In my last post I provided an overview of what SQL Server database corruption is – and how it’s almost always caused by problems at the IO subsystem (or disk) level. However, while it’s all fine and well to talk about things in such a theoretical sense, in that post I also mentioned that a great way to get a ‘feel’ for how corruption works is to simulate it a bit on your own. Accordingly, in this post I’ll provide a step-by-step walkthrough of what that looks like by simulating some corruption. Setting the Stage Obviously, when it comes to actively trying to ‘corrupt’ a database there are a couple of caveats that need to be addressed – above and beyond the OBVIOUS caveat that this is something you’d never want to do with production database. (It IS a great experiment to test against COPIES of your production databases though). Otherwise, the caveats to simulating corruption are that you’ll want to DELETE any potential existing data from msdb’s suspect_pages table (which we’ll talk about in a future post), and that you’ll obviously want to make sure that you’ve got a viable backup (even if it’s a simple copy/paste of an existing .BAK or .mdf/.ldf files) of whatever database you’ll be corrupting. In my case, I’m corrupting a copy of the AdventureWorks database that I have running in my environment – mostly because I just hate AdventureWorks so much. So, in my case I’m backing it up like so: Simulating Corruption Then, when it comes to actually simulating corruption, that ends up being a bit hard to do when SQL Server has its ‘hooks’ into the database in question – so I’ll just Detach it using the SSMS GUI, as follows: Once detached, the database is just a collection of ‘zeroes and ones’ that I can then open up and ‘mangle’ as needed. At this point I then just need to find the actual .mdf file for this database (which I happen to know is in my D:\SQLData\ drive on my test server), and then I can open it up with an application other than SQ

Why does using repair invalidate replication subscriptions?

Paul Randal — Sun, 29 Jan 2012 21:45:55 GMT

By Paul Randal
Question: Several times in the last year I’ve been forced to run the repair option of CHECKDB because of corruption affecting a database and backups being unavailable. In one case the database was a replication publication database and I followed the Books Online guidelines to reinitialize it’s subscribers after running repair. Can you explain why this is necessary? Answer: Any time that the REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB (hereafter just called ‘repair’) is used, you need to consider the effect on replication if the database being repaired is a publication database. There are two cases to consider – if the repairs affected the replication metadata tables or not. The simplest is when they did not affect the replication metadata tables. When using merge replication, changes to the publication database are captured using DML triggers. When using transactional replication, changes to the publication database are captured by analyzing the database’s transaction log and converting the logged operations into logical operations on the database. In both cases the logical operations are then applied to the replication subscribers. Neither of these mechanisms allows the capture of operations performed by repair. Repair operations are always direct physical changes to the database structures to fix inconsistencies in the structures (e.g. a database page, table record, or a linkage between two pages), as opposed to physical changes because of queries performing inserts, updates, or deletes on tables. These repair operations cannot translate into logical operations that can be applied to replication subscribers because there are no logical operations than can be expressed using Transact-SQL for the equivalent of the direct structural changes that repair is performing. This means that if any table that is part of a replication publication is changed by a repair operation, the replication subscription is no longer valid and must be reinitialized. As an example,

SQL Server Database Corruption Part I : What Is Corruption

Michael K. Campbell — Fri, 27 Jan 2012 09:55:02 GMT

By Michael K. Campbell
When it all comes down to it there are really only two main things that DBAs need to worry about: making data available to the proper people, and making sure it’s inaccessible to the not-so-proper people. All other considerations are really just appendages to these two concerns. For example, performance is just an extension of making data available to the right users – because if they can’t access it in timely fashion, then it’s not really as ‘available’ to them as it should be. As such, concerns around disaster prevention and recovery should rank high on every DBA’s list. Yet, surprisingly, I find that most DBAs (especially the ‘reluctant’ ones) don’t have enough of a grasp on how to sufficiently protect against database corruption. I therefore thought it would be fun to launch into a multi-part set of posts that provide a hands-on overview of SQL Server Database Corruption Basics – including an overview of what corruption is, why you can’t really prevent it – and what you can do to deal with that reality such that you’re able to ensure proper availability and uptime of your data even when corruption occurs. What is Database Corruption? For the purposes of this series of posts, database corruption is defined as a problem associated with the improper storage of the actual zeroes and ones needed to store you database data at the disk or IO sub-system level. In this sense, the corruption addressed in this series of posts is VERY different from other kinds of ‘disasters’ that might render business data useless (such as when a developer or admin runs an UPDATE statement without a WHERE clause or a software glitch incorrectly ‘mangles’ unit prices). However, while user/application errors or ‘disasters’ are technically much different than disasters caused by corruption, it’s important to note that many of the mitigation strategies outlined in this series of posts for addressing SQL Server database corruption ALSO represent best-practices that can easily be leverag

Will Apple’s iBooks 2 Revolutionize the SQL Server Book and Training Market?

Brian Moran — Thu, 26 Jan 2012 13:13:09 GMT

By Brian Moran
Brian Moran highlights the SQL Server 2012 Virtual Launch Event and explores how Apple’s iBooks 2 electronic textbook publishing platform will change the way SQL Server professionals read SQL Server books and attend conferences.

SQL Server 2012: How to Write T-SQL Windows Functions, Part 3

Itzik Ben-Gan — Thu, 26 Jan 2012 11:18:00 GMT

By Itzik Ben-Gan
Itzik wraps up his discussion of SQL Server 2012’s window functions by focusing on optimization. He provides indexing guidelines for optimal performance, and he describes cases with especially optimal treatment, cases that compute the difference between two cumulative values, cases that expand all frame rows, and cases in which the optimizer can use an in-memory spool versus an on-disk spool.

5 Tips for Developing SQL Server Integration Services Packages

Robert Sheldon — Thu, 26 Jan 2012 09:54:00 GMT

By Robert Sheldon
These five tips help you avoid a few frustrations that might arise when developing SQL Server Integration Services (SSIS) packages so you keep your development efforts moving forward.