European Windows 2012 Hosting BLOG

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

SQL Server 2019 Hosting - HostForLIFE.eu :: New Resumable Online Index Create SQL Server 2019

clock August 5, 2020 13:32 by author Peter

SQL Server 2019 brings a very exciting new feature that was long overdue. Resumable Online Index Creation is one of my favorite new things. This, when paired with Resumable Index Rebuilds introduced with SQL Server 2017, really gives database administrators much more control over the index processes.

Have you ever started to build a new index on very large table only to have users call and complain their process is hung, not completing, or sthe ystem is slow? That’s when you realize you’re the cause because you tried to sneak in a new index. I have many times; because creating a new index can impact the performance and can be a problematic process for users when you have no or little downtime windows available. When you kill the create process, it rolls back requiring you to start from the beginning the next time. With resumable Online Index Creation, now you have the ability to pause and restart the build at the point it was paused. You can see where this can be very handy.

To use this option for creating the index, you must include "RESUMABLE=ON".

CREATE INDEX MyResumableIndex on MyTable (MyColumn) WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=30)  

Let’s say you have only two 30-minute windows available to create this new index over the next two days. You could use the MAX_DURATION option with the new RESUMABLE=ON to specify the time interval for an index being built. Once the 30 minutes time is up, the index building automatically gets paused if it has not completed. When you’re ready the next day, you can RESUME right where it left off, allowing you to complete the process. Very cool.

Another added benefit is managing transaction log growth. As we all know, creating indexes, especially large ones, can cause hefty log growth events and can unfortunately lead to running out of disk space. This new functionality allows us to better manage that. We can now pause the process and truncate or backup the log mid process building the index in chunks.

In the case of when you create an index only to get complaints from users or manage your log growth, you can simply do the below to PAUSE and restart it when a time is better, or your transaction log maintenance has completed.

You can KILL the SPID creating the index or run the below.
ALTER INDEX MyResumableIndex ON MyTable PAUSE; 

To restart -

ALTER INDEX MyResumableIndex on MyTable RESUME; Or simply re-execute your CREATE INDEX statement  

According to MSDN, Resumable online index create supports the follow scenarios:

  • Resume an index creation operation after an index create failure, such as after a database failover or after running out of disk space.
  • Pause an ongoing index creation operation and resume it later allowing to temporarily free system resources as required and resume this operation later.
  • Create large indexes without using as much log space and a long-running transaction that blocks other maintenance activities and allows log truncation.

Note
SORT_IN_TEMPDB=ON is not supported when using RESUMABLE=ON

Once you pause it, how do you know how far the index got and how much is left to be created? With the Resumable REBUILD Index feature added in SQL Server 2017, we have also got a new sys.index_resumable_operations system view. This view shows us the percentage complete, current state, start time, and last pause time. I am very excited about this new Index Create feature. I think this is a big WIN for SQL Server 2019.

HostForLIFE.eu SQL Server 2016 Hosting
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.



SQL Server 2016 Hosting - HostForLIFE.eu :: SQL Injection

clock July 29, 2020 12:58 by author Peter

In this article, we are going to learn about SQL Injection. We are going to define SQL Injection, look at its common uses, look at a few examples, and its consequences. This article serves as an introduction to SQL injection.

SQL Injection, also known as SQLi, is one of the most common web hacking techniques that hackers use to input malicious SQL statements that may destroy a database or gain them unqualified access/data to the system.

SQL Injection is a common vector attack that may have unlimited gross effects to any organization which includes breach of authentication, integrity, and confidentiality in business concerns. This may result in loss of customer trust and the effects are not favorable in any business environment.

SQL attacks SQL databases and web applications or web sites, which require user input, are the biggest targets.
The history of SQL Injection dates back to the late 90s, and since then it remains a major security concern, even in huge organizations.

SQL Injection allows attackers to override application security parameters and allow them access to confidential information or in some cases delete or tamper with sensitive data to their advantage. According to this, the attack report of 2012 reveals that an average web application receives four attacks per month and in most cases, financial institutions suffer twice as much as other industries.

SQL Queries
Before we get in-depth with SQL Injection, it is best that we understand what SQL is. SQL is an abbreviation for Structured Query Language and is used to communicate with relational databases. SQL has its set of commands and syntax and this is used to manipulate data from a database. SQL commands are used to retrieve, insert, update, or delete data in a database. A simple SQL command is the 'SELECT' statement, which is used to retrieve data from one or more tables. E.g.

This statement will simply retrieve a list of all customers from a table. Likewise, an SQL query may also be used to update or delete data within a table. E.g.

This statement updates the customer with customer number ‘XY99’ to ‘xxxxx’. And likewise,
Will delete all the data in the customers table.

Using such knowledge of SQL we can now explore the effects and uses of SQL Injection in the real world.
Types of SQL Injection
In-band SQLi (Classic)
Inferential SQLi (Blind)
Out-of-band SQLi

SQL Injection in web pages (examples)
SQL injection may occur when a user is required to input some data using given interface controls such as username or a password and the malicious user knowingly inputs an SQL statement such as ‘or 1=1’ in the password field.

Such a query may return a result set as shown below.
This statement may end up giving the malicious user all the user names and passwords in that particular table hence giving him/her access to the application and a lot of damage may result using one malicious SQL statement.

In some cases, it may be because of incorrectly filtered escape characters that the application may end up running malicious queries that may even DROP/UPDATE / ALTER database contents. Such as shown in the example below:

int user_id=getAuthUserid(); 
String query =” Select * from tbl_users where user_id = '" + user_id + "'; 
” 

The above code intends to get a user’s ID and use it to authenticate the user but if the malicious user then knowingly crafts the user_id variable using any one of the SQL comments(/*,--,{) like as follows to block the rest of the query,
' OR '1'='1'; -- 
' OR '1'='1'; /* 
' OR '1'='1'; { 

Then the query may be executed as:
Select * from tbl_users where user_id='' OR '1'='1'; 

This query will give the malicious user access to all of the table columns and this may result in serious consequences.

Another example includes the use of harmful SQL statement, which drops a table from the database through user input,
It is common practice for many developers to use batch executions and in this case, the attacker may end up deleting all the important data in a given table. In most cases perpetrators of Injection are people with a little bit of expertise in programming and their intentions and knowledge of the application will determine how dangerous they can be once they hack into any system. Apart from Drop/Delete statements, hackers may use select or update SQL statements to obtain or manipulate data in an unfavorable way to cause harm to the application.

The expected result set may be as follows:
As shown in most of the examples above, hackers maybe people with actual intent to cause harm or gain malicious access and they target loose ends such as poor SQL commands on authentication and it is important that prevention measures are taken to avoid SQL Injection.

Results of SQL Injection
Authentication
If the SQL statements used not secure this may lead to hackers getting access to the entire system and damaging the system.

Confidentiality
Since a database always carries sensitive data the advent of a malicious intruder will damage the organization’s reputation.

Authorization
If authorization data is contained within the database they may allow the malicious user to change information and result in the company’s disrepute.

Integrity
Just as it may be possible to read sensitive information, it is also possible to make changes or even delete this information with a SQL Injection attack.

Can SQL Injection be prevented or managed? Yes. We discuss this in my next article SQL Injection Protection Methods and in upcoming articles we also look at an ASP.net example of SQL Injection, Other Injection flaws, Blind, and time-based SQL Injection, and many more.



SQL Server 2016 Hosting - HostForLIFE.eu :: Introduction To SQL And SQL Commands

clock July 17, 2020 13:48 by author Peter

In this article, we will learn about SQL and SQL Commands. SQL stands for Structured Query Language. SQL is used to create, remove, alter the database and database objects in a database management system and to store, retrieve, update the data in a database. SQL is a standard language for creating, accessing, manipulating database management system. SQL works for all modern relational database management systems, like SQL Server, Oracle, MySQL, etc.

Different types of SQL commands
SQL commands can be categorized into five categories based on their functionality.
 
DDL
DDL stands for data definition language. DDL commands are used for creating and altering the database and database object in the relational database management system, like CREATE DATABASE, CREATE TABLE, ALTER TABLE, etc. The most used DDL commands are CREATE, DROP, ALTER, and TRUNCATE.

    CREATE
    CREATE command is used to create a database and database object like a table, index, view, trigger, stored procedure, etc.
    
    Syntax
    CREATE TABLE Employee (Id INT, Name VARHCAR(50), Address VARCHAR (100));
    
    ALTER
    ALTER command is used to restructure the database object and the settings in the database.
    
    Syntax
    ALTER TABLE Employee ADD Salary INT;
    
    TRUNCATE
    The TRUNCATE command is used to remove all the data from the table. TRUNCATE command empties a table.
    
    Syntax
    TRUNCATE TABLE Employee;
    
    DROP
    DROP command is used to remove the database and database object.
    
    Syntax
    DROP TABLE Employee;

DML
DML stands for data manipulation language. DML commands are used for manipulating data in a relational database management system. DML commands are used for adding, removing, updating data in the database system, like INSERT INTO TableName, DELETE FROM TableName, UPDATE tableName set data, etc. The most used DML commands are INSERT INTO, DELETE FROM, UPDATE.

    INSERT INTO
    INSERT INTO command is used to add data to the database table.
    
    Syntax
    INSERT INTO Employee (Id, Name, Address, Salary) VALUES (1, ‘Arvind Singh’, ‘Pune’, 1000);
    
    UPDATE
    UPDATE command is used to update data in the database table. A condition can be added using the WHERE clause to update a specific row.
    
    Syntax
    UPDATE Employee SET Address = ‘Pune India’, Salary = 100 WHERE Id =1;
    
    DELETE
    DELETE command is used to remove data from the database table. A condition can be added using the WHERE clause to remove a specific row which meets the condition.
    
    Syntax
    DELETE FROM Employee WHERE Id =1;

DQL
DQL stands for the data query language. DQL command is used for fetching the data. DQL command is used for selecting data from the table, view, temp table, table variable, etc. There is only one command under DQL which is the SELECT command.
 
Syntax
SELECT * FROM Employee;
 
DCL
DCL stands for data control language. DCL commands are used for providing and taking back the access rights on the database and database objects. DCL command used for controlling user’s access on the data. Most used DCL commands are GRANT and REVOKE.
 
GRANT
GRANT is used to provide access right to the user.
 
Syntax
GRANT INSERT, DELETE ON Employee TO user;
 
REVOKE
REVOKE command is used to take back access right from the user, it cancels access right of the user from the database object.
 
Syntax
REVOKE ALL ON Employee FROM user;
 
TCL
TCL stands for transaction control language. TCL commands are used for handling transactions in the database. Transactions ensure data integrity in the multi-user environment. TCL commands can rollback and commit data modification in the database. The most used TCL commands are COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION.
 
COMMIT
COMMIT command is used to save or apply the modification in the database.
 
ROLLBACK
ROLLBACK command is used to undo the modification.
 
SAVEPOINT
SAVEPOINT command is used to temporarily save a transaction, the transaction can roll back to this point when it's needed.
 
Syntax
Just write COMMIT or ROLLBACK or SAVEPOINT;



European SQL 2016 Hosting - HostForLIFE.eu :: Multiple SQL Operation In Single Procedure

clock June 30, 2020 13:10 by author Peter

In this blog, I will show you how to write multiple SQL Statements in one procedure. Instead of writing separate procedures for Select, Insert, Delete and Update operations into the table, I am going to show you how to combine all operations into one single Stored Procedure.

This Table is called tblEmployee with the below structure:
We want to write a procedure for this table for Select, Insert, Delete and Update records.

Instead of writing separate a stored procedure for each operation we are going to write only one stored procedure to perform Select, Insert, Delete and Update records.

How To Achieve It?
Are you wondering how to accomplish this? It is simple -- just add a parameter to the stored procedure. Depending on this parameter we are going to execute the appropriate operations.

Here is the stored procedure:
Createprocedure [dbo].[USP_Employee] @empid asint=0, @empname asvarchar(50)=NULL, @age asint=0, @salary asint=0, @dob asvarchar(20)=NULL, @designation asvarchar(50)=NULL, @Reqtype asvarchar(10)=NULL AS  
BEGINIF @Reqtype='SELECT'   
BEGIN   
SELECT empid,   
       empname,   
       age,   
       salary,   
       dob,   
       designation   
FROM   tblemployee   
ENDIF @Reqtype='INSERT'   
BEGIN   
insertinto tblemployee VALUES(@empid,@empname,@age,@salary,@dob,@designation)   
ENDIF @Reqtype='DELETE'   
BEGIN   
deletefrom tblemployee WHERE [email protected]   
ENDIF @Reqtype='UPDATE'   
BEGIN   
UPDATE tblemployee   
SET    [email protected],   
       [email protected]age,   
       [email protected],   
       [email protected],   
       [email protected]   
WHERE  [email protected]   
ENDEND 

In the above example, based on the @Reqtype column the corresponding sql command will execute. For example, if the @Reqtype is select then select statement will execute. If the @Reqtype is inserted then Insert statement will execute.

In this blog, we have learned how to write multiple SQL operations into a single SQL procedure.

European SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server 2016 Hosting - HostForLIFE.eu :: Subqueries And Correlated Subqueries

clock June 24, 2020 12:53 by author Peter

Subqueries In SQL Server
Subqueries are enclosed in parentheses. Subquery is also called an inner query and the query which encloses that inner query is called an outer query. Many times subqueries can be replaced with joins.
    select * from Employee where DepartmentID not in (select distinct DepartmentID from Department) 

Another example:
    select Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID) from Department as d; 

The above query is an example of using subquery in the select list. The above result can be achieved using join also; see the below query
    select d.Department_Name,COUNT(e.empid) as empcount from Department d 
    join Employee e on e.DepartmentID=d.DepartmentID 
    group by d.Department_Name 
    order by empcount; 


According to MSDN, you can nest up to 32 levels.
 
Columns present in subqueries cannot be used in the outer select list of a query.
 
Correlated Subqueries
If our subquery depends on the outer query for its value then it is called a Correlated Subquery. It means subquery depends on outer subquery. Correlated subqueries are executed for every single row executed by outer subqueries.
 
A correlated subquery can be executed independently,
select distinct Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID group by DepartmentID) as empcount from Department as d order by empcount; 

What to choose for performance --  Subquery or Join?
According to MSDN, there is no big difference between queries that use sub-queries and joins.
 
But in some cases, we need to check the performance, and Join produces better performance because the nested query must be processed for each result of the outer query. In such cases, JOIN will perform better.
 
In general, JOIN works faster as compared to subqueries but in reality, it will depend on the execution plan generated by the SQL Server. If the SQL server generates the same execution plan then you will get the same result.



SQL Server 2016 Hosting - HostForLIFE.eu :: Restoring A SQL Server Database In Docker

clock June 23, 2020 13:27 by author Peter

Last month I blogged about using Docker to run SQL Server as a quick and easy way to get SQL Server up and running.  While it continues to be immensely useful, there aren’t any user databases  running on it.  The only databases present are the system databases. Yes, I could manually create a database, but it would be a lot easier to have a sample database available.

How do we do restore a sample database, in a container, that is running on a Mac laptop?  Let’s check it out!
 
Disclaimer
Not a container expert so there might be various ways of doing it.  This is just how I’ve figured out how to make the magic happen.
 
Also, if you have not read the first blog on how to get SQL Server running in Docker on your Mac, take a few minutes to read through it.
 
Here are the steps that we will take to make this work,
    Download one of the sample databases from I have a “mssql” directory in my local profile to make things easier
    Make sure the container is started.  You can issue a “docker ps” command terminal to see which containers are running
    Create a directory within the container
    Copy the sample database backup file into the directory you just created
    Restore the database onto the SQL instance that is running within the container

Sounds simple, right? Let’s see!
 
Create a directory
To create a directory within the container (remember that the container is running Linux), we can issue a command to the container that will create the directory.  Using Terminal (you can go to Spotlight to find the Terminal program or it is under Utilities in the Applications folder), execute the following command,
    Docker exec -it sql2019 mkdir /var/opt/mssql/backups  

Let us break that command down,
    Docker – this indicates that we are going to be doing something with Docker
    Exec – this tells the specified container to exec the command we pass into it
    -it – this basically allows for an interactive session with the container
    Sql2019 – this is the name of the container. You can specify the container name when you start the container or Docker will name it for you
    Mkdir – this is short for “make directory”
    /var/opt/mssql/backups – this is the directory path that is to be created.

    Restoring A SQL Server Database In Docker


Copy the Backup File
Now that the directory has been created, we need to get the backup file of the sample database into the container.  In my case, I am using AdventureWorks2017.bak
    Docker cp ./mssql/AdventureWorks2017.bak sql2019:/var/opt/mssql/backups 

Here is how that command breaks down,
    Docker – this indicates that we are going to be doing something with Docker
    cp – this is short for “copy”
    ./mssql/AdventureWorks2017.bak – this is the path of the source file that is being copied into the container. The “.” Indicates start with whatever working directory I am in, which is my profile directory as indicated by the “jmorehouse$”
    Sql2019 – this is the name of the container.
    :/var/opt/mssql/backups – this is the destination directory that is within the container.

Once the command is complete, we can check to make sure that the file was copied successfully.
    Docker exec -it sql2019 ls /var/opt/mssql/backups  

The “ls” refers to “list”.  This is equivalent to executing a “dir” command in DOS.
 
Restoring A SQL Server Database In Docker
 
Restore the Database
The backup file now resides within the container and we just need to tell SQL Server to restore it.  In this section, I will be using Azure Data Studio and native T-SQL commands.
 
Let us first check that SQL Server can see the file.
    RESTORE FILELISTONLY FROM DISK=N’/var/opt/mssql/backups/AdventureWorks2017.bak’ 
    GO 

Restoring A SQL Server Database In Docker
Excellent!  SQL Server can see the backup file which means that we can restore it.  Notice on the left-hand side, there are no user databases, just system databases. Also notice that the physical names of the database shown above are from the Windows Operating System.  Since SQL Server is running on Linux within the container, we will have to move the physical files to a different location.
    RESTORE DATABASE AdventureWorks2017 FROM DISK=N’/var/opt/mssql/backups/AdventureWorks2017.bak’ WITH 
    MOVE ‘AdventureWorks2017’ to ‘/var/opt/mssql/data/AdventureWorks2017.mdf’, 
    MOVE ‘AdventureWorks2017_log’ to ‘/var/opt/mssql/data/AdventureWorks2017_log.ldf’ 
    GO 

Restoring A SQL Server Database In Docker

Above we can see that the database was restored and then subsequently upgraded to the SQL Server 2019 database version.  If you refresh the Databases branch on the left-hand side, the AdventureWorks2017 database is now present!
 
Docker continues to be my current choice of “go to” when I need a quick and easy SQL Server solution to play around.  While I absolutely recommend Azure and its offerings, utilizing Docker on my local laptop is just faster, and frankly, fun to play around it.   Now that I can easily restore databases, it just makes it that much better of a solution for non-production uses.



SQL Server 2016 Hosting - HostForLIFE.eu :: What Is Batch Mode On Rowstore In SQL Server

clock June 17, 2020 13:24 by author Peter

Under compatibility level 150, in both SQL Server 2019 and Azure SQL Database, you now can use batch mode for CPU-bound analytic type workloads without requiring columnstore indexes. There is no action needed to turn on batch mode aside from being on the proper compatibility mode. You also have the ability to enable it as a database scoped configuration option (as shown below), and you can hint individual queries to either use or not use batch mode (also shown below). If you recall in my earlier blogs on columnstore, it is batch mode in conjunction with page compression that drastically increases query performance. This feature, Batch Mode on Rowstore, allows all operators enabled for batch mode to operate as such in SQL Server.
 

What does this mean? It means that query operations can process data faster, more efficiently and mimic what makes columnstore so fast. Instead of reading row by row (row store mode) it can read in chunks i.e. batches of 900 rows at a time. The performance impact of this can be tremendous which effectively uses CPU more efficiently.
 
Just like columnstore this only benefits analytic type workloads or data warehouses, as mentioned above. This is meant for aggregations and joins that process thousands of rows. It will not benefit you when processing singleton lookups. If where clause that does not look up a range of values and is just satisfying predicates, then batch mode does not provide a benefit.
 
How does the engine know when to use batch mode? According to docs.microsoft.com the query processor uses heuristics and will make decision based on three checks. An initial check on tables sizes, operators used and cardinality estimates. Then the optimize checks to see if there are cheaper plans it can use. If no alternative better plans are available, the optimizer will choose batch mode. There are some limitations that will prevent the use of batch mode such as, in-memory OLTP tables or for any index other than B-Trees or on-disk heaps. It will also not work for LOB columns including sparse and XML columns.
 
You can easily decipher when batch mode is used to run query inside the operator’s properties. Let’s see a demo.
 
To demo I want to first show you a plan NOT using Batch Mode on Row Store, so let’s turn the feature off because as I mentioned earlier it is already enabled for compatibility mode 150 by default. Run the below database scope configuration script to turn it off.
    USE AdventureworksDW2016CTP3 
    GOALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF; 


Now let’s run this query and make sure we capture the execution plan.
    SELECT FS.[ProductKey], 
           AVG([SalesAmount]), 
           [UnitPrice] 
    FROM [dbo].[FactResellerSalesXL] FS 
        JOIN dbo.DimProduct DP 
            ON DP.ProductKey = FS.ProductKey 
    GROUP BY FS.[ProductKey], 
                [UnitPrice] 


Note the Table Scan. By hovering over it you can see the operator’s properties and see the Actual Execution Mode says ROW and it processed 11,669,653 rows.

Now let’s run it again in Batch. Instead of changing compatibility lets just turn on the feature with an OPTION HINT.

SELECT FS.[ProductKey], 
       AVG([SalesAmount]), 
       [UnitPrice] 
FROM [dbo].[FactResellerSalesXL] FS 
    JOIN dbo.DimProduct DP 
        ON DP.ProductKey = FS.ProductKey 
GROUP BY FS.[ProductKey], 
            [UnitPrice] 
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE')); 

You can clearly see the optimizer chose to use BATCH mode based on our HINT. In addition, you can see it ran significantly faster at only 405 ms versus 1.119s using row mode. In general, we’ve seen queries that benefit from batch mode running in almost half of what row mode performance is and columnstore in batch mode performance.

Let’s go ahead and change back to the default Batch Mode again for our database just to prove it would have used batch mode without the use of our hint. Run the below and look at the plan.

    ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON; 
    GO 
     
    SELECT FS.[ProductKey], 
           AVG([SalesAmount]), 
           [UnitPrice] 
    FROM [dbo].[FactResellerSalesXL] FS 
        JOIN dbo.DimProduct DP 
            ON DP.ProductKey = FS.ProductKey 
    GROUP BY FS.[ProductKey], 
                [UnitPrice] 


BINGO! There you have it!

If you are already using compatibility mode 150 you are already taking advantage of this feature and may not even realized. For those that have not made the leap to 2019 I highly recommend it, if only for this little gem which is one of the many reasons why you should upgrade.



SQL Server 2016 Hosting - HostForLIFE.eu :: Max Degree Of Parallelism Vs Cost Threshold For Parallelism

clock May 8, 2020 10:09 by author Peter

The title sounds like something that you would see for a boxing match. However, in reality it is a misleading title. The Max Degree of Parallelism and the Cost Threshold for Parallelism SQL Server settings actually work more together than they do against each other.

These two settings actually define the how many and the when in regards to parallel execution plans. The Max Degree of Parallelism (MDop) simply defines the number of processors/cores that SQL Server will use when the optimizer determines parallelism is needed. The Cost Threshold for Parallelism is cost threshold of when the SQL Server will use parallelism. The cost is the overall cost the optimizer determines for each query and SQL Server will use parallelism if the cost is above the threshold value.
 

The recommended settings for MDop is the number of cores not to exceed 8. However, when setting this, it is important to continue to monitor the system to see if the change has caused an improvement. The default value is 0.
 
The recommended setting for the Cost Threshold for Parallelism is 25 to 50 and has a default value of 5. Yes, the default setting is too low. By changing this, SQL Server will reduce the number of smaller queries that may use parallelism. Just like the MDop setting, it is important to monitor the server after making a change to see if an improvement is make. There have been many times where simply changing this setting from the default, I have seen the CPU utilization drop from close to 100% to less than 10%. This in no way means you will see the same improvement, just what I have seen.
 
Neither one of these settings require a reboot of the service when changing. It is also important to keep in mind that if the MDop is set to 1, SQL Server will ignore the Cost Threshold for Parallelism setting.
 
This snippet of code can be used to query server configuration settings
    SELECT CONVERT(VARCHAR(60), name) AS 'Name' 
    , value 
    , description 
    FROM sys.configurations



SQL Server 2016 Hosting - HostForLIFE.eu :: Could Not Find Server 'Server Name' In sys.servers In SQL Server

clock April 29, 2020 07:39 by author Peter

I was working with a client and they had set up one sql server for an ETL process. When we tried to get the data from  the database we got the error:
"Could not find server 'server name' in sys.servers in SQL Server"

How to resolve this
First you need to check if the server exists in sys servers,
select name from sys.servers 

You will get the servers list here, if the server does not exist in the list, then add it using the command,
EXEC sp_addlinkedserver @server = 'New_Server_Name' 

Once the server is added to the linked server, then you can log in like this,
EXEC sp_addlinkedsrvlogin 'New_Server_Name','false',NULL,'USERNAME','PASSWORD' 

Now you can do whatever you want to do, you can use your local server now,
exec [New_Server_Name].[Database_Name].dbo.Procedure_NAME

Finally, you can drop this server from the linked server list using this command,
sp_dropserver 'New_Server_Name', 'droplogins' 



SQL Server 2019 Hosting - HostForLIFE.eu :: Using JSON Support in SQL Server 2019

clock April 22, 2020 10:03 by author Peter

There's so many new capabilities to play around and explore in SQL Server 2019. One of the new capabilities that has caught our attention is the introduction of JSON support. In this post, I will get started in creating a JSON document using SQL Server 2019.

SQL Server 2016 will support JSON (JavaScript Object Notation). JSON is an open, text-based exchange format based on JavaScript’s object literal notation. JSON is a popular data-interchange format used in modern web and mobile applications, as well for storing unstructured data. It is supported in several DB/NoSQL engines and this improvement will enable developers to put some JSON processing logic in the SQL Server that will enable them to parse, query, analyze, and update JSON data.

Step 1

In this post, you will query a standard table and then convert it into JSON format. For this example, you are querying the standard AdventureWorks database:

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]

Sample output for rows inside SQL Server Management Studio looks like below:

Step 2

Now, you will add the FOR JSON AUTO support to get the same output in JSON format.

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]
FOR JSON AUTO

The output looks like below:

[
{
"PersonType":"EM",
"FirstName":"X",
"MiddleName":"Y",
"LastName":"Z",
"EA":[{"EmailAddress":"[email protected]"}]
},
{"PersonType":"EM",
"FirstName":"A",
"MiddleName":"B",
"LastName":"C",
"EA":[{"EmailAddress":"[email protected]"}]
}
]

Step 3

You can also generate JSON using the PATH option like:

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]
FOR JSON PATH

The output for this query would look little different like:

[
{"PersonType":"EM", "FirstName":"X”, “MiddleName":"Y”, “LastName":"Z”, “EmailAddress":"[email protected]"},{"PersonType":"EM”, “FirstName":"A”, “MiddleName":"B”, “LastName":"C”, “EmailAddress":"[email protected]"}
]

Now that you are able to generate few simple output using JSON keyword.

HostForLIFE.eu SQL Server 2019 Hosting
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.



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