InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 06:39:47 GMT20http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxClosed circle then, because I'm reading this stuff to try to understand what I might be seeing in the future.Thu, 11 Mar 2010 09:23:44 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxWe had to yield to some ORM usage where I work, luckily usage is controlled as of now and limited to one small application that runs a job once daily. We carefully monitor the 'chattiness' and procedure cache usage. The first attempt they had lots of query plans that were not being reused but they fixed it next time with some setting in NH not sure what but it reuses plans as of now. We keep the ORM model and strongly regulate what they use so right now it seems alright but I dont know about the future. I wish DBAs had enough power to get rid of stuff like this but really as support personnel our powers are limited. I keep watching Grant's blog for more info on NH related issues.Thu, 11 Mar 2010 09:11:19 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxYou guys are great and I really appreciate your insight. OK, so now that I have vented my frustrations let's get down to work. I am trying to figure out the order of operations here. I need to ultimately get control of the data platform so that is becomes a loosely coupled system from the application. One main reason for this is so I can begin on architecture changes on the back end while still serving the application. This brings in the Database-As-API methodology where you need to treat the db platform like any another system that exposes contract interfaces where any allowed service and take advantage of. The only SQL Server construct to handle this is the stored procedure. Now I can begin to query out the dm_exec_query_stats metadata view for queries against a given object and start putting together a query and column list. The other big thing however is that I need to get the query execution from the app down substantially. As I stated before, we generate nearly 200 million queries every day against our platform and most queries take on the ORM form of 'select * from view where id = @id'. What do I need to do to get this execution count down? I know without having a clear understanding of the architecture this may be difficult, but has anyone seen similiar problems and how did they correct it from an application standpoint? Once I have a listing of the queries I then will need to meet with members of development to get there understanding on how the result set is being used. I found this great quote which I have hanging over my desk"Every time a select * enters production code, God kills a kitten".I need to truly understand what is needed from the application standpoint. Once I have a full understanding of what is necessary for the product development group I can begin to take pieces and build exposed stored procedures for the data retrieval and data manipulation. The other issue here is the ORM. This guy needs to go, but not without a better plan. Unfortunately as is the case many times developmenet see's the database and platform in general as just a place to persist data without any respect for the decades of work around relational engines and the incredible things you can do now with SQL 2008. All intelligent thinking has to come from product development and then those decisions are pushed down. I am sure many of you understand ORM's so I won't go into how our operates specifically other than to say it creates the sql based on a xml template as well as generating the DAO's for the higher code. Does anyone have any experience in this and getting away from ORM's? I know that much of this work will be upon myself, but I am fine with that if it helps us become more efficient and because such more profitable. I know that Database Projects in Visual Studio 2008 will be the item filling a large gap here since it can handle database code refactoring and even set policies regarding bad sql code.Anyway there are a lot of things in motion here and I am trying to figure out best how to deal with each one to ultimately create the full system. I would appreciate any help per the items I listed above. ThanksThu, 11 Mar 2010 08:20:03 GMTbirdmic1http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxA toast to birdmac1 for trying to do the right thing in the face of constant adversity and ignorance! I'm glad I'm not in your shoes but like they say, if you want to catch fish, go where there's fish!Keep after them... if you need help, folks on this forum are very happy to help on a well formed question.Thu, 11 Mar 2010 07:07:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx200 million queries... that's a poster child for the horrors of ORM. Yikes. Best of luck with your issues.Thu, 11 Mar 2010 05:31:39 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx200m queries?! flippin eck!I'm assuming once they've finished hammering your database, they want it to produce the same query regularly in most cases?We're looking at a similar problem and will be going down the route of agreeing a policy that the majority of users design the dataset they need (columns, filters etc.) and then we build a stored procedure to produce that for them. They wont have access to the tables.Some users will need to make adhoc queries, we'll make sure that they understand SQL to a basic level and the implications of complex queries before letting the handful of users loose on the tables.Thu, 11 Mar 2010 02:32:13 GMTBen Moorhousehttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxThanks for the reply. Yes we most certainly do have staging and demo environments so I am going to move that direction. The problem is that there are so many political walls being created around how we handle data by those who don't even understand proper architecture or design. It's just about who has been here longer than the next person. Apparently that makes up for a lack of talent and understanding. We have many tables that don't even fit into 1st NF let alone 3rd. The other problem is that the whole data tier is currently handled via ORM tool that writes the sql and codes the data access objects. This is great for development because is allows them to not have to worry about writing sql however it also means the ORM tool can't make intelligent query decisions and it creates extremely chatty interfaces (IE 200 million queries) against our db platform every day! I am part of a very large privately held company and serve within one of their LOB's. Our LOB creates roughly 300 million in annual revenue. Ultimately I want to bring database development and dba work back into visual studio with 2008 database projects. Over time I need to get past the ORM tool and start development within visual studio. Anyway I appreciate your comment and would appreciate any more you may have that would help the process. How do you think we keep our system performing at a decent level? Lots of hardware and lots of indexing! Some of our largest tables have over 20 indexes on them! Not to mention the data distribution problems that occur within the stats because of non-normalized tables. This causes problems for the optimizer to continue and make good plan decisions. Well I am sure you can feel my frustration, but I will try to take hope and continue moving forward. Thanks.Wed, 10 Mar 2010 16:07:22 GMTbirdmic1http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxI think you're absolutely right in what you're doing at the moment. By shifting data access to stored procedures rather than direct table access, not only can you make table changes without affecting the user (translating those change through the stored procedures), but you can also restrict access to the data at certain times or to specific user accounts, and even log how often each procedure is used!.I'm afraid you're probably not going to be able to say no to people a) until you've proven that you know your stuff and have made big improvements, and b) without some serious evidence to back it up. It may be worth noting the effect the changes you're making are having on peoples queries, and then using that when it comes to proving your point.It may also help you work if you have a database on which you can develop and test without affecting users, before running your code on the live SQL server (I dont know what setup you have at the moment).Wed, 10 Mar 2010 01:10:30 GMTBen Moorhousehttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxMy problem is that I was brought in as a DBA to administer and manager a very stressful OLTP system. However over the years I have began to take a more proactive approach. Realizing that my ability to succeed depends on me being able to control my platform I have begun a crusade across the IT department pushing the database as an api methodology. If I can get the application DAO's from talking directly to the schema it allows me to begin a normalization crusade within the system. By getting the application dependant on exposed stored procedures I only need to guarantee the availability of those items while optimization can be done behind the scenes. "And no development, we can't place another 10 indexes on our 100 million row 100 column wide orders table"! However I am one man against an entire development machine pushing new development down my throat all the time. Not to mention the ORM tool being used to code all sql and write the DAO objects at the same time. How do I get control of this and fight back?Tue, 09 Mar 2010 19:22:02 GMTbirdmic1http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxI've recently found that keeping things structured and in writing helps me.It allows me to have a couple of hours a week of stressful discussion about work and times, but then I can relax, get my head down and do good work with my target clear.If issues arise, I deal with them based on priority, and then just document and provide updates on what happened.Your boss shouldn't "have a word" with you if you didn't hit a deadline because something legitimately more important came up.Wed, 03 Feb 2010 01:48:12 GMTBen Moorhousehttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxI've found the key to relieving stress is to not let anything rattle me. Server down? It doesn't get fixed any faster if I'm stressing. I let things roll off my back and just keep my head down working at a steady pace.If that doesn't work, drink like a fish :-DTue, 02 Feb 2010 17:43:43 GMTstevet-1034619http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx[quote][b]hammerheaded (2/2/2010)[/b][hr]Drink Heavily ;-)[/quote]Sounds like a very good plan today!Tue, 02 Feb 2010 05:08:48 GMTBen Moorhousehttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxDrink Heavily ;-)Tue, 02 Feb 2010 05:04:12 GMThammerheadedhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx:-P[quote][b]Ben Moorhouse (1/25/2010)[/b][hr]Ha! So true Jeff!I've been working alongside another person in my team, and I keep having to solve stupid mistakes.Such as records not being deleted even though I'm using the stored procedure correctly... oh yes, no where clause in one of the checks!Having said that though, some other DBAs are awesome and it's nice to be able to fall back on their work.[/quote]Glad to see someone else have a similar problem. "Whiles and cursors and loops... OH MY!"Thu, 28 Jan 2010 20:22:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx[quote][b]Nicholas Cain (1/27/2010)[/b][hr]I like to make poke fun at us SQL DBA's with our Oracle counterparts in the group. Once you admit to them that SQL Server is just a pretend database system then they lighten up quite a lot.[/quote]Heh... do they like pork chops?Thu, 28 Jan 2010 20:18:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx[quote][b]Dave Schutz (1/21/2010)[/b][hr]I've found most of the problems we are discussing are not DBA problems; these problems exist in most business/technical areas. .....[/quote]if this is engineers forum, the title must be "How Do You Handle the Stress of Being an Engineer?".. :)Thu, 28 Jan 2010 19:13:59 GMTMiz Zahanhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxI like to make poke fun at us SQL DBA's with our Oracle counterparts in the group. Once you admit to them that SQL Server is just a pretend database system then they lighten up quite a lot.Wed, 27 Jan 2010 16:12:53 GMTNicholas Cainhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxSounds like an SME?I'd love to work for a company where the databases were my responsibility :D Lucky thing.Wed, 27 Jan 2010 02:53:43 GMTBen Moorhousehttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxNot only am I the DBA, but the main application support person/go to person when things go awry. When I'm not here, I try to have things to keep my mind off of work - my family, pets, hobbies for the most part. I monitor - and monitor well, yes, I do get paged when things are not right. When I find something that is misbehaving, not only do I fix it, but I try to determine how to catch it before it becomes an issue - for the next time. Tools are incredibly important in keeping ahead of the chaos that can make your life stressful. It's the job, I enjoy it (most of the time)...:-)Tue, 26 Jan 2010 11:07:34 GMTDeanne Pedronihttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxHa! So true Jeff!I've been working alongside another person in my team, and I keep having to solve stupid mistakes.Such as records not being deleted even though I'm using the stored procedure correctly... oh yes, no where clause in one of the checks!Having said that though, some other DBAs are awesome and it's nice to be able to fall back on their work.Mon, 25 Jan 2010 01:33:14 GMTBen Moorhousehttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxHeh... lately for me, a better question would be "How do you deal with the stress of other DBA's?" ;-)Sat, 23 Jan 2010 12:04:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx[quote][b]dma-669038 (1/22/2010)[/b][hr]All the DBAs who are men in my team are gaming almost on a daily basis. Personally i think stress esp for DBAs is due to too much thinking/alertness and a little less stimulating activity is a better stress buster. And it has been proven not to do anything stimulating before you go to bed anyway. I wish more people would play real games - get out there exercise both body and mind with a good game of tennis might help better than an online game.[/quote]I can attest to that. I was doing MMORPG's for a bit, but the time constraints (and the lack of an ability to "pause" and walk away for a bit) make that less than ideal these days. So - I'm back to single-player games, but still - it's a huge stress buster.Kind of like Grant's comment earlier: getting to kick someone (virtually or not) cuts down the desire to kick someone while at work....Sat, 23 Jan 2010 10:58:39 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxLive long and propsper ....Sat, 23 Jan 2010 00:09:15 GMTrudy - Doctor "X"http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx[quote][b]dma-669038 (1/22/2010)[/b][hr]All the DBAs who are men in my team are gaming almost on a daily basis. Personally i think stress esp for DBAs is due to too much thinking/alertness and a little less stimulating activity is a better stress buster. And it has been proven not to do anything stimulating before you go to bed anyway. I wish more people would play real games - get out there exercise both body and mind with a good game of tennis might help better than an online game.[/quote]Reminds me of the old "Star Trek" episode where Spock made the observation of "The more complex the mind, the greater the need for the simplicity of play" (or something like that).Fri, 22 Jan 2010 17:47:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx[quote][b]dma-669038 (1/22/2010)[/b][hr]All the DBAs who are men in my team are gaming almost on a daily basis. Personally i think stress esp for DBAs is due to too much thinking/alertness and a little less stimulating activity is a better stress buster. And it has been proven not to do anything stimulating before you go to bed anyway. I wish more people would play real games - get out there exercise both body and mind with a good game of tennis might help better than an online game.[/quote]Fully agree with the outside games. While my Son does spend some time online - he also belongs to a softball league, basketball league, flag football league, works out 4-5 times a week at his gym, and climbs a not so little hill that we have called the Incline. Getting up in the ages - I prefer to do the non-teaming events such as walking, biking, skiing, etc...JoeFri, 22 Jan 2010 13:49:20 GMTcrookjhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxAll the DBAs who are men in my team are gaming almost on a daily basis. Personally i think stress esp for DBAs is due to too much thinking/alertness and a little less stimulating activity is a better stress buster. And it has been proven not to do anything stimulating before you go to bed anyway. I wish more people would play real games - get out there exercise both body and mind with a good game of tennis might help better than an online game.Fri, 22 Jan 2010 08:38:48 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxVery Interesting topic!I try to get at least 2 short walks in on a daily basis. I do longer walks on the weekends. I have a nightly exercise routine that is great. A call to my Father once a week and that is a great stress reducer. Playing with the Grandkids at least 3 times a week is the icing on the cake.One thing that has caught my attention is....My Son is an engineer and he and his friends reduce their stress by online gaming. I have yet to see and mention of that in this thread. I still am working thru the posts and currently am up to 3ish yesterday so if I missed one - sorry. Guess DBAs either don't game or don't have the time for it?Give me a good game of cribbage or pinochle and I am one happy camper!JoeFri, 22 Jan 2010 08:27:16 GMTcrookjhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxI actually wanted to ask if that is what it meant..thanks for clarifying.Fri, 22 Jan 2010 08:24:35 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxaha - someone who both read and digested the statement !Fri, 22 Jan 2010 08:20:46 GMTrudy - Doctor "X"http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx[quote][b]rudy komacsar - Doctor "X" (1/21/2010)[/b][hr] - Acceptance that your 'circle of concern' is far smaller than 'your sphere of influence'[/quote]isn't it: - Acceptance that your 'circle of influence' is far smaller than 'your sphere of condern' ?Fri, 22 Jan 2010 02:03:20 GMTEduardhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspx[quote][b]CirquedeSQLeil (1/21/2010)[/b][hr]A good hardy laugh is also very useful in keeping the stress at bay.[/quote]That's what I normally do although I have been known to accelerate a pork chop or two in the specific direction of an uninformed antagonist. :-PSounds like a "bus-man's holiday" but if I've had a really bad day, I always find some "light" on this forum even if it's "Wow! Look at that dude! And I thought I was having a bad day..." :hehe:Thu, 21 Jan 2010 23:58:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxA good hardy laugh is also very useful in keeping the stress at bay.Thu, 21 Jan 2010 22:04:26 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxit's really stressful that everyone is pointing to DBA just because their applications use SQL server or they find the word "SQL" somewhere in the application error logs, and do not accept our explanation that the problem has nothing to do with SQL Server...Thu, 21 Jan 2010 20:48:42 GMTMiz Zahanhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxGreat article Brad, you hit just about every point one could think of. Many of the responses have touched things that I have done in the past to quote, 'relieve stress'. I think the key is that one realizes that stress is a part of life - the postings that mention other occupations and their stresses seem to support that. Now let me move to your original question - How I handle stress, first the common sense things: - eating meals on a regular schedule - eating snacks on a regular schedule - sleeping/waking on a regular schedule - 2 glasses of red wine every day - being flexible in scheduling life, work and their balance - taking a 5-10 minute break to walk around every hour at work - compartmentalization - no pictures or distractions at work from home - compartmentalization - the only work thing at home is my laptop and docking station Now for the more Zen things: - Acceptance that stress is a part of life - Acceptance that change is a part of life - Acceptance that you have only the 'illusion' of control of your life - Acceptance that your 'circle of concern' is far smaller than 'your sphere of influence'Thu, 21 Jan 2010 13:02:33 GMTrudy - Doctor "X"http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxThanks for all of the thoughtful feedback. I purposely didn't say what I do to relieve stress in the editorial, as I didn't want to influence anyone. While I can't claim to deal with stress particularly well, my philosophy has been to try and avoid it in the first place. This usually means that, as much as possible, I perform my job as best I can, I try to be proactive, and I try to set the expectations of those around me. This helps some, and gives me a little sense of control over what happens to me. Of course, there is still a lot I can't control, and invariably I feel stress. As some have recommended, there is only so much you can do, and at that point, you just have to accept what is given you. Sometimes I deal with this well, and other times not so well. Stress is an on-going battle for me, one that I doubt will ever go away completely.Thu, 21 Jan 2010 12:00:10 GMTbradmcgehee@hotmail.comhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxI see several mentioning martial arts stuff... Do your coworkers have to attend as well to get the full relaxing effect...? :-DChadThu, 21 Jan 2010 11:01:02 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxsurfing. hmph. 30 degrees F and no ocean. I'm sure it helps your stress, but you just upped mine...;-)Thu, 21 Jan 2010 10:06:58 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxSurfing and Brasilian Jiu Jitsu help me out tons. I do this stuff daily and it helps with work stress.AndyThu, 21 Jan 2010 10:00:50 GMTMario Melendezhttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxSome stress is good, some stress is bad. I have found, personally, that there needs to be some level of stress in the environment in order to get things done. It has to be managed, however, and constant overstressing is when problems start occurring.Thu, 21 Jan 2010 08:52:40 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic850971-263-1.aspxI've learnt two things about the scope inflation problem -1 Dont' go all out and learn something just 'coz it is stated to be cool2 Try to optimise on what you can learn with existing technology at work as much as possible.It is true that scope has expanded hugely but from experience there is always hope/opportunities for those of us who do what we do well, and have a general knowledge of things that are outside the scope. It has become increasingly acceptable to say for example that you used clustering but can learn mirroring if needed, or used transactional replication but can learn any other form of replication fi necessary. Also certification gives people confidence that you do have an overview of technologies even if you have not used them.Thu, 21 Jan 2010 08:51:42 GMTdma-669038