Sql-server – SQL Server Memory Determination

Try the query below:

SELECT (count(*)*0.0078125) AS 'Pages in Buffer pool'
    ,CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 1 DESC

This will show you the amount of space each DB has occupied in the buffer pool. Note that there are other factors in SQL that also use up the memory apart from the buffer pool.

I will tell you the way we deploy in my place SSAS projects between different environments. We use a set of Powershell scripts that:

Part 1:

• get the last version from the build server and deploy the file to generate the last working version of XMLA;
• take the generated XMLA from the build server and copy it to the QA server;

Part 2:

• Now, on the QA server, we change manually the server name, the database name (set as parameters to the PS script), we verify that all required scripts are there;
• one PS script runs the resulted XMLA on the current QA server, so the SSAS database is created (not processed though);
• a XMLA script that commands the full process of the cube and its dimensions;

All of these steps are done as automatically as possible, with as little intervention from the developer/QA guy as possible. We do, though, insert some eyeballing in between, just to keep our minds safe. I won't say that it's the best, but it works and gets the job done. Before the last step I'm sure you can insert any XMLA script to remove existing roles and create new ones. And to change a PS script to execute some new SQL or XMLA script is not hard at all. I suppose that the roles and permissions don't change that much, so they can be scripted and reused.

PS: this is an environment with domain users and fixed servers (so we can use shares, domain accounts and all that fluff). If you have another environment it might be a more difficult to get it done.