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

Be Aware: Training Resource

Recently, I was informed about some very useful resources (some free webcasts and information about SQL performance tuning related training) at www.sqlworkshops.com

That initiative is headed by Ramesh Meyyappan, a former Microsoft employee who worked on the SQL Server Product team. I found Ramesh’s guidance insightful and refreshing. I would urge you to visit the site and decide for yourself.

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!

by Arvind Shyamsundar

Reportviewer and drillthrough

I was doing some testing the other day with a ReportViewer control hosted in a WinForms application to do local mode report processing. As some of my reports had a drillthrough / navigation option set, I had setup a set of DrillthroughEventHandler to ensure that the right datasources are bound to the report when the drillthrough reports are invoked.

The problem in my case was that on specific reports being invoked, the drillthrough would cause an exception inside ReportViewer and it would display:

An error occurred during rendering of the report.
Object reference not set to an instance of an object.

If this issue was random I would have suspected issues like the one in http://support.microsoft.com/kb/959595 but I was able to get this error consistently. On later troubleshooting it was clear that:

  • I had two DrillthroughEventHandler setup, but only one for each report should have been ‘active’.
  • Unfortunately the way you setup these handlers is you use the following construct:

reportViewer1.Drillthrough += new DrillthroughEventHandler(CorrectDrillthroughEventHandler)

  • Due to the way my code was structured, it turned out I would add the 2nd DrillthroughEventHandler to a report which never really needed it. So I ended up making sure that the unwanted handlers were registered first, just before the call to the registration of the correct one.

reportViewer1.Drillthrough -= new DrillthroughEventHandler(UnwantedDrillthroughEventHandler);

Doing this got rid of the exception and things worked fine for me. Of course, be sure that this is not the ONLY reason for the above exception. This was a specific case and I hope it might prove useful for someone who is using Drillthrough and multiple DrillthroughEventHandler.

If you found this useful, please do leave a comment! And if you are logged in, please do rate the post as well.

by Arvind Shyamsundar

Replication and Linked Servers

Problem

I recently hit upon an issue with trying to setup a linked server to an instance which was already a subscriber to a publication. When replication is setup, it actually creates a remote server for the subscriber. However that ‘remote server’ is not configured for data access. So if you try to use that server, you would end up with:

Msg 7411, Level 16, State 1, Line 1
Server ‘foosub’ is not configured for DATA ACCESS.

Also, any attempt to add a similarly named linked server would fail with the error message below:

EXEC master.dbo.sp_addlinkedserver @server = N’foosub’, @srvproduct=N’SQL Server’

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82
The server ‘foosub’ already exists.

Troubleshooting

Next, I tried to add a linked server (using the SQL Native Client) but with a different name (MYSRV) but pointing to the right server (foosub). My initial attempt yielded the following error:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "MYSVR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "MYSVR" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)

Solution

The problem turned out that I had not used the right ‘provider’ string. Here is the script which finally worked for me:

EXEC master.dbo.sp_addlinkedserver @server = N’MYSVR’, @srvproduct=N’foosub’, @provider=N’SQLNCLI10′, @provstr=N’Server=foosub;Database=master;Trusted_Connection=yes’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’MYSVR’, @locallogin = NULL , @useself = N’True’
GO

Or if you are more comfortable using the SQLOLEDB provider, here’s a sample:

EXEC master.dbo.sp_addlinkedserver @server = N’MYSRV’, @srvproduct=N’SQLOLEDB’, @provider=N’SQLOLEDB’, @datasrc=N’foosub’, @provstr=N’Data Source=foosub;Initial Catalog=master’, @catalog=N’master’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’MYSRV’, @locallogin = NULL , @useself = N’True’
GO

You can also do this from the SSMS GUI by using ‘‘SQLNCI10’ or ‘SQLOLEDB’ as the Provider.

With this, I can subsequently access remote tables as such:

SELECT * FROM MYSVR.master.sys.tables

Hope this is useful! Please leave a comment if you find it useful.