December 23, 2015 at 11:14 am
chudman (12/23/2015)
This happens quite often. It would be great to be able to go to a web page where the SQL DBA collective could review a list of packaged application/db's that are troublesome. Each app would include descriptions that cover the problems that will be encountered as you begin to maintain one of these warthogs, describe issues you will encounter during application upgrades and various workaround attempted.Sort of an Angie's list of crappy db design. All sorted on industry segment or general purpose. It would be a sanity check. If you have tried all kinds of things to help performance and nothing works, it would be nice to know that this problem has been encountered before and what was done.
It would probably have to live in the deep web, because nobody likes the truth if it hurts the bottom line.
If somebody starts one, I can add one right away.
Jeff Bennett
SQL DBA
It's a great idea. I'd wonder about the legal liability. Many package vendors have a "DeWitt Clause" that prohibits reviews of their software (yes it's named after Dr. DeWitt of SQLPass Summit Keynote fame - Oracle did not like one of his performance benchmarks when he tested it as a college professor and designed the clause to keep people from circulating bad reviews) without company permission.
Also, a libel suit is possible. While the lawsuit might fail, who has the money to defend a massive lawsuit?
I'm going to study the idea and speak with a fried of mine who is an IP attorney in Chicago.
Thanks
John.
December 23, 2015 at 11:15 am
I am pretty sure I have seen this recently.
December 23, 2015 at 11:18 am
blog 24112 (12/22/2015)
Nice story John. One I'm sure most of us that have been working in our field for longer than a few years can relate to.I had a question, and comment, about the "Missing Index" script by the SQL expert Pinal Dave that you so glowingly referred to. First, I'll admit right up front, I'm NOT an SQL expert. That said, I downloaded the wonderful script thinking it might give me (a NON expert SQL person) some valuable insight into possible ways of improving my database performance. Then I ran the script and got a lovely result of... nothing.
So then I thought, well, maybe I have to modify the script somewhere so it knows the database I want it to check. NOT being an SQL expert, I tried putting the database name into the parameter for the DB_ID() function and ran it again... same result.
The script happily runs and produces no output that I can find. So, I'm left to assume that even though I'm NOT an SQL expert, my database is so well designed that the script can't find a single area where perhaps a new index may help. Which just doesn't seem very likely to me.
Now for my comment. I find it amazing that so many "experts" provide code samples, scripts, or whatever and apparently ASSUME they are providing the information only to other "experts" that will automatically know HOW to use their code, script, whatever with NO EXPLANATION AT ALL, or will automatically know HOW to put in all the missing pieces they left out because they ASSUMED the "expert" reading it should recognize they left all those little details out.
It makes me wonder why in the hell they bothered even providing the code, script, whatever in the first place. If it was only meant for "experts", why would they assume any other "expert" wouldn't already know the information they are providing? It wouldn't seem to require TOO MUCH logic to deduce that if you're going to provide code, script, whatever to people THAT DON'T ALREADY KNOW what you (the "expert") knows, maybe, just MAYBE, you should provide some little tidbit of information on what ADDITIONAL information a NON EXPERT might need to know to actually USE your little code, script, whatever. (rant over)
If someone could tell me what I'm missing in my efforts to use Dave Pinal's wonderful "Missing Index" script, it would be greatly appreciated. Or, once again, maybe my database design really is just so perfect that it can't be improved...
It uses the DMV in SQL 2005+ that diagnoses missing indexes. As a caveat, it finds only the missing indexes detected since the last startup of SQL Server. I have more than one DB of the 600+ that I manage where I get no missing indexes. It is possible to have sufficient indexes in place to cover all index needs.
Maybe Pinal can come onto this thread and speak to this. Let me see if I can reach him.
Thanks
John.
December 23, 2015 at 11:25 am
Did you execute the script within the context of the database you want to analyze? If you run this script against the default database when you open a query window, then you are probably running it against master, and that will result in an empty set.
Jeff Bennett
SQL DBA
December 23, 2015 at 11:36 am
Hey, Thanks! John. I really appreciate someone that actually reads peoples comments and replies. I've been designing complete applications and their databases for over 25 years, so I'm not a complete idiot when it comes to DB design. On the other hand I've only been using a "relational" DB for the last few years and basically learned just enough to get the job done. Most of my time is spent on the actual application programming.
All of my tables have primary keys which probably cover 90+% of the accesses to that table. Even so, I'm sure my DB design could be improved. You mentioned that the Missing Index script only finds "missing indexes detected since the last startup of SQL Server". Perhaps that's the reason I'm not getting any results. I only ran it against my development DB, not the live production DB.
Thanx again for the reply.
Randy
December 23, 2015 at 12:57 pm
chudman (12/23/2015)
Did you execute the script within the context of the database you want to analyze? If you run this script against the default database when you open a query window, then you are probably running it against master, and that will result in an empty set.Jeff Bennett
SQL DBA
Exactly what do mean by "within the context of"? Did I have the target DB open? Did I have it highlighted? Was I mentally focusing REALLY hard on the one I wanted? What?
December 23, 2015 at 4:40 pm
blog 24112 (12/23/2015)
chudman (12/23/2015)
Did you execute the script within the context of the database you want to analyze? If you run this script against the default database when you open a query window, then you are probably running it against master, and that will result in an empty set.Jeff Bennett
SQL DBA
Exactly what do mean by "within the context of"? Did I have the target DB open? Did I have it highlighted? Was I mentally focusing REALLY hard on the one I wanted? What?
USE DbToAnalyze
GO
--Run Missing Index Script
Also, check out Brent Ozar's sp_Blitz scripts. There's even a set of temporary stored procs that are mostly up to date from what I can see if you don't want to create the procs permanently. They're free but do ask for an e-mail address.
As noted above, this won't be too useful unless the system has been used for a while as the data behind the DMVs doesn't persist across server restarts. It also won't help you if you're running against the read-only node of an Availability Group if that applies in your scenario.
December 24, 2015 at 12:30 am
Peter Schott (12/23/2015)
blog 24112 (12/23/2015)
chudman (12/23/2015)
Did you execute the script within the context of the database you want to analyze? If you run this script against the default database when you open a query window, then you are probably running it against master, and that will result in an empty set.Jeff Bennett
SQL DBA
Exactly what do mean by "within the context of"? Did I have the target DB open? Did I have it highlighted? Was I mentally focusing REALLY hard on the one I wanted? What?
USE DbToAnalyze
GO
--Run Missing Index Script
Also, check out Brent Ozar's sp_Blitz scripts. There's even a set of temporary stored procs that are mostly up to date from what I can see if you don't want to create the procs permanently. They're free but do ask for an e-mail address.
As noted above, this won't be too useful unless the system has been used for a while as the data behind the DMVs doesn't persist across server restarts. It also won't help you if you're running against the read-only node of an Availability Group if that applies in your scenario.
Thanx Peter
December 24, 2015 at 9:03 am
dbeggs57 (12/22/2015)
After reading just the first section "The Horror Begins", I thought you were talking about a Microsoft Dynamics application. With a slight bit of exaggeration added, just slight. I am the BI developer of one of these older Dynamics packages. I am glad to hear it is a (semi-)fictional story.
My exact reaction also, except I did not think there was any exaggeration:-). It was a real disappointment to open a Dynamics database schema the first time. I wonder if there is a single "Best Practice" that they managed to follow;-)
Since that experience I have had the misfortune to run into other CRM/HR systems that follow similar practices as well as a couple of industry specific (health care) applications that were at least as bad.
It's not just ORM's though. I think the "database agnostic" design is one big part of the issue. Another is that most of the applications I have seen don't seem to consider the "data model" from a global or enterprise view. Each development sub-team seems to create whatever functionally specific objects they need.
December 24, 2015 at 9:38 am
Ray Herring (12/24/2015)
dbeggs57 (12/22/2015)
After reading just the first section "The Horror Begins", I thought you were talking about a Microsoft Dynamics application. With a slight bit of exaggeration added, just slight. I am the BI developer of one of these older Dynamics packages. I am glad to hear it is a (semi-)fictional story.My exact reaction also, except I did not think there was any exaggeration:-). It was a real disappointment to open a Dynamics database schema the first time. I wonder if there is a single "Best Practice" that they managed to follow;-)
Since that experience I have had the misfortune to run into other CRM/HR systems that follow similar practices as well as a couple of industry specific (health care) applications that were at least as bad.
It's not just ORM's though. I think the "database agnostic" design is one big part of the issue. Another is that most of the applications I have seen don't seem to consider the "data model" from a global or enterprise view. Each development sub-team seems to create whatever functionally specific objects they need.
The inspiration for this story was a "database agnostic" ERP application. The trouble is that the lowest common denominator was not SQL Server->Oracle->mySQL - it seemed to be IBM ISAM or worse. mySQL and Oracle support ANSI joins and typed columns. Moveover BIGINT (int64) column types with an identity specification are supported by the big three as well, which means that they are the perfect clustering index for SQL Server/mySQL (I do realize that Oracle IOT are not all that great but an Oracle implementation need not use IOT).
The database seemed to be designed to make it fast to code the programs, not to store the data properly.
That frustrates me because even a little effort to normalize the data will pay off big-time in both insert and select performance.
Thanks
John.
December 24, 2015 at 10:51 am
Hi
This story may be fiction, but most DBA's working in larger organisations have gone through this exact scenario.
January 1, 2016 at 3:56 pm
How about this one: the stored procedures in this vendor application where I am at are all TEMPORARY. They are created, executed, and dropped. There are hundreds of them.
January 1, 2016 at 9:31 pm
John Langston (1/1/2016)
How about this one: the stored procedures in this vendor application where I am at are all TEMPORARY. They are created, executed, and dropped. There are hundreds of them.
Heh... dropping them is stupid. They can create Temporary Stored Procedures much as you would a Temp Table and, yes, they do begin with a # sign. 😛
I guess it does guarantee that the database code will be automatically released when the application is released. At least they're using stored procedures instead of 100% managed code. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2016 at 7:37 am
Never thought about their being released when the application is released. I just viewed this approach as pointless since those generated query plans will never be re-used. The only reason I could ever see is to make their application harder to rip-off by forcing someone to profile their code in order to capture the business logic contained in the stored procedures.
January 2, 2016 at 7:42 am
John Langston (1/2/2016)
Never thought about their being released when the application is released. I just viewed this approach as pointless since those generated query plans will never be re-used. The only reason I could ever see is to make their application harder to rip-off by forcing someone to profile their code in order to capture the business logic contained in the stored procedures.
Whatever the case, I agree that it does seem a bit insane to me for them to do such a thing. Thank goodness they don't do the same thing with the tables! 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 87 total)
You must be logged in to reply to this topic. Login to reply