Retrieve current user account through MDX query

Submitted by masscloud on

masscloud's picture

Tags: 

The following MDX query will reveal the current user account, and the result will look like domainnameusername:

-- Get current user authority\username
WITH MEMBER Measures.x AS UserName
SELECT Measures.x ON COLUMNS
FROM [Adventure Works]

The following MDX query shows the current user account without the domain name:

Log in
to vote
0

Retrieve SQL Server Analysis Services database schemas

Submitted by masscloud on

masscloud's picture

Tags: 

Sometimes we want to retrieve SQL Server Analysis Services cube schema through handy queries instead of getting them from Visual Studio Business Intelligence. SQL Server Analysis Services provides some Dynamic Management Views (DMVs) which contain the cube schema info:

4 Database Schema DMVs:

  • $SYSTEM.DBSCHEMA_CATALOGS
  • $SYSTEM.DBSCHEMA_COLUMNS
  • $SYSTEM.DBSCHEMA_PROVIDER_TYPES
  • $SYSTEM.DBSCHEMA_TABLES

10 data mining models DMVs:

Log in
to vote
0

Clear cache of a SSAS database with ClearCache

Submitted by masscloud on

masscloud's picture

Tags: 

This XMLA clears the memory cache for a specified SSAS database with ClearCache.

<Execute xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ClearCache>
    <Object>
      <DatabaseID>YOUR SSAS DATABASE</DatabaseID>
    </Object>
  </ClearCache>
</Execute>
Log in
to vote
0

Get SQL Azure database current and max sizes

Submitted by masscloud on

masscloud's picture

Tags: 

SQL Azure database has size limitation based on its version, and the following query is to get a database's current and max sizes (quota).

-- Get current and max database size
SELECT GETUTCDATE() AS collect_time,
N'YourDBName' AS db_name,
DATABASEPROPERTYEX ('YourDBName', 'MaxSizeInBytes') AS max_size_in_bytes,
SUM(reserved_page_count) * 8192 AS actual_size_in_bytes
FROM sys.dm_db_partition_stats
Log in
to vote
0

Get database size on SQL Azure

Submitted by masscloud on

masscloud's picture

Tags: 

Here is how to get the database size on SQL Azure.

-- Get the current database size
SELECT SUM(reserved_page_count)*8.0/1024 AS SizeInMB
FROM sys.dm_db_partition_stats; 
GO

-- Calculates the size of individual database objects. 
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 AS SizeInMB
FROM sys.dm_db_partition_stats, sys.objects 
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id 
GROUP BY sys.objects.name; 
GO
Log in
to vote
0

Get row counts from dm_db_partition_stats

Submitted by masscloud on

masscloud's picture

Tags: 

We all know that SELECT COUNT(*) will get the row count number of a table. However it is inefficient if the table is huge, and lots of disk I/Os may happen. Sometimes we don't care about the accurate row count number, an approximate number will satisfy, then we can use sys.dm_db_partition_stats get the estimated row count.

SELECT OBJECT_NAME(st.OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st 
join sys.objects o
on o.object_id=st.object_id
WHERE index_id < 2
and type='U'
ORDER BY st.row_count DESC
Log in
to vote
0

Automatically enable page level data compression for all tables and indexes

Submitted by masscloud on

masscloud's picture

Tags: 

Data compression is a great feature introduced in SQL server, and it can help reduce the size of the database. More importantly, compressed data take fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. Extra CPU is required for compressing and decompressing data though, and the good thing is CPU resource is abundant on most SQL servers.

Log in
to vote
0

Get concurrent query list on SQL Server

Submitted by masscloud on

masscloud's picture

Tags: 

In some cases, we need to get the concurrent running query list and their details. Those information is very important to troubleshoot performance issues. On SQL Server, you may use the activity monitor to understand the running queries. However the GUI result isn't flexible, and sometimes you may need a query to get those info and do analysis based on it.

Here is the query:

Log in
to vote
0
Subscribe to SQL Land RSS