« first day (314 days earlier)      last day (2197 days later) » 

8:38 AM
Q: Slow temp table drops in sql 2005

JorgeSandovalI'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 like @bretlowery answer
remember all txns use WAL so the disk volume must respond "OK" or the transactional deallocation of pages.
would this show up as a wait?
coupled with the many, many database
.. on that box
1 hour later…
9:56 AM
hello all
how are you Mark?
good cheers
great to see Mike Walsh and Brent Ozar chatting away in here with you guys :)
@gbn that should of shown up in both waitstats and IO response times. Waits were clear and IO response for all files were sub 5mx
@JackDouglas Yeh, numbers are going up. All good.
9:59 AM
"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."
I said I think that is micro-optimisation
it's on this answer btw
@JackDouglas Icing on the cake. What question/answer is it mentioned on.
where I live full index scans are the norm - I guess for most people it will be a 'maybe'
I've invited Simon in here to chat if he likes...
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
the average site wants to keep it simple with SAME ie RAID10 the lot right?
Almost always, when disk is limited I first want to separate data from logs, often then tempdb from data
10:05 AM
what do you think about triple mirroring everything? I see the point, but the cost/benefit doesn't really add up for me...
Then it depends on the nature of the workload
ah yes - but logs can be shipped off the server right?
what is the logic of separating tempdb rather than striping it up with all the other data?
@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
ASM lets you do it but I never have
Workload dependent again but data -> tempdb or tempdb -> data is quite common
10:09 AM
do you mean moving data from one to the other?
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
thanks - helpful thoughts. I learn a lot from this site I can tell you...
@JackDouglas it comes down to write patterns
log files are 100% write
SQL Server 2005 spank tempdb
data access is almost irrelevant if can be cached
tempdb contention can be a killer for SS under heavy load
10:29 AM
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?
on same spindles?
it is, hence the separation of data and logs
but you run out of spindles quickly unles you have nas or san
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?
I'm in the nah camp also
10:33 AM
scattered data writes don't happen synchronously with the transaction in SQL Server
on checkpoint
you still have to wait for checkpoints eventually
You have in fact got zero data writes occurring most of the time in a typical SQL OLTP database
like what he said :)
@MarkStoreySmith but assuming you have :)
by zero you just mean they are asynchronous?
if I'm spanking 10s of ks rows per second, I'm log file write performance bound
In that, as gbn mentioned, the writes to data only occur on checkpoint
10:35 AM
@gbn I believe you :)
I just don't understand why
when I write the data to disk eventually, I'll interrupt the log file "flow" (couldn't think of better word)
and at high volumes checkpoints will happen more often
so you are either writing to log or data but not both at any one time?
could be both
i see, you don't want to interrupt the log flow
mid transaction checkpoint for example
let me dig up a whitepaper
"Sector ordering" is important too: the log write must happen
10:43 AM
thanks I will have a read around :)
summary: this is why SQL Server folk are anal about log and tempdb placement
you need ASM :)
tempdb can be spanked on reads too, writes for triggers, RCSI and god know what
let's not go into backup volumnes just yet
ASM? Should I look on urbandictionary or persevere with serious sites?
ASM is awesome
first glance looks like windows software raid (sorry) or sql server filegroups (sorry again)
10:51 AM
he he
"Provides near-optimal I/O balancing without any manual tuning"
like raid that is clever enough to vary the stripe depending on the kind of data
marketing weasel bollocks
I use it, it is good. Essential for RAC too.
but that is another story
11:09 AM
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
2 hours later…
12:58 PM
@MarkStoreySmith SO is not our dustbin :)
It isn't? :)
Could be / should be?
1:11 PM
ha ha
we can just delete trash
thoughts on sql_variant anyone (this q)
instinct says very bad idea 99.99% of the time it is likely to be considered
sql_variant is dodgy to use
I ignored the q
shall I follow suit or is there something concrete I can say to Casper ?
I have invited him in here :)
1:39 PM
Hey Jack, thanks for the invite, and thank you for the advise, I'm not trying to be intentionally difficult
I'm just trying to balance concerns, and if there's something I'm missing, be open to understanding what the benefit is.
(With my comments)
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"
@casperOne Hi, thanks for joining us
I'm not an expert with SS - operating in 'hunch' mode really
@Mark that makes sense to me now you say it
When I read the question I wondered if its trying to solve the wrong problem
@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.
1:51 PM
@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.
That wouldn't preclude what I'm suggesting, it would just require a mechanism to version both the root blog address and the post address
@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.
@casperOne we are here to give expert DBA help to folk whatever their background - the question certainly fits here rather then SO IMO
@MarkStoreySmith: It should be assumed that the domain name is not subject to change, that's in a separate table for namespaces/domains.
@JackDouglas: I also figured I'd get a better opinion from the DB side if I brought it to DBA.
@JackDouglas: So you're suggesting I have two tables, one for each key type?
I must be missing something here. Give me 10 to pop out for a smoke and then re-read the question :)
1:54 PM
@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?
@casperOne if you don't need to store extra data alongside then ...
@casperOne I was just about to say that :)
but like @Mark, I'm not sure if we are starting off from the right point
assuming we are though, use a check constraint to ensure exactly one is filled in
@casperOne different or same datatypes?
@gbn one is int and the other a timestamp IIRC - right @casperOne?
@gbn @JackDouglas: Yes, currently, but it could expand to more (I can't see how many more, maybe a smallish varchar) at some point?
@JackDouglas: The other data being stored on that table, what you'd expect on a blog post, datetime published, author, content, etc.
@casperOne code for what you know now
I-d consider...
a check constraint to ensure that only one column is populated
and a computed column
2:02 PM
Ok, I would Post (BlogId int, PostId int, url varchar, urlhash varbinary) constraint on BlogId + UrlHahd
don-t encode the datetime in an int 201111111359
use unixtime or seconds since 01 Jan 1900 etc
and index the computed columns... if you need ine index
@gbn or use a datatime?
cashes with int
clashed with int
I just read the question - short story, don't use somebody else's data as a key in your systems. Everything including postid can change.
In my own blog, I've had to dump the same post and redo it as another post, but kept the same URL. Different postid.
PostId I'm suggesting is a synth key, good old int identity
2:05 PM
Yeah, that makes much more sense. Use synthetic keys and treat the source data as a slowly changing dimension: secure.wikimedia.org/wikipedia/en/wiki/…
I'd do type 2 for that, one record for all fields.
@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?
the only thing you can identify a "post" by reliably is the url string. Make use of a hash of the url for index/constraint
@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.
2:13 PM
@BrentOzar So how do you do that then? I'm guessing there is no foolproof way?
hashing the content is unlikely to work (or be easy) I'd think
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.
Hashing the content there would be easy.
(Says the DBA)
Would have to RSS scrapes unless you define the content region on every source. Via RSS it starts to look pretty easy.
@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.
@MarkStoreySmith RSS is not the chaos the rest of the internet is :)
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.
2:23 PM
@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.
ok, so 'it depends' :)
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.
@JackDouglas if you've got some spare kit to test on, throw tpc.org at it
@BrentOzar so you SAME otherwise?
2:26 PM
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.
my app is lots and lots of scattered writes
Then stick with one big RAID 10.
Thats the point, its synthetic, which is what you want for a generalised test
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
But generalized tests give you generalized recommendations. You don't need generalized tests to figure that out - use one big RAID 10.
2:28 PM
I say generalised, I mean comparative
for most apps it won't make a difference of course
@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.
If you want comparative, just use something like CrystalDiskMark. Takes 3 minutes and you're done, no database install required.
@casperOne sorry I created a diversion
@BrentOzar CrystalDiskMark over SQLIO? I've not looked at it, should it be in the toolbox?
2:30 PM
you do get a minor benefit if you have disk corruption: you can recover more data from the tail of the log if data drives goes bad
@gbn If you have 2 drives in a RAID 10 go bad, you're screwed regardless.
@gbn I'd argue that your better solution for drive failure is fast transaction log backups.
@BrentOzar if they are in the same mirror :)
@BrentOzar of course
@JackDouglas Exactly.
@BrentOzar to where?
same RAID 10?
2:32 PM
@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.
network can't keep up in high volumes
and your network target may be R5 file server or such
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.
If I had that load, I wouldn't have one big volume. Nor a single NIC. NAS or SAN, a separate backup network with separate storage
in a large corporate, the general network canbe slow because of outlook pst files
Outlook PST files where? On the SQL Server or the target file server?
there is no one size fits all solution really
2:39 PM
If you're using decent switches, you won't be dragged down by Joe copying files from the group file share on another server.
he he
@BrentOzar 100MB network ports where I'm sat today! :)
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. ;-)
Tell me about it, I could crack out the modem on my mobile phone and get data faster
@BrentOzar you have to spend to get good HA/DR
I've been lucky this 6 years or so
2:41 PM
@gbn You're talking to a consultant, man. You have to spend to get anything. ;-)
2-site replicated SAN, separate networks
@gbn kvm and drbd gets it on the cheap (cheap not fast) :)
@JackDouglas: How so?
@casperOne no SAN but shared storage (sort of)
@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
2:45 PM
which is the most likely disaster...
did it happen?
@gbn actually I don't jest - I run exactly that setup with SS2008 on top for a client. I've never worked for a company that can afford high-end kit.
@JackDouglas nah, Swiss bank. We had traders to feck things up
my current place (very small) has a USB drive to 800GB of DB backup.
My CV is up to date
@gbn oh boy
there is a budget request in for next year
but my contract is up end of month (as yet)
the fileservers are replicated DFS between UK and Malta, but no offline backup
@gbn I bet you find a job somewhere hot :p
@gbn eh?
Has this room ever had so many people in it?
2:52 PM
@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.
@gbn you mean you delete files one end and they get automatically deleted the other?
@JackDouglas It's baltic today. Down to 17
(Centigrade for the yanks)
@Mark i doubt it!
@JackDouglas yes
@MarkStoreySmith Don't know. :c)
2:54 PM
@MarkStoreySmith how do we know we are all people?
er... monkeys?
@JackDouglas Related: youtube.com/watch?v=B1BdQcJ2ZYY
@gbn one above weasels hey ;)
@SQLkiwi hello
depends on your point of view
2:55 PM
@JackDouglas Hello!
I'm a pessimist (and a yank) so 'mornin ;-)
@gbn Time to raise the datamonkeys.se issue again?
@SQLkiwi Ah, you are Paul White - you know @Mike, right?
just joining up the dots...
@JackDouglas Yes. I have met @Mike
@MarkStoreySmith to a wider audience?
2:58 PM
@gbn To anyone that will listen!
A: We need to formalize a site title/name. Does "Database Administrators" still ring true?

gbnData 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...

datapeople. ?
@SQLkiwi You frequent the MVP discussion list?
@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).
@MarkStoreySmith a rose is still a rose :p

« first day (314 days earlier)      last day (2197 days later) »