Esoteric

Opinion Poll: Are PRINT statements considered harmful?

Arvindsh — Thu, 24 May 2012 17:08:43 GMT

Today during a discussion a point came up around the role of PRINT statements in production code. While most data access today is routed through a data access layer (typically .NET or JDBC) and is focussed on consuming result sets (or executing U/I/D nonquery stuff) we were wondering on what you use PRINT statements for. In the long past, I would have said that the print statement is probably the best way for debugging, but in today's world with easy access to the T-SQL debugger, a developer is probably much better off without PRINT.

With SqlClient, we consume the PRINT events using the InfoMessage event, but in practice I wonder how many of you actually rely on that. Please comment on this post to share your experiences with PRINT and if you think it is good / bad / evil in today's world :-)

Implementing MDX Drillthrough in SSRS

Arvindsh — Mon, 19 Mar 2012 15:47:00 GMT

Let’s say for some reason your users want to implement MDX DRILLTHROUGH statement (equivalent to the cube action provided by most OLAP browsers) in SSRS. Here’s a step-by-step way to implement this.

For the purposes of this walkthrough, we are using the standard AdventureWorks Analysis Services database. You can obtain this and the related DW database from http://msftdbprodsamples.codeplex.com/. Do note that you have to manually deploy and process the database after opening the Adventure Works.sln file from its default location of C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks Analysis Services Project\enterprise

Our objective is to implement two reports:

1. The parent report will list the top 10 products along with their sales amount totals

2. The child (drillthrough) report will display a raw DRILLTHROUGH for the first 500 rows and get back the product quantity of the individual order, along with the order number and date the order was placed

Step 1. Verify the AS database is deployed and processed

Step 2. Here is our (pretty basic) MDX query for the Top 10 listing for current products:

WITH MEMBER PKey AS
[Product].[Product].CurrentMember.Properties("Key0")
select {[Measures].[Reseller Sales Amount], PKey } on 0,
(TopCount([Product].[Product Categories].[Product], 10, [Measures].[Reseller Sales Amount])) on 1
from [Adventure Works]
where [Product].[Status].[Current]

Why the calculated member PKey? You will see later.

Step 3. Capture the DRILLTHROUGH query in Profiler

Since writing the DRILLTHROUGH statement by hand can be a bit of a challenge, here it is captured from SQL Profiler:

DRILLTHROUGH Select ([Measures].[Reseller Sales Amount],[Product].[Product].&[358]) on 0 From [Adventure Works] RETURN [Reseller Sales].[Reseller Order Quantity],[$Employee].[Employee],[$Delivery Date].[Date],[$Sales Territory].[Sales Territory Region],[$Reseller Sales Order Details].[Sales Order Number]

You can see the product key is highlighted. This is actually the key column syntax as can be seen from the ampersand prefix. So now you know why we selected the PKey calculated member in the previous query.

Step 4. Create the parent report

The first step is to create a shared data source to the SSAS database:

Next, we actually create the report. We select the New Report option and use the above shared data source. In the Query Designer, we switch to Query Mode and type in the MDX query we developed earlier:

Once this is done, select Tabular report, and move all the columns into the Details section of the report. Title the report as MainReport.

In the generated report, delete the PKey column.

Step 5. Create the drillthrough report

This is the tricky part. Normally, the MDX query designer does not support DRILLTHROUGH SELECT syntax. Hence we need to use a workaround. Please note that this workaround may not be officially supported by Microsoft.

We start the same way, by creating a new report and referencing the shared data source. But when you launch the query designer please switch the query type to DMX instead of MDX.

After this is switched, also switch to query mode as done before. In the space at the bottom, type in the ‘DMX’ query which is actually our MDX DRILLTHROUGH SELECT query. However now we do need to parameterize the query to accommodate the PKey parameter:

DRILLTHROUGH MAXROWS 50 Select ([Measures].[Reseller Sales Amount], StrToMember('[Product].[Product].&[' + @PKey + ']')) on 0 From [Adventure Works] RETURN [Reseller Sales].[Reseller Order Quantity],[$Employee].[Employee],[$Delivery Date].[Date],[$Sales Territory].[Sales Territory Region],[$Reseller Sales Order Details].[Sales Order Number]

Before you can click on OK though, you need to setup the parameter as well. You do that by clicking on the Query Parameters button:

We provide a dummy value of 394 so that the fields can be retrieved. The actual value, of course, will be provided by the drillthrough action later.

Select the tabular layout and move all the fields to the Details section. Name the report DrillThroughReport.rdl. Finally, set the report parameter PKey type as Hidden:

Step 6. Create the drillthrough action

Back to MainReport.rdl, right click on the Product text box and select Text Box properties. In the Action tab, you need to set up the Drillthrough (SSRS this time… don’t get confused by the similar term )

We also change the hyperlink look and feel for good measure:

And that does it for our reports:

Final Notes

How ‘legitimate’ is it to masquerade a MDX DRILLTHROUGH as a DMX query? As I said before, this is not officially supported. You can refer the to the MS Connect posting at http://connect.microsoft.com/SQLServer/feedback/details/126175/reporting-services-drillthrough-mdx-queries for the ‘won’t fix’ response.

The main reason for my documenting the approach above is that in the specific case I was interested in, the MAXROWS clause is very important. Of course one can argue that using MDX the same can be achieved, but then it is a matter of preference in the end.

BTW, Chris Webb also shares his perspectives on this at http://cwebbbi.wordpress.com/2009/06/16/implementing-analysis-services-drillthrough-in-reporting-services/

If you liked this posting, please rate it! In any case please do take a minute and leave comments, questions etc.

.NET Debugging Quick Start

Arvindsh — Wed, 14 Mar 2012 16:12:59 GMT

Many a time I am asked how to get a head start with debugging .NET issues – hangs / crashes and most commonly, memory issues. So I assembled this list of resources which helps you get started. In addition, if you are a Premier Support subscriber, you can avail of our most excellent .NET Debugging WorkshopPLUS conducted by my team (Premier Field Engineering).

The Production Debugging for .NET Framework Applications guide (http://msdn.microsoft.com/en-us/library/ee817663.aspx)
Maoni Stephens blog (covers GC and managed heap) http://blogs.msdn.com/b/maoni/
Sample chapter from Advanced .NET Debugging book: http://advanceddotnetdebugging.com/chapter.htm
.NET Framework Libraries source code (http://referencesource.microsoft.com/netframework.aspx)
Debugging tools for Windows: http://www.microsoft.com/whdc/devtools/debugging/installx86.mspx
DebugDiag 1.2: http://www.microsoft.com/download/en/details.aspx?id=26798
KB article for DebugDiag 1.2: http://support.microsoft.com/kb/2580960
DebugDiag 1.1 white paper: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23521
DebugDiag 1.1: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=28bd5941-c458-46f1-b24d-f60151d875a3&displaylang=en
PSSCOR2 debugger extension: http://www.microsoft.com/download/en/details.aspx?displayLang=en&id=1073
PSSCOR4 debugger extension: http://www.microsoft.com/download/en/details.aspx?id=21255
SOSEx debugger extension: http://www.stevestechspot.com/
Public Labs from Tess Fernandez: http://blogs.msdn.com/b/tess/archive/2008/02/04/net-debugging-demos-information-and-setup-instructions.aspx
Tess Fernandez at Oredev conference (video): http://oredev.org/prod/oredev/site.nsf/docsbycodename/session?opendocument&sid=12201594012B3B23C125759200289A81&track=5EA1ADD99261C8A5C12575A500494952&day=3
Visual Studio 2010 IntelliTrace: https://channel9.msdn.com/tags/intellitrace/ and http://ecn.channel9.msdn.com/o9/pdc09/ppt/FT16.pptx

I hope you find the links useful and if you do I would appreciate some comments and / or you rating this post!

Failover cluster (group) maximum failures limit

Arvindsh — Fri, 09 Mar 2012 13:13:00 GMT

My colleague reported that during testing forced failover for a SQL database engine instance, it just ‘failed’ and refused to fail over to the other node in a 2-node cluster. The failure in this case was initiated by shutting down the local service for the clustered instance – which is tantamount to failing the clustered instance itself.

This behavior was slightly unexpected. After some research, we traced it to the ‘Maximum failures in the specified period’ setting at the cluster group (service application) level:

It turns out that the testing activity involved some failures already due to which the above limit was already reached. Subsequently, when the SQL service level failure was initiated, the cluster service did not fail it over to the other node and left it in the failed state.

During this investigation I found some articles which are very useful in this:

http://support.microsoft.com/kb/947712 – this is the primary article which describes the failure limit and the associated failover logic
http://support.microsoft.com/kb/950804 – this article explains an UI issue in Win2008 cluster where the above maximum failure limit is displayed incorrectly (n-1)
http://support.microsoft.com/kb/228923 – this advanced article explains the RetryPeriodOnFailure property which changed from Windows 2003 (where it was ‘disabled’) to 60 minutes in Windows 2008+.

Moral of the story: When performing failover drills or testing, it may be appropriate to increase this value to a higher number, such as 5 or 10 for the duration of the testing. Subsequently the value may be reset back to 1 or 2.

FYI when you look at the cluster log, this is the message which is recorded when the limit has been reached (and is therefore disallowing restarts):

0000088c.00001038::2012/03/07-06:09:09.834 WARN [RCM] Not failing over group <groupname>, failoverCount 8, failover threshold 4294967295, nodeAvailCount 1.

Hope this helps!

MDX Studio Quick Tips

Arvindsh — Thu, 24 Nov 2011 11:26:18 GMT

Recently I had to use MDX Studio for some MDX query tuning. 2 quick tips:

Latest download link for MDX Studio can be obtained from the support forum at http://ssas-info.com/forum/3-mdx-studio/
The earlier versions of MDX Studio are referenced from Mosha’s website at http://www.mosha.com/msolap/mdxstudio.htm

Also very quickly, if you are finding that the Perfmon section in MDX Studio shows 0 for most values such as ‘Cells calculated’ then try running the MDXStudio.exe with the ‘Run as Administrator’ option.

Also take a look at Ashwani Roy’s SQL Bits session ‘Supercharge MDX performance using MDX Studio’ which is available from https://www.sqlbits.com/Sessions/Event6/supercharge_mdx_performance_using_mdx_studio

TEMENOS T24 Core Banking Optimized on Microsoft SQL Server Database Platform

Arvindsh — Wed, 02 Nov 2011 16:25:27 GMT

I found these links today in response to a customer question around 'real world' benchmarks for financial applications running on SQL Server. These benchmarks describe the TEMENOS T24 Core Banking system, which is probably as 'real world' as it gets.

http://blogs.technet.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-85-48-Files/4617.Benchmark-Results-for-Temenos-T24--with-SQL-Server-2008-R2-on-Intel_2D00_based-NEC-Servers.pdf

http://download.microsoft.com/download/B/2/3/B23E0B02-AA00-4921-8A76-B4384B6197DD/sql-temenos-t24.pdf

Parallel Data Warehouse resources

Arvindsh — Thu, 28 Apr 2011 06:23:33 GMT

I’ve recently started learning about SQL Server Parallel Data Warehouse (PDW) appliance. In the course of learning, I came across some links and resources in connection with this appliance, which I will share in this blog post. I will keep it updated as much as possible.

Learning resources:

Start your learning by looking at the collateral from http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/pdw.aspx
Free e-Learning clinic ‘Clinic 10333: Introduction to Microsoft SQL Server 2008 R2 Parallel Data Warehouse’: https://www.microsoftelearning.com/eLearning/courseDetail.aspx?courseId=187496&tab=overview
SQL PDW blog from our Microsoft PFEs working on PDW: http://www.sqlpdw.com/
Matt Peebles’ excellent deep-dive talk about PDW at TechEd: http://www.msteched.com/2010/NorthAmerica/BIE309
Jeff Spiller on PDW and FastTrack best practices: http://www.msteched.com/2010/NorthAmerica/BIE306
Microsoft IT sharing their experience about a 90TB PDW implementation: http://technet.microsoft.com/en-us/library/gg447063.aspx and http://technet.microsoft.com/en-us/edge/deploying-sql-server-2008-r2-parallel-data-warehouse-at-microsoft.aspx
Fairly detailed spec on what constitutes HP’s PDW implementation: http://h18004.www1.hp.com/products/quickspecs/13830_na/13830_na.pdf and http://h18004.www1.hp.com/products/quickspecs/13830_na/13830_na.html

Community blog posts:

My friend Shailendra has blogged about the positioning of PDW and FastTrack: http://www.abhyast.com/abhyastcom/post/SQL-Server-Fast-Track-vs-Parallel-Data-Warehouse-(PDW).aspx
Adam Jorgensen’s post: http://adamjorgensen.wordpress.com/2010/07/29/in-depth-parallel-data-warehousing-pdw/
Richard Waymire’s blog is useful: http://www.richardwaymire.com/wordpress/
Joe Chang has a fairly detailed post: http://sqlblog.com/blogs/joe_chang/archive/2011/03/10/parallel-data-warehouse.aspx

I will continue to gather links and post them here as I come across them. In the meantime, I hope the above help you!

SQLDiag Configuration Tool released

Arvindsh — Thu, 28 Apr 2011 04:27:36 GMT

My colleague and friend, boB Taylor (the spelling is not a typo – please visit http://www.opsvault.com/meet-our-contributors and see why) has recently released a GUI tool which allows you to configure the XML files required for SQLDiag to capture specific types of events, counters etc.)

Download it from http://sdct.codeplex.com

Please review, test, blog about it and provide feedback as you see fit.

bOB blogs at OpsVault. His posts can be easily retrieved from http://www.opsvault.com/author/bobtay/

OPTION(RECOMPILE) redux (a.k.a. Parameter Embedding Optimization not working)

Arvindsh — Tue, 15 Feb 2011 09:21:06 GMT

A long time ago, I had blogged about the perils of ‘wildcard’ query patterns. As a response to one of the comments in that post, I learnt about a new optimization introduced in SQL 2008 wherein the OPTION (RECOMPILE) hint would help in these kind of cases. Ever since then, I had used this in some customers; but recently, I found that on SQL 2008 R2 and even in SQL 2008 SP1, the behavior was not working correctly.

Recently, I found the reason for this issue from some other sites, and I also found the official name for this enhancement: Parameter Embedding Optimization. It turns out that, based on some bug reports, this optimization was disabled starting in SQL 2008 CU4 (and also disabled in SQL 2008 R2 RTM). The fixes for the issue are in SQL 2008 SP1 CU5 and SQL 2008 R2 CU5 respectively.

Here are the links which cover the details, and I hope they help you!

http://blogs.msdn.com/b/grahamk/archive/2009/11/18/changed-behaviour-of-option-recompile-syntax-in-sql-server-2008-sp1-cumulative-update-5.aspx
http://support.microsoft.com/kb/976603/ (FIX: Different results may be returned when you concurrently run the same query with the RECOMPILE option in SQL Server 2008)
http://www.sommarskog.se/dyn-search-2005.html

window.open without address bar

Arvindsh — Wed, 09 Feb 2011 06:36:23 GMT

Recently we were struggling with trying to figure out how to suppress the address bar for a popup window opened using JavaScript’s window.open method. Normally, there is an option there which allows you to specify ‘location=no’ and the address bar is expected to be hidden. However, no matter what we tried, this did not work for us.

Later on I chanced upon an Internet Explorer setting which actually controls the final display. There is a security setting ‘Allow websites to open windows without address or status bar’:

This setting needs to be set to ‘Enable’ for the respective zone (such as Trusted sites etc.) for the address bar display to be suppressed.

Hope this hint helps!

LinkedIn Group for Premier Field Engineering India

Arvindsh — Tue, 01 Feb 2011 04:15:40 GMT

My team, Premier Field Engineering (India) recently created a group on LinkedIn (http://www.linkedin.com/groups?gid=3761475&trk=hb_side_g) which we will use to share our experiences, upcoming offerings, career opportunities and more. Membership is open to all, so I welcome you to take this opportunity to connect with us!

DB Mirroring Tips

Arvindsh — Wed, 22 Dec 2010 04:45:35 GMT

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

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.

http://support.microsoft.com/kb/947462/ (FIX: Error message when you start a database mirroring session in SQL Server 2005: "Communications to the remote server instance 'TCP://<ComputerName>:<PortNumber>' failed before database mirroring was fully started")
http://support.microsoft.com/kb/979042 (FIX: The principal database is not recovered if the database has a large number of virtual log files in SQL Server 2005 or in SQL Server 2008)
http://support.microsoft.com/kb/978791 (FIX: Data loss may occur if the database mirroring thread stops responding for a long time during an automatic failover process in SQL Server 2005 or in SQL Server 2008)
http://support.microsoft.com/kb/982933 (Error message when you shrink data files on principal in a Database Mirroring for two SQL Server 2005 servers
http://support.microsoft.com/kb/983500 (FIX: The role switch is delayed when a mirroring automatic failover occurs in SQL Server 2005)

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.

http://support.microsoft.com/kb/926150 (Using database mirroring for cross-database transactions or distributed transactions is not supported in SQL Server)
http://support.microsoft.com/kb/977350 (FIX: Assertion failures occur in SQL Server 2005 when a Distributed Transaction Coordinator (DTC) transaction is rolled back while that transaction is being commited by the DTC)

For the networking side, the key things to consider are given below.

Review the effect of network latency on mirroring throughput. This is described in the white paper at http://technet.microsoft.com/en-us/library/cc917680.aspx
If you are facing issues with mirroring being disconnected very frequently, especially under load, check if your NIC card device driver needs updating.
In addition, review and if required, disable, the TCP Chimney feature in Windows 2003 SP2. More details can be found at http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx

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!

Licensing changes in SQL Server 2008 R2

Arvindsh — Tue, 07 Sep 2010 06:01:13 GMT

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.

Be Aware: Training Resource

Arvindsh — Wed, 01 Sep 2010 08:58:14 GMT

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.

PowerGUI tip

Arvindsh — Mon, 02 Aug 2010 08:13:26 GMT

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>\AppData\Local\Quest Software\PowerGUI\Logs”. 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.

Reconstructing the SQL Server Best Practices Toolbox

Arvindsh — Sat, 26 Jun 2010 17:55:40 GMT

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!

Performance Dashboard Reports in SQL Server 2008

Arvindsh — Fri, 25 Jun 2010 07:33:00 GMT

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.

Windows Mobile Device Center: missing Icons

Arvindsh — Thu, 24 Jun 2010 09:14:49 GMT

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:\Users\arvindsh\AppData\Roaming\Microsoft\ActiveSync\Profiles\<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!

Web installer for obtaining / installing debugging tools (WinDbg)

Arvindsh — Fri, 28 May 2010 13:16:21 GMT

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 Files\Debugging 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.

Programmatically Getting version of loaded assembly

Arvindsh — Wed, 12 May 2010 05:31:15 GMT

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!

Reportviewer and drillthrough

Arvindsh — Wed, 12 May 2010 05:08:31 GMT

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.

Replication and Linked Servers

Arvindsh — Mon, 10 May 2010 06:21:18 GMT

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.

My Favorite SQL Server Blogs

Arvindsh — Mon, 10 May 2010 05:04:27 GMT

At our workshops and during other customer interactions, we are usually asked for links to good blogs and reading materials. Here is a list of my favorite SQL-related blogs, arranged in no specific order.

http://blogs.msdn.com/craigfr: Craig Freedman on Query Processing (QP)

http://blogs.msdn.com/sqlprogrammability: Plan cache, parameterization etc.

http://blogs.msdn.com/sqlserverstorageengine: Storage Engine

http://blogs.msdn.com/repltalk: Replication support team

http://blogs.msdn.com/psssql: CSS SQL Support team

http://www.sqlskills.com/blogs/PAUL: Paul Randal with lots of tips

http://blogs.msdn.com/sqlqueryprocessing/default.aspx: Query Processing again

http://blogs.msdn.com/sqlserverfaq: Other CSS SQL Support team members

http://blogs.msdn.com/davidlean: Good series of posts on SQL Spatial features

http://blogs.msdn.com/sql_pfe_blog: Our global SQL PFE team blog

http://blogs.msdn.com/robertbruckner: Good information on SSRS

http://www.sqlcat.com: The SQL Server Customer Advisory Team (SQLCAT)

http://blogs.msdn.com/sqlreleaseservices: Learn about the latest releases (Service Pack / Cumulative Update) from SQL product team

I will be updating this list as and when I remember / discover other useful blogs. I hope this is useful for the community!

Hack of the day: Shrink all log files in the instance

Arvindsh — Sun, 14 Feb 2010 06:03:43 GMT

Before I proceed, I will add two disclaimers:

This script is provided as-is for knowledge purposes. It is not a recommendation, or in any way intended for production usage. No warranty or guarantee is made about the correctness of the script. Use it at your own risk.
We do not recommend using SHRINK operations on any production databases. This script is provided for demonstration purposes only and that too for test or development servers. For more details, please refer to the series of posts by Paul Randal at http://www.sqlskills.com/BLOGS/PAUL/category/Shrink.aspx

With that behind us, here’s what I would like to share. On my laptop I keep running large queries and many times those will grow the log file. Though my database is in simple recovery mode, a single long running transaction can cause this kind of growth.

So I would sometimes like to reclaim disk space (oh so precious on a laptop!) periodically. I would however not like to shrink the data file, just the log file. (Shrinking data files can cause fragmentation issues to occur.)

The problem is I normally operate on like 5-6 databases on my laptop. I would not want to use the GUI nor script this manually each time. So here is a simple script-generator, which will generate the necessary DBCC SHRINKFILE commands, which you can then execute as per your requirement.

drop table #f
go
create table #f (name sysname, fileid int, filename sysname, filegroup sysname null,
size sysname, maxsize sysname, growth sysname, usage sysname)
go
exec sp_MSforeachdb 'declare @s varchar(8000); use ?;
truncate table #f; INSERT #f (name, fileid, filename, filegroup, size, maxsize, growth, usage )
exec sp_helpfile
select @s = ''use ?; DBCC SHRINKFILE ('' + name + '' )'' from #f where usage = ''log only''
print @s '

Really crude script, but it does save me some time once in a while.

Priority boost details – and why it’s not recommended

Arvindsh — Wed, 27 Jan 2010 07:04:17 GMT

Some times, we see customer has (accidentally or otherwise) enabled the option ‘boost priority’ for SQL Server worker threads. In general Microsoft does not recommend that you set this option. Why?

First a bit of background. When we set the ‘priority boost’ option using sp_configure what is happening is that after restart the SQL engine will call Win32 API SetPriorityClass() and passes in HIGH_PRIORITY_CLASS (if you are debugger savvy, you can set breakpoints on these APIs and check what is happening – that’s what I did, no source code is required to verify this). From MSDN:

HIGH_PRIORITY_CLASS

0x00000080

Process that performs time-critical tasks that must be executed immediately. The threads of the process preempt the threads of normal or idle priority class processes. An example is the Task List, which must respond quickly when called by the user, regardless of the load on the operating system. Use extreme care when using the high-priority class, because a high-priority class application can use nearly all available CPU time.

It then proceeds to call SetThreadPriority() with priority as THREAD_PRIORITY_HIGHEST. For this combination of Process Priority Class and Thread Priority Level, the base priority level of these worker threads is 15. The only ones higher than this in the hierarchy of the OS are any threads which have process priority class set to REALTIME_PRIORITY_CLASS (which should be a very rare case for any application.) this means that many SQL worker threads are running at a priority level which is close to the highest on the system. Hence, they will tend to be selected frequently by kernel dispatcher to execute on the CPU.

So what is the effect?

There is clear precedent in the support teams of priority boost causing unresponsive servers. Sluggish UI / mouse / keyboard movements are other common symptoms if this setting is interfering with the capability of the OS to give (non-SQL) threads their desired quantum on the CPU. On a cluster, having priority boosted SQL threads can cause other critical threads such as the resource monitor’s IsAlive poll thread to timeout, thereby causing unwanted failover. Therefore we do not recommend to set priority boost to 1, especially in clustered instances.

Reference links:

SetPriorityClass: http://msdn.microsoft.com/en-us/library/ms686219(VS.85).aspx

SetThreadPriority: http://msdn.microsoft.com/en-us/library/ms686277(VS.85).aspx

Effective Base Priority: http://msdn.microsoft.com/en-us/library/ms685100(VS.85).aspx

Windows Priority levels: http://www.microsoft.com/mspress/books/sampchap/4354c.aspx and http://www.microsoft.com/mspress/books/sampchap/4354d.aspx