Esoteric

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

WARNING: Stack unwind information not available. Following frames may be wrong.

Arvindsh — Wed, 23 Dec 2009 04:52:48 GMT

I’m sure if you have ever used the WinDbg family of debuggers sometime, you must have seen the above message. What does it really mean? How does it affect you?

Quick Background on stack operation

In x86, the way the stack is built up, the entry point of the function (a.k.a. prolog) generated by the compiler contains some key instructions including one which saves the previous value of the EBP register for that thread on the stack. The next instruction sets the EBP register to point to the current stack pointer (ESP).

What am I talking about? If you don’t know what EBP and ESP are, I recommend you take a quick look at the links in the reference section at the end.

Frame Pointer Omission

So in some cases the compiler may choose to omit the setting of the EBP register and may instead directly use ESP to reference locals and parameters. In such cases the EBP register (a.k.a. ‘frame pointer’) is deemed as ‘omitted’ and the generated code is called FPO (Frame Pointer Omission) code.

In such cases the debugger will typically complain that it cannot unwind the stack based on the EBP (unless it has symbol files which match the module whose function has FPO enabled). Under those cases it will emit the warning which is the title of this post.

Recommendation

For builds which have /Oy enabled, it is necessary to have symbol files to successfully reconstruct the stack.
In the real world the most common reason to encounter the above message is faulty symbol paths. Check your symbol path.
In cases where you can tolerate the overhead of setting up the frame pointer, leave FPO off (/Oy-). See the last reference in my post for something which Windows team has supposedly done regarding this.

References

YADCU - Yet another dump capture utility

Arvindsh — Wed, 23 Dec 2009 04:32:32 GMT

The plethora of dump capture tools is amazing and sometimes confusing. But here is one from Mark Russinovich which looks interesting: ProcDump. Some unique capabilities I can see in this tool are things like CPU threshold based triggers, the ability to clone a process so that it is suspended for minimum time when dump is captured, ability to launch another image on the event trigger, etc. Take a look at it, it should add to your debugging toolbox!

Low Fragmentation heap details

Arvindsh — Fri, 18 Dec 2009 05:42:42 GMT

Quick post here: I recently came across a great presentation by Richard Johnson which among other things talks about the implementation details behind ntdll’s Low Fragmentation Heap. While officially not much documentation is available on this subject, I believe Richard’s presentation offers pretty much the most accurate publicly available details on the same.

The meaning of CID in output of kernel debugger commands

Arvindsh — Fri, 30 Oct 2009 04:26:51 GMT

Recently someone asked me what was the real meaning of the ‘Cid’ field which appears in the output of commands such as !process and !thread in the kernel debugger (kd). Though from a practical perspective I was aware that these represent the Process ID and Thread ID, I was unsure of what Cid stands for. In course of a search, I found a public source which answers the question. Cid is short for CLIENT_ID which in turn is an undocumented structure.

The public source is a free PDF version of the excellent “Undocumented Windows 2000 Secrets: A Programmer's Cookbook” book, which you can now find at http://undocumented.rawol.com/. I think this resource is a very useful one for all those interested in Windows Internals and debugging as well. Go take a look at it!