The best DBA of all time
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
Dave Kerr wrote:
Try to work out how best to let him down gently
Risky, very risky
Dave Kerr wrote:
Then wonder why he's getting paid three times what I am.
He's a better salesman - he may not know his way around database systems, but he knows how to handle management ... but then, I guess you knew that ... ;)
Espen Harlinn Senior Architect, Software - Goodtech Projects & Services My LinkedIn Profile
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
Dave Kerr wrote:
Anyone else get this sort of thing?
All the time, not just DBA's...
-
Dave Kerr wrote:
Anyone else get this sort of thing?
All the time, not just DBA's...
Still bitter over the time the shopkeeper told you to index every table huh? You'd only popped in for a sandwich.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
And why did it take so long for him to come up with this solution? When I've heard an inexperienced DBA suggest such a thing, it usually leaps out of their mouth with only a little fanfare.
_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug... The difference between an ostrich and the average voter is where they stick their heads.
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
-
So what's the problem with indexing every field? Politely nod. Sigh but did you ask him what his argument was for doing so?
"You get that on the big jobs."
It makes more sense when you actually try it. Do it, for real, sit at your keyboard, clasp your hands together, politely nod a couple of times, then sigh. To increase the effect, close your eyes, and gradually turn your nod into a shake of the head side to side, while slouching into your seat, gradually further and further until only the top of your head is visible above the desk. Then begin sobbing quietly.
-
It makes more sense when you actually try it. Do it, for real, sit at your keyboard, clasp your hands together, politely nod a couple of times, then sigh. To increase the effect, close your eyes, and gradually turn your nod into a shake of the head side to side, while slouching into your seat, gradually further and further until only the top of your head is visible above the desk. Then begin sobbing quietly.
-
It makes more sense when you actually try it. Do it, for real, sit at your keyboard, clasp your hands together, politely nod a couple of times, then sigh. To increase the effect, close your eyes, and gradually turn your nod into a shake of the head side to side, while slouching into your seat, gradually further and further until only the top of your head is visible above the desk. Then begin sobbing quietly.
What if you had a static database and the queries potentially searched on any of the fields? When you think about it, it would then make sense to index all fields but it sounds like there is rule against that. You can sigh and nod all you want but unless you ask the question...
"You get that on the big jobs."
-
What if you had a static database and the queries potentially searched on any of the fields? When you think about it, it would then make sense to index all fields but it sounds like there is rule against that. You can sigh and nod all you want but unless you ask the question...
"You get that on the big jobs."
You could craft "what if" clauses to satisfy any given scenario. But without more specific information, which is likely too confidential to post anyway, you have to go with the average situation, in which indexing every single column is almost always picking at the wrong symptoms, and an unprofessional, uninformed overreaction to the problem.
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
-
What if you had a static database and the queries potentially searched on any of the fields? When you think about it, it would then make sense to index all fields but it sounds like there is rule against that. You can sigh and nod all you want but unless you ask the question...
"You get that on the big jobs."
I worked at a place that had a static database that was reloaded every night. (For details it was a tax law document library) They wanted to index every document to each other for faster retrieval times, hopefully to sell more access to those books. Problem was, the projected time to rebuild these expanded indexes was over 24 hours. See above on how the database was reloaded nightly, and I think you'll see the problem.
Psychosis at 10 Film at 11 Those who do not remember the past, are doomed to repeat it. Those who do not remember the past, cannot build upon it.
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
Dave Kerr wrote:
Try to work out how best to let him down gently
Unfortunately, that's the time when you shut up and let the ship sink instead of walking the plank by telling the captain he's wrong.
"To alcohol! The cause of, and solution to, all of life's problems" - Homer Simpson
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
-
I worked at a place that had a static database that was reloaded every night. (For details it was a tax law document library) They wanted to index every document to each other for faster retrieval times, hopefully to sell more access to those books. Problem was, the projected time to rebuild these expanded indexes was over 24 hours. See above on how the database was reloaded nightly, and I think you'll see the problem.
Psychosis at 10 Film at 11 Those who do not remember the past, are doomed to repeat it. Those who do not remember the past, cannot build upon it.
That's sort of like the daily backup that takes 28 hours.
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
Hes not a DBA then, or he is a dba in training... its a common mistake, and usually and honest one... however... he could be right if there are less than 5 columns and the RDBMS does not allow clustering of columns in the indexes... overall though you are correct, this is usually a terrible idea. The maintenance on this would be bad even for a small table and wasting CPU cycles (I know most everyone does not code for CPU inefficiency in distributed systems, unless the cycles are above NN% or NNN% for Mutliproc)... Not to mention that an insert update or delete would be very much drawn out longer than it needed to be. A rule of thumb for MOST major RDBMS (DB2, Oracle, MSSQL, Mysql) no greater than 5 indexes per table. IF you stick to this and follow your rules for using multicolumn indexes, High Cardinalyty and some very basic rules for SQL writing (group instead of sortby, Predicate order, etc...) you can very efficiently make your code SING!
-
Hes not a DBA then, or he is a dba in training... its a common mistake, and usually and honest one... however... he could be right if there are less than 5 columns and the RDBMS does not allow clustering of columns in the indexes... overall though you are correct, this is usually a terrible idea. The maintenance on this would be bad even for a small table and wasting CPU cycles (I know most everyone does not code for CPU inefficiency in distributed systems, unless the cycles are above NN% or NNN% for Mutliproc)... Not to mention that an insert update or delete would be very much drawn out longer than it needed to be. A rule of thumb for MOST major RDBMS (DB2, Oracle, MSSQL, Mysql) no greater than 5 indexes per table. IF you stick to this and follow your rules for using multicolumn indexes, High Cardinalyty and some very basic rules for SQL writing (group instead of sortby, Predicate order, etc...) you can very efficiently make your code SING!
Good user name! :thumbsup:
_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug... The difference between an ostrich and the average voter is where they stick their heads.
-
I worked at a place that had a static database that was reloaded every night. (For details it was a tax law document library) They wanted to index every document to each other for faster retrieval times, hopefully to sell more access to those books. Problem was, the projected time to rebuild these expanded indexes was over 24 hours. See above on how the database was reloaded nightly, and I think you'll see the problem.
Psychosis at 10 Film at 11 Those who do not remember the past, are doomed to repeat it. Those who do not remember the past, cannot build upon it.
I've found the solution to this problem is to remove the indexing, do the reload and then reapply the indexing. I guess it depends on the amount of indexing involved but I remember one process that took a similar amount of time was cut to about 20 minutes. Indexing can really hammer data entry.
"You get that on the big jobs."
-
So we're talking to the customer's DBA about how we've managed to improve certain queries on our system by a careful analysis of SQL Server usage and by adding indexes to a couple of database columns, removing indexes from others. The customer DBA looks very thoughtful at this and comes back a few days later with the air of a man with a plan. "I've been thinking about it" (worried silence from all) "I know how we can improve performance. Put an index of EVERY column of EVERY table." Politely nod. Sigh. Try to work out how best to let him down gently. Then wonder why he's getting paid three times what I am. Anyone else get this sort of thing?
My Blog: http://www.dwmkerr.com
Perhaps you've missed my story on the head of IT that couldn't log in[^]? Yeah, I definitly get that kind of stuff :laugh:
It's an OO world.
public class Naerling : Lazy<Person>{
public void DoWork(){ throw new NotImplementedException(); }
} -
If you're concerned about this, you clearly have either a) too many tables, b) too many columns in your tables or c) too much data. After indexing, start deleting columns/tables until performance improves.
-
That's sort of like the daily backup that takes 28 hours.
I gave a suggestion to remove functions that produce a constant in the where clause. With an example that cut the query from 13 seconds to 30 milliseconds. The group proceeded to remove every function they could from where clauses. I asked about removing GETUTCDATE in the maintenance apps, since it didn't produce a constant value. "Since it was a daily cleanup job, the few remaining rows will be picked up the next day." That made emminent sense and since the idividual queries would run quicker, it went into production. Most of the jobs did run faster with no problem and all of them ran faster at first. Didn't pay attention to the jobs, as soon as one went over 24 hours, the next daily job didn't submit. The next job was 48 hours out of date and fairly quickly reached a running time of a week. Since the constant value query still made good sense, we analysed the queries, figured out the inefficiencies in the queries, fixed that as well. (In one case, the datetime field used to delete was indexed. Scratched my head, realized what was going on, forced the query to ignore that field for looking up records and increased loop speed 50 times.) Anyway, even good ideas can have bad consequences. The reason it worked OK with the slower query... If it ran for 16 hours, the next run only had 8 hours to fix. With daily and weekend slowdowns it remained stable.
