Performance Tuning with Query Store in SQL Server 2017
If you want to monitor the performance of your queries within SQL Server 2017, you can with a feature called Query Store. Query Store monitors changes to your queries to see if performance deteriorates or improves when the query plan changes.
When you create a new database, is disabled by default; you need to enable this feature.
To enable Query Store, do the following steps
1. In Object Explorer, right-click a database, and then click Properties.
2. In the Database Properties dialogue box, select the Query Store page.
3. In the Operation Mode (Requested) box, select On.
You would use Query Store when you want to
• Find queries that have become slow over time and fix them by forcing the queries to use a different query plan.
• Find out how many times a query was run in a set period.
• Find out how many times the query plan was changed.
• Analysis why your query plan changes.
The best way to learn about
is to play around with it so just enable it and refresh the database, so it looks like this
Then just double click on each item in the query store to see what they do. It is better to enable Query Store on a database that has many transactions so you will be able to see results appear fast. I would suggest using a development server to test this feature as turning on this feature can have a minor effect on performance on your server.
If you have any comments on this article, just leave a comment below.
After you have downloaded the Microsoft SQL Server Management Studio, you will find it is easy to install. Just double click the setup file and you should see something like this.
Just click the ‘Install’ button.
After you have installed the software, you can find the SQL Server Management Studio in Windows menu. When you open the software, you should see a splash screen.
Once the software is open, you should see a screen like this.
I will write more blog posts on how to use SQL Server Managment Studio in the future.
If you find this useful, just leave a comment below.
SQL Server 2017 Installation Guide
If you have downloaded SQL Server 2017 and you want to install it but you are not too sure how to? Well, this is a guide will help you through the steps needed to install SQL Server 2017.
I will presume you have already downloaded SQL Server 2017 so the first thing to do after you have download SQL Server 2017 is to run the setup file.
You should see a screen like the above.
After the splash screen has finished loading, you will see several options that you can pick.
I will show you how to do a custom install of SQL Server so you can see the options that are available to you. Press the ‘Custom’ button then on the next screen press ‘Install.’
You should see a screen like this if anything needed downloading.
Once this is complete, it is now time to install SQL Server 2017.
If you click on ‘Planning’ option, you can navigate through all the options to see if your PC/laptop has the correct specifications to run SQL Server 2017. After you are satisfied that your PC/laptop will be able to run SQL Server 2017, click the Installation tab and Click “New SQL Server stand-alone installation or add features to an existing installation.”
On the Product Key screen press Next (Developer should be highlighted)
On the Licence Screen press Next, after you have read the terms and conditions (I bet you will not read the terms and conditions)
Check the box if you want the Microsoft Updates, it is a good idea to accept them.
Click next until you come to Install Rules and if you cannot click Next on this screen, fix any errors that it shows you.
On the feature selection screen, you have many options that you can select for features you want to install within SQL Server. The only one that you need to install is the database engine so select ‘Database Engine Services’ and press next.
On the Instance Configuration screen, you have two choices; you can pick Default Instance or Named Instance.
An Instance is the name of your SQL Server.
You will use Default Instance if this is going to be the first installation of a SQL Server.
You would use Named Instances if you wanted to install multiple SQL Servers on to the same PC/Laptop
InstanceID is usually the name of your PC/Laptop if you have selected the Default Instance. If you choose Named Instance, then you can give the new installation a name. So if your PC is called ‘SELECTSQLSERVER’ and you wanted your SQL Server to have a special name like ‘STOCK’ then you would type in STOCK in the InstanceID.
I will show in a later blog on how you would connect to a named server and default server.
Click Next on the Instance Configuration.
Server Configuration – Click Next
Database Engine Configuration– Click Add Current User. You have two ways to connect to SQL Server. Windows Authentication or Mixed Mode. You will use Mixed Mode if you want to access SQL Server without a Windows Account. Click Next
On the Ready To Install screen, click install.
That is is, let me know in the comments below if you have trouble installing SQL Server 2017.
So you have come across a problem in a SQL Server query where you want to find out if a value is null and if it is, use another value. Which function should you use, ISNULL or COALESCE?
Well if you just want to do something simple and to evaluate the value to see if it’s NULL and use another value, then both commands would work if the other number is NOT NULL.
If you wanted to do something more advanced and compare a series of numbers and use the first number that is not null, then COALESCE is the function that you have to use.
Run the code into a SQL Server Environment to see how COALESCE works
ISNULL vs Coalese Code Example
SET NOCOUNT ON;
IF EXISTS (SELECT name FROM sys.tables
WHERE name = ‘wages’)
DROP TABLE wages;
CREATE TABLE wages
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
INSERT wages VALUES(10.00, NULL, NULL, NULL);
INSERT wages VALUES(20.00, NULL, NULL, NULL);
INSERT wages VALUES(30.00, NULL, NULL, NULL);
INSERT wages VALUES(40.00, NULL, NULL, NULL);
INSERT wages VALUES(NULL, 10000.00, NULL, NULL);
INSERT wages VALUES(NULL, 20000.00, NULL, NULL);
INSERT wages VALUES(NULL, 30000.00, NULL, NULL);
INSERT wages VALUES(NULL, 40000.00, NULL, NULL);
INSERT wages VALUES(NULL, NULL, 15000, 3);
INSERT wages VALUES(NULL, NULL, 25000, 2);
INSERT wages VALUES(NULL, NULL, 20000, 6);
INSERT wages VALUES(NULL, NULL, 14000, 4);
SET NOCOUNT OFF;
SELECT CAST(COALESCE(hourly_wage * 10 * 10, salary, commission * num_sales) AS money) AS ‘Total Salary’
There are some things to think about when using COALESCE, if you want to evaluate a series of numbers and all the values are NULL, at least one has to be a typed NULL
SELECT COALESCE(NULL, NULL);
will result in
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an
expression that is not the NULL constant.
but this will work
SELECT COALESCE(CAST(NULL AS INT), NULL);
Using ISNULL does not error the code, so this will work
SELECT ISNULL(NULL, NULL);
There are some minor differences between ISNULL and COALESCE and can act differently in different scenarios that you have to think about the outcome.
Did this help you, leave a comment below!