Post reply

What do you do with a new SQL Server?

  • June 11, 2008 at 10:10 pm

    #183423

    Comments posted to this topic are about the item What do you do with a new SQL Server?

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • June 11, 2008 at 10:11 pm

    #827638

    Nice, clear, well written article with good advice. Thanks!

    --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)

  • June 11, 2008 at 10:21 pm

    #827639

    Well written and well segmented document.....

  • June 12, 2008 at 3:21 am

    #827714

    Yes, I agree, good concise advice.

    Just one quick question - you mention the Fill Factor defaults to 100% in 2005, whereas it was 90% in 2000. [To be precise, it defaults to zero, which is the same as 100%]

    Would you suggest changing the default Fill Factor to 90%? Or to some other value? Or leave the system-wide default and change the fill factor on a per-index basis?

    Andy

  • June 12, 2008 at 3:57 am

    #827725

    Nice article - I voted 5 🙂

    A few things I'dd add:

    (1) check the server collation. Where I currently am DBA I have to standardise the collations across 20 servers because previous DBAs didn't see a difference between SQL_Latin1_General_CP1_CI_AS and

    Latin1_General_CI_AS :(. This is best dealt with sooner rather than later to avoid the pain of changing all the column collations, checking code, constraints etc.

    (2) Check that the correct version of SQL Server is being used. On some of my systems consultants have installed SQL 2005 Enterprise Edition when Standard will do fine - unlike SQL 2000 the general functionality is much closer to Enterprise and there is a difference of £10k or so per processor.

    (2) Check the physical memory and if AWE is enabled if necessary (and change Boot.ini etc).

    (3) Check the audit of licenses and if multiple procs are being taken account of in a multi-core machine.

    (4) Check that the master key is being backed up if it is used.

    (5) BUILTIN\Administrators - policy for enabling/disabling exists?

    (6) Who is in the sysadmin role and are they supposed to be there (I have inherited several application users in the sysadmin role!).

    Probably other checks I've forgotten but this is all I can think of right now. Personally I poll the servers for most of this type of info and collect it centrally, and fortunately our life for this sort of administration will become easier with the Policy based Management in SQL 2008. Anyway, thanks again for the useful article.

    Paul Ibison

  • June 12, 2008 at 4:05 am

    #827726

    Hi Carolyn! This is a very helpful article, thanks. We are upgrading to SQL 2005 this week so this article will prove very useful to double check we haven't missed anything. Hope all is well with you.

    Kate (from just one of the many companies Carolyn has helped improve in the past)

  • June 12, 2008 at 4:30 am

    #827735

    AndyD

    Default fill factors at 0% is fine if you have high spec’d servers and small databases that are not heavily used. In these cases also 90% won’t make a great deal of difference either. What you want to default it to is your call based on the use of the database – heavy reads say leave at 0% (100%) / heavy writes say adjust to 90% - but what do I know!!!!. The default will only be used on new table creation anyway.

    Fill factors are not a precise science, I would start monitoring use and adjust the fill factors of the most heavily used and fragmenting tables.

    Paul

    More for my list thanks. Collations can always be an issue in the UK, they’ve been the bane of my life on several occasions in the past.

    Kate

    Nice to here from you, I’ve sent you a private message.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • June 12, 2008 at 6:16 am

    #827780

    Nice article. Very direct and to the point.

    I'd consider a different title, tho, because it's always good to review your backup procedures.

  • June 12, 2008 at 6:26 am

    #827785

    AndyD (6/12/2008)

    Yes, I agree, good concise advice.

    Just one quick question - you mention the Fill Factor defaults to 100% in 2005, whereas it was 90% in 2000. [To be precise, it defaults to zero, which is the same as 100%]

    Would you suggest changing the default Fill Factor to 90%? Or to some other value? Or leave the system-wide default and change the fill factor on a per-index basis?

    Andy

    I remember seeing an article on the difference between 0% and 100% fill factors... Dunno if I can find it, again, but I'll look...

    --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)

  • June 12, 2008 at 6:37 am

    #827792

    Jeff Moden (6/12/2008)

    I remember seeing an article on the difference between 0% and 100% fill factors... Dunno if I can find it, again, but I'll look...

    I was only regurgitating what is stated in BOL... from my point of view it seems strange that 0% and 100% mean the same thing :hehe:

  • June 12, 2008 at 6:43 am

    #827795

    AndyD (6/12/2008)

    Jeff Moden (6/12/2008)

    I remember seeing an article on the difference between 0% and 100% fill factors... Dunno if I can find it, again, but I'll look...

    I was only regurgitating what is stated in BOL... from my point of view it seems strange that 0% and 100% mean the same thing :hehe:

    Oh no... sorry. It wasn't a challenge... I agree that's what BOL says... dang, I gotta find that article.

    --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)

  • June 12, 2008 at 6:54 am

    #827800

    I found it... them, really.

    http://www.sqlmag.com/Articles/ArticleID/19851/19851/pg/2/2.html?Ad=1

    http://www.sqlmag.com/Article/ArticleID/20242/sql_server_20242.html

    I'm not a "member" of that particular site so I can't see the whole article. What I glean from the two articles is that there used to be a difference in the early days and that there's really no difference now.

    What is interesting is, if you scroll down in the 2nd article, you'll find that "PAD INDEX" supposedly makes the 0% and 100% fills even "tighter"... seems like they both leave 1 extra row without it.

    --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)

  • June 12, 2008 at 7:10 am

    #827812

    Perhaps the esteemed Mr Phil Factor would like to get involved in the discussion?

  • June 12, 2008 at 8:54 am

    #827921

    I set up the alerts on a test system and have database mail enabled with a valid profile (that I can send a test email from). I added the notification to the alerts to use a operator that used this DBMail profile. I generate a test deadlock but the alert never fires and yes the deadlock does happen:

    Msg 1205, Level 13, State 45, Line 4

    Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    What am I missing here? Alert set up, dbmail set up, operator set up. Alert setup for deadlock as recommended to use the operator that uses dbmail.

  • June 12, 2008 at 9:01 am

    #827924

    Check checking did you enable the profile you created as per the article? Did the alert fire into the event log? do any of the other events fire?

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

Viewing 15 posts - 1 through 15 (of 26 total)

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