Troubleshooting SQL Server worst performing queries

In this blog, I will shortly discuss how to troubleshoot SQL Server worst performance queries.

In my case, I always use Dynamic Management views DMVs  sys.dm_exec_query_stats for troubleshooting worst queries performance. SQL server stores runtime statistics about cache execution plan in the DMVs and Dynamic Management function (DMFs).

Therefor, you can easily find out worst performing query in your SQL server using the following query: sys.dm_exec_query_stats.

To remember this, Every cache execution plan , SQL server stores details information about this execution plan perform at runtime, and tell you how much CPUs, and I/Os would be consumed by that query.

when you execute select * from sys.dm_exec_query_stats, for each eSQL server gives you the following information metric and for each metric 4 different columns with aggregation information :

  • Worker Time (columns …_worker_time)
  • Physical Reads (columns …_physical_reads)
  • Logical Writes (columns …_logical_writes)
  • Logical Reads (columns …_logical_reads)
  • SQLCLR Time (columns …_clr_time)
  • Elapsed Time (columns …_elapsed_time)
  • Row Count (columns …_rows)

Aggregation information: total value, last value, min value, max value

1

Now, you need to evaluate your SQL query performance base on performance bottleneck. Performance bottleneck can be CPU bound or I/O bound.

If your Performance bottleneck is CPU bound, you can evaluate your worst query performance using the following query in term of CPU consumption:

— Worst performing CPU bound queries– Worst performing CPU bound queries

GoSELECT TOP 5 st.text, qp.query_plan, qs.*FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY total_worker_time DESC

2

–Worst performing I/O bound queries–Worst performing I/O bound queries

SELECT TOP 5 st.text, qp.query_plan, qs.*FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY total_logical_reads DESC

3

If you click on query_plan, you can see execution plan for the specific worst query performer. 4.jpg

Now you can troubleshoot worst query and find out further more about how to get resolve the issues.

Note: SQL Server stores runtime statistics  about execution plan in the DMV sys.dm_exec_query_plan.

Thank you 🙂

 

Advertisements

Windows: Configure System Image Backup

In this tutorial, I am gonna discuss how you can configure your system image and files backup and restore them using windows built-in-utilities without third-party utility Tools.

Task lists:

  • Configure a System Image Backup in Windows 7, 8, or 10
  • Configure a system repair disc that you can use to start your computer and restore a backup image
  • Restore Personal File, How to configure File History Settings
  • Schedule of file saving and how long you want to keep saved versions

Features are: Windows built-in File History feature
Backup and Restore feature including Create a System Image image and a System Repair disc

File History feature gives you the facility to save a copy of your files to another location

A system image backup is a full snapshot of an entire hard drive. The benefit of a system image is that if a hard drive crashes, you can replace it, restore the image, and get back to your system where it was when the image was last captured. You don’t require to reinstall Windows or your applications.

Steps for the task:  Configure a System Image Backup in Windows 7, 8, or 10

Windows 7, 8, 10 have the same technique to create a system image backup. As I am a windows 10 user, I am going to show it in windows 10.

Step1: Go to your control panel and choose “Backup and Restore (Windows 7) or you can select File history

1.png

Step 2:  Now you need to click on “Create a system image”1

Step 3: Now you can see a new window and get a message why you need to save the backup and the options in where you can save your image backup.

  • If you want to save the backup on your pen drive or hard drive, select the first  option “On a hard disk”
  • 2nd option: “on One or more CD/DVDs” if you want to save the backup on CD/DVD disk
  • 3rd option: “On a network location” if you want to save the backup file in the different network location

In my case, I am using option 2 “On one or more DVDs”

2.jpg

Step 4: Click Next button after selecting your desired backup location.

Confirm your backup settings by clicking on “Start backup3.jpg

4

6.jpg

SQL database backup Strategy Plan

In this blog, I will discuss SQL database backup strategy plan

A Backup of a database has two types: Full and Differential

Full database backups represent the whole database at the time the backup finished. Differential database backups contain only changes made to the database since its most recent full database backup.

Manual Backup: In this section, I discuss how to take a manual back up of any specific database.

Step 1: Go to the Microsoft SQL Server Management Studio -> Click the database [you want to take backup] -> select Back Up 1.jpg

Step 2: Specify the source database and backup type: Full or Differential2

Step 3: You can see backup is progressing in the backup directory location 3.jpg

Step 4: You will receive a successful message after completing the backup job. 4.jpg

Automatic back scheduling using Microsoft SQL Management Studio -> Management -> Maintenance Plans

We can make back up the strategy using two features of Management plans

a) Maintenance plan wizard b. New Maintenance plan

1.jpg

a) Maintenance plan wizard:

Step 1: Click Management -> Maintenance plans-> Maintenance plan wizards -> Next1.jpg

Step 2:  Now you can set your plan properties. Give the name of the backup job, Run as It would be appeared automatically, now you need to schedule you back up the task: you can see a single schedule plan for entire tasks or separate schedule for your every task based on your requirement. Untitled1

Here, I am going to select Separate schedule for each task

2

3.jpg

Step 3: Configure backup plan for full database backup. Here you can configure backup plan for all databases or select the specific database you want to keep in your full database backup plan.

4.jpg

Step 4: Setup the scheduled time for full database backup. The backup will run automatically. Keep in mind that it would be better if you set backup schedule on an off-pick hour.

5.jpg

Step 5: You need to schedule the same configuration for differential database backups, transaction log, the mainanenac task

8.jpg

9.jpg

10

Define the location of log files1112

Execution the maintenance plan 1314

Check the backup is processing or not in the backup location such as disk, tab, URL15

You can check your log file16

Thank you 🙂

SQL database access permission to a User ( Error: The database is not accessible)

In this blog, I will discuss how to give SQL database access permission to a specific user.

3.jpg

Step 1: Login your SQL server as an Administrator user

2.jpg

Step 2: Click on the Username whom you want to give a specific database access permission

Click right button on username -> select Properties

4.jpg

Step 3: Here you can see a Login Properties dialog box

I will discuss here Server roles, User Mapping, and status 

Server roles: Here you can give a bunch of admin power to a user based on nature of user activities.

Example:

sysadmin: Members of the sysadmin fixed server role can perform any activity on the server.

serveradmin: Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.

securityadmin: Members of the securityadmin fixed server role manage logins and their properties. The can grant, revoke, deny server level permission and database level permission if they have permission to access database as well.

You can see more from : https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles

5.jpg

User Mapping: User Mapping is that grant a user to access a specific database based on nature of user activities

6.jpg

Here, I am giving permission user momo9 to access testdb database instance and allowing some specific roles such as db_ddladmin, db_datawriter, db_datareader etc.

You can set your user permission based on what activities that user wants to do on that specific database. More on: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

6.jpg

Step 4: Status

here you can see permission to database permission “Grant” or “Deny” and Login “Enable ” or Disable”7.jpg

Step 5: As I have given momo9 user admin power. That specific user can now create a table, insert the value and execute select statement without any error.8.jpg

9.jpg

 

permissions-of-database-roles

Source: https://docs.microsoft.com/

 

Thank you 🙂

How to fix Login Failed for a User (Microsoft SQL Server, Error: 18456)

In this blog, I will discuss how to fix Microsoft SQL Server, Error: 18456

error.png

First of all, You need to log in as an administrator user in your server machine for giving the privilege to other users/local users to connect SQL server.

Step 1: Login as an Administrator user in your SQL Server management studio Tools

Step 2:  Go to the Security panel under SQL Server

Now I am showing you how to grant privilege for a local user to access SQL server

Right-click -> New->Login Untitled.jpg

Step 3: Now you can see the Login New window. Select Windows Authentication -> click Search [if you don’t know login user name]2.jpg

Step 4: You will get “select User or Group” window box, click -> Advanced -> click Find Now 3.jpg

Step 5: Now select the user you want to grant access to your SQL server and Click Ok

5.jpg

Now you can see the user under Security -> Logins

6.jpg

Step 6: Go to the user machine and connect SQL server using Microsoft SQL server manager studio1.jpg

Step 7: Now you can login without Microsoft SQL Server, Error: 18456

2.jpg

Thank you 🙂

 

AWS: Red Hat Enterprise Linux Configuration

Amazon EC2 instance is a web service that allows users to configure the resizable compute capacity and secure environment in the cloud with minimal friction.  Amazon EC2 reduces the time required to obtain and boot new server instances to minutes, allowing users to quickly scale capacity, both up and down, as computing requirements change.

In this section, I will discuss how to configure Red Hat Enterprise Linux 7.4 (HVM), SSD Volume Type.

AWS Services Required: Amazon EC2, Amazon EBS

Step1: You have to go the AWS management console and select compute -> EC2

0.jpg

Step2: Click on “Launch Instance” and choose the Amazon machine image (AMI) you want to configure.

1.jpg

I am going to install Red Hat Enterprise Linux 7.4 (HVM).

The advantage of  Amazon EC2 running Red Hat Enterprise Linux provides a dependable platform to deploy a broad range of applications. A user can leverage the cost effectiveness, scalability and flexibility of Amazon EC2, the proven reliability of Red Hat Enterprise Linux, and AWS premium support with back-line support from Red Hat by running RHEL on EC2.

2.jpg

Step3: Choose the instance type: In my case, I chose t2.micro because of the free trial. The advantage of T2 instances is Burstable Performance which is facilitated a baseline level of CPU performance with the ability to burst above the baseline.

3

Step 4: Now you need to configure the instance details in where you need to provide the number of instancces (you can deploy more than 1 instance at a time with auto-scaling group facility),  Your own VPC, and Public IP for securing connection and Define IAM role for privacy and security etc.

4.jpg

Step5: Add the storage size and select volume type

5.jpg

Step 6: Configure Security group which will configure the traffic of your instance. You can control inbound and outbound traffic by assign protocol type and port group

6.jpg

Step 7: Check Launch Status and

8.jpg

Now you can see an EC2 Linux instance has been deployed and running.

9

Summary of Configure: 

  • Choose an Amazon Machine Image (AMI)
  • Choose an instance type
  • Security group: Configure your virtual firewall.
  • Launch an instance: Review your instance configuration and choose “Launch”.
  • Create a key pair: Select “Create a new key pair” and assign a name. The key pair file (.pem) will download automatically – save this in a safe place as we will later use this file to log in to the instance.
  • Finally, choose “Launch Instances” to complete the setup.

Connect to your instance:

Step 1:  Go to the “Actions panel” and select “Connect”

1

Step 2: You can connect the EC2 Linux instance using two options: SSH client via PuTTy and a Java SSH client directly from your browser.2

NPAPI doesn’t support Google Chrome, in this case, you need to use Firefox3

Thank you 🙂

TADHACK GLOBAL 2017 Experience

TADHACK GLOBAL 2017 September 23-24 Auckland

Attending TAD Hack Global 2017 was one of most thrilling experience I enjoyed last weekend. Before that, I attended a lot of competitions with my own team or with proper preparation, this time It was totally different experience. When I went to Oracle New Zealand on Saturday morning, I hadn’t any idea what I was going to do or whom I was going to meet. I met first time with my five unknown team-mates, TAD Hack Coordinators. It was a global competition. Therefore, we can see each other activities on Live Stream telecast. TAD Hack is the widely global competition for all kind of IT professionals included students, business executive, or anyone.

When people meet with a common interest, they don’t need to know each other for starting a conversation. When we started sharing our ideas, we even didn’t know each other name. We didn’t know where we came from or what is our background. We were all focusing on our ideas and how to implement it.

After a few hours, we got to collaborate. We planned, designed and started implementing. We solved and troubleshoot problems, supported each other’s, wrote code, deployed the project, and celebrated each successful moment. We spent 2 days, almost 18 hours together. We never felt like uncomfortable, or we met the first time.  When we finished our competition, wrapped up our bag, and left Oracle New Zealand office, feelings were like we know each other.

Our story is: Six different unknown IT nerds, met somewhere, took a common challenge, and completed it.

I learned one thing: Passion and dedication can take you anywhere you wish to.

We won Local TADHack Auckland Prize and 1st Prize from Temasys Global Winners.

TADHACK Auckland MediConnect Team Member

 

PowerPoint Slide

 Thank you 🙂