First let me just say - fantastic looking site :)
This sort of thing is exactly what I wrote that XSLT for - I'm glad that others are finding it useful!
I have to confes its something that I wrote a while ago and then got kind of side-tracked, there are some improvements that I've been planning to make for a while now that I hope to get around to actually implementing soon!
Some links:
Let me know if you have any suggestions for improvements!
Not sure where you've stumbled along the way, but this works for me:
CREATE LOGIN permtest WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE somedatabase;
GO
CREATE USER permtest FROM LOGIN permtest;
GO
According to this page, the user needs SELECT permission on sys.sql_expression_dependencies, and VIEW DEFINITION on the database.
In my experimentation, the following allowed the user to select from the view, but it returned 0 rows, because they don't have the ability to view definition (which includes dependency chains):
GRANT SELECT ON sys.sql_expression_dependencies TO permtest;
In order to actually see any relationships in somedatabase, I also had to add the following:
GRANT VIEW DEFINITION ON DATABASE::floob TO permtest;
I could not find any way to make that more granular (VIEW/DENY definition worked for individual objects, but without the database-level right, I still couldn't see any rows in the catalog view, and DENY did not prevent the objects from showing up in the catalog view nor did it even prevent me from viewing the definition). I feel like SQL Server would have a hard time resolving that granularity anyway - if you had a view that referenced a table, how should the catalog view look if you have grant on the view and deny on the table, or vice versa?
If you don't want to grant VIEW DEFINITION on the database, then create procedures that use EXECUTE AS OWNER, select (filtered?) rows from the catalog view, and give the users (and of course, that could also be a role) execute permissions on the procedure.
CREATE PROCEDURE dbo.GetDependencies
WITH EXECUTE AS OWNER
AS
BEGIN
SELECT is_schema_bound_reference --, ...
FROM sys.sql_expression_dependencies;
END
GO
GRANT EXECUTE ON dbo.GetDependencies TO permtest;
Best Answer
It's a snapshot of the same information that you can get from sys.dm_exec_query_resource_semaphores. I believe that it's
target_memory_kbfor the regular resource semaphore multiplied by the maximum query grant percent for the Resource Governor group that the query is executing in. If RG is not enabled then just use the default of 25%.On the servers that I looked at, I generally see about 28% of server memory not available for query memory grants. So once I've run a workload the max query grant that's available for a query is 0.25 * 0.72 *
MaxServerMemory.Here's a query that you can run that finishes almost instantly but asks for a large memory grant:
If I force that query to timeout then I get the following in the actual plan:
MaxQueryMemorydoes not change as a result of the timeout. It isn't directly affected by other queries currently executing and using query memory. It's indirectly affected by anything that changes target memory KB for the resource pool.This information could be useful if you have a bunch of different Resource Governor pools and groups and want to validate that a query goes to the correct place. It can also provide context to the total size or configuration of a server that you don't have access to. It's another way to tell if a query timed out waiting for a memory grant, but there are already other ways to do that. The intended use case may be to give more information about performance problems caused by target memory changing quite a bit on servers, but I've never seen that happen personally. Maybe it can happen on servers with multiple instances of SQL Server running?