Monday, March 21, 2011

Compiled string.Format

Ever looked at Performance wizard and seen a significant potion being taken by string.Format? one day I have and decided to try and find a faster string.Format, it took a couple of hours and I came up with a way to cache the static and dynamic portions of the string which speed up things by a bit, but not enough to permanently integrate it into the project, maintenance time is not worth it. 


But if you're program relies heavily on string.format and the difference you have with 1 million executions is worth the 100-500 ms you'll save on it, have fun.


For example, a formatted string with 5 parameters times 1 million executions is ~2350 ms with my method and ~2800 ms with string.Format.


You can find the project here:
https://github.com/drorgl/ForBlog/tree/master/FormatBenchmarks


and the benchmarks here:
http://uhurumkate.blogspot.com/p/stringformat-benchmarks.html

The categories in the graph are a number of parameters the formatting needs to parse.

Saturday, March 19, 2011

Network information and tracing


We all have these times where we offer help or asked to do things outside our job description, some more reasonable then others, this tool was written to help those times and shorten the time we did something else so we can get back to the more interesting stuff.

Here are some of the features:

Fully multithreaded
Tabbed browser-like environment
Whois client, you can add your servers easily!
Traceroute and see estimated distances between nodes.
Watch your routes on the globe!
ASN/Peers information
Get Abuse emails for your hostnames
Check if your IPs/Hostnames are in spam blacklists
a DNS tracing tool.

GeoLite City needs to be updated for the maps to show correct locations.

https://sourceforge.net/projects/netinfotrace/


I felt creative that week, so I even did a website for this project! Be gentle, I'm a developer, not a designer :-)

http://netinfotrace.sourceforge.net/

Thursday, March 3, 2011

Using FTP to Sync


I've needed a script to sync a remote folder, its a folder that is used to get updates dropped into it but I wanted to download only non existing files, I've found a script on dostips, which I've used for a while until I've stumbled on their note "Since all files are passed into the FTP`s MGET command there might be a limit to the number of files that can be processed at once."

So I've changed the script, it batches downloads, you can specify how many files to download in a batch, but 10 is the most stable (it depends on the filename length).


@Echo Off
REM Taken from http://www.dostips.com/DtTipsFtpBatchScript.php
REM 2011-02-15 - Dror Gluska - Added limit of files to download in a batch

if [%1]==[] goto usage

set servername=%~1
set username=%~2
set password=%~3
set rdir=%~4
set ldir=%~5
set filematch=%~6
set maxfilesperbatch=%~7


REM -- Extract Ftp Script to create List of Files
Set "FtpCommand=ls"
Call:extractFileSection "[Ftp Script 1]" "-">"%temp%\%~n0.ftp"
rem notepad "%temp%\%~n0.ftp"

REM -- Execute Ftp Script, collect File Names and execute batch download
setlocal ENABLEDELAYEDEXPANSION

set /a nocount=0
set /a totalfiles=0

Set FileList=
For /F "tokens=* delims= " %%A In ('"Ftp -v -i -s:"%temp%\%~n0.ftp"|Findstr %filematch%"') Do (
    call set filename=%%~A
    
    if Not Exist "%ldir%\!filename!" (
        echo [!filename!] added to batch
    
        set /a nocount+=1
        set /a totalfiles+=1
        call Set FileList=!FileList! ""!filename!""
        
        if !nocount! EQU !maxfilesperbatch! (
            
            if !nocount! gtr 0 (
                echo Downloading !totalfiles! files...
                Call:downloadFiles "!FileList!"
                call set /a nocount=0
                call Set FileList=
            )
        )
    )
)

if !nocount! gtr 0 (
    echo Downloading !totalfiles! files...
    Call:downloadFiles "%FileList%"
)

endlocal

exit /B 0

GOTO:EOF

:downloadFiles filenames
SETLOCAL Disabledelayedexpansion
Set "FtpCommand=mget "
call set "FtpCommand=%FtpCommand% %~1"

call set FtpCommand=%FtpCommand:""="% 

rem echo %nocount% files to download

Call:extractFileSection "[Ftp Script 1]" "-">"%temp%\%~n0.ftp"
   rem notepad "%temp%\%~n0.ftp"

REM -- Execute Ftp Script, download files

ftp -i -s:"%temp%\%~n0.ftp" > nul
Del "%temp%\%~n0.ftp"

exit /b

:usage

echo %0 ^<server^> ^<username^> ^<password^> ^<remotepath^> ^<localpath^> ^<maximum files^>
exit /B 1

goto:EOF


:extractFileSection StartMark EndMark FileName -- extract a section of file that is defined by a start and end mark
::                  -- [IN]     StartMark - start mark, use '...:S' mark to allow variable substitution
::                  -- [IN,OPT] EndMark   - optional end mark, default is first empty line
::                  -- [IN,OPT] FileName  - optional source file, default is THIS file
:$created 20080219 :$changed 20100205 :$categories ReadFile
:$source http://www.dostips.com
SETLOCAL Disabledelayedexpansion
set "bmk=%~1"
set "emk=%~2"
set "src=%~3"
set "bExtr="
set "bSubs="
if "%src%"=="" set src=%~f0&        rem if no source file then assume THIS file
for /f "tokens=1,* delims=]" %%A in ('find /n /v "" "%src%"') do (
    if /i "%%B"=="%emk%" set "bExtr="&set "bSubs="
    if defined bExtr if defined bSubs (call echo.%%B) ELSE (echo.%%B)
    if /i "%%B"=="%bmk%"   set "bExtr=Y"
    if /i "%%B"=="%bmk%:S" set "bExtr=Y"&set "bSubs=Y"
)
EXIT /b


[Ftp Script 1]:S
!Title Connecting...
open %servername%
%username%
%password%

!Title Preparing...
cd %rdir%
lcd %ldir%
binary
hash

!Title Processing... %FtpCommand%
%FtpCommand%

!Title Disconnecting...
disconnect
bye


Example:


ftpsync 10.0.0.1 "anonymous" "email@email.com" "/" ".\Temp" ".txt" 10


You have to specify all the parameters.

Unfortunately you have to specify some kind of regex file match, otherwise it will attempt to download the ftp status messages too, could be a problem if there are more status messages than number of files in a batch.


Checking SQL Load by Top Queries

So you're checking you SQL server, you see the CPU is very high for long periods of time or your users complain the database is slow, where can you start looking?

Well, we can check the load and what is causing it.

The basic query is:


SELECT TOP 500
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
creation_time,
last_execution_time,
execution_count,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time

FROM sys.dm_exec_query_stats
ORDER BY sys.dm_exec_query_stats.last_elapsed_time DESC


We can modify the order by or where clauses so it will give the results for our needs.

I would recommend adding 


where execution_count > 100


So it will weed out the single long-running queries from the result set or you can check for execution_count = 1 if you suspect a programmer abuses dynamic SQL.

Then we can modify the order by 


ORDER BY total_worker_time/execution_count desc


so it will give us the mostly used/long running queries. 

You can find out more in the documentation.