February 8, 2010 at 12:32 am
Dear All,
Thanks in advance for your help:-)
We have a SQL Server 2005 standard server,
server RAM is 4GB, and SQL Server is configured with Minimum server memory: 2000MB, Maximum server memory: 3500MB.
We have been facing the query slowness problem recent few months. I use profiler and get many queries that took more than 30 seconds to complete, the longest one took 90 seconds. The select queries' reads are very high, for example, reads: 2098701.
I checked the activity monitor of SQL Server, and found several suspended status processes, and wait type are “LCK_M_IX” and “PAGEIOLATCH_SH”.
I also monitored system monitor, and found ：
1）the Disk Reads/sec counter is very high, and Disk Writes/sec is relatively high.
2）SQL Server: page life expectancy is very slow, the value is only 20 to 50.
3） sql server total server memory(KB) is 1599168.
I noticed the server has 4gb RAM, but /3gb switch is not turned on, so I am wondering if it will help improve the performance if we turn on the /3gb switch? and should we modify the current SQL Server memory settings?
Thanks again,
Vivian
February 8, 2010 at 4:03 am
Check the execution plan of those queries, see why they are slow. Indexing may be your problem.
Is your database set to auto update statistics? Are you aware of any unusual event that may have happened before this "slowness"?
February 8, 2010 at 4:35 am
Thanks so much for your reply. I just had a look at the query plan for 2 queries and didn't see table scan, most are the index scan or index seek. But some slow queries are running towards a view. Do you think it will be more faster if we use indexed views?
The index is rebuilt by sql job once a week. auto update statistics is true for our database.
Also do you think it will help improve performance if add /3gb switch? Any ideas would be appreciated 🙂
Thanks again,
February 8, 2010 at 4:57 am
*vivian* (2/8/2010)
Dear All,Thanks in advance for your help:-)
We have a SQL Server 2005 standard server,
server RAM is 4GB, and SQL Server is configured with Minimum server memory: 2000MB, Maximum server memory: 3500MB.
We have been facing the query slowness problem recent few months. I use profiler and get many queries that took more than 30 seconds to complete, the longest one took 90 seconds. The select queries' reads are very high, for example, reads: 2098701.
I checked the activity monitor of SQL Server, and found several suspended status processes, and wait type are “LCK_M_IX” and “PAGEIOLATCH_SH”.
I also monitored system monitor, and found :
1)the Disk Reads/sec counter is very high, and Disk Writes/sec is relatively high.
2)SQL Server: page life expectancy is very slow, the value is only 20 to 50.
3) sql server total server memory(KB) is 1599168.
I noticed the server has 4gb RAM, but /3gb switch is not turned on, so I am wondering if it will help improve the performance if we turn on the /3gb switch? and should we modify the current SQL Server memory settings?
Thanks again,
Vivian
Vivian,
I have couple of questions
1.What is the database size?
2.Are you feel slowness always or particular period?
3.What is your machine configuration? (X64 or X86)
server RAM is 4GB, and SQL Server is configured with Minimum server memory: 2000MB, Maximum server memory: 3500MB.
This is not best practice
4.How can you set Max & Min memory?
5.Did you enabled AWE ?
3) sql server total server memory(KB) is 1599168.
Your server has currently took 1.5 GB memory only.
6.what is the target server memory(KB) ?
7.Check the Pages/Sec using perfmon
8.How many DB's the server has ?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 8, 2010 at 5:05 am
*vivian* (2/8/2010)
Thanks so much for your reply. I just had a look at the query plan for 2 queries and didn't see table scan, most are the index scan or index seek. But some slow queries are running towards a view. Do you think it will be more faster if we use indexed views?The index is rebuilt by sql job once a week. auto update statistics is true for our database.
Also do you think it will help improve performance if add /3gb switch? Any ideas would be appreciated 🙂
Thanks again,
can you attach the execution plan.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 8, 2010 at 5:21 am
Hi muthukkumaran, thanks a lot for taking your time on this, really appreciated. Below is my update to your queries:
1.What is the database size?
-- 109GB
2.Are you feel slowness always or particular period?
--Always, morning is more slower, especially Monday morning.
3.What is your machine configuration? (X64 or X86)
--X86
4.How can you set Max & Min memory?
--It's set by the guys who set up the server, we should leave it as sql server default, correct?
5.Did you enabled AWE ?
--Did not enable AWE.
6.what is the target server memory(KB) ?
--Target server memory is 1599168, same with total server memory(KB).
7.Check the Pages/Sec using perfmon
--Most of the time, the value is below 20, only occasionally it's over 20 even greater than 500.
Any findings with my update? Thank you so much for help!
Regards,
8.How many DB's the server has ?
--Some other databases for qas usage, this database is the only heavily used database on the server
February 8, 2010 at 5:37 am
Hi, I attached 2 query plans for 2 of the slow queries, thanks again :--)
February 8, 2010 at 6:03 am
*vivian* (2/8/2010)
Hi muthukkumaran, thanks a lot for taking your time on this, really appreciated. Below is my update to your queries:
1.What is the database size?
-- 109GB
2.Are you feel slowness always or particular period?
--Always, morning is more slower, especially Monday morning.
Run a server side trace find the slow running queries and tune it.
For more check the Gail shaw article (side trace)
3.What is your machine configuration? (X64 or X86)
--X86
I am wondering,Your Db size is 109 GB.You have only 4 GB ram in your box and sql server taking 1.5 GB ram only.This might be the problem.
I'd recommended upgrade to X64 is the good thing,
if not possible Increase the machine memory and enable the /PAE & AWE to getting more memory (32-bits only)
You already told the rebuild job runs weekly once ?
You have to check the fragmentation daily once for the production servers.
I ll give a suggestion read the index defragmentation script added in my signature it will do the same.If you want you can use because it is minimize the running time only rebuild & reorganize the index if needed and update the statistics.so you can automated daily.
4.How can you set Max & Min memory?
--It's set by the guys who set up the server, we should leave it as sql server default, correct?
5.Did you enabled AWE ?
--Did not enable AWE.
You have only 4GB memory on your box.You didn't enable the AWE then how the sql server taking more memory.
6.what is the target server memory(KB) ?
--Target server memory is 1599168, same with total server memory(KB).
7.Check the Pages/Sec using perfmon
--Most of the time, the value is below 20, only occasionally it's over 20 even greater than 500.
Definitely you have memory pressure.
Any findings with my update? Thank you so much for help!
Regards,
8.How many DB's the server has ?
--Some other databases for qas usage, this database is the only heavily used database on the server
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 8, 2010 at 6:06 am
*vivian* (2/8/2010)
Hi, I attached 2 query plans for 2 of the slow queries, thanks again :--)
Read the Gail's article and post back the Execution plan once.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 8, 2010 at 6:59 am
Hi MUTHUKKUMARAN, thank you so much for your great help. I accquire a lot from your post.
However We may not be able to upgrade the software in short time. So with the current hardware environment, I am going to firstly do as below and see if performance improves more or less. please let me know if you have more advices:-)
1. add /3gb switch and enable AWE
2. use ur script to defrag indexes daily
Many thanks again for your valuble input. BTW, I attach my slow query information and its execution plan.;-)
February 8, 2010 at 10:42 am
*vivian* (2/8/2010)
Hi MUTHUKKUMARAN, thank you so much for your great help. I accquire a lot from your post.However We may not be able to upgrade the software in short time. So with the current hardware environment, I am going to firstly do as below and see if performance improves more or less. please let me know if you have more advices:-)
1. add /3gb switch and enable AWE
2. use ur script to defrag indexes daily
Many thanks again for your valuble input. BTW, I attach my slow query information and its execution plan.;-)
Hi,
Your welcome 🙂
1. add /3gb switch and enable AWE
What's your OS version ?
If you add /3gb in boot.ini,
Kernal memory will be reduced =2GB-1GB = 1 GB
User memory will be increased =2GB+1GB= 3 GB
IMO:
Don't adjust without knowing the /3GB boot.ini .(Contact any experienced consultant then go ahead)
You only have 4 GB memory on your box so,First you need to increase your total server(Machine) memory,then enable the AWE.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 8, 2010 at 5:43 pm
The server OS version is windows server 2003 standard edition, I see currently the server has 1.5GB avaliable memeory, so should be ok if enable /3gb? coz we may not be able to increase more memory for the server in short time, and currently 'page life expectancy' is too low (sometimes below 10) 🙁
Thanks~~
February 8, 2010 at 10:03 pm
*vivian* (2/8/2010)
The server OS version is windows server 2003 standard edition, I see currently the server has 1.5GB avaliable memeory, so should be ok if enable /3gb? coz we may not be able to increase more memory for the server in short time, and currently 'page life expectancy' is too low (sometimes below 10) 🙁Thanks~~
From MS:
Caution Microsoft supports the use of the /3GB parameter in Windows Server 2003, Standard Edition in a production environment for use by Active Directory directory service. For other applications, Microsoft supports the use of the /3GB parameter in Windows Server 2003, Standard Edition only in a production environment if the application vendor has tested in this environment and if the vendor is willing to support the customer who is using this functionality. Microsoft Exchange Server 2003 and Microsoft SQL Server 2000 and the /3GB functionality are supported in a production environment. Contact your application vendor for more information about their application. The /3GB parameter can cause some applications to have problems that are related to address dependencies or to a reduction in kernel space. Except in the cases that are described here, the /3GB parameter in Windows Server 2003, Standard Edition is only for development and for testing.
is the server is dedicated server ?
I already told increasing memory is good one.
Why you didn't increase the memory ? Your server has 109 GB database but you have only 4GB on your box....
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 11, 2010 at 2:21 am
Hi Vivian,
Did you manage to sort our the problem? I have to agree with Muthukumaran on the /3Gig switch, it's not something to play around with on a production server if you only have 4Gig of RAM. Perhaps you can test this on your QA or DEV server, after all that's what they are there for 🙂 .
Perhaps you can make your 109Gig database smaller by archiving some of the older data? This might help performance wise.
Regards,
William
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply