Stack Exchange
log in

Jon Seigel

 Discussion on question by Ravi: Why i…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 15:29
@Martin: Thanks for that.
Jon Seigel
Aug 29, 2017 15:29
@Mike: Not the same, at least as far as RDBMS. This question (I believe) is in the context of Oracle; that question is SQL Server specific. That said, any discussion of DDL/DML/whatever should probably be database-agnostic, which that question isn't.
 

 Discussion on question by Alan T: Hig…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 11:03
Looking at what you posted again, the plan indicates that the big read and aggregation only estimated as 2.5% of the overall cost. Without seeing the entire plan, it may be the case that those hints sped up other parts of the query significantly. I can't really say anything else without seeing the whole plan and what indexes are available on all the tables.
Jon Seigel
Aug 29, 2017 11:03
Remove the hint and try using OUTER APPLY instead of the first LEFT JOIN. The JOIN will typically materialize the inner query and then join, while APPLY usually produces a nested loops type of plan that will use an index seek if an index is available (add an index if none exists). It's unclear if this is actually going to help because the entire set is being selected, but it's worth a shot, IMO.
 

 Discussion on question by Joshua Enfi…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 10:58
Depending on the workload, it may be beneficial to change the clustered index to be on a key that leads with the foreign key column.
 

 Discussion on question by bumble_bee_…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 10:57
As mentioned in the article I linked to, I think this issue probably has to do with the balloon driver and memory reservation settings.
Jon Seigel
Aug 29, 2017 10:57
Read this -- very timely.
 

 Discussion on question by CRCerr0r: S…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 10:55
How is this data being collected and processed? Are you sure what you're seeing is actually the data behind the report?
 

 Discussion on question by Hani El Mou…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 10:30
Can you show us the code you're using to update the table to test the trigger? The trigger code looks okay to me (or at least nothing obvious sticks out). Maybe no rows actually got changed -- have a look at the query plan to see the row flow.
 

 Discussion on question by Federico Gi…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 10:27
Is the view uvw_Members_Display indexed?
 

 Discussion on question by Mrchief: SQ…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 10:24
So... if I'm understanding this conversation right, the full range of a 64-bit value is enough, but half of that isn't? That's pretty gutsy for estimation accuracy, IMO. In that position, I would probably abandon any 64-bit type on the grounds that it simply isn't going to be safe enough to scale. Is the ulong code already set in stone, or is it possible to use something else?
 

 Discussion on question by webdad3: In…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 10:21
Mainly, I would want to look at the application code to see what kind of algorithm it's using to do the load. Seeing the table schema couldn't hurt either.
Jon Seigel
Aug 29, 2017 10:21
You're welcome. I honestly think it could be improved further, but as Aaron and I said, you'll have to give us more insight into how the process works. If you want to follow up later, feel free to comment-ping me using @Jon.
Jon Seigel
Aug 29, 2017 10:21
Could you please post the code of how the INSERTs are being done? This can make a huge difference.
Jon Seigel
Aug 29, 2017 10:21
Okay, no problem. Could you please answer my original question about when the spatial index is created? Also, it would help if you post a sample of your code so we can see if there are any other process improvements that could be made.
Jon Seigel
Aug 29, 2017 10:21
See this for a definition. Basically, for a load, does it have to be done in less than a given amount of time? (It doesn't sound like it from your response.)
Jon Seigel
Aug 29, 2017 10:21
Did you create the index before or after the load? If before, try doing it after instead. Also, what is your SLA? Is ~30 minutes okay, or do you need it faster than that?
 

 Discussion on question by Brian Hoope…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 10:02
I don't know exactly what the use case is; maybe consider buffering the data on the client and flush it out every 2-500 rows, or every 5 seconds, or whichever comes first. Or something similar to that.
Jon Seigel
Aug 29, 2017 10:02
Have you tried using System.Data.SqlClient.SqlBulkCopy? This will be much, much faster than singleton INSERT statements.
 

 Discussion on question by Ezi: How to…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 09:32
@Ezi: Did the slowdown happen gradually over time, or did it change suddenly?
Jon Seigel
Aug 29, 2017 09:32
@Aaron: "... accepting all of the Database Engine Tuning Advisor's terrible advice." -- terrible as it can be, it may have helped in this case. I don't want the OP to think they can be dropped without any other considerations. That was my point.
Jon Seigel
Aug 29, 2017 09:32
To be fair, we have no idea whether or not the indexes are being used at this point without seeing some dm_db_index_usage_stats output. Dropping them without checking could be a huge mistake and make things worse.
 

 Discussion on question by redsquare: …

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 09:24
Even though I did say this already, I'll state it explicitly: I'm not suggesting using compression within SQL Server. I'm suggesting using compression before it ever hits the database.
Jon Seigel
Aug 29, 2017 09:24
Are you deleting batches of rows every day, or do rows expire exactly after 3 days? Implementing either table partitioning or partitioned views will solve this if you're deleting every day. Either way, a properly designed clustered index on the table will go a long way. I'd like to look at the table schema. Using pre-insert compression will solve the space problem (at the expense of increased CPU).
Jon Seigel
Aug 29, 2017 09:24
Table partitioning is SQL Server Enterprise edition; you can use partitioned views in all editions. What is the current performance problem? Have you attempted to compress the XML documents before putting them in the database? Compression on a text stream is usually very good. You may see 90%+ of the space being used now go away.
 

 Discussion on question by SteLoe: Wei…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 09:23
This doesn't repro on 11.0.3000 or 9.0.4060. I don't have a 2008 non-R2 instance handy. All instances I tested are 32-bit, in case that makes a difference.
Jon Seigel
Aug 29, 2017 09:23
Wow. I can repro this. My test instance is 10.50.2811. I don't know if this is a known bug, but definitely contact Microsoft either using a support ticket or on Connect.
 

 Discussion on question by somecallmem…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 09:16
How are you arriving at the 480 bytes/row number? Are you going to store these as strings (insanity!)? My rough count using either 4 or 8 byte integers to store these values is 116 bytes/row; this could probably be narrowed down even more.
 

 Discussion on question by Ali Razeghi…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 09:16
You're welcome. Let me know if you have any other questions.
Jon Seigel
Aug 29, 2017 09:16
Okay, I rolled back my answer to the revision that contains the TypeID. I added it before I realized it didn't meet your "requirement."
Jon Seigel
Aug 29, 2017 09:16
You could, but why? I can add the TypeID to the one view, then your unique index would be on EpochDate, CustomerID, TypeID, with one row per day per customer per type. It wouldn't be one row per day per customer, but if you add a predicate on TypeID, the results will be.
Jon Seigel
Aug 29, 2017 09:16
Adding TypeID to your example output makes it impossible to guarantee 1 row per customer per day, for the reasons I mention in my answer.
Jon Seigel
Aug 29, 2017 09:16
Which version of SQL Server are you using, please?
Jon Seigel
Aug 29, 2017 09:16
If those column are put in the GROUP BY, then you end up with a partial aggregate, and potentially > 1 row per customer per day.
Jon Seigel
Aug 29, 2017 09:16
I think that should work. My point was that the other columns can't be included in the SELECT list in order to end up with 1 row per customer per day.
Jon Seigel
Aug 29, 2017 09:16
If you GROUP BY the other columns and the values of them change from hour-to-hour, the view will only represent a partial aggregation of the data. You'll still have to aggregate the Value column on a per-day basis if that's the actual total you want.
Jon Seigel
Aug 29, 2017 09:16
I'm confused. You're going to GROUP BY Epoch, and also by customer, too, right? But that means you need to aggregate the other columns in the SELECT list somehow -- TypeID and ErrorID, so there is already a problem in the proposed view definition. Could you please clarify?
 

 Discussion on answer by Thomas Kejser…

Imported from a comment discussion on dba.stackexchange.com/qu...
Jon Seigel
Aug 29, 2017 07:34
@Edward: That figure was a reference to content in the first link -- hardware for the test was 48 cores and 4 Fusion-IO cards.
Jon Seigel
Aug 29, 2017 07:34
I meant "not reading back the data" in the sense that I/O performance for that isn't an issue because of the extra space taken up by the fragmented table data. In any event, it depends what the OP is doing with the data... and it was never stated that this was for an OLTP system. Again, I'm not disagreeing with you; I just think that like Aaron said, this advice only applies to a relatively narrow audience, and probably doesn't apply here. Perhaps you can clarify the scenario with the OP.
Jon Seigel
Aug 29, 2017 07:34
Scaling to 1M+ rows/sec is great, but this assumes many other things, like conserving disk and buffer pool space is irrelevant, and reading the data back is never done. Random inserts will fragment the data in the table. This advice applies to the clients you deal with, but probably not to most people's systems.
 

 Cross-database snapshot isolation

Jon Seigel
Jan 28, 2014 18:17
lol, if the feature is in the wild, people will be using it, regardless of whether or not it's a good idea.
Jon Seigel
Jan 28, 2014 18:00
Ultimately I think that's a strategy that will backfire bigtime in the long-term, but that's a totally separate discussion. :)
Jon Seigel
Jan 28, 2014 17:52
No worries. It would be nice if this kind of thing was officially documented more clearly.
Jon Seigel
Jan 28, 2014 17:47
Thanks, @Aaron.
Jon Seigel
Jan 24, 2014 19:36
Anyway, thanks for the chat, much appreciated.
Jon Seigel
Jan 24, 2014 19:36
Haha, I don't go in there much, but I believe you.
Jon Seigel
Jan 24, 2014 19:32
Hahaha that works, too. Thanks.
Jon Seigel
Jan 24, 2014 19:31
I'm not worried about others reading it either; just that it's stuff Mark doesn't really need to go through when he comes back later. I'll clean up the comments on my answer.
Jon Seigel
Jan 24, 2014 19:26
In any event, I should get back to work (oops). I'll be interested to hear what Mark has to say when he looks at this again. (Do we want to clean up this chat?)
Jon Seigel
Jan 24, 2014 19:21
It would be interesting to be a fly on the wall in a mod-only discussion about how to handle him.