The most weird cause of failure for a SQL Server clustered instance I’ve ever seen

I have a customer who has a two node (W2K3 R2 SP2+ x64) failover cluster (MSCS) which are mainly dedicated to host 11 instances of SQL Server 2005 and 7 instances of Analysis Services 2005. When the total percentage of processor time reached 100% and was pegged there for a few seconds, the Network Interface/Output…

2

Expressions associated to the connection manager used by ASExecuteDDLTask (Analysis Services Execute DDL Task) are not evaluated as one would expect

When using the Analysis Services Execute DDL Task within an SSIS package, any expressions defined for the MSOLAP100 connection manager pointed by the ConnectionName property of the instance of ASExecuteDDLTask are not evaluated after the evaluation that occurs on package load (when ComputeAllExpressions is called from CPackage::LoadFromXML), because AcquireConnection is not being called for that…

0

SQL Server Profiler 2008 (or 2008 R2) not showing expected columns for given events while setting up the definition of a new trace

This week, while working in a customer, they showed to me a weird (an somehow inconsistent) experience with SQL Server Profiler. The story was like this: In a laptop that was running Windows 7 (x64) when they launched Profiler.exe (the one installed with SQL Server 2008), attempted to create a new trace connecting to an…

0

Microsoft.SqlServer.Management.Smo.PartitionFunction.ScriptCreate generates syntactically invalid statements for non-numeric data types

A colleague of mine recently came across this one problem for which I have filed a code defect so that it gets fixed in a future release. If you have a partition function whose range values are of a non-numeric data type, the generated script will lack the single quotes surrounding the literals so it…

0

Assumptions about SPIDs from where deadlock events will be produced

This is related to a discussion I’ve had with some colleagues during an XE session couple of weeks ago. These are the assumptions you can made: Lock:Deadlock Chain event is produced in the context of the lock monitor’s session. It tends to be the same SPID in a given system, and is typically SPID 6,…

0

How can you configure CLR Enabled setting from the UI?

Someone asked me that question some weeks ago. Back in 2005 you were able to enable/disable CLR integration from SAC’s GUI (SAC being Surface Area Configuration). Of course you could do it programmatically via sp_configure or via Microsoft.SqlServer.Management.Smo.Configuration.IsSqlClrEnabled property which is what SAC leveraged underneath. Starting with 2008 SAC was removed and the justification for…

0

Watch your CREATE CERTIFICATE *_DATE clauses syntax order!

I just noticed that given the way SQL Server parser parses the CREATE CERTIFICATE statement, the order in which you specify START_DATE & EXPIRY_DATE clauses really matters. If you specify the EXPIRY_DATE clause first, whatever value you specify is set as the expiry date attached to the certificate, but if a START_DATE clause is encountered…

1

How and when are the pages pushed down to the sparse files of a Database Snapshot?

Somebody recently asked me the following: “I’ve been told that when a hashed page from the BPool changes it is not immediately written to the data file on disk. Only the operations that caused that modification to the page are guaranteed to be written to the TLog before the transaction commits. The data page will…

0

Reason why the SLOTS XECustomizableAttribute of package0.synchronous_bucketizer & package0.asynchronous_bucketizer seem to be interpreted incorrectly?

Some friends of mine and I were trying to build some monitoring leveraging the XE bucketizer targets. During the exercise, we encountered that the value we were specifying in the SLOTS attribute was not exactly what was being used in the end. For example, we ran this one event session definition: CREATE event session LockCounts…

0

How to tell from ShowPlan output if (optimize for (unknown)) was used?

This one question someone asked me recently. In SQL Server 2005, 2008, and 2008 R2 there’s no difference between the ParameterList node generated for a parameterized query optimized for the parameter’s runtime value and the ParameterList node generated for the same query parameterized for UNKNOWN. For the query engine, a parameter for which the UNKNOWN…

0