By: Daniel Farina | Updated: 2018-09-14 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Database Administration
Problem
Administering SQL Server comes in many forms and there are many tools that can be used to manage and administer SQL Server instances. One tool that all DBAs should be familiar with is sqlcmd, the command line tool that allows you to run queries without the need of a graphical interface. With the release of SQL Server on Linux, the use of command line tools becomes even greater. In this tip we look at some things you can do to help administer SQL Server using sqlcmd and some key scripts that you can add to your toolbox.
Solution
In my career I have worked with many database engines other than SQL Server, like Oracle and MySQL that run on Linux since the release of these database systems. That forced me to work in situations where I was unable to use a GUI. Now that SQL Server has versions that run on Linux, we will see more situations when DBAs don’t have access to a graphical interface.
SQL Server has included command line tools to run queries since its beginning. First there was the deprecated isql.exe, osql.exe and now we have sqlcmd. But most of us are not used to operating at the console level. As a consequence most of us overlook sqlcmd, even though we can use SQL Srever Management Studio (SSMS) to run in sqlcmd mode. If you are one of those that didn’t know about sqlcmd mode in SSMS, I suggest you read SQL Server Management Studio SQLCMD mode option.
Why we must have SQL Server Code Templates
Among the benefits of using code templates is the obvious benefit of code reusability. But there is also a time benefit of using scripts, because we don't have to think and write a new query from scratch. This is very important if you are working in a production environment, when problems arise you need solve them as soon as possible.
SQL Server Code Templates
I don’t know a Database Administrator or even an IT professional that does not have his/her own script collection. Some DBAs use a folder with script files and others like me take advantage of SQL Server Management Studio Template Browser Window. By doing so, I have my scripts when I need them and don’t need to drag and drop a script file into SSMS. But when you don’t have a graphic utility you should rely on a folder schema to hold your code templates.
When thinking about code templates on Linux we have two different approaches that differ just a bit by formality: its invocation. Please allow me to explain. Both approaches rely on the sqlcmd tool; the difference is that we can invoke sqlcmd directly or by a bash script. By using a bash script you will need to pass the connection data (server name, login and password) on the script invocation; otherwise, you can opt to use a .sql script and then you should pass the connection data directly to sqlcmd. For simplicity, I will only consider the case that we are only using sqlcmd and not a bash script.
In order to use sqlcmd to run our script templates, we must specify the server on which we want to run it and the user and password if you are using SQL authentication. Then we have to pass the script as an argument and I suggest you use the –W switch to remove trailing spaces and of course, include the script to be executed with the –i switch. Additionally, when the script template needs to be executed on a specific database you should add the –d switch followed by the database name.
Take a look at this example.
sqlcmd -S ORIOM -E -i HighVLFs.sql -d AdventureWorks2012 -W
Let’s break down the command:
| Parameter | Description |
|---|---|
| -S ORIOM | Tells sqlcmd to connect to server ORIOM. |
| -E | Uses Windows authentication for logging into the instance. |
| -i HighVLFs.sql | Loads and executes the script file HighVLFs.sql. |
| -d AdventureWorks2012 | The database where to run the script. |
| --W | To remove trailing spaces from the column output. |
As an alternative, you can use the –v switch to specify the database on which you want to execute the code within the script. That switch is used for sending parameters to the script. In case you decide to use the –v switch you must modify your script so it uses the sqlcmd format for variable replacement. Sqlcmd parses the scripts after executing and searches for expressions like “$(something)” where “something” is the name of a variable that will be replaced with the value provided by using the –v switch.
Take a look at the next example:
USE $(database)
GO
SELECT name ,
physical_name ,
CAST(size / 128. AS NUMERIC(10, 2)) AS [size MB] ,
state_desc
FROM sys.database_files
The previous script returns the name, location, size and status of the files in a specific database. The database is passed as an argument with the –v switch as follows:
sqlcmd -S ORIOM -E -W -i dbFiles.sql –v database = AdventureWorks2012
Installing the SQL Server Code Templates on Linux Servers
To use the scripts on multiple servers, I suggest compressing the files and then copying the compressed file as shown below.
We are all familiar with using Windows and compressing files into a compressed file and copy files around the network. Below I will cover how this can be done when using Linux.
Compressing Files on Linux
Assuming your script collection is in a folder, the best way to move it across servers is by compressing the scripts in a compressed file. In this case I named the compressed file Templates.tar.xz. If you are working on Windows you can use any compression program like Winzip or Winrar.
If you are using Linux you can use the tar console command as follows:
tar -cJf <archive.tar.xz> <files>
Let’s break down the command:
| Parameter | Description |
|---|---|
| -c | Creates a new archive |
| -J | Uses oz. tool for compression. |
| -f | Tells tar to use an archive for output instead of a tape. |
| archive.tar.xz | The database on which the script will run. |
| files | List of files to be compressed. |
In the case the previous command, assuming you will execute it on the same directory that contains your templates will be like the command below:
tar -cJf Templates.tar.xz *
For those of you who are new to Linux, tar is a utility for collecting many files into one archive file. The name is derived from Tape Archive because its origins was mostly used with magnetic tapes for backup.
Copy Compressed File to Another Linux Server
Now we must copy the file with our templates to other servers. For this purpose I will use PSCP, the PuTTY Secure Copy client which is a tool for transferring files securely between computers using an SSH connection. You can download this tool and the entire PuTTY suite. The syntax of PSCP to transfer files to another computer is as follows:
pscp <File> <UserName>@<Host or IP Address>:<Destination Folder>
Take a look at the next example:
pscp C:\Users\Administrator\Documents\Templates.tar.xz [email protected]:/home/daniel/Documents/
In this case we are copying file “C:\Users\Administrator\Documents\Templates.tar.xz” to folder “/home/daniel/Documents/” on the remote server “10.10.10.60” by using the credentials of local user “Daniel”.
Decompress Linux Files
After copying the file to the target server, we must decompress its contents with the following command, but first I will create a new folder named Templates to hold the files.
mkdir Templates tar –xf Templates.tar.xz –C ./Templates
Let’s break down the command:
| Parameter | Description |
|---|---|
| -x | Extracts files from the source which in this case is a file. |
| -f | Tells tar to use an archive instead of a tape. |
| -C | Sets destination folder. |
Valuable SQL Server Scripts for your sqlcmd Templates
In order to help you to build your own script templates, I collected some queries on Dynamic Management Views.
Top 5 SQL Server Queries by CPU
Save the following code in a file named Top5CPU.sql.
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
Top 5 SQL Server triggers identified by average elapsed time
Save the following code in a file named Top5TriggersByCPU.sql.
SELECT TOP 5 d.object_id, d.database_id, DB_NAME(database_id) AS 'database_name',
OBJECT_NAME(object_id, database_id) AS 'trigger_name', d.cached_time,
d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_trigger_stats AS d
ORDER BY [total_worker_time] DESC;
SQL Server row count aggregate information
The following will show total rows, minimum rows, maximum rows and last rows for queries.
Save the following code in a file named RowCountAggByQuery.sql.
SELECT qs.execution_count ,
SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1,
( CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2) AS query_text ,
qt.dbid ,
dbname = DB_NAME(qt.dbid) ,
qt.objectid ,
qs.total_rows ,
qs.last_rows ,
qs.min_rows ,
qs.max_rows
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE '%SELECT%'
ORDER BY qs.execution_count DESC;
Linking SQL Server session information to operating system threads
Save the following code in a file named SessionThread.sql.
SELECT STasks.session_id, SThreads.os_thread_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id;
Last SQL Server Log Backup Time by Database
Save the following code in a file named LastLogBckupTime.sql.
SELECT name AS 'Database Name' ,
log_backup_time AS 'last log backup time'
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id);
SQL Server Databases with more than 100 VLFs in log files
Save the following code in a file named LastLogBckupTime.sql.
SELECT [name] ,
COUNT(l.database_id) AS 'vlf_count'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name]
HAVING COUNT(l.database_id) > 100
SQL Server Lock Information by Database ID
Save the following code in a file named lock_info_by_dbid.sql.
SELECT resource_type, resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description ,
DB_NAME(resource_database_id) [Database]
FROM sys.dm_tran_locks
SQL Server cursors opened longer than 36 hours
Save the following code in a file named OldCursors.sql.
SELECT creation_time ,
cursor_id ,
name ,
c.session_id ,
login_name
FROM sys.dm_exec_cursors (0) AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 36;
GO
Percentage of failed SQL Server background jobs
Save the following code in a file named FailedBGJobs.sql.
SELECT CASE ended_count
WHEN 0 THEN 'No jobs ended'
ELSE CAST((failed_lock_count + failed_giveup_count + failed_other_count) / CAST(ended_count AS float) * 100 AS varchar(20))
END AS [Percent Failed]
FROM sys.dm_exec_background_job_queue_stats;
GO
SQL Server Asynchronous jobs in the background queue
Number of active asynchronous jobs in the background queue for each database in the instance of SQL Server.
Save the following code in a file named AsyncJobsInBGQueue.sql.
SELECT DB_NAME(database_id) AS [Database] ,
COUNT(*) AS [Active Async Jobs]
FROM sys.dm_exec_background_job_queue
WHERE in_progress = 1
GROUP BY database_id;
Percentage of SQL Server failed job enqueue attempts
Save the following code in a file named FailedEnqueuedAttempts.sql.
SELECT CASE enqueued_count
WHEN 0 THEN 'No jobs posted'
ELSE CAST((enqueue_failed_full_count + enqueue_failed_duplicate_count) / CAST(enqueued_count AS float) * 100 AS varchar(20))
END AS [Percent Enqueue Failed]
FROM sys.dm_exec_background_job_queue_stats;
GO
SQL Server Blocking Sessions
Save the following code in a file named blocking_sessions.sql.
SELECT A.Session_id, A.blocking_session_id,
DB_NAME(B.database_id) [Database] ,
C.text
FROM sys.dm_exec_requests A
LEFT JOIN sys.dm_exec_requests B ON A.blocking_session_id = B.session_id
OUTER APPLY sys.dm_exec_sql_text(B.sql_handle) C
WHERE A.blocking_session_id <> 0
GO
SQL Server Wait Types
Save the following code in a file named Wait_Types.sql.
SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats;
SQL Server Pending IO Requests
Save the following code in a file named dm_io_pending_io_requests.sql.
SELECT * FROM sys.dm_io_pending_io_requests
SQL Server Space Consumed in TempDB
This query can be used to determine space consumed in tempdb, by version store of each database in a SQL Server instance.
Save the following code in a file named tempdb_space_by_versionstore.sql.
SELECT DB_NAME(database_id) as 'Database Name',
reserved_page_count,
reserved_space_kb
FROM sys.dm_tran_version_store_space_usage;
SQL Server Last Active VLF Position
Save the following code in a file named dLastActiveVLFPos.sql. Notice the “d” in the file name. This is because the script must be executed in the context of the database you want to know the last active VLF position.
;
WITH cte_vlf
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY vlf_begin_offset ) AS vlfid ,
DB_NAME(database_id) AS [Database Name] ,
vlf_sequence_number ,
vlf_active ,
vlf_begin_offset ,
vlf_size_mb
FROM sys.dm_db_log_info(DEFAULT)
),
cte_vlf_cnt
AS ( SELECT [Database Name] ,
COUNT(vlf_sequence_number) AS vlf_count ,
( SELECT COUNT(vlf_sequence_number)
FROM cte_vlf
WHERE vlf_active = 0
) AS vlf_count_inactive ,
( SELECT COUNT(vlf_sequence_number)
FROM cte_vlf
WHERE vlf_active = 1
) AS vlf_count_active ,
( SELECT MIN(vlfid)
FROM cte_vlf
WHERE vlf_active = 1
) AS ordinal_min_vlf_active ,
( SELECT MIN(vlf_sequence_number)
FROM cte_vlf
WHERE vlf_active = 1
) AS min_vlf_active ,
( SELECT MAX(vlfid)
FROM cte_vlf
WHERE vlf_active = 1
) AS ordinal_max_vlf_active ,
( SELECT MAX(vlf_sequence_number)
FROM cte_vlf
WHERE vlf_active = 1
) AS max_vlf_active
FROM cte_vlf
GROUP BY [Database Name]
)
SELECT [Database Name] ,
vlf_count ,
min_vlf_active ,
ordinal_min_vlf_active ,
max_vlf_active ,
ordinal_max_vlf_active ,
( ( ordinal_min_vlf_active - 1 ) * 100.00 / vlf_count ) AS free_log_pct_before_active_log ,
( ( ordinal_max_vlf_active - ( ordinal_min_vlf_active - 1 ) )
* 100.00 / vlf_count ) AS active_log_pct ,
( ( vlf_count - ordinal_max_vlf_active ) * 100.00 / vlf_count ) AS free_log_pct_after_active_log
FROM cte_vlf_cnt
GO
Last SQL Server Log Backup Time
Save the following code in a file named LastLogBckupTime.sql.
SELECT name AS 'Database Name' ,
log_backup_time AS 'last log backup time'
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id);
Viewing the number of SQL Server R scripts running on server
Save the following code in a file named RScriptsRunning.sql.
SELECT counter_name ,
counter_value
FROM sys.dm_external_script_execution_stats
WHERE language = 'R';
Next Steps
- In case you need to refresh your knowledge on the sqlcmd utility you can read my previous tip: Introduction to SQL Server's sqlcmd utility.
- Don't know how to execute script files with sqlcmd? Take a look at this tip: Execute SQL Server Script Files with the sqlcmd Utility.
- You want to create a template that accepts inputs? Use sqlcmd scripting variables which you can learn more about in this tip: Using SQL Server sqlcmd scripting variables.
- In order to improve your custom templates, I suggest you take a look at the next tip where I cover user variables and variable precedence on sqlcmd: sqlcmd User Variables and Variable Precedence Examples.
- Are you experiencing difficulties when connecting to your SQL Server instances using sqlcmd? Here is a tip that covers the different ways you can connect to your instances with sqlcmd: Different Ways to Connect to SQL Server Using sqlcmd.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-09-14
