How It Works: sp_server_diagnostics – spinlock backoffs

There are numerous articles outlining how spinlocks work so I won’t cover the details in this post.   Instead, I want to focus on the spinlockbackoffs value recorded in the sp_server_diagnostics output. Component = System <system spinlockBackoffs=”0″ sickSpinlockType=”none” sickSpinlockTypeAfterAv=”none” … Querying select * from sys.dm_os_spinlock_stats the backoffs column is presented.   This is NOT the same as…

0

How come sys.dm_exec_requests.cpu_time never moves?

Today, I want to point out another SQL Server 2012 SP2 fix that may affect your performance troubleshooting. When you are troubleshooting a long running query, chances are you will use sys.dm_exec_requests to look for progresses of the query. But one of the column – cpu_time is not accurate prior to SQL Server 2012 SP2.When…

0

A faster CHECKDB – Part III

Bob Ward introduced Part 1 and Part 2 of ‘A faster CHECKDB’ as highlighted in the following links. Part 1: http://blogs.msdn.com/b/psssql/archive/2011/12/20/a-faster-checkdb-part-i.aspx  Part 2: http://blogs.msdn.com/b/psssql/archive/2012/02/23/a-faster-checkdb-part-ii.aspx  Recently,  Jonathan pointed out a memory grant issue in the following post. https://www.sqlskills.com/blogs/jonathan/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect/ I always enjoy my interactions with Jonathan and this is yet another positive experience for us all.  After…

0

SQL Server MAX DOP Beyond 64 – Is That Possible?

I recently posted a blog outlining how the partitions of a table can be used in the calculation for the achievable max degree of parallelism (MAX DOP). http://blogs.msdn.com/b/psssql/archive/2014/09/04/a-partitioned-table-may-limit-the-runtime-max-dop-of-create-alter-index.aspx  Discussing this with various peers I uncovered a perception that SQL Server was always limited to a max of 64 CPUs, even if the machine had more…

1

SQL CLR assembly fails verification with “Unable to resolve token”

Recently we worked with a customer has an SQL CLR assembly. This customer decided to upgrade from SQL Server 2008 R2 to SQL Server 2012. But this assembly failed to register with SQL Server and he received the following error:Msg 6218, Level 16, State 2, Line 11CREATE ASSEMBLY for assembly ‘test3.5’ failed because assembly ‘test3.5’…

1

A Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index

I was working with a 1.3 trillion row table in the Microsoft lab when I learned more about the ins and outs of this behavior.  This issue is alluded to in SQL Server Books Online but allow me to expand on the behavior a bit more. (http://msdn.microsoft.com/en-us/library/ms190787.aspx) The lab machine is a 128GB, 64 CPU…

1

Power View in Excel won’t render from SharePoint

I originally encountered this issue back in May with a customer.  We had another customer this month that had the same issue.  When you try to load an Excel Document with a Power View Report in it from SharePoint, you may encounter the default unable to load power view report image. Before I get into…

2

VSS backup of AlwaysOn Secondaries

Hi Everyone, Today I’m going to highlight one of the changes brought by SQL Server 2012 SP2, which is the way we handle VSS Backup requests on AlwaysOn Secondary Databases. Until now, any request for a FULL database backup (VSS_BT_FULL) thru VSS against a DB that is an AlwaysOn secondary was failing by design. Our…

5

Having performance issues with table variables? SQL Server 2012 SP2 can help!

In a previous blog, I talked about how table variable can impact performance of your query. The reason is that the statements referencing the table variable are compiled when the table variable has no data in it. Therefore, cardinality estimate for the table variable is always 1. If you always insert small number of rows…

4

The Additive Design of SSAS Role Security

SSAS security roles are additive – that is, a user gets permission to access data allowed in any role to which the user belongs, even if another role membership for the same user does not allow access to the same data. This can cause confusion in some circumstances, and has been incorrectly reckoned a defect…

4