Using SQL CLR to execute web services

Question

Post reply

Using SQL CLR to execute web services

Viewing 8 posts - 31 through 37 (of 37 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

Robyn Page and Phil Factor published a CLR cribsheet at

http://www.simple-talk.com/dotnet/performance/net-performance-cribsheet/

It also mentiones the ms CLRProfiler.

You can download "CLR Profiler for the .NET Framework 2.0" at http://www.microsoft.com/downloads/details.aspx?FamilyId=A362781C-3870-43BE-8926-862B40AA0CD0&displaylang=en

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

ALZDBA (5/19/2008)
Robyn Page and Phil Factor published a CLR cribsheet at
http://www.simple-talk.com/dotnet/performance/net-performance-cribsheet/
It also mentiones the ms CLRProfiler.
You can download "CLR Profiler for the .NET Framework 2.0" at http://www.microsoft.com/downloads/details.aspx?FamilyId=A362781C-3870-43BE-8926-862B40AA0CD0&displaylang=en

Heh... that's a lot of stuff to remember... especially about how to use heap memory so garbage collection will automatically clear it (failure to do so will make for nasy little "memory leaks" that will crush the server over time).

I guess that's why part of my Mantra is "do as much as you can in T-SQL instead of CLR's" and "you can do more than you think in T-SQL, you probably don't need a CLR". 😉 Obvious exceptions to that include RegEx and that's about it.

--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)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

Jeff Moden (5/19/2008)

...I guess that's why part of my Mantra is "do as much as you can in T-SQL instead of CLR's" and "you can do more than you think in T-SQL, you probably don't need a CLR". 😉 Obvious exceptions to that include RegEx and that's about it...

Indeed, I'm 100 % with you.

But if you opened Pandoras box, I think you'd be happy with the reference. 😎

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

I skipped some posts, but I can see calling a web service if you want to move data into SQL Server.

But not as part of a query. If you need the query, either set up a real linked server and deal with the issues with that, or pull the stuff together on the client. That's what it's really there for, to allow async stuff to be put onto one page.

The problems I can see is that if the web service is down or slow, your server can get bogged down waiting for the web service to return, hold locks, blocks, etc.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

ALZDBA (5/19/2008)

Jeff Moden (5/19/2008)
...I guess that's why part of my Mantra is "do as much as you can in T-SQL instead of CLR's" and "you can do more than you think in T-SQL, you probably don't need a CLR". 😉 Obvious exceptions to that include RegEx and that's about it...
Indeed, I'm 100 % with you.
But if you opened Pandoras box, I think you'd be happy with the reference. 😎

Is that kind of like "be sure to pack warm for your trip to Antarctica?" :w00t:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

Jeff Moden (5/19/2008)
Heh... that's a lot of stuff to remember... especially about how to use heap memory so garbage collection will automatically clear it (failure to do so will make for nasy little "memory leaks" that will crush the server over time).
I guess that's why part of my Mantra is "do as much as you can in T-SQL instead of CLR's" and "you can do more than you think in T-SQL, you probably don't need a CLR". 😉 Obvious exceptions to that include RegEx and that's about it.

Memory leaks?? :crying:

I honestly hadn't thought about that...

Thanks Jeff for bringing it up...

Does anyone know how memory leaks can be monitored?

(through SQL Server 2005 queries or otherwise)

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Thomas SNyder-379443 SSC Enthusiast Points: 159 More actions · Answer 7

Marios Philippopoulos (5/20/2008)

Jeff Moden (5/19/2008)
Does anyone know how memory leaks can be monitored?
(through SQL Server 2005 queries or otherwise)

I have used the following check, not feature rich but better than nothing. also it describes how the -g switch is used depending on you RAM, (very important if your not running 64bit)

http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx

It would be nice to use the CLRProfiler if could be used to analyze SQLCLR.

has anyone has been able to get this to work with SQLCLR?

-Tom

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 8

Thomas SNyder (5/20/2008)I have used the following check, not feature rich but better than nothing. also it describes how the -g switch is used depending on you RAM, (very important if your not running 64bit)
http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx
It would be nice to use the CLRProfiler if could be used to analyze SQLCLR.
has anyone has been able to get this to work with SQLCLR?
-Tom

I ran the following query - from link you mentioned - and got 3 rows of data. Should I sum them all up to get the total memory taken up by SQLCLR?

select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb
from sys.dm_os_memory_clerks
where type = 'MEMORYCLERK_SQLCLR'

Output:

--------------------

5688
1832
0

Would a memory leak manifest itself in a gradual increase of these values over time?

Is there another way to assess this?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]