Expert Scripting & Automation

I'm very please to announce that my new book, Expert Scripting and Automation for SQL Server DBAs has now been published.

It is already available on Amazon.com as both an e-book and a print book, and will hit the UK shelves in the coming week.

I don't believe that there is any other book quite like this one on the market, so I really hope you all enjoy the read!

Using Row-Level Security with HierarchyID

When I first heard about Row-Level Security (RSL), one of the first use cases I though of, was to satisfy queries against a hierarchical table. For example, imagine that you had a table of employees details; you could use RSL to limit the rows that each employee could view, to include only those employees who report to them.

So lets have a look at how you could achieve this, by using the HumanResources.Employee table in the AdventureWorks2016CTP3 database.

In order to implement RSL, we will need two objects. The first is a Security Predicate. This consists of an inline table-valued function, which determines if a row should be accessible. The second, is a Security Policy. The Security Policy is a new artifact type in SQL Server 2016, and binds the Security Predicate to a table.

The script below creates the Security Predicate in a schema named Security.

CREATEFUNCTION Security.fn_securitypredicate(@OrganizationNode HIERARCHYID)
    RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
FROM HumanResources.Employee e1
WHERE @OrganizationNode.IsDescendantOf(OrganizationNode) = 1
AND LoginID = 'adventure-works\' + SUSER_SNAME() ;
GO

The function accepts a single parameter, of the type HIERARCHYID. This is a complex data type, implemented through SQLCLR, which provides a number of methods, which can be used to determine position within a hierarchy, as well as hierarchy ancestry. A full method reference for the data type can be found at  https://msdn.microsoft.com/en-us/library/bb677193.aspx.

Using SCHEMABINDING on the function means that columns refferenced by the Security Predicate cannot be altered, but simplifies security administration, as the user who implicitly calls the function, will not need permissions to any tables or functions that are referenced by the Security Predicate.

The query uses the IsDecendantOf method, against the @OrganizationNode parameter (which will represent the OrganizationNode column of each row within the Employees table, to find all descendants of the row, where the LoginID column corresponds with the User ID of the user that has run the query. The concatenation of 'adventure-works\' is used to make the value returned by the SUSER_SNAME() function match the values stored in the table, where the domain of the user is also recorded. 1 is returned, for each row that matches the criteria, which tells the Security Policy that the row can be accessed.

The script below creates the Security Policy.

CREATE SECURITY POLICY Security.EmployeeSecurityPolicy
ADD FILTER PREDICATE Security.fn_securitypredicate(OrganizationNode) ON HumanResources.Employee
WITH (STATE=ON, SCHEMABINDING=ON) ;

The Security Policy is also created in the Security schema. Creating RSL objects in a separate schema is a best practice, as it simplifies security administration. The ADD FILTER PREDICATE syntax performs several functions.  

Firstly, it specifies that the predicate should be used to silently filter rows, as opposed to a BLOCK predicate, which will stop DML statements beling issued against rows, and return an error message.

Secondly, it binds the Security Predicate to the HumanResources.Employee table.

Thirdly, it passes the OrganizationNode column, from the HumanResources.Employee table, to the Security Predicate function.

The WITH statement specifies that both STATE and SCHEMABINDING are ON. STATE will determine if the Policy is enabled on creation. SCHEMABINDING will determine if a Security Predicate MUST use SCHEMABINDING, or if it is optional.

I talk more about RSL, in my upcoming book, Securing SQL Server, which will be published by Apress, in early 2017.

Regular Expressions in SQL Server

In my last post ddl triggers as work-around for policy limitations, I discussed how triggers can be used to enforce database settings are not changed. This cannot be achieved through Policy Based Management, because the Database facet does not support On Change: Log or On Change: Prevent modes.

I also noted that I was not happy with the pattern matching that I originally used within the trigger, to resolve the issue. Therefore, in this post, I will talk about how we can use CLR to gain the benefits of regular expressions in SQL Server.

So, the challenge is....We want a trigger that will ensure that no statement can turn off the AUTO CREATE STATISTICS, AUTO UPDATE STATISTICS or AUTO UPDATE STATISTICS ASYNC  settings of any database within the instance. The pattern matching offered by the LIKE predicate in T-SQL works well for changes made through the GUI, but if a change is scripted, then a developer may have used an extra space, a tab, or a line break, etc.

The first thing that we need to do to work around this, is create a CLR function, that will accept the SQL Statement from the EVENTDATA() function as a parameter and check the patter against the regex libraries. Below is the c# method to achieve this.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static int CheckMatch(SqlString statement)

    {

        Regex rgxUpdate = new Regex("AUTO_UPDATE_STATISTICS[_ASYNC]{0,}[\r\n\t{0,}[ \n\t\t]{0,}OFF", RegexOptions.IgnoreCase | RegexOptions.Singleline);

        Int16 retcounter = 0;

        string stringstatement = Convert.ToString(statement);

        Match matchUpdate = rgxUpdate.Match(stringstatement);

        if (matchUpdate.Success)

        {

            retcounter++;

        }

        Regex rgxCreate = new Regex("AUTO_CREATE_STATS[\r\n\t{0,}[ \n\t\t]{0,}OFF", RegexOptions.IgnoreCase | RegexOptions.Singleline);

        Match matchCreate = rgxCreate.Match(stringstatement);

        if (matchCreate.Success)

        {

            retcounter++;

        }

        if (retcounter > 0)

        { return 1; }

        else

        { return 0; }

    }

}

There are a few things to pay particular attention to in this code. Firstly, notice that we are importing the following .NET base libraries:

System.Data.SqlTypes

Microsoft.SqlServer.Server

System.Text.RegularExpressions

The first of these libraries, is used because we are working with the sqlstring data type, The second library is required because we are creating a function that will be used by SQL Server and the third library is required to use the regex classes.

The next thing to note in this code is the regex options. We are using regex options to treat the SQL Statement as a single line and to make the pattern match case insensitive.

Once we have built the project, we will need to create an assembly within SQL Server. This can be created using the CREATE ASSEMBLY DDL statement, as demonstrated below.

CREATE ASSEMBLY CheckStats   
FROM 'c:\sql_assemblies\checkstats.dll'  
WITH PERMISSION_SET = SAFE; 

The script assumes that we have built the project as CheckStats.dll to the c:\sqlassemblies folder.

Now that the assembly has been built, we can create the CLR function, which will call the c# method within the assembly. The script below demonstrates how to create the function.

CREATE FUNCTION dbo.CheckMatch

(

@Statement NVARCHAR(MAX)

)

RETURNS INT

AS EXTERNAL NAME CheckStats.UserDefinedFunction.CheckMatch;

Notice that we reference the method, using the 3-part name Assembly.Class.Method

Finally, we can create the server scoped DDL trigger that will respond to ALTER DATABASE statements and call the CLR function. This is demonstrated below.

CREATE TRIGGER StatisticsManagement

ON ALL SERVER

AFTER ALTER_DATABASE

AS

BEGIN

IF (SELECT dbo.CheckMatch(UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')))) = 1

BEGIN

PRINT 'Auto Stats Settings MUST Be Configured As ON';

ROLLBACK;

        END

END

You can read more about DDL Triggers in my book Pro SQL Server Administration, available from apress.com, Amazon and most book shops.

DDL Triggers as a Work Around for Policy Based Management Limitations

A college recently asked for my help in solving a problem with SQL Server Policy Based Management. He had a requirement to enforce a policy that prevent the auto update and auto create statistics settings being changed on production databases during code deployments. The problem is, that these settings are in the Database facet and this facet does not support On Chage:Prevent or On Change Log.

You can determine which actions are supported by which facets, by using the below query:

SELECT
    name ,
    'Yes' AS on_demand,
    CASE
        WHEN (CONVERT(BIT, execution_mode & 4)) = 1
            THEN 'Yes'
        ELSE 'No'
    END AS on_schedule,
    CASE
        WHEN (CONVERT(BIT, execution_mode & 2)) = 1
            THEN 'Yes'
        ELSE 'No'
    END AS on_change_log,
    CASE
        WHEN (CONVERT(BIT, execution_mode & 1)) = 1
            THEN 'Yes'
        ELSE 'No'
    END AS on_change_prevent
FROM msdb.dbo.syspolicy_management_facets ;

Instead, I suggested that he use a DDL trigger to ensure that these settings are not changed, and helped him write the below trigger definition.

CREATE TRIGGER DBA_StatisticsManagement 

ON ALL SERVER

AFTER ALTER_DATABASE

AS

BEGIN

       IF (SELECT UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')))  LIKE '%AUTO_UPDATE_STATISTICS_ASYNC OFF%'

       BEGIN

             PRINT 'Auto Update Stats Async MUST be configured as ON'

             ROLLBACK ;

       END

       IF (SELECT UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')))  LIKE '%AUTO_CREATE_STATISTICS OFF%'

       BEGIN

             PRINT 'Auto Create Stats MUST be configured as ON'

             ROLLBACK ;

       END

       IF (SELECT UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')))  LIKE '%AUTO_UPDATE_STATISTICS OFF%'

       BEGIN

             PRINT 'Auto Update Stats MUST be configured as ON'

             ROLLBACK ;

       END

END 

This trigger has been created on the instance, which means that it will reside in the Master database and is configured to fire when an ALTER DATABASE statement is executed. There is no need for it to fire when a CREATE DATABASE statement is executed, because even when using the GUI to create a database, the auto update and auto create statistics settings are configured with ALTER DATABASE statements, after the initial database creation.

The trigger pulls the statement executed from the EVENTDATA() function and then uses the LIKE predicate to pattern match the strings that would turn off the auto update and auto create statistics options. The event data is returned as an XML document and the schema can be found at http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd. Therefore, we need to use the value() Xquery method, to extract the data.

After writting this trigger, it played on my mind. Whilst this trigger will always work for changes made through the GUI, there is a chance that it will not work, if the change is made through a script. This is because of the way the LIKE predicate pattern matches the statement. For example, what is a script included an additional space before OFF?

In my next post, I will talk about how I resolved the this, by using a CLR function.

I talk more about DDL triggers and Policy Based Management, in my book, Pro SQL Server Administration, available from apress.com, Amazon and most book shops.

How to Calculate Binary Bit Masks

Ok, so I know this post is a bit geeky, but there is nothing wrong with a little geeky fun from time to time.

SQL Server uses bit maps for various thinks, such as CPU affinity and Server Agent Operator's pager availability, but how do you work with bit maps? Well, it is a case of calculating the integer expresentation of the bit map value. This is made more complex because the INT data type is a 32-bit signed integer, meaning that some of the representations will be negative numbers.

Using CPU affinity as an example, the table below shows the processor number, bit mask and signed integer representation for processors 0 - 31.

Processor Number Bit Mask                                                               Signed Integer Representation
0                             0000 0000 0000 0000 0000 0000 0000 0001      1
1                             0000 0000 0000 0000 0000 0000 0000 0010      2
2                             0000 0000 0000 0000 0000 0000 0000 0100      4
3                             0000 0000 0000 0000 0000 0000 0000 1000      8
4                             0000 0000 0000 0000 0000 0000 0001 0000     16
5                             0000 0000 0000 0000 0000 0000 0010 0000     32
6                             0000 0000 0000 0000 0000 0000 0100 0000     64
7                             0000 0000 0000 0000 0000 0000 1000 0000     128
8                             0000 0000 0000 0000 0000 0001 0000 0000     256
9                             0000 0000 0000 0000 0000 0010 0000 0000     512
10                           0000 0000 0000 0000 0000 0100 0000 0000     1024
11                           0000 0000 0000 0000 0000 1000 0000 0000     2028
12                           0000 0000 0000 0000 0001 0000 0000 0000     4096
13                           0000 0000 0000 0000 0010 0000 0000 0000     8192
14                           0000 0000 0000 0000 0100 0000 0000 0000     16384
15                           0000 0000 0000 0000 1000 0000 0000 0000     32768
16                           0000 0000 0000 0001 0000 0000 0000 0000     65536
17                           0000 0000 0000 0010 0000 0000 0000 0000     131072
18                           0000 0000 0000 0100 0000 0000 0000 0000     262144
19                           0000 0000 0000 1000 0000 0000 0000 0000     524288
20                           0000 0000 0001 0000 0000 0000 0000 0000     1048576
21                           0000 0000 0010 0000 0000 0000 0000 0000     2097152
22                           0000 0000 0100 0000 0000 0000 0000 0000     4194304
23                           0000 0000 1000 0000 0000 0000 0000 0000     8388608
24                           0000 0001 0000 0000 0000 0000 0000 0000     16777216
25                           0000 0010 0000 0000 0000 0000 0000 0000     33554432
26                           0000 0100 0000 0000 0000 0000 0000 0000     67108864
27                           0000 1000 0000 0000 0000 0000 0000 0000     134217728
28                           0001 0000 0000 0000 0000 0000 0000 0000     268435456
29                           0010 0000 0000 0000 0000 0000 0000 0000     536870912
30                           0100 0000 0000 0000 0000 0000 0000 0000     1073741824
31                           1000 0000 0000 0000 0000 0000 0000 0000     -2147483648

On a 32-core server, there are 2.631308369336935e+35 possible combinations for processor affinity, but a few examples are included below.

Aligned Processors Bit Mask Signed                                                  Integer Representation
0 and 1                    0000 0000 0000 0000 0000 0000 0000 0011      3
0, 1, 2, and 3           0000 0000 0000 0000 0000 0000 0000 1111      15
8 and 9                    0000 0000 0000 0000 0000 0011 0000 0000      768
8, 9, 10, and 11       0000 0000 0000 0000 0000 1111 0000 0000      3840
30 and 31                1100 0000 0000 0000 0000 0000 0000 0000      -1073741824
28, 29, 30, and 31   1111 0000 0000 0000 0000 0000 0000 0000      -268435456

Because an integer has a a maximum range of 2^32 then the Affinity Mask can only accommodate values for up to 32 CPUs. If your server has between 33 and 64 processors, then SQL Server relies on a 2nd value, called the 64 Bit Affinity Mask, to store the details of the next 32 CPUs.

There are a number of calculators available online, for calculating the signed integer representation of a bit mask. I tend to use www.binaryconvert.com/convert_signed_int.html

SQL Enterprise Edition vs SQL Enterprise Core Based Licensing Edition

There is a common point of confusion, when DBAs decide what version of SQL Server to install. Until SQL Server 2012, if you needed Enterprise Edition, then things were straight forward. When SQL Server 2012 was released, however, things changed, with the release of core-based licensing.

Subsequently, SQL Server Enterprise Edition should not be installed in most cases. It is designed as a "tide-me-over" edition, to see companies through, as they move from CAL based, to core based licenses.

What a lot of people do not realize, is there is actually a technical difference between the two editions. The two products have different SKUs and SQL Server Enterprise Edition will only support a maximum of 20 cores.

If you have a server large enough to test this out, you can see for yourself, by running the following query:

SELECT COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'

If you are using Enterprise Edition, then the maximum value returned will be either 20, or 40 if the server has hyper-threading turned on.

Check out my books on Apress.com:

Pro SQL Server Administration

SQL Server AlwaysOn Revealed

Expert Scripting and Automation for SQL Server DBAs 

Use SQL 2016 Query Store to Remove Ad-hoc Plans

Ad-hoc query plans consume memory and can be of limited use. It is a good idea to remove ad-doc query plans if they are not being recused. The query below demonstrates how to use Query Store metadata to identify and remove unwanted ad-hoc query plans from the cache. 

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL =

(

        SELECT 'EXEC sp_query_store_remove_query ' 

                + CAST(qsq.query_id AS NVARCHAR(6)) + ';' AS [data()]

        FROM sys.query_store_query_text AS qsqt

        JOIN sys.query_store_query AS qsq

               ON qsq.query_text_id = qsqt.query_text_id

        JOIN sys.query_store_plan AS qsp

               ON qsp.query_id = qsq.query_id

        JOIN sys.query_store_runtime_stats AS qsrs

               ON qsrs.plan_id = qsp.plan_id

        GROUP BY qsq.query_id

        HAVING SUM(qsrs.count_executions) = 1

               AND MAX(qsrs.last_execution_time) < DATEADD (HH, -24, GETUTCDATE())

        ORDER BY qsq.query_id

        FOR XML PATH('')

) ;

EXEC(@SQL) ;

You can find out more about the Query Store in SQL Server 2016, in my new book, Expert Scripting and Automation for SQL Server DBAs, available from Apress at www.apress.com/9781484219423?gtmf=s or at Amazon at www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465300485&ref_=sr_1_1&sr=8-1

Navigating a SQL Server Instance with PowerShell

As well as navigating a folder structure with commands such as get-childitem, and set-location, PowerShell can also be used to navigate the SQL Server object hierarchy of an instance. You can connect PowerShell to the SQL Server database engine provider by using set-location to navigate to SQLSERVER:\SQL The information returned by get-childitem is dependent on the current location of the object hierarchy. The table below shows what information is returned from each level of the hierarchy.

Location	Information Returned
SQLSERVER:\SQL	The name of the local machine
SQLSERVER:\SQL\ComputerName	The names of the database engine instances installed on the local machine
SQLSERVER:\SQL\ComputerName\InstanceName	Instance level object types
Lower levels	Object types or objects contained within the current location

Once you have navigated to the level of the hierarchy, in which you are interested, you are able to use PowerShell to perform basic operations against objects at that level. For example, the script below will navigate to the "tables" namespace within the AdventureWorks2016 database and rename the dbo.DatabaseLog table, to dbo.DatabaseLogPS. The dir commands will display the original name and new name of the table.

Import-Module sqlps

sl SQLSERVER:\SQL\ESASSMgmt1\MASTERSERVER\DATABASES\ADVENTUREWORKS2016\TABLES

dir | where{$_.name -like "*DatabaseLog*"}

rename-item -LiteralPath dbo.DatabaseLog -NewName DatabaseLogPS

dir | where{$_.name -like "*DatabaseLog*"}

More complex tasks that do not have a specific cmdlet, associated with them can be performed by using the invoke-sqlcmd cmdlet. 

You can also start PowerShell from within SSMS. This is done by selecting "Start PowerShell" from the context menu of an object folder, within Object Explorer. This will cause the PowerShell CLI to be invoked, with the initial location being set to the object folder that you used to invoke the CLI.

My new book, Expert Scripting and Automation for SQL Server DBAs contains extensive details and examples of how to use PowerShell to administer SQL Server. The book is available from Apress at www.apress.com/9781484219423?gtmf=s or from Amazon, at www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465300485&ref_=sr_1_1&sr=8-1

Running Administrative Queries Across Multiple Servers

As a DBA, sometimes we are asked questions, such as "What is total size of databases used by Application A?" or "Does Application A have any databases with a mismatched collation?"

If Application A has a single database server, then these questions are straight forward to answer. If Application A has databases dispersed across multiple instances, however, then the question becomes more time consuming to answer. The temptation is to connect to each instance individually, and copy/pasting the query.

It is possible to make use of Central Management Servers, to simplify answering this kind of question, however. For example, if you are supporting a large, dispersed data-tier application, you can create a server group, containing each of the instances that support the application.

For example, in the image below, you can see that I have created two folders; one for the EMEA region and another for the US.

We can now register our servers\instances under the relevant group. Below, you can see that I use the New Server Registration dialog box, to add servers. In this example, I will add two servers, nammed ESPROD1 and ESPROD2, to the  Production | NTAM server group. 

Imagine a scenario where you have a number of SQL Server instances, where the database files are hosted on a SAN (Storage Area Network). You come into work one morning, to find that there has been an issue with the production SAN and you need to check all databases, on all instances, for corruption. This can be easily achieved, using central management servers.

Because you have a separate SAN for each environment, in each data center, you will only want to check the servers in the NTAM server group, as this is where the SAN issue occurred. Therefore, select New Query, from the context menu of the NTAM server group. This will cause a new query window to be displayed and we will use DBCC CHECKDB to check the consistency of all databases on both instances. This is demonstrated below:

EXEC sp_MSforeachDB 'DBCC CHECKDB (?)' ;

This command executes the undocumented sp_MSForeachDB system stored procedure. We pass the command that we want to run against each database, into this procedure and use ? as a placeholder for the database name. This is automatically populated by sp_MSForeachDB, for each database in turn.

Below is a partial output. You can easily see which results relate to each server, along with the execution context. You may also notice the query status bar is pink instead of yellow and displays the name of the server group that we can the command against.

To learn much more about Central Management Servers, including further examples of useful queries to run across instances, full details of how to configure them and a demonstration of how to evaluate Policies against Central Management Servers or Server Groups, please refer to my new book, "Expert Scripting and Automation for SQL Server DBAs"

Apress link is www.apress.com/9781484219423?gtmf=s

Amazon US link is www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422/ref=sr_1_1?

Expert Scripting and Automation for SQL Server DBAs

My new book "Expert Scripting and Automation for SQL Server DBAs" is now available to pre-order on Amazon and Apress.com

The link for Amazon.com is www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422/ref=sr_1_1?ie=UTF8&qid=1465205714&sr=8-1&keywords=expert+scripting+and+automation

The link for Amazon.co.uk is www.amazon.co.uk/Expert-Scripting-Automation-Server-DBAs/dp/1484219422/280-0264565-3359429?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465205714&ref_=sr_1_1&sr=8-1

The link for Apress.com is www.apress.com/9781484219423?gtmf=s

It is worth noting that the expected release date on Amazon is not correct. Publication is actually expected in August.

Pro SQL Server Administration

I am very excited that my first book has now been published by Apress. Pro SQL Server Administration is available in paperback and e-book formats, pretty much everywhere, including Apress, Amazon and Waterstones


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Finding Object Dependencies In SQL Server

In a sprawling database application, it can sometime be a nightmare if you need to alter the definition of a table, for fear that you will break an object that references it, that you were unaware of. (Although this will all be detailed in your documentation of course! ;-) )

But recently I found a great DMF, which is documented on MSDN, but I had never come across, that given the name of a table, will list all of the Procedures, Functions, Views and Triggers that reference it, and you can join it back to other system object, such as sys.sql_modules to pull back other useful information, such as is the object schema_bound (which will potentially stop you altering the table, and throw an error when you run the ALTER script.)

The DMF is called sys. dm_sql_referencing_entities and the example below will list the details of all objects that depend on a table called mySchema.myTable, along with if the object is schema bound, and even the object’s definition (providing it is not encrypted).

SELECT referencing_schema_name, referencing_entity_name, is_schema_bound, [definition]

FROM sys.dm_sql_referencing_entities ('mySchema.myTable', 'OBJECT') r

INNER JOIN sys.sql_modules m

                ON r.referencing_id = m.object_id

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Find a User's Effective Permissions

There was a recent post on the MSDN forums, that paraphrased, asked, how do I find out what permissions a User has to all Objects, at all levels, including those granted by their role memberships, etc.

Now, I am assuming that this is a fairly common security auditing task, so I scanned Google to dig out a script that would do it, but to my amazement, I could not find one, so I decided to write one. It is a pretty simple script, using the fn_my_permissions function and a couple of well known system tables.

In order to run it, you will need to be able to impersonate the login that you are running it for, and of course, you will also need the appropriate permissions to the system tables.

Feel free to modify the script, but please give due credit. This will work with 2005 and 2008, but I actually wrote it in Denali, so it is defiantly forward-compatible. Enjoy...

EXECUTE AS LOGIN = 'INSERT LOGIN NAME'
 SELECT t.name,  a.subentity_name, a.permission_name
 FROM sys.objects t
 CROSS APPLY fn_my_permissions(QUOTENAME(t.name), 'OBJECT') a
 UNION ALL
 SELECT d.name, a.subentity_name, a.permission_name
 FROM sys.databases d
 CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') a
 UNION ALL
 SELECT @@SERVERNAME, a.subentity_name, a.permission_name
 FROM fn_my_permissions(NULL, 'SERVER') a
 ORDER BY t.name
 REVERT


Find my book, Pro SQL Server Administration on Amazon -

America


United Kingdom

SQL Server Southampton User Group 13/04/2011

I was speaking at the Southampton SQL UG tonight, and as promised, here are links to the code samples that I used, plus some more in depth articles on the subjects I spoke about. Thanks to everybody for a good night...!

Shrinking Databases

Why not to shrink a database here
The world's worst maintenance plan? here

Using %%physloc%%

How to find what page your data is stored on here
Logical Order NOT Physical Order here

Allocation Units

What Are Allocation Units here
Querying Allocation Units here


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

What Are Allocation Units?

I found out this week that a couple of my colleagues were unfamiliar with Allocation Units, so I thought I would knock-up a quick post explaining what they were, and how you can use them to optimize performance. So first off - what are they?

Well, traditionally, SQL Server has had a fixed limit of 8060-Bytes for a row and 8000-Bytes for a column, as a single row can not span more than one page. So, in more recent versions of SQL Server, it has been possible to push some of this data off-row, in order to let developers overcome this fixed limitation.

So now, every index, including the clustered index, or heap can have it's data split across multiple allocation units. There are 3 types of allocation unit. The first is for your standard data, and this is called the IN_ROW_DATA Allocation Unit. This is the allocation unit that will hold the majority of your data, in most cases. The second Allocation Unit is know as the LOB_DATA allocation unit. This is used to store the older type LOBs (Large Object Blocks) such as Text, NText and Image. The final Allocation Unit, which is the most recent addition, is the ROW_OVERFLOW_DATA Allocation Unit. Also know as the SLOB (Small-LOB) Allocation Unit, this is used to store newer Large Objects, such as Varchar, NVarchar, SQLVariant (although this should be avoided!) and the .NET based data-types, such as Geospatial data-types.

So, are there three allocation units per table? Oh no! There is a lot more than that! Every index can be partitioned, with a maximum of 1000 partitions, and in SQL Server 2008, you can have up to 1000, indexes, and the Allocation Units propagate down to the partition level, so it is actually possible to have 3 Million Allocation Units. In SQL Server 2005, the limit is 750,000 Allocation Units, because you are limited to 250 indexes per-table.

By default, LOBs (Text, NText and Image) are stored out of row. You can move them in row, by changing the value of 'Text In Row' by using sp_tableoption. Obviously, this will only bring in LOBs that will physically fit on the page. (i.e. They still can not be more than 8000-Bytes each, and 8060-Bytes in total), and you can also specify the maximum size for values to bring in-row. If you do not set this limit, the default is 256-Bytes.

By Default, SLOBs are stored in-row (providing they will fit in the row, using the same calculations as above) but you can push them off-row by setting the 'Large Value Types Out Of Row' option using sp_tableoption. Unlike the 'Text In Row' option, it is not possible to specify a custom size limit. Either all rows that will fit are stored in-row, or all values are stored out of row. The size limit is 8000-Bytes, and this can not be changed.

So the next question, is when should you push data off-row? Well, before we tackle that question, we need to think about how much overhead is caused by pushing data off-row, and this calculation is fairly simple. If LOBs are stored off row, a 16-Byte pointer is stored in the row. If SLOBs are pushed off-row, then a 24-Byte pointer is stored in-row. This can get slightly confusing however, because if you specify the (MAX) operator for a Varchar or NVarchar, then SQL treats it as a LOB, rather than a SLOB, and only requires a 16-Byte pointer.

So the first, and most obvious considerations are (from an SLOB perspective) are 1) Are the majority of SLOBs less than 24-Bytes? If so, there would be no benefit in pushing them off-row. In fact, it would be detrimental. 2) Are the SLOBs more than 8000-Bytes? If so, you have no choice, but to store them off row. 3) Do your SLOBs add-up to more than 8060-Bytes? If so, again, you have no choice, they must be off-row.

But what about the cases in the middle? What about if your SLOBs are all between 24 and 8000-Bytes, and together, they all fit within the 8060-Byte limit? How should you store them? Well, it depends on how you are using your data. If these SLOBs are going to be frequently queried, then you should store them in-row. If you do not, then you are increasing IO, and also forcing that IO to be random, as opposed to sequential, and random IO is not your friend!

If on the other hand, the SLOBs are essentially just a data store, and you are only querying them infrequently, but the other columns in the table are being queried, then you should store the SLOBs off-row. This is because you will be able to fit more rows on a page, and this give you two big advantages. Firstly, queries that need to read this table will have to read less pages (because there will be less pages to read). And the second reason is simply that you will use less memory. If you read less pages, then less pages will be read into the Buffer Cache, and memory pressure will be reduced.

The Five Horesmen Of The SQL Server Appocolypse!

Ok, so it should be four horsemen, but there was a two-way tie for fourth place. What can I say?

Thank you all from you votes from the What is the WORST Performing Feature Of SQL Server Poll (see this post)
You can see the results and percentages in a nice little chart at this link, and they are certainly interesting, so lets look at the winners in a bit more detail...

The clear winner is Auto shrink / Scheduled shrink. But why is this so bad? Well basically it kills performance, both during the shrink operation, and worse still, after the operation has finished. When you shrink a file, starting at the end of the file, and moving backwards, it takes each page, and moves it to the earliest available space within the file.

This is a resource intensive operation, and can cause performance issues whilst it is running, but also causes your indexes to become highly fragmented. I have already blogged about this in detail, so I won't repeat myself too much here, but please check out this post.

Next in the ranking, is Autogrow logs by 1MB . Why is this so bad? Well, once again, there is a two-fold problem. Firstly, if you log is coming under space pressure, and therefore needing to grow, then it can cause huge performance problems, as it needs to grow the Log file (in some circumstances) for almost every transaction. Remember, depending on the action you are performing, SQL may need to record before and after images of the data. I have also experienced issues in the past, where during large operations, your database suddenly ends up in Recovery, because it has not been able to keep up with the number of log grows required, and eventually resulted in the log becoming corrupt, resulting (in my case - the DB was nearly 1TB) in over an hour of downtime, and of course the transaction being rolled-back. See this post for some tips on Log File Optimization.

The other issue is Log File Fragmentation. Log Fragmentation? What on earth is that? Well, basically if you grow your log file in tiny chunks, then you end up with a massive amount of VLFs (or Virtual Log Files). On the VLDB I mentioned above, the client ended up with over 2000. The rule of thumb recommendation, is not to have more than 50! See this post for more info.

Very close behind, in 3rd place, came Functions in Select list. Why not put a function in a SELECT list? That is what they are there for isn't it? No! No, no, no, no, no, no, no! NO! :) If you put a function in a SELECT list, it needs to be evaluated separately, for every single row of the query, which is causing a "cursor like" effect, and often can not be included in the same execution plan. Lets have a look at an example, and how we could perform the task better...

Using the AdventureWorks2008DW database, I have created the following example, which is a reproduction of one of the worst SQL implementations I have seen. (The code has been changed to protect the guilty!) :)

The first thing I am going to do, is create some "dirty data" in the FactResellerSales table, to demonstrate why the code existed, with the following script...

  UPDATE [AdventureWorksDW2008R2].[dbo].[FactResellerSales]
  SET OrderQuantity = 0
  WHERE ProductKey = 351

  UPDATE [AdventureWorksDW2008R2].[dbo].[FactResellerSales]
  SET UnitPrice = 0
  WHERE ProductKey = 223

...Now to create the Function, which I will call SaftyDiv, and will basically stop a divide by 0 error occuring...

CREATE FUNCTION dbo.SaftyDiv(@FirstNumber DECIMAL, @SecondNumber DECIMAL)
RETURNS DECIMAL
AS
BEGIN
     DECLARE @ReturnValue DECIMAL

     IF (@FirstNumber = 0)
     BEGIN
          SET @ReturnValue = 0
     END
     ELSE IF (@SecondNumber = 0)
     BEGIN
          SET @ReturnValue = 0
     END
     ELSE
     BEGIN
          SET @ReturnValue = @FirstNumber / @SecondNumber
     END
     RETURN @ReturnValue
END

...So, there are so many things wrong about this function, I will not patronise you, or drive myself insane by listing them all, but lets see how it performs when we use it to select values from the table...

SELECT
       [SalesTerritoryKey]
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      , [dbo].[SaftyDiv] ([OrderQuantity], [UnitPrice])
 
  FROM [AdventureWorksDW2008R2].[dbo].[FactResellerSales]

 SQL Server Execution Times:
   CPU time = 438 ms,  elapsed time = 1559 ms.

...If you look at the Execution plan for this, it looks very clean, but that is in fact, because SQL was unable to include the function in the same plan, and actually had to create a second plan for it!

So lets see what performance we get, if we tear down the buffer cache, and then rewrite this query to use a CASE statement in the Select list. To be honest, there are more elegant ways of doing it than this, but it's getting late, and this demonstrates the point...

SELECT
       [SalesTerritoryKey]
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      , CASE WHEN ([OrderQuantity] = 0 OR [UnitPrice] = 0) THEN (SELECT 0)  ELSE (SELECT [OrderQuantity] / [UnitPrice] ) END 

  FROM [AdventureWorksDW2008R2].[dbo].[FactResellerSales]

 SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 1177 ms.

...So you can see, we got a 78.5% improvement in processor time (because it only needed to compile 1 plan, instead of 2) and we also got a 24.5% improvement in execution time. Remember, in this example, we are only dealing with aprox. 65,000 rows. Imagine if we started scaling that up to millions of rows!

Tied for fourth place, were Encrypt all data with cell level encryption and Cursors. Now I must be honest, this was a surprise for me, I though that cursors would be right up there with Auto/Scheduled shrink. Why did I think that? Well, they are the bain of my life! With no disparity meant what so ever to .NET developers, you can always tell when somebody who is experienced in writing .NET code, but less so in SQL, has been writing SQL code. That is because they love cursors! And it makes perfect sense, in .NET languages, looping is often the best way to achieve your goals, but T-SQL is a SET-Based language, meaning it is optimized for performing an operation on multiple rows at the same time, as opposed looping over a set of rows, which is exactly what a Cursor does.

There is a place is T-SQL for the use of Cursors, but these days, it is a very small, limited place, and basically only shows itself in situations where you need to iterate over a series of DDL objects, such as looping over indexes in a dynamic rebuild scenario.

For almost all other purposes, we have a better way of doing things. For example, if we need to produce a cross-tabulated query, then we have the Pivot and UnPivot operators. If we need to implement recursive logic, then we have Recursive CTEs. For concatenating rows into a string, or vice versa, we have tricks we can use with XML (see this post for an example), and for very complex logic, or string manipulation, we have CLR integration.

Lets look at an example of using a simple Cursor, versus a simple SET-Based solution to perform the same task, and see how they perform...

CREATE TABLE #Sales
(
SalesAmount DECIMAL,
RunningTotal DECIMAL
)

DECLARE @SalesAmount DECIMAL,
        @RunningTotal DECIMAL
SET @RunningTotal = 0

DECLARE myCursor CURSOR
FOR
SELECT SalesAmount
FROM FactResellerSales

OPEN myCursor
FETCH NEXT FROM myCursor INTO @SalesAmount
WHILE @@FETCH_STATUS = 0
 BEGIN
      SET @RunningTotal = @RunningTotal + @SalesAmount
      INSERT #Sales VALUES (@SalesAmount,@RunningTotal)
      FETCH NEXT FROM myCursor INTO @SalesAmount
 END

CLOSE myCursor
DEALLOCATE myCursor

SELECT * FROM #Sales
ORDER BY RunningTotal

...To be honest, I don't know how long this would take to complete, because I got bored of watching it run after about 19 minutes, (remember there are only 65,000 rows in this table) and killed it, so I could run my SET-Based version!

So, how long did the SET-Based version take? Drumb roll...

SELECT a.SalesAmount,
       SUM(b.SalesAmount) AS RunningTotal
FROM FactResellerSales a
INNER JOIN FactResellerSales b
ON (a.SalesOrderNumber = b.SalesOrderNumber
        AND a.SalesOrderLineNumber = b.SalesOrderLineNumber)
GROUP BY a.SalesAmount
ORDER BY RunningTotal

...Less than 1 Second! I rest my case! :)

So finally, what is wrong with using cell-level encryption? Well nothing, if used in moderation, and it is often a necessary evil, in order to meet regulatory requirements, but if you over-indulge, then you have a problem.

If you need to encrypt a CreditCardNumber column to meet a regulatory requirement, then this is fine. Use a symmetric key, and avoid encrypting that key with an asymmetric key, and that with a certificate, and so on! But do not go right on ahead and encrypt the entire table, despite there being no requirement to do so, other than a manager "thinks it might be a good idea". As a technical person, it is your responsibility to point out the limitations of technology to the business, so if you, as I have, had a Director telling you that they want the salaries encrypted, to avoid the slightest risk that somebody might find out what a disgustingly large bonus they get, the correct answer is something along the lines of my standard reply... "Yes Sir, certainly, you are the customer, and if that's what you want, then I can make it happen. However, please be warned that depending of the encryption algorithms used, then this can cause a performance degradation of up to 45-50% when accessing that data, and can cause a data-bloat of up to 4000%!" That is normally enough to make people see it your way! ;-)

I hope you find these results as interesting as I did.


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom