CogKnowHow

Product:
Microsoft SQL Azure

Issue:

The process need more CPU, can i change the DTU on the AZURE SQL server from inside the SQL store procedure?

Solution:

Yes, use command:

ALTER DATABASE [databasename] MODIFY (EDITION = 'Standard', MAXSIZE = 250 GB, SERVICE_OBJECTIVE = 'S4');

You have to test what level is needed for your SQL query’s.

DTU Model is a preconfigured blend of compute, storage, and IO resources at a fixed price. This is the simpler option that is great for paying a fixed amount each month for a preconfigured bundles of resources.

DTU stands for Database Transaction Unit. DTUs give you a way to compare database performance across the service tiers offered by Azure. DTUs roughly measure performance as a combination of CPU, Memory, Reads, and Writes.

100 DTU S3 is less than one CPU core in a on-prem database.

To see current Azure level in a database:

SELECT Edition = DATABASEPROPERTYEX('databasename', 'EDITION'),
ServiceObjective = DATABASEPROPERTYEX('databasename', 'ServiceObjective'),
MaxSizeInBytes = DATABASEPROPERTYEX('databasename', 'MaxSizeInBytes');

More Information:

https://legacysupport.timextender.com/hc/en-us/articles/360026418592-What-Azure-SQL-Database-Service-Tier-and-Performance-Level-Should-I-Use- 

Azure SQL Database DTU Versus vCore

https://sqlperformance.com/2017/03/azure/what-the-heck-is-a-dtu 

https://www.fmsinc.com/microsoft-azure/sql-server/monitoring.htm

https://techcommunity.microsoft.com/t5/azure-sql-blog/real-world-azure-sql-db-unexpected-database-maximum-size-limit/ba-p/305363 

Product:

Microsoft SQL Azure

Issue:

What processes are running on the SQL database server?

Solution:

In SSMS enter this:

select 
r.session_id, 
s.login_name, 
c.client_net_address, 
s.host_name, 
s.program_name, 
st.text, s.status 
from sys.dm_exec_requests r 
inner join sys.dm_exec_sessions s 
on r.session_id = s.session_id 
left join sys.dm_exec_connections c 
on r.session_id = c.session_id 
outer apply sys.dm_exec_sql_text(r.sql_handle) st 
where client_net_address is not null and text is not null and s.status = 'running'

Copy the content of the text column out to notepad to see the query running in detail.

To see if it is CPU or I/O that is used most the last hour, enter in SSMS this:

SELECT
database_name = DB_NAME()
, UTC_time = end_time
, 'CPU Utilization In % of Limit' = rs.avg_cpu_percent
, 'Data IO In % of Limit' = rs.avg_data_io_percent
, 'Log Write Utilization In % of Limit' = rs.avg_log_write_percent
, 'Memory Usage In % of Limit' = rs.avg_memory_usage_percent 
, 'In-Memory OLTP Storage in % of Limit' = rs.xtp_storage_percent
, 'Concurrent Worker Threads in % of Limit' = rs.max_worker_percent
, 'Concurrent Sessions in % of Limit' = rs.max_session_percent
FROM sys.dm_db_resource_stats AS rs --past hour only
ORDER BY rs.end_time DESC;

Data IO show 100%, here is the bottleneck, maybe the SQL QUERY PLAN is using more TEMPDB than it should.

To see what SQL plan is in use, install, and use:

sp_blitzwho @GetLiveQueryPlan=1

Click on live-query plan, and you will get the SQL plan in used shown inside SSMS.

More Information:

https://learn.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use?view=azuresql 

https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs?view=azuresql

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

https://github.com/amachanic/sp_whoisactive/releases 

https://www.brentozar.com/first-aid/sp_blitzwho/ 

https://www.mssqltips.com/sqlservertip/4132/correct-sql-server-tempdb-spills-in-query-plans-caused-by-outdated-statistics/

Product:
Microsoft SQL 2016 server

Issue:

How create a new database from a BACPAK file in SSMS?

Solution:

Start SSMS and connect to the target SQL server.

On the database select “Import Data-Tier Application”

Click Next

Select the BACPAC file to import and click Next

Enter the name of the new database and click Next

Click Finish

Wait during the import.

When Operation Complete click Close.

You have your new database in the new server, including data.

More Information:

https://4sysops.com/archives/dacpac-and-bacpac-in-sql-server/ 

https://blogs.msmvps.com/deborahk/deploying-a-dacpac-with-sql-server-management-studio/ 

https://www.sqlshack.com/importing-a-bacpac-file-for-a-sql-database-using-ssms/ 

A DAC is a logical database management entity that defines all of the SQL Server objects which associates with a user’s database. A BACPAC includes the database schema as well as the data stored in the database.

Product:
Microsoft SQL server 2016 standard
Microsoft Windows 2016 server

Problem:
How load data from csv file into SQL server, where the table contains more columns than the text file?

The BULK INSERT command will fill the not used target columns with the next row of data, and you get a inconsistent table.

Solution:

Create a view of the table, where you have less columns shown, and then bulk insert to the view.

From inside SSMS script out a select of the table, and add create view to first line, remove the columns you do not want.

CREATE VIEW StorageView AS
SELECT [Organisation]
,[Version]
,[Belopp]
FROM StorageTable

Then in your BULK INSERT us the view instead, so the csv files number of columns match the (view) target tables number of columns.

BULK INSERT [StorageView]
FROM 'C:\temp\storagefile.csv'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\r',
FIRSTROW = 2
);

FIELDTERMINATOR = set the separator for the columns in the csv file
ROWTERMINATOR = set the character to skip to next row/record
FIRSTROW = tell that first line in the csv file is headers and should not be read

If you get a error like this:

Msg 4832, Level 16, State 1, Line 16
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 16
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 16
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.

Then the csv file have a blank line as the last line, edit your csv file to not contain any empty lines.

More Information:

https://www.sqlservertutorial.net/sql-server-administration/sql-server-bulk-insert/ 

https://www.w3schools.com/sql/sql_view.asp

https://www.w3schools.com/sql/sql_update.asp

https://www.mssqltips.com/sqlservertip/6109/bulk-insert-data-into-sql-server/

Product:

Microsoft SQL Azure server

Issue:

Need to change a column to have nvarchar(20) instead of nvarchar(200). How change nvarchar in table?

Solution:

Check the data length in the column (nvarchar is unicode, that uses 2 bytes for a single character):

SELECT DATALENGTH(Column_Name) AS FIELDSIZE, Column_Name 
FROM Table_Name
ORDER by FIELDSIZE DESC

Change the columns value with:

Update Table_Name set Column_Name = left(coalesce(Column_Name ,''),20);
Alter table Table_Name alter column Column_Name nvarchar(20) not null;

If you have constrains, you need to drop them first.  DROP INDEX index_name  ON table_name  ;

https://www.w3schools.com/sql/sql_ref_drop_constraint.asp

Otherwise, rename the table;

sp_rename 'old_table_name', 'new_table_name'

Creata a new table with the old name, in SSMS you select the table and from menu select “Script table as” – create to – new query editor window. Adjust the code to have the new nvarchar value, example below:

CREATE TABLE [dbo].[table2](
[index] [int] NOT NULL,
[Name] [nvarchar](20) NULL,
[Country] [nvarchar](50) NULL,
[Employess] [int] NULL
) ON [PRIMARY]

Copy the data over to the new table

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition; 

For above table the example is as below, the left(coalesce([Name] ,”),20) make that we only copy the 20 first characters.

 insert into table2 ( [index],[Name]
,[Country]
,[Employess])
select [index] ,left(coalesce([Name] ,''),20)
,[Country]
,[Employess] from table1

If you not use the LEFT function you may get a error like “String or binary data would be truncated”.

More Information:

https://javarevisited.blogspot.com/2016/03/how-to-increase-length-of-existing-VARCHAR-column-in-SQL-Server.html 

https://www.sqlservertutorial.net/sql-server-basics/sql-server-nvarchar/

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver16 

How to rename tables in SQL Server with the sp_rename command

https://www.w3schools.com/sql/sql_insert_into_select.asp

https://www.tutorialrepublic.com/sql-tutorial/sql-cloning-tables.php

https://www.w3schools.com/sql/sql_ref_insert_into_select.asp

https://www.w3schools.com/sql/func_sqlserver_left.asp

As index is a reserved command in SQL, you need to have it inside [ ] for SQL to understand it is a table name.

Product:
Microsoft SQL Azure

Issue:
System is slow, and it looks like TEMPDB is working hard. What can we do?

Solution:

Check what is going on in SQL TEMPDB with this query:

SELECT [Source] = 'database_transactions',
[session_id] = ST.session_id,
[transaction_id] = ST.transaction_id,
[login_name] = S.login_name,
[database_id] = S.database_id,
[program_name] = S.program_name,
[host_name] = S.host_name,
[database_id] = DT.database_id,
[database_name] = CASE
WHEN D.name IS NULL AND DT.database_id = 2 THEN 'TEMPDB'
ELSE D.name
END,

[log_reuse_wait_desc] = D.log_reuse_wait_desc,
[database_transaction_log_used_Kb] = CONVERT(numeric(18,2), DT.database_transaction_log_bytes_used / 1024.0 ),
[database_transaction_begin_time] = DT.database_transaction_begin_time,
[transaction_type_desc] = CASE DT.database_transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END,

[transaction_state_desc] = CASE DT.database_transaction_state
WHEN 1 THEN 'The transaction has not been initialized.'
WHEN 2 THEN 'The transaction is active'
WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
WHEN 4 THEN 'The transaction has generated log records.'
WHEN 5 THEN 'The transaction has been prepared.'
WHEN 10 THEN 'The transaction has been committed.'
WHEN 11 THEN 'The transaction has been rolled back.'
WHEN 12 THEN 'The transaction is being committed. (The log record is being generated, but has not been materialized or persisted.)'
END,

[active_transaction_type_desc] = CASE AT.transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END,

[active_transaction_state_desc] = CASE AT.transaction_state
WHEN 0 THEN 'The transaction has not been completely initialized yet.'
WHEN 1 THEN 'The transaction has been initialized but has not started.'
WHEN 2 THEN 'The transaction is active'
WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
WHEN 6 THEN 'The transaction has been committed.'
WHEN 7 THEN 'The transaction is being rolled back.'
WHEN 8 THEN 'The transaction has been rolled back.'
END

FROM sys.dm_tran_database_transactions DT
INNER JOIN sys.dm_tran_session_transactions ST ON DT.transaction_id = ST.transaction_id
INNER JOIN sys.dm_tran_active_transactions AT ON DT.transaction_id = AT.transaction_id
INNER JOIN sys.dm_exec_sessions S ON ST.session_id = S.session_id
LEFT JOIN sys.databases D ON DT.database_id = D.database_id
WHERE DT.database_id = 2 -- tempdb
ORDER BY ST.session_id, DT.database_id;

More Information:

https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-db-and-tempdb-usage-tracking/ba-p/1573220 

https://techcommunity.microsoft.com/t5/azure-database-support-blog/resolve-tempdb-related-errors-in-azure-sql-database/ba-p/3597944 

Product:
Microsoft Azure Database SQL

Issue:
When running a Store Procedure (SP) you get an error after a long time,

Sql error number: 40544. Error Message: The database ‘tempdb’ has reached its size quota.

Suggested solution:

Increase, if possible, your DTU, this will reset the tempdb and you will free space to start over.

In SSMS check what SQL Azure level you are with:

SELECT Edition = DATABASEPROPERTYEX('databasename', 'EDITION'),

        ServiceObjective = DATABASEPROPERTYEX('databasename', 'ServiceObjective'),

        MaxSizeInBytes =  DATABASEPROPERTYEX('databasename', 'MaxSizeInBytes');

In SSMS enter to see the file size of TEMPDB at the moment:

SELECT [Source] = 'database_files', 
[TEMPDB_max_size_MB] = SUM(max_size) * 8 / 1027.0, 
[TEMPDB_current_size_MB] = SUM(size) * 8 / 1027.0, 
[FileCount] = COUNT(FILE_ID)
FROM tempdb.sys.database_files
WHERE type = 0 --ROWS

Above we used up the file size limit of 13.9 GB for TEMPDB that exist in the first tiers. But with below SQL query can you see how much space is used inside:

SELECT 
(SUM(unallocated_extent_page_count)*1.0/128) AS [Free space(MB)]
,(SUM(version_store_reserved_page_count)*1.0/128) AS [Used Space by VersionStore(MB)]
,(SUM(internal_object_reserved_page_count)*1.0/128) AS [Used Space by InternalObjects(MB)]
,(SUM(user_object_reserved_page_count)*1.0/128) AS [Used Space by UserObjects(MB)]
FROM tempdb.sys.dm_db_file_space_usage;

Recommended solution, is to check what is causing the creation of large use of TEMPDB space, by check your query plans in SSMS.

Then improve you table columns format, to only what you need. Use nvarchar(50) instead of nvarchar(max) etc.

Check you store procedures query’s, and insert a index on the columns that you thing will make the selection smaller fastest.

DTU in azure is a combination of CPU usage per second and read/write I/O per second to disc. When you have used up your quota, there is a limit on how much bytes you can write to disc per second, so your process will succeed but it will take much longer as a small amount of data is processed at each second.

A database transaction unit (DTU) represents a blended measure of CPU, memory, reads, and writes. Service tiers in the DTU-based purchasing model are differentiated by a range of compute sizes with a fixed amount of included storage, fixed retention period for backups, and fixed price.

https://blog.atwork.at/post/Azure-Subscription-and-Service-Limits 

https://www.spotlightcloud.io/blog/what-is-dtu-in-azure-sql-database-and-how-much-do-we-need

More information:

https://knowledge-base.havit.eu/2018/02/19/azure-sql-the-database-tempdb-has-reached-its-size-quota-partition-or-delete-data-drop-indexes-or-consult-the-documentation-for-possible-resolutions-microsoft-sql-server-error-40544/ 

https://techcommunity.microsoft.com/t5/azure-database-support-blog/resolve-tempdb-related-errors-in-azure-sql-database/ba-p/3597944 

https://sqlcoffee.com/Azure_0013.htm 

https://www.brentozar.com/archive/2018/02/memory-grants-sql-servers-public-toilet/

Top five considerations for SQL Server index design

https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-dtu-single-databases?view=azuresql#tempdb-sizes 

https://www.brentozar.com/archive/2023/09/oops-i-lost-my-indexes-in-azure-sql-db/ 

https://www.sqlshack.com/sql-index-overview-and-strategy/ 

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver16  

https://datasimantics.com/2018/08/24/sql-servers-nvarcharmax-and-how-to-wield-it/ 

https://www.ibm.com/support/pages/only-first-1024-characters-nvarcharmax-column-are-presented-report-based-dqm-package

https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu?view=azuresql

https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-dtu-single-databases?view=azuresql 

To see sessions that use TEMPDB:

-- Sessions with open transactions in tempdb
SELECT [Source] = 'database_transactions', 
[session_id] = ST.session_id, 
[transaction_id] = ST.transaction_id, 
[login_name] = S.login_name, 
[database_id] = S.database_id, 
[program_name] = S.program_name, 
[host_name] = S.host_name, 
[database_id] = DT.database_id, 
[database_name] = CASE
WHEN D.name IS NULL AND DT.database_id = 2 THEN 'TEMPDB'
ELSE D.name
END, 
[log_reuse_wait_desc] = D.log_reuse_wait_desc, 
[database_transaction_log_used_Kb] = CONVERT(numeric(18,2), DT.database_transaction_log_bytes_used / 1024.0 ), 
[database_transaction_begin_time] = DT.database_transaction_begin_time, 
[transaction_type_desc] = CASE DT.database_transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END, 
[transaction_state_desc] = CASE DT.database_transaction_state
WHEN 1 THEN 'The transaction has not been initialized.'
WHEN 2 THEN 'The transaction is active'
WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
WHEN 4 THEN 'The transaction has generated log records.'
WHEN 5 THEN 'The transaction has been prepared.'
WHEN 10 THEN 'The transaction has been committed.'
WHEN 11 THEN 'The transaction has been rolled back.'
WHEN 12 THEN 'The transaction is being committed. (The log record is being generated, but has not been materialized or persisted.)'
END,
[active_transaction_type_desc] = CASE AT.transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END, 
[active_transaction_state_desc] = CASE AT.transaction_state
WHEN 0 THEN 'The transaction has not been completely initialized yet.'
WHEN 1 THEN 'The transaction has been initialized but has not started.'
WHEN 2 THEN 'The transaction is active'
WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
WHEN 6 THEN 'The transaction has been committed.'
WHEN 7 THEN 'The transaction is being rolled back.'
WHEN 8 THEN 'The transaction has been rolled back.'
END
FROM sys.dm_tran_database_transactions DT
INNER JOIN sys.dm_tran_session_transactions ST ON DT.transaction_id = ST.transaction_id
INNER JOIN sys.dm_tran_active_transactions AT ON DT.transaction_id = AT.transaction_id
INNER JOIN sys.dm_exec_sessions S ON ST.session_id = S.session_id
LEFT JOIN sys.databases D ON DT.database_id = D.database_id
WHERE DT.database_id = 2 -- tempdb
ORDER BY ST.session_id, DT.database_id;

Product:
Microsoft SQL Azure

Issue:
How many rows are it in a database table?

Solution:

Enter in SSMS:

sp_spaceused 'dbo.tablename';

This will give you both number of rows and space used by the table.

More Information:

https://www.brentozar.com/archive/2014/02/count-number-rows-table-sql-server/

https://www.sqlshack.com/capture-databases-usage-stats-using-sp_spaceused-powershell/