PSA: If you edit a question to make it better, consider up voting the result. If you answer a question, consider up voting the question that was good enough to answer.
@ConcernedOfTunbridgeWells Some of it is probably accurate, but that guy seems to be resentful that Mongo killed a product he was building by introducing a Postgres connector (as I recall it).
The aggregation framework he mentions is fun to play with (kind of like having the sed/awk/grep/cut/uniq etc pipeline in the database) but it would be frustrating to have to rely on that in production instead of SQL aggregation and window functions.
@JamesLupolt Yeah, he is probably biased by that ;)
He has a point though. A connector that transforms the data from Mongo objects and kind of loads them into a Postgres db is not the same as a connector (or product) that does the aggregation inside Mongo.
It's like admitting that your (super scaling MongoDB) product can't do some things, and you have to rely on a SQL engine
@ypercubeᵀᴹ Well it depends. If we were to merge sql into sql-query, I'm pretty syre you'd keep your goldhammer. You'd still have a close vote and the ability to flag in any case, of course.
@ypercubeᵀᴹ Indeed. Gold hammers often come with a surprise element. Equally when they don't work because the tag wasn't original. The other solution of course is to double your present score in the PG tag :)
You can only clear these one table at a time (Or by restarting the server). If you rebuild an index, add an index or drop an index that table gets removed from the view.
So by extension if you create a 1-row filtered index on the primary key and then drop that index again you will remove that ta...
@ypercubeᵀᴹ For the longest time, part of my new computer setup was installing cygwin so linux is not so foreign for me. Well, unix(r) isn't so foreign
How should I implement in SQL the scenario depicted in the following Entity-Relationship diagram?
As it is shown, every A entity type occurrence must be related to at least one B counterpart (indicated by the double connecting lines), and vice versa. I know I should create the three tables tha...
I've looked through it but there's a ton of data in there and I'm hardly a db expert. This is all pretty low-level stuff for me, which ironically is over my head.
I'm just a code monkey. I dance for peanuts and bananas.
One thing that I noticed (and I have no idea if this is relevant or not) is that the problematic environment has significantly more files and file groups than the others
Hmm. What would be the best way to get these results somewhere where you can see them @swasheck? Should I just dump them into a google doc or something?
I (and the rest of my team) appreciate the assistance. We don't have any actual DBAs on our team so when weird db stuff starts happening we're basically dead in the water.
I'm also thinking timeouts is not really a SQL Server thing. If you run query on Management Studio or sqlcmd etc it will run from now until the end of days. Timeouts is more of a client thing, eg default ADO connection timeout is 30 seconds.
If that hasn't changed, then could be the sign of an overly busy server (which would almost certainly be apparent in the SQL Server Event Log) or something network related, which probably wouldn't.
The error message is being thrown by the client, let me look up that stack trace
Possibly related to files/file groups - event log says "Autogrow of file 'tempdev' in database 'tempdb' was cancelled by user or timed out after 30 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."
Hmm, percentage files growth are not popular because they get worse over time, eg 10% of 4TB is ... you're gonna wait a long time. MB settings are, generally speaking, better.
It's a "best practice" (again generally) to use MB growths rather than percentage, I'm just wondering if it's the root cause of all your problems here - probably not is what I'm thinking. Pre-sizing tempdb so it doesn't grow is good too.
@wBob the numbers are so huge that it has to be micro ... but msdn says milliseconds
so shrug
it'd be nice to know what's going on that's causing you to use tempdb ... but here are a few low-hanging fruit that i'm noticing: 1) your sql service account is not able to use instant file initialization (the "Perform Volume Activities" exercise that i had you do earlier). put the service account in that group in secpol.msc and bounce the sql server service (during an appropriate window) 2) all of your files appear to be in the same location (unless you have fantastically awkward mount points). change this. request new data stores as mounted volumes for user db logs, user db data files, an…
3) check your index utilization and make sure you have the correct indexes to support your workload
isn't is something like, if you view the data in Profiler it is ms, but the raw data is micro ...? Anyway, cross-ref against the Disk Usage report which has the reported ms figures.
@mikeTheLiar that's still a long time for 3/4 MB growth. i think you have a file layout problem. you could set up all kinds of awesome perfmon counters to get your disk response times
buuuuut that may be overkill for something as red-flaggish as this
Just looking through the wait info, there's a few locks, a few PREEMPTIVE_OS_GENERICOPS, which no-one knows what that is, someone is taking a long running backup, although this looks like an automated one, looks like there was a series of index rebuilds / index maintenance recently. I'm not seeing anything else particularly eye-catching, although there's a lot of stuff, may have missed something.
backups aren't generally blocking but can prevent log truncation (I think). This could lead to filegrowths, percentage filegrowths lead to pain, pain leads to the dark side.
Here's how I would approach this: presize the main tempdb file to 1GB, alter the filegrowth to 128MB. Presize the tempdb log to 512MB, same filegrowth. This may not be the root cause of your problem, but tempdb's current size shows it needs to be at least that size for certain operations. Make sense?
Do a similar filegrowths investigation into your main database.
Perf. tuning can be iterative, so if we had warnings about tempdb in our log this time, and we rule it out by presizing it, next time, if this problem occurs we know it's not tempdb autogrowths - we've ruled it out. We look elsewhere.
@swasheck has also made some good points about instant file init etc, they need to be looked at, alongside potentially splitting tempdb into multiple files, but thats another conversation. Good luck!
@MichaelGreen Client called me last week and said Excel cube function report isn't working because the numbers won't change anymore. We open it up, and user has typed over all the formulas to hard code numbers into a row at some point.
How should I implement in SQL the scenario depicted in the following Entity-Relationship diagram?
As it is shown, every A entity type occurrence must be related to at least one B counterpart (indicated by the double connecting lines), and vice versa. I know I should create the three tables tha...
