Category: SQL

DBA Utilities, SQL

How to find Table Name from Page ID in SQL Server
Recently, we had a performance issue in one of our testing environment and while we are troubleshooting the issue with Live Monitoring Query, we identified a locking scenario which is the root cause for the performance problem.

Now, we know there is a locking and the wait resource is showing as PAGE: 9:1:401776. What does it mean? How do we know which object is being locked? Let us try to answer these questions.

We know there is a LCK_M_IX which means an intent exclusive modification lock happened on the object “PAGE: 9:1:401776”. If we have a page number like this, then there is an easy way to get the object information with the help of a DBCC command – DBCC PAGE. 

DBCC TRACEON (3604);
DBCC PAGE (9, 1, 401776, 0);
DBCC TRACEOFF (3604);

The result of DBCC PAGE provides lots of information, however, we should specifically look at PAGE HEADER section for “Metadata: ObjectId “. It would provide us the ObjectId. Once we have the object_id, it is an easy to get the object name as below. Please note, we should select the database name while executing the command otherwise, we might end up no result or wrong information. 

Select db_name(9)
GO
use <dbname_from_previous_query>
GO
Select object_name(946818435)

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

CHARINDEX() in SQL Server
CHARINDEX() function returns the position of the string to search in the string to be searched.

Syntax: CHARINDEX ( search string, string to be searched, position search to start )

Remarks:

    1. CHARINDEX is a case insensitive search

    2. CHARINDEX always returns first occurrence of the search word from the search position is defined

    3. If search position is not defined, it returns the first occurrence position

Examples: 

Declare @SearchtoString varchar(100) = 'SQL Server is a database server. I love SQL Server.'
Declare @SearchString varchar(100) = 'Server'

SELECT CHARINDEX(@SearchString, @SearchtoString,4) AS MatchPosition;


--Another example
;With cte as
(
	Select @SearchtoString stringtobesearched, CHARINDEX(@SearchString, @SearchtoString, 0) StringPosition
	union all 
	Select stringtobesearched, CHARINDEX(@SearchString, stringtobesearched, StringPosition+1) From cte
	where StringPosition > 0
)
Select * From cte where StringPosition  0

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

sp_procoption in SQL Server
sp_procoption procedure set or clears a stored procedure for automatic execution that runs every time an instance of SQL Server is started.

Syntax: 

exec sp_procoption @ProcName = ['stored procedure name'], 
@OptionName = 'STARTUP', 
@OptionValue = [on|off]

ProcName – is the procedure name

OptionName – is the option name ( Currently, this does not really make sense as there is only one value permitted, which is ‘STARTUP’

OptionValue – is the value whether it is (ON or OFF)/(1 or 0)/(TRUE or FALSE)/(YES or NO)

Remarks:

    1. Startup procedure must be in the master database and owned by DBO

    2. It allows either SQL Procedures / CLR Procedures / Extended Procedures

    3. The procedure cannot contain INPUT or OUTPUT parameters

    4. It changes server configuration – “scan for startup procs”

    Once the server is rebooted, then the “run_value” also gets changed to 1.

    5. Execution of the stored procedures starts when all databases are recovered and the “Recovery is completed” message is logged at startup

    6. Requires sysadmin permission

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE() and SYSDATETIME() in SQL Server
CURRENT_TIMESTAMP – Returns the current system date and time.

GETDATE() – Returns the current system date and time.

GETUTCDATE() – Returns the current UTC date and time.

SYSDATETIME() – Returns the date and time.

Precisely, let us quickly see the output of each one as below. 

Select current_timestamp,getdate(),getutcdate(),sysdatetime()

Whats the difference between CURRENT_TIMESTAMP and GETDATE()?

CURRENT_TIMESTAMP is an ANSI SQL function whereas GETDATE is the T-SQL version of that same function.It is to be mentioned that CURRENT_TIMESTAMP is getting converted to GETDATE() when creating the object within SSMS. Both functions retrieve their value from the operating system in the same way. CURRENT_TIMESTAMP is the recommended usage because it is portable to any ANSI compliant database, where as GETDATE() is not. So, its more of a project requirement and for future demands or compatibility, few people would go with CURRENT_TIMESTAMP over GETDATE().

About performance – I have not seen any notable or any difference between thee two, it may be due to the fact that SQL-SERVER engine is converting CURRENT_TIMESTAMP to GETDATE(). 

Select CURRENT_TIMESTAMP,getdate() Where CURRENT_TIMESTAMP=getdate()

Whats the difference between GETDATE() and GETUTCDATE()?

The difference between GETDATE() and GETUTCDATE() is in timezone. GETUTCDATE() returns current time and date in UTC (Universal Time Coordinate) or GMT timezone. The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

Whats the difference between GETDATE() and SYSDATETIME()?

If you want more precision, yes SYSDATETIME() is your friend. SYSDATETIME() which has millisecond precision. It actually returns a datetime2(7) value, but the last four decimals are just noise to me. I hardly seen people are using it.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

LEFT() and RIGHT() functions in SQL Server
Extracts defined number of characters from the string provided, from left or right depending on LEFT() or RIGHT() function used.

That means Left(‘string’,3) returns “str”, 3 characters from the left side. Likewise, RIGHT does it from right side of the string.

A simple representation as below:

SELECT 'Latheesh' Fullname,Left('Latheesh', 4) AS LeftString,RIGHT('Latheesh', 4) AS RightString;

Another common use case is when we would like to add the ‘0’ characters prefixing depending on available numbers.For an example, if we need to represent digit like as always 6 characters irrespective the actual number by adding “0” (s) to the digit, we can easily achieve with RIGHT() function. 
Declare @mydigit int 

Set @mydigit = '34'
Select Right('000000'+cast(@mydigit as varchar(6)),6)

Set @mydigit = '100'
Select Right('000000'+cast(@mydigit as varchar(6)),6)

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!