CLR function performance difference same server different database

Question

Post reply

CLR function performance difference same server different database

DennisPost

SSCrazy

Points: 2691
More actions
May 29, 2015 at 6:34 am

#297255

Hi,
We have an CLR function that converts UTC times to local times.
On our live database (DB_Live) it performs much slower than on our test database (DB_Test) which was created from a backup of DB_Live and is in the same instance (SQLSvr1). Both databases files reside on the same LUN.
The following code only returns 1 row, which means the CLRs are identical, right?
SELECTname, content FROM DB_Live.sys.assembly_files WHERE name = 'ConvertUTCToTimeZone'
UNION
SELECTname, content FROM DB_Test.sys.assembly_files WHERE name = 'ConvertUTCToTimeZone'
This same CLR is used on a much more heavily used server (SQLSvr2) in 2 different databases, one of which is a transactional replication subscriber of DB_Live, the other database is our biggest and by far most active. The function runs very fast against these databases.
Both SQLSvr1 and SQLSvr2 only have default instances.
On SQLSvr1: (Each ran seperately)
USE DB_Live
GO
SELECTTOP 10000 dbo.ConvertUTCToTimeZone('20150529', 'W. Europe Standard Time')
FROMsys.all_columns c1, sys.all_columns c2
USE DB_Live -- correct
GO
SELECTTOP 10000 DB_test.dbo.ConvertUTCToTimeZone('20150529', 'W. Europe Standard Time')
FROMsys.all_columns c1, sys.all_columns c2
/*
Trial 2Trial 1
CLient processing time4414988
Total execution time7515563
Wait time on server replies31575
*/
SQLSvr1:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4302.0 (X64)
Feb 7 2014 17:23:24
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
DB_Live is in Full recovery mode and is published (3 push subscribers)
DB_Test is in Simple recovery mode.
There is also another CLR function that does the opposite of this one. It is also very slow.
How can I find the problem?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MadAdmin SSChampion Points: 11261 More actions · Answer 1

Can you compare the content column from assembly, between the 2 DB's?

When you do, are they exactly the same?

IIf they are the same, what happens when instead of doing use db_live go select db_test.dbo.CLR ,

you do, use db_test go select dbo.CLR

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

DennisPost SSCrazy Points: 2691 More actions · Answer 2

Thanks for taking the time to help!

Can you compare the content column from assembly, between the 2 DB's?
When you do, are they exactly the same?

The comparison is done with the UNION. If there was a difference I would get 2 rows. Because I only got 1 row back, the must be the same.

IIf they are the same, what happens when instead of doing use db_live go select db_test.dbo.CLR ,
you do, use db_test go select dbo.CLR

It runs very fast. The problem is in the Live db, not the Test db.

This is also very slow:

use

db_test

go

select db_live.dbo.CLR

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

... sorry... bad post removed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)