Month / June 2010

by Arvind Shyamsundar

Reconstructing the SQL Server Best Practices Toolbox

Till some time ago, I used to direct my customers to http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx for obtaining scripts which use the DMVs in SQL 2005/2008 to retrieve very commonly used performance troubleshooting related information. Those scripts were contributed by Microsoft’s SQL CAT team.

Unfortunately that link no longer works correctly. Luckily though the scripts are still available at different places on Script Center, and here is quick compilation of those while the original link is fixed.

SQLOS / Execution Model

List SQLOS Execution Model Information

List Runnable Queues

List Scheduler Wait List Information

Wait Statistics

List Top Wait Types for a Workload

Retrieve Waitstat Snapshots

Compare Signal Waits and Resource Waits

List Statements from a Specified Waiter List

 

Query Compilation

Determine CPU Resources Required for Optimization

List Recompiled Statements

Compare Single-Use and Re-Used Plans

Query Statistics

Retrieve Statements with the Highest Plan Re-Use Counts

Retrieve Statements with the Lowest Plan Re-Use Counts

List Statements with the Highest Execution Counts

List Statements By Input/Output Usage

List Statements With the Highest Average CPU Time

Query Execution

Retrieve a SQL Statement with a Specified .SQL_Handle

Retrieve SQL Text and XML Plans

List Currently-Executing Statements

List Real Time Tempdb Task Usage

List Real-Time Tempdb Statements

List Currently-Executing Parallel Plans (the script at this link is actually the script for the next item :-))

List Cached Plans Where Worker Time Exceeds Elapsed Time (the script at this link is actually the script for the previous item :-))

I/O and Buffer cache

Calculate Average Stalls

Retrieve Buffer Counts by Object and Index

Indexes

List Indexes With the Most Contention

Retrieve Tables, Indexes, Files, and File Groups Information

Determine Index Cost Benefits

Retrieve Object and Index Fragmentation Information

Analyze Index Statistics

Identify Missing Indexes

Retrieve Index Usage Statistics

Retrieve Indexes Not Used Since the Last Recycle Time

List Rarely-Used Indexes

Locking and Blocking

Report Blocker and Waiter SQL Statements (sp_block_info)

 

I hope this compilation is useful to you!

Advertisement
by Arvind Shyamsundar

Performance Dashboard Reports in SQL Server 2008

While Activity Monitor in SQL 2008 does a good job depicting waiting tasks, top N queries etc.; many DBAs are simply too familiar with the erstwhile SQL Server 2005 Performance Dashboard Reports. While officially Microsoft does not support the usage of these reports with SQL 2008, I found two links of interest which will help you get these reports up and running in 2008. But please keep in mind: this is AS-IS!

Link 1: http://blogs.msdn.com/b/vascov/archive/2008/09/30/using-performance-dashboard-with-sql-server-2008.aspx

Link 2: http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx

You have to perform BOTH the fixes to get the reports working. In Link #2, the specific place to look for is a comment which says ‘line number 3271’.

DO make a BACKUP of your recent_cpu.rdl file before you attempt the fix in Link #2.

[Update 26 Jun 2010] In addition, if you get an error related to an “overflow at runtime” then refer to the other fix in Link #2 above, the comment says ‘Because DATEDIFF returns and int once’. Look for the fix from David.

Hope this is useful to some folks out there! If you liked the post, please do leave a comment and / or rate the post.

by Arvind Shyamsundar

Windows Mobile Device Center: missing Icons

I recently acquired a new Windows Mobile phone and was using the Windows Mobile Device Center to set up the partnership with my laptop. In the process, I came to a stage where I had to activate IRM as per the steps in Activate Information Rights Management by using ActiveSync.

I entered my domain credentials and then pressed the button, but what I got back was a rather cryptic error exception with the message: “The operation completed successfully”. Successfully?

Luckily this is was a .NET exception wrapping up the underlying Win32 exception, and the .NET runtime displayed the stack trace:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.ComponentModel.Win32Exception: The operation completed successfully

at Microsoft.WindowsMobile.ImageUtils.LoadIcon(String fileName, IconSize iconSize)

at Microsoft.WindowsMobile.DeviceManager.Partnership.GetImage(IconSize iconSize)

at Microsoft.WindowsMobile.DeviceCenter.ProgressControl.set_WorkType(Work value)

at Microsoft.WindowsMobile.DeviceCenter.ProgressControl..ctor(String titleText, String descriptionText, Work workType, OkCancelControl okCancelControl)

at Microsoft.WindowsMobile.DeviceCenter.Six.IrmActivationControl.SaveContentSettings(MainHost mainHost, AnimatedControl outgoingControl, String errorTitle, Boolean removeControl)

at Microsoft.WindowsMobile.DeviceCenter.Six.IrmActivationControl.OnOk(Object sender, OkClickedEventArgs e)

at Microsoft.WindowsMobile.DeviceCenter.OkCancelControl.ClickSave(Object sender, EventArgs e)

at Microsoft.WindowsMobile.DeviceCenter.OkCancelControlButton.button_Click(Object sender, EventArgs e)

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.PerformClick()

 

After some debugging with WinDbg, I found that my “sync.ico” file under C:UsersarvindshAppDataRoamingMicrosoftActiveSyncProfiles<XYZPQR> was in some way “corrupt” or “not good”, causing the icon loading to fail and eventually the above exception. So I went ahead and put in a “good” sync.ico file in the above folder, restarted the sync process and then I was able to proceed with IRM activation. It turns out that this icon is used in the animated screen which displays when IRM is being activated, and that was the reason why the un-readable icon was so critical and caused the above exception.

I hope this esoteric tip is useful to someone out there! If you like it, please leave a comment and / or rate this post, please!