I’ve recently used this when troubleshooting some unexplained high CPU load on my server:

select top 20

st.objectid, st.dbid, total_worker_time/execution_count AS AverageCPUTime,

CASE statement_end_offset

WHEN -1 THEN st.text

 ELSE

SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2)

END AS StatementText

from  sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY AverageCPUTime DESC

Read more

 To view if there are any orphaned users, run this query against the DB;

 sp_change_users_login ‘report’

To fix;

 sp_change_users_login ‘update_one’, ‘dbUser’, ‘sqlLogin’

Read more

SELECT cpu_count/hyperthread_ratio AS cores

   FROM sys.dm_os_sys_info;

Read more

A query to find out which Stored Procedures are taking the longest to run ( in this example – the Top 10).

I’m sure there are many other ways but this is a quick and easy script I found.

SELECT TOP 10 obj.name, max_logical_reads, max_elapsed_time
FROM sys.dm_exec_query_stats A
CROSS APPLY…

Read more

Just a quick list of undocumented SPs, many of which are deprecated.

Note, use caution using some of these ! But others are very useful (e.g. sp_msforeachdb)

sp_checknames
sp_columns_rowset
sp_enumoledbdatasources
sp_fixindex
sp_gettypestring
sp_ms_marksystemobject
sp_msaddguidcolumn
sp_msaddguidindex
sp_msaddlogin_implicit_ntlogin
sp_msadduser_implicit_ntlogin
sp_mscheck_uid_owns_anything
sp_msdbuseraccess
sp_msdbuserpriv
sp_msdependencies
sp_msdrop_object
sp_msforeachdb
sp_msforeachtable
sp_msget_qualified_name
sp_msgettools_path
sp_msgetversion
sp_msguidtostr
sp_mshelpcolumns Read more

Scouring the web this week, I stumbled across SQL Server MVP Adam Mechanics and his script :-

Who is Active?

Its basically a  stored procedure that helps you monitor SQL Server activity quickly and efficiently. Bit like sp_who but with extras ! You can read more over here as… Read more

Synchronizing SQL user in Database Mirroring:

 If a login used in an application that involves database mirroring has SQL Login, there can be issues with login failure. While I was working with database mirroring set up between two databases in different server, I ran into an issue where I was… Read more

Different High Availability Solutions in SQL Server.

While working on finding different High Availability solutions, I did quite a bit of research on various methods of DR solutions and I wanted to share following technologies are available in SQL Server for us to implement DR solution. In this cutting edge… Read more

Different High Availability Solutions in SQL Server.

While working on finding different High Availability solutions, I did quite a bit of research on various methods of DR solutions and I wanted to share following technologies are available in SQL Server for us to implement DR solution. In this cutting edge… Read more