Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

What do you do with a new SQL Server? Expand / Collapse
Author
Message
Posted Wednesday, June 11, 2008 10:10 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 1,458, Visits: 3,005
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
Post #515640
Posted Wednesday, June 11, 2008 10:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:06 PM
Points: 35,218, Visits: 31,678
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #515642
Posted Wednesday, June 11, 2008 10:21 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,371, Visits: 1,391
Well written and well segmented document.....


Post #515643
Posted Thursday, June 12, 2008 3:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:57 AM
Points: 1,050, Visits: 1,119
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
Post #515740
Posted Thursday, June 12, 2008 3:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 8, 2013 5:26 AM
Points: 14, Visits: 144
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
Post #515752
Posted Thursday, June 12, 2008 4:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:44 AM
Points: 52, Visits: 686
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)
Post #515754
Posted Thursday, June 12, 2008 4:30 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 1,458, Visits: 3,005
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
Post #515766
Posted Thursday, June 12, 2008 6:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 31, 2008 9:43 AM
Points: 8, Visits: 25
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.
Post #515830
Posted Thursday, June 12, 2008 6:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:06 PM
Points: 35,218, Visits: 31,678
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #515836
Posted Thursday, June 12, 2008 6:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:57 AM
Points: 1,050, Visits: 1,119
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
Post #515844
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse