Our vendor changed column widths on almost every column in the entire database. The database is around 7TB, 9000+ tables. We are trying to create an index on a table that has 5.5billion rows. Before the vendor's upgrade we could create the index in 2 hours. Now it takes days. What they have done...
@ypercube The OP only says that the vendor changed the column sizes and it looks like the party who has to (re)create the indices is the OP (or their company). (I'm assuming that includes clustered indices as well.)
but if the columns changed include the PK columns, then the clustered index was "changed" with all the other indexes when the columns changed (not sure if there was an actual change in any of the indexes or it was a metadata only change).
My guess would be (but only a guess) that A) the column sizes changed but this was only a metadata change. Somehow when they want to create a new index after that, it involves some not metadata changes. or B) the increased column sizes make the index creation much slower because the server assumes it has to use much more memory.
@ypercube Yes. I assume the question exposes some deep dark internal thing where the possibility of exceeding the limit disables some crucial optimization. Not something I know offhand. Also, 50 bounty seems stingy.
@ypercube I know. Given Geoff's rep, it seems stingy.
50 unicorns = 5 upvotes.
This question would benefit from some code for creating the table and indexes, and a specific example of an operation that now takes an unreasonable length of time. Also, be specific about the version and build of SQL Server. It seems perfectly possible that the potential for exceeding the maximum key length results in a much less efficient codepath. My feeling is that this question went unanswered originally because it is an edge case that would require a significant time investment to investigate. — Paul White ♦4 mins ago
Honestly, the first time I saw it I was tempted to close it - broad, unclear, localized, iceberg - take your pick. I must have been distracted, because I didn't comment at the time.
> What they have done is increase any varchar(xx) size to varchar(256).
@dezso Kind of explains why they might not have wanted to take the time to decide which tables/columns actually needed the change and which ones didn't. ("Oh, it'll take too long, let's just increase all of them and be done with it.")
Wouldn't really make the decision less stupid, though.
What is Stack Overflow's long-term solution for the Help Vampire problem?
Quote from article follows:
Identifying Help Vampires can be tricky, because they look like any ordinary person (or Internet user, whichever is lesser). But by closely observing an individual's behavior using this hand...
I'm a MongoDB noob and trying to import data from an Oracle table.
My Oracle table has 1 unique index on a single column, which is an id (a number - primary key).
I successfuly imported data to MongoDB and added a unique index on the same column, so as to have the 'same' structure.
Now, when I...
Hi, Can we add answer to 2 months old question dba.stackexchange.com/questions/108457/… I dont find answer given is correct ...or we can leave it as it is. Any recommendation on how to deal with Old thread if you see unanswered
Surprised? That a relatively new framework (I wouldn't even call Mongo a database), based on no sound foundations is not as efficient as an old product (Oracle), designed to store and retrieve data efficiently and had decades to improve and is based on a sound theoretical foundation (that is relational theory)? — ypercube1 min ago
> An other point to note is the task manager shows memory used by all OS processes so why are you even referring to it and making wrong conclusion about SQL Server. Remember, Task Manager is a Windows tool, not a SQL Server tool.
@AndriyM In practice, I've seen a man who could make MongoDB working - he still was very happy when we could finally burninate it (which happened because with the proper design and indexes PostrgeSQL could do the same task much faster)
> Ask me any programming question, I'm available at your service for $10/hour, just chat me the question first on Skype (kiswono.prayogo), if I confident to answer, transfer the payment to my PayPal, then I'll try my best to answer.
SSIS Package running on a schedule is apparently just stuck at "Created Execution". Execution log has no messages. Thought? SQL Server 2012 if that matters. Only thing I found is stackoverflow.com/questions/30899054/… but that doesn't seem to be the issue here.
I have created a small test for checking this:
CREATE TABLE jsonb_test (id serial, data jsonb);
Then inserted some generated data:
INSERT INTO jsonb_test (data)
FROM (SELECT i AS bla,
ARRAY[i, i+1, i+2] AS foo,
ARRAY[('v' || ...
This is my linux version
Linux ArRac1 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
I'm trying to install MariaDB from this link using the RPMs since I do not have internet access
Since I ...
First of all I agree completely with Aaron Bertrand when he suggested in the comments you move the SQL into a stored procedure. This is also the clear winner as far as "the proper way to wrap a set of SQL operations in a .NET program" by the way.
Since you're worried about managing code in two p...
In which @Erik is revealed as a (gasp) VB.NET coder.
@dezso Actually VB3 probably wasn't as bad as subsequent versions. From VB4 onwards they did a major re-architecture to accomodate COM. This resulted in VB having two distinct and incompatible type systems and a horrendous garbage collection system that was basically a thin layer over COM.
@TomV I once worked on extending a straight port of a classic ASP site to ASP.NET. That was the worst C# code I've ever had to touch. The developer put all the C#/SQL code inline, emitted HTML via Response.Write, and had one 20k line master class that was instantiated at the start of every page. It was heinous beyond words.
Essentially, from VB4 onwards they needed to re-jig the architecture to support COM. This resulted in a number of architectural bastardisations such as automation-compatible types (which were incompatible with many native VB data types), variants, single-threaded apartments, and a reference-counting garbage collector tigthtly coupled to COM. It was very prone to silently leaking COM object references if you didn't mind your P's and Q's.
For the benefit of the room, and the moderators handling flags, I will say nothing more on the matter
Here's an odd request - I need a crappy query such that it takes more than 30 seconds to get the metadata back from the plan. I had thought I could fake it with a waitfor delay but that's not happening
Basically, when an SSIS package goes to set the metadata for a source in the data flow, it parses the query to find the first thing that returns a resultset. It then uses either fmtonly or sp_execute_first_resultset to get the meta
This is my post, but I must have accidently submitted as a guest, and I don't have enough points to comment on my own post so I am replying here.
When I enter your command I get:
]$ cat /etc/passwd | sed 's/^([^:]):.$/crontab -u \1 -l 2>\&1/' | grep -v "no crontab for" | sh
sed: -e expression ...
just so the mods are aware in case they aren't already, we have another lost soul
we have a very old Oracle 188.8.131.52.0 database here that - unfortunately - is still used heavily as every day production system.
I want to start making plans on how to get this DB upgrade to a recent Oracle 12c.
Unfortunately, both DB versions don't seem to be able to talk to each other anymore (...
some random articles from there: > ‘I’ll put a hole right through your head,’ off-duty cop tells motorist > Dead man with weapons stash linked to missing woman who believed he was an alien savior > ‘Monster’ disembowels girlfriend after she calls out ex-husband’s name during sex
For the admins in the crowd, if you're reviewing the sql log, what metric do you use to separate the wheat from the chaff? Would it be as simplistic as looking for the word Error (excluding DBCC checks that state "without errors")?
Comment out the WHERE clause in these two views:
Change the view catalog.event_messages by commenting out the WHERE clause:
--WHERE opmsg.[operation_id] in (SELECT [id] FROM [internal].
because there's such a diversity of things that can appear there, i've really only ever found it helpful to parse it on-demand. so dumping logs to a table and then allowing someone to fall back to the date/time of incident and sift through the logs to see what may have happened
@PaulWhite Thanks for the feedback, Paul. I had never done a bounty before, so I just took the default amount to get some additional eyes on another user's question that I found interesting. Good to know that people really look to the specific amount of the bounty as a meaningful indication of "generosity" :) And feel free to let me know if I'm not using the system correctly!
@GeoffPatterson Yes it's a perfect use of the bounty system. I don't care for rep myself, but I can imagine other people won't go nuts over 50, whereas they might be tempted to expend the personal effort for a couple of hundred or so. This is just my impression though; I might be wrong.
I just came across an interesting user who has more gold badges than most top 20 users. The low reputation of below 10,000 made me wonder how that's possible. I found out that he's continuously spending his reputation on bounties.
Would someone mind building a SEDE query that allows us to find t...
@GeoffPatterson ... i love your contributions ... save one. pastebin is blocked here. think you could use something else (like gist.github)?
also ... i love this comment
Are you willing to share the name of the Vendor, so we can steer clear of them? It sounds like they don't understand the basics of designing databases for performance, yet their product supports billions of records. — datagod2 hours ago
Just working from Paul's comment. The key difference in the statement in Books Online and your interpretation:
It says: ...one row per cached stored procedure plan.
You read: ...one row per cached stored procedure.
You can check which plan attributes are different and leading to differ...
@swasheck Also, should I edit my script into the original question? Wasn't sure if that's something I should do or not since I'm not the original poster and it's not 100% clear if the script is the same issue they had.
I agree with Hector's assessment. It will probably be more flexible just to keep your original design. The piece that you might have missed to make it work is using recursion to traverse up the tree. I don't know much about recursion in MySQL, but this answer on StackOverflow feels like a good r...
Is the vault a commercial product? If yes, are you affiliated with the company (coworker, retailer, other)? In any case, how do you set it up to do what the OP wants? Furthermore, if you secure your OS, the .pgpass way is totally acceptable in production, too. — dezso54 mins ago