Product:
Microsoft SQL server 2016

Issue:
Can i see if i should improve anything on my SQL server?

Solution:

Mr Ozar have develop some sp that can give you more information, get the kit from

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit#writing-sp_blitz-output-to-a-table

install this one in your master database

Install-Core-Blitz-With-Query-Store.sql

Now you have a set of SP to use.

First run (if you have performance issues just now)

sp_BlitzFirst

Run this to get the most common wait issues you need to check on your server:

sp_BlitzFirst  @sincestartup = 1, @outputtype = 'Top10'

 

Setup a database LOGSQL and schedule this every month;

EXEC sp_Blitz @OutputDatabaseName = 'LOGSQL', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzResults';

Will give you a table BlitzResults to look at to see what status is for your SQL server.

Run this manually to see what can be done with index

EXEC sp_BlitzIndex @GetAllDatabases = 1, @BringThePain = 1;

copy result to excel for further analysis, remove unused index and duplicates.

 

Run to see the biggest queries:

EXEC sp_BlitzCache @Top = 20,  @BringThePain = 1;

Run this to see the size of index in one database:

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
  ,IX.name AS Index_Name
  ,IX.type_desc Index_Type
  ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
  ,IXUS.user_seeks AS NumOfSeeks
  ,IXUS.user_scans AS NumOfScans
  ,IXUS.user_lookups AS NumOfLookups
  ,IXUS.user_updates AS NumOfUpdates
  ,IXUS.last_user_seek AS LastSeek
  ,IXUS.last_user_scan AS LastScan
  ,IXUS.last_user_lookup AS LastLookup
  ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update

Check the size of the index and the usages – have it been used?

https://www.sqlshack.com/boost-sql-server-performance-with-wait-statistics/

https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

What is the most worrying wait type?

 

More Information:

https://www.brentozar.com/archive/2018/07/cxconsumer-is-harmless-not-so-fast-tiger/ 

 

 

Product:

Microsoft SQL server 2016

Issue:

Should i make a index?

Solution:

Yes, all table should have a index – at least a clustered index.

But a data-ware house table, should maybe not have a index when you load a lot of data.

Index can be created after you have loaded a lot of data. This to speed up the process.

Correct made index make the SELECT faster. Any index in the target table, makes the INSERT slower.

You should have not too few, or too many index on a table to get the best performance.

To create a index:

CREATE TABLE dbo.TestTable 
(TestCol1 int NOT NULL, 
TestCol2 nchar(10) NULL, 
TestCol3 nvarchar(50) NULL);

CREATE CLUSTERED INDEX IX_TestTable_TestCol1 
ON dbo.TestTable (TestCol1);

You can also create index by right-click on table name – indexes – New Index.

Check if index exist, before you drop it;

IF EXISTS (SELECT name FROM sys.indexes  
    WHERE name = N'IX_ProductVendor_VendorID')   
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;   

CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID   
    ON Purchasing.ProductVendor (BusinessEntityID); 

Use ONLINE=ON to make the table readable when you create the index:

CREATE NONCLUSTERED INDEX [IX_NonClusteredIndexDemo_StudentName] 
ON [dbo].[NonClusteredIndexDemo] ([StudentName],[STDAddress]) 
WITH ( ONLINE=ON, FILLFACTOR=90) 

To get more information of a query process use:

SET STATISTICS TIME ON
SET STATISTICS IO ON

SELECT * FROM [dbo].[DimAccount] 
WHERE [AccountType] ='Assets'

More Information:

https://www.sqlshack.com/tracing-and-tuning-queries-using-sql-server-indexes/

https://dataschool.com/sql-optimization/how-indexing-works/

  • Columns with text, image, ntext, varchar(max), nvarchar(max) and varbinary(max) cannot be used in the index key columns.
  • It is recommended to use an integer data type in the index key column. It has a low space requirement and works efficiently. Because of this, you’ll want to create the primary key column, usually on an integer data type.
  • You should consider creating a primary key for the column with unique values. If a table does not have any unique value columns, you might define an identity column for an integer data type. A primary key also creates a clustered index for the row distribution.
  • You can consider a column with the Unique and Not NULL values as a useful index key candidate.
  • You should build an index based on the predicates in the Where clause. For example, you can consider columns used in the Where clause, SQL joins, like, order by, group by predicates, and so on.
  • You should join tables in a way that reduces the number of rows for the rest of the query. This will help query optimizer prepare the execution plan with minimum system resources.
  • If you use multiple columns for an index key, it is also essential to consider their position in the index key.
  • You should also consider using included columns in your indexes.

https://blog.quest.com/11-sql-server-index-best-practices-for-improved-performance-tuning/

https://hungdoan.com/2017/04/13/nvarcharn-vs-nvarcharmax-performance-in-ms-sql-server/

The clustered index defines the order in which the table data will be sorted and stored. As mentioned before, a table without indexes will be stored in an unordered structure. When you define a clustered index on a column, it will sort data based on that column values and store it. Thus, it helps in faster retrieval of the data.

There can be only one clustered index on a table because the data rows can be stored in only one order.

When you create a Primary Key constraint on a table, a unique clustered index is automatically created on the table.

The non-clustered index does not sort the data rows physically. It creates a separate key-value structure from the table data where the key contains the column values (on which a non-clustered index is declared) and each value contains a pointer to the data row that contains the actual value. It is similar to a textbook having an index at the back of the book with page numbers pointing to the actual information.

https://www.tutorialsteacher.com/sqlserver/indexes

https://www.midnightdba.com/Jen/2014/12/create-a-clustered-index-and-a-primary-key-on-existing-tables/

https://www.sqlshack.com/designing-effective-sql-server-clustered-indexes/

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/

https://tprojects.schneider-electric.com/GeoSCADAHelp/ClearSCADA%202017%20R2/Content/SQLGuide/IntroductiontoSQLQueryStructure.htm

https://www.brentozar.com/archive/2021/06/learn-fundamentals-of-index-tuning-for-1/

https://www.sqlshack.com/designing-effective-sql-server-non-clustered-indexes/

Product:

Microsoft SQL server 2016

Issue:

How allow a domain group only to execute all SP in a database?

Solution:

Create a role with only the correct rights:

CREATE ROLE db_executor;

GRANT EXECUTE TO db_executor;

Add a existing database user to that role:

ALTER ROLE db_executor ADD MEMBER [company\username]

 

To add a domain user to the sysadmin role:

exec sp_addsrvrolemember 'whatDomain\Domain Admins', 'sysadmin';

To add a domain user and add it to a predefined role in the database:

CREATE LOGIN [company\User] 
CREATE USER [company\User]
ALTER ROLE [db_datareader] ADD MEMBER [company\User]

 

More Information:

https://www.sqlmatters.com/Articles/Adding%20a%20db_executor%20role.aspx

https://kimconnect.com/how-to-add-domain-admins-to-sql-server/

https://help.genesys.com/pureconnect/mergedprojects/wh_tr/mergedprojects/wh_tr_active_directory_sql/desktop/run_sql_server_script_to_add_ad_accounts.htm

Product:

Microsoft SQL server 2016

Issue:

How list the size of the tables in a database?

Solution:

Try this SQL query:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    SUM(a.total_pages) DESC

 

More Information:

https://ss64.com/sql/syntax-largest-table.html

Useful T-SQL queries and scripts to work in SQL Server

https://gist.github.com/hlsupe/d38b10ffdf34eb620f428100257a2695

Product:

Microsoft SQL server 2016

Microsoft Windows 2016 server

Problem:

How add rows for a date to a table?

Solution:

For this example we use this demo database:

https://www.brentozar.com/archive/2021/03/download-the-current-stack-overflow-database-for-free-2021-02/

Create a table with;

CREATE TABLE [dbo].[Storage](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CreationDate] [datetime] NOT NULL,
[DisplayName] [nvarchar](40) NOT NULL,
[DownVotes] [int] NOT NULL,
[LastAccessDate] [datetime] NOT NULL,
[Location] [nvarchar](100) NULL,
[Reputation] [int] NOT NULL,
[UpVotes] [int] NOT NULL,
[Views] [int] NOT NULL,
[AccountId] [int] NULL,
[UpdateDate] [datetime] NOT NULL
CONSTRAINT [PK_Storage_Id] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

To insert one row based on date and time;

INSERT INTO [dbo].[Storage]
(
[CreationDate],
[DisplayName],
[DownVotes],
[LastAccessDate],
[Location],
[Reputation],
[UpVotes],
[Views],
[AccountId],
[UpdateDate] 
)
SELECT
[CreationDate],
[DisplayName],
[DownVotes],
[LastAccessDate],
[Location],
[Reputation],
[UpVotes],
[Views],
[AccountId],
(GETDATE()) AS UpdateDate
FROM
[dbo].[Users]
WHERE
LastAccessDate = '2018-06-18 11:47:32.200'

To set a variable for a date;

DECLARE @@TargetDate DATETIME = '2018-06-18'
SELECT @@TargetDate

To insert all rows for a specific LastAccessDate;

INSERT INTO [dbo].[Storage]
(
[CreationDate],
[DisplayName],
[DownVotes],
[LastAccessDate],
[Location],
[Reputation],
[UpVotes],
[Views],
[AccountId],
[UpdateDate] 
)
SELECT
[CreationDate],
[DisplayName],
[DownVotes],
[LastAccessDate],
[Location],
[Reputation],
[UpVotes],
[Views],
[AccountId],
(GETDATE()) AS UpdateDate
FROM
[dbo].[Users]
WHERE
CONVERT (date,(LastAccessDate)) = @@TargetDate

 

More information

https://www.sqlshack.com/how-to-update-from-a-select-statement-in-sql-server/

https://popsql.com/learn-sql/sql-server/how-to-query-date-and-time-in-sql-server

SQL Server GETDATE () function and its use cases

Product:

Microsoft SQL server 2016

Microsoft Windows 2016

Problem:

How add data from one table to other table?

Suggested solution:

For the example we have downloaded the StackOverflow2013 database from here

https://www.brentozar.com/archive/2021/03/download-the-current-stack-overflow-database-for-free-2021-02/

Create a new table;

CREATE TABLE [dbo].[Staff](
[Id] [int] NOT NULL,
[CreationDate] [datetime] NOT NULL,
[DisplayName] [nvarchar](40) NOT NULL,
[DownVotes] [int] NOT NULL,
[LastAccessDate] [datetime] NOT NULL,
[Location] [nvarchar](100) NULL,
[Reputation] [int] NOT NULL,
[UpVotes] [int] NOT NULL,
[Views] [int] NOT NULL,
[AccountId] [int] NULL,
CONSTRAINT [PK_Staff_Id] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

To be able to copy IDENTITY values between tables you need to use IDENTITY_INSERT and specify the columns in the INSERT INTO statement (not used in our example) .

SET IDENTITY_INSERT dbo.Employee ON

To copy all rows for users from Denmark to the new table;

INSERT INTO dbo.Staff
SELECT
    [Id],
    [CreationDate] ,
    [DisplayName] ,
    [DownVotes] ,
    [LastAccessDate],
    [Location] ,
    [Reputation] ,
    [UpVotes] ,
    [Views] ,
    [AccountId]
FROM windows2016.stackoverflow2013.dbo.users
WHERE location = 'Denmark'

To set the value to zero for column DownVotes when View is less than 100;

UPDATE [StackOverflow2013].[dbo].[Staff]
  SET DownVotes = 0
  WHERE views < 100

Select only one row by use of ID column;

SELECT *
FROM StackOverflow2013.dbo.staff
WHERE id = 19 AND location = 'denmark'
Update one column value for one row;
UPDATE StackOverflow2013.dbo.staff
SET DownVotes = 1
WHERE id = 19 AND location = 'denmark'

To list all users sorted by views column;

SELECT 
       [Id]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[LastAccessDate]
      ,[Location]
      ,[Reputation]
      ,[UpVotes]
      ,[Views]
      ,[AccountId]
  FROM [StackOverflow2013].[dbo].[Staff]
  ORDER BY views DESC

 

 

More information:

http://www.sql-server-helper.com/error-messages/msg-8101.aspx

https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/

https://www.sqlshack.com/overview-of-the-sql-insert-statement/

https://www.tutorialspoint.com/sql/sql-order-by.htm

Product:

Microsoft SQL server 2016

Issue:

How do I give access only to a table?

Solution:

Ensure the user exist in the database. Enter below SQL query to create a role (RESTRICTED_SELECT), and set the SELECT to a table (TABLE_NAME) for that role. Last add the user (USER_NAME) to the role.  Replace values with your user and table names.

Or better add a AD group to the role, then the IT department can add people to that AD group to get access to the table.

CREATE ROLE RESTRICTED_SELECT
GO
GRANT SELECT ON TABLE_NAME TO RESTRICTED_SELECT
GO
SP_ADDROLEMEMBER RESTRICTED_SELECT , USER_NAME

If i want give access to a function, for instance the user should be able to see Active Monitor from SSMS enter below SQL query.

USE master;
CREATE SERVER ROLE SQLMonitor ;
GRANT VIEW SERVER STATE TO SQLMonitor ;
GO

To view the Data File I/O pane, besides VIEW SERVER STATE, the login must be granted CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permissions.

To kill a process, it’s necessary to be a member of the sysadmin role.

To add the user to a server role enter:

sp_addsrvrolemember USER_NAME, SQLMonitor

More Information:

https://www.sqlshack.com/sql-server-activity-monitor/

https://www.codeproject.com/Tips/1103206/SQL-Server-Grant-Permission-to-Particular-Table

https://www.guru99.com/sql-server-create-user.html

https://sqlserverplanet.com/dba/using-sp_addrolemember

Product:

Microsoft SQL server 2016

Issue:

How log start and stop times of SP in your database?

Suggested solution:

Create a table to store time values, and create a SP that you call from other SP to update the table.

Create the log table:

CREATE TABLE [dbo].[_log_logged](
[LogID] [BIGINT] NULL,
[starttime] [DATETIME] NULL,
[endtime] [DATETIME] NULL,
[iscomplete] [INT] NULL,
[runstatus] [INT] NULL,
[durationInSeconds] [INT] NULL,
[whatProcess] [VARCHAR](100) NULL
) ON [PRIMARY]
GO

 

Create the SP to call to create log values:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[_logger] @LogID [INT], @StartTime [DATETIME],@EndTime [DATETIME], @WhatProcess [VARCHAR](100), @NewLogID [INT] OUT 
AS BEGIN 
SET NOCOUNT ON;
DECLARE @RunTimeSeconds INT,
@RunStatus INT,
@IsComplete INT,
@NewLogRowID INT,
@myDuration NUMERIC(20,10)

IF @LogID IS NULL
SET @LogID = 0

IF @LogID = 0
BEGIN 
SET @RunStatus = 1
SET @IsComplete = 0

SET @LogID = (SELECT ISNULL((MAX(LogID)),0) + 1 AS maxID FROM _log_logged)
INSERT INTO _log_logged(LogID
,StartTime
,runStatus
,IsComplete
,WhatProcess
)
SELECT @LogID,
@StartTime, 
@RunStatus,
@IsComplete,
@WhatProcess

END

ELSE IF @LogID != 0
BEGIN
SET @RunStatus = 0
SET @IsComplete = 1
UPDATE a
SET EndTime = @EndTime,
IsComplete = @IsComplete,
RunStatus = @RunStatus,
whatProcess = @WhatProcess,
DurationInSeconds= DATEDIFF(SECOND,@StartTime, @EndTime) 
FROM _log_logged a
WHERE a.LogID = @LogID

END

SET @NewLogID = @LogID
END
GO

Add below code to your SP, to enable them to log values to the above table.

-- =============================================
-- Author: 
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE [dbo].[USP_ThisIsTheTest]
AS
BEGIN

SET NOCOUNT ON;
declare @StartTime datetime = GETDATE();
declare @EndTime datetime 
declare @logid int
DECLARE @NewLogID INT
DECLARE @whatProcessText VARCHAR(100) = 'Your name of the sp to be logged';


EXEC [dbo].[_logger] @LogID = 0
,@StartTime = @StartTime
,@EndTime = NULL
,@whatProcess = @whatProcessText
,@NewLogID = @NewLogID OUTPUT

-- =============================================
-- Here should your code be for the process to execute


WAITFOR DELAY '00:00:10.000'


-- =============================================
-- Log the end of the sp process with this call:

SET @EndTime = GETDATE()
EXEC [dbo].[_logger] @LogID = @NewLogID, @StartTime = @StartTime ,@EndTime = @EndTime ,@whatProcess = @whatProcessText ,@NewLogID = @NewLogID OUTPUT


END

Run the SP to test the log table.

 

The result can look like this:

 

More information:

SLEEP Command in Sql Server

https://www.mssqltips.com/sqlservertip/2003/simple-process-to-track-and-log-sql-server-stored-procedure-use/

https://www.sqlservercentral.com/articles/logging-and-error-handling-for-sql-stored-procedures

https://www.tech-recipes.com/database/sql-server-error-logging-and-reporting-within-a-stored-procedure/ 

Product:

Microsoft SQL server 2016

Problem:

Why should collation be an issue?

Solution:

Ensure that the SQL server and the database all have the same collation setting from the beginning – and try to not change it.

But some products demand a specific collation setting (like cognos) – that is equal to the SQL server.

To get around differences, between databases, in each select statement use collate;

SELECT * FROM Products P INNER JOIN
ProductDesc D ON P.Pcode=D.Pcode collate SQL_Latin1_General_CP1_CI_AS

 

More information:

https://kimconnect.com/latin1_general_ci_ai-vs-sql_latin1_general_cp1_ci_as/

The Windows collation can use an index while comparing unicode and non-unicode, such as nvarchar to varchar, with a slight performance cost. The SQL collation cannot use the index while comparing data in such scenarios.

Some characters that are treated as independent letters. For example, operator LIKE ‘%ß%’ will return that exact match in SQL collation, while Windows collation will also return LIKE ‘%ss%’ as the expanded character of ß to ss.

Mixing collations within the database can cause errors such as this: ‘Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the equal to operation.’

Collation can be specified in a statement to instruct the SQL engine to use such collation ad-hoc (e.g. SELECT * FROM SomeTable WHERE SomeField COLLATE SQL_Latin1_General_CP1_CI_AS = N’ßeta’)

https://docs.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-ver16

https://www.sqlshack.com/sql-server-collation-introduction-with-collate-sql-casting/

https://www.sqlshack.com/the-collate-sql-command-overview/

Product:

Microsoft SQL server 2016

Issue:

When you try to script out the database schema, to be able to create a empty database on other server, we get a error when we select to much. If you have more than 42000 views in a database you will get issues with the wizard.

The wizard – Generate Scripts – from task menu on the database, will give error if the database contain to many objects.

Can also be a an issue if you use an older versions of SSMS or that you have encrypted SP in the database.

SSMS can crash when you try to generate the script with to many objects selected.

Solution:

Manually script out all the views in a table.

select schema_name(v.schema_id) as schema_name,

       v.name as view_name,

       v.create_date as created,

       v.modify_date as last_modified,

       m.definition

from sys.views v

join sys.sql_modules m 

     on m.object_id = v.object_id

 order by schema_name,

          view_name;

 

Then copy the result from the table column to a new query window for the new database.

Change that each line (command) end with ;

Then run the new SQL statements, to recreate the views in the new database.

More information:

https://www.gethynellis.com/2020/07/ssms-error-generating-scripts.html

https://dataedo.com/kb/query/sql-server/list-views-with-their-scripts

https://dwhanalytics.wordpress.com/tag/generate-create-script-for-all-views-in-sql-server-database/

https://sqlblog.org/2011/11/03/the-case-against-information_schema-views