SQL Server 2016 Public Preview (CTP2) – Deploying to Azure VM

Posted on June 10, 2015 by Amit Banerjee

I had written a post earlier on deploying a SQL Server instance on a Azure Virtual Machine. Now that SQL Server 2016 CTP2 is out, let’s see how that looks on Azure. The wizard is the same as before but a new gallery option exists for deploying SQL Server 2016 CTP2. The catch is that any virtual machine created with this gallery image will expire on June 30th, 2016. The locations where this image can be deployed are East Asia, Southeast Asia, North Europe, West Europe, Central US, East US, East US 2 and South Central US. The gallery image gets provisioned with a single disk.

After the deployment is complete, you will need to enable connectivity for your SQL Server database engine as outlined in an earlier post of mine. What you get is the default instance of Database Engine, Analysis Services, Integration Services and Reporting Services. The deployment will not have the “PolyBase Query Service for External Data”. So if you are planning to test the PolyBase options in SQL Server 2016, then you will need to run the installation from the C:\SQLServer_13.0_Full folder. The other feature that is not available is the Distributed Replay. So, if you are planning to play around with these two features, then you would need to run the installer again.

Another feature which the gallery image does not use is the tempdb multiple file option setup parmater, “SQLTEMPDBFILECOUNT“. This is left at 1 so you will end up with the default tempdb configuration which you saw in the older releases. I would recommend using a virtual machine instance which has a SSD drive as the temporary drive so that you can use a SSD for testing out any intensive workload which requires either high tempdb usage or a local disk which supports high IOPs.

So now you have any option to play around with SQL Server 2016 CTP2 without having to hunt down a separate virtual machine or physical box in your environment.

SQL Server 2016 Public Preview (CTP2) – sys.dm_exec_query_stats

Posted on June 5, 2015 by Amit Banerjee

In any new release of SQL Server, one of the features that I first explore is the DMV enhancements. In SQL Server 2016 CTP 2, 24 new columns are added to sys.dm_exec_query_stats (Transact-SQL) provide information about memory grants and parallel thread usage!

I have documented in the table below the equivalent XML nodes from the execution plan which are now available in the DMV output. This saves you a lot of time from tracking down each and every plan and then shredding the XML to get the relevant information! I had documented how to parse the XML plans in an older post of mine.

Column Name	Comments	Equivalent XML Execution Plan Node
total_dop	This information is available in the XML plan and now without having to parse XML, you will be able to get this information using direct SELECTs to a DMV.	<QueryPlan DegreeOfParallelism=”4″ MemoryGrant=”28968″ CachedPlanSize=”128″ CompileTime=”9″ CompileCPU=”9″ CompileMemory=”832″>
last_dop
min_dop
max_dop
total_grant_kb	Again you are saved from XML parsing and this information lets you get statistical averages of the memory grants per execution.	<MemoryGrantInfo SerialRequiredMemory=”7168″ SerialDesiredMemory=”7392″ RequiredMemory=”28744″ DesiredMemory=”28968″ RequestedMemory=”28968″ GrantWaitTime=”0″ GrantedMemory=”28968″ MaxUsedMemory=”4384″ />
last_grant_kb
min_grant_kb
max_grant_kb
total_used_grant_kb	Again this is something that you can fetch from the XML execution plan but it is available in the DMV now. This lets you check how much of the granted memory is being used during execution.
last_used_grant_kb
min_used_grant_kb
max_used_grant_kb
total_ideal_grant_kb	This is the a good field to look at to check if there was a difference in the ideal and the actual granted values. If this is an abnormal value, then this warrants investigation.
last_ideal_grant_kb
min_ideal_grant_kb
max_ideal_grant_kb
total_reserved_threads	This is quite useful when tracking down queries which have a high number of worker threads usage	<ThreadStat Branches=”1″ UsedThreads=”4″> <ThreadReservation NodeId=”0″ ReservedThreads=”5″ /> </ThreadStat>
last_reserved_threads
min_reserved_threads
max_reserved_threads
total_used_threads
last_used_threads
min_used_threads
max_used_threads

The DMV output with the new columns is shown in the screenshot below.

There are a number of views which have been added for supporting the new Query Store feature which are:

sys.database_query_store_options
sys.query_context_settings
sys.query_store_plan
sys.query_store_query
sys.query_store_query_text
sys.query_store_runtime_stats
sys.query_store_runtime_stats_interval

Additionally, two new views have been added for supporting the new row level security feature which are:

sys.security_predicates
sys.security_policies

More details about the above views in a later post!

Reference:

sys.dm_exec_query_stats (SQL Server 2016 CTP2)

Disclaimer: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

SQL Server 2016 Public Preview (CTP2) – Let’s see how it looks

Posted on May 28, 2015 by Amit Banerjee

The Public Preview of SQL Server 2016 is available for download on the Microsoft downloads site. The feature highlights are available in this blog post. I thought I will give blog about what’s different in the setup of the Database Engine.

The basic screens remain the same. The screen to select the edition is the same and the there is no change in the “License Terms”, “Global Rules”, “Product Update”, “Install Setup Files”, “Install Rules” and “Setup Role” pages.

The first major change that you will notice is in the “Feature Selection” page. This is where you will notice a change. Namely, the “PolyBase Query Service for External Data“. This installs two services:

1. SQL Server Polybase Engine Service which creates, coordinates and executes the parallel query plan against external data sources.

2. SQL Server Polybase Data Movement Service which manages communication and data transfer between SQL Server and the external data sources which will be used by the instance.

Note that these services are not instance aware and like Integration Services, there can only be one service per Windows host.

The next screen where you will notice a change is the Feature Rules page which checks if Oracle JRE 7 Update 51 is available or not. This is required for the PolyBase service since I had selected that in the previous screen.

If you don’t have the requisite Oracle JRE version running, then you will be prompted with the following message:
Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required” failed.

This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.

The forwarding link will take you to a webpage where you can download the latest JRE update. Once that is done, you will be able to continue with the installation. Another interesting part was the check for KB2919355 which is the Windows 8.1 Update which contains a cumulative set of security updates, critical updates and updates. So the setup has an OS related check as well.

The Server Configuration page allows you to choose the service accounts for the two PolyBase services.

The Database Engine Configuration page comes with a nifty tempdb file configuration which has a bit of learning to do but provides the opportunity to create additional tempdb files during the installation itself.

Once the setup is complete, you can connect to the database engine using Management Studio and you will see that your version number is 13.0.200. Also, you will notice that there are three user databases: DWConfiguration, DWDiagnostics and DWQueue which is related to the PolyBase service. More on that maybe in a future post.

So that was all about the Database Engine setup for the new SQL Server 2016 Public Preview release.

Running SQL Nexus using Command Line Parameters

Posted on May 12, 2015 by Amit Banerjee

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data. One of the least commonly known facts is that the SQL Nexus tool also allows you to run it’s reports using command line arguments or even import diagnostic data automatically. The command line help reference is shown below. You can find more details on GitHub.

sqlnexus.exe [ [ /S [ /E | /Uuser /Ppwd ] [/D"database"] ] | [/C"connstr"] ] [/Iinputpath] [/Ooutputpath] [/Rreport] [/X]

sqlnexus.exe [ [ /S [ /E | /Uuser /Ppwd ] [/D"database"] ] | [/C"connstr"] ] [/Iinputpath] [/Ooutputpath] [/Rreport] [/X] [/Q] [/N]

/S"server"      Specifies a SQL Server name to connect to.
/D"database"    Database to connect to
/E              Log in to SQL using Windows/integrated security
/Uuser          Specifies a SQL (non-Windows) login name
/Ppassword      Specifies the password for a SQL (non-Windows) login
/C"connstring"  Specifies the SqlClient ConnectionString to use (can be used instead of /S /E).
/I"path"        Import SQL diagnostic data from this path
/R"report"      Specifies a report file name to run.
/O"path"        Specifies an export path for reports executed via /R. Also this is where the sqlnexus.000.log would get created
/X              Exit after importing (/I) or exporting (/O) the specified report (/R)
/Vparam=value   Specify the value of a form parameter
/Q              Quiet Mode - minimize windows in console mode
/N              Create a new SQLNexus database before importing (drop existing).

Automating Report Execution

The first scenario that I want to outline is the need to automate a report execution especially when you need to execute the reports repeatedly after activities like performance testing or during some other iterative activity which requires analysis of various sets of diagnostic data.

This can be achieved using the command line parameters shown below:

sqlnexus.exe /S"." /X /D"sqlnexus" /R"\Summary.rdl" /O""

As you can see from the screenshot below, you can see that I have a standard report i.e. “Bottleneck Analysis_C.xls” that was exported and a non-standard report which I created Summary.xls in the same folder. This also gives additional opportunity to create your own performance reports and schedule them using the SQL Nexus executable.

After you have the exported reports, you could also write additional automation to email the reports as required.

Automating Data Import

SQL Nexus also allows you to perform data imports automatically using the command line parameters. The command line shown below performs data import from the specified folder into a SQL Server database called sqlnexustest on the default SQL server instance installed on the machine.

sqlnexus.exe /S"." /X /D"sqlnexustest" /I"C:\temp\output"

If the database does not exist on the SQL Server instance, then it will be created. The settings for SQL Nexus utility will be used during the data import which were saved when the last time the UI was used. So if you have disabled the profiler trace import from the UI and saved your settings, then the command line execution will not import the profiler traces.

Azure Storage and SQL Server – Part 7

Posted on November 26, 2014 by Amit Banerjee

One of the recommendations of running SQL Server instances on Azure virtual machines is to ensure that default paths and directory setup is changed once the gallery image has been deployed. If you are performing a custom install of your SQL Server instance and not using an Azure Gallery Image, then you would want to instruct the setup program to install the system databases and the log directory in a separate folder other than the operating system drive or the temporary drive.

If you have used a virtual machine gallery image, then you will have a default instance of SQL Server with the binaries and system database files on the C: drive. I had earlier blogged about how to find out the startup parameters using a WMI class and VBScript. I just use PowerShell to make the WMI query. Yes, even I have learnt a few new tricks over the years! Winking smile

The PowerShell script below would let you check if the startup parameters which contains the location of the SQL Server Errorlog, the master data file and the master log file are placed on the OS drive. If yes, then it will report that you are doing something that we do not recommend! Time to change the files and the ERRORLOG to a data disk.


# Find out the boot drive on the virtual machine
$BootDrive = gwmi -Class Win32_Volume -Filter "BootVolume = 'True'" | Select-Object DriveLetter

# Get the startup parameters using the service name
# Depending on the version of SQL Server installed, the WMI Management namespace would vary
# The code block below checks the relevant WMI namespace
$Service = gwmi -Class Win32_Service -Filter "Name = 'MSSQLServer'" | Select-Object Name, PathName
if ($Service.PathName.ToString().Contains("MSSQL12"))
{
$Params = gwmi -Namespace root\Microsoft\SqlServer\ComputerManagement12 -Class SqlServiceAdvancedProperty  -Filter "SqlServiceType = 1 and PropertyName = 'STARTUPPARAMETERS' and ServiceName = 'MSSQLSERVER"
$Values = $Params.PropertyStrValue.Split(";")

}
elseif ($Service.PathName.ToString().Contains("MSSQL11"))
{
$Params = gwmi -Namespace root\Microsoft\SqlServer\ComputerManagement11 -Class SqlServiceAdvancedProperty -Filter "SqlServiceType = 1 and PropertyName = 'STARTUPPARAMETERS' and ServiceName ='MSSQLSERVER'"
$Values = $Params.PropertyStrValue.Split(";")

}
elseif ($Service.PathName.ToString().Contains("MSSQL10"))
{
$Params = gwmi -Namespace root\Microsoft\SqlServer\ComputerManagement10 -Class SqlServiceAdvancedProperty -Filter "SqlServiceType = 1 and PropertyName = 'STARTUPPARAMETERS' and ServiceName ='MSSQLSERVER'"
$Values = $Params.PropertyStrValue.Split(";")

}
else
{
Write-Host "Issue: No instances found running SQL Server 2008 or above" -ForegroundColor Red
}

# Run a foreach loop to check if the boot drive is present in the startup parameters. If yes, report the same.
foreach ($StartupParam in $Values)
{

if ($StartupParam.Contains($BootDrive.DriveLetter))
{
Write-Host "Boot drive used in" $StartupParam -ForegroundColor Red
}
}