January 8, 2009 at 9:57 am
hello gurus, i have a question i currently have a server 64 bit, 4 GB of memory, total hard disk space 275 GB. only Sql server 2005 installed.
We have recently added a second Database which added a load of 100 GBs (counting backups) now leaving us with only 50 GB of free space in the servers counting all the drives.
After this new DB was added, users started complaining of timeout errors however CPU does not go higher than 20%. I have changed the full backups (no transactional backups done due to lack of space) to every 2 days. All maintence jobs are running good , (rebuilding indexes every night, shrinking DB once a week)
In your opinion do you think adding more space will help increase performance?
January 8, 2009 at 10:05 am
If temp database uses more space then it might help to increase the storage. Check for the usage of the temp DB.
Regards,
Nitin
January 8, 2009 at 10:10 am
In my opinion, adding more space will not increase performance, but you might want to consider it for future expansion of your database storage. Your processor may be operating in the proper ranges, but how much memory is being used? Is it reaching its limit to where there's a lot of swapping of data from RAM to virtual memory? You may be experiencing a lot of I/O operations not only in memory, but in SQL processes as well. Another tool that I like to use is the Performance app located in the Administrative Tools folder. You can monitor all kinds of operations and services.
Hope this helps,
Micheal
January 8, 2009 at 10:23 am
Micheal Young (1/8/2009)
In my opinion, adding more space will not increase performance, but you might want to consider it for future expansion of your database storage. Your processor may be operating in the proper ranges, but how much memory is being used? Is it reaching its limit to where there's a lot of swapping of data from RAM to virtual memory? You may be experiencing a lot of I/O operations not only in memory, but in SQL processes as well. Another tool that I like to use is the Performance app located in the Administrative Tools folder. You can monitor all kinds of operations and services.Hope this helps,
Micheal
I agree with this completely. The only way that adding more space would actually help would be if you also added more spindles (increased the IO potential) or split your databases off into separate physical drives (to control how much IO each database will be allowed to have). Based on the original post it sounds like they are set up in a way that the two databases are sharing IO which certainly will negatively impact the performance of a database that previously was able to have all that IO to itself.
January 8, 2009 at 10:27 am
4 GB of RAM is a dead rubber...I wouldn't be scratching my head why the server is slow...just ADD RAM..
Did you even check how much the BUFFER CACHE HIT ratio was before and after the DB was added..was this point even discussed...well its not late you know...
Why change backup strategy..can you afford 2 days of data loss if something happens?
Well the memory could be an issue...
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 8, 2009 at 10:29 am
Looking at Nitin's reply reminded me of a few coding things that can hinder performance (I'm basing this off my own experience).
1. If there are queries that use the AliasName = Whatever format instead of Whatever AS AliasName (excluding the setting of variables), this can slow things down especially if the query is accessing large size tables.
Example (Bad Idea):
[font="Courier New"]
SELECT FieldA = colA
, FieldB = COALESCE(colB, colB2, '')
, FieldC = colC
FROM MyTable
[/font]
Example (Good Idea):
[font="Courier New"]
SELECT colA AS FieldA
, COALESCE(colB, colB2, '') AS FieldB
, colC AS FieldC
FROM MyTable
[/font]
2. If you're using a lot of temp tables in your queries, which take time to create in the temp database. I tried to avoid them if possible and use cte's instead since they're more memory based. Plus you don't have to worry about dropping them.
3. Using a unnecessary ORDER BY statements will cause slow-downs. I've seen situations where people who create temp tables with ORDER BY statements (usually more than one) and then create the resulting query that references the temp tables and again specify an ORDER BY statement. In these cases, it's not necessary to use ORDER BY.
Thanks,
Micheal
January 8, 2009 at 10:32 am
i checked task Manager and this is what I see:
Physical Memory (k)
Total 4193000
Available 107600
System Cache 217240
Kernel Memory (k)
total 152470
page 116820
Nonpaged 35020
I also checked the Performance monitor
for Counter Pages/sec , Object: Memory
i get average 2.98, Maximum 294.940, Duration 1:40
What do you think? do I need more memory?
January 8, 2009 at 10:37 am
You just have 100MB of available memory...sounds like a bottleneck..but lets not stop there..what are the RAID configurations?
Is tempdb on a separate disk? What kind of activity goes on these databases..like more reads or writes or both?
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 8, 2009 at 10:39 am
Do you have IIS installed? If so is it on the DB server or on a separate app server?
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 8, 2009 at 10:42 am
right now i have the data files from my new DB in the same physical drive as the one i had before, if i move them to a different drive will this help?
January 8, 2009 at 10:47 am
Depends on what type of RAID is the existing disk..and the performance would boost if it were to be moved to a disk with higher throughput..but first we need to find if the disk is a bottleneck..
You said you don't have transaction logs enabled..what is the recovery model then?
If it is FULL then NO Transaction Log backups mean the DB is in Pseudo Simple Recovery model...(NO POINT IN TIME RESTORES)
If its full then is autogrowth enabled?, then it will be growing time and again causing slowness..
Sorry about a bunch of questions..but will help checking the bottleneck..
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 8, 2009 at 10:51 am
DBA (1/8/2009)
right now i have the data files from my new DB in the same physical drive as the one i had before, if i move them to a different drive will this help?
This will definitely not hurt you no matter what the case is because depending on your set up moving it to a different physical drive will allow the original database to have the same potential IO that it had before (and the new DB will have all of the IO based on the drive you are moving it to). In general if you can afford to do it, it is always nice to be able to split your DB's onto separate physical drives, specifically high IO db's.
However, if as mentioned above your issue ends up being more memory/cache based and not strictly IO based then it will not be a great deal of help to you.
January 8, 2009 at 10:55 am
hello, by using this query select * from sys.dm_os_performance_counters
where counter_name = 'Buffer cache hit ratio'
i get :
cntr_value cntr_type
2660 537003264
January 8, 2009 at 11:35 am
hi, don't worrry ask as many questions as you want, yes it is full with simple recovery model, i have both my Dbs as autogrowth, by 10% , unrestricted.
January 8, 2009 at 12:15 pm
hi i will find out my RAID configuration,
this is what i know:
ALL NTFS, Partition
Disk 0
C and D drive
Disk 1
E drive
Disk 2
F drive
Disk 3
G drive
the Temp DB is in my C drive. (separate to the other DBs).
yes I have IIs installed in this box.
Thank you so much for all your help guys!! any help will be greatly apreciated 🙂
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply