European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

European SQL 2017 Hosting :: How to Create Registration Form in ASP.NET with SQL Server Database

clock March 19, 2019 11:25 by author Scott

In this post we will see how to create ASP.NET Registration form and save its data in MS SQL Database. It will be quite simple form with values like Employee Id, Employee name, Date of Birth, Country, State, City names and more. We will be also adding Profile Image of the user and saving it to ASP.NET Project’s folder to complete our Registration form data. So let’s see.

I’m also using AJAX to create this registration form in ASP.NET. You can add it to your project by using Visual Studio’s Nuget Manager Console.

First create a new ASP.NET web project in Visual studio. I’m using Visual studio 2017 to create this registration form for ASP.NET.

Now add a new ASPX form with name Registration.aspx and edit it as below:

Registration.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs"
Inherits="RegistrationForms.Registration" ValidateRequest="true"%>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<link href="css/bootstrap.min.css" rel="stylesheet" />
<style>
.borderless td, .borderless th {
border: none;
border-color: Red;
}

.table-condensed > thead > tr > th, .table-condensed > tbody > tr > th, .table-condensed > tfoot > tr > th, .table-condensed > thead > tr > td, .table-condensed > tbody > tr > td, .table-condensed > tfoot > tr > td {
padding: 3px;
}

input, select {
border-radius: 3px;
padding: 1px;
border: 1px solid darkgray;
}

.btnCoral {
background-color: crimson;
color: #fff;
}

body {
/* Permalink - use to edit and share this gradient: http://colorzilla.com/gradient-editor/#1e5799+0,2989d8+50,207cca+51,7db9e8+100;Blue+Gloss+Default */
background: #1e5799; /* Old browsers */
background: -moz-linear-gradient(top, #1e5799 0%, #2989d8 50%, #207cca 51%, #7db9e8 100%); /* FF3.6-15 */
background: -webkit-linear-gradient(top, #1e5799 0%,#2989d8 50%,#207cca 51%,#7db9e8 100%); /* Chrome10-25,Safari5.1-6 */
background: linear-gradient(to bottom, #1e5799 0%,#2989d8 50%,#207cca 51%,#7db9e8 100%); /* W3C, IE10+, FF16+, Chrome26+, Opera12+, Safari7+ */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#1e5799', endColorstr='#7db9e8',GradientType=0 ); /* IE6-9 */
}

.parent-container {
background-color: black;
width:70%;
}

.container {
background-color: white;
margin:2px;
width:auto;
}

</style>
<title>Registration Form</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager runat="server" />
<div class="parent-container">
<div class="container">
<table class="table-condensed borderless">
<tr>
<td align="center" colspan="2">
<h2 style="background-color: black; color: White; padding: 5px;">REGISTRATION FORM</h2>
</td>
</tr>
<tr>
<td align="right">EMP Id:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtEmpId" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtEmpId" ErrorMessage="*" ForeColor="Red"
ValidateRequestMode="Enabled"/>
</td>
</tr>
<tr>
<td align="right">Name:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtName" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtName" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Date of Birth:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtDOB" />
<ajax:CalendarExtender runat="server" Format="yyyy-MM-dd" TargetControlID="txtDOB" />

<asp:RequiredFieldValidator runat="server" ControlToValidate="txtDOB" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Address:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtAddress" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtAddress" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Country:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddCountry"
AutoPostBack="true" OnSelectedIndexChanged="ddCountry_SelectedIndexChanged" /></td>
</tr>
<tr>
<td align="right">State:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddState"
AutoPostBack="true" OnSelectedIndexChanged="ddState_SelectedIndexChanged" /></td>
</tr>
<tr>
<td align="right">City:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddCity" /></td>
</tr>
<tr>
<td align="right">Pincode:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtPincode" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtPincode" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Date of Joining:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtDOJ" />
<ajax:CalendarExtender runat="server" Format="yyyy-MM-dd" TargetControlID="txtDOJ" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtDOJ" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Department:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddDepartment" /></td>
</tr>
<tr>
<td align="right">Role:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddRole">

<asp:ListItem Text="USER" Value="USER" />
<asp:ListItem Text="ROLE" Value="ROLE" />

</asp:DropDownList></td>
</tr>
<tr>
<td align="right">Profile Pic:</td>
<td align="left" valign="middle">
<asp:FileUpload runat="server" ID="fileUpload" />
<asp:Button runat="server" Text="Upload" ID="btnUpload" OnClick="btnUpload_Click" />

<asp:Image ID="imgProfile" runat="server" Width="150px" Height="150px" />
</td>
</tr>

<tr>
<td align="right">Email ID:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtEmail" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtEmail" ErrorMessage="*" ForeColor="Red" />
<asp:RegularExpressionValidator runat="server" ValidationExpression="\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" ControlToValidate="txtEmail"
ErrorMessage="Email address invalid" />
</td>
</tr>
<tr>
<td align="right">Password:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtpassword" TextMode="Password"/>
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtpassword" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Confirm Password:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtConfirmPassword" TextMode="Password"/></td>
</tr>
<tr>
<td colspan="20">
<asp:CompareValidator ID="comparePasswords"
runat="server"
ControlToCompare="txtpassword"
ControlToValidate="txtConfirmPassword"
ErrorMessage="Passwords do not match up."
ForeColor="Red" />
</td>
</tr>
<tr>
<td colspan="2" align="center">
<br />
<asp:Button runat="server" ID="btnSubmit" Text="Submit" OnClick="btnSubmit_Click"
CssClass="btn btnCoral" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="lblInfo" />
</td>
</tr>
</table>
</div>

</div>
</form>
</body>
</html>

 

Ajax’s Data picker is used to set Date of Birth and Date of Joining information of the employee to complete the registration process.

Profile picture will be saved inside “ProfileImages” folder.

 

And below is my code for the registration page:

using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;
using System.Web.UI;

namespace RegistrationForms
{
public partial class Registration : System.Web.UI.Page
{
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter adapter;
SqlDataReader reader;
DataSet ds;
DataTable dt;

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadCountries();
LoadDepartment();
}
}

public void LoadDepartment()
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblDepartments", con);
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["DeptId"] = 0;
dr["Department"] = "Please select Department";
dt.Rows.InsertAt(dr, 0);

ddDepartment.DataSource = dt;
ddDepartment.DataTextField = "Department";
ddDepartment.DataValueField = "DeptId";
ddDepartment.DataBind();
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
lblInfo.Text = ex.Message.ToString();
}
}
public void LoadCountries()
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblCountries", con);
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["CountryId"] = 0;
dr["Country"] = "Please select Country";
dt.Rows.InsertAt(dr, 0);


ddCountry.DataSource = dt;
ddCountry.DataTextField = "Country";
ddCountry.DataValueField = "CountryId";
ddCountry.SelectedIndex = 0;
ddCountry.DataBind();
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
lblInfo.Text = ex.Message.ToString();
}

}
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
if (Page.IsValid)
{

con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_AddUsers";
cmd.Connection = con;
cmd.Parameters.AddWithValue("@UserId", txtEmpId.Text.ToString());
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@DOB", txtDOB.Text.ToString());
cmd.Parameters.AddWithValue("@Address", txtAddress.Text.ToString());
cmd.Parameters.AddWithValue("@City", ddCity.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@State", ddState.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@Country", ddCountry.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@Pincode", txtPincode.Text.ToString());
cmd.Parameters.AddWithValue("@Department", ddDepartment.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@UserRole", ddRole.SelectedItem.Text.ToString());
cmd.Parameters.AddWithValue("@Passcode", txtpassword.Text.ToString());
cmd.Parameters.AddWithValue("@DOJ", txtDOJ.Text.ToString());
cmd.Parameters.AddWithValue("@ProfilePic", imgProfile.ImageUrl.ToString());
cmd.Parameters.AddWithValue("@EmailId", txtEmail.Text.ToString());
con.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
ShowAlert("Success", "Saved Successfully");
ClearFeilds();
}
else
ShowAlert("Error", "Please enter all fields");
}
catch (Exception ex)
{

}
}

public void ShowAlert(String header, String message)
{
ScriptManager.RegisterStartupScript(this, GetType(), header, "alert('" + message + "');", true);
}

public void ClearFeilds()
{
txtAddress.Text = "";
txtConfirmPassword.Text = "";
txtDOB.Text = "";
txtDOJ.Text = "";
txtEmail.Text = "";
txtEmpId.Text = "";
txtName.Text = "";
txtpassword.Text = "";
txtPincode.Text = "";
LoadCountries();
}
protected void ddCountry_SelectedIndexChanged(object sender, EventArgs e)
{
LoadState();
}

public void LoadState()
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblStates where CountryId=@id", con);
cmd.Parameters.AddWithValue("@id", ddCountry.SelectedValue.ToString());
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["StateId"] = 0;
dr["State"] = "Please select State";
dr["CountryId"] = "0";
dt.Rows.InsertAt(dr, 0);
ddState.Items.Clear();
ddState.ClearSelection();
ddState.DataSource = dt;
ddState.DataTextField = "State";
ddState.DataValueField = "StateId";
ddState.DataBind();
ddState.SelectedValue = null;
ddState.SelectedIndex = 0;
ddCity.Items.Clear();
ddCity.SelectedValue = null;
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
}
}

protected void ddState_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblCities where Stateid=@id", con);
cmd.Parameters.AddWithValue("@id", ddState.SelectedValue.ToString());
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["CityId"] = 0;
dr["City"] = "Please select City";
dr["StateId"] = "0";
dt.Rows.InsertAt(dr, 0);
ddCity.DataSource = null;
ddCity.DataSource = dt;
ddCity.DataTextField = "City";
ddCity.DataValueField = "CityId";
ddCity.DataBind();
ddCity.SelectedValue = null;
ddCity.SelectedIndex = 0;
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
}
}

protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
if (fileUpload.HasFile)
{
string fileName = Path.GetFileName(fileUpload.PostedFile.FileName);
fileUpload.PostedFile.SaveAs(Server.MapPath("~/ProfileImages/") + fileName);
imgProfile.ImageUrl = "~/ProfileImages/" + fileName;
}
}
catch (Exception ex)
{
lblInfo.Text = "Image upload: " + ex.Message.ToString();
}
}
}
}

Below is the Database script (MS SQL Database):

--Create database [ESource]
USE [ESource]

CREATE TABLE [dbo].[tblStates](
[StateId] [int] IDENTITY(1,1) NOT NULL,
[State] [nvarchar](50) NOT NULL,
[CountryId] [int] NOT NULL
)

CREATE TABLE [dbo].[tblCities](
[CityId] [int] IDENTITY(1,1) NOT NULL,
[City] [nvarchar](50) NOT NULL,
[StateId] [int] NOT NULL
)

CREATE TABLE [dbo].[tblCountries](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[Country] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[tblDepartments](
[DeptId] [int] IDENTITY(1,1) NOT NULL,
[Department] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[tblUserDetails](
[EId] [int] IDENTITY(1,1) NOT NULL,
[EmailId] [nvarchar](50) NOT NULL,
[UserId] [nvarchar](50) NULL,
[Passcode] [nvarchar](50) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[DOB] [datetime] NOT NULL,
[Address] [nvarchar](max) NOT NULL,
[City] [nvarchar](50) NOT NULL,
[Pincode] [int] NOT NULL,
[State] [nvarchar](50) NOT NULL,
[Country] [nvarchar](50) NOT NULL,
[Department] [nvarchar](50) NOT NULL,
[DOJ] [datetime] NOT NULL,
[ProfilePic] [nvarchar](max) NOT NULL,
[UserRole] [nvarchar](50)
)

CREATE procedure [dbo].[sp_AddUsers]
(
@UserId nvarchar(50),
@Passcode nvarchar(50),
@Name nvarchar(50),
@DOB datetime,
@Address nvarchar(max),
@City int,
@Pincode nvarchar(50),
@State int,
@Country int,
@Department int,
@DOJ datetime,
@ProfilePic nvarchar(max),
@UserRole nvarchar(50),
@EmailId nvarchar(50)
)
as begin
Insert into tblUserDetails
(UserId,Passcode,Name,DOB,Address,City,Pincode,State,Country,Department,DOJ,ProfilePic,UserRole,EmailId)
values
(@UserId,@Passcode,@Name,@DOB,@Address,@City,@Pincode,@State,@Country,@Department,@DOJ,@ProfilePic,@UserRole,@EmailId);
end
GO



European SQL Hosting :: Performance Between CLR vs T-SQL

clock March 6, 2019 08:17 by author Scott

I am pretty sure that all of us read or even participated in quite a few heated discussions about Common Language Runtime (CLR) code in Microsoft SQL Server. Some people state that CLR code works faster than T-SQL, others oppose them. Although, as with the other SQL Server technologies, there is no simple answer to that question. Both technologies are different in nature and should be used for the different tasks. T-SQL is the interpreted language, which is optimized for set-based logic and data access. CLR, on the other hand, produces compiled code that works the best for imperative procedural-style code.

Even with imperative code, we need to decide if we want to implement it in CLR or as the client-side code, perhaps running on the application servers. CLR works within SQL Server process. While, on one hand, it eliminates network traffic and can provide us the best performance due to the “closeness” to the data, CLR adds the load to the SQL Server. It is usually easier and cheaper to scale out application servers rather than upgrading SQL Server box.

There are some cases when we must use CLR code though. For example, let’s think about the queries that performing RegEx evaluations as part of the where clause. It would be inefficient to move such evaluations to the client code and there is no regular expressions support in SQL Server. So CLR is the only choice we have. Although, in the other cases, when procedural-style logic can be moved to the application servers, we should consider such option. Especially when application servers are residing closely to SQL Server and network latency and throughput are not an issue.

Today we will compare performance of the few different areas of CLR and T-SQL. I am not trying to answer the question – “what technology is better”. As usual it fits into “It depends” category. What I want to do is looking how technologies behave in the similar tasks when they can be interchanged.

Before we begin, let’s create the table and populate it with some data.


As the first step, let’s compare the user-defined functions invocation cost. We will use the simple function that accepts the integer value as the parameter and returns 1 in case if that value is even. We can see CLR C# implementation below.

As we can see, there are the attributes specified for each function. Those attributes describes different aspects of UDF behavior and can help Query Optimizer to generate more efficient execution plans. I would recommend specifying them explicitly rather than relying on default values.

One of the attributes – DataAccess – indicates if function performs any data access. When this is the case, SQL Server calls the function in the different context that will allow access to the data. Setting up such context introduces additional overhead during the functional call, which we will see in a few minutes.

T-SQL implementation of those functions would look like that:


Let’s measure average execution time for the statements shown below. Obviously, different hardware leads to the different execution time although trends would be the same.

Each statement performs clustered index scan of dbo.Numbers table and checks if Num column is even for every row from the table. For CLR and T-SQL scalar user-defined functions, that introduces the actual function call. Inline multi-statement function, on the other hand, performed the calculation inline without function call overhead.

As we can see, CLR UDF without data access context performs about four times faster comparing to T-SQL scalar function. Even if establishing data-access context introduces additional overhead and increases execution time, it is still faster than T-SQL scalar UDF implementation.

The key point here though is than in such particular example the best performance could be achieved if we stop using the functions at all rather than converting T-SQL implementation to CLR UDF. Even with CLR UDF, the overhead of the function call is much higher than inline calculations.

Unfortunately, this is not always the case. While we should always think about code refactoring as the option, there are the cases when CLR implementation can outperform inline calculations even with all overhead it introduced. We are talking about mathematical calculations, string manipulations, XML parsing and serialization – to name just a few. Let’s test the performance of the functions that calculate the distance between two points defined by latitude and longitude.

 



We can see that CLR UDF runs almost two times faster comparing to inline table-valued functions and more than five times faster comparing to T-SQL scalar UDF. Even with all calling overhead involved.

Now let’s look at the data access performance. The first test compares performance of the separate DML statements from T-SQL and CLR stored procedures. In that test we will create the procedures that calculate the number of the rows in dbo.Numbers table for specific Num interval provided as the parameters. We can see the implementation below

Table below shows the average execution time for stored procedure with the parameters that lead to 50,000 individual SELECT statements. As we can see, data access from CLR code is much less efficient and works about five times slower than data access from T-SQL.

Now let’s compare performance of the row-by-row processing using T-SQL cursor and .Net SqlDataReader class.

As we can see, SqlDataReader implementation is faster.

Finally, let’s look at the performance of CLR aggregates. We will use standard implementation of the aggregate that concatenates the values into comma-separated string.


As with user-defined functions, it is extremely important to set the attributes that tell Query Optimizer about CLR Aggregate behavior and implementation. This would help to generate more efficient execution plans and prevent incorrect results due to optimization. It is also important to specify MaxByteSize attribute that defines the maximum size of the aggregate output. In our case, we set it to -1 which means that aggregate could hold up to 2GB of data.

Speaking of T-SQL implementation, let’s look at the approach that uses SQL variable to hold intermediate results. That approach implements imperative row-by-row processing under the hood.

As another option let’s use FOR XML PATH technique. It is worth to mention that this technique could introduce different results by replacing XML special characters with character entities. For example, if our values contain < character, it would be replaced with &lt; string.

Our test code would look like that:


When we compare the performance on the different row set sizes, we would see results below

As we can see, CLR aggregate has slightly higher startup cost comparing to T-SQL variable approach although it quickly disappears on the larger rowsets. Performance of both: CLR aggregate and FOR XML PATH methods linearly depend on the number of the rows to aggregate while performance of SQL Variable approach degrade exponentially. SQL Server needs to initiate the new instance of the string every time it concatenates the new value and it does not work efficiently especially when it needs to be populated with the large values.

The key point I would like to make with that example is that we always need to look at the options to replace imperative code with declarative set-based logic. While CLR usually outperforms procedural-style T-SQL code, set-based logic could outperform both of them.

While there are some cases when choice between technologies is obvious, there are the cases when it is not clear. Let us think about scalar UDF that needs to perform some data access. Lower invocation cost of CLR function can be mitigated by higher data access cost from there. Similarly, inline mathematical calculations in T-SQL could be slower than in CLR even with all invocation overhead involved. In those cases, we must test different approaches and find the best one which works in that particular case.



SQL Server 2017 Hosting :: How to Know that Your SQL Server Eat too Much Memory?

clock January 29, 2019 08:14 by author Scott

Sounds impossible, right? The saying goes that you can never be too rich or too thin or have too much memory.

However, there is one good indication that your SQL Server is probably overprovisioned, and to explain it, I need to cover 3 metrics.

1. Max Server Memory is set at the instance level: right-click on your SQL Server name in SSMS, click Properties, Memory, and it’s “Maximum server memory.” This is how much memory you’re willing to let the engine use. (The rocket surgeons in the audience are desperate for the chance to raise their hands to point out different things that are or aren’t included in max memory – hold that thought. That’s a different blog post.)

2. Target Server Memory is how much memory the engine is willing to use. You can track this with the Perfmon counter SQLServer:Memory Manager – Target Server Memory (KB):

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Target Server%';

Generally, when you start up SQL Server, the target is set at your max. However, SQL Server doesn’t allocate all of that memory by default. (Again, the experts wanna raise hands and prove how much they know – zip it.)

3. Total Server Memory is roughly how much the engine is actually using. (Neckbeards – seriously – zip it until the end of the post. I’m not playing.) After startup, SQL Server will gradually use more and more memory as your queries require it. Scan a big index? SQL Server will start caching as much of that index as it can in memory. You’ll see this number increase over time until – generally speaking – it matches target server memory.

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Total Server%';

What if Total Server Memory doesn’t go up?

Say you have a SQL Server with:

  • 64GB memory
  • Max memory set to 60GB
  • 1GB of total database size (just to pick an extreme example)

Infrequent query workloads (we don’t have hundreds of users trying to sort the database’s biggest table simultaneously, or do cartesian joins)

SQL Server might just not ever need the memory.

And in a situation like this, after a restart, you’ll see Total Server Memory go up to 2-3GB and call it a day. It never rises up to 10GB, let alone 60GB. That means this SQL Server just has more memory than it needs.

Here’s an example of a server that was restarted several days ago, and still hasn’t used 4GB of its 85GB max memory setting. Here, I’m not showing max memory – just the OS in the VM, and target and total:



In most cases, it’s not quite that black-and-white, but you can still use the speed at which Total Server Memory rises after a reboot to get a rough indication of how badly (and quickly) SQL Server needs that memory. If it goes up to Target Server Memory within a couple of hours, yep, you want that memory bad. But if it takes days? Maybe memory isn’t this server’s biggest challenge.

Exceptions obviously apply – for example, you might have an accounting server that only sees its busiest activity during close of business periods. Its memory needs might be very strong then, but not such a big deal the rest of the month.



European SQL 2016 Hosting - HostForLIFE.eu :: Warehousing JSON formatted data in SQL Server 2016

clock March 8, 2017 10:34 by author Scott

In this article, I continue to review the exciting features available in SQL Server 2016. One such feature is the long awaited T-SQL support for JSON formatted data. In this article we take a look at how JSON support will impact data warehouse solutions.

Background

Since the advent of EXtensible Markup Language (XML) many modern web applications have focused on providing data that is both human-readable and machine-readable. From a relational database perspective, SQL Server kept up with these modern web applications by providing support for XML data in a form of an XML data type and several functions that could be used to parse, query and manipulate XML formatted data.

As a result of being supported in SQL Server, data warehouse solutions based off SQL Server were then able to source XML-based OLTP data into a data mart. To illustrate this point, let’s take a look at the XML representation of our fictitious Fruit Sales data shown in figure below.

To process this data in data warehouse, we would first have to convert it into relational format of rows and columns using T-SQL XML built-in functions such as the nodes() function. 

The results of the above script are shown in figure below in a recognisable format for data warehouse.

Soon after XML became a dominant language for data interchange for many modern web applications, JavaScript Object Notation (JSON) was introduced as a lightweight data-interchange format that is more convenient for web applications to process than XML. Likewise most relational database vendors released newer versions of their database systems that included the support for JSON formatted data. Unfortunately, Microsoft SQL Server was not one of those vendors and up until SQL Server 2014, JSON data was not supported. Obviously this lack of support for JSON, created challenges for data warehouse environments that are based off SQL Server.

Although there were workarounds (i.e. using Json.Net) to addressing the lack of JSON support in SQL Server, there was always sense that these workarounds were inadequate, time-wasting, and were forcing data warehouse development teams to pick up a new skill (i.e. learn .Net). Fortunately, the release of SQL Server 2016 has ensured that development teams can throw away their JSON workarounds as JSON is supported in SQL Server 2016.

Parsing JSON Data into Data Warehouse

Similarly to XML support in SQL Server, SQL Server supports of JSON can be classified into two ways:

  • Converting Relational dataset into JSON format
  • Converting JSON dataset into relational format

However, for the purposes of this discussion we are focusing primarily on the second part – which is converting a JSON formatted data (retrieved from OLTP sources) into a relational format of rows and columns. To illustrate our discussion points we once again make use of the fictitious fruit sales dataset. This time around the fictitious dataset has been converted into a JSON format as shown below.

ISJSON function

As part of supporting JSON formatted data in other relational databases such as MySQL and PostgreSQL 9.2, there is a separate JSON data type that has been introduced by these vendors. Amongst other things, JSON data type conducts validation checks to ensure that values being stored are indeed of valid JSON format.

Unfortunately, SQL Server 2016 (and ORACLE 12c) do not have a special data type for storing JSON data instead a variable character (varchar/nvarchar) data type is used. Therefore, a recommended practice to dealing with JSON data in SQL Server 2016 is to firstly ensure that you are indeed dealing with a valid JSON data. The simplest way to do so is to use the ISJSON function. This is a built-in T-SQL function that returns 1 for a valid JSON dataset and 0 for invalids.

Image below shows us the implementation of ISJSON function whereby we validate our fictitious sample dataset.

OPENJSON function

Now that we have confirmed that we are working with a valid JSON dataset, the next step is to convert the data into a table format. Again, we have a built-in T-SQL function to do this in a form of OPENJSON. OPENJSON works similar to OPENXML in that it takes in an object and convert its data into rows and columns.

Figure below shows a complete T-SQL script for converting JSON object into rows and columns.

Once we execute the above script, we get relational output shown below.

Now that we have our relational dataset, we can process this data into data warehouse.

JSON_VALUE function

Prior to concluding our discussion of JSON in SQL Server 2016, it is worth mentioning that in addition to OPENJSON, you have other functions such as JSON_VALUE that could be used to query JSON data. However this function returns a scalar value which means that unlike the multiple rows and columns returned using OPENJSON, JSON_VALUE returns a single value as shown below.

If you the JSON object that you are querying doesn’t have multiple elements, than you don’t have to specify the row index (i.e. [0]) as shown below.

Conclusion

The long wait is finally over and with the release of SQL Server 2016, JSON is now supported. Similarly to XML, T-SQL support the conversion of JSON object to relational format as well the conversion of relational tables to a JSON object. This support is implemented via built-in T-SQL functions such as OPENJSON and JSON_VALUE. In spite of all the excitement with the support of JSON is SQL Server 2016, we still don’t have a JSON data type. The ISJSON function can then be used to validate JSON text.



European SQL 2016 Hosting - HostForLIFE.eu :: MSSQL Server Comes with JSON?

clock January 17, 2017 10:27 by author Scott

With over a thousand votes on the Microsoft Connect site, JSON support is the most requested feature for SQL Server 2016. This month, Microsoft announced that the upcoming release of SQL Server 2016 will fulfill that request. Sort of.

Microsoft will certainly be touting this feature as an additional reason to upgrade when the time comes. Natively supporting JSON helps bridge the gap between the desirable aspects of a NoSQL database and a relational database like SQL Server. The trouble is that with this current implementation, you're not really gaining anything over what's currently available. It's nothing like the native XML data type that became available starting with SQL Server 2005.

The announcement starts off with a big caveat: they will be providing native JSON support, but not a native JSON type. In fact, storage of JSON data will happen the same way it happens today, in a NVARCHAR typed column. They list 3 hollow reasons for this:

  • Backward compatibility
  • Cross feature compatibility
  • Non Microsoft controlled JSON parsers on the client (C#)

Backward compatibility is weak, if you're already storing JSON data you wouldn't have a hard time moving into the JSON type. Cross feature compatibility means that they're not interested in implementing JSON in other SQL Server components, so instead everything that already works with NVARCHAR (aka everything) will still work (aka nothing's changed). The client side JSON parser point is an odd one and it leads into my next gripe of this feature implementation.

On the client side, such as in a C# application, it's already common to serialize data back and forth in JSON using a JSON parsing library. The most common, though maybe not the fastest, is the Newtonsoft JSON.Net library which comes packaged with the default templates in ASP.NET projects etc. The fact that there are other options out there doesn't really matter in my opinion. Sure some behave differently, but in the end, a JSON object has a specific syntax and any proper parser should be able to serialize and deserialize the output of any other parser.

What's baffling are the features that are being supported in this native JSON support.

You can now use FOR JSON to export the results of a query as JSON. This means that you can make a normal T-SQL query and ask for the result back as a JSON formatted result.  OK, but I could have just serialized the result on the client side in literally one line of code. They use the example of returning the results directly from an OData request through a web service.

You can transform a JSON object to a relational table with OPENJSON. This means that within a T-SQL query, you can provide a JSON object as part of the query and each item in the JSON will be returned as a table row which can be used to query or insert records into a relational table. They use the example of loading a JSON document into rows of a database, but again, that can easily be done on the client site by deserializing the JSON Array first.

Finally they are providing some built in functions for processing JSON data in the database. ISJSON will check if a NVARCHAR column has JSON data and JSON_VALUE which provides some scalar selection ability similar to the dot notation of JavaScript, e.g. 

$.property1.property2.array1[5].property3.array2[15].property4

That's basically the least they could do with this implementation. And as for indexing of JSON data, you're left with some basic support on the level of full-text indexing of any other NVARCHAR column.

So in the end, this "native JSON support" is basically nothing more that a few convenience function on top of a normal NVARCHAR column. It takes a familiar eye about 2 minutes to uncover the sad truth about this feature and the response has shown that Microsoft isn't fooling anybody with this JSON implementation. That's not to say there aren't great features coming in SQL Server 2016 (stretch database seems pretty cool), but JSON support isn't one of them.



European Entity Framework Core 1.0 Hosting - HostForLIFE.eu :: How to Access SQL from Entity Framework Core

clock January 12, 2017 08:33 by author Scott

ORMs like Entity Framework have always been shrouded in a bit of controversy from SQL purists who aren't in control of the types of queries that these tools are building behind the scenes. While this is a perfectly valid stance to take, these same tools can often assist in getting things done much faster than tinkering with an endless chain of JOINs, sub-queries, etc.

But what if you want to know exactly what is going on behind the scenes? What SQL is being generated by Entity Framework? And is it efficient enough to suit your needs, or do you need to handle writing some yourself?

This post will cover a quick tip that will allow you to see the SQL behind the scenes and judge for yourself using Entity Framework Core.

How Did This Work Prior to Entity Framework Core?

Previously, you could use Reflection to create an ObjectQuery object and then call the ToTraceString()method to actually store the query results as seen below:

// Build a query using Entity Framework
var query = _context.Widgets.Where(w => w.IsReal && w.Id == 42); 
// Get the generated SQL
var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString(); 

And that's really it. The result of the ToTraceString() call will return a string variable containing the entire SQL query being executed.

Options for Entity Framework Core

The previous approach no longer works within the Entity Framework Core (EF7) world, so we have to resort to one of three options, which may vary depending on your needs:

  • Using Built-in or Custom Logging. Logging the executing query using your logger of choice or the built-in Logger in .NET Core as mentioned in this tutorial.
  • Using a Profiler. Using an SQL Profiler like MiniProfiler to monitor the executing query.
  • Using Crazy Reflection Code. You can implement some custom reflection code similar to the older approach to perform the same basic concept.

Since both of the first two options are fairly well documented, we will be focusing on the crazy reflection approach.

Getting Behind the Scenes in Entity Framework Core

Using the following snippets of code, which rely on Reflection to resolve information about the compiler, parser, database, and fields being targeted, we can use those things to reconstruct what is actually going on behind the scenes.

NOTE: Since this relies on Reflection, it may be subject to breaking in the future, especially with any API changes.

public class IQueryableExtensions 
{
    private static readonly FieldInfo QueryCompilerField =
    typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields.First
    (x => x.Name == "_queryCompiler");

    private static readonly PropertyInfo NodeTypeProviderField =
    QueryCompilerTypeInfo.DeclaredProperties.Single(x => x.Name == "NodeTypeProvider");

    private static readonly MethodInfo CreateQueryParserMethod =
    QueryCompilerTypeInfo.DeclaredMethods.First(x => x.Name == "CreateQueryParser");

    private static readonly FieldInfo DataBaseField =
    QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");

    private static readonly FieldInfo QueryCompilationContextFactoryField =
    typeof(Database).GetTypeInfo().DeclaredFields.Single
    (x => x.Name == "_queryCompilationContextFactory");

    public static string ToSql<TEntity>
    (this IQueryable<TEntity> query) where TEntity : class
    {
        if (!(query is EntityQueryable<TEntity>)
        && !(query is InternalDbSet<TEntity>))
        {
            throw new ArgumentException("Invalid query");
        }

        var queryCompiler = (IQueryCompiler)QueryCompilerField.GetValue(query.Provider);
        var nodeTypeProvider =
        (INodeTypeProvider)NodeTypeProviderField.GetValue(queryCompiler);
        var parser = (IQueryParser)CreateQueryParserMethod.Invoke
        (queryCompiler, new object[] { nodeTypeProvider });
        var queryModel = parser.GetParsedQuery(query.Expression);
        var database = DataBaseField.GetValue(queryCompiler);
        var queryCompilationContextFactory =
        (IQueryCompilationContextFactory)QueryCompilationContextFactoryField.GetValue(database);
        var queryCompilationContext = queryCompilationContextFactory.Create(false);
        var modelVisitor =
        (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
        modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
        var sql = modelVisitor.Queries.First().ToString();

        return sql;
    }
}

And as far as actual usage goes, you would simply call the ToSql() method to return your SQL query string:

// Build a query using Entity Framework
var query = _context.Widgets.Where(w => w.IsReal && w.Id == 42); 
// Get the generated SQL
var sql = query.ToSql(); 



European Windows 2016 Hosting - HostForLIFE.eu :: Top Security Features in Windows Server 2016

clock December 9, 2016 07:19 by author Scott

Most of the press surrounding the release of Windows Server 2016 has focused primarily on new features such as containers and Nano Server. While these new capabilities are undeniably useful, the bigger story with the Windows Server 2016 release is Microsoft's focus on security.

Admittedly, it is easy to dismiss claims of enhanced operating system security as being little more than marketing hype. After all, every new Windows release boasts improved security. In Windows Server 2016, however, Microsoft has implemented a number of new security mechanisms that are designed to work together to provide better overall security.

This article discusses virtual secure mode, which serves as the foundation for several new Windows Server 2016 security features, and three of the more innovative new features.

Microsoft's Virtual Secure Mode

Microsoft's latest security-related buzz phrase is Virtual Secure Mode. The idea behind virtual secure mode is that the Windows operating system can be made more secure by offloading some of its security functions to the hardware, rather than performing those functions solely at the software level.

There are two important things to understand about virtual secure mode. First, virtual secure mode doesn't really provide any security by itself. Instead, virtual secure mode is more of an infrastructure- level component of the operating system, and is the basis for other security features which will be discussed later on.

The other thing that must be understood about virtual secure mode is that the word virtual is there for a reason. As you probably know, modern CPUs include on-chip virtualization extensions. Historically, these virtualization extensions have been the basis of server virtualization. The hypervisor sits on top of the CPU and acts as an intermediary between the virtual machines and the hardware.

One of the big advantages to using this approach to server virtualization is that the hypervisor is able to ensure that virtual machines are truly isolated from one another. Virtual secure mode uses a similar technique to create a virtualized space on top of the hypervisor. Sensitive operations can be securely performed within this space, without being exposed to the host operating system.

Feature No. 1: Credential Guard

As previously noted, virtual secure mode is not a security feature itself, but rather a platform that can be used by other security features. Credential Guard is one of the security features that relies on virtual secure mode. As its name implies, Credential Guard is designed to prevent user credentials from being compromised.

The authentication process used by the Windows operating system is a function of the Local Security Authority (LSA). Not only does the LSA provide interactive authentication services, but it also generates security tokens, manages the local security policy and manages the system's audit policy. Credential Guard works by moving the LSA into Isolated User Mode, the virtualized space created by virtual secure mode.

Although the operating system must be able to communicate with the LSA in order to perform authentication services, Microsoft has designed the operating system to protect the integrity of the LSA. First, the memory used by the LSA is isolated, just as a virtual machine's memory is isolated. Microsoft also limits the LSA to running only the bare minimum binaries, and strict signing of those binaries is enforced. Finally, Microsoft prevents other code, such as drivers, from running in Isolated User Mode.

Feature No. 2: Device Guard

Device Guard is another operating system feature that leverages virtual secure mode. Device Guard isn't really a feature per se, but rather a collection of three security features that fall collectively under the Device Guard label. These three features include Configurable Code Integrity, VSM Protected Code Integrity, and Platform and UEFI Secure Boot (which has been around since Windows 8). Collectively, these three features work together to prevent malware infections.

The Device Guard component that is designed to work with virtual secure mode is VSM Protected Code Integrity. This component ensures the integrity of code running at the kernel level. Although moving kernel mode code integrity into virtual secure mode goes a long way toward protecting the operating system, the Configurable Code Integrity feature is equally noteworthy. This feature is designed to ensure that only trusted code is allowed to run. Administrators can use the PowerShell New-CIPolicy cmdlet to create integrity policies that essentially act as whitelists for applications.

In case you are wondering, these policies are based on application signatures. Since not all applications are signed, Microsoft provides a tool called SignTool.exe that can create a catalog (a signature) for unsigned applications.

Feature No. 3: Host Guardian and Shielded Virtual Machines

Although server virtualization has been proven to be relatively secure, it has always had one major Achilles heel: virtual machine portability. Today, there is little to prevent a virtualization administrator, or even a storage administrator for that matter, from copying a virtual machine's virtual hard disk to removable media.

The rogue administrator would then be able to take the media home, mount the virtual hard disks on his own computer and gain full access to the virtual hard disk's contents. If necessary, the administrator could even go so far as to set up their own host server and actually boot the stolen virtual machine. Microsoft's Host Guardian Service is designed to prevent this from happening by allowing the creation of shielded virtual machines.

The Host Guardian Service is a Windows Server 2016 attestation and key protection service that allows a Hyper-V host to be configured to act as a guarded host. A guarded host must be positively identified on the network and attested at the Active Directory and/or TPM level. If TPM trusted attestation is being used, then Windows goes so far as to verify the host's health by comparing its configuration against a known good baseline configuration. It is worth noting, however, that Active Directory trusted attestation does not support host configuration verification.

The Host Guardian Service enables the use of shielded virtual machines. A shielded virtual machine is a virtual machine whose virtual hard disks are encrypted via virtual TPM. This encryption prevents a shielded virtual machine from running on any Hyper-V server other than a designated guarded host. If a virtual hard disk is removed from the organization, its contents cannot be accessed and the virtual machine cannot be run.

Shielded virtual machines are BitLocker encrypted. BitLocker makes use of a virtual TPM device, residing on the host server. The virtual TPM is encrypted using a transport key, and the transport key is in turn protected by the Host Guardian Service.



European SQL 2016 Hosting - HostForLIFE.eu :: Using SQL Server 2016 Query Store to Force Query Execution

clock December 5, 2016 10:44 by author Scott

Many a time we come across a scenario where suddenly in production without any release or changes some query which was working perfectly alright till yesterday is taking too long to execute or consuming lot of resources or timing out.

Most of the times such issue are related to execution plan change (commonly referred as Plan Regression). Till yesterday the query was running fine as it was running with good cached execution plan and today a bad plan is generated and cached. Because of this bad cached plan the query which was executing perfectly alright suddenly starts misbehaving.

To identify and fix such performance problems due to the execution plan change the Query Store feature introduced in Sql Server 2016 will be very handy.

Query Store basically captures and stores the history of query execution plans and its performance data. And provides the facility to force the old execution plan if the new execution plan generated was not performing well.

What’s Interesting with SQL Query Store

The SQL Server Query Store consists of two main store parts; the Plan Store, where the execution plans information stored, and the Runtime State Store, where the execution statistics will be stored. The query execution statistics and plans are stored first in memory, and flushed to the disk after a specific interval of time. In this way, the Query Store information will not be lost when the SQL Server service is restarted, as the data is hardened to the disk. The default flush to disk configurable database option DATA_FLUSH_INTERNAL_SECONDS value is 15 minutes, this means that, the executed queries information will be written to the disk from the Query Store every 15 minutes. The smaller flush interval, the more frequent write-to-disk operations, the worst SQL performance. The Query Store data will be flushed automatically to the disk to release the memory for other processes if there is a memory pressure. For proper memory and space usage for the Query Store, the execution information is aggregated in the memory first over fixed interval of time, and then flushed to the disk in aggregated form. The max_plans_per_query option can be used to control the number of plans that will be stored for review.

SQL Server Query Store provides you with an easy way to troubleshoot query performance, where you can identify the top CPU, Memory and IO consuming queries, with full execution history for these queries in addition to find which and when the query performance regressed and fix it directly by forcing the best plan, preventing the SQL Server Query Optimizer from using the less efficient new plan. It also can be used to draw a general image about the workload of your environment, with the query text, execution plans, the number of executions and SQL Server resources utilization, which can help you in evaluating how much resources required by your SQL Server database.

When the query is executed for its first time, the query execution plan with full query properties and statistics will be stored in the Query Store internal tables. If you run the query again and the SQL Server Query Optimizer decides to recompile the query and create a new execution plan, this plan will be added to the Query Store, in addition to the old one, with the plan and query execution statistics. Query Store allows you to track the queries execution plans and performance changes, and enforce the plan that the query behaves better when you execute the query with it. When a query is compiled, the SQL Server Query Optimizer will take the latest plan in the plan cache if it is still useful, unless a PLAN HINT is used, or another plan is forced by the Query Store. In this case, the Query Optimizer will recompile the query and use that new plan. No change is required from the application side to perform that, as this is performed transparently from the users.

To start using the new Query Store feature, you need to enable it at the database level, then it will start capturing the queries execution statistics and plans automatically. The below ALTER DATABASE statement is used to enable the Query Store feature on the SQLShackDemo database , which is disabled by default, and specify the operation mode for that feature:

USE [master]
GO
ALTER DATABASE [SQLShackDemo] SET QUERY_STORE = ON
GO
ALTER DATABASE [SQLShackDemo] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
GO

You can also use the SQL Server Management Studio to enable the Query Store, from the Database Properties window below, where you can find the new Query Store tab introduced when you use the SQL Server 2016 version:

As you can see from the previous image, SQL Server Query Store can be run in two operation modes; Read-Onlymode, where you can only use the persisted statistics to analyze the queries , without capturing any new data. This occurrs when the Query Store reaches its maximum allocation space. In Read-Write mode, the Query Store will capture the execution statistics for the current workload and store it, to be used for analyzing the queries execution performance. The DATA_FLUSH_INTERVAL_SECONDS option determines how frequent the Query Store data stored in the memory will be asynchronously transferred to the disk. By default, SQL Server will write the in-memory Query Store statistics to the disk every 15 minutes, or 900 seconds. You can also flush the Query Store data manually from the memory to the disk by executing the below query:

USE SQLShackDemo
GO
EXEC sys.sp_query_store_flush_db

The maximum size of data that can be stored in the Query Store can be controlled by the MAX_STORAGE_SIZE_MB option. As mentioned previously, exceeding that limit will change the Query Store operation mode to Read-Only mode automatically. By default, the Query Store can keep up to 100 MB of query statistical data before transferring to the Read-Only operation mode. You can make sure that the Query Store will activate the cleanup process if the execution data exceeds the MAX_STORAGE_SIZE_MB value by setting the SIZE_BASED_CLEANUP_MODE to AUTO, which is the default value, or turn it OFF to stop the automatic cleanup process.

The QUERY_CAPTURE_MODE option specifies if the Query Store will capture ALL queries, or ignore the queries that are not running frequently or running on a very small time with AUTO capture mode or stop capturing any new query using the NONE capture mode. The number of days to keep the Query Store data is specified by the STALE_QUERY_THRESHOLD_DAYS parameter.

As mentioned previously in this article, query execution statistics data is aggregated in memory and later flushed to Query Store internal tables to optimize the space usage. The aggregation process is performed over a fixed time interval that is controlled by the INTERVAL_LENGTH_MINUTES parameter, which is 60 minutes by default.

You can also find other useful information in the Query Store page under the Database Properties window, such as the Query Store disk usage in the current database and disk space used by the Query Store internal tables. This page allows you to delete or purge the Query Store data by clicking on the Purge Query Data at the right bottom part of the window:

Or use the below ALTER statement to purge the content of the Query Store:

ALTER DATABASE [SQLShackDemo] SET QUERY_STORE CLEAR

SQL Server introduced 6 new system stored procedures and 7 new system views to check the Query Store feature information and deal with it. These system objects can be listed by querying the sys.all_objects system table as below:

USE master
GO
SELECT Name as ObjectName , type_desc as ObjectType
FROM sys.all_objects
WHERE name LIKE '%query_store%'
or name= 'query_context_settings'

The result will be like:

The sys.query_store_plan, sys.query_store_query, and sys.query_store_query_text system tables can be used to get the current query plans in the Query Store. To show you up-to-date statistics, the data stored in the disk and the current data in the memory will be merged together to provide toy with full image as follows:

SELECT QST.query_text_id,
QST.query_sql_text,
QSP.plan_id,
QSRS.first_execution_time,
QSQ.last_execution_time,
QSQ.count_compiles,
QSQ.last_compile_duration,
QSQ.last_compile_memory_kb,
QSRS.avg_rowcount,
QSRS.avg_logical_io_reads,
QSRS.avg_logical_io_writes
FROM sys.query_store_plan AS QSP  
JOIN sys.query_store_query AS QSQ  
    ON QSP.query_id = QSQ.query_id  
JOIN sys.query_store_query_text AS QST  
    ON QSQ.query_text_id = QST.query_text_id
JOIN sys.query_store_runtime_stats  QSRS
   ON QSP.plan_id =QSRS.plan_id

The result will be like:

The SQL Server Query Store has no direct impact on SQL Server performance, but you need to take into consideration the disk space required to store the aggregated query execution data, where the data will be stored in the Primary database filegroup and consume the configurable MAX_STORAGE_SIZE_MB disk amount as described previously. Also the Query Store capture the execution data asynchronously as it writes the data to the memory first and flush it later to the disk. Query Store avoids consuming the CPU and Memory resources in the way it uses to capture the new plans and the executions statistics.

Once the Query Store is enabled on your database, you can query the sys.database_query_store_options system object to check the Query Store settings you configured as follows:

USE SQLShackDemo
GO
SELECT  actual_state_desc,
                                flush_interval_seconds,  
                                interval_length_minutes,
                                max_storage_size_mb,
                                stale_query_threshold_days,
                                max_plans_per_query,
                                query_capture_mode_desc,
                                size_based_cleanup_mode_desc    
FROM sys.database_query_store_options;  

The result in our case will be like:

Refresh your database node from the Object Explorer to ensure that the new Query Store node that is added in SQL Server 2016, is enabled as below:

Four nodes will be displayed, from where you can check and track the changes in the queries execution statistics and performance. Choose the Regressed Queries node that will show you the queries execution plans with all related statistics:

From the previous window, you can use the Metric drop-down list to choose the criteria that will be used to compare the execution plans performance, with the Statistic drop-down list to select the aggregation function used in the comparison. You can also view the graphical plan for the queries, the query text and the available plans for the query to force the best plan if requested.

Assume that we faced a memory consumption performance issue recently with one of our queries, and we arrange to force the Query Optimizer to use the old plan. Using the Query Store feature, select the Memory Consumption (KB) metric, the query and the plan as in the below window:

<image>

It is clear that the memory consumption for plan 56 is double the memory consumption of plan 31, so we will force the plan 31 for that query. The size of the bubbles shown in the right chart of the window depends on the total number of executions for each plan. The plans data can be displayed in grid form or as bubbles depends in the view you choose. Choose plan 31 in the previous window and click on Force Plan button.

A message will be displayed to confirm that you need to force the selected plan for that query as follows:

This action will force the SQL Server Query Optimizer to recompile that query in the next run using the forced plan. The selected plan will be shown with tick inside it, indicating that this plan is forced now for that query as below:

You can also use the sp_query_store_force_plan SP to enforce a specific plan for the query as follows:

EXEC sp_query_store_force_plan @query_id = 31, @plan_id = 31;

Conclusion:

The SQL Server Query Store is a nice feature introduced in SQL Server 2016, which certainly merits your time and attention, although it will take few minutes from you to configure and learn. This feature is a simple way that can be used to track performance changes and troubleshoot any degradation in the queries performance by comparing the execution plans for the same query and force the best one by overriding the one saved in the plan cache and used by the Query Optimizer. The SQL Server Query Store has no major impact on SQL Server performance due to the way that is used in capturing and saving the queries execution statistics and plans to be viewed later.



European SQL 2016 Hosting - HostForLIFE.eu :: Json in SQL Server 2016

clock November 17, 2016 10:17 by author Scott

In this article, we will focus on Json support in SQL Server 2016. We will take a look how Json will impact data warehouse solutions

Since the advent of EXtensible Markup Language (XML) many modern web applications have focused on providing data that is both human-readable and machine-readable. From a relational database perspective, SQL Server kept up with these modern web applications by providing support for XML data in a form of an XML data type and several functions that could be used to parse, query and manipulate XML formatted data.

As a result of being supported in SQL Server, data warehouse solutions based off SQL Server were then able to source XML-based OLTP data into a data mart. To illustrate this point, let’s take a look at the XML representation of our fictitious Fruit Sales data shown in figure below.

To process this data in data warehouse, we would first have to convert it into relational format of rows and columns using T-SQL XML built-in functions such as the nodes() function. 

The results of the above script are shown below in a recognisable format for data warehouse.

Soon after XML became a dominant language for data interchange for many modern web applications, JavaScript Object Notation (JSON) was introduced as a lightweight data-interchange format that is more convenient for web applications to process than XML. Likewise most relational database vendors released newer versions of their database systems that included the support for JSON formatted data. Unfortunately, Microsoft SQL Server was not one of those vendors and up until SQL Server 2014, JSON data was not supported. Obviously this lack of support for JSON, created challenges for data warehouse environments that are based off SQL Server.

Although there were workarounds (i.e. using Json.Net) to addressing the lack of JSON support in SQL Server, there was always sense that these workarounds were inadequate, time-wasting, and were forcing data warehouse development teams to pick up a new skill (i.e. learn .Net). Fortunately, the release of SQL Server 2016 has ensured that development teams can throw away their JSON workarounds as JSON is supported in SQL Server 2016.

Parsing JSON Data into Data Warehouse

Similarly to XML support in SQL Server, SQL Server supports of JSON can be classified into two ways:

1. Converting Relational dataset into JSON format
2. Converting JSON dataset into relational format

However, for the purposes of this discussion we are focusing primarily on the second part – which is converting a JSON formatted data (retrieved from OLTP sources) into a relational format of rows and columns. To illustrate our discussion points we once again make use of the fictitious fruit sales dataset. This time around the fictitious dataset has been converted into a JSON format as shown in below.

ISJSON function

As part of supporting JSON formatted data in other relational databases such as MySQL and PostgreSQL 9.2, there is a separate JSON data type that has been introduced by these vendors. Amongst other things, JSON data type conducts validation checks to ensure that values being stored are indeed of valid JSON format.

Unfortunately, SQL Server 2016 (and ORACLE 12c) do not have a special data type for storing JSON data instead a variable character (varchar/nvarchar) data type is used. Therefore, a recommended practice to dealing with JSON data in SQL Server 2016 is to firstly ensure that you are indeed dealing with a valid JSON data. The simplest way to do so is to use the ISJSON function. This is a built-in T-SQL function that returns 1 for a valid JSON dataset and 0 for invalids.

OPENJSON function

Now that we have confirmed that we are working with a valid JSON dataset, the next step is to convert the data into a table format. Again, we have a built-in T-SQL function to do this in a form of OPENJSON. OPENJSON works similar to OPENXML in that it takes in an object and convert its data into rows and columns.

Below shows a complete T-SQL script for converting JSON object into rows and columns.

Once we execute the above script, we get relational output shown below

Now that we have our relational dataset, we can process this data into data warehouse.

JSON_VALUE function

Prior to concluding our discussion of JSON in SQL Server 2016, it is worth mentioning that in addition to OPENJSON, you have other functions such as JSON_VALUE that could be used to query JSON data. However this function returns a scalar value which means that unlike the multiple rows and columns returned using OPENJSON, JSON_VALUE returns a single value as shown in Figure below.

If you the JSON object that you are querying doesn’t have multiple elements, than you don’t have to specify the row index (i.e. [0]) 

Conclusion

The long wait is finally over and with the release of SQL Server 2016, JSON is now supported. Similarly to XML, T-SQL support the conversion of JSON object to relational format as well the conversion of relational tables to a JSON object. This support is implemented via built-in T-SQL functions such as OPENJSON and JSON_VALUE. In spite of all the excitement with the support of JSON is SQL Server 2016, we still don’t have a JSON data type. The ISJSON function can then be used to validate JSON text.



European Windows Hosting - HostForLIFE.eu :: New Features in Windows Server 2016

clock November 3, 2016 08:59 by author Scott

As we’ve come to expect from new versions of Windows Server, Windows Server 2016 arrives packed with a huge array of new features. Many of the new capabilities, such as containers and Nano Server, stem from Microsoft’s focus on the cloud. Others, such as Shielded VMs, illustrate a strong emphasis on security. Still others, like the many added networking and storage capabilities, continue an emphasis on software-defined infrastructure begun in Windows Server 2012.

The GA release of Windows Server 2016 rolls up all of the features introduced in the five Technical Previews we’ve seen along the way, plus a few surprises. Now that Windows Server 2016 is fully baked, we’ll treat you to the new features we like the most.

Here are several features that you can get from Windows Server 2016:

Nano Server

Nano Server boasts a 92 percent smaller installation footprint than the Windows Server graphical user interface (GUI) installation option. Beyond just that, these compelling reasons may make you start running Nano for at least some of your Windows Server workloads:

  • Bare-metal OS means far fewer updates and reboots are necessary.
  • Because you have to administratively inject any server roles from outside Nano, the server has a much-reduced attack surface when compared to GUI Windows Server.
  • Nano is so small that it can be ported easily across servers, data centers and physical sites.
  • Nano hosts the most common Windows Server workloads, including Hyper-V host.
  • Nano is intended to be managed completely remotely. However, Nano does include a minimal local management UI called "Nano Server Recovery Console," shown in the previous screenshot, that allows you to perform initial configuration tasks.

Containers

Microsoft is working closely with the Docker development team to bring Docker-based containers to Windows Server. Until now, containers have existed almost entirely in the Linux/UNIX open-source world. They allow you to isolate applications and services in an agile, easy-to-administer way. Windows Server 2016 offers two different types of "containerized" Windows Server instances:

  • Windows Server Container. This container type is intended for low-trust workloads where you don't mind that container instances running on the same server may share some common resources
  • Hyper-V Container. This isn't a Hyper-V host or VM. Instead, its a "super isolated" containerized Windows Server instance that is completely isolated from other containers and potentially from the host server. Hyper-V containers are appropriate for high-trust workloads.

Linux Secure Boot

Secure Boot is part of the Unified Extensible Firmware Interface (UEFI) specification that protects a server's startup environment against the injection of rootkits or other assorted boot-time malware.

The problem with Windows Server-based Secure Boot is that your server would blow up (figuratively speaking) if you tried to create a Linux-based Generation 2 Hyper-V VM because the Linux kernel drivers weren't part of the trusted device store. Technically, the VM's UEFI firmware presents a "Failed Secure Boot Verification" error and stops startup.

Nowadays, the Windows Server and Azure engineering teams seemingly love Linux. Therefore, we can now deploy Linux VMs under Windows Server 2016 Hyper-V with no trouble without having to disable the otherwise stellar Secure Boot feature.

ReFS

The Resilient File System (ReFS) has been a long time coming in Windows Server. In Windows Server 2016, we finally get a stable version. ReFS is intended as a high-performance, high-resiliency file system intended for use with Storage Spaces Direct (discussed next in this article) and Hyper-V workloads.

Storage Spaces Direct

Storage Spaces is a cool Windows Server feature that makes it more affordable for administrators to create redundant and flexible disk storage. Storage Spaces Direct in Windows Server 2016 extends Storage Spaces to allow failover cluster nodes to use their local storage inside this cluster, avoiding the previous necessity of a shared storage fabric.

ADFS v4

Active Directory Federation Services (ADFS) is a Windows Server role that supports claims (token)-based identity. Claims-based identity is crucial thanks to the need for single-sign on (SSO) between on-premises Active Directory and various cloud-based services.

ADFS v4 in Windows Server 2016 finally brings support for OpenID Connect-based authentication, multi-factor authentication (MFA), and what Microsoft calls "hybrid conditional access." This latter technology allows ADFS to respond when user or device attributes fall out of compliance with security policies on either end of the trust relationship.

Nested Virtualization

Nested virtualization refers to the capability of a virtual machine to itself host virtual machines. This has historically been a "no go" in Windows Server Hyper-V, but we finally have that ability in Windows Server 2016.

Nested virtualization makes sense when a business wants to deploy additional Hyper-V hosts and needs to minimize hardware costs.

Hyper-V Server has allowed us to add virtual hardware or adjust the allocated RAM to a virtual machine. However, those changes historically required that we first power down the VM. In Windows Server 2016, we can now "hot add" virtual hardware while VMs are online and running. I was able to add an additional virtual network interface card (NIC) to my running Hyper-V virtual machine.

PowerShell Direct

In Windows Server 2012 R2, Hyper-V administrators ordinarily performed Windows PowerShell-based remote administration of VMs the same way they would with physical hosts. In Windows Server 2016, PowerShell remoting commands now have -VM* parameters that allows us to send PowerShell directly into the Hyper-V host's VMs!

Invoke-Command -VMName 'server2' -ScriptBlock {Stop-Service -Name Spooler} -Credential 'tomsitprotim' -Verbose

We used the new -VMName parameter of the Invoke-Command cmdlet to run the Stop-Service cmdlet on the Hyper-V VM named server2.

Shielded VMs

The new Host Guardian Service server role, which hosts the shielded VM feature, is far too complex to discuss in this limited space. For now, suffice it to say that Windows Server 2016 shielded VMs allow for much deeper, fine-grained control over Hyper-V VM access.

For example, your Hyper-V host may have VMs from more than one tenant, and you need to ensure that different Hyper-V admin groups can access only their designated VMs. By using BitLocker Drive Encryption to encrypt the VM's virtual hard disks, shielded VMs can solve that problem.

 



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in