EPiWiki.se - EPiServer notes shared with others

/ Tools
/ SQL Server profiler

SQL Server profiler

[Edit]

SQL Server profiler tips and trix.

Importeant columns to add

SPID - so it's possible to folow one request
StartTime and EndTime - to see in what actual order its executed
CPU - to se if a high CPU hang or a low CPU hang (
Duration - to see how long time te execution is

Other columns to add

SP:Compleated - Listen to executed sotred procedures
SP:StmtCompleated - Listen to events fired by triggers

Query a trace file from SQL Management studio

SELECT Name, *
FROM ::fn_trace_gettable('C:\TestLocks.trc', default) e
JOIN sys.trace_events ON e.EventClass = trace_event_id

Create your own event

EXEC master..sp_trace_generateevent @event_class = 82, @userinfo = N'Log to SQL profiler'

In SQLProfiler add the event "User Configurable" sub event "User Configurable:0"

My favorite base configuration

Copy this and click “Paste Counter List” in PerfMon.
%Processor time – Processor information
#Total committed - .NET Memory (w3wp)
%time in GC – .NET Menory (w3wp)
Cache Total Entries - ASP.NET Applications (specific instance has to be changed)
Request/Sec - ASP.NET Applications (specific instance has to be changed)

<OBJECT ID="DISystemMonitor1" WIDTH="100%" HEIGHT="100%"
CLASSID="CLSID:C4D2D8E0-D1DD-11CE-940F-008029004347">
<PARAM NAME="_Version" VALUE="393219"/>
<PARAM NAME="_ExtentX" VALUE="21378"/>
<PARAM NAME="_ExtentY" VALUE="12991"/>
<PARAM NAME="DisplayType" VALUE="1"/>
<PARAM NAME="ReportValueType" VALUE="0"/>
<PARAM NAME="MaximumScale" VALUE="100"/>
<PARAM NAME="MinimumScale" VALUE="0"/>
<PARAM NAME="ShowLegend" VALUE="-1"/>
<PARAM NAME="ShowToolbar" VALUE="-1"/>
<PARAM NAME="ShowScaleLabels" VALUE="-1"/>
<PARAM NAME="ShowHorizontalGrid" VALUE="0"/>
<PARAM NAME="ShowVerticalGrid" VALUE="0"/>
<PARAM NAME="ShowValueBar" VALUE="-1"/>
<PARAM NAME="ManualUpdate" VALUE="0"/>
<PARAM NAME="Highlight" VALUE="1"/>
<PARAM NAME="ReadOnly" VALUE="0"/>
<PARAM NAME="MonitorDuplicateInstances" VALUE="-1"/>
<PARAM NAME="UpdateInterval" VALUE="5"/>
<PARAM NAME="DisplayFilter" VALUE="1"/>
<PARAM NAME="BackColorCtl" VALUE="13160660"/>
<PARAM NAME="ForeColor" VALUE="0"/>
<PARAM NAME="BackColor" VALUE="13160660"/>
<PARAM NAME="GridColor" VALUE="8421504"/>
<PARAM NAME="TimeBarColor" VALUE="255"/>
<PARAM NAME="Appearance" VALUE="-1"/>
<PARAM NAME="BorderStyle" VALUE="0"/>
<PARAM NAME="NextCounterColor" VALUE="13"/>
<PARAM NAME="NextCounterWidth" VALUE="0"/>
<PARAM NAME="NextCounterLineStyle" VALUE="0"/>
<PARAM NAME="GraphTitle" VALUE=""/>
<PARAM NAME="YAxisLabel" VALUE=""/>
<PARAM NAME="DataSourceType" VALUE="1"/>
<PARAM NAME="SqlDsnName" VALUE=""/>
<PARAM NAME="SqlLogSetName" VALUE=""/>
<PARAM NAME="LogFileCount" VALUE="0"/>
<PARAM NAME="AmbientFont" VALUE="0"/>
<PARAM NAME="FontName" VALUE="Tahoma"/>
<PARAM NAME="FontSize" VALUE="8.25"/>
<PARAM NAME="FontItalic" VALUE="0"/>
<PARAM NAME="FontUnderline" VALUE="0"/>
<PARAM NAME="FontStrikethrough" VALUE="0"/>
<PARAM NAME="FontWeight" VALUE="400"/>
<PARAM NAME="LegendColumnWidths" VALUE="5.73979591836735E-02 5.73979591836735E-02 0.165816326530612 0.307397959183673 6.63265306122449E-02 6.63265306122449E-02 9.69387755102041E-02"/>
<PARAM NAME="LegendSortDirection" VALUE="0"/>
<PARAM NAME="LegendSortColumn" VALUE="2097272"/>
<PARAM NAME="CounterCount" VALUE="5"/>
<PARAM NAME="MaximumSamples" VALUE="100"/>
<PARAM NAME="SampleCount" VALUE="100"/>
<PARAM NAME="SampleIndex" VALUE="97"/>
<PARAM NAME="StepNumber" VALUE="10"/>
<PARAM NAME="Counter00001.Path" VALUE="\\WIN2003\.NET CLR Memory(w3wp)\# Total committed Bytes"/>
<PARAM NAME="Counter00001.Color" VALUE="32896"/>
<PARAM NAME="Counter00001.Width" VALUE="1"/>
<PARAM NAME="Counter00001.LineStyle" VALUE="0"/>
<PARAM NAME="Counter00001.ScaleFactor" VALUE="-6"/>
<PARAM NAME="Counter00001.Minimum" VALUE="0"/>
<PARAM NAME="Counter00001.Maximum" VALUE="105414656"/>
<PARAM NAME="Counter00001.Average" VALUE="71514071.04"/>
<PARAM NAME="Counter00001.StatisticStatus" VALUE="0"/>
<PARAM NAME="Counter00001.Data" VALUE="82849792 71663616 86581248 72937472 105414656 92721152 83427328 71651328 83361792 71012352 61493248 31686656 8527872 82604032 93855744 82321408 81408000 89464832 89292800 78786560 67252224 98496512 87216128 75759616 88043520 74268672 62504960 34115584 8527872 2170880 104685568 94900224 86310912 74735616 92250112 80896000 68317184 97456128 86499328 74747904 90177536 90177536 90177536 90177536 89391104 77615104 64393216 60219392 27656192 8527872 83189760 72359936 99770368 87097344 71684096 86433792 75051008 105111552 91811840 80805888 90337280 80461824 70025216 70025216 63475712 32530432 8527872 0 104714240 90193920 74866688 87756800 70893568 102469632 88285184 73097216 89436160 74932224 62521344 55021568 8527872 8527872 74850304 67522560 67522560 67522560 67522560 67522560 67522560 67522560 67522560 67522560 67522560 67522560 67522560 67522560 67522560 67522560 67522560 -1"/>
<PARAM NAME="Counter00002.Path" VALUE="\\WIN2003\.NET CLR Memory(w3wp)\% Time in GC"/>
<PARAM NAME="Counter00002.Color" VALUE="4210752"/>
<PARAM NAME="Counter00002.Width" VALUE="1"/>
<PARAM NAME="Counter00002.LineStyle" VALUE="0"/>
<PARAM NAME="Counter00002.ScaleFactor" VALUE="0"/>
<PARAM NAME="Counter00002.Minimum" VALUE="0"/>
<PARAM NAME="Counter00002.Maximum" VALUE="54.5651708889949"/>
<PARAM NAME="Counter00002.Average" VALUE="9.09941145245111"/>
<PARAM NAME="Counter00002.StatisticStatus" VALUE="0"/>
<PARAM NAME="Counter00002.Data" VALUE="9.64277213418276 5.7043364991931 8.89105455734308 5.62250322756276 11.6997883156798 11.1216289807538 6.46281090644244 4.90017559661921 15.5327397697686 18.6322315308544 3.09649825474763 17.2414494607653 0.632314410853418 5.07326467512871 10.3520816707258 5.16459878265602 5.2924355412235 15.9778925447008 12.1581545900544 6.21853554000009 3.60724853681272 14.8291393917887 7.87520572973368 5.02979859231989 7.21235658865443 5.51202021793446 3.86946500558985 2.27643354972104 0.743441364855276 0 9.48491475967155 4.7981010279075 3.61039299399989 10.5725436978861 7.40679720808504 11.1079342837106 6.65167312133744 4.51631411158145 4.31046444324846 7.39567669018623 0.125744149414433 0.125744149414433 0.125744149414433 0.125744149414433 9.76148106176677 10.2789110958014 2.90094590498861 2.136140241826 54.5651708889949 1.14109320105425 9.22839827449754 3.4320258085911 3.61973826089034 9.51597213453711 9.65491156780536 7.76641839610205 5.51800910518727 10.5195510691924 10.0246081755409 4.26022752483385 10.4925527067909 7.48225983535529 8.28633191478596 8.28633191478596 2.7926718202757 2.41220411904406 0.738020368864622 0 12.6652926171645 6.92734035144464 10.421698718434 7.51717316496294 7.98921887191254 8.2720049594747 10.1697832573411 1.76298600622915 9.81663957621384 5.89100885686569 1.16070439271026 44.578346931337 0.449805772059283 2.11666098815168 11.6195826195542 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 16.0611034039886 -1"/>
<PARAM NAME="Counter00003.Path" VALUE="\\WIN2003\ASP.NET Applications(_LM_W3SVC_1222541103_Root)\Cache Total Entries"/>
<PARAM NAME="Counter00003.Color" VALUE="64"/>
<PARAM NAME="Counter00003.Width" VALUE="1"/>
<PARAM NAME="Counter00003.LineStyle" VALUE="0"/>
<PARAM NAME="Counter00003.ScaleFactor" VALUE="-3"/>
<PARAM NAME="Counter00003.Minimum" VALUE="29"/>
<PARAM NAME="Counter00003.Maximum" VALUE="59234"/>
<PARAM NAME="Counter00003.Average" VALUE="55573.03"/>
<PARAM NAME="Counter00003.StatisticStatus" VALUE="0"/>
<PARAM NAME="Counter00003.Data" VALUE="59205 59205 59204 59204 59204 59205 59205 59205 59205 59192 58777 109 59214 59192 59192 59192 59192 59192 59192 59192 59193 59192 59192 59192 59192 59193 58907 173 29 59229 59207 59207 59207 59207 59207 59207 59207 59208 59208 59208 59190 59192 59192 59192 59193 59191 59188 58681 39 59234 59212 59212 59212 59212 59212 59212 59212 59213 59213 59213 59213 59213 59192 59192 58802 157 59214 59202 59192 59192 59192 59192 59192 59193 59192 59192 59192 59193 58980 56160 31 59121 59099 59020 59020 59020 59020 59020 59020 59020 59020 59020 59020 59021 59021 59021 59021 59021 59021 -1"/>
<PARAM NAME="Counter00004.Path" VALUE="\\WIN2003\ASP.NET Applications(_LM_W3SVC_1222541103_Root)\Requests/Sec"/>
<PARAM NAME="Counter00004.Color" VALUE="16711935"/>
<PARAM NAME="Counter00004.Width" VALUE="1"/>
<PARAM NAME="Counter00004.LineStyle" VALUE="0"/>
<PARAM NAME="Counter00004.ScaleFactor" VALUE="0"/>
<PARAM NAME="Counter00004.Minimum" VALUE="0"/>
<PARAM NAME="Counter00004.Maximum" VALUE="24.6432895814659"/>
<PARAM NAME="Counter00004.Average" VALUE="12.395558511795"/>
<PARAM NAME="Counter00004.StatisticStatus" VALUE="0"/>
<PARAM NAME="Counter00004.Data" VALUE="19.2953408024688 18.3114839263998 18.6300471115699 18.7715873218217 19.6299444059047 17.6281327574287 17.9765170730265 18.9710553933739 19.8299348201501 3.60545802775515 0.200487055039292 -1 1.79838717420604 18.0168423522764 20.3833632391716 18.016697259425 21.0328530268075 19.5700998657188 19.0941686206379 17.5695860275925 18.7784523967782 18.5655580681602 17.982163244542 17.8182625561813 19.8333697306613 12.9713564740453 0.399841204885854 -1 0.200327336180806 13.3786392965286 17.9728815998902 16.0003862036566 17.8027582862474 19.9998133858445 21.7995992748709 19.4939455312846 19.706712639641 18.6297114008751 20.1699238351627 16.4270088846739 0 0 0 0.59909976329204 19.9689215077002 18.0008076249089 1.40354923515889 0.20347396952914 -1 3.80606325527599 18.7718491355299 18.3917853284752 18.7939820110709 20.8560189296688 21.7446400997454 20.2015960160728 19.0146856653542 20.8343029765018 20.2317169648433 21.5673664752634 19.9619013819184 16.5863909434856 0 1.99755463769212 0.199993306635862 -1 0.600954564746143 19.4304162664549 24.2871170431798 21.2350410850209 23.3284455607654 23.9989326492396 22.8371591220645 22.3660230671121 24.6432895814659 20.1664574535627 24.0393852791089 16.275389182973 0.597268401152863 -1 0.400624494049355 10.9796500978172 10.5979494210105 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 -1"/>
<PARAM NAME="Counter00005.Path" VALUE="\Processor(_Total)\% Processor Time"/>
<PARAM NAME="Counter00005.Color" VALUE="255"/>
<PARAM NAME="Counter00005.Width" VALUE="1"/>
<PARAM NAME="Counter00005.LineStyle" VALUE="0"/>
<PARAM NAME="Counter00005.ScaleFactor" VALUE="0"/>
<PARAM NAME="Counter00005.Minimum" VALUE="1.55997993548634"/>
<PARAM NAME="Counter00005.Maximum" VALUE="100"/>
<PARAM NAME="Counter00005.Average" VALUE="63.7987144983572"/>
<PARAM NAME="Counter00005.StatisticStatus" VALUE="0"/>
<PARAM NAME="Counter00005.Data" VALUE="72.4134399954802 68.8469532659239 73.7493552841658 73.4368199825915 79.3744719864829 67.3922012258767 74.1368467376311 78.4321166563174 72.8084626005269 87.7737469347091 97.1961360691389 98.4374426541454 99.3749919998976 74.0621679957503 69.3746079949823 60.3119919934975 79.9997439967232 86.5623279977984 73.6673745411402 70.9371279952383 79.3747359966208 74.1430676156165 70.6248119987968 70.9373139988096 71.2469866842045 88.4344652036694 92.8106135049837 97.4994445766294 100 85.9372299948159 56.2491599838717 54.0610979447093 56.2485999551986 72.1866099715191 71.2489546119897 68.3373421476689 69.1576941601286 69.3741792280033 72.4996479954943 69.9996159950847 2.1862479839742 4.3737759843326 1.56613816569078 8.40981251062446 74.6857178745384 73.7481518698916 90.6243399535327 93.7498289953213 99.0624819996544 96.5624339987328 61.5617619858301 66.2493519875582 64.9993279870974 71.5613499409916 81.8736078930862 79.0608918764961 69.9979502599618 83.1245679889405 76.1749386677716 83.800208338293 78.7492009699565 65.6236799493101 2.8087678566857 61.559044926958 97.4997279704032 96.5621259593044 100 87.1870079811065 86.9153854012667 83.4369143292907 97.8124439985664 97.8124439985664 95.9373959973375 94.0621228260419 100 86.8741599462366 96.2496376399888 90.6239798890119 90.6239798890119 94.9996443747079 98.4374499984 90.9372099907197 38.7481465189137 4.37255193732959 2.18499593589596 4.06004393712479 1.55997993548634 4.99756793773921 1.87248793569116 4.3720183195312 1.87185989951678 4.05942990175686 2.18478856233895 4.6850599375344 1.87248793569116 4.05975610902471 2.80938990047681 3.43440990111683 2.18453814781512 -1"/>
<PARAM NAME="Selected" VALUE="\\WIN2003\.NET CLR Memory(w3wp)\# Total committed Bytes"/>
</OBJECT>

Version author:
Mattias Lövström

EPiServer version

All