Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««4,2174,2184,2194,2204,221»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Tuesday, November 19, 2013 11:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1515705
Posted Tuesday, November 19, 2013 2:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1515796
Posted Wednesday, November 20, 2013 3:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:58 AM
Points: 2,951, Visits: 5,231
And once again I learn more from one of (our) Jeff's "rants" than two days of research and "playing" with partitioning.


Rodders...




Post #1515967
Posted Wednesday, November 20, 2013 1:27 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:13 PM
Points: 961, Visits: 4,980
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!
Post #1516185
Posted Wednesday, November 20, 2013 1:35 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1516186
Posted Wednesday, November 20, 2013 2:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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...





--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516227
Posted Thursday, November 21, 2013 3:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:58 AM
Points: 2,951, Visits: 5,231
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...





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.

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...



Post #1516365
Posted Thursday, November 21, 2013 8:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 2,751, Visits: 7,166
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...





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.

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
Post #1516457
Posted Thursday, November 21, 2013 8:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1516464
Posted Thursday, November 21, 2013 9:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:58 AM
Points: 2,951, Visits: 5,231
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...





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.

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...



Post #1516477
« Prev Topic | Next Topic »

Add to briefcase «««4,2174,2184,2194,2204,221»»»

Permissions Expand / Collapse