SQL Server - Down & Dirty: June 2016

Tuesday, 7 June 2016

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

Monday, 6 June 2016

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"