Sql Server : CPU TIME vs. Elapsed Time

Interesting one today:

When we run a query with SET STATISTICS TIME ON, we see CPU Time and Elapsed Time statistics for a query. What is the difference?

SET STATISTICS ON

 

CPU Time is amount of CPU cycles (in time) spent by the workload thread on a particular processor/CPU. This is not an indication of the duration since the query began running on that CPU (there are some nuances — we’ll cover below)

Elapsed Time is the total duration of that thread, the time it took from start to end.

Simple Example:

Let’s say there is a workload thread for a simple query. The query runs for a bit (let’s say 2 seconds) and gets put on hold for a bit (let’s say 5 seconds), and then runs again (let’s say for 1 second) all the way to completion. The time the thread actually spent on CPU is counted towards CPU time (the two times, 2 & 1 seconds — 3 seconds total). Elapsed time is the total duration from when the query was issued to be executed to the time the result was generated — total 8 seconds.

Parallel Processing:

Let’s say there are 4 thread carrying out the workload of a query. Each run on a processor for 1 second and complete all the work in one shot.

Total CPU Time = 4 * 1 = 4 seconds (4 CPU’s with 1 second each)

Elapsed Time = 1 second (from start to finish)

Summary

CPU Time and Elapsed Time measure different time metrics for the same query. One measure the CPU cycles spent (in time) and the other measures the duration. The difference is important in figuring out what problem we are trying to solve.

1. If a query has high Elapsed Time, but low CPU Time, that means, there is some CPU contention or other resource contention (waiting for all resources to be available before the query could be given a CPU).
   1. We want to look for ways to optimize the the way query is written — is it requesting for more resources (rows) than it needs. End user will feel the pain in waiting — if this happens to majority of the queries. 
   2. Adding to this, if the CPUs are all busy all the time, that definitely means we need bigger CPUs for the workload or optimized queries.
   3. Conversely, if the CPU’s are not too busy at this time, that means, the thread is just waiting for other resources (concurrency — locked tables) to be released for this query to progress. Optimize the queries.
2. If CPU Time is higher than Elapsed Time, then there is good amount of parallelism in play.
   1. As long as the CPUs are optimally utilized, this is not a problem, but if you notice more than 80% utilization all the time (the threshold is even lower if they are virtual CPUs), then we need to add extra CPUs to alleviate the situation.
   2. This may not be a problem right now, but it will evolve into a problem soon. One extra long query will snowball everything into high delays — to the point where a user will notice the delays. 

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Split String using T-SQL

Interesting one today:

In Sql Server, for a long time, we’ve needed a quick way to split a string based on some delimiter. Starting Sql Server 2016, a new table-valued function was introduced called STRING_SPLIT.

Simple Example: Split a string to return a table

--
-- Split string into a table
--
SELECT * 
FROM string_split('Apple, Pineapple, Orange, Cherries', ',')
GO

DECLARE   @Delimiter VARCHAR(1) = ','
		, @SourceString	VARCHAR(100) = 'Apple, Pineapple, Orange, Cherries'
SELECT * 
FROM string_split(@SourceString, @Delimiter)
GO

String Split

NOTE: A minimum of Compatibility level 130 is required for this feature to be available.

JOIN with other tables:

Also, in situations where you want to convert the string and join with another table at the same time, use CROSS_APPLY

Let’s take the below code as example:

--
-- JOIN string_split with another table
--
CREATE TABLE dbo.TestTable(
	  ID			TINYINT			NOT NULL IDENTITY(1,1)
	, FoodType		VARCHAR(10)
	, SampleValues	VARCHAR(100)
)
GO

INSERT INTO dbo.TestTable (FoodType, SampleValues) 
VALUES ('Fruit', 'Apple, Pineapple, Orange, Cherry, Tomato')
	 , ('Vegetable', 'Carrot, Okra')
GO

SELECT * 
FROM dbo.TestTable

SELECT ID, FoodType, T.value
FROM dbo.TestTable
CROSS APPLY string_split(SampleValues, ',') AS T
GO

Join String Split

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server: Query to check Database Mail was successful

Quick one today:

While not used every time or ever where, Database Mail is a valuable tool in Sql Server. It elevates any in-house monitoring solutions — and provides a key piece to send alert emails. Today, we’ll look at how to check if the emails queued are sent or failed.

--
-- Check the status of emails in MSDB
--
SELECT mailitem_id, subject, recipients, send_request_date, send_request_user, sent_status, sent_date
FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC
GO

DB Mail Status

There are more system tables that helps capture the status of unsent, failed & sent emails.

--
-- Other system tables to check status of emails in MSDB
--
SELECT * 
FROM msdb..sysmail_sentitems
GO
SELECT * 
FROM msdb..sysmail_unsentitems
GO
SELECT * 
FROM msdb..sysmail_faileditems
GO

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : How to start a Sql Agent Job using T-SQL query

Quick one today:

A quick technique to run Sql Agent job without using UI has come in handy several times in the past, so it deserves a post by itself.

--
--    Start a Sql Agent Job
--
USE msdb
GO

EXEC dbo.sp_start_job N'JobName'
GO

This is simple and elegant way to run one or many jobs at the same time making
interactions with SQL Server more efficient. Just query job names from
--
--    Get list of job names
--
SELECT * FROM msdb..sysjobs
GO

Hope this helps,
_Sqltimes
					
Read Full Post »
				

Sql Server: Multiple ways to query definition of procedures, functions, views, etc

Interesting one today:

In one of the recent posts, we saw a technique to query definition of several database objects using sys.sql_modules. As with anything in Sql Server, there are more than one ways to accomplish the same task.

Today, we’ll uncover a couple more.

Object_Definition Function:

This is one of the easiest way to query an object definition. This system function provides definitions of several database objects

• Check Constraint
• Default
• Stored Procedure
• Functions
• Rule
• Trigger
• View
• etc (yes there are more)

Example:

--
-- Query definition of database objects
--
--
SELECT OBJECT_DEFINITION(OBJECT_ID('SomeProcedure'))
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('SomeView'))
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('SomeTrigger'))
GO

HelpText Procedure

This one goes even further; sp_helptext procedure helps us query definitions of system objects as well, along with the list in above section.

• Rule
• Default
• CHECK Constraint
• Stored Procedure
• Function
• Trigger
• View
• System Objects
• etc (yes there are more)

Example:

--
-- Query object definition
--
EXEC sp_helptext 'SomeProcedure'
GO
EXEC sp_helptext 'SomeTrigger'
GO
EXEC sp_helptext 'SomeView'
GO

Sql_modules system table:

In a recent post, we saw more details, so this will be just a short mention (for completeness), for more details please refer to the old article here.

 

Query objects using object name (but not table name):

Example:

--
-- Query objects using object name (but not table name)
--
SELECT O.object_id, O.name, O.type_desc, M.definition
FROM sys.objects AS O
LEFT OUTER JOIN sys.sql_modules AS M
ON M.object_id = O.object_id
WHERE O.name LIKE '%Object_Name%'
OR O.name LIKE '%Object_Name_but_not_table_name%'
GO

Query objects using any object name, including table name:

 
-- 
-- Query objects using any object name in the definition (including table name)
-- 
SELECT O.object_id, O.name, O.type_desc, M.definition
FROM sys.objects AS O
LEFT OUTER JOIN sys.sql_modules AS M
ON M.object_id = O.object_id
WHERE M.definition LIKE '%ViewName%'
OR M.definition LIKE '%TableName[.]%'
GO

More information is available on MSDN

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Get table row count without COUNT(*). Non invasive

Quick one today:

Quite frequently, we need to get a sense of the size of tables in our databases. While the default approach is running a COUNT(*) query, which is too invasive on the tables, there are better ways.

Sql Server always knows more about the objects in its databases than us. So its to our advantage to use some of those hidden gems.

Drumroll !!

Enter system table sys.sysindexes. It has loads of data about each table and its indexes. Below is a query that gets row count for all tables while ignoring many system tables. 

Three Options:

1. Query row counts on all tables including system tables
2. Query row counts on all user tables
3. Query row counts on a given user table

 

Query row counts on all tables including system tables

Query all tables in the database:

--
--  Query row count on all tables
--
SELECT OBJECT_NAME(id), rowcnt 
FROM sys.sysindexes 
WHERE indid <= 1 
ORDER BY rowcnt DESC
GO

 

Query row counts on all tables including system tables

Query all user tables

--
--  Query row count on user tables without running COUNT(*)
--
SELECT OBJECT_NAME(id), rowcnt
FROM sys.sysindexes
WHERE indid <= 1
AND OBJECT_NAME(id) NOT LIKE 'sys%'
AND OBJECT_NAME(id) NOT LIKE 'queue%'
AND OBJECT_NAME(id) NOT LIKE 'sqlagent%'
AND OBJECT_NAME(id) NOT LIKE 'plan%'
AND OBJECT_NAME(id) NOT LIKE 'filestream%'
AND OBJECT_NAME(id) NOT LIKE 'filetable%'
ORDER BY rowcnt DESC
GO

 

Query row counts on all tables including system tables:

If you want to filter it down further, just add a filter for your table.

--
--  Query row count on MyTable (particular table)
--
SELECT OBJECT_NAME(id), rowcnt
FROM sys.sysindexes
WHERE indid <= 1
AND OBJECT_NAME(id) NOT LIKE 'sys%'
AND OBJECT_NAME(id) NOT LIKE 'queue%'
AND OBJECT_NAME(id) NOT LIKE 'sqlagent%'
AND OBJECT_NAME(id) NOT LIKE 'plan%'
AND OBJECT_NAME(id) NOT LIKE 'filestream%'
AND OBJECT_NAME(id) NOT LIKE 'filetable%'
AND OBJECT_NAME(id) LIKE '%MyTable%'       -- put your table name here
ORDER BY rowcnt DESC
GO

 

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Using ESCAPE clause with LIKE clause in T-SQL

Interesting one today

LIKE is a popular clause in T-SQL. It has many nuances to filter/query the exact row that you want. One of them is the ESCAPE clause.

Lets say, you want to query for all names that start with Kumar, then your query might look something like this:

--
--  Query rows with names that start with Kumar
--
SELECT *
FROM dbo.MyTable
WHERE Name LIKE 'Kumar%'
GO

Now if you want to take it a bit further and query for any mis-spellings and fat-fingered entries like

1. Kumar
2. Kumaran
3. Kumar’an
4. Kumar”an
5. Kumar\an
6. etc

Rows with special characters

Then within the name you want to look for single-quotes and double quotes. But single-quote and double-quotes are part of T-SQL syntax. To over come that we use ESCAPE clause with LIKE clause.

--
-- Query rows with names that start with Kumar with possible single or double-quotes in the name
--
SELECT *
FROM dbo.MyTable
WHERE Name LIKE 'Kumar|\%' ESCAPE '|'
GO

SELECT *
FROM dbo.MyTable
WHERE Name LIKE 'Kumar%'
GO

SELECT *
FROM dbo.MyTable
WHERE Name LIKE 'Kumar\''%' ESCAPE'\'
GO

Result looks like this for each LIKE clause with ESCAPE clause added. The beauty here is that we could use any character as an ESCAPE clause.

Results with ESCAPE clause

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Change database name using T-SQL

Quick one today:

Occasionally, we’ll need to change database names either in lab or test environments. Like many repeated operations, this is a lot easier to perform this operation using T-SQL than UI.

Changing database name requires the database to be in single user mode, either by forcing SINGLE_USER mode or by not having any active connections to the database. To accommodate for this, we use ALTER DATABASE to set single user mode.

Dormant user, i.e. users connected to database, but no active queries running.

--
--  Set database in single user mode then rename it
--
ALTER DATABASE MyDB SET SINGLE_USER
GO
ALTER DATABASE MyDB MODIFY NAME = NewNameDB
GO
ALTER DATABASE MyDB SET MULTI_USER 
GO

 

Hope this helps,

_Sqltimes

Read Full Post »