November 19, 2013 at 11:51 am
Ed Wagner (11/19/2013)
jcrawf02 (11/19/2013)
L' Eomot Inversé (11/14/2013)
Lynn Pettis (11/13/2013)
Lynn Pettis (11/13/2013)
It is 23:27 Afghan time. Three minutes until my phone interview. Wish me luck!Just finished the interview, and I feel like a Junior DBA. I have been working in to sheltered of an area far too long. I could not answer questions about SSRS, SSAS, Clustering, MS Replication. Very little was asked in areas that I feel strong in, T-SQL and tuning code.
I really need to learn more about best practices as well. Sad to be asked about them and not being able to rattle them off the top of my head. Hopefully I hit them even if inadvertently.
I feel very small at the moment.
Don't be silly Lynn, you've no excuse for feeling small. I agree with Grant and Jeff and the three others who have commented so far: your big, not small, in the SQL community.
For myself, I don't claim to understand the first thing about SSAS or SSRS or SSIS or DQS or SSDT. I know some relational theory, T-SQL, and snapshot and transactional replication. I haven't the first clue about clustering, and merge replication is a complete mystery to me. But I don't feel small - why should I? And if I don't, why should you?
As for best practices, I get the impression that you are quite hot on that area, at least so far as best SQL practices are concerend; I don't think I'd ask you about best practice for configuring RAID arrays or choice of network gear or configuring complex hardware geometries, but none of that is about best SQL Server practice. No-one expects you to be totally on top of best practice in every area of IT operations and development, with every type of technology - no-one expects it because (apart perhaps for the odd genius like Wilkes, and of course in his day there was far less of it him to know about) it isn't humanly possible.
"some"....Tom knows "some" of that. jeebus.
Yeah...Tom knows "some" of it, but no more than "some". Too bad we don't have a sarcasm icon. 😉
I can take "some" as equivalent to "any". 🙂
http://technet.microsoft.com/en-us/library/ms175064.aspx
-- Gianluca Sartori
November 19, 2013 at 2:43 pm
Stefan Krzywicki (11/18/2013)
Has anyone heard anything about partitioning in 2014 making drastic performance improvements? I was at a presentation on SQL Server 2014 recently and the presenter, a Microsoft guy, told the audience that a way to get huge performance improvements on large tables was to use partitioning. He went so far as to say "you should all be partitioning your tables."From what I know, that's just not true. Partitioning is a tricky, time consuming thing to set up properly and has other effects on your tables and indexes. It can be very useful in archiving, but doesn't improve performance by itself. I didn't know how to bring this up during the presentation though and was hesitant in contradicting someone who works for Microsoft.
To add to what everyone else has said on the subject and to agree, in many cases....
The only "Major" improvement that I've heard of is that you'll be able to rebuild the clustered index in an online fashion as of 2014. Other than that, partitioning isn't what most people would expect when you get into it. For example, the partitioning column MUST be a part of every unique index including the PK. That makes BIG sucking sounds if you have DRI on the table and because of the fact that means that the unique column can now have duplicates!!! More BIG FAT SUCKING SOUNDS!
Because of all that and the fact that properly written code against a properly indexed table is actually faster than working against a partitioned table in many cases, I think that anyone who partitions a table for reasons of performance is totally out of their mind. The reason why it can be slower is if the query you're using isn't keyed on the partitioning column, then the query has to go through not 1 but multiple B-Trees to get the data (CI or NCI).
The only reason why I'm currently in the throws of partition is for two reasons...
1. Be able to do "Piece-Meal" restores where you can "get back in business" quickly and restore the less critical things (like huge write-once audit tables, for example) after the system is back up and running and...
2. To greatly reduce backup requirements. I currently have a 250GB database (telephone system) where only 10GB ever changes per day yet we're backing up all 250GB every day and it takes 5 bloody hours to do that (I have a love/hate relationship with UNC network shares for such things)!!! I'm working on partitioning it all by month with one month per filegroup and one file per filegroup. That will support piece-meal restores AND it allows me to set the file group for each month (as the roll-over each month) to read-only to protect the data (it IS an audit table of sorts) and to make piece-meal restores even easier than they already are. This will also allow me to only have to backup the current month data which will drop my backup times from 5 hours to about 10 minutes and will also greatly reduce the cost of tape storage/swapping, as well.
For anything that isn't like an audit table, you should strongly consider making the PK the partitioning column otherwise your PK won't actually be unique anymore because you have to add the partitioning column to any and all Unique indexes if you want them to be "aligned".
I've found that partitioned views are actually easier to use BUT they have problems with IDENTITY columns. Sure there's a workaround for that (especially since SEQUENCES came out... YIPPEE!!! BOOOO!!!!) but MS didn't make any form of partitioning easy from a design standpoint. If you do it willy-nilly instead of really studying the before/after problems, then you will hate yourself plenty in the morning... for a shedload of mornings. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2013 at 3:16 am
And once again I learn more from one of (our) Jeff's "rants" than two days of research and "playing" with partitioning. 😀
Rodders...
November 20, 2013 at 1:27 pm
So reading yet another "how do I shrink / truncate my log files without running log backups" topic, I had (what seemed to be) an incredible idea!
Why not ask MS to put in the ability to restore a DB to a point-in-time prior to the full backup! Say you take a full backup at 6pm, and need to recover to 5pm, you just use that full backup from 6pm!
Then I realized how dumb that was...
The full backup would have no knowledge of any of the transactions that would need to be rolled back to get to 5pm, it's a "snapshot" of the data and transactions at the moment it completes and nothing more...
I can too be smart, really!
:hehe:
November 20, 2013 at 1:35 pm
That would only be possible if the transaction log backups did not truncate the log and only the full backup truncated the log after it ran. Not only would that complicate log management, it would make the full backup take much longer and be much larger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2013 at 2:49 pm
rodjkidd (11/20/2013)
And once again I learn more from one of (our) Jeff's "rants" than two days of research and "playing" with partitioning. 😀Rodders...
:-):blush:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2013 at 3:43 am
Jeff Moden (11/20/2013)
rodjkidd (11/20/2013)
And once again I learn more from one of (our) Jeff's "rants" than two days of research and "playing" with partitioning. 😀Rodders...
:-):blush:
Jeff, I was hoping you'd take that in the best possible taste! I did worry that the humour wouldn't quite translate in a forum post 😀
Looks like you did, so that's alright. :w00t:
But seriously it was the "aligning of indexes" that was troubling me, and you managed to put it far more succinctly than I had managed. We still need to test, to see if it's going to come out with more positives than not. Just have a small problem in not having a test server to use... Oh well!
Rodders...
November 21, 2013 at 8:37 am
rodjkidd (11/21/2013)
Jeff Moden (11/20/2013)
rodjkidd (11/20/2013)
And once again I learn more from one of (our) Jeff's "rants" than two days of research and "playing" with partitioning. 😀Rodders...
:-):blush:
Jeff, I was hoping you'd take that in the best possible taste! I did worry that the humour wouldn't quite translate in a forum post 😀
Looks like you did, so that's alright. :w00t:
But seriously it was the "aligning of indexes" that was troubling me, and you managed to put it far more succinctly than I had managed. We still need to test, to see if it's going to come out with more positives than not. Just have a small problem in not having a test server to use... Oh well!
Rodders...
That was my problem too, no test server.
I've been thinking about this a little. I think you might be able to get some performance benefit if you choose your partition column very carefully. If you find what your most used or slowest or most important query parameter is and partition by that, you could see a performance benefit from it on those queries. I don't know if it'd be worth it though since you'd still have a multi-column PK, you'd have to make sure those queries only searched by that parameter and you wouldn't really be able to use it for archiving unless it was also the date column or you have a very specific use for the data. I think you'd probably be better served by creating tables that hold the subset range you're searching on most.
Maybe vertical partitioning would work better, but I'd have to read up on that more and there's not a lot published on partitioning of any kind.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 21, 2013 at 8:46 am
Stefan Krzywicki (11/21/2013)
I think you might be able to get some performance benefit if you choose your partition column very carefully.
Maybe. Depends on the query forms
If you find what your most used or slowest or most important query parameter is and partition by that, you could see a performance benefit from it on those queries.
Probably not
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2013 at 9:20 am
Stefan Krzywicki (11/21/2013)
rodjkidd (11/21/2013)
Jeff Moden (11/20/2013)
rodjkidd (11/20/2013)
And once again I learn more from one of (our) Jeff's "rants" than two days of research and "playing" with partitioning. 😀Rodders...
:-):blush:
Jeff, I was hoping you'd take that in the best possible taste! I did worry that the humour wouldn't quite translate in a forum post 😀
Looks like you did, so that's alright. :w00t:
But seriously it was the "aligning of indexes" that was troubling me, and you managed to put it far more succinctly than I had managed. We still need to test, to see if it's going to come out with more positives than not. Just have a small problem in not having a test server to use... Oh well!
Rodders...
That was my problem too, no test server.
I've been thinking about this a little. I think you might be able to get some performance benefit if you choose your partition column very carefully. If you find what your most used or slowest or most important query parameter is and partition by that, you could see a performance benefit from it on those queries. I don't know if it'd be worth it though since you'd still have a multi-column PK, you'd have to make sure those queries only searched by that parameter and you wouldn't really be able to use it for archiving unless it was also the date column or you have a very specific use for the data. I think you'd probably be better served by creating tables that hold the subset range you're searching on most.
Maybe vertical partitioning would work better, but I'd have to read up on that more and there's not a lot published on partitioning of any kind.
Cheers.
Well at least the request has been put through to have another server spun up. I have the scripts ready to go, so hopefully the testing starts soon. Usual thing of waiting for resources to be released!
Rodders...
November 21, 2013 at 9:25 am
GilaMonster (11/21/2013)
Stefan Krzywicki (11/21/2013)
I think you might be able to get some performance benefit if you choose your partition column very carefully.Maybe. Depends on the query forms
If you find what your most used or slowest or most important query parameter is and partition by that, you could see a performance benefit from it on those queries.
Probably not
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/
Excellent article as usual there Gail!
Problem here is the business is looking for a "this will make it faster" answer, and I'm saying it could help, we need to run some tests... Oh is that a "it depends", I think it is 🙂
November 21, 2013 at 9:29 am
Gail,
That reminds me... Sorry been rather busy the last few weekends...
I've did look at the photo's I took at PASS. I had a new camera (Point and shoot super zoom), tried that on day one. They look OK, couple didn't focus properly.
Day two I used my older, supposedly "better" camera. It really struggled with the light. I don't like taking photos with flash for couple of reasons. Doesn't look as though any came out well though, sadly. Pity as it was when you were presenting as a Jedi! I'll take a look again over the weekend and pick the "best" of the bunch and send them over to you.
Rodders...
November 21, 2013 at 9:33 am
rodjkidd (11/21/2013)
Stefan Krzywicki (11/21/2013)
rodjkidd (11/21/2013)
Jeff Moden (11/20/2013)
rodjkidd (11/20/2013)
And once again I learn more from one of (our) Jeff's "rants" than two days of research and "playing" with partitioning. 😀Rodders...
:-):blush:
Jeff, I was hoping you'd take that in the best possible taste! I did worry that the humour wouldn't quite translate in a forum post 😀
Looks like you did, so that's alright. :w00t:
But seriously it was the "aligning of indexes" that was troubling me, and you managed to put it far more succinctly than I had managed. We still need to test, to see if it's going to come out with more positives than not. Just have a small problem in not having a test server to use... Oh well!
Rodders...
That was my problem too, no test server.
I've been thinking about this a little. I think you might be able to get some performance benefit if you choose your partition column very carefully. If you find what your most used or slowest or most important query parameter is and partition by that, you could see a performance benefit from it on those queries. I don't know if it'd be worth it though since you'd still have a multi-column PK, you'd have to make sure those queries only searched by that parameter and you wouldn't really be able to use it for archiving unless it was also the date column or you have a very specific use for the data. I think you'd probably be better served by creating tables that hold the subset range you're searching on most.
Maybe vertical partitioning would work better, but I'd have to read up on that more and there's not a lot published on partitioning of any kind.
Cheers.
Well at least the request has been put through to have another server spun up. I have the scripts ready to go, so hopefully the testing starts soon. Usual thing of waiting for resources to be released!
Rodders...
Some things that tripped me up, maybe you already know this, but I figure it can't hurt to share.
1) Always leave a completely empty partition on the end where you're going to make new partitions. If you don't do this, creating a new partition takes forever.
2) You know how you always hear "row order in tables doesn't matter"? It does here. If your table and archival table don't match exactly, you can't swap partitions.
3) If you have a file for your archival data and a file for your new data and you swap a partition, it doesn't swap files, just tables. Eventually you have to create a new file for data.
Sorry if this is all obvious, just some things I ran into.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 21, 2013 at 9:41 am
Michael Earl-395764 (11/18/2008)
I can understand basic, foundational questions, but what has been driving me batty lately are the questions that you can copy and paste into google and click "I'm Feeling Lucky" and get the correct answer.
One of my co-workers has a sign on his door that reads something like "Have you Googled it? If not, go away!"
Dave
November 21, 2013 at 9:44 am
Stefan Krzywicki (11/21/2013)
rodjkidd (11/21/2013)
Stefan Krzywicki (11/21/2013)
rodjkidd (11/21/2013)
Jeff Moden (11/20/2013)
rodjkidd (11/20/2013)
And once again I learn more from one of (our) Jeff's "rants" than two days of research and "playing" with partitioning. 😀Rodders...
:-):blush:
Jeff, I was hoping you'd take that in the best possible taste! I did worry that the humour wouldn't quite translate in a forum post 😀
Looks like you did, so that's alright. :w00t:
But seriously it was the "aligning of indexes" that was troubling me, and you managed to put it far more succinctly than I had managed. We still need to test, to see if it's going to come out with more positives than not. Just have a small problem in not having a test server to use... Oh well!
Rodders...
That was my problem too, no test server.
I've been thinking about this a little. I think you might be able to get some performance benefit if you choose your partition column very carefully. If you find what your most used or slowest or most important query parameter is and partition by that, you could see a performance benefit from it on those queries. I don't know if it'd be worth it though since you'd still have a multi-column PK, you'd have to make sure those queries only searched by that parameter and you wouldn't really be able to use it for archiving unless it was also the date column or you have a very specific use for the data. I think you'd probably be better served by creating tables that hold the subset range you're searching on most.
Maybe vertical partitioning would work better, but I'd have to read up on that more and there's not a lot published on partitioning of any kind.
Cheers.
Well at least the request has been put through to have another server spun up. I have the scripts ready to go, so hopefully the testing starts soon. Usual thing of waiting for resources to be released!
Rodders...
Some things that tripped me up, maybe you already know this, but I figure it can't hurt to share.
1) Always leave a completely empty partition on the end where you're going to make new partitions. If you don't do this, creating a new partition takes forever.
2) You know how you always hear "row order in tables doesn't matter"? It does here. If your table and archival table don't match exactly, you can't swap partitions.
3) If you have a file for your archival data and a file for your new data and you swap a partition, it doesn't swap files, just tables. Eventually you have to create a new file for data.
Sorry if this is all obvious, just some things I ran into.
Always good to mention anyway.
Point 3 I hadn't realised.
Rodders...
Viewing 15 posts - 42,181 through 42,195 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply