Analysis Services - Shrinkable vs. non-shrinkable memory

Shrinkable vs. non-shrinkable memory

Analysis Services divides memory into two primary categories: shrinkable memory and non-shrinkable memory

An Example with the Intraday processing

The below example uses a staging cube to do the processing and then we synchronise changes to the cube users access.

This example is at the end of the sync of the staging cube to the live cube. This will clear out the data cache of the live cube
You can see in the below diagram that the Datacache decreases to nothing at 2:52PM

Once someone runs another query on the server after a sync, analysis services needs to go to the disk to get the information.
You can see from the below diagram at 2:54 it needed to read from disk and that the datacache at the same time starts building up again in the above diagram

Each of these explained (from ssas2005 perf guide)

When the cleaner is activated, it begins evicting elements of shrinkable memory, based on a cost/benefit algorithm that takes into account a variety of factors, including how frequently the entry is used, the amount of resources required to resolve the entries, and how much space is consumed by related entries. Shrinkable memory elements include the following:

Cached Results	Cached results include the Storage Engine data cache and Query Execution Engine calculation cache. As stated earlier in this document, the Storage Engine data cache contains measure group data and the Query Execution Calculation Engine cache contains calculation results. While both caches can help improve query response times, the data cache provides the most benefit to query performance by storing data that has been cached from disk. In situations of memory pressure, the cleaner shrinks the memory used for cached results. With this in mind, it is a good practice to monitor the usage of memory so that you can minimize the scenarios where elevated levels of memory pressure force the removal of cached results. For more information on how to monitor memory pressure, see Monitoring memory management.
Paged in dimension data	Dimension data is paged in from the dimension stores as needed. The paged-in data is kept in memory until the cleaner is under memory pressure to remove it. Note that this is different behavior than previous versions of Analysis Services where all dimension data was resident in memory.
Expired Sessions	Idle client sessions that have exceeded a longevity threshold are removed by the cleaner based on the level of memory pressure. Several server properties work together to manage the longevity of idle sessions. For more information on how to evaluate these properties, see Monitoring the timeout of idle sessions.

Non-shrinkable memory elements are not impacted by the Analysis Services cleaner.
Non-shrinkable memory includes the following components:

Metadata	For each Analysis Services database, metadata is initialized and loaded into memory on demand. Metadata includes the definition of all objects in the database (not the data elements). The more objects in your database (including cubes, measure groups, partitions, and dimensions) and the more databases that you have on a given server, the larger the metadata overhead in memory. Note that this overhead is generally not large for most implementations. However, you can experience significant overhead if your Analysis Services server contains hundreds of databases with tens or hundreds of objects per database, such as in hosted solutions. For more information on how to monitor metadata overheard, see Minimizing metadata overhead.
Active Sessions	For each active session, calculated members, named sets, connections, and other associated session information is retained as non-shrinkable memory.
Query Memory and Process Memory	Analysis Services reserves specific areas of memory for temporary use during querying and processing. During the execution of a query, for example, memory may be used to materialize data sets such as during the cross joining of data. During processing, memory is used to temporarily store, index, and aggregate data before it are written to disk. These memory elements are non-shrinkable because they are only needed to complete a specific server operation. As soon as the operation is over, these elements are removed from memory.

Links

http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/ssas2005perfguide.doc
http://download.microsoft.com/download/6/5/6/6567C845-FC8D-4D62-920F-C027A349C889/SSASPerfGuide2008R2.docx

Finding code in Object Definitions

INFORMATION_SCHEMA Views

Today I was trying to find a quick way of finding all stored procs that had deleted information from a table

So realising the information_schema.routines table held the actual definition for the stored proc, you can actually query the definition to find the code you are looking for.

SELECT *

FROM INFORMATION_SCHEMA.routines

WHERE ROUTINE_DEFINITION LIKE '%DELETE FROM ExampleTable%'

OBJECT_DEFINITION

Perhaps the best way is using the sql function OBJECT_DEFINITION. This will take in the sql server object id and return the definition for that object.

Example:

-- OBJECT_DEFINITION function

SELECT OBJECT_DEFINITION(OBJECT_ID('trigger_ExampleTable'))

I have found this is useful for finding code that is in triggers

SELECT NAME,OBJECT_DEFINITION(object_id) 
FROM sys.objects
WHERE TYPE = 'TR' -- triggers

Orphaned Database Snapshots prevents mds configuration manager database connection

Problem:

Orphaned database snapshots in dev and test environments can stop you being able to use the MDS Configuration Manager to connect to the database. This occurs if you restore the mdm_hub database when a database snapshot exists on the target server. Because the snapshot is orphaned it gets left in SUSPECT mode.

SELECT name,state_desc  FROM sys.databases

Solution:

Delete the database snapshot and make sure the database is in ONLINE mode.

More Info:

To check for the error you can run a profiler trace which will bring up an Exception 926 when trying to connect with configuration manager.
Books online error 926

Viewing job history via T-SQL

This SQL Server script will display job history. The benefit of this script over displaying it from the GUI is that you get to see the job durations quickly. You will need to replace with the job you want to see history for.

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
WHERE job_name = ''
order by job_name, run_datetime

Great Plains Document Locks

Overview

The following code is useful to find who has a document locked in Great Plains.
For example if someone has a sales document open during fulfilment it will fail... so we need to check who is in the document to get them to come out of it.

T-SQL to find who has document locks in Great Plains

USE DYNAMICS

DECLARE curLocks CURSOR
FOR SELECT  sqlSessions.login_name, LOCK.table_path_name, LOCK.row_id
 FROM    tempdb..DEX_LOCK LOCK
        INNER JOIN tempdb.dbo.DEX_SESSION SESS 
   ON LOCK.session_id = SESS.session_id        
        INNER JOIN master.sys.dm_exec_sessions sqlSessions 
   ON SESS.sqlsvr_spid = sqlSessions.session_id

DECLARE @name varchar(40)
DECLARE @table_path_name varchar(80)
DECLARE @row_id int
OPEN curLocks

FETCH NEXT FROM curLocks INTO @name, @table_path_name, @row_id
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN

  DECLARE @sql varchar(1000)
  SELECT @sql = 'select ''' + @name + ''' AS [LOGIN NAME], * from 
   ' + RTRIM(@table_path_name) + ' where dex_row_id = ' + CAST(@row_id AS VARCHAR(50))
  EXEC (@sql)

 END
 FETCH NEXT FROM curLocks INTO @name, @table_path_name, @row_id
END

CLOSE curLocks
DEALLOCATE curLocks
GO

Excel dates do not match SQL Server dates

Overview

There is a bug in excel that number representations for dates (ie 1 = 01/01/1900) do not match up with SQL Server number representations for dates. Apparently this is something that is known but will not be fixed as it will break to many peoples spreadsheets!

How dates work in Excel

Dates in excel are really just numbers formatted as a date. This picture shows the first column as the number representation of the date. All i have done is to copy the column across to B and reformat it to a Date column.

The Bug

This shows there is a 29 Feb 1900 when it never existed! So it adds another date to the calender, thereby ruining any sort of consistency with the number representation of the date.

In SQL Server

Dates work the same way in SQL Server except they start from 0 rather than 1.

DateID      SQL Dates

----------- -----------------------
0           1900-01-01 00:00:00.000
1           1900-01-02 00:00:00.000
2           1900-01-03 00:00:00.000
3           1900-01-04 00:00:00.000

And in SQL Server the dates are correct...

SELECT 58 AS DateID, CONVERT(DATETIME,58) AS [SQL Dates] UNION ALL
SELECT 59, CONVERT(DATETIME,59) UNION ALL
SELECT 60, CONVERT(DATETIME,60) UNION ALL
SELECT 61, CONVERT(DATETIME,61)

Returns the following

DateID      SQL Dates
----------- -----------------------
58          1900-02-28 00:00:00.000
59          1900-03-01 00:00:00.000
60          1900-03-02 00:00:00.000
61          1900-03-03 00:00:00.000

So what you get in the end that it can be complicated to link the dates to Excel if you are trying to match up Excel to SQL Server with the number representation of the date

Unit Testing Framework with tSQLt and Redgate SQLTest

Overview

tSQLt allows you to implement unit tests in T-SQL. Unit testing will help us get to a stage where we are doing continuous integration, test driven development and Agile Development.

Redgate have released a user interface for tSQLt that integrates directly into SSMS
http://www.red-gate.com/products/sql-development/sql-test/

What is tSQLt?

http://tsqlt.org/
tSQLt is a database unit testing framework for Microsoft SQL Server. tSQLt is compatible with SQL Server 2005 (service pack 2 required) and above on all editions.

How to install

1. Go to http://www.red-gate.com/products/sql-development/sql-test/
2. Download the preview version
3. Install on your machine where SSMS is installed (you will have to close SSMS)

Are there an server components installed?

Yes. You will be prompted when you open SSMS. These are the changes it makes to a database you are doing unit testing on..

A Hello World Example

Create a procedure you want to test

USE [tSQLt_Example]
GO
CREATE PROCEDURE [dbo].[getHelloWorld]
 @pass BIT, 
 @HELLO VARCHAR(15) OUTPUT
AS
BEGIN

 /*
  Return Hello World or Goodbye depending on @pass param
 */
    IF @pass = 1
  SET @HELLO = 'Hello World!'
 ELSE
  SET @HELLO = 'Goodbye World!'  
   
END
GO

Create your unit Test stored procedure

In SSMS in the SQL Test window, choose 'New Test...'

Depending what version you are on depends what happens here (anything prior to 2008R2 means you just have the write the sproc, i couldn't get anything else to happen)

USE [tSQLt_Example]
GO
Create PROCEDURE [AcceleratorTests].[testHelloWorld]
AS 
    BEGIN
 
 /*
  Return Hello World!
 */
    DECLARE @ret VARCHAR(15)
    EXEC getHelloWorld @pass = 1, @HELLO = @RET OUTPUT

 /*
  Check Hello World is returned
 */
    EXEC tSQLt.AssertEqualsString 'Hello World!', @ret

    END ;
GO

You will end up with a test in the SQL Test window if you choose refresh

Running your tests

Choose 'Run Tests' from the SQL Test window and there will be a tick or cross as to whether your test passed.

Fail Example

To see a failed test example alter the getHelloWorld stored procedure to pass in a 0 for the @pass parameter
eg. EXEC getHelloWorld @pass = 0, @HELLO = @RET OUTPUT

This is what the SQL Test window shows

And output in the SQL Test Messages

[AcceleratorTests].[testHelloWorld] failed: Expected:  but was: 

Related stuff

http://www.red-gate.com/products/sql-development/sql-test/
http://tsqlt.org/