| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2010-11-09 : 04:33:48
|
| Hi All,I have a production database having 55 GB of Datafile and 6 GB of Tran log file.In the recent last 6 months database growth was 8 GB.We have Index rebuild maintenance plan and full backup ones in a week and daily diff log with every 1 hour tran log backup.In the last 2 days the website connecting to this database is very slow and whole performance is very bad.Could somebody pls suggest me the high level idea how i can tune for better performance.Thanks,Gangadhar |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-09 : 06:37:58
|
| [url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]--Gail ShawSQL Server MVP |
 |
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-11-09 : 08:57:26
|
| Hi,Any further guidelines will be helpful. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-11 : 04:32:16
|
| This is far to general a question to provide a very helpful answer. How do you know SQL is the cause of the slowdown? Have you run the Queries in SSMS to see if they run slow there (Eliminate the web app). Once you do that, is it all queries or just certain ones? What is the perf bottleneck on the server?? Memory, Disk, Processor?? Is there a job or query running that is driving high IO and slowing everything else down? -Chad |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-11-22 : 01:36:26
|
| From the above posted link i found some of the stored proc duration.IO/CPU usage is more and i have requested my developer to look into it.Suggest what next i need to do for performance improvement.I have done with1.Created maintance plan to have index rebuild/statistics update.2.Need to enable AWE /3GB |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-23 : 21:05:56
|
| Is it 32 bit? How much memory? |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-11-23 : 21:49:28
|
| yes its 32 bit server.here is the output from version: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Now AWE is not enabled i checked it from sys.configurationMy server RAM is 32 GB but current AWE value is at 8 GBPlease help me in this regard.Thanks,Gangadhar |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-23 : 21:54:55
|
| Ok, without AWE you are not using the vast majority of the memory on the server (At least SQL isn't able to use it). You need to enable AWE, definitely DO NOT do /3GB, you will almost certainly run out of PTEs resulting in system instability, dropped packets, and potentially even blue screens. Cap Max Server Memory around 29-30GB and enable AWE. I assume you already have PAE enabled, or the OS has it auto enabled. Does windows see the whole 32GB (Or most of it)?-Chad |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-11-23 : 23:49:06
|
| Hi,Please if you could please give me some details related to how to check PAE is enabled by OS has auto enabled it or not ?Also if you pls give me some reference which shows how to enable AWE with proper settings on production server.Thank you very much for your valuable comments here.Thanks,Gangadhar |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-27 : 04:12:23
|
| If it is 32 bit, and you check the computer properties, Windows will not see the memory over 4GB is PAE is not enabled one way or the other.If you go into Management studio, right click the server, select properties, and go to the memory tab, there you can check AWE, and set the max server memory. You can also do both with sp_configure if you prefer scripting it.-Chad |
|
|
|
|
Performance Tunning of Prod Database