In the simplest case, when we insert a new row into a table (and the transaction commits), it will be visible to all subsequent transactions. See xmax being 0 in this example:
CREATE TABLE vis (
id serial,
is_active boolean
);
INSERT INTO vis (is_active) VALUES (FALSE);
SELECT xmin, xmax,...
> Most common tags already exist on a mature site. You should always favor existing tags; only create new tags when you feel you can make a strong case that your question does cover a new topic that nobody else has asked about before on this site.
But I might just be grumpy because Glenn was listed first.
Just out of curiosity, if you guys Google 'row visibility' are the results you get about controlling whether rows are visible in a Reporting Services report?
(I'm just curious, not stating an opinion on whether row visibility is a good tag for MVCC issues.)
Hi all, For those who aren't aware, I'm going into surgery on 22/2. I will be in St. Thomas's hospital (next to Waterloo station) for about a week after that. Beckett Ward, 5th floor, East Wing.
@JamesLupolt Yes, it's arguably one of the top hospitals in the world. The surgeon says the operation has a better than 99% survival rate so I should be OK.
Hi, Welcome to the site. Please post table definitions, sample data and expected output. If you have tried anything so far please post your attempts too. — Tom V27 secs ago
When I look sp_blitz results, it warns me that we have some seriously crappy disks
It's pulling from sys.dm_io_virtual_file_stats to make said claims
This client has apparently complained from day one that the virtualized instance performs terribly.
It's my understanding when you present disks to VMs you can ... present them as an external resource in which case the overhead is very low or you can also virtualize the disk itself which ... looks more transparent to windows but buffers the hell out of actual writes
and it's a very poor understanding I have since I've never done this
Anyways, assuming what I barely remember hearing people discuss is at all accurate, is there a way I can tell from within the guest OS whether they've done the dumb or is that only available to me in the host?
Back in the last 2000s when a company I was at went all in on virtualization and then all out on it, they ran into something that they did wrong with regard to storage
I thought I had heard that could still be an issue at that various sql saturdays but again, it could just be me not having enough context to understand it properly
The simplest VM error (one I've seen a few times as a consultant) is placing multiple databases (and/or OS'es) on the same physical drive/LUN. That'll kill performance every time. :)
In Hyper-V I saw much lower latency when making iSCSI connections over TCP from the guest compared to presenting passthrough disks (with the Hyper-V CSV cache on) to the guests.
If the disks are being accessed over iSCSI from the guest, you'll see TCP connections (typically port 3260) in the guest machine. If they're pass-through you might be able to tell as much from the guest in Device Manager. Trying to see if I have a VM with pass-through disks here.
At that latency they probably just need to do some basic troubleshooting to figure out whether there's a bottleneck on the guest, the hypervisor, a network link, or the storage.
@billinkc You could just run chrystaldiskmark on that server and on another one you know works well from inside the guest to see if there really is a storage issue (or sqlio/diskperf if you need more details)
Preferably outside of business hours ;)
Then present evidence that there is a problem (or not) and have the other teams/consultants assist
Challenge with that, and all the work I'm doing for this quarter is that it's this fixed bucket of support hours. Nobody wants to use those hours to do discovery on things that weren't set up correctly long ago and I'm apparently the only one who looks at things and asks questions.
Probably a good indicator they shouldn't put me on support work...
@Mathematics @Mathematics Hi. Since it seems that you are interested in asking a database structure question, you might find this series of posts of help.
TIL BLOBs on a separate page can cause read-writer deadlocks between a single-row insert and a select on a table with a clustered index and no nonclustereds. Hadn't really thought about that before...
TL,DR: Browser addon that highlights answers and questions by your favorite users!
See it in work:
And the easy config via user's profile:
Just like many other people before us, even we once began to wonder how to favorite a user on Stack Overflow (or any other Stack Exchange site). Wh...
Unfortunately, it seems to be tied to display name rather than user ID (I guess so that it works cross-network). This is great for relatively unique display names, but horrible if you have a user you (dis)like named John.
> Could not install package: 'UTILITY_PROCESS_CRASHED_WHILE_TRYING_TO_INSTALL'. Could not install package because a utility process crashed. Try restarting Chrome and trying again.
You need to go to their profile. If the extension is installed and enabled, you should see a star there somewhere (though I haven't tried it since the profile overhaul).
@AndriyM Was working at a client site with a CRM consultant from another company who kept adding ntext fields in a custom table in Dynamics because "Well, i want to make sure they have enough room to enter the information they want" We looked at the data to be entered, and the max length was 64 characters.
@mmarie I'm usually never sure what type to choose for storing large pieces of text. Don't want to make it too small ("would varchar(1000) be enough or should I perhaps make it 2000?"), but on the other hand, always declaring it as varchar(8000) or varchar(max) (just to play safe) would seem overkill.
> The mysqlpump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server.
I have the following with clause which written in SQL server
WITH myCTE as
(select
ROW_NUMBER() over (order by AmountViewed asc) as RowNum,
AmountViewed,
COUNT(peopleid) as Peeps,
sum(COUNT(peopleid)) over (PARTITION by vpackid) as totalpeeps,
MAX(...
Does using partitioning functions for a table affect log usage in some way that is dreadfully non-obvious?
CIP, I have a table, estimated row size is 300 bytes. Clustered, but non-unique, index on an int column. One unique NCI on two ints. Loading ~ 500k rows via TSQL (INSERT INTO T1 SELECT * FROM T)
Simple recovery model. It runs for ~30 minutes before the log hits about 30GB and we run out of space on the drive
I'm about to drop the pf to test but thought I'd ping the collective brain
TL,DR: Browser addon that highlights answers and questions by your favorite users!
See it in work:
And the easy config via user's profile:
Just like many other people before us, even we once began to wonder how to favorite a user on Stack Overflow (or any other Stack Exchange site). Wh...
I have the following with clause which written in SQL server
WITH myCTE as
(select
ROW_NUMBER() over (order by AmountViewed asc) as RowNum,
AmountViewed,
COUNT(peopleid) as Peeps,
sum(COUNT(peopleid)) over (PARTITION by vpackid) as totalpeeps,
MAX(...
