Stack Exchange
log in

Mark Storey-Smith

 The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....
5
Mark Storey-Smith
Aug 28, 2024 14:23
Thanks for the typically epic answer to my question @PaulWhite 👍
Mark Storey-Smith
Jun 6, 2024 15:02
Long time no see folks
Mark Storey-Smith
Aug 4, 2018 16:14
@ConcernedOfTunbridgeWells Not enough hours in the day type problem. Will try and get back to regular lurking ;)
Mark Storey-Smith
Aug 4, 2018 16:14
@dezso Settled in Bucharest. Back to London for a weekend a month typically, for a West Ham game. Still a season ticket holder, for my sins :)
Mark Storey-Smith
Aug 3, 2018 14:12
It's a worthy topic
Mark Storey-Smith
Aug 3, 2018 13:51
@Marian summoned me
Mark Storey-Smith
Aug 3, 2018 13:47
hola :)
4
Mark Storey-Smith
Aug 4, 2017 17:57
For the record, I'm a Speyside man. Macallan being the default go to.
Mark Storey-Smith
Aug 4, 2017 17:55
How did a whisky discussion get started on International Beer Day!
Mark Storey-Smith
Aug 4, 2017 14:57
@ConcernedOfTunbridgeWells Hey buddy! Bucharest most of the time through the summer months as no footy on :)
Mark Storey-Smith
Aug 4, 2017 11:58
@gbn When did you leave Malta?
Mark Storey-Smith
Aug 4, 2017 11:24
Now I'm here, I might settle back in for a while!
Mark Storey-Smith
Aug 4, 2017 11:24
I primarily popped in to see if @gbn was still kicking around as I might swing by Malta in a couple of weeks
Mark Storey-Smith
Aug 4, 2017 11:24
Same old :)
Mark Storey-Smith
Aug 4, 2017 11:19
gday folks
6
Mark Storey-Smith
Jul 12, 2016 17:09
@ConcernedOfTunbridgeWells Might be able to make it back that week but I've got something on over here that weekend. How's the old ticker btw? :)
Mark Storey-Smith
Nov 25, 2015 18:09
@ConcernedOfTunbridgeWells Is there a reservation?
Mark Storey-Smith
Nov 25, 2015 17:35
@ConcernedOfTunbridgeWells Roger!
Mark Storey-Smith
Nov 19, 2015 14:00
@dezso At least half my friends and family think I'm Budapest. I keep correcting, they keep forgetting.
Mark Storey-Smith
Nov 19, 2015 13:47
@ypercube Yip, flying back that morning!
Mark Storey-Smith
Oct 23, 2015 13:26
gday folks
Mark Storey-Smith
Sep 11, 2015 12:02
And hello, long time no see etc :)
Mark Storey-Smith
Sep 11, 2015 12:02
@PaulWhite Encountered any multi-site clusters at clients?
Mark Storey-Smith
Sep 10, 2015 15:33
I've seen a proposal for this config but without a standby node at the primary site, which doesn't sit comfortably. Just wondering if its commonly done.
Mark Storey-Smith
Sep 10, 2015 15:33
@swasheck yes, nodes across multiple sites like this
Mark Storey-Smith
Sep 10, 2015 15:30
Anyone running multi-site failover clusters (SQL Server)?
 

 Discussion on question by Robert Gann…

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Aug 29, 2017 11:18
Can you post the execution plan XML please. I might be reading it wrong late in the day but that looks like a three deep nested correlated sub-query. Probably just bad query, bad plan, pegging a single core.
 

 Discussion on question by bumble_bee_…

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Aug 29, 2017 10:57
I'm intrigued. I've not encountered anyone virtualising with a 1/1 guest to host ratio, how does this simplify administration?
 

 Discussion on question by S. Robert J…

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Aug 29, 2017 10:35
@EricHiggins That's a somewhat absurd comparison. You are also implying that a clustered index is more expensive to maintain than a heap, which is simply not true.
Mark Storey-Smith
Aug 29, 2017 10:35
@EricHiggins re: performance hit of maintaining the CIX over a heap? That's priceless.
Mark Storey-Smith
Aug 29, 2017 10:35
@S.RobertJames Create/drop will rebuild the table once and every non-clustered index twice. The alternative (copy to a new table, rename) does it once. Lazy way or the right way, take your pick :)
Mark Storey-Smith
Aug 29, 2017 10:35
@EricHiggins You're missing the point. If the vendor design necessitated a heap, they should of had a reason for it and they should have mitigation for it. More likely, it's a mistake/omission/fubar. And no, creating and dropping a CIX is not "the right path".
Mark Storey-Smith
Aug 29, 2017 10:35
@S.RobertJames From your update, it sounds like the vendor is covering their backside and avoiding addressing the problem. "That's a Microsoft question" stinks as a response. They designed the tables as heaps, they should have designed appropriate maintenance. Preferable would be to add the CIX and leave it, if there is a suitable candidate column. Alternatively if it must remain a heap, copy the data to a new table, drop old, rename.
Mark Storey-Smith
Aug 29, 2017 10:35
Why do they need to be defragmented? Are they particularly large? Have you identified them as the cause of a performance issue? Do you have a support contract with the vendor (which may be voided if you alter the database)?
 

 Discussion on question by prog_sr08: …

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Aug 29, 2017 10:15
@flores2013 Try this particular query at MAXDOP 1 also. The row count (760k) and data size (8MB) seems low for a parallel plan to be chosen... If you run sp_configure, what is the value reported for 'cost threshold for parallelism'?
Mark Storey-Smith
Aug 29, 2017 10:15
@AaronBertrand Snap. My only observation from the plans was 32 DOP on new server and 8 on old. I'd be tempted to test server wide MAXDOP 16 (as its 16 physical cores).
 

 Discussion on question by Tyler Davis…

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Aug 29, 2017 10:03
@Kickstart So change it when the requirement emerges. Don't design and build for possibly/maybe, be ready to adapt when the inevitable change arrives.
Mark Storey-Smith
Aug 29, 2017 10:03
My gut reaction is that your professor is an idiot, or you've misinterpreted something he's said. There is a good case for adopting his approach where the field is treated as atomic i.e. it doesn't change, or when it does the whole field changes, not a constituent part of it. As a blanket policy it's nonsense.
 

 Discussion on question by Brian Hoope…

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Aug 29, 2017 10:02
@FilipDeVos Could you post your two comments as an answer?
 

 Discussion on question by Jon of All …

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Aug 29, 2017 10:00
Ideal would be DDL for the objects in play, along with the statistics. As demonstrated by the answers to Unexpected scans during delete operation using WHERE IN, the more we get to work with the better :)
 

 Discussion on question by Ezi: How to…

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Aug 29, 2017 09:32
How do you know these large(ish) tables are the cause of "slowing down the system"? What observations have you made that led you to that conclusion?
 

 Discussion on question by somecallmem…

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Aug 29, 2017 09:16
On reflection, I've VtC'd. The answer to "Which database could handle storage of billions/trillions of records?" is "all of them" but your question is something else. Following the comments conversation it appears you're looking for a tier 1 network analysis tool, which is likely cheaper to buy than build. Might be better off closing this and posting that question at ServerFault.
Mark Storey-Smith
Aug 29, 2017 09:16
I guess the last question probably should of been the first... have you got a $1M+ budget?
Mark Storey-Smith
Aug 29, 2017 09:16
How real-time do you need? Live, pseudo real time (minutes to hour latency) or will queries always be looking at historic data?
Mark Storey-Smith
Aug 29, 2017 09:16
What questions will you be asking the database? You mention a need for a timely query response but no indication of typical enquiries.
 

 Discussion on answer by yoel halb: Pl…

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Aug 29, 2017 07:36
Hate to admit it but I have been giving some thought as to how best describe the reasoning behind my answer. The crux of this rather prolonged thread is that (aside from banging on about tempdb and "spindles" despite the Q&A referencing transaction log and SSDs) you appear to misunderstand the nature of "write contention". So, I can either revisit my answer to explain why at this scale it's just IOPS that matter, or you could post a new question on the topic. A fresh question would allow this communities contributors to offer their thoughts outside the confines of a comment thread. Your call.
Mark Storey-Smith
Aug 29, 2017 07:36
I don't think I need to say anything other than "spindle" :)
Mark Storey-Smith
Aug 29, 2017 07:36
@yohal No, it is merely an example of debunking dogma. As for why you shouldn't blindly follow Microsoft recommendations without question, try this example for starters.
Mark Storey-Smith
Aug 29, 2017 07:36
@yohal I suspect I'm wasting keyboard clicks here but does this answer regarding tempdb mechanics lead you to question SQL Server dogma in anyway at all?
 

 Discussion on question by Levi Johans…

Imported from a comment discussion on dba.stackexchange.com/qu...
Mark Storey-Smith
Apr 19, 2016 12:47
"This system is used by hundreds Search and Rescue teams..." - is this "for real"? If so step away from the dial and call an expert. And stop restarting SQL Server as a fix. Your restart doesn't fix anything, it just makes all the traffic go away.