Showing posts with label SQL Server. Show all posts

Monday, 22 August 2022

[SQL Server] Where is my SQL Server Configuration Manager and SSMS?

[SQL Server] Where is my SQL Server Configuration Manager and Where is my SSMS(SQL Server Management Studio)?

SQL Server Configuration Manager

Sometimes, for some reason, you can't find(search) SQL Server Configuration Manager.

Here are the file names for SQL Server Configuration Manager

SQL Server 2022: SQLServerManager16.msc
SQL Server 2019: SQLServerManager15.msc
SQL Server 2017: SQLServerManager14.msc
SQL Server 2016: SQLServerManager13.msc
SQL Server 2014 (12.x): SQLServerManager12.msc
SQL Server 2012 (11.x): SQLServerManager11.msc

Path

C:\Windows\SysWOW64

C:\Windows\System32

However they are all from System Path, therefore you can simply execute them by type it.

i.e.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver16

=============================================================

SQL Server Management Studio

Sometimes, for some reason, you can't find(search) SQL Server Management Studio.

Here are the locations for SSMS (depending on SQL Server/SSMS versions)

From SSMS 18: The location has been changed.

C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe

SQL Server 2017

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe

SQL Server 2016

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe

SQL Server 2014

C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe

SQL Server 2005: Really? Do you still use SQL Server 2005?

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe

Thursday, 27 January 2022

Best practices for deploying Microsoft SQL Server on Amazon EC2

Guide Link: https://docs.aws.amazon.com/prescriptive-guidance/latest/sql-server-ec2-best-practices/welcome.html

PDF Link: https://docs.aws.amazon.com/prescriptive-guidance/latest/sql-server-ec2-best-practices/sql-server-ec2-best-practices.pdf

Monday, 28 December 2020

[SQL Server] A few things you need to consider when you use views in SQL Server - Part 2.

A few things you need to consider when you use views in SQL Server - Part 2. Aggregation.

Please check Part 1. More joins for the preparations.

One of the advantages of using a view is that you can hide code from the end-user. It means users do not need to care about the structure of views or table relations.

But what about aggregation queries in the view.

Example query(GROUP BY)

There is a view using GROUP BY as below.

CREATE OR ALTER VIEW dbo.vw_PostCount_per_Users
AS
SELECT
	COUNT(DISTINCT p.Id) AS PostCount
	, u.Id As UserID, u.AboutMe, u.Age, u.CreationDate AS UserCreationDate, u.DisplayName
FROM
	dbo.Posts p LEFT JOIN dbo.Users u ON p.OwnerUserId = u.Id
GROUP BY
	u.Id , u.AboutMe, u.Age, u.CreationDate, u.DisplayName
;

Only TOP 1000?

Lots of people use "SELECT TOP 1000 Rows" using Object Explorer in order to see what's inside.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [PostCount]
      ,[UserID]
      ,[AboutMe]
      ,[Age]
      ,[UserCreationDate]
      ,[DisplayName]
  FROM [StackOverflow].[dbo].[vw_PostCount_per_Users]

Looks ok, right?

But maybe it will take forever.

Then, what about TOP 10?

SELECT TOP (10) [PostCount]
      ,[UserID]
      ,[AboutMe]
      ,[Age]
      ,[UserCreationDate]
      ,[DisplayName]
  FROM [StackOverflow].[dbo].[vw_PostCount_per_Users]

It will not return the result very quickly as well.

What's happening?

Let's compare the execution plan.

SELECT TOP (1000) [PostCount]
      ,[UserID]
      ,[AboutMe]
      ,[Age]
      ,[UserCreationDate]
      ,[DisplayName]
  FROM [StackOverflow].[dbo].[vw_PostCount_per_Users]


SELECT TOP (10) [PostCount]
      ,[UserID]
      ,[AboutMe]
      ,[Age]
      ,[UserCreationDate]
      ,[DisplayName]
  FROM [StackOverflow].[dbo].[vw_PostCount_per_Users]

You might be noticed that it has several problems.

1. It's 50 vs 50. No big differences.

2. They all uses "Clustered Index Scan", meaning no index was being used.

-- dbo.Posts has 46947633 Rows.

The main reason it became a heavy query is that SQL server cannot return the result to you until GROUP BY operation is finished.

How to solve this issue?

Modifying the query to use the proper index can solve this issue.

For e.g.

SELECT TOP 1000 * FROM dbo.vw_PostCount_per_Users 
WHERE DisplayName LIKE 'Kevin%';

Let's compare this one vs the previous one (without where clause - no index usage).

But, ultimately, you need to change this view to a stored procedure in order to enforce to use indexes.

Sunday, 6 December 2020

[SQL Server] A few things you need to consider when you use views in SQL Server - Part 1.

A few things you need to consider when you use views in SQL Server - Part 1. More joins

View in SQL Server

View in SQL Server is great when it comes to the security, readability and simplicity.
But in terms of performance, you need to consider several factors.

Preparation

I use StackOverflow database

You can download from here(Thanks to Brent): https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/

Creating indexes

Create indexes in order to check the performance clearly and save time for the result.

USE StackOverflow
GO

CREATE INDEX IX_Posts_OwnerUserId ON dbo.Posts(OwnerUserId);

CREATE INDEX IX_Comments_PostID ON dbo.Comments(PostID);

CREATE INDEX IX_Votes_PostID ON dbo.Votes(PostID);

CREATE INDEX IX_Comments_UserId ON dbo.Comments(UserId);

Creating a view

Create a view for retrieving multiple columns across related tables at once.

USE StackOverflow
GO

CREATE OR ALTER VIEW dbo.vw_PostInformation
AS
SELECT
	p.Id AS PostId, p.Title AS PostTitle, p.AnswerCount AS PostAnswerCount, p.Body AS PostBody
	, p.ClosedDate AS PostClosedDate, p.CreationDate AS PostCreationDate
	, p.LastEditDate AS PostLastEditDate, p.ViewCount AS PostViewCount
	, p.OwnerUserId AS PostOwnerUserId, u.DisplayName AS UserDisplayName, u.CreationDate AS UserCreationDate
	, c.Id AS CommentID, c.CreationDate AS CommentCreationDate, c.Score AS CommentScore, c.Text AS CommentText
	, v.BountyAmount AS VoteBountyAmount, v.VoteTypeId, v.CreationDate AS VoteCreationDate
FROM
	dbo.Posts p JOIN dbo.Users u ON p.OwnerUserId = u.Id
	JOIN dbo.Comments c ON p.Id = c.PostId
	JOIN dbo.Votes v ON p.Id = v.PostId
;

Example query of the view.

1 2	SELECT * FROM dbo.vw_PostInformation WHERE PostID = 33

When you need only a few columns of data?

You may select lots of information using the view.

But if you select only a few columns from a few tables, then it becomes a different story.

For e.g. if you only select data from "Posts" table and "Users" table.

Executions

Queries

-- 01. With view
SELECT v.PostId, v.PostBody, v.PostCreationDate, v.UserDisplayName, v.UserCreationDate
FROM dbo.vw_PostInformation AS v
WHERE PostID = 33
GROUP BY v.PostId, v.PostBody, v.PostCreationDate, v.UserDisplayName, v.UserCreationDate

-- 02. Without View
SELECT
	p.Id AS PostId, p.Body AS PostBody, p.CreationDate AS PostCreationDate
	, u.DisplayName AS UserDisplayName, u.CreationDate AS UserCreationDate
FROM
	dbo.Posts p JOIN dbo.Users u ON p.OwnerUserId = u.Id
WHERE
	P.Id = 33
;

Result

Looks like the same.

But, when you use a view, you need to use "GROUP BY" for this case in order to have unique values otherwise you will get lots of duplicate rows.

Execution plan comarison

Let's take a look at the execution plans.

View uses Index seek(Luckily not scans, at least) for "Comments" and "Votes" table which we don't need for this query. Of course, using view is more expensive.

I/O comparison

Now, let's take a look at I/O using "SET STATISTICS IO ON".

Queries

SET STATISTICS IO ON;
-- 01. With view
PRINT '01. With View'
SELECT v.PostId, v.PostBody, v.PostCreationDate, v.UserDisplayName, v.UserCreationDate
FROM dbo.vw_PostInformation AS v
WHERE PostID = 33
GROUP BY v.PostId, v.PostBody, v.PostCreationDate, v.UserDisplayName, v.UserCreationDate

-- 02. Without View
PRINT '02. Without View'
SELECT
	p.Id AS PostId, p.Body AS PostBody, p.CreationDate AS PostCreationDate
	, u.DisplayName AS UserDisplayName, u.CreationDate AS UserCreationDate
FROM
	dbo.Posts p JOIN dbo.Users u ON p.OwnerUserId = u.Id
WHERE
	P.Id = 33
;
SET STATISTICS IO OFF;

Result

01. With View

(1 row affected)
Table 'Votes'. Scan count 4, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Comments'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)
02. Without View

(1 row affected)
Table 'Users'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

As you can see, it reads from "Votes" and "Comments" tables when using a view even though you didn't use any data from those tables.

Therefore, using a view can read additional pages because of the structure.

Thursday, 26 November 2020

[SQL Server] Copy and paste column names of the table using Object Explorer in SSMS

Get column names(parameter names) using Object Explorer in SSMS

Template Explorer contains lots of SQL Example Scripts maybe you want to create.

Situation

I want to type column tables from the table easily.

Typing all columns manually is not really efficient. (Maybe that's why you type "*" ).

Copy and paste column names using Object Explorer

New query.

Maybe you want to put "*" in there. But please wait.

Open Object Explorer and expand to "Columns"

Now, drag and drop that "Columns" to SQL Query Window.

Voila, all column names pasted to Query Window.

Copy and paste Parameters of Stored Procedure using Object Explorer

You can also get parameters of stored procedure with the same method.

drag and drop "Parameters" from Object Explorer to Query Window.

Parameters will be there.

What about the other objects?

As you can imagine, most of the objects from Object Explorer can be copied to Query Window.

But there's bug to copying "Constraints". SSMS might die when you try to drag and drop "Constraints" to Query Window.

References

SQL Server Management Studio (SSMS) Query Editor

Friday, 23 October 2020

[SQL Server] Make the SQL Life easier with Template Explorer in SSMS

Create a script with Template Explorer in SSMS.

Template Explorer contains lots of SQL Example Scripts maybe you want to create.

Situation

Are you googling to find the example code for SQL Server scripts?

Here's Template Explorer what you can get quickly.

Open Template Browser

Select "View" in the menu and Select "Template Explorer".

Now you can see Template Browser on the Right pain.

Open a template

Backup script

If you want to create a backup script, then Select "Backup > Backup Database".

Then you will get the script as below.

-- ===========================
-- Backup Database Template
-- ===========================
BACKUP DATABASE <Database_Name, sysname, Database_Name> 
	TO  DISK = N'<Backup_Path,,C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\><Database_Name, sysname, Database_Name>.bak' 
WITH 
	NOFORMAT, 
	COMPRESSION,
	NOINIT,  
	NAME = N'<Database_Name, sysname, Database_Name>-Full Database Backup', 
	SKIP, 
	STATS = 10;
GO

Add key script

If you want to add key(constraint), then Select "Table > Add Key"

Then you will get the script as below.

-- =============================================
-- Add Key template
--
-- This template creates a table, then it  
-- adds a PRIMARY KEY constraint to the table
-- =============================================
USE <database, sysname, AdventureWorks>
GO

IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL
  DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
	<columns_in_primary_key, , column1>      int      NOT NULL, 
	column2      char(8)  NOT NULL
)
GO

-- Add a new PRIMARY KEY CONSTRAINT to the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
  ADD CONSTRAINT <constraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , column1>)
GO

Where is it?

It's possible to edit the existing template or to create a new one.

But, where is it?

Select "Edit" on Template Browser

Now, Select "Open Containing Folder".

Now, they are here.

In general, the location will be "C:\Users\[<User Name>]\AppData\Roaming\Microsoft\SQL Server Management Studio\[<SSMS Major Version>]\Templates\Sql"

References

Template Explorer

Monday, 28 September 2020

[SQL Server] Set the specific port number for SQL Server DAC

Set(change) the specific port number for SQL Server DAC

DAC is very useful when your SQL Server is stuck.

If you want to know how to configure SQL Server DAC, then click here.

Problem

If you have installed single instance on the server, then it will be fine as DAC will use 1434 port.

But what if you have installed multiple instances?

Then SQL Server will use random ports so you don't know which port you need to request to open for DAC to security team.

In this scenario, you can specify the port number for the specific instance.

Check Registry

The location of DAC port registry is here.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<number>.<InstanceName>\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

In my case, I have installed 2 SQL Server instances on the server.

* SQL Server 2017: SQL2017 is the instance name.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.SQL2017\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

TcpDynamicPorts is the value you need to take a look.

As you can see the port number is a random value and it's 50147 for the moment.

* SQL Server 2019: SQL2019 is the instance name.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.SQL2019\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

Modify the registry

Now, modify the value of TcpDynamicPorts.

* SQL Server 2017: 22017

* SQL Server 2019: 22019

Open the firewall for DAC

* SQL Server 2017: 22017

* SQL Server 2019: 22019

Restart services

Restart SQL Server Services that you've changed the port.

Check ports and verify connections

Check the port is opened.

netstat -an | findstr "22017"

Connect to the defined port from local -- 22019 (SQL 2019)

sqlcmd -S tcp:localhost,22019 -E

Connect to the defined port from remote -- 22017 (SQL 2017)

sqlcmd -S <server>,22017 -U <user> -P <password> -d master

References

Sunday, 16 August 2020

[SQL Server] SQL Server remote admin connections -- DAC (Dedicated Admin Connection)

SQL Server remote admin connections -- DAC (Dedicated Admin Connection)

If the users can't connect to the server, or there's problem with SQL Server, then you might need to connect to SQL Server explicitly for diagnostics purpose or something.

Mostly this situation can happen when there are too many connections or there are too high CPU usage queries are running on the server so that no one can connect to the server at that moment.

DAC(dedicated administrator connection) is designed for these kinds of situations.

Enable DAC

First of all you need to check the current configuration.

sp_configure 'remote admin connections'
GO

sp_configure 'remote admin connections', 1;  
GO  
RECONFIGURE;  
GO
sp_configure 'remote admin connections'
GO

Verify ports for DAC

Make sure the default DAC port (1434) is opened.

netstat -an | findstr ":1434"

Connect to the server via DAC

You can connect via SSMS, but it's better to connect via SQLCMD which is a command line.

Because when you connect to the server via SSMS, it opens at least 2~3 new sessions and which means it's not possible to connect with DAC by default.

sqlcmd -S {Servername} -U {username} -P {password} -d {databasename} -A

Note!

Make sure SQL Server Browser service is Running.

** Error -- when SQL Browser is not running.

Check the current DAC session

Check Query

SELECT dess.session_id AS DACSessionID, @@SPID AS CurrentSessionID, dess.original_login_name AS DACLogin
FROM sys.endpoints AS ep JOIN sys.dm_exec_sessions AS dess ON ep.endpoint_id = dess.endpoint_id
WHERE ep.name='Dedicated Admin Connection';
GO

References

Monday, 3 August 2020

[Windows, SQL Server] How to verify SQL Server port is opened from the client computer?

How to verify the SQL Server port is opened from the client computer?

Situation

You want to make sure that the SQL Server service port is opened and can connect from the client computer.

If you want to verify the listening port on SQL Server, take a look here.

Telnet Client

The telnet command is a very traditional old way to check the server port is opened.

Moreover, it's also possible to use the same command parameters as Linux.

But, you have to turn on Windows feature as below.

And may require your computer reboot.

telnet <desination host> <destination port>

You may wait until it returns the result when it fails or you have to close the window even if it succeeded.

Luckily, there are 2 more ways you can do that quite easily.

PsPing

PsPing is one of the utility applications from PsTools.

You can download PsTools from here.

It enables you to check the qualities of "ICMP, TCP and UDP" connections.

Basic command

psping <Destination host>:<Destination port>

It has lots of options/parameters you can use. For example, you want to use PsPing with

2 seconds interval,
10 times try,
Use IPv4,
The destination host is "testserver",
The destination port is "1433"

then, the command would be like this.

Note!

You need to perform this operation on the same directory whereas the executable files located.
You can add the directory to your Path(System Properties > Advanced > Environment Variables > Path > Add directory of the file location)
Alternatively, you can copy the executable files to the current system directory e.g. C:\Windows\System32.

Portping

PortPing is the simplest tool to check the port (from my perspective)

You can download Portping from here.

It enables you to check the qualities of "ICMP, TCP and UDP" connections.

Basic command

portping [-c <tries(count)>] <Destination host> <Destination port>

It has only one option -c: stop after count connections (default 5).

Thus, it's very simple to use.

For e.g.

Note!

The name can be different depending on the version such as "portping-v1.0-windows_amd64.exe", therefore it will be easier to rename this file to "portping.exe" in order to use convenience.
You need to perform this operation on the same directory whereas the executable files located.
You can add the directory to your Path(System Properties > Advanced > Environment Variables > Path > Add directory of the file location)
Alternatively, you can copy the executable files to the current system directory e.g. C:\Windows\System32.

What else?

PsPing and Portping can be also useful to check the connectivity to the server for the specific services in a situation of ICMP(ping) protocol blocked in the target network by the administrator.