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,2184,2194,2204,2214,222»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Thursday, November 21, 2013 9:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 2,951, Visits: 5,237
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



Post #1516481
Posted Thursday, November 21, 2013 9:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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



Post #1516483
Posted Thursday, November 21, 2013 9:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 2,751, Visits: 7,179
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...





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


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
Post #1516485
Posted Thursday, November 21, 2013 9:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 466, Visits: 764
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
Post #1516488
Posted Thursday, November 21, 2013 9:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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





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


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



Post #1516492
Posted Thursday, November 21, 2013 10:18 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 3,970, Visits: 2,978
rodjkidd (11/21/2013)
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

Business is always looking for the "make it faster" answer to everything and it shouldn't take any time or testing to implement. In fact, it should have already been done.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1516499
Posted Thursday, November 21, 2013 10:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 4,387, Visits: 3,398
Ed Wagner (11/21/2013)
rodjkidd (11/21/2013)
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

Business is always looking for the "make it faster" answer to everything and it shouldn't take any time or testing to implement. In fact, it should have already been done.

I call it the MS Project school of management: "All QA will be done on Thursday afternoon and we will launch on Friday."
Post #1516503
Posted Thursday, November 21, 2013 10:32 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 3,970, Visits: 2,978
Revenant (11/21/2013)
Ed Wagner (11/21/2013)
rodjkidd (11/21/2013)
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

Business is always looking for the "make it faster" answer to everything and it shouldn't take any time or testing to implement. In fact, it should have already been done.

I call it the MS Project school of management: "All QA will be done on Thursday afternoon and we will launch on Friday."

That's about accurate. And we should do all our production releases on Friday afternoon after people have left early. Oh, wait...didn't we get rid of the entire QA team in the interests of saving money? That's okay, it went through the QA phase, so that's good enough, isn't it?



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1516507
Posted Thursday, November 21, 2013 11:57 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: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
rodjkidd (11/21/2013)
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


tbh, the answer should be 'This probably won't make things noticeably faster unless we take a great deal of time to change the queries to benefit from the partitioning. Is such a large query refactoring in scope?

about the photos. I have about 2 photos of me from PASS.



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 #1516539
Posted Thursday, November 21, 2013 1:16 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: Today @ 8:14 AM
Points: 961, Visits: 4,983
Gotta love situations like this...

Please install this program to make it easier for us to make your server (OS) more secure that requires this OS feature you've never needed before (.NET4)

Followed by:
OH GAWD LOOK AT ALL THESE VULNERABILITIES IN .NET4 THAT YOU'VE NOT PATCHED YET!!!


Thank any deity I can think of that they're not pushing to get this installed NOW.
I've already put it on several of my QA boxes just to make sure it wouldn't hose SQL. Thinking (planning) to wait on production until the next round of MS patches come out...
Post #1516570
« Prev Topic | Next Topic »

Add to briefcase «««4,2184,2194,2204,2214,222»»»

Permissions Expand / Collapse