I've run into a problem on our production sql server where temp table objects take a long time to drop (obvious when small and synchronous drop is used). I can't reproduce this on other sql servers (similarly spec'd with same numbers of spindles serving tempdb data files (split in same number of ...
"I usually keep indexes separate so I can use different cache and readahead behaviour -- a full table scan typically benefits from readahead, while index scans do so less often."
For me, its in that category where if everything else is perfect there might be a benefit for certain parts of certain workloads. But, we're talking SQLCAT type installations
@JackDouglas In an ideal world but you have to be able to justify the expenditure for the workload. For starters, it's not widely supported. The EMC array at the place I'm sat now doesn't support triple for instance
ok, but that has to be weighed against the general performance benefit from maxing the number of spindles in your RAID10 by keeping them together (although the benefits tail off after 8-10 drives in our experience) don't know about SAN, just DAS
Absolutely. I'm not prescriptive about it, always depends on the current and anticipated workload and I'm probably tainted by the nature of many of the systems I've got under my wing at the moment i.e. typically OLTP, 99% of access hits buffer pool, heavy tempdb utilisation
like Mark I am very biased by my experience - but in the other direction, always optimising for sequential reads and writes to table data and indexes. From the answer you linked to, I don't understand why logs are the bottleneck - If I write 1GB blocks scattered around my data, that will be 1GB scattered writes and 1GB(ish) sequential writes for log. Why wouldn't the scattered io be the bottleneck?
if i can use the extra spindles you have dedicated to log to speed up my scattered writes even a little bit, that will surely outweigh the benefit of separation?
automatic rebalancing when you shove in an extra disk or lose one (forget dedicated hot spares), variable stripe size, 'raw' speed - no filesystem in the way, nor os caching to get rid of, surprisingly simple to set up for an Oracle product
I'll float a few thoughts, perhaps I'm misunderstanding the question posed...
Why is the "post" not just the varying element of the URL? e.g. for www.myblog.com/posts/abc123 the "blog" is identified by www.myblog.com and a post is identified by "/posts/abc123"
@Casper see above for "sql_variant is dodgy to use" - and that is from an expert. @Mark here falls into that category to, along with Nick and several others
also @Casper, it is good having you here on the site - even if you come from a slightly different background to the guys here. Thanks for your contributions so far
to be honest I've never used sql_variant, can't envisage using it and would probably treat it as a code smell if I found it somewhere. That's not to say it doesn't have its uses, I've just not found one yet.
@MarkStoreySmith: With SEO implications always being tweaked, URLS are subject to change, I'm capturing that URL at the time, but it can't be relied upon to be permanent; it's not a permalink.
@JackDouglas: Thanks for the welcome. And I assume you mean because I'm coming from more of a programming background? I don't have a problem having the question moved back to SO if you want to migrate it out.
@JackDouglas: Perhaps I should just have individual nullable columns on the Post table and a constraint that only one can be filled, and then index on that?
@BrentOzar: That's an interesting stance. Perhaps I need to rethink the model; perhaps have the url in the main table and then have another table for the ids?
@casperOne Exactly. Even the datetime can change - I've had posts accidentally go live, retracted them temporarily, and republished them hours/days later.
@BrentOzar @MarkStoreySmith: BTW, it was never said that this be the primary key. This will definitely not be the primary key. I will be using a synthetic key for the row.
@casperOne It's really a data warehouse project: you're taking data from different source systems. Don't consider the external keys as real keys. They're just attributes. All external data is garbage, heh.
And then when you consider 'em as attributes, it makes more sense to put every external datatype into its own field. You don't want to merge fields.
The constraint part is a cool problem though - what you use to uniquely identify a piece of content on the web.
The URL and the content can all change. You almost want those in separate tables, and then track the combination of the two. (Lends itself well to tracking syndicated content too.)
Now I'm making your job hell, but we do syndicate stuff - publish the same content on multiple sites. Sites like SQLServerPedia.com, SQLblog.com, MSDN.com, etc will all host the same blog posts from end user blogs.
In a perfect world, you'd capture the content of the blog post. Sites like femtoo.com let you define what areas of the page you want to watch by using CSS styles - you hover your mouse over the content area, and it figures out what the CSS tag is that frames the content.
If you scrape via the RSS feed, it'd be a lot easier - that would clearly define the content and the URL for you without worrying about CSS stylings.
@BrentOzar: Yes, it is a data warehousing project, and and beginning to see that they are just that, attributes. I'd like for them to be real keys, but at the same time, you are right, the URL can shift, or the post id can shift (in the case of wordpress) it's hard to keep one or the other. The best I can do is associate everything that I can together and then filter out the dupes by some process that satisfies my threshold.
I'm not even sure you want to filter out dupes. I would want to show that the dupes are related, and I'd want to track the visibleStartDate and visibleEndDate for each URL. After all, you'll probably want to track links between pages, and keeping that history will help.
For example, I've got pages in my blog that have almost exactly the same content in two places. Take a tag archive page and a post page, and the tag only has one associated post. I've got links to the tag archive page in case I write more about that particular topic.
Then I've also got pages (not posts) that have highly similar content because I want the page nav structure for some stuff.
The more I think about it, my blog is just about worst practices for everything, ha ha ho ho.
@Brent a question if you don't mind? Can you point me at some benchmarking that shows why it is better to split logs and data on a high traffic SS instance? eg 8 disks split into 2 4 drive RAID10 arrays, one for logs one for data versus 8 drive RAID10 for the whole lot?
The benchmarking won't be consistent due to drive types, RAID controller types, SQL Server load, etc.
Even the features you use can make a difference. Recently had a client with an ISV app that implemented RCSI in a patch, and suddenly their entire IO pattern changed. The micromanaged config (X drives for data, X drives for logs) was suddenly wrong, and they were borked.
I don't recommend splitting up drives unless you've got at least 20 to play with, or unless you're intimately familiar with the database's load and you control the changes.
If you've got 20 to play with, and you've only got one database, and you need seriously high performance for inserts/updates/deletes, then use 4 in a RAID 10 for that database's t-log files and 16 in a RAID 10 for the rest.
Yeah, I'm all over one big RAID 10 just because it's easier to absorb load changes when the app changes, SQL Server version changes, features are implemented, etc.
The problem with TPC is that it's bullshit synthetic load that doesn't match your app.
but I really just wanted to hear what you just said - SAME is default until you've done a lot of thinking and you are really sure you need to micro-manage
@BrentOzar: I should say that ultimately, these entries will be used for putting data into a document-database for searching, it's the videos in the blog post which I am focusing on; putting that history in a document database doesn't work for me, unless you are saying I should put the most recent history in.
@MarkStoreySmith CrystalDiskMark's results are totally unrelated to SQL Server, but the cool part is that it's ridiculously easy to use - a few mouse clicks and you're off. So easy a caveman can do it.
I'd disagree. If you're talking about 10-12 local hard drives, you're decreasing their throughput by backing up to the same arrays. Use teamed network cards and back up to a shared R10 array.
Remember, if you back up to the same array you're reading from, you just went from sequential to random access - and now you're dropping well below a single network cable.
But I agree - if you're in a situation with crappy switches, no teamed network cards, and a shared RAID 5, then sure, back up locally - but know that you're screwed when (not if) the RAID array fails, motherboard fails, memory fails, etc.
@MarkStoreySmith Good Lord, man. I'm at Starbucks and we have faster WiFi. ;-)
@JackDouglas you jest.. but my last place separated BCP and DR. DR was where IT went off site in isolation, BCP was where business went off site and used primary site. No-one joined the dots for business+IT on same site, break link to primary
@JackDouglas: Stuff happens, and it's quite alright. I think I have a lot to think about, and appreciate what everyone's told me. I might ultimately not place a key on any of this at all.
Data Monkeys
datamonkeys.stackexchange.com
Or maybe "Database Monkeys", "Data Apes" etc
This avoids the potential "DBA" stigma that could scare away NoSQL or BI. Data Professionals is a tad pretentious IMO.
Edit:
I'm more serious then joking
My profile says "Code monkey"
Why?
It isn't p...
@JackDouglas Yes, I have met @MikeWalsh, he is a yank, it is morning, but he didn't strike me as a pessimist. (Slowly getting the hang of this interface).
Data Monkeys
datamonkeys.stackexchange.com
Or maybe "Database Monkeys", "Data Apes" etc
This avoids the potential "DBA" stigma that could scare away NoSQL or BI. Data Professionals is a tad pretentious IMO.
Edit:
I'm more serious then joking
My profile says "Code monkey"
Why?
It isn't p...
