August 30, 2016 at 4:39 pm
SQL Server 2014, 8 CPUs, degree of parallelism 16, 256 GB of RAM... This is a physical box, not on a SAN.
I work for a small company that uses quite a few vendor software products and am in the middle of an upgrade with Sunguard that involves moving from Informix to SQL Server. The Sunguard folks have set up the SQL Server, though I don't know under whose specs it was purchased.
I got my first look at it yesterday and it has a single, 1.63T drive, RAID 10 with 6 drives. Zero partitions. All the databases that have been migrated from Informix are in the Primary file group. It's configured to have TempDB, Logs, etc. all there in Primary as well.
Has anybody worked with a machine set up this way? This box will host business critical databases, accounting, financial, payroll, etc. I just found out that there is a vague idea that it will be clustered and there is another identical machine still in the box for that purpose. The in-house practice is to use Networker to copy backup files to tape (that doesn't seem useful if there is an emergency).
What do I need to know to make rational decisions bout this? Should I be worried? Fragmentation? Corrupted data files? I'm imagining all kinds of potential monsters but are they real?
Advice from anyone who has tried this sort of set up would be appreciated. Thanks!
August 30, 2016 at 6:01 pm
I'm sure others will give more feedback.
from my point of view having data and logs on the same drive are not necessarily bad - all depends on the workload.
If nothing else I would do the following
-Change maxdop to 8
- ensure maxmemory is set
- ensure Instant File Initialization is enabled
-Ensure that cost threshold for parallelism is not set to the default value of 5 – something around 50 is normally better but final value should be tested
-Buy a set of SSD drives and move the tempdb to those
- ensure tempdb has at least 4 files, 8 probably better on that machine spec
-See which trace flags are enabled
-Myself I normally set the following ones instance wide
---1117 - tempdb
---1118 - tempdb
---1222 – lock information
---3226 – remove backup successful info message
---4199 – enable all fixes
August 31, 2016 at 1:29 am
Take a look at the wait statistics. Understand what is causing the server to run slow, if it is. It could be fine, but you'll only know by monitoring.
I would worry a lot about backups. Get together with the business to define the Recovery Point Objective and the Recovery Time Objective. RPO represents the amount of data that could be lost. It helps you define the frequency of full, differential and log backups. RTO determines the amount of downtime you're allowed. I would always get these in writing with full business buy-in so that everyone understands what you can deliver in terms of recovery. That's pretty important.
"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
August 31, 2016 at 6:53 am
Fly Girl (8/30/2016)
I got my first look at it yesterday and it has a single, 1.63T drive, RAID 10 with 6 drives. Zero partitions. All the databases that have been migrated from Informix are in the Primary file group. It's configured to have TempDB, Logs, etc. all there in Primary as well.
if all files are located on the same physical array then performance could well suffer. To rely on the fault tolerance of the RAID10 array wouldn't be my ideal
Fly Girl (8/30/2016)
This box will host business critical databases, accounting, financial, payroll, etc.
not an ideal configuration imho
Fly Girl (8/30/2016)
I just found out that there is a vague idea that it will be clustered and there is another identical machine still in the box for that purpose.
With the lack of shared storage it sounds like the plan is for an Availability Group rather than a Failover Cluster Instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply