SQL Server memory models (Part II)

So what is part II about? It is about a change in Standard 64 bit Editions of SQL Server 2005 and 2008. This change implements the support of AWE APIs and “Lock Pages in Memory” privilege by these editions.  It has been already advertised by Bob Ward and worth to be known especially by the…


How and Why to Enable Instant File Initialization

You may want to consider enabling Instant File Initialization (via SE_MANAGE_VOLUME_NAME a.k.a. "Perform volume maintenance tasks") for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but DOES NOT WORK FOR LOG FILE ALLOCATIONS. This is enabled for each instance via the "Perform volume maintenance tasks"…


SQL Server memory models (Part I)

You may have questioned why, on 64 bit enterprise Edition, DBCC MEMORYSTATUS reports non zero “AWE allocated” memory even if ‘AWE enabled’ option is not enabled. A short description of SQL Server memory model will help to answer. Here is an example of a DBCC MEMORYSTATUS output on a 32GB X64 Enterprise SQL Server 2005:…


Side effects of storing an Analysis Services partition data in a non-default location

In 2005 and 2008, SQL Server Analysis Services allows users to store partition data in a non-default location. That is something we could not do in 2000 version. To store a partition data in a non-default location means that you can have the partition storage location set to some folder other than the default data…


How to use Partition Processing Destination Task to pump data into a SSAS partition via SSIS

To load and process the data into Analysis Services, SQL Integration Services provides two data flow components: Partition Processing Destination and Dimension Processing Destination. However, I found very few users know how to use them or use them correctly.  Some said the features look nice but can’t find any good example to follow. That makes…


SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffers

I ran into a scenario a while ago that I thought I would share, where we were troubleshooting high CPU on  SQL Server.  The sporadic High CPU ( 90+ % )  pretty much brought the box down to its knees for a period of 5-10 minutes minutes or so, and then cleared itself up. We…


SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffers

I ran into a scenario a while ago that I thought I would share, where we were troubleshooting high CPU on  SQL Server.  The sporadic High CPU ( 90+ % )  pretty much brought the box down to its knees for a period of 5-10 minutes minutes or so, and then cleared itself up. We…


Sum of a SQL Analysis Services calculated measure within a Reporting Services report gives #Error

Summing a SQL Analysis Services calculated measure result within a Reporting Services report gives #Error but the same calculated measure output can be summed within Analysis Services perfectly fine. Environment: SQL Server 2008 Analysis Services and Reporting Services + SP1, Report builder 2.0 sp1. This should be applicable to all SQL 2008 and 2005  Reporting…