SQL 2005 Setup Configuration - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL 2005 Setup Configuration

Author

Topic

mse-ron
Starting Member

34 Posts

Posted - 2007-10-05 : 03:13:48

I'm a Network Administrator that has to do everything from desktop to server, to routing, and DBA so I need a bit of advice. Stretched thin for time and knowledge in the SQL area and trying to do the best I can.

I currently have a MS SQL 2000 database that is 60GB in size for MS Dynamics 8.0 and one that is 2GB that I want to move to a new server.

I've setup and tested it and it all works, now I'm looking for some Best Practices information for best performance. Currently doing about 3,000 transactions a day.

Server is HP ML350-G5 2 Xeon Dual Core 2.0GHz CPUs w/16GB RAM w/external drive bays running 72GB 15K SAS drives with SMART ARRAY P800.
OS is Windows Server 2003 Enterprise w/SP2 - MS SQL 2005 Standard
I have the following drive configurations setup.

C:(RAID1+0)\OS - SQL 2005
D:(RAID1+0)\Dynamics Application \ Shared dictionary files for clients
G:(RAID0)\BACKUPS (see note below about TEMPDB) I also have a 25GB swap file on this drive maybe better to put in on the D: volume if I put TEMPDB here
H:(RAID1+0)\LOGS (2 user db logs and the tempdb log)
I:(RAID1+0)\DATA (2 user db - [model, msdb, master]

Recently found that my G: drive (RAID0) only has space for 2 backups (poor space planning on my part) before I need to move the files. Normally I backup the DB with BackupExec 10.1d just fine in about 2 hours over the wire at night since no one is in the DB at night.
Was wondering if putting my TEMPDB there on (RAID0) would be advisable or not. In SQL 2005 it gets rebuilt each time SQL is restarted so it would seem that ensuring it's on a RAID1 or RAID1+0 might not be as necessary as say the DATA files.
Then if I really wanted to I could actually do backups on the I:\DATA drive at night and then use TAPE to back that up, and use an automated script to move them off to offline storage once the TAPE backup is done.

For the [model, msdb, master] should they need to be on the I:\DATA drive or just leave them on C:\OS ? Not sure how much they're actually used.

Also doing some brief testing the Microsoft Dynamics was much slower on the new Server than it was on the original. Odd to me since the new Server is like a McLaren F1 racing a Ford Pinto. The old server is running a single P4 CPU and 6GB RAM

I've set MS SQL 2005 to use 12GB of RAM on the new server so as to leave 4GB for other Server tasks.

I ran the Maintenance Wizard and had it do checks and defrag and index rebuild etc... so not really sure why the speed would be noticeably slower. One thing I did notice is that once a screen query was ran then the next time it was a bit faster but overall I was expecting to see the screens fly by on this new hardware and SQL 2005 but it doesn't really do that.

I've read some of the white papers etc for tuning, but wow I just don't have unlimited hours to do all this analysis, there seem to be hundreds of pages to read on that type of stuff.

Thanks in advance for the advice.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 13:48:14

Databases should not be placed on the system drive, regardless if they are the system databases. Here's how to move them:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k

Your post is a bit long and doesn't include questions, could you point out what questions you have?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mse-ron
Starting Member

34 Posts

Posted - 2007-10-05 : 14:03:57

quote:
Originally posted by tkizer
Your post is a bit long and doesn't include questions, could you point out what questions you have?

It's long to give details up front instead of being asked details one by one. I'm sorry if the questions are not clear and pointed out.

1. Should system db be on OS drive [you answered]
2. Should TEMPDB be placed on a RAID0 drive
3. Speed is SLOW compared to SQL2000 on older hardware - any advise of obvious things to check for not listed above.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 14:08:59

2. I don't have the different raids memorized as other do here, so I'll leave that one to rmiao, eyechart, etc...
3. Run this to see how much index fragmentation you have:


SELECT 
    [object_id] AS ObjectId, 
    index_id AS IndexId, 
    avg_fragmentation_in_percent AS FragPercent, 
    record_count AS RecordCount, 
    partition_number AS PartitionNumber,
    index_type_desc AS IndexType,
    alloc_unit_type_desc AS AllocUnitType
FROM sys.dm_db_index_physical_stats (DB_ID('DatabaseNameGoesHere'), NULL, NULL, NULL, 'SAMPLED')
WHERE 
    avg_fragmentation_in_percent > 10 AND 
    index_id > 0
ORDER BY ObjectId

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mse-ron
Starting Member

34 Posts

Posted - 2007-10-05 : 14:28:02

Okay I'll run it as is and save as information. Then move the databases and run it again later tonight.

As for question #2
Main difference is that RAID0 has no recovery, ie. if you lose one of the drives you lose all data. On a RAID1 if you lose a drive you can still recover by replacing the drive.
RAID1 is also slower at WRITES than RAID0

Unless someone jumps in and says woah! I'm going to proceed and place TEMPDB on the RAID0 drive.

I'll get back with you later on about the testing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 14:35:06

RAID0 sounds perfect for tempdb as you shouldn't be storing anything in there anyway. Tempdb gets recreated using the model database as a template each time the SQL Server service starts up.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mse-ron
Starting Member

34 Posts

Posted - 2007-10-05 : 15:59:51

Yes I read that, that's why I was suggesting putting it there just wanted confirmation from someone with more experience than myself.

Okay I ran the script you provided and it returned 204 rows with most heavily fragmented.
I need to make all the DB/LOG location changes and then I'll also run a maintenance defrag/index etc and then run your script again.

If the GUI Wizard does not improve it then I'm not really sure how to properly re-index it.

I won't post all 204 lines but here is an example.

quote:
7111216 1 31.8181818181818 799 1 CLUSTERED INDEX IN_ROW_DATA
7111216 2 87.5 799 1 NONCLUSTERED INDEX IN_ROW_DATA
7111216 3 75 799 1 NONCLUSTERED INDEX IN_ROW_DATA
7111216 4 87.5 799 1 NONCLUSTERED INDEX IN_ROW_DATA
7111216 5 87.5 799 1 NONCLUSTERED INDEX IN_ROW_DATA
7111216 6 75 799 1 NONCLUSTERED INDEX IN_ROW_DATA
7111216 7 87.5 799 1 NONCLUSTERED INDEX IN_ROW_DATA
7111216 8 87.5 799 1 NONCLUSTERED INDEX IN_ROW_DATA
7111216 9 87.5 799 1 NONCLUSTERED INDEX IN_ROW_DATA
7111216 10 87.5 799 1 NONCLUSTERED INDEX IN_ROW_DATA
7111216 11 87.5 799 1 NONCLUSTERED INDEX IN_ROW_DATA
9428199 1 50 160 1 CLUSTERED INDEX IN_ROW_DATA
82099333 2 50 368 1 NONCLUSTERED INDEX IN_ROW_DATA
82099333 3 50 368 1 NONCLUSTERED INDEX IN_ROW_DATA
82099333 4 50 368 1 NONCLUSTERED INDEX IN_ROW_DATA
87215611 2 85.7142857142857 2379 1 NONCLUSTERED INDEX IN_ROW_DATA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 16:01:59

You can use my script instead of the maintenance routines.

Here's ALTER INDEX:
http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx

And here's the rest of my replacements for the maintenance wizard:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mse-ron
Starting Member

34 Posts

Posted - 2007-10-05 : 16:25:21

Sorry if these are basic questions but I'm not a DBA I just have the job of one.
Also, can this be run from a normal Query window?

1. Can you please explain the difference and which would typically be used.
@statsMode - LIMITED, SAMPLED or DETAILED

2. Which would you recommend since this is a new move and I will do it before the users start using it.
@defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)

3. Not sure what I should use here:
@minFragPercent - minimum fragmentation level
@maxFragPercent - maximum fragmentation level
@minRowCount - minimum row count

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 16:32:10

1. You can check out the details in BOL for that DMV that I am using. I'd recommend SAMPLED due to your database size. LIMITED doesn't give us row count, which we'll probably need to exclude small tables.
2. I recommend REBUILD for a new database.
3. I'd use 10,100,1000 to start with.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mse-ron
Starting Member

34 Posts

Posted - 2007-10-05 : 16:45:48

Thanks.

One more question since I'm going to be moving tempdb

Default startup size is 8MB
My tempdb size after running for a couple weeks of testing and backups was at about 2GB

Should I set the default size to about 2GB or would that potentially cause thrashing or overhead? Most documents caution against just allowing auto-resize to create the right size as all other operations will suffer during the resize.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 16:53:47

I've never bothered with changing it, but I suppose you could test what works best for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mse-ron
Starting Member

34 Posts

Posted - 2007-10-05 : 20:59:37

Okay well finally got some time and moved all the database Files
Couldn't find much about the tempdb MDF and LDF files as far as size ratios
so I set what appears to be adequate from the previous size they grew to.

G:\MSSQL\DATA\tempdb.mdf (default size is now 2,048MB = 2GB)
H:\MSSQL\LOGS\templog.ldf (default size is now 500MB)

G: is a RAID0 (fast reads/writes)
H: is a RAID1+0 (fast reads / slower writes but not as slow as RAID1)
[use of RAID1+0 or 10 as some think of it is recommended for high end DB use according to some sites)

I have to wait for the production database users to quit and then wait for the backup to finish. Should be
about 4 more hours or so, then I can copy the 2 production databases over to my new server and run
your script to check fragmentation level, then run your other script to defragment it and then retest
again for level of fragmentation.

Thanks again for your assistance with this task - greatly appreciated.

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 05:08:57

"Couldn't find much about the tempdb MDF and LDF files as far as size ratios"

I haven't tested this theory, but my opinion is:

Default 8MB start, increase by 10%, is a lot of extensions before you get to 2GB, which may result in a badly fragmented file. So I would reckon that initial size of 2GB, which will hopefully result in the file being 1one contiguous chunk, would be better.

I agree with RAID10 for database files.

The MDF and LDF files ought to be on separate channels, rather than sharing a single channel. (I think you have that from what you said above, so may be a NULL point

)

This is probably too late to be useful

but you can speed up the migration by:

Take full backup (users still online)
Restore onto new server with NORECOVERY option
Kick users off
Take differential backup (should be very small file)
Restore onto new server with RECOVERY option

There is a more detailed discussion here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Move%20database,Moving%20to%20a%20new%20sql%20server,Fix%20Orphaned%20Users

"Recently found that my G: drive (RAID0) only has space for 2 backups"

We have ALL our servers set to backup to Compressed folders. Some say this is a sin! we haven;t had any problems.

You could also look at Litespeed which (makes compressed backups, and is very highly regarded by DBAs of large databases [no personal experience])

As I understand it you are moving from SQL 2000 to SQL 2005. As such you should change the Compatibility (once restored on SQL 2005) from 80 to 90 - assuming you have tested that there are no application issues in doing this). You also need to rebuild all indexes / stats / etc. UNTIL you do that the performance will be DIRE! (Sounds like you did that, but if you are Compatibility=80 on SQL 2005 that is likely to result in dire performance too)

See : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Migrating+to+SQL+2005+Hints+and+Tips

If it is still slow a possible culprit is that you have implicit datatype conversions in JOINs / WHERE clause, which can cause poorly constructed query plans. This is something that changed with SQL2000 SP4 [as best as I understand it], so if you are coming from SQL2000 with a service pack EARLIER than SP4 that might be something to check.

Might also just be that you need to get the database more heavily used until the cache gets properly populated

Kristen

mse-ron
Starting Member

34 Posts

Posted - 2007-10-06 : 05:32:37

Thanks for the input Kristen

Yes I changed compatibility mode to 90 already.
Yes 2000 was SP4 already

Well I'm going to try the provided script and see if that builds all the indexes etc well.

This is a Microsoft financial application similar to SAP so nothing I can do about any internal code changes, I'm hoping the reindex script and or cache will correct it and increase the speed.

Thanks again for your confirmations

mse-ron
Starting Member

34 Posts

Posted - 2007-10-06 : 16:43:42

Tara,

I attempted to run your defrag script (uncommented the header portion where db name goes etc. Got this error, not sure how to correct.

quote:
Msg 111, Level 15, State 1, Procedure isp_ALTER_INDEX, Line 44
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Line 44 currently says:

IF @statsMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-06 : 18:49:10

It's a problem with how you ran it and not the actual code. Could you post exactly what you are executing at the time of the error, the entire portion that is in the window or what is highlighted?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mse-ron
Starting Member

34 Posts

Posted - 2007-10-07 : 00:10:56

Locally logged into computer, ran SQL Studio and
I opened a query window, pasted your code, modified to remove the remark /* */, input my db name.

Then hit F5 while on master (but code says to work on my MSE database)

How am I supposed to run it?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-07 : 01:28:42

Choose your db from drop down list and try again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-07 : 15:12:01

We'd need to see exactly what was on the screen when you hit F5 and got the error.

Changing the database won't change the error. You are probably just missing a GO statement before the CREATE PROC.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mse-ron
Starting Member

34 Posts

Posted - 2007-10-07 : 15:25:09

Well as said I'm connected via "Microsoft SQL Server Management Studio" for SQL 2005

In a query window here is a portion of your script Tara.

-------------------------------------------------------------------------------------------
-- OBJECT NAME           : isp_ALTER_INDEX
--
-- AUTHOR                : Tara Kizer
--
-- DATE                  : February 27, 2007
--
-- INPUTS                : @dbName         - name of the database
--                         @statsMode      - LIMITED, SAMPLED or DETAILED
--                         @defragType     - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
--                         @minFragPercent - minimum fragmentation level
--                         @maxFragPercent - maximum fragmentation level
--                         @minRowCount    - minimum row count
--
-- OUTPUTS               : None
--
-- DEPENDENCIES          : sys.dm_db_index_physical_stats, sys.objects, 
--                         sys.schemas, sys.indexes, sys.partitions
--
-- DESCRIPTION           : Defragments indexes

                           EXEC isp_ALTER_INDEX 
                            @dbName = 'mse', 
                            @statsMode = 'SAMPLED', 
                            @defragType = 'REBUILD', 
                            @minFragPercent = 10,
                            @maxFragPercent = 100,
                            @minRowCount = 1000

-------------------------------------------------------------------------------------------
CREATE PROC [dbo].[isp_ALTER_INDEX]
(
    @dbName sysname, 
    @statsMode varchar(8) = 'SAMPLED', 
    @defragType varchar(10) = 'REORGANIZE', 
    @minFragPercent int = 25, 
    @maxFragPercent int = 100, 
    @minRowCount int = 0
)
AS

SET NOCOUNT ON

IF @statsMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED')
BEGIN
    RAISERROR('@statsMode must be LIMITED, SAMPLED or DETAILED', 16, 1)
    RETURN
END

IF @defragType NOT IN ('REORGANIZE', 'REBUILD')
BEGIN

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources