Post reply

Foreign Keys

  • August 3, 2018 at 9:32 am

    #2000185

    HappyGeek - Friday, August 3, 2018 8:51 AM

    Jeff Moden - Friday, August 3, 2018 8:41 AM

    HappyGeek - Friday, August 3, 2018 8:35 AM

    Grant Fritchey - Friday, August 3, 2018 5:05 AM

    HappyGeek - Thursday, August 2, 2018 10:18 PM

    Many thanks to everyone for your valued contribution, the general opinion is aligned to what I think. However here is my situation; I have inherited an environment in which about a third of all tables have a primary key and no clustered index; the remainder have a primary key and clustered index. Of that third that do not; they have a primary key on an identity column, that identity column has a non clustered index on it which is then used as a foreign key, performance is suffering badly. It would seem sensible, without having access to any other data, to convert the unique non clustered index to a clustered index and yes I understand that this is not an ideal situation given the comments above, any thoughts on this scenario? Again many thanks for your time so far;

    I lean very heavily towards having all the tables have a clustered index and yeah, probably on the primary key. However, a blanket recommendation of just scripting a mass conversion on those lines would be dangerous in my opinion. We joke, but "it depends" really is a very common answer. I'd want to have more data to ensure that I was making the right decision. Have you looked at wait stats, execution plans, most frequently called queries, longest running queries, queries using the most resources. Ensure that you know where the slow performance is coming from absolutely, not just see a nominally questionable practice and aim to undo it. That's my advice. Measure twice, cut once.

    Many thanks Grant but when I say I have no other data, I mean exactly that, I have no access to the server, no access to the SQL Instance, but can wander into the kitchen unaccompanied, if I could look at stats and other metrics it would be great!!

    OK... I'm seriously confused... If you have "no access to the server", how were you intending to make changes to the indexes?

    Lol, not as confused as I am, told to sort the server and all I have to work with is an outdated copy of a dev version of a database, I can only advise on what may be pressure points from the database design point of view!! Strange job this :hehe::blink::blush:

    That is even worst - many times the dev copies are not a true image of the prod version with respect to indexes and even columns - if insisting that access to the prod server is not an option at least get their DBA to script (full script, not the defaults that SSMS has) the db in question and I would also request that dumps of particular dmv's are taken every day (2 -3 times a day if possible) with the likes of query plans, index stats, query stats, missing indexes and so on. Or if they have a monitoring tool in place get access to it.

    Anything you do like this may not even be what is required - a "missing" clustered index may be exist on the prod version.

  • August 3, 2018 at 9:46 am

    #2000190

    HappyGeek - Friday, August 3, 2018 8:51 AM

    Jeff Moden - Friday, August 3, 2018 8:41 AM

    HappyGeek - Friday, August 3, 2018 8:35 AM

    Grant Fritchey - Friday, August 3, 2018 5:05 AM

    HappyGeek - Thursday, August 2, 2018 10:18 PM

    Many thanks to everyone for your valued contribution, the general opinion is aligned to what I think. However here is my situation; I have inherited an environment in which about a third of all tables have a primary key and no clustered index; the remainder have a primary key and clustered index. Of that third that do not; they have a primary key on an identity column, that identity column has a non clustered index on it which is then used as a foreign key, performance is suffering badly. It would seem sensible, without having access to any other data, to convert the unique non clustered index to a clustered index and yes I understand that this is not an ideal situation given the comments above, any thoughts on this scenario? Again many thanks for your time so far;

    I lean very heavily towards having all the tables have a clustered index and yeah, probably on the primary key. However, a blanket recommendation of just scripting a mass conversion on those lines would be dangerous in my opinion. We joke, but "it depends" really is a very common answer. I'd want to have more data to ensure that I was making the right decision. Have you looked at wait stats, execution plans, most frequently called queries, longest running queries, queries using the most resources. Ensure that you know where the slow performance is coming from absolutely, not just see a nominally questionable practice and aim to undo it. That's my advice. Measure twice, cut once.

    Many thanks Grant but when I say I have no other data, I mean exactly that, I have no access to the server, no access to the SQL Instance, but can wander into the kitchen unaccompanied, if I could look at stats and other metrics it would be great!!

    OK... I'm seriously confused... If you have "no access to the server", how were you intending to make changes to the indexes?

    Lol, not as confused as I am, told to sort the server and all I have to work with is an outdated copy of a dev version of a database, I can only advise on what may be pressure points from the database design point of view!! Strange job this :hehe::blink::blush:

    Oh my... I do feel for you.  This is like being told to fix someone's car and they say you can't access it but here's the "HowWheels" version of the car.

    Step one for them would be to get a copy of Adam Machanic's sp_WhoIsActive, learn how to use it to identify the pain points, and then go from there.  I'd also recommend getting copies of some of Brent Ozar's "sp_Blitz" series of code helpers and see what the status of the server and indexes actually is.

    Last but not least, they need to provide you with a recent restore of the production database where you can do some serious analysis.  What would be better than that is to provide you with some "trust" and let you have enough access to the production box to examine some of the problems.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 4, 2018 at 1:43 am

    #2000254

    Grant, Jeff and  all of you thanks for the advice and support, my tools of choice happen to be sp_whoisactive from Adam Mechanic and Brent's sp_Blitz and sp_BlitxCache, hopefully this week someone will listen to reason; I think  this question was born out of sheer exasperation and frustration, I would not really make wholesale arbitrary changes without having more data. Perhaps I should have asked "would you walk away from this job?" :hehe:

    ...

  • August 4, 2018 at 9:53 am

    #2000265

    HappyGeek - Saturday, August 4, 2018 1:43 AM

    Grant, Jeff and  all of you thanks for the advice and support, my tools of choice happen to be sp_whoisactive from Adam Mechanic and Brent's sp_Blitz and sp_BlitxCache, hopefully this week someone will listen to reason; I think  this question was born out of sheer exasperation and frustration, I would not really make wholesale arbitrary changes without having more data. Perhaps I should have asked "would you walk away from this job?" :hehe:

    Heh... no.  Wouldn't walk away.  Maybe run if you can't get people to provide you with the tools and privs you need to actually do the job they're asking you to do. 😀

    On the other hand (and this would probably be the way I'd go), I'd apply some patience along with a bit of constant pressure.  As you know, it's not possible to solve production performance problems unless you have a better environment to test, not to mention a list of the problems at the code level that they're actually having problems with.  If you're fairly new to the company, it does take some time to build the necessary trust.  I know I wouldn't give a newbie to the company even access to the prod box never mind the privs that it would take to effectively troubleshoot things until I've tested their metal in lesser environments.

    Figure out what that test should be, demonstrate "Before'n'After" results, and then tell them how much better you could do if you did have either a more representative test environment or actual access to prod.  You've got to earn that "first stripe" of trust the hard way but it's usually well worth the effort.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 4, 2018 at 11:59 am

    #2000278

    HappyGeek - Saturday, August 4, 2018 1:43 AM

    Grant, Jeff and  all of you thanks for the advice and support, my tools of choice happen to be sp_whoisactive from Adam Mechanic and Brent's sp_Blitz and sp_BlitxCache, hopefully this week someone will listen to reason; I think  this question was born out of sheer exasperation and frustration, I would not really make wholesale arbitrary changes without having more data. Perhaps I should have asked "would you walk away from this job?" :hehe:

    I'd sure consider it if I couldn't start to influence things in a positive direction. Jeff is right (which is like saying water is wet), if this is a new situation, patience is key. You can work them into trusting you. And if you can't...

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply