SQL Snippet: Who owns that SQL Server Job?

John Sansom — Wed, 10 Mar 2010 12:37:00 +0000

Here is a handy T-SQL script that will list all SQL Server Agent Job owners by job.

From an administration perspective, this query can come in handy when you need to ensure/validate the job owners on your server. One common mistake that I have seen is that when a user creates a new SQL Server Agent Job via SQL Server Management Studio, by default their Login will be assigned as the owner of the job. This is often not a desirable choice of owner and so it’s good idea to do a little house keeping every once in a while and check who the current Job Owners are for your environment.

SELECT
A.Name AS JobName,
B.name AS JobOwner
FROM dbo.sysjobs A
INNER JOIN master.sys.syslogins B ON
A.owner_sid = B.sid

More Handy SQL Snippets

I hope you find this SQL Snippet useful in your administration of SQL Server. If you have any questions regarding this snippet, SQL Server Agent Jobs or anything to do with SQL Server then let me know!

SQL Snippet: SQL Server Wait Types

John Sansom — Sun, 07 Mar 2010 16:44:30 +0000

Here is a handy little SQL Snippet that will return information about those all important SQL Server Wait Types for your server. It uses the SQL Server Dynamic Management View (DMV) sys.dm_os_wait_stats in order to extrapolate the desired information. A column has also been added to provide details of the percentage of total wait time that a particular Wait Type is responsible for.

T-SQL Query to rank SQL Server Wait Types by highest percentage of total wait time

SELECT
wait_type,
waiting_tasks_count,
max_wait_time_ms,
resource_wait_time_ms = (wait_time_ms - signal_wait_time_ms),
PercentOfAllResourceWaitTime =
(cast((wait_time_ms - signal_wait_time_ms) AS decimal(19,2)) /
(SELECT sum((wait_time_ms - signal_wait_time_ms)) FROM sys.dm_os_wait_stats))
* 100
FROM sys.dm_os_wait_stats
ORDER BY
PercentOfAllResourceWaitTime DESC

I hope you find this SQL Snippet useful in your administration of SQL Server. If you have any queries regarding this snippet, SQL Server Wait Types or anything to do with SQL Server whatsoever then be sure to let me know!

SQL Server Memory Configuration, Determining MemToLeave Settings

John Sansom — Sun, 07 Mar 2010 11:43:15 +0000

I am going to discuss some of the additional memory configuration tweaking that you may wish to undertake so that your environment can provide adequate support for workloads involving managed code, .NET CLR, Linked Servers and extended stored procedures.

Establishing a good foundation

Before considering additional tweaking of your SQL Server platform, it is wise to have already established a good foundation and to have addressed the basics.

To begin with you will want to ensure that the correct settings for both the hardware and software (Windows operating system version and SQL Server Edition) for your particular environment have been applied. You will no doubt also wish to ensure that your choice of configuration makes the maximum possible use of the resource available to you.

If you want to ensure the best configuration for your SQL Server environment then contact us now.

Additional considerations when configuring SQL server memory

Having established a good foundation, in addition to this you may find that the specific requirements of your individual platform require you to carry out further tweaking.

For example, your environment may dictate that SQL Server share the available server memory with other applications, in which case you will need to consider the distribution of memory resource between these. The ideal configuration choice is that SQL Server runs on its own dedicated server however this may not always be practical.

Now suppose your environment uses a fair amount of managed code such as .NET CLR. These technologies naturally require memory just as SQL Server does however one critical point to note is that the memory for these resources is allocated from outside of the SQL Server Buffer, in a portion of memory known as MemToLeave.

What is MemToLeave?

MemToLeave is virtual address space (VAS) that’s left un-used when SQL Server starts so that external components called by SQL Server are saved some address space. So in order for these technologies, .NET CLR, Linked Servers and extended stored procedures, to operate efficiently you must ensure that they too have access to sufficient memory.

This is why it is often recommended that you explicitly set the maximum amount of memory that SQL Server uses, as opposed to allowing it to consume all that is available on your server.

How do I determine my MemToLeave usage?

To assist in assessing your current utilisation, the T-SQL script below can be used to identify the largest available block of virtual address space (MemToLeave) outside of the buffer pool and is sourced from Christian Bolton’s Blog Post – SQL Server memtoleave, VAS and 64-bit

The query utilises the Dynamic Management View (DMV) sys.dm_os_virtual_address_dump which returns information about pages in the virtual address space of the calling process.

WITH VAS_Summary
AS
(
SELECT
Size = VAS_Dump.Size,
Reserved =
SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0)
WHEN 0 THEN 0
ELSE 1
END),
Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0)
WHEN 0 THEN 1
ELSE 0
END)
FROM
(
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],
region_allocation_base_address [Base]
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
SELECT
CONVERT(VARBINARY, region_size_in_bytes) [Size],
region_allocation_base_address [Base]
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
) AS VAS_Dump
GROUP BY Size
)
SELECT
SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB],
CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB]
FROM VAS_Summary

How can I tell if I need to allocate more memory to MemToLeave?

There are two key indicators that express a need to assign more memory to MemToLeave.

If the above T-SQL script shows that the amount of available memory is small for the requirements of your platform. (For example, your application/development team may be able to advise on the expected memory requirements of the managed code components that have been developed).
A more pressing indicator takes the form of a variety of warning/error messages raised by either SQL Server or the specific managed code component.

For example, if the MemToLeave region is too small for .NET managed code, a common indicator of this will be the appearance of frequent “Application Domain Unload” messages appearing in the SQL Server Error log. An example message is provided below:

AppDomain 8 (DatabaseName.dbo[runtime].7) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.

Another indicator is an error message that occurs when using Linked Server queries, that states:

“There is insufficient system memory to run this query.”

If you encounter any of these indicators then you almost certainly need to evaluate your SQL Server usage of VAS.

How do I allocate more resources to MemToLeave?

In the event that you determine that you require to increase the size of the MemToLeave area, this can be done by defining the -g startup option for the SQL Server Service.

The value that you assign to the parameter will determine the size of the MemToLeave area of memory.

If you are unsure of how much additional memory to allocate to MemToLeave, then it is best to increase the volume assigned in smaller increments for example 64MB (although dependent on your specific platform), until your original indicators are no longer being raised i.e. your application domain unload messages are no longer appearing.

Detailed instruction for using the -g startup option can be found in the books online article, Using the SQL Server Service Startup Options.

It is important to note that increasing the size of the MemToLeave area will reduce the amount of available memory to the SQL Server Buffer Pool. For this reason it is wise to iterate your tweaking of this parameter in small increments.

Summary

A SQL Server DBA is responsible for ensuring the performance of the platforms they administer.

After completing an initial SQL Server configuration you may need to tweak your memory configuration further in order support managed code. Where appropriate, this can be achieved by using the SQL Server -g startup parameter.

I hope you have enjoyed reading this article and that it proves fruitful in your administration of SQL Server. If you have any questions about MemToLeave or anything whatsoever about SQL Server then contact Santech now.

Santech Solutions