|
SQL Server ProTop Stories
|
The Top Stories from SQL Server Pro
|
-
Partitioned Tables v. Partitioned Views–Why are they even still around?
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?
By Denny Cherry
No... Oh you wanted more information than that? OK Here goes...
-
SQL Server Database Corruption Part II: Simulating Corruption
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
|