| Author |
Topic |
|
Shaila24072009
Starting Member
7 Posts |
 Posted - 2010-08-19 : 08:20:53
|
| We have our application using SQL Server 2008 running on windows 2008 64 bit machine. The application was working fine for some days. Now the client has started reporting that the application hangs after certain transactions and that the cpu utilization of SQL Server 2008 is very high.After analyzing this problem, we increased the max memory settings, AWE enabled options on that machine. After doing the setting changes, the application worked fine for two weeks. After two weeks the same problem has appeared again. The application has started hanging when the SQL Server cpu utilization is high. RAM on the machine is 8GB. So we have configured 7 GB of RAM (max server settings) for SQL Server 2008.Is this known issue wiith SQL Server 2008? Is there any solution to this problem? What can be done to stop my application from hanging?Thanks in advance |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-19 : 09:45:19
|
| you need to start by identifying the poorly performing queries. high CPU is almost always missing indexes |
 |
|
|
Shaila24072009
Starting Member
7 Posts |
Posted - 2010-08-20 : 01:13:46
|
| This problem is appearing only with SQL Server 2008 and on 64 bit machine. The application is working fine on 32 bit machine with SQL Server 2000/2005. So could this problem be because of hardware? There are around 50 Consecutive users using the application. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-20 : 10:49:40
|
| did you rebuild the indexes on sql 2008 machine yet? |
|
|
|
pizzazzz
Yak Posting Veteran
55 Posts |
Posted - 2010-08-20 : 19:25:45
|
| have you tried truncating transaction files? seem I had a similar problem and it turned out to be the log files being bloated... just a thought... |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-20 : 23:47:00
|
quote:
Originally posted by pizzazzz
have you tried truncating transaction files? seem I had a similar problem and it turned out to be the log files being bloated... just a thought...
This is terrible advice. Sorry pizzazzz but it's important that no one actually reads this and tries it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-21 : 04:06:01
|
Agreed. If log files become "Bloated" then back them up. If you already have a routine backup then increase the frequency. Every 15 minutes is a good interval. See me after class if you want to use a different interval  |
|
|
|
pizzazzz
Yak Posting Veteran
55 Posts |
Posted - 2010-08-25 : 13:57:28
|
| sorry Russel, but when transaction file get full you can encounter strange issues that sound like this persons issue. I sure not why you would not backup and truncate trans-file and why you think this is bad advice? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-08-25 : 14:18:52
|
quote:
Originally posted by pizzazzz
sorry Russel, but when transaction file get full you can encounter strange issues that sound like this persons issue. I sure not why you would not backup and truncate trans-file and why you think this is bad advice?
You'll encounter errors, not strange issues. The fact that you think truncating a transaction log is good thing means that you don't understand backups and recovery models. What recovery model are you using? If it's full, then how often are you backing up the transaction log?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 16:01:25
|
quote:
Originally posted by pizzazzz
sorry Russel, but when transaction file get full you can encounter strange issues that sound like this persons issue. I sure not why you would not backup and truncate trans-file and why you think this is bad advice?
because it is.let me ask you this: why do u think discarding uncommitted transactions is a good idea?oh and by the way, it's not even possible in SQL 2008. it's such a bad idea that MS removed it in 2008 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-08-25 : 16:16:02
|
quote:
Originally posted by russell
let me ask you this: why do u think discarding uncommitted transactions
It doesn't discard the uncommitted transactions, it removes the inactive portion of the log. When you truncate it, it is still guaranteed to be transactionally consistent.So if you've got a very large update occurring in one transaction that has not completed yet and the tlog is truncated, that transaction will still be in there as it is active.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
pizzazzz
Yak Posting Veteran
55 Posts |
Posted - 2010-08-25 : 16:31:23
|
| Ok ladies and gentlemen - maybe I mis-spoke when I suggested truncation - BUT what I meant was to look at the logs and if "bloated" perform some maintenance. Perhap this person is setting up a new 2008 instances and have yet to setup any maintenance process. Besides there is a "Shrink DB" process that will take care of reducing the disk space consumed by the database - log files and remove the empty data and log pages for 2008.let's all be friends, hold hands and sing!! Weeee! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 17:15:32
|
quote:
Originally posted by tkizer
quote:
Originally posted by russell
let me ask you this: why do u think discarding uncommitted transactions
It doesn't discard the uncommitted transactions, it removes the inactive portion of the log. When you truncate it, it is still guaranteed to be transactionally consistent.So if you've got a very large update occurring in one transaction that has not completed yet and the tlog is truncated, that transaction will still be in there as it is active.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Tara, i meant not "hardened" yes everything will be transactionally consistent, but changes will be lost if u need to restore. And the log chain is broken. And I know you know that, but others may not.Pizzazz, have a look at this http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx |
|
|
|
pizzazzz
Yak Posting Veteran
55 Posts |
Posted - 2010-08-25 : 18:46:18
|
| Russell - this is fascinating... ok, i am impressed with your dba knowledge, now just chill-out... |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 20:39:46
|
| haha. i am chilled. i didnt mean to offend you. we're all here to help and to learn |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-26 : 05:10:37
|
quote:
Originally posted by pizzazzzBesides there is a "Shrink DB" process that will take care of reducing the disk space consumed by the database - log files and remove the empty data and log pages for 2008.
IMHO Shrink should only ever be used as a one-off action when some unforeseeable accident/action has occurred. The database should never be shrunk below its normal operating size - the regrowth will fragment the physical file, take CPU time (which may lead to timeouts, particularly in SQL 2000) and will fragment the indexes - such that an index rebuild, after shrink, is prudent and also expose if the database has been shrunk smaller than normal operating size |
|
|
|
|
SQL Server 2008 memory utilization issue