Kevin Kline
SQL Down Under Show 22 - Guest: Kevin Kline - Published: 25 Apr 2007
In this show SQL Server MVP and PASS President Kevin Kline lists what he considers the ten biggest mistakes made when using SQL Server.
Details About Our Guest
Kevin Kline is a technical strategy manager for SQL Server Solutions at Quest Software, leading provider of award winning tools for database management and application monitoring on the SQL Server platform. Kevin is also the president of the international professional association for SQL Server over the past, he’s been a Microsoft SQL Server MVP since 2004, is the lead author of SQL in a Nut Shell, and co-author of PRO SQL Server 2005 Database Design and Optimization and Database Benchmarking. Kevin writes monthly columns for Database Trends and Applications [Internet Site] and SQL Server Magazine [Internet Site] and keeps blogs at sqlblog.com and sqlmag.com. Kevin’s a top rated speaker appearing in international conferences like TechEd PASS, Microsoft IT Forum, DevTeach and SQL Connections. When he’s not pulling out his hair over work, he loves to spend time with his four kids and in his flower and vegetable gardens.
Show Notes And Links
Show Transcript
Greg Low: Introducing show number 22 with guest, Kevin Kline.
Our guest today is Kevin Kline. Kevin is a technical strategy manager for SQL Server Solutions at Quest Software, leading provider of award winning tools for database management and application monitoring on the SQL Server platform. Kevin is also the president of the international professional association for SQL Server over the past, he’s been a Microsoft SQL Server MVP since 2004, is the lead author of SQL in a Nut Shell, and co-author of PRO SQL Server 2005 Database Design and Optimization and Database Benchmarking. Kevin writes monthly columns for Database Trends and Applications [Internet Site] and SQL Server Magazine [Internet Site] and keeps blogs at sqlblog.com and sqlmag.com. Kevin’s a top rated speaker appearing in international conferences like TechEd PASS, Microsoft IT Forum, DevTeach and SQL Connections. When he’s not pulling out his hair over work, he loves to spend time with his four kids and in his flower and vegetable gardens. Welcome Kevin.
Kevin Kline: Thank you Greg, pleasure to be with you.
Greg Low: I haven’t caught up with you, think last time was at TechEd in Boston and at time, you had no voice.
Kevin Kline: Yes, it’s kind of funny tradition I have but for some reason, with four kids I pick up every illness that comes from the school, but it seems to happen just before I go to a major conference.
Greg Low: Were you speaking at TechEd last year?
Kevin Kline: Last year, I was not, no. I also went to DevTeach in Montreal where I had a bad cold there and then at big PASS conference in Seattle last year, of course being President had to speak a bit and give technical lessons and almost completely lost my voice. Quite a trial.
Greg Low: Like I do with most guests, tell us how did you come to get involved with SQL Server at all?
Kevin Kline: I’ve been in the IT business since 1986 and I started out after graduating from University of Alabama. In Northern Alabama, have one of nation’s largest NASA Space Flight Facilities, and my early programming experience was in dBase and FORTRAN and I happened to come into a job working with Oracle there. Established a lot of skill with Oracle and relational database and at that point when I left NASA went to work with the US Army Missile Command, also worked with Oracle database for a while. As you can tell, both jobs I had had working with Oracle were in government. Most particularly in sort of defense and high technical industries. At end of Cold War, Congress cut funding for that so our family wanted to move to city with more diverse economic base than that related to US Government. Led me from Northern Alabama to Nashville, Tennessee where I took a job with very large IT and consulting and auditing company known as Deloitte & Touché. Deloitte was embarking on first client server application project with Oracle. I came to work for Deloitte at that time working on Oracle, and not long after, decided to implement Microsoft SQL Server in each of offices at their departmental server. Back in 4.2.1.days, not even a Windows.
Greg Low: Stage I started to get involved with as well. Certainly challenges at that point.
Kevin Kline: Indeed there were, but on other hand, much more affordable than Oracle, and evidently easier to implement and maintain in different offices around country and we chose it as our office level departmental server. I moved from Oracle side of the house to SQL Server support and as our relational database investment grew became team lead for SQL Server DBA work eventually worked into position manager of information architecture for Deloitte & Touché, working on Enterprise SQL Server applications and continued in that role ‘till I moved to Quest in 2002 to bring all ideas as Enterprise DBA to life in our tools.
Greg Low: With Deloitte, given fact they had such a significant Oracle background, was it mostly cost reason they moved across?
Kevin Kline: One of the very large reasons. Cost certainly was factor. On other hand, at least with Deloitte, another issue was that they derive a lot of their business and revenue from market, customer’s large fortune 5,000 companies, to medium sized companies. Many of clients and customers were using this Microsoft stuff and they felt it was important to them to tell clients with straight face “Yes we use product that you are using and you’re paying us to do consulting for.” So it’s just part good business as well, in terms of the marketing and that aspect of consulting side.
Greg Low: You’ve enjoyed time at Quest? You’re still there so…
Kevin Kline: Yeah I have. Quest is great company to work for. Fortunate that Quest has allowed me to change jobs within SQL Server point of view, in working in SQL Server space. Initially meant to architect products. Had two products when I started. One Spotlight and another SQL Navigator, which we no longer have. Brought in to build next generation to build SQL Server tools called Quest Central, not to build, but design them so I was in architectural for about year, and next year and a half to two, was head of R&D effort to build all of tools and get them out to the public. About year and a half ago, moved to more technology evangelism role where I worked to promote tools, worked closely with sales group and customer visits, spent time at user groups, conferences, and not just promoting for Quest tools but SQL Server in general.
Greg Low: Material we’re going to cover is top ten mistakes you think people make. Perhaps if we start at number ten and work our way to the top?
Kevin Kline: Variety of mistakes I see and I spend a lot of time talking with people, mostly at very high end of Enterprise scale, with HP superdomes and 32 CPUs, ES700s with 64 GB of RAM all way down to “Mom and Pop” shop with commodity one and two CPU boxes and couple bytes of RAM. It’s amazing how often I see mistakes perpetuate not just in places you might expect or new shop, just utilizing SQL Server or not having experience with computers, but even in place where they’d have some sort of degree in IT, still see mistakes. Number ten mistake, with lowest intensity is design mistake, not knowing scalability requirements for application or design. Frequently manifest in no capacity planning for future or poorly done capacity which manifests not only having too little resources for server in long run, sometimes too much resource for application or task at hand.
Greg Low: I’ve seen many times where people have issues and they tend to just throw hardware at it. Often that’s nothing near what problem is and certainly doesn’t help, but reduces budget…
Kevin Kline: Exactly. In fact, doing session at TechEd about performance base lining and monitoring. Whole idea “Don’t just throw hardware at it.” If have bad design, and just throw hardware at it, just move bottleneck down until you encounter it again.
Greg Low: I’ve done it enough, it’s mind-set, but I know some consulting I’ve been doing over few months, one of clients suddenly decided it would be useful to spend like a week every month having a look at performance issues in database but what intrigued me is didn’t do that before. Also did major upgrades because they were having performance issues. In end, so many things .NET guys if they do tuning see five percent differences, but in database, we see procs that go five million logical reads to 50, things like that. Intrigues they don’t do that first before spending fortune on hardware.
Kevin Kline: So common, find everywhere you turn, don’t know why it happens, wish I could blame poor training, inadequate understanding of concepts, or something, but even well trained people don’t seem to not make mistakes again.
Greg Low: Number nine?
Kevin Kline: I would consider to be more DBA mistake than design and it’s estimating disk’s need in terms of disk’s for volume but not for IO load. Happens a lot in shops have SANS where I’ll see DBA say to SAN administrator, “I need 500 GB” But also “I need maximum concurrence, maximum CPU rate of 3,500 transactions per second”. Sometimes they don’t know it themselves that metric is just as important as allocation of disks as having right amount of space on here.
Greg Low: In fact, good you mentioned SANS, be great one to answer questions on. Big changes I’ve seen very much moving to everything being SAN based, certainly Enterprise. But increasingly, when start to ask question how things laid out, disk performance, IO through-put, tend to get discussion that comes back “Don’t worry about that.” Yet, sort of perception if throw enough Gear under covers, just won’t be issue, but invariably it is.
Kevin Kline: So many people interpret SAN as cure-all for anything related to disk or IO, when in fact couldn’t be further. By using SAN introduce new issues, for example, cache, may have enormous cache. It’s possible how people distribute applications across SAN, all cache being consumed by exchange because it’s all across SAN. May have cache configuration too much for read or write, vice versa. Another common mistake I see with SANS is SAN administrator thinking only of needs of DBA in terms of volume. Chooses to use RAID 5 when right would be RAID 1 maybe application warrants RAID 10, but these aren’t consideration because disk is administrated by SAN administrated, who has no idea what performance profile application needs.
Greg Low: Tend to allocate volumes off SAN with no comprehension as to how that maps to physical drives under covers. I find invariably SQL folk think doing right thing getting logs on different volumes, but that isn’t happening anyway…
Kevin Kline: Fundamental and essential rule keep transaction logs on physically separate disks from database file. SAN can opuscate that, make impossible whether happening, and consequently happens quite frequently.
Greg Low: Other problem with SANS often hosted environments high end becoming more common. And shared SANS where becomes even harder to work at what’s going on, and just periods where things just go slow and can’t work out why because often don’t have tools to look at it.
Kevin Kline: Leads directly to mistake eight, not explicitly knowing server load and performance profile of SQL Server. Leads to variety of issues. Biggest one which I find to be very important, is have no idea because don’t know load of server. Don’t know what normal is nor is it easier to determine if behavior is abnormal. Because of that, for example, in my case in Deloitte, never tell until know explicitly what server load is. Never able to tell how good user experience is except by volume of calls you get. So if phone ringing, know things are bad. If don’t explicitly know through performance monitoring what server’s average load looks like and current performance compared to it, never have quantitative information tells whether things good or not. Leads to situations like a terrible problem existed on server since Friday, but since everyone left on Friday, don’t know ‘till Monday. If been monitoring, you could’ve fixed it over weekend. But since didn’t know what load on server was, come to find out when everyone comes in on Monday.
Greg Low: Substantial issue. Thing I always remember with performance shooting is always case of having base line because find invariably whenever someone monitors, everything looks strange.
Kevin Kline: One of common question I get is, example, monitoring disk’s length because they’re figuring out why disk is slow, and I get question “Disk line spikes up every so often and I can’t tell exactly what’s happening.” Experienced user, remember check-pointing happens so often and that’s when IO flushed disk. When folks looking at first time, don’t know what normal is and become concerned, without realizing its normal process of server.
Greg Low: Use to see that with network traffic monitoring, where people would go and run Ethernet Sniffers, and first time did that was when there was problem. Everything they looked at would leave them to go and investigate, but absolutely no idea its how it looks.
Kevin Kline: Great anecdote. Never thought of it. Seen same thing in past, and worst thing in world is to start looking at server load, is when there’s problem. Can’t tell what normal is. Can’t tell what’s abnormal because have no knowledge on what normal is.
Greg Low: What’s next?
Kevin Kline: Number seven very common developer mistake, not understanding how query engine of SQL Server works. Consequently, see horrifically bad queries that come out of ignorance how query works. Example, not understanding important of wear-clause. Main means by which reveal to query engine search arguments, and those are main things SQL Server turns and figures out indexes it can use, algorithms it can use, and so forth. Some things I’ve seen, issue of query against SQL Server and bring back all data and then having firming application sort out data at front end for user. Not only does it generate network traffic, generates tables scans, sole index reads, logical and physical IO, where as if had thought about and added wear-clause to query they issued, gotten better performance and saved client? Application in process in sorting out data.
Greg Low: Often also seen in development languages, often options that you can apply when executing commands and so on. People don’t understand difference those options make. For example, common one used to see AVO previously had record-set object and what had, options that said, “AVO open static” default key options and so on. When people ran in development, see no difference at all because working small amounts of data and only one working in database. When showed them profile trace, could see handful of IOs as opposed to 80,000 remote procedure calls, suddenly realize, changing word makes substantial difference. They’re often developing environments where can’t perceive problem.
Kevin Kline: Seen multitude of times. Often encourage developers to think about. Not just developers but bus owners and analysts as lay down requirements for applications, we see bus requirements for a stored procedure says, “Sorts of inputs, need to return x, y, z sorts of output” and that’s all it says. Occasionally see extra requirements that say, “Need to return output within 30 seconds”. Doesn’t say what it needs to say. “Will return results within 30 seconds under user load of 300 concurrent users.” At that scalability point, see application designed well or not.
Greg Low: Certainly found enlightening for people to watch profile trace just to see what’s being sent to database. Often removed from what’s actually hitting database. One of things fascinates me with directions of link in upcoming current Orca’s development cycle. People would be further removed from what’s going on. Supposed two possibilities there, one is it will write better code than they would have anyway, so if it doesn’t write great code, maybe better off. Alternatively, could write horrible code and sort of wonder where that leaves us. Watched a few demos where he said “Concerned about SQL generator can stop and look at it.” What they don’t follow up with is, “If you don’t like it what are you going to do next?” Look at this automatically generated SQL and it’s far from what normally want. Example, every time involve several tables, instead of using table, use sub-select from table simply so they can alias it and reuse it as co-generate query. Just looking at all this stuff and thinking “Oh my God, no way normally write query looks like that.” What I’m wondering, are other SQL team going to change way query optimizer works in SQL Server to deal with link better? I do wonder if development tools are going to push change.
Kevin Kline: Great question.
Greg Low: Don’t know answer.
Kevin Kline: Me neither.
Greg Low: See what happens.
Kevin Kline: Moving on, number six design issue closely related to number seven, not understanding query engine, which is indexing issues. Usually arise because don’t’ understand how indexes impact query performance and overall performance of application. Very common ones egregious of query errors, sort of pecking order in broad landscape of index issue. Example, having no cluster index of any kind, no primary key of any kind. Egregiously bad errors in terms of indexes. Other more subtle that in some cases are equally important. Example, not applying fill-factors to table that has lots of inserts or update changes to it, thereby causing page splits on that table. Sort of tuning technique often overlooked but is valuable in those situations where it could come into play. Other example not balancing indexes of given table, not balancing table for IO.
Greg Low: Question say with fill-factor, probably drill into a bit further, where we’re allowing percentage of space at leaf level of index with PAD_INDEX allows us to push up to higher levels as well. With fill-factor, question is where inserts occurring. Kind of useful if inserts occurring across range of leaf pages more so if they’re all occurring off table.
Kevin Kline: Exactly right. Depend how clustered indexes arranged and where records being inserted or if have lots of updates across spectrum of entirety of table rather than at last page or two. Situation like that, inserts going to last page, good to have not too much of fill-factor. Other situations very detrimental.
Greg Low: In fact seen converse of that where people make fill-factor low but haven’t thought of converse effect if page running half full got to read twice as many pages…
Kevin Kline: Exactly. Have to have good understanding of IO requirements of application as it applies to specific table because as mentioned, more fill-factor and also if use PAD_INDEX and take up to index level of specific table, then it generates more IO. Sometimes additional IO will not gain performance benefits. So it’s a mixed bag and has to have good understanding of application and how it moves its data against these tables and indexes and conversely how the query engine and transaction applied within application apply against database. A lot of issues I’ve described all go back to whether in position where can understand what’s happening or not. If choose to take path of not understanding, really taking a lot of risks.
Greg Low: Kevin, anything you’re willing to share? Where you live, what you do, hobbies, sports, etc.?
Kevin Kline: Well thing I’m most proud of is I’m the father of four wonderful kids. Ages 16 all the way down to six. If ever want to have intelligent discussion about SpongeBob SquarePants, I’m the man. I love every moment I’m able to spend with my kids. My oldest is my son, he’s sixteen, then I have three daughters aged fourteen, eight, and six. Quite a mix of ages.
Greg Low: This involves you to do sports or anything?
Kevin Kline: They’re rather homebodies so I don’t have to carry them to all sorts of activities but younger girls are getting active in karate and dance. My two older kids are very involved in music and theatre. So there’s a little bit of to and fro, but it’s all quite manageable and watch them mature and learn new things.
Greg Low: Which city were you living in?
Kevin Kline: I live in Nashville, which is nick-named Music City USA and it’s really funny and fun in some ways because when I was growing up, it wasn’t common for guys to join choir. But at my son and daughter’s school, they have choir and very large groups of men in choir and “Of course dad, it’s Music City, everybody sings here!” Conversely, my son is a very good guitarist. I grew up playing guitar and particular Spanish guitar. Plenty of rock, and stuff but in my hometown, if went to a party and somebody said, “Yeah I play guitar a little…” meant they knew a few songs. In Nashville, go to a party “Yeah I play guitar a little…” they proceed to play…
Greg Low: They’re absolute wizards yeah…
Kevin Kline: They only work in IT because that’s the day job and they’re trying to get into the music scene here and that’s their real passion.
Greg Low: Music can easily be passion, unfortunately can leave you broke.
Kevin Kline: Doesn’t pay the bills quite as well. But it’s to our benefit, just a plain old resident and can go to any tavern or bar and there’s quite a very good group performing for tips. No cover charge. Hoping to be seen by the record producer and get them in the door of the music business.
Greg Low: Carl Franklin wonders connection between developers and IT and music and the area and it seems really strong. Serious correlation between the two. Sort of wonder why that is. Any thought why music seems to fit people in IT? Patents, problem-solving or…
Kevin Kline: I’m not sure but believe people who excel in IT and are good at this are used to displaying their creativity through an instrument. So conversely, I don’t think you’ll find nearly as many people good developers for example, who might also be very active in local community theatre. Not used to it. Not comfort zone to be up there that’s just them, singing or music…Conversely, very comfortable in picking up instrument like computer and having that be representation of their creativity and intellect and capacity to do creative things. When turn around and pick up guitar or lay hands on keyboard, although thought process different, kind of energy derive from creative expression I think is similar. Can see why IT guys enjoy their guitar…
Greg Low: Intriguing. With all the things and kids, been any time for your own sports or hobbies or?
Kevin Kline: You know, it’s tyranny of paycheck these days for me. With four kids lot of mouths to feed. College funds to try and satisfy and so forth. My main hobbies I really had to subordinate to supporting family. I do have hobbies but they’re so small in comparison in how to spend my time. I enjoy reading, classical guitar myself, but I haven’t really been able to explore those too terribly much lately.
Greg Low: In reading too, I find most knowledgeable people in industry also have passion for reading. Had Yuval Lowie out talking about this and he’s someone who consumes books endlessly. What intrigues me often books non-technical. Reading interesting passion. Tend to have periods for long time where I haven’t read much apart from technical books. In recent times finally managed to read a few other things. Really refreshing. Get to read anything but technical books?
Kevin Kline: Indeed. When I say reading I mean outside of technology. My favorite these days are non-fiction books. Problem for fiction or enjoy novel, they tend to grip me and consequently don’t want to put book down and finish whatever job I have at hand. I tend to enjoy more often non-fiction because I can read them for a while and go back to work and read them again for an hour or two hours. Whereas reading a novel, I might see a drop in productivity because I’ll be sneaking off every chance I have to read it.
Greg Low: Company I work for, couple interested in Harry Potter. When next book in Harry Potter series appears, there will certainly be adults who disappear for a day or two who resurface after knowing what happened to Harry.
Kevin Kline: I’m caught in that trap too. Can’t wait to find out.
Greg Low: Book I’ve enjoyed most I’ve been telling people about was mentioned by one of my colleagues, Bill Bryson has wonderful book, Brief History of Nearly Everything. Kind of a wander from beginning of time quickly to present time. But it’s so funny in places and has amazing combination of depths of scientific knowledge but also humorous stuff. One quick story I’ll share about Thomas Midgley. Most people never heard of Thomas Midgley, and I know people describe him as single most dangerous organism that has infested the planet. Poor Thomas had three major inventions. Invented putting lead in petrol. Classically bad stuff. Second were CFCs. How one person could have both of those is beyond comprehension. Third invention, he got polio and invented machine to turn over in bed and it strangled him in bed.
Kevin Kline: How very ironic, isn’t it?
Greg Low: Probably done more to damage planet than any other organism that’s ever existed.
Kevin Kline: Who knows how many cases of skin cancer led fatality because of CFCs, you know asthma caused by leaded gasoline…
Greg Low: I think Thomas Midgley be poster boy on a dart board for environmental movement.
Kevin Kline: Interesting corollary to book I’ve been reading. Book called, Collapse Jared Diamond. Corollary is that when we touch different natural systems like air quality or water quality, don’t understand how many things it touches. May think knocking down single domino, but it touches ten dominoes, that touch ten others, etc. The author selected several societies throughout time that through conscientious decisions societies made provoked environmental collapse, and provoked entire collapse of the society. Example would be islanders on Easter Island. All seen famous stone statues, but Easter Island was once lushly forested and beautiful island full of wildlife and full of people too. So he goes through all kinds of archeological information as well as first-hand accounts of Europeans that visited island. When Europeans got there, about two or three generations there before society collapsed. Forty to fifty thousand people to when explorers arrived, two to three thousand people. How did this society fall from so great a height? What kind of lessons can we as a modern society learn from that? Looks at several others. Goes through archaeological evidence of the Mayans. Describes each of these and goes through multiplicity of systems, like for example by not even introducing lead to CFCs, disturb all kinds of systems. In situation like ours, we reverse course. Come of civilizations the author pointed out, they didn’t change course. Why is that? Interesting question he poses and interesting answers he brings up too.
Greg Low: I’ll add that to my list of ones I need to get. Back on SQL Server topics, I suppose we’re in top five, so what’s number five?
Kevin Kline: I think they’re all pretty vague and egregious errors. When SQL Server pro in bus sees things being done slaps their forehead and says, “Here’s this problem again.” Number five is using cursors heavily and TSQL code, not understanding difference between set theory and row-based processing. So it’s very common to see cursors and SQL Server Code. But cursors are the sort of thing that can be used profitably when it has very small need. Not going to process a lot of records. Plenty of times where I’ve seen group of programmers develop application where every time access data from SQL Server engine, use cursor to do it. In this situation, introducing great deal of overhead and additional work for engine. Memory consumption and locking issues… comes from lack of understanding of SQL Server processes Select Statements and impact cursors can do to SQL Server engine.
Greg Low: I think what’s intriguing is it may be development background but I think it’s where people learn to think procedurally. Don’t tend to learn much and think in terms of set-based operations. Often in that case think one of least understood areas is simple like case statements appropriately to allow you to express how to process entire set of things with some procedural type or logical decision without having to write procedural code.
Kevin Kline: Excellent example. Similar follow-on thought, yes very true developers perpetuate things they learn in development experience. They learn C Sharp for example, try to do things with C Sharp. Try to do in Transact SQL. One of things interesting being former oracle pro, see commonly someone in oracle retooling for SQL Server will make mistake frequently because in oracle world, any select statement you write is materialized behind the scenes in oracle query engine as implicit cursor. So cursors on oracle have no difference in performance compared to SQL select statement. When I was learning SQL Server having been Oracle for 5 years, took me a while understanding cursors are very different. Have to allocate them, and when done have to de-allocate, give memory then release locks open by cursor… Development background of person contributes to issue but also if have different platform experience before coming to SQL Server adds to this and compounds problem.
Greg Low: Interesting. I have to say most situations where I come across substantial cursor use and temporary table use tend to be from people with Oracle background. I’ve spent some time working with Oracle over years but never considered myself an Oracle person. It has intrigued me as to why that is. I come across shops where they’ve considered implicit cursors in Oracle almost evil. They’ve said “Always have explicit cursors.” When they make migration across, absolutely first thing they do.
Kevin Kline: Very interesting.
Greg Low: In fact, I suppose whenever I look for polite performing code often tell-tale signs when first open code for stored procedures, tons of cursor and temporary tables
Kevin Kline: Usually things indicate person doesn’t understand SQL Server very well. Example, one point developing large application and the developers used large number of cursors. Every one of stored procedures used procedures. I explained cursors not effective for large data sets typically unless there’s special exceptions. They can slow things down. Reason for poor performance of heavy use of cursors and temporary table. Made them take them out. They were very busy worked hard rewrote store procedures and brought back for code review and when I looked in first store procedure, very familiar had used cursors first thing they did was set row count equals one. While disconditioned loop through record one at a time, so use loop to look at record one after the other and make the change. I threw up my hands in exasperation saying “Let’s come back and focus on this idea of set-theory. Want to work with sets at a time rather than individual rows.” Funny anecdote. Taking cursors out and replacing them with loops to go through one record at a time…
Greg Low: Seen same thing many times. Also seen situations where people felt loops perform better than cursor. In many cases where it just isn’t case because they end up doing something like while I can’t select, if doesn’t exist, end up querying again every time go around loop. Every time standard cursor been far better. Other situation I find is when people don’t have good grasp of language at all. At site two weeks ago, lady there, when I looked at her use of temp tables she didn’t understand union statements. Things like, “I have to select for more than one place.” So she would create temporary table, insert selects from different spots and then do selective rows at end. No concept for her that she could’ve used union with multiple selects. Didn’t have to create temporary structures.
Kevin Kline: Wow. Yep that will certainly do it.
Greg Low: What’s number four?
Kevin Kline: Number four is another developer issue I find. We talked about several things that I think lead up to number four and number four is when don’t understand how to tune a query once has been written. Don’t mean sense of knowing how to write better SQL since I think that kind of falls in first two that appeared earlier, but I mean they don’t know how to use tools even free native tools that come with SQL Server to detect if query is performing badly then how to isolate that and work with it. Don’t know how to use profiler. Don’t know how to use DMVs or system views in SQL Server 2000. Don’t know how to use DBCC memory usage. Any things help reveal how well query is operating and go from there. As developer, essential to know how to use, even if don’t know how to use theoretical concepts. I can forgive developer for that for some reason, than not to understand the simple and effective tools right there at your disposal. Developer is tool using person. Tools should be thing they dive into and learn quickly.
Greg Low: Even in SQL Server 2000, reason called query analyzer. Not query executer.
Could do other things with it. Got session on DMVs for TechEd coming up. What are your favorites?
Kevin Kline: Look forward, and saw your session with great interest because I’m not quite as well versed in DMVs as I want to be. Hope to get better panorama. So many DMVs now.
Greg Low: That’s thing. There are so many. At moment, every opportunity I get, I ask “What are your favorites?”
Kevin Kline: I can’t give favorites just on limited knowledge I have. DMVs aren’t direct one-for-one equation compared to old system views in SQL Server 2000. Some of DMVs I like are the OS Memory Clerks, and several of those clerk DMVs are really useful because they tell you things how much memory these particular store procedures consuming and procedure cache or table consuming in buffer cache. Then writing clever queries against DMVs can see top ten consuming store procedures or most commonly called tables, and different things associated with memory. Feel like area where can make performance gains in understanding SQL Server memory.
Greg Low: Wonderful view they’ve given us into what’s going on in SQL Server.
Kevin Kline: Instrumentation is so much richer now in SQL Server 2005. I think in way, kind of about time. As old Oracle hound, I can tell you Oracle had sorts of views for many years. They’ve used this approach for quite a long time and had much better instrumentation. Good to see SQL Server coming onto parody with Oracle in regards to that. On practitioner point of view, great to have ability to utilize these and get information out of system. So helpful.
Greg Low: Well what are we up to number three?
Kevin Kline: Yes. I think number one design mistake I see out there and when it comes to design, amazes me how many people don’t know what normalization is. Extension of that, if they have learned, they don’t know when it’s appropriate not to normalize. Comes down to improperly normalized tables. Not normalizing enough or too much or inappropriate application constructing normalized tables for data warehouse. Amazes me people don’t know how to go through rigorous process of table design and deciding what data is held in specific table and what is put into other tables and linked through relationship on primary keys and foreign keys. Sort of thing people say “All academic.” Really does make a difference.
Greg Low: I often look at problems with query performance and directly related to the tables being poorly designed in first place. Queries end up being in order of magnitude more complicated than need to be.
Kevin Kline: One of things I see frequently. Our industry is one in USA that needs people with these skills. Many cases take people with related experience. Promotion for them so they get a new job. Let’s say good with Access or good with Excel. Get to work on SQL Server so design SQL Server tables exactly like once they were small access desktop application or Excel spreadsheet. I don’t know how many times run into developers, “I need to put records in file.” What they mean haven’t learned terminology in SQL Server since file is sort of Access terminology. One of moments have to gasp and say, “Let’s start at beginning…” Instruct them on what it means to design table properly.
Greg Low: You mentioned same lady who didn’t follow union thing had endless discussion why it even mattered to have primary key on table, etc. People get put into roles doing this stuff. When can explain things to them there’s an “Ah-hah” moment. Sad it’s at end of project rather at the beginning.
Kevin Kline: Indeed. Number two, last two are DBA issues. Having no or very poor error notification in place on your SQL Server. Number of tools make very easy to get messages straight to your cell phone or e-mails, sorts of notification. Don’t have to buy other tools. Yet so many people ignore these. Sometimes daunted by fact “I’m only DBA here. I have 30 servers I have to support…” Frightened by scale of amount of support they have to do. At end of day, you can consolidate and standardize error notification, makes job easier not harder. Otherwise I’ve seen effective DBAs render almost completely ineffective if they can only deal with servers through sort of fire fighting mentality. “If they break I’ll deal with it, otherwise I’m going to ignore it.” Self-defeated attitude. I understand perspective because they’re so busy that if they take time to make things better, they’ll fall behind. Only way you can get on top of situation.
Greg Low: That’s very true. Otherwise doesn’t scale enough to be able to do that.
Kevin Kline: These people do not scale. So often asked to support environments that scale a lot.
Greg Low: Number one?
Kevin Kline: Number one mistake in SQL Server world I see comes from amount of pain it inflicts both psychologically and physically are those sites that have gone through the trouble to test back up and feel safe, but didn’t go extra step to test recovery. Discovery not only not safe, actually seriously at risk. By not understanding recovery process have to spend whole week in office going through recovery process to bring things back up online when if they had taken back-up one step further during day hours and going through full recovery process and learning exactly all gaps and shortcomings have in backup process, have reliable recovery process as well. Number one error mistake on SQL Server is not testing backup process all the way through to a full recovery.
Greg Low: I must admit, bit I like to add is usually on another machine.
Kevin Kline: Exactly. Very good point.
Greg Low: See so many situations where only tape drive on planet that will read tape is the one that just got stolen or destroyed… Even though process worked some of the things they needed didn’t get backed up and so on…
Kevin Kline: So many shops, “Everything’s good!” What he means is he has backup of database. User database. So many things they forget about and only way can learn is do full restore to essentially from bare metal of server. Install OS, install SQL Server then try to recover from that. Discover maybe didn’t have service packs you needed. Are you running same version of MDAC? Maybe application has older version out there.
Greg Low: Or if have to put another server in place, will clients talk to it?
Kevin Kline: Maybe haven’t backed up text catalogs or…Another one I’ve seen is DBA recovers user database but didn’t backup master so they don’t have logins or permission information required to allow users to connect to database.
Greg Low: There’s one I’m going to put plug in for too. On connect side, connect.microsoft.com. One of suggestion I have is “Create login” and where you can say, “From database”. Create login from existing one in database. I noticed in Server Pack 2, gave option where “Create user” and you can have it deal with mismatched security IDs but what they do is go off and fix on in database. Like they did with SharePoint user’s login. Most common scenario other way around. When restore database on other servers. Don’t want to come up with new security IDs. What I wish could do is “Create login” “With SID from database such and such…” and have it retrieve original Security ID and be simpler thing to deal with. Need people to go and vote for it.
Kevin Kline: Connect.microsoft.com? That is brilliant.
Greg Low: I think that would be really pleasant.
Kevin Kline: We’ve sat through many of those restore operations that aren’t so pleasant.
Greg Low: Must look at common one I’ve come up across a couple times. Almost like tooling error. People go to backup dialog and add file then don’t delete one that’s already there and end up striping database backup across two files and then when new one’s created, just get it then later try to restore from it.
Kevin Kline: Ah. Haven’t heard that before. That’s very interesting.
Greg Low: Seen that a couple of times now. That’s all it is. They go and do backup, there’s already file in the box. Don’t remove it and it immediately stripes across two files. Don’t think most people seem completely unaware that that can even occur. Then they’re surprised they get and error. It’s kind of sad.
Kevin Kline: I agree. Again, that’s one of things about recovery. Nothing worse finding out problem with backup when you’re recovering.
Greg Low: Exactly same problem. Yes. That’s great Kevin. Other question I’ve got, where will we see you? What’s exciting at Quest?
Kevin Kline: I have a lot of activities going on. Be speaking at a lot of user groups, not just throughout USA but around world. Today is Tuesday the 24th, or Wednesday 25th in Australia. All of next week I’ll be in Ireland going to biggest cities speaking at SQL Server and .NET user groups. Mid May I will be at DevTeach conference in Montreal. In June, making plans speaking several locations throughout Germany, Austria, and Switzerland and various German-speaking countries.
Greg Low: Plus June in TechEd?
Kevin Kline: Yes. That’s right. TechEd first, then week off then go to Germany then take two oldest children with me on that trip. We’re going to have some fun during the days and in evenings speak to user groups and do things like that. Some smaller local group meetings. Charlotte, National User Group as well, gosh, hard to keep track of traveling and speaking I’ll be doing these days.
Greg Low: And PASS Conference, September of this year. Bittersweet year. I’m thrilled to serve as president for past four years. This conference which I hope will be largest yet. We’ve seen growth every year and is more successful. I hope my legacy will be one of PASS continuing to grow and increasing in reach and ability to help community. Educate community and so forth. On one hand I’m kind of sad my term of service as president will end this year. That will be my last conference as president. On other hand, PASS takes quite a few hours in the week. Nice to give back to my work and family and be able to sleep late every now and then. Is a bit of sweet to the bitter as well.
Greg Low: What about at Quest? Anything exciting?
Kevin Kline: Thing or two I’d mention about Quest. First is we have new drop of our LiteSpeed product coming out next week. Version 4.8. It’s well known that enables you to do backups in greatly compressed in terms of size, amount of space to consume. Time it takes can be reduced 50-70 percent. New features in new drop excited about is… People like object level restore in LiteSpeed which can’t do in native tools. Can recover single database, but one of things I like in next release is you can attach to a backup file, either natively or SQL LiteSpeed and issue queries against native backup or LiteSpeed backup file. Without ever attaching it, restoring database.
Greg Low: That’s very great.
Kevin Kline: Run into customers whole reason doing backup isn’t because of error on disk or crash but because user did something crazy and have to repair all the data and only way can repair is pull up last backup and re-query good data back out of database. This gives users ability to do that without having to go through all extra process to get to that data. It aids in those recovery situations. Also have in next release nice log-reader feature built into system. If want to undo single transaction or two or three small transaction in transaction log, also very easy thing to do. Also have LiteSpeed Express products similar to SQL Server Express. Free version of LiteSpeed and it’s free for any database up to two GB in size. For all developers or QA people or folks tinkering more than supporting production applications, might want to take a look at LiteSpeed Express. Great way to get extra power and benefit without paying for full license. Couple other products. We have for SQL Server 3.0 in data right now and has fantastic features I’m excited about. It’s most powerful TSQL IDE you’ll find anywhere. All sorts of features I think really ground breaking. Also we’re in data two of product called Performance Analysis, which is most amazing performance related tools I’ve ever seen. Includes capabilities we’ve had in past to capture granulated detail on SQL Server but also has smart advisor so it not only tells you about performance but also ways to improve it.
Greg Low: What you can do about it…
Kevin Kline: Tells you time server was heavy in latch weights and give ways to fix it. Miraculous sort of thing. Kind of tool I could’ve used to make my life easier long time ago.
Greg Low: That’s great. Well listen, thanks again, Kevin taking time to talk to us today. I will see you at TechEd I presume?
Kevin Kline: My pleasure, Greg. Thank you so much for the invitation to come and speak with you and I really appreciate it.
Greg Low: Thank you.
Phone: 1300 SQL SQL (1300 775 775) l International +61 1300 775 775 l Fax: +61 3 8676-4913
Copyright 2017 by SQL Down Under | Terms Of Use | Privacy Statement