Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Archives: August 2010

Return top 20 queries by Average CPU

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

3 comments, 2,007 reads

Posted in DatabaseExpertise.com on 31 August 2010

How to fix Orphaned Users

 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

2 comments, 927 reads

Posted in DatabaseExpertise.com on 31 August 2010

Determining the no of processor cores in your SQL Server

SELECT cpu_count/hyperthread_ratio AS cores
   FROM sys.dm_os_sys_info;

Read more

0 comments, 632 reads

Posted in DatabaseExpertise.com on 31 August 2010

Hunting down those expensive SPs

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

0 comments, 228 reads

Posted in DatabaseExpertise.com on 19 August 2010

Undocumented Stored Procedures

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

0 comments, 887 reads

Posted in DatabaseExpertise.com on 19 August 2010

Neat script to monitor activity

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

0 comments, 275 reads

Posted in DatabaseExpertise.com on 19 August 2010

Synchronizing SQL user in Database Mirroring- Abi Chapagai

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

0 comments, 1,082 reads

Posted in DatabaseExpertise.com on 16 August 2010

Different High Availability Solutions in SQL Server

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

5 comments, 3,013 reads

Posted in DatabaseExpertise.com on 12 August 2010

Different High Availability Solutions in SQL Server – By Abi Chapagai

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

0 comments, 1,641 reads

Posted in DatabaseExpertise.com on 12 August 2010