October 29, 2013 at 4:45 am
At my workplace I was stunned by the general "No FK please!" police adopted.
They said they got bad time with FKs and decided to ripe it off and to not use it anyway.
They arguments are FKs makes de DB to run slow, very slow.
After a inspection on the scenario I found the average application do a lot of inserts/deletes and the old NOLOCK (we are using 2008 r2) hint is used at all joins.
Its like hundreds new rows are inserted by minute, half of it on old records are deleted while some massive BI/reports are running in background.
Also it scares me a bit how they love to use GUID fields as PKs.
Some databases grows quickly and they paid a lot for a third party "data integration" solution to handle data bumping and It looks like using some sort of built in referential integrity mechanism (but some coworkers whispered its not a reliable one)
Also every server is a VM. I just ventured for development and test servers and they are a mess with hundreds of databases.
they are slow and at some hours they just stop, probably a few tests start to run at same time and that server runs out of physical resources.
Even to just open a db diagram is slow, is funny to see SQLSMS struggling to draw that tables, one by one. A dozen tables can take like a minute to get draw (slow to retrieve the db objects info?).
I'm not tasked to put a end in the mess, I'm just a developer with a (accidental DBA background) very concerned about how data bases are being handled.
To my experience if the DB runs slow without FKs and a lot slower with FK, if they need to put nolock on every select its a symptom rather than a solution.
I'm just the "new guy" and since its "how thing are done here" for a long time now I fell ill need a lot of ammo to help some minds to change.
To start I'm asking to advice, not just technical but also what I can argue with my boss and not sound bad for the guys put that police in charge.
Thanks in advance.
October 29, 2013 at 5:07 am
Hi,
They arguments are FKs makes de DB to run slow, very slow.
This is not true. Moreover, if the FKs are supported with indexes then they simply bring performance to the system.
After a inspection on the scenario I found the average application do a lot of inserts/deletes and the old NOLOCK (we are using 2008 r2) hint is used at all joins.
If it's a transnational system then NOLOCK is not recommended.
To start I'm asking to advice, not just technical but also what I can argue with my boss and not sound bad for the guys put that police in charge.
You have to make performance investigations. For e.g. take the database on a test server, create the FKs, create supporting indexes on them and measure the queries speed. Save the results. Then measure the queries speed on their original database and show that yours changes bring performance. You can also combine using a bit presentations, best practices and etc...
Anyway, I had a similar case.
not sound bad for the guys put that police in charge
Well, I cannot predict the reaction, I can tell you for me: I got the etiquette of a 'bad' guy 🙂
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
October 29, 2013 at 5:57 am
As for the GUIDs, I was fortunately enough to attend a seminar on keys by Kimberly Tripp once and she said that she uses a few criteria when looking for a clustered index: Unique, Narrow, Static and Ever-Increasing. She went into each one in-depth and it was a good presentation. During the Q & A session that followed, she was asked specifically about using GUIDs and she correctly pointed out that they're not ever-increasing. Another point to consider is that the uniqueidentifier data type consumes 16 bytes instead of 4 like an integer. That's 16 bytes for the primary key and 16 bytes for each foreign key.
On the topic of NOLOCK, Gail Shaw knows quite a bit about performance, so please read http://www.sqlservercentral.com/Forums/Topic563258-338-2.aspx#bm563628. I've seen quite a few posts about using the NOLOCK hint as a panacea.
When you said that they had a bad time with using foreign keys in the past, my guess is that they didn't have any supporting indexes. In Oracle, when you create a foreign key, it automatically creates a supporting index for you, but SQL Server does not. So any time you want to do a delete of a parent row, it causes a full table scan of the child table(s) before deleting the row in the parent. This understandable performance hit is easily remedied by a simple nonclustered index on the foreign key field.
If you haven't been tasked with fixing the problem and are also the new guy, be careful. While you sound like you can and should bring this to light, you have to be prepared and don't want to step on anyone's toes while doing so. Like IgorMi suggested, create a test copy of the database, do some performance testing, fix things and do the same tests again. I would save all the queries you run for your tests so people can see that they're the same queries. Yes, you should bring this to the attention of the DBAs, but you don't want to lose your job in the process. Prepare, test, test, test and present.
Good luck with it. I hope this helps.
October 29, 2013 at 8:01 am
Yeah that sounds like they need some serious help. You are absolutely right that adding NOLOCK to every query is dealing with the symptom not the cause. The real issue with that is it will bring its own issues to the table that are for more difficult to deal with. Things like missing and/or duplicate data is not only possible it is very real and pretty easy to duplicate. Here are some of the better articles I have seen on this topic.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2013 at 8:02 am
Sounds like a place I contracted at once that had a 'no temp tables' rule. One of the boss-types was convinced any sort of temporary table was a performance hit.
I was very glad when that contract ended.
October 29, 2013 at 11:01 am
Ed Wagner (10/29/2013)
If you haven't been tasked with fixing the problem and are also the new guy, be careful. While you sound like you can and should bring this to light, you have to be prepared and don't want to step on anyone's toes while doing so. Like IgorMi suggested, create a test copy of the database, do some performance testing, fix things and do the same tests again. I would save all the queries you run for your tests so people can see that they're the same queries. Yes, you should bring this to the attention of the DBAs, but you don't want to lose your job in the process. Prepare, test, test, test and present.
A +1 on this.
Tread lightly, especially as the new guy. Your comments about the "way they do things" can be easily misunderstood especially if you haven't been asked to fix the issues you are referring to.
Once you've done as has been suggested, carefully choose your moment to share your findings.
October 29, 2013 at 12:35 pm
OTF (10/29/2013)
Ed Wagner (10/29/2013)
If you haven't been tasked with fixing the problem and are also the new guy, be careful. While you sound like you can and should bring this to light, you have to be prepared and don't want to step on anyone's toes while doing so. Like IgorMi suggested, create a test copy of the database, do some performance testing, fix things and do the same tests again. I would save all the queries you run for your tests so people can see that they're the same queries. Yes, you should bring this to the attention of the DBAs, but you don't want to lose your job in the process. Prepare, test, test, test and present.A +1 on this.
Tread lightly, especially as the new guy. Your comments about the "way they do things" can be easily misunderstood especially if you haven't been asked to fix the issues you are referring to.
Once you've done as has been suggested, carefully choose your moment to share your findings.
+1 here... Consider taking things in small bites. Find a process that seems to be taking a long time to process. Examine the tables, keys, indexes and code and try to apply best practices in tuning it. Support your changes with good ole MSDN or TechNet articles. Then demonstrate the differences. Don't try to over sell your point. If I were doing it then I'd suggest making a "small change" and monitor it over time. Then I'd suggest looking at other "small changes"..... small steps at a time may convince the masses that there are better ways to design things.
Best of luck.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 29, 2013 at 2:16 pm
Thanks for everyone take time to write a comment.
Things are a bit worse:
1. The guys enforced this police are the architects/bosses/co owners of the corporation, to steps above me in hierarchy.
2. There are hundreds of developers and just a couple of DBAs (and they are more likely to nod to anything boss say).
3. Since I'm not tasked to do it I cannot even see the real thing running, all I can do is to ask, nicely, with sugar on top, extra sugar on top.
Or not:
1. At least the bosses appears to be smart/reasonable ones (I did make mistakes before).
2. I'm pretty sure they dont created indexes for that FKs and tryied to do many levels cascade operations forcing a lot off full table scans, also the nolock use can be duo to the heavy table splits caused by that PK GUID (inserting causing entire page locks and forcing the use of uncommited reads to speed up things).
3. Since my background in DB I'm tasked to model a "module" and off course I'll use FKs.
Please keeping posting advices.
October 30, 2013 at 5:49 am
jcb (10/29/2013)
Thanks for everyone take time to write a comment.Things are a bit worse:
1. The guys enforced this police are the architects/bosses/co owners of the corporation, to steps above me in hierarchy.
2. There are hundreds of developers and just a couple of DBAs (and they are more likely to nod to anything boss say).
3. Since I'm not tasked to do it I cannot even see the real thing running, all I can do is to ask, nicely, with sugar on top, extra sugar on top.
Or not:
1. At least the bosses appears to be smart/reasonable ones (I did make mistakes before).
2. I'm pretty sure they dont created indexes for that FKs and tryied to do many levels cascade operations forcing a lot off full table scans, also the nolock use can be duo to the heavy table splits caused by that PK GUID (inserting causing entire page locks and forcing the use of uncommited reads to speed up things).
3. Since my background in DB I'm tasked to model a "module" and off course I'll use FKs.
Please keeping posting advices.
Yeah...I'd say that qualifies as being "a bit worse" than you originally thought. It sounds like you have your work cut out for you and I would proceed with caution. Generally, a new guy who hasn't proven himself at all isn't going to be allowed to come in and redesign a complex system. Like Kurt said, take things in small bites. You could try asking if performance is an issue. If they respond that it is, ask if you can look for performance tuning opportunities. I strongly suspect that creating clustered and nonclustered indexes on key fields will give you a lot of bang for your buck and start to eliminate full table scans.
Accuracy is another avenue of approach. Like Sean said above, the lack of foreign keys may have resulted in orphaned child records and duplicate data, which are simple to prove.
Another approach is simply to do what you've been tasked with and lead by example. Make sure it performs well by using good design and implementation. When people see it working well, they'll ask about it, so make sure you can explain everything in detail. That may lead to looking at other areas of the system. Remember that things take time.
October 31, 2013 at 2:38 am
As well as the above, See if you can get a "clean" development VM to work on without the shed loads of other databases.
And keep looking for other jobs! :crazy:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply