SQL server 2008/2005 Tuning

I am having a look at SQL server parameters which I can tune to optimize database and not having much luck.

When I execute
SELECT * FROM sys.configurations
ORDER BY name ;
GO

I see list of 70 parameters but is that all we have?
Which config parameters one should look at for configuring a production server of database 150GB and between 250-500 concurrent connections?

Regards
SQlServer-Tuning-Parametres.png
crazywolf2010Asked:
Who is Participating?
 
David ToddSenior DBACommented:
Hi,

I would advise against directly editing/altering that table.

sp_configure is the system stored procedure to do what you are after.

Now the second part of the question - Which parameters ...

Fillfactor and max degree of parallelism, and maybe max Server Memory and min Server Memory.

Other options tend to a be fairly well set anyway, and b turn things off or on - if you don't use SQL Mail there is nothing to be gained by turning it on.

OTOH there is more to tuning a server than the configuration options. How the disks are created/formatted, where the individual data files are located etc.

That is, is the OS on its own drive, system databases on their own drive, tempdb on its own drives with the lgo on a different drive to the data file, user databases on one drive, user database log files on another, backups on yet another drive ... data drives formatted with a 64k allocation unit, drives are no more than 80% full ... maybe different user databases on different drives to reduce io contention. Maybe think about key files being on SSD's instead of regular hard disks.

HTH
  David
0
 
crazywolf2010Author Commented:
Hi David,
Is that all I need to do for memory or database settings?
I was expecting possibly 10s of parameters which I do on Oracle databases everyday.

Also are these parameters applicable to all databases on instance/server or they could be set on each SQL database level ?

Regards
0
 
David ToddSenior DBACommented:
Hi,

The ones I showed are server level.

Generally if the server is installed, SQL is pretty good without a lot of tuning.

What are you trying to achieve? If you can be more specific then maybe my answers can be more accurate.

Regards
  David

PS As an Oracle DBA you'll know about setting up disks ...

Fillfactor can be set per clustered index, or it uses the server default. If the table is using a common sql practice of say TableID int identity then anything other than fillfactor 100 is wasting space, as inserts are going to the end of the index - logically appends not inserts.

The memory depends on what else is on the box - If there is JUST this SQL instance then its almost not worth touching. Otherwise the min parameter makes sure that SQL gets some ram, and max makes sure it doesn't get overly greedy and kill the box.

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
jogosCommented:
On the long list of dtodd I will add some more
multiple files in tempdb (http://www.mssqltips.com/sqlservertip/1980/sql-server-tempdb-one-or-multiple-data-files/), (filtered) indexes, partioned tables, statistics.

Following the hartbeat of your system and gradualy know where there could be improvement you do by loocking at your DMV's (dynamic management views)
http://www.mssqltips.com/sql-server-tip-category/31/dynamic-management-views-and-functions/ 
There's a hands-on book about DMV's http://www.manning.com/stirk/ focusing on monitoring and finding problems with query execution's.
0
 
LowfatspreadCommented:
@dtodd


Fillfactor can be set per clustered index, or it uses the server default. If the table is using a common sql practice of say TableID int identity then anything other than fillfactor 100 is wasting space, as inserts are going to the end of the index - logically appends not inserts.


depends on amount of update activity surely...
0
 
Scott PletcherSenior DBACommented:
>> If the table is using a common sql practice of say TableID int identity then anything other than fillfactor 100 is wasting space <<
>> depends on amount of update activity surely... <<

I don't think it depends on the amount of UPDATE activity as much as on the types of UPDATE done.

Particularly if you commonly increase the length of varchar columns, you will definitely want to leave freespace on the page.  [In fact, if you know you will do that, it can make sense to "pad" the varchar column with a dummy value prior to UPDATE so that the column does not expand when UPDATEd.]

A fillfactor of 95-99, depending on the types of UPDATEs, will still pack your data tightly but not risk too many page moves/splits, which can really hurt overall performance.
0
 
LowfatspreadCommented:
thanks Scott, that was then intention of my point...

or potentially giving values to columns which where previously null.

0
 
Scott PletcherSenior DBACommented:
>> or potentially giving values to columns which where previously null. <<

Again, you mean to varchar columns that were previously null, right?

Which would increase their length, yes :-).  And so fall under what I said before.

As I understand it, a fixed-length column, say an int, still takes 4 bytes whether it is null or not.
0
 
LowfatspreadCommented:
once again yes... thanks scott.

but now you've got me thinking of "text in row"/pointers  and wondering where the break even points occur...
can't give that proper attention now will leave that for after the xmas pud...

0
 
David ToddSenior DBACommented:
Hi Scott and Lowfat,

Good point about the varchar updates. I was thinking more of a standard transaction table, wich almost never has updates, and no varchar columns.

Regards
  David
0
 
Scott PletcherSenior DBACommented:
Naturally the same applies to nvarchar.  (And varbinary, for those that use it.)

Even [n]varchar(max) can cause a split/move, depending on the size specified to be stored in-row.
0
 
crazywolf2010Author Commented:
Question not asnwered to the point
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.