Trouble shooting SQL Server with Profiler and perf

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Trouble shooting SQL Server with Profiler and perf

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-11-03 : 00:05:14

Hello All,

My SQL Server database was fine until we upgraded one of our applications on the server and now we have noticed poor performance on the application side, high memory usage, and high CPU usage.
To try to resolve this issue the following was done:

-Installed 4GB additional memory totaling 16GB on the server
-Added reorganize index, rebuild index, and update statistics to the maintenance plan

still problem exists. I ran SQL server profiler and the Performance monitoring at the same time to try to figure out what is causing the sluggish performance. The following counters were high to my knowledge:

Page Faults/sec
SQL Recompilations/sec
Lock Timeout/sec
Lock wait time (ms)
Lock waits/sec
Buffer cache hit ratio
Database pages
checkpoint pages/sec
page life expectancy
% Processor Time
Pool Nonpages Bytes
Transition Faults/sec
Free system Page Table Entries
Pool Pages bytes
Available Mbytes

The following SQL Server trace was performed on the server:

Errors and warnings
Locks
Stored procedures
TSQL

I was able to import the performance data into SQL Server Profiler and now, what should I be looking for? By looking at the profiler with the performance monitor, how would I figure out what is wrong with the system/SQL Server?

Please advice.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-03 : 00:15:17

I'd start with the long running queries:

SELECT TOP 1000 Duration/1000000.0 AS DurationInSeconds, TextData
FROM YourTraceTable
WHERE TextData IS NOT NULL
ORDER BY Duration DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

pras2007
Posting Yak Master

216 Posts

Posted - 2009-11-03 : 01:28:25

thanks for the quick response tkizer, in order to query that results I would have to load the trace file into a table? Please advice.

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-03 : 11:16:22

see here: http://msdn.microsoft.com/en-us/library/ms179455(SQL.90).aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-03 : 12:50:00

You can't do very good analysis if the data is just in the GUI. Load it into a table or save it to a file so that you can query it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-11-03 : 21:43:42

Correlate Profiler with Performance counters to find where the bottlenecks are.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources