August 11, 2005 at 4:56 am
We have SQL Server 2K standard ed on a quad cpu and 3gb RAM. 2GB assigned for SQL and 1 for O/S. About 45 different databases. We also run other applications and third party rdbms on the same machine. I find the CPU usage is around 45% and SQL memory 1.77GB. Extensive paging - pages/sec often seems to be above 80%. Log Cache hit ratio around 52%. I get complaints from users for slow query output and sometime timeout error. Timeout setting is unlimited both in server and client. Enough space on all dbs and enough indexes (clustered and non-clustered). integrity checks and optimization is done on nightly job. Can someone tell me what is the best practice? Whether SQL Server should be run on a dedicated server. What would be the impact with current setup.
Much appreciated.
August 11, 2005 at 6:10 am
Yes get a dedicated SQL box.
Assuming you have Windows 2k advanced or 2k3 enterprise, and can run Enterprise edition of SQL, get more RAM (8Gb total, use AWE)
This is next bit is my own opinion:
Ensure your disks are Raid 1+0 and 15000 rpm.
Given your number of dbs, I'd go for one big array for SQL not separate logs etc.
Reason : 45 dbs have 45 log files which will not generate sequential write on the array.
^Yes you woudl have separate arrays for tempdb, log, data etc but with 45 dbs, I'd go for general best guess
More spindles in an array, the better.
Depending on your server, upgrade SCSI card to top line U320 with bags of cache
I know I'dd get flamed for that, but this is not a singel big DB situation.
Did I say, get a dedicated SQL box?
Cheers
Shawn
August 11, 2005 at 6:32 am
Oops! forgot to mention. We are using windows 2k advanced, in a SAN environment with SQL Server standard edition.
August 11, 2005 at 6:45 am
Great, SQL Enterpise and 8Gb RAM with AWE for starters then.
SAN, hhmm, not really worked with one yet.
I did research at my last job for a SAN and it all comes down to SAN architecture, because RAID 5 is not always RAID 5 on a SAN. Also, a dedicated set of spindles on the SAN will help.
And that's me out of SAN knowledge...sorry, Shawn
August 11, 2005 at 6:51 am
Thanks Shawn. Looks like I have a great job to convince my boss for $27000 x 4 (cpu) for SQL Server Enterprise.
August 11, 2005 at 6:59 am
A dedicated SQL box will help.
Off-hand I'd say your CPU usage may be a consequence of paging, which is generating IO, causing normal db IO to stall etc.
Without seeing the box, your issue is not enough RAM at least with possibly poor IO.
A dedicated SQL box with 2 CPUs and hods of RAM...?
Alos, IO contention on teh SAN coudl be an issue.
What about local disks for data/logs, backup to SAN? Bit odd but cheap to do and isolate the SAN?
August 12, 2005 at 3:25 am
mmmm... what about a cheaper machine as log keeper? connected by a dedicated ethernet port would take away some stress on the main server....
August 12, 2005 at 6:10 am
This is a very bad situation your in, as this server is now a single critical point of failiure for all Your Applications and DB's. Simply bad architecture. If you want performance, you need hardware, there are cost cuts you can do but your sacrificing performance.
SQL should always be on a dedicated box, especially if you have over 50 db's all with I/O activity. Use perf mon to study your disk queues (dont forget you need to switch it on using cmd first - then reboot server). As a rule, anything over 3 is not good. Disk queues will cause the paging your monitoring currently.
Without knowing more detailed info I cant comment how you should spec the machine, but I would suggest You ALWAYS keep log files on a separate Disk array to Data files, for security and performance. Try to have a dedicated back up Drive, or even better, warm standby server which has replication feeds / log shipping for company critical data.
In the meantime to reduce I/O you can look at the following:-
Look at the recovery methods of each of your DB's. Are they all set to FULL? Do they need to be? do you backup T-logs or can recovery model be set to simple? - This is a great way of reducing I/O
Try to keep all Log files on a sep disk array to Data... move log files if they are not.
Look at Defragmenting the Disk at Operating system level. What is sometimes a good idea is to give some extra disk space to db's what are expected to grow... eg. a 4 GB database, give it 8GB lots of free space to grow then Defrag the Hard Disk at operating system level. This will reduce fragmentation and reads of the disk when DB grows. Also Defrag tables at SQL level.
TEMPDB - This is a pain sometimes, especially if you use a lot of Temp tables. Try to move this away from other DB files- ideally adedicated disk/array would be excellent, might also be worth moving this from the SAN onto Local machine for peformance.
Hope this helps, there are other performance issues you can look at, DB contention is another big area that can cause delays: This is a good resource to look at:
August 12, 2005 at 7:19 am
Before you invest (although it is a GOOD idea) you might want to look more closely at paging just to keep things alive until Christmas.
Check to see if the Windows page file is heavily fragmented. Also check to see if it is set to vary in size. Experience says that a larger, fixed page file that is in one fragment can reduce paging. That won't fix everything but may buy you some time. sysinternals.com has a page file defragmenter.
And do pursue a dedicated SQL server machine. It will be worth it in the end.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
August 12, 2005 at 8:13 am
Before you get a new box, there are some things you can do on your current box and software.
First, increase your RAM. If any of your applications can use AWE memory, go for 8GB or even 16GB. If none of them can use AWE, there is little to gain in having more than 4GB as nothing can use it.
Next, set the /3GB switch in boot.ini. If you have over 4GB memory, also set the /PAE switch. You need to reboot for these to take effect.
Now look at the memory used by your applications. You say you have multiple DBMSs installed, so set their maximum memory so they are not causing paging. SQL2K Standard Edition cannot use AWE, but if your other DBMSs can, then ensure it is used.
This should minimise the paging, which will release a lot of CPU and I-O capacity to do real work.
If all of this does not fix the problem, then you need to look at splitting out stuff on to different servers. If you do look at a significant cash spend, then also look at putting SQL on its own box, and upgrading to 64-bit as this allows you to get significant amounts of extra memory.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 12, 2005 at 12:30 pm
Paging would be caused by the need for data in memory (from vm space on disk), not by the inability to get it off of disk quickly. Elevated Queue lengths are thus a possible symptom of insufficient memory.
The first thing I would check is an SQL counter called "page life expectancy" That is a key indicator of whether or not SQL server has enough memory. According to Microsoft, this value should be consistently higher than 300, although even higher is better.
Usually, when a system is very memory-starved, the value will drop periodically down near zero. Looking at this counter will tell you if SQL server is out of memory or if it is some other process that is causing paging. Follow that counter during the periods of user complaints and let us know what you see.
You may need more memory, but all the RAM and boot.ini switches in the world won't make SQL Standard Edition use more than 2GB.
hth jg
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply