Level 400 Microsoft SQL Server Performance Monitoring and Tuning Webcast

Monday, January 25, 2010 Leave a comment

Ramesh Meyyappan’s webcast about Microsoft SQL Server Performance Monintoring and Tuning.

Full with practical examples and real world scenarios.

If you signup to the Email Updates you will receive the scripts used in the webcast.

Advertisements
Categories: Performance, SQL Server

Oracle Patching – PSU or CPU ?

Monday, September 28, 2009 Leave a comment

Any clue don’t use PSU (Patch Set Updates) ?

As described in Oracle Metalink Note 854428.1 switch back to CPU “…would require significant effort, and so is not advised”.

Please comment.

Performance Issues After Migration To SQL 2005

Sunday, September 13, 2009 Leave a comment

During reading support forums i found many issues related to performance after migrating to SQL 2005.

Some advice to increase performance:

– Rebuild indexes (DBCC DBREINDEX)

– Update statistics (SP_UPDATESTATS)

– Check the compatibility level of the database in question.

If the compatibility level is set to 80 (SQL 2000)  change it to 90 (SQL 2005).

The compatibility level is not really related to performance but needed for use new features released with SQL 2005.

Oracle Grid Control Repository Database ORA-0600 [17059]

Monday, April 27, 2009 Leave a comment

During the daily database check i found error ORA-0600 [17059] in the alert log of the repository database (Release 10.2.0.3, Linux x86].

Error occured during weekend and a high amount of trace- and dump-files are generated and fill up the file system.

Investigation at Oracle Support show me that the Oracle Bug 5705795 (Doc ID 4167271) caused the problem.

I download Patch 5705795 for our linux system.

 (You need Patch 5731535 if using windows).

Stopped agent, opmn and iasconsole, shutdown the repository database and applied the patch.

After restart all oracle processes and the repository database the error no longer occured in the alert log.

But this time i found another error in the log (ORA-07445 [lstclo()+45] [SIGSEGV] [Address not mapped to object].

In my case the error occured because i don’t shutdown ALL databases which shared the same ORACLE_HOME.

The second database was open and caused the problem.

After bouncing and restart this database the error was cleared.

Recommendable SQL Server Posts – July 2008

Thursday, July 17, 2008 1 comment

Very interesting post from James DiMauro on SQLServerCentral.com about recovering a database when transaction log deleted, no backup available and you couldn’t set the database in emergency mode.

Recover database when transaction log deleted

Categories: Troubleshooting

Troubleshooting SQL 2005 Performance Dashboard

Thursday, July 10, 2008 6 comments

Maybe some issues appear when using SSMS 2005 Performance Dashboard.

Found solutions for some problems at MSDN.

Because the whole thread is confusing i collect the solutions at this site to get a clear overview.

Note: As always try at your own risk. Make a backup of your files before try the solutions.

Issue 1 – Difference of two datetime columns caused overflow at runtime.

Open C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\setup.sql.

Search for following code:

sum(convert(bigint, datediff(ms, login_time, getdate()))) – sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

and replace this line with the code below.

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) – sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

Issue 2 – Blue Zone Problem (Server is running for 24 or more days).

Open C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\recent_cpu.rdl.

Go to line number 3271 and delete all lines till line number 3306.

Replace the delete lines with following code:

from (select s.session_id,
r.request_id,
s.login_time,
— s.host_name,
s.program_name,
s.login_name,
s.status as session_status,
s.last_request_start_time,
s.last_request_end_time,
s.cpu_time as session_cpu_time,
r.cpu_time as request_cpu_time,
— s.logical_reads as session_logical_reads,
— r.logical_reads as request_logical_reads,
r.start_time as request_start_time,
r.status as request_status,
r.command,
master.dbo.fn_varbintohexstr(r.sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(r.plan_handle) as plan_handle,
r.statement_start_offset,
r.statement_end_offset,
case
— Steve: Fixes begin here:
when convert(bigint, CAST ( DATEDIFF ( minute, start_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond,
DATEADD ( minute,DATEDIFF ( minute, start_time, getdate() ), Start_time ),getdate() ))
> 0
then convert(float, r.cpu_time) / convert(bigint, CAST ( DATEDIFF ( minute, start_time, getdate()) AS BIGINT)*60000
+ DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, start_time, getdate() ), Start_time ),getdate() )) else convert(float, 1.0) end
as avg_request_cpu_per_ms,
isnull (datediff(ms, case when r.start_time < @WithActivitySince then @WithActivitySince else r.start_time end, getdate()), 0)
as request_ms_in_window,
case when s.login_time > getdate() then convert(float, s.cpu_time) / (datediff(dd, s.login_time, getdate()) * cast(86400000 as bigint) + datediff(ms, dateadd(dd, datediff(dd, s.login_time, getdate()), s.login_time), getdate())) else convert(float, 1.0)
end as avg_session_cpu_per_ms,
convert(bigint,isnull(datediff(s, case when s.login_time < @WithActivitySince then @WithActivitySince else s.login_time end, case when r.request_id is null then s.last_request_end_time else getdate() end), 0)  )* 1000
as session_ms_in_window
from sys.dm_exec_sessions s
left join sys.dm_exec_requests as r on s.session_id = r.session_id  and s.session_id = 1
— Steve: Fixes end here
where (s.last_request_end_time > @WithActivitySince or r.request_id is not null)) as d
where (avg_request_cpu_per_ms * request_ms_in_window) + (avg_session_cpu_per_ms * session_ms_in_window) &gt; 1000.0</CommandText>
<QueryParameters>
<QueryParameter Name=”@WithActivitySince”>
<Value>=Parameters!WithActivitySince.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>DataSource1</DataSourceName>
</Query>

Issue 3 – Arithmetic overflow error converting expression to data type datetime.

This problem appear mostly when clicking the blue cpu zone.

Open C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\recent_cpu.rdl.

Search for WithActivitySince and replace the parameter from String to DateTime.

Orphaned User After Migration To SQL 2005

Monday, February 25, 2008 Leave a comment

After migrate SQL 2000 to SQL 2005 the SQL-Logins lost the relationship (Database <-> Server).

To fix the problem:

Find the SQL-Logins which orphaned: Exec sp_change_users_login ‘Report’

Run following statement to resolve the problem: Exec sp_change_users_login ‘UpdateOne’, ‘Name’, ‘Name’

Note: This will only work for SQL-Logins.

There is a parameter “Auto_Fix available. I don’t use it due to security reasons.