Category / Uncategorized

by Arvind Shyamsundar

DB Mirroring Tips

 

Here are some essential things to consider when trying to tune a DB mirroring setup. They revolve round the following concepts:

  • The effect of Virtual Log Files (VLFs) on recovery and therefore failover times
  • The interplay (or lack thereof) between mirroring and some other SQL Server engine features
  • Network speed and issues

 

  1. Be on the latest available Service Pack and Cumulative Update for SQL 2005 or 2008. There are many issues related to mirroring which are fixed in these updates. Some of the key ones which I have seen are given below.
  • The remedial steps for this set of problems revolves round fixing the Virtual Log File Fragmentation issues, such as what is described by Kimberly Tripp. The relevant blog posts are:
  • You should also be aware of the article in http://support.microsoft.com/kb/937531 (The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005)
  • If you are using MSDTC transactions with a database which is mirrored, please consider fact that it is not officially supported, as the below articles.
  • For the networking side, the key things to consider are given below.

I will keep adding to this post as I receive additional information. In the meantime, I hope you stay informed and therefore hopefully avoid known issues with mirroring.

If you like this post, please take a few seconds to leave a comment and do rate the post!

by Arvind Shyamsundar

Licensing changes in SQL Server 2008 R2

In case you are not aware, there have been some significant licensing changes in the SQL 2008 R2 release, especially with the introduction of the DataCenter edition as the top-of-line edition. SQL Books Online documents the facts, as always, but sometimes it’s convenient to just understand what are the deltas. Towards this, I was made aware of the following article from Directions on Microsoft:

http://www.directionsonmicrosoft.com/samples/Licensing_SQL_Server_2008.pdf

This article contains a nice tabular summary of the facts. I found it useful, and I hope you do as well!

Many thanks to the folks at Directions on Microsoft.

by Arvind Shyamsundar

PowerGUI tip

I recently faced a situation where the free space on my laptop disk was suddenly depleted. After some searching, I found that PowerGUI keeps its scripteditor logs at “C:Users<username>AppDataLocalQuest SoftwarePowerGUILogs”. If you use PowerGUI a lot, you might experience a slow but steady exhaustion of disk space due to seemingly orphaned log files in that folder.

If you are like me, you may want to keep cleaning out this folder to keep space available.

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!

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!

by Arvind Shyamsundar

Web installer for obtaining / installing debugging tools (WinDbg)

Recently there has been a change to the way you obtain downloads of the WinDbg family of Debugging Tools for Windows. The classic link for the direct download of these installers was http://www.microsoft.com/whdc/devtools/debugging/default.mspx. Unfortunately, that link only directly makes available the older versions of the debugger (circa March 2009.) For the latest debugger it recommends you download the Windows Development Kit, which is around 700MB in size.

Relief is at hand, though: there is a Web installer for the WinSDK which allows you to just select those items of choice, such as the Debugging Tools for Windows. The link for the SDK is at http://msdn.microsoft.com/en-us/windows/bb980924.aspx

Do note that, this will INSTALL the debugging tools (which is not a bad thing, you can later XCOPY it to your target server) to the default location such as “C:Program FilesDebugging Tools for Windows (x64)” (in my case this is the path, as I’m using x64 OS.) But it does help save some download time.

by Arvind Shyamsundar

Programmatically Getting version of loaded assembly

I recently had to determine at runtime the version of the assembly containing a particular type. After some searching I hit upon this:

System.Reflection.Assembly.GetAssembly(typeof(MyNamespace.MyType)).GetName().Version.ToString()

Do note that this will only work if the assembly containing the referenced type is already loaded.

Updated based on a comment, this also does the job equally well:

typeof(MyNamespace.MyType).Assembly.GetName().Version.ToString()

Thank you!