RE: Performance analysis

RE: Performance analysis

 

  

Ning Wong,
It's all good! hehe.



-----Original Message-----
From: Ning Wong
[mailto:mssqldba-ezmlmshield-x99002459.[Email address protected]
Sent: Wednesday, April 26, 2006 4:26 PM
To: LazyDBA Discussion
Subject: RE: Performance analysis


Not a thing Christopher. Everyday has been Friday since 1999.

-----Original Message-----
From: Bellizzi Christopher - IBG
[mailto:mssqldba-ezmlmshield-x13236306.[Email address protected]
Sent: Wednesday, April 26, 2006 1:22 PM
To: LazyDBA Discussion
Subject: RE: Performance analysis


Did I miss a few days? Yahoo!



Christopher Bellizzi
SQL DBA/Systems Analyst
240 Fall Street
SFO Main Office
Seneca Falls, NY 13148
315-568-7886


-----Original Message-----
From: Edwards Ed
[mailto:mssqldba-ezmlmshield-x74619842.[Email address protected]
Sent: Wednesday, April 26, 2006 4:07 PM
To: LazyDBA Discussion
Subject: RE: Performance analysis

Oh come, on! Christopher, it's Fridayyyyyyyyyyyyyyyy!
Partyyyyyyyyyyyy! hehe.





Please take a few minutes to provide feedback on the quality of service
you received. The Department of Education values your feedback as a
customer. Commissioner John L. Winn is committed to continuously
assessing and improving the level and quality of services provided to
you by Department staff. Simply use the link below. Thank you in
advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Bellizzi Christopher - IBG
[mailto:mssqldba-ezmlmshield-x64386367.[Email address protected]
Sent: Wednesday, April 26, 2006 3:59 PM
To: LazyDBA Discussion
Subject: RE: Performance analysis


Hey Beadle juice you been hangin with the DeVo's too .. nice

Hope you guys are having a better day than I!

Christopher Bellizzi
SQL DBA/Systems Analyst
240 Fall Street
SFO Main Office
Seneca Falls, NY 13148
315-568-7886


-----Original Message-----
From: Ken Ross
[mailto:mssqldba-ezmlmshield-x62463629.[Email address protected]
Sent: Wednesday, April 26, 2006 3:50 PM
To: LazyDBA Discussion
Subject: RE: Performance analysis

Thanks!! That's a great tool to keep on hand - thanks for sharing!

-----Original Message-----
From: Michael_Beadles
[mailto:mssqldba-ezmlmshield-x42946510.[Email address protected]
Sent: Wednesday, April 26, 2006 3:08 PM
To: LazyDBA Discussion
Subject: RE: Performance analysis

Here is a query I use to start out with profiler analysis ...
(I have different versions for CPU, Reads, Writes)

It strips out the paramaters applied to the stored procedures, just
leaving the base procedure name.
Because it does this kind of filtering, it takes about 5 minutes to run
against a 5 million row trace table.

In this way, procedures with different parameter strings are not
considered to be distinct from one another, you just get the top 10
distinct slowest procedure names or select queries that occured during
the trace.


Note: I have replaced the "at symbol" with a " ^ " so the
posting
software doesn't think it is an email address.
You will need to replace the ^ with the "at symbol".

You then ask the developers to focus on the ones with the highest
average duration that were executed the most times.

To spot that easily, I take the square of the avg duration and multiply
by the square of the times executed.
The biggest number marks the procedure that is the least efficient in
the applicaton.

***********************************************

set nocount on

Create table #process_01 (
Proc_Date varchar(32),
duration int,
Reads int,
Writes int,
CPU int,
Proc_Text varchar(500))


--slowest of all
Insert #process_01

SELECT distinct
Convert(varchar(32),StartTime,100)
,duration
,reads
,writes
,cpu
,cast(TextData as varchar(500))
from dbo.<trace_table_name_here>


Select top 10
count(a.Duration) as TimesExecuted,
min(a.Duration) as MinDuration,
max (a.Duration)as MaxDuration,
avg(a.Duration) as AvgDuration,
a.Process_String from
(
Select top 100 percent
LTrim(Rtrim(Case
when PATINDEX ( '%^%' , Proc_Text ) > 0
then Left(Proc_text, PATINDEX ( '%^%' , Proc_Text )-
2)
else Proc_text end))
as Process_String,
duration duration
From #process_01 ) a
Where
(a.Duration > 100
and PatIndex('%msdb%',a.Process_string) < 1
and PatIndex('%set %',a.Process_string) < 1
and PatIndex('%backup %',a.Process_string) < 1
and PatIndex('%master.dbo%',a.Process_string) < 1)
or PatIndex('select *%',a.Process_string) > 0 Group by
a.Process_String having avg(a.Duration) >= 100 Order by max(Duration)
desc

set nocount off
drop table #process_01


It returns data like this ...

Times
Executed |MinDuration |MaxDuration|AvgDuration|Process_String
-------- ----------- ----------- ----------- --------------
104 |110 |300603 |15116 |Exec
My_DB.dbo.get_BP_Sort_LS
2 |37359 |37390 |37374 |select * from
My_DB..STATUSDATE
2414 |106 |30546 |823 |Exec
My_DB.dbo.getsomeSlowProcess
32 |109 |23828 |1472 |EXEC
dbo.getsomeSlowProcessForPrelimData
84 |109 |23720 |2295 |exec
My_DB.dbo.getsomeSlowProcessSupportAllData
294 |422 |12140 |909 |exec getWebUser1
661 |109 |11657 |835 |EXEC
dbo.getsomeSlowProcess1
39 |719 |11547 |1787 |Exec
My_DB.dbo.getsomeSlowProcessEPrequal
11 |265 |10580 |3668 |Exec
My_DB..getsomeSlowProcessQuickSubmit
18 |3547 |10110 |5158 |EXEC
utlPostEventForZombieNotification






"SIDNEY D IVES "

<mssqldba-ezmlmsh

ield-x42291401.x1
To
[Email Address Removed] "LazyDBA Discussion"

.com> <[Email address protected]


cc
04/26/2006 10:42

AM
Subject
RE: Performance analysis

















I would start with Reads and Writes. When those numbers increase then
more resources are being used. Start with the highest values then work
down the list. At some point you will reach a point of diminishing
return.

Since you have to keep the service on the same server as the db then I
would run test cycles with SQL controlling memory and then where you
adjust memory. Compare the two.

I apologize for being so vague but this sort of troubleshooting is not
an exact science.

Sidney Ives
Database Administrator
Sentara Healthcare
(757) 965-0117
[Email address protected]

>>> "Ken Ross "
<mssqldba-ezmlmshield-x36503208.[Email address protected] 4/26/2006 1:17
PM >>>

Thanks Sidney,

I would also split the machines in real life but in this case (and in
some customer situations) they have to play nice together on the same
box.

What should I be watching for in Profiler to pick out the top offenders?

Thanks again!

Ken

-----Original Message-----
From: SIDNEY D IVES
[mailto:mssqldba-ezmlmshield-x78362487.[Email address protected]
Sent: Wednesday, April 26, 2006 1:10 PM
To: LazyDBA Discussion
Subject: Re: Performance analysis

Run Profiler to determine the performance of the queries being sent by
the application. Resolve the biggest offenders then re-evaluate.

The best architecture for your situation (in my opinion) is to move the
application service to another server and allow SQL to control the
memory. Allowing SQL to control the memory in your current situation
would likely effect the applicaton more so than the OS. Before doing
that you may want to run Perf.mon to see how the memory in the server is
being utilized and adjust accordingly.

Sidney Ives
Database Administrator
Sentara Healthcare
(757) 965-0117
[Email address protected]

>>> "Ken Ross " <mssqldba-ezmlmshield-x8290233.[Email address
protected]
4/26/2006 11:54 AM >>>

We have a new application that's heading towards alpha and so we're
starting to take a serious look at how well its behaving with SQL 2005.
The first sign of a problem is that with only one connected client the
CPU on the SQL box is being pegged at 100% for more than 40 seconds at a
time, with brief pauses between cycles.

The test system isn't anything special - a Pentium 4 desktop with 768Meg
RAM. SQL has been restricted to 100M of RAM in order to make sure the OS
and application has enough room.

The general application architecture is that there is a service running
on the SQL box and remote applications communicate to this service over
a Remoting interface to send data updates and retrieve results. The
service makes use of BCP for some operations and all updates are pushed
into a temp table prior to migrating it into the "real" tables. Temp
tables are created and destroyed as needed when each client performs an
update.

My question really is how best to attack this to figure out what's
killing the CPU.

Thanks!

Ken


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website
:
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website
:
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html





======================================================================
Confidentiality Notice: The information contained in and transmitted
with this communication is strictly confidential, is intended only for
the use of the intended recipient, and is the property of Countrywide
Financial Corporation or its affiliates and subsidiaries. If you are
not the intended recipient, you are hereby notified that any use of the
information contained in or transmitted with the communication or
dissemination, distribution, or copying of this communication is
strictly prohibited by law. If you have received this communication in
error, please immediately return this communication to the sender and
delete the original message and any copy of it in your possession.
======================================================================



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


************************************
This e-mail and any files transmitted with it are proprietary and
intended solely for the use of the individual or entity to whom they are
addressed. If you have received this e-mail in error please notify the
sender. Please note that any views or opinions presented in this e-mail
are solely those of the author and do not necessarily represent those of
ITT Industries, Inc. The recipient should check this e-mail and any
attachments for the presence of viruses. ITT Industries accepts no
liability for any damage caused by any virus transmitted by this e-mail.
************************************




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


************************************
This e-mail and any files transmitted with it are proprietary and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of ITT Industries, Inc. The recipient should check this e-mail and any attachments for the presence of viruses. ITT Industries accepts no liability for any damage caused by any virus transmitted by this e-mail.
************************************



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page