In order to refresh your OLAP Cubes (SQL Server Analysis Services), you need to create XMLA files. These XMLA files can be used in a SQL Server Agent Job, so that the Cubes are refreshed and re-processed. To accomplish this, you need to follow the next steps:
1) Deploy your Cube to your SQL Server
2) Right click on the database, and choose “Script database as” –> “Alter To”
3) Add the following node to the top of the XML:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
4) Change the “Alter” node into the following node:
<Alter AllowCreate="true" ObjectExpansion="ExpandFull">
5) Add the following nodes to the bottom of the XML:
<Parallel>
<Process>
<Type>ProcessFull</Type>
<Object>
<DatabaseID>SandBox</DatabaseID>
</Object>
</Process>
</Parallel>
</Batch>
6) Change the "DatabaseID" in the nodes of Step 5 to the Cube name you deployed
7) In your SQL Job, choose the "Type" of task "SQL Server Integration Services Package"
So from now on, your SQL Job will re-create the SSAS Cube, and process it. But be aware, every time you change your Cube, change your XMLA file as well!!!
Every ones in a while you want to know the record count of all objects in your database. If you have 10 objects or less in your database, you can choose to type out the statements yourself, but once it’s more then that it gets quite annoying.
Because I needed this for a production issue (make sure that all objects contain records after import) I wrote a cursor to query all the database objects, and count the content of it:
USE Sandbox
--====================================================================================================
DECLARE @TableName VARCHAR(50) = '',
@SQLStatement VARCHAR(MAX) = ''
--====================================================================================================
DECLARE TableCursor CURSOR FOR
SELECT name
FROM sys.tables
WHERE name NOT IN ('sysdiagrams')
AND name NOT LIKE 'Temp%'
AND name NOT LIKE '%ReturnGrid%'
ORDER BY name ASC
--====================================================================================================
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
--====================================================================================================
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLStatement = @SQLStatement + 'SELECT ''' + @TableName + ''' AS TableName, COUNT(*) AS RecordAmount FROM ' + @TableName
SET @SQLStatement = @SQLStatement + ' UNION ALL '
/* Print Result */
PRINT @TableName
/* Select SQL Statement */
--SELECT @TableName, @SQLStatement
FETCH NEXT FROM TableCursor INTO @TableName
END
--====================================================================================================
CLOSE TableCursor
DEALLOCATE TableCursor
--====================================================================================================
SET @SQLStatement = LEFT(@SQLStatement, (LEN(@SQLStatement) - 10))
--====================================================================================================
/* Print */
PRINT '--===================================================================================================='
PRINT 'Executing Statements'
EXEC(@SQLStatement)
--====================================================================================================
The script uses a cursor to query the object names from the sys.tables, and with that data it creates a dynamic SQL query. That query counts the content of all objects. The sysdiagrams (MS Shipped table), Temp and ReturnGrid tables are excluded from the cursor.
For the big databases I build in a PRINT statement, so you can keep track of the progress while running the query. If the dynamic SQL statement is executed, it will show the text “Executing Statements” via a PRINT statement in the bottom of the query. After that you will see the results.
For (almost) all developers, there’s no greater feeling, then knowing your source code is (relatively) safe. The same counts for T-SQL script. Especially if you know that you need the same code next year (argh). So first thing I did after the initial release of scripts, was looking for a way to check-in T-SQL from SQL Server Management Studio (SSMS).
So a colleague of mine, Dries (Blog | Twitter), dropped by with a solution: TFS MSSCCI Provider 2010 from the Visual Studio Gallery. It’s a free plugin from Microsoft. This will allow you to check-in your scripts from SSMS, without the hassle of manually checking in files via Visual Studio Team Explorer.
You can download the plugin and install it, even with SSMS running. All it takes is a restart of SSMS to pick up the plugin, en you’re ready to roll!
When you work with SQL Server, you regularly want to know when an object is last executed, last modified, etc.. Well, at least I do! In most cases you need to use the sys.objects to obtain this information, join it with other sys tables, search for your information, and so on. But there is an easier way.
The script below will get you the latest information of any object in your database.
USE SandBox
SELECT
DB_NAME(Txt.dbid) AS DatabaseName,
OBJECT_NAME(Txt.objectid, Txt.dbid) AS ProcedureName,
Objects.create_date AS CreationDate,
Objects.modify_date AS ModifiedDate,
MAX(Stats.last_execution_time) AS Last_Execution,
CASE Objects.type
WHEN 'AF' THEN 'Aggregate function (CLR)'
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'L' THEN 'Log'
WHEN 'FN' THEN 'Scalar function'
WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'IT' THEN 'Internal table'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'SN' THEN 'Synonym'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'SQL DML Trigger'
WHEN 'TT' THEN 'Table type'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint (type is K)'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE '-'
END AS ObjectType
FROM sys.dm_exec_query_stats Stats
CROSS APPLY sys.dm_exec_sql_text(Stats.sql_handle) Txt
LEFT JOIN sys.objects Objects
ON Objects.object_id = Txt.objectid
WHERE 1 = 1
AND Txt.dbid = db_id() --Execute for selected DB
AND Txt.dbid IS NOT NULL --Exclude Ad-Hoc queries
AND DB_NAME(Txt.dbid) IS NOT NULL --Exclude Ad-Hoc queries
GROUP BY
DB_NAME(Txt.dbid),
OBJECT_NAME(Txt.objectid, Txt.dbid),
Objects.create_date,
Objects.modify_date,
Objects.type
ORDER BY DatabaseName, ProcedureName ASC
This script will show you the information you need to decide if you want to keep the object or, for example, want to delete it. With this script you can also check if Functions are still in use, or when a Trigger is last fired.
If you need more information about the object you can also use the query below. This will show you the time needed to execute the object, last execute time needed, etc:
SELECT *
FROM sys.dm_exec_query_stats Stats
CROSS APPLY sys.dm_exec_sql_text(Stats.sql_handle) Txt
With these two queries you will be able to determine the status of the objects in your database.
If you write a SQL query, it’s important to know when certain pieces of you query are executed. For example, it’s possible that some statement in your query interferes with another part of your query, because of the execution time of a specific piece.
The order of execution is:
1. FROM Clause
2. JOIN / APPY / PIVOT / UNPIVOT Clause
3. WHERE Clause
4. GROUP BY Clause
5. CUBE / ROLLUP Clause
6. HAVING Clause
5. SELECT Clause
6. DISTINCT Clause
7. TOP Clause
8. ORDER BY Clause
So in many cases this won’t be a problem. But in some cases this can pose as a problem. In those cases it’s important to know the order of execution if you want to debug your query.
Sources: Logical Query Processing
In SQL Server we know 3 kinds of temporary tables: Local Temp Tables, Global Temp Tables and Table Variables. In this blog I will try to explain the differences, and tell you how to use it.
Local Temp Tables
The local temp table is the most commonly used temp table. This temp tables is created with the following statement:
CREATE TABLE #TempTable
(ID INT IDENTITY(1,1) NOT NULL,
Description VARCHAR(10) NULL)
The table that the script above created is a temporary table that is stored on disk. To be exact, it’s stored in the TempDB. The table can only be reached from within the same scope. It will be cleaned automatically, but it’s more wise to clean it up yourself by using a DROP TABLE statement.
A session specific identifier is added to the name, to make sure that temp tables from other sessions don’t use the same name. If you query the sys.tables, you will see a table name similar to this:
#TempTable___________________________________________________________________
_______________________________________000000000005
The benefits of using a temp table are (amongst others): reduction of required locks (because the user is the only one who has access), less logging necessary, easy debugging (in some cases), etc. One downside is that the objectname is limited to 116 characters.
Global Temp Tables
Global temporary tables work just like local temporary tables (stored in TempDB, less locking necessary). However, they are visible to all sessions, until the session that created the object goes out of scope and all referring sessions to the object go out of scope. Also, the name of the object will remain the same as you declared it. So if you query sys.tables in the database, you will notice that (instead of the script above) the name is still TempTable.
A global temp table can be created with the following statement:
CREATE TABLE ##TempTable
(ID INT IDENTITY(1,1) NOT NULL,
Description VARCHAR(10) NULL)
Most of the time you will not encounter these Global temp tables “in the wild”, because permanent tables are mostly preferred.
Table Variables
A temporary table can also be created in memory. This has several advantages: less locking then permanent objects, performs slightly better then temporary- or global tables, not as much logging necessary, etc. Table variables are cleared automatically when the procedure, function or query goes out of scope.
There are a few disadvantages and/or demands for using table variables:
- They are allowed in SQL Server 2000 and above, and databases with Compatibility level 80 or higher
- They cannot be used for “SELECT * INTO”-queries
- They cannot be changed after declaration. They need to be recreated
- They cannot be truncated
- They cannot contain: User Defined Function (UDF), User Defined Type (UDT), Computed Column or Default Constraint
- They cannot be dropped. You need to let them go out of scope
- They cannot be created dynamically (“SELECT * INTO @TempTable”-statement), or used in a dynamic SQL statement
- They are not included in statistics by SQL Sever, and you cannot create it manually
- They don’t use parallelism when you use an “INSERT INTO”-statement
- They will always have a cardinality of 1, because the table doesn’t exist at compile time. Cardinality refers to the uniqueness of a column. The lower the cardinality, the more duplicated items in a column
- They must be referenced by an alias if you join the object in a query. If you don’t the compiler will tell you the object doesn’t exist
Conclusion
As always, there is no right or wrong answer. In most cases you will use the Table Variable or Local Temporary Table. The right answer in your case will be a judgment call between performance and usability. There are a few rules of thumb you can use to determine which type of object suites your needs:
- If you have less then 100 rows, generally you want to use a Table Variable
- If you need an index on your object, use a Temporary Table (Local or Global)