Query performance - SQL Server

I didn't get your point. Are you talking about to reduce disk I/O?

This is my DB IO

Database Name      Number of Read      Number of Writes
TESTDB                         6569                        9014

This is disk IO

total_num_of_writes
19177

total_num_of_bytes_written      
296824320      

total_num_of_reads
8477

total_num_of_bytes_read      
1187894784      

total_size_on_disk_bytes
2615869440

Pls find the attached code below. In above screenshot the predicate is showing in the clustered index scan operator is DomainName = SUSER_SNAME()

ALTER function [dbo].[_FSD] ()
returns uniqueidentifier
as
begin
	declare @userGuid uniqueidentifier
	if (is_member('ReaderRole') | is_member('db_owner')) = 1
	begin
		select @userGuid = cast(context_info() as uniqueidentifier)
	end

	if @userGuid is null
	begin
		select @userGuid = s.SystemUserId
			from TBl_NAME s
			where s.DomainName = SUSER_SNAME()	
	end
	return @userGuid
end
GO

Select allOpen in new window

How many rows in your TBL_NAME? Depending on the table size a table scan is okay. Does an index with DomainName as first column exists? If not create one. When still the clustered index is used compare it with using a query index hint.

btw, this is a server semantical error: a domain name is something different.  When your Windows user account is e.g. first.last@domain.tld (same as domain.tld\first.last), then the domain name is domain and the full qualified domain name is domain.tld.SUSER_SNAME() returns the login name. This can be a Windows account name (SAM) or the name of a SQL Server login.

We have already created non-clustered index on column:DomainName and this column datatype is nvarchar(1024).

The total number of rows in tbl_name are 246. The above query is taking around 36 seconds for 215 records (It's very high).

we have 256 GB RAM on server. Is there any alternative solution to reduce the execution time?

Then the an index scan is not faster, too few rows. Look at statistics.

Have you tested the second querie stand-alone, without the function? Functions are often a performance killer.

Also you don't need an NVARCHAR(1024). Imho the max length is 127 characters.

btw, as you have the domain name normally as domain\user the string comparision needs more time. Here an reverse lookup can save you time. Thus test:

Create a persisted computed column as REVERSE(DomainName) and index this field and try using WHERE DomainName = REVERSE(SUSER_SNAME()).

Can you share sql query for statistics to check results for your review

>>" The above query is taking around 36 seconds for 215 records (It's very high). "

the function code supplied at comment ID: 41533655 takes 36 seconds?
or,
the query (which you haven't supplied yet) takes 36 seconds?

Have you examined, what really takes so long? I'd say it's rather the context_info() calls failing to give you an info and thus you do the final query. Do you ever SET CONTEXT_INFO in your sessions?

In which context are you using this _FSD function? In which queries is it used in which way? Consider SET CONTEXT_INFO in this function to really make use of the first part.
Also: For which users does this run slow? Are they having the ReaderRole or are db owners? Or are they other users?

Bye, Olaf.

Hi Ste5an, I am not able to alter column to Create a persisted computed column as REVERSE(DomainName). Is the below syntax is correct?

alter TABLE [dbo].[Tbl_Name] alter column [DomainName] As REVERSE(DomainName,[nvarchar](127)) NOT NULL

Hi Paul; the above UDF is taking 36 sec.

Hi Olaf, Do I need to set SET CONTEXT_INFO on above UDF?

I said create, thus add a new column:

ALTER TABLE .. ADD [ReverseDomainName]  AS (REVERSE([DomainName])) PERSISTED NOT NULL;

Select allOpen in new window

btw, how many users do you have? The is_member() call could be also the reason.

around 200 users

Hi Ste5an,
1. I have added new column using above statement and created non-clustered index on new column
2. Modified UDF where condition as a DomainName = REVERSE(SUSER_SNAME())

Now query is not returning any rows (0 rows), Can you check where condition once again.

Data example for domainname columns:
Domainname\username that is company\AAA, Company\BBB....

Please post a concise and complete example. Include table DDL and sample data INSERT statements and your CREEATE FUNCTION and a usage sample.

It's Monday. No mindreading today.

>Do I need to set SET CONTEXT_INFO on above UDF?
Only if you ever SET CONTEXT_INFO the context_info() will return that for the same session, otherwise that part of the UDF will always just return NULL and you do the more expensive select of the s.SystemUserId.

So no matter what part of the code filled the @userGuid variable, it would perhaps pay to
SET CONTEXT_INFO TO @userGuid in the end, so next run context_info() will return that.

Bye, Olaf.

--