MS SQL: Procedures time execution

SELECT context, operation, count (total_length)
,avg(total_length) as avg_total
,min(total_length90) as total_length90
,max(total_length) as max_total
,avg(task_length) as avg_task
,min(task_length90) as task_length90
,max(task_length) as max_task
,(avg(total_length) - avg(task_length)) as diff_avg



FROM
(select operation, context, total_length, task_length,
case when total_length_dev = (min(total_length_dev) over(partition by operation, context)) then total_length else null end as total_length90,
case when total_task_dev = (min(total_task_dev) over(partition by operation, context)) then task_length else null end as task_length90
from
(select operation, context, total_length, task_length,
abs(90 -
(
cast((row_number() over (partition by operation, context order by total_length)) * 100 as float) /
(count(*) over (partition by operation, context))
)) as total_length_dev,
abs(90 -
(
cast((row_number() over (partition by operation, context order by task_length)) * 100 as float) /
(count(*) over (partition by operation, context))
)) as total_task_dev
from [wo_perf_log]
--where (end_time between '2010-02-25 09:12:30' and '2010-02-25 10:28:00')
)
t
) t
group
by
operation, context
order by total_length90 desc

Jmeter: Launch from the Command line

jmeter -n -t C:\JMeter_script.jmx -l log.csv

Jmeter: Garbage Collector

-XX:+PrintGCTimeStamps -XX:+PrintGCDetails -Xloggc:gc.log

MS SQL: DB size

-- db size
use tempdb
exec sp_spaceused

-- file size
select
sf.groupid,
sf.name,
sf.filename,
((sf.size*8192.0)/1048576) 'MBytes'
from
tempdb.sys.sysfiles sf
order by
sf.groupid,
sf.filename

IIS 7: Resolve problem with the site sharing

1. Go to %Systemroot%, normally C:\WINDOWS or C:\WINNT

2. Right-click the Temporary folder (mine is C:\WINDOWS\Temp) and select Sharing and Security. Select the Security tab and take a look at the list of “Group or user names:”

3. NETWORK SERVICE should be in the list. If it is not, click Add and type Network Service in the text box:

4. Make sure NETWORK SERVICE have Full Control in the Permissions list.

5. Click OK.

MS SQL: Cache cleaning

DBCC FREEPROCCACHE

MS SQL: Deadlocks monitoring

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'ARAM-GFF System Improvement' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'ARAM-GFF System Improvement'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name='ARAM_GFF_DeadlockTrace',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Automatic Deadlock-Trace',
@category_name=N'ARAM-GFF System Improvement',
@owner_login_name=N'NT AUTHORITY\SYSTEM', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [start trace] Script Date: 11/18/2009 01:08:11 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'start trace',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500
declare @fileName nvarchar(245)

set @fileName = N''C:\ARAM_GFF_Deadlocks'' + CONVERT(nvarchar(10), GETDATE(), 112) + REPLACE(CONVERT(nvarchar(10), GETDATE(), 108), '':'', '''')
exec @rc = sp_trace_create @TraceID output, 0, @fileName, @maxfilesize, NULL
if (@rc != 0) goto error
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setstatus @TraceID, 1
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
',
@database_name=N'master',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Auto',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081203,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: