Monthly Archives: November 2017

How to determine the cache plan size in SQL server?

How to determine the cache plan size in SQL server?

–SQL 2012+ Plan Cache Size

— How big is the plan cache SQL 2012

select name, sum(pages_kb) /1024.0 MBUsed

from sys.dm_os_memory_clerks

where name = ‘SQL PLans’

group by name;

–Pre-SQL 2012 Plan Cache Size

— How big is the plan cache pre SQL 2012

select name, SUM(single_pages_kb + multi_pages_kb)/1024.0 MBUsed

from sys.dm_os_memory_clerks

where name = ‘SQL PLans’

group by name;

 

SQL Server Python Tutorial

 

See these additional resources for more R samples:

How to enable sp_execute_external_script to run R script in Sql server?

================ Error ===================

Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 9]

‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.

Msg 11536, Level 16, State 1, Line 10

EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

========================= fix ===========================

when enabled, you will see the config_value changed from 0 to 1

Note: SQL Server Service has to be restart for this to take into effect!

 

SQL Server how to run as another user?

EXECUTE AS LOGIN = ‘login1’;
–Verify the execution context is now login1.
SELECT SUSER_NAME(), USER_NAME();
–Login1 sets the execution context to login2.
EXECUTE AS USER = ‘user2’;
–Display current execution context.
SELECT SUSER_NAME(), USER_NAME();
— The execution context stack now has three principals: the originating caller, login1 and login2.
–The following REVERT statements will reset the execution context to the previous context.
REVERT;