|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 18,144,
Visits: 12,165
|
|
Tom Leykis (2/13/2008)
Ok, this will really light your fire  The reason I was asking is becuase it's my database, for my company, that I manage the db for, because I'm anal when it comes to just how insanely far I can go to inprove performance, and I don't want anyone else touching it. This is my own personal fire-breathing dragon, and no one's code but mine gets to swim in the acid pool. The info I've gotten here is very useful and I appreciate it a great deal. Btw: My code is top notch, probably because I learned most everything about how to write proper code on sites like this, from folks such as yourselves. So thanks for that as well. :P
Actually, that cools the fire quite a bit because now I know where you're coming from, Tom. I'm actually a bit jealous in that you get to do the whole thing, womb to tomb. I've not had the privilege nor the responsibility of being able to do that for a long time.
I agree about sites like this, especially this one... I learn something new, everyday... sometimes, it's just to learn when to put the soap box away, sometimes not early enough... :) thanks folks.
--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."
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, October 28, 2009 12:35 PM
Points: 74,
Visits: 387
|
|
Bah, I missed the whole war between the trip home, dinner, kids bath, and now. :) How come nobody called to tell me?! Oh...right...why would I let you guys call me? =)
I have three mantras as a DBA/Developer/Network Engineer.
1. It can always go faster. You just haven't figured it out yet and someone else may already have. So talk to everyone.
2. I can't fix broken code that developers say is "working". But, I can make broke code go faster...much faster. =)
3. Stupidity got us into this mess and stupidity will get us out.
Point 3 is all about being outside the box. Because there's just some code that makes you turn your head sideways and say, "uhhhhhh...what?" and when you're done spending 2 weeks staring at it and refactored a 1,200 line stored procedure into 5 lines that does exactly the same thing. =)
Christopher Ford
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Monday, August 10, 2009 5:34 PM
Points: 45,
Visits: 166
|
|
Hello gentelmen,
I am no where near the expertise level you guys obviously portray , however in my intermediate DBA experience I have also come to realize when you have a large transactional muti-tier subscriber environment the one crucial reason to separate the datafiles in addition to all that have been mentioned is to be proactive in troubleshooting database corruptions. The technique generally used to bcp out and in the data of tables can be very time consuming on large applications. In my experience the database file grew 300GB.
Assuming your DBCC statements come back with courpted data pages.
I was curious as to why nobody had commented on that point, I have known this to be a fundamental reason.
If I am missing something please be nice about it ..hehehe
:D
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, October 28, 2009 12:35 PM
Points: 74,
Visits: 387
|
|
Tom,
I guess manageability has several different meanings. To me, having more files, albeit smaller files...does not make a database more "manageable". That's my opinion anyway.
Being able to quickly answer questions about your databases health is the key to being more "manageable".
Am I using indexes effectively?
Am I using space effectively in the tables? (fragmentation)
Am I using I/O effectively?
What is my worst performing query/procedure?
What is my worst contending table?
Am I distributed properly across the drives available to me?
Am I using cached plans effectively?
Am I backing up regularly?
Does my disaster recovery plan work?
Is it automated?
What is my database growth like?
How many table scans are occuring?
Are those table scans a bad thing? (they may not be depending on how big the table is)
In a database, there is always something to improve, but at some point you've got to be happy with it. Until someone publishes some article in SQL Magazine or on this site that makes you go, "Whoa! I can do that?!" =)
Jeff, I could almost hug you actually...you wrote an article the other day that I have been working a problem for 6 months on trying to solve. Running totas, eg. Triangular Joins. THANK YOU! =) I know, not the post to do it...but since we're on the topic of performance. :)
Ofcourse, since learning from that I now use ROW_NUMBER() to guarantee my row results instead of creating an index. but that conversation is def. not part of this topic. :)
Glad we could help Tom! Well...I hope I helped...sounds like Paul and Jeff got all the good points across. :)
Christopher Ford
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, October 28, 2009 12:35 PM
Points: 74,
Visits: 387
|
|
reshadit (2/13/2008)
Hello gentelmen,
I am no where near the expertise level you guys obviously portray , however in my intermediate DBA experience I have also come to realize when you have a large transactional muti-tier subscriber environment the one crucial reason to separate the datafiles in addition to all that have been mentioned is to be proactive in troubleshooting database corruptions. The technique generally used to bcp out and in the data of tables can be very time consuming on large applications. In my experience the database file grew 300GB.
Assuming your DBCC statements come back with courpted data pages.
I was curious as to why nobody had commented on that point, I have known this to be a fundamental reason.
If I am missing something please be nice about it ..hehehe
:D
Now you should use SSIS to do that kind of operation. It's faster and safer. :)
However, speaking on SQL 2005 (if you haven't upgraded why not?!), when a database goes suspect due to corrupted data pages, the event is stored in msdb, in the suspect_pages table.
Corrupted pages are detected when a query needs to read the data or a DBCC CheckDB or CheckTable is being run, Backup or restore, or when the database is being dropped. They can also be detected when running DBCC DBREPAIR.
You can find the event_type value definitions in BOL if you search for "suspect_pages":
http://technet.microsoft.com/en-us/library/ms174425.aspx
Then you would just restore that page from your last backup.
Christopher Ford
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Saturday, May 10, 2008 9:35 AM
Points: 21,
Visits: 99
|
|
Then you would just restore that page from your last backup.
Wait, you're saying this thing has a backup feature? 
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, October 28, 2009 12:35 PM
Points: 74,
Visits: 387
|
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, October 28, 2009 12:35 PM
Points: 74,
Visits: 387
|
|
Erhm...Forgive me for posting incomplete information. =)
Previously I mentioned restore the corrupted data page...that assumes you backup your log. :D Sorry.
Okay, I should leave this post alone. BYE!
Christopher Ford
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 18,144,
Visits: 12,165
|
|
Christopher Ford (2/13/2008)
Jeff, I could almost hug you actually...you wrote an article the other day that I have been working a problem for 6 months on trying to solve. Running totas, eg. Triangular Joins. THANK YOU! =) I know, not the post to do it...but since we're on the topic of performance. :)
Christopher, thanks for the compliment! Tickles me to death when I can help someone out like that. I really appreciate the feedback! No hugs, though... send beer... I'm on an all liquid diet :P
I'd really be interested in how those two articles helped... maybe a post on the discussion area for one of the two? Thanks, Christopher.
--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."
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
