June 15, 2012 at 3:27 am
Good day
Hi ,
we have an environment in windows 2003 32 bit os and sp2 with SQL Server 2005 sp2.
We have encountered recently 'memory leakage issue' when more number of users connections reach about 1000, the server cpu went 100% and hung.
in this situatio, we can't act or do any thing. so we will be switching the nodes from 1 to node 2.
Brief details about our setup.
1. Linked servers so many with unix / oracle and windows oracle and other sql server as well as with Informix.
2. scheduled jobs which is taking data about million of records to updating on 'mercury servers on jboss / jrun and coldfusion'.
3. Open query is being used it some of the query.
4. many of the table is not maintained 'index'. because application is not required to have an index. if we create it, the applicaiton has an issue.
5. So many applications are accessing this particular database on sql server.
6. when the time is created the number os users are about 20000, but now about 47K.
Following action performed:
1. Have reduced some jobs and linked servers.
2. Have shrinked the database and log files.
3. provided the dbfiles size around 2GB always to use it for new data to be stored.
4. auto close - set it as 'TRUE'
5. AUTO SHRINK - set it as 'TRUE'
6. Index re organized - completed
7. some scheduled tasks are re aligned the timing.
8. Increased the AWE memory to 14 GB,
9. suggested to increase the OS either 32 bit to 64 bit or upgrade Sql server 2008 R2.
instead of going for sp4 upgrade on this server.
becuase we do have some bulk users access during some business requirement which is necessary
Kindly do update us with
Fail over is not working successfully on Sql Server 2005
Appreciated your timing and giving solution for this.
June 15, 2012 at 3:32 am
thamaraiselvan (6/15/2012)
4. auto close - set it as 'TRUE'5. AUTO SHRINK - set it as 'TRUE'
Firstly, set these to FALSE
June 15, 2012 at 3:34 am
could you explain it more why ?
what could be the possible performance impact / gain in this?
June 15, 2012 at 3:41 am
thamaraiselvan (6/15/2012)
could you explain it more why ?what could be the possible performance impact / gain in this?
AutoClose - Whenever the last user disconnects fron the DB it is closed. It is opned again when a user wants to connects to it. Openening/Closeng the db causes disk io. Whenever is db is opened consistancy check happens. Cache gets cleared. Recompilation. etc., etc.
AutoShrink - Whenever there is free space (of certain amount) file shrink can happen. It causes disk io. Later file may grow again when space is needed...
June 15, 2012 at 4:33 am
as suresh mentioned file growth is costly operation and it may grow immediately when required. set the same to false.
2. Have shrinked the database and log files.
try to avoild this also.
3. provided the dbfiles size around 2GB always to use it for new data to be stored.
can you explain what have you done on this.
Try updating statistics as you mentioned most of them are Heap might improve performance.
Regards
Durai Nagarajan
June 18, 2012 at 4:50 am
Hi,
Have done it the changes. but i have an issue today which we had earlier.
Close Cursor on Commit Enabled is it set up for 'true' or 'false', pls. explain it
June 18, 2012 at 4:52 am
have settled for auto close and auto shrink.
what is the problem in sql server 2005, if it reaches the number of connection 1000 or 10000 number of transactions, the cpu is going for 100% and hung.
how can we resolve it.
any idea?///
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply