Help with Performance Issues / Application Event Log filled with S

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simon - 07 Jul 2008 18:00 GMT

Hi all
Win2k3 2-Node Active/Passive config cluster
SP2 Win2k3 and SQL2005 SP2

2 small questions
1) SQL is writing events into the Application Log. I am not too sure how to
turn this off. I thought it was from the PROPERTIES of the Server itself,
then goto SECURITY and turn off "Login auditing". trouble is, I do need
auditing. Is there a way to capture this without the details written to the
App log?

2) SQL Cluster is configured as VMWare machines. This is the standard and
works fine. This particular cluster config has a 1.4GB Database, 8 users
mostly connect using another application and approx 500,000 records.

Performance is poor.... almost to the point the CPU is racked up to 80 -
100% and nothing to show for it. Not being a SQL guru, I wondered if anyone
could point me towards finding out what is causing the high CPU usage.

Reboot seems to sort out most issues, but not really ideal. Network monitor
shows hardly anything, and apart from the processor, there is almost 3GB of
free memory, loads of disk space (TB's) free and the OS is hardly doing much.

SQLServer process (the .exe) is the main cause. If anyone has links to
troubleshoot performance issues for a small DB, that would be great

Thanks,

Reply to this Message

Linchi Shea - 07 Jul 2008 18:17 GMT

> auditing. Is there a way to capture this without the details written to the
> App log?

What's wrong with logging info to the app eventlog?

Linchi

> Hi all
> Win2k3 2-Node Active/Passive config cluster
[quoted text clipped - 23 lines]
>
> Thanks,

Reply to this Message

Simon - 08 Jul 2008 05:51 GMT

Linchi
Well to help reduce performance. From a google search, I found a MS article
that talks about turning auditing off in the app log to help improve
performance.

Ok, may not help much... but I thought auditing was captured in the Security
log?

> > auditing. Is there a way to capture this without the details written to the
> > App log?
[quoted text clipped - 30 lines]
> >
> > Thanks,

Reply to this Message

Linchi Shea - 07 Jul 2008 18:20 GMT

> 100% and nothing to show for it. Not being a SQL guru, I wondered if anyone
> could point me towards finding out what is causing the high CPU usage.

Try the following query to find the top 20 CPU-consuming queries:

SELECT TOP 20
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end
-qs.statement_start_offset)/2)
as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,qt.text
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
[Avg CPU Time] DESC

Linchi

> Hi all
> Win2k3 2-Node Active/Passive config cluster
[quoted text clipped - 23 lines]
>
> Thanks,

Reply to this Message

Simon - 08 Jul 2008 05:55 GMT

Hello Linch
Again, thank for you script.
I have run it and detailed below the Avg CPU Time

The 1st Column shows Avg CPU Time 325572
Query text talks about a delete from executionlog
Next column shows dbid 11, dbname is Reportserver and object ID 430624577
Assume the 1st one reported is the highest CPU activity?

Numbers 2 - 20 show dbid as NULL, DBNAME as Null and objectid as NULL
Does this help?

Thank you
Simon

> > 100% and nothing to show for it. Not being a SQL guru, I wondered if anyone
> > could point me towards finding out what is causing the high CPU usage.
[quoted text clipped - 45 lines]
> >
> > Thanks,

Reply to this Message