.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Very poor performance from SQL/CLR

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
I have been developing with SQL/CLR recently, by creating stored procedures in C# and installing these in SQL Server 2008. It has gradually emerged that performance of the code when run in SQL Server is far far worse than when run as an ordinary .Net app. I have benchmark C# code that runs in 0.5 seconds in a console app, but takes around 8 or 9 seconds when installed and run as a stored proc in SQL Server 2008. We are running the 64-bit edition of SQL Server on 64-bit Vista. Now I just read this: "Managed code may incur additional overhead necessary to maintain the execution environment and perform security checks. This is the case, for example, when running inside SQL Server and numerous transitions from managed to native code are required (because SQL Server needs to do additional maintenance on thread-specific settings when moving out to native code and back). Consequently, extended stored procedures can significantly outperform managed code running inside SQL Server for cases in which there are frequent transitions between managed and native code." and this worries me, because our app uses a library that does a great deal of calling into some native API, so I know the code is transitioning to/from native code a great deal. I have to say that I am astonished at the degree of slow down, I need to find out if there is anything we can do to reduce this excessiv

View Complete Post

More Related Resource Links

SQL server poor performance - Urgent help

I have a windows 2003 Server Processor 880, 2.40GHz and 8Gb RamIts 64 bit  - SQL 2005It is on a SAN with Raid 5 on disksThere is one 50gb database mainly being used.The Server CPU is constantly up and down and Maxing out every few mins.There are runnable processes running and numerous suspended.Running Sp_who2 doesn't give much info and often shows no blocking or anything noticeable.Users are getting timeouts and not able to use the system.I looked at the memory allocation it was ;The Results were;name minimum maximum config_value run_valuemax server memory (MB) 16 2147483647 2147483647 2147483647name minimum maximum config_value run_valuemin server memory (MB) 0 2147483647 0 0 I have updated this and amended BOTH to be 6500, being max and min but not certain this is right.I can see in task manager in the performance window that it's using at 8gb, but if you look at the processes tab sqlserver.exe is only using 149 858k. I do not understand why sqlserver.exe is NOT using the available memory ? For example I ran DBCC checkdb and the only user on the server as it's out of hours and this task alone maxes out the CPU.I am also about to rebuild indexes and complete some other maintenance tasks.I'm very puzzled that simple tasks are maxing out the CPU but no memory is being used. My company is unable to complete ANY work…..this is urgent please help.Thanks

Poor performance on Sql 2005 vs. Sql 2000 - AGAIN!


I was hoping I wouldn't be another poster with performance issues after migrating to SQl 2005 from SQL 2000 but here I am.


I am in the process of testing out our databases on Sql Server 2005 for migration from SQL Server 2000 and there are certain portions of code that have been affected negatively. I have read thru many of the posts here and have tried out most of the recommendations. I will start out with things I've done and then provide the actual SQL.


1) I have rebuilt all indexes ( using the DBCC REINDEX using the table option).

2) Updated the db engine to latest hot fix (build 3239) that addresses speed related fixes.

3) I also ran sp_createstats using the 'fullscan' option to create stats on all columns of all tables (minus indexed columns)

4) Since nothing seemed to work, I even ran UPDATE STATICS with FULL SCAN on all tables even though I did not need it as the REBUILD woudl have created stats. But I was willing to try anything.


I have confirmed that the execution plans are different even though the data on both sql 2000 and sql 2005 are identical (i put a copy on 2005). The plans themselves are huge as the queries are huge. Here is the query.

SSAS Poor Performance


I have a performance issue with one Excel report that is connected to a SSAS 2008 cube that as about 120k records. The report works fine if we drop few attributes, but since we drop more attributes than usual (lets say, more than 10) for an ah-doc report, the report becomes very slow. In SQL Profiler there are a lot of "Calculate Non Empty Current" events, but the MDX query generated by Excel as the NON_EMPTY function included. In this MDX, there are also a lot of CROSSJOIN, one for each field and I don't know if this is this is correct.

Can someone help me? Thanks in advance.

CURSOR performance on multiple delete/insert and maybe poor table design


We have table documents and table documents_additonal_data, joined via key (docnum, doctype), important to know is that in documents_additonal_data we just have two more fileds valuetype and value (varchar(MAX)), and every record in doc can have variable 0-n valuetypes so there is no schema enforced in any way.

Now I've made cursor that needs to transfer history data from one older table to docs, must use cursor with order by clause because transaction_id, some bussines logic etc.  In short it takes values, have some IF logic on cursor variables, checks if there is already record for that document, some more IF logic on variables (with few simple selects) and calls insert procedure.

About insert procedure, it doesn't update values but reads current, stores old to XMLvalue (xml made with select) in storage table, deletes current, and inserts new data, reason for that is probably multiple indexes  (docs is primarly 'read from' table) and millions of records, update was slower probably because in add_data on update it usually needs to delete, insert, update in the same time. Insert procedure takes XML parameter for additional_data which is then translated and inserted via exec sp_xml_preparedocum

Poor Performance when filtering using Excel 2007 vs Excel 2010.


I've have a cube with a flat dimension called portfolio which is simpy a number.  Right now there are approx 15,000 members in the dimension. The cube is running on a 2008R2 box with approx 20GB RAM and is partioned along a different dimension (Month) and we are using Excel 2007/2010 to browse the cube. 

Today we noticed that if you include the portfolio dimension in the filter of pivot in 2007 Excel and try to select a portfolio to filter (drop down the portfolio list that is), the list of portfolios never returns and eventual hangs Excel.  When we try this same thing in Excel 2010, no problem.  Is a flat dimension with 15K member too large for excel 2007 to process?  Is the mdx different?  We are going to try to trace both and look for differences.  Anyone else notice this issue?


WCF named pipe ipc has poor performance


I created a couple of simple test client/server applications and use NetNamedPipeBinding to call a single remote function in a loop. No matter what I changed I could not get better than a 1ms round trip time for the function call. However if I run the client application with the Visual Studio 2010 profiler the function call executes 10 times faster and I get 0.1 ms round trip times for the function call.

I'm guessing this has to do with thread scheduling and that the profiler starts a high resolution timer that improves performance. I tried stating a MM timer, timeBeginEvent(1), and that helped slightly, however it is not possible to specify a time below 1ms. Is there any way for an application to emulate the effect of running under the profiler itself to boost WCF performance?




ASP.NET Best Practices for High Performance Applications

This article lists the techniques that you can use to maximize the performance of your ASP.NET applications. It provides common issues, design guidelines, and coding tips to build optimal and robust solutions.

Top .NET Performance Problems and how to avoid them

Every time I work with one of our .NET customers to help them with managing their application performance I come across the same problems as seen with other clients before: lots of ADO.NET queries, many hidden exceptions in core or 3rd party .NET libraries, slow 3rd party components, inefficient custom code

High-Performance .NET Application Development & Architecture

It has always been a goal of project architects to plan an effective strategy from the ground up in regards to an new application. All relevant factors are taken into consideration with respect to the application, from its design and layout to a functional website infrastructure. Pre-.NET strategies and design guidelines still effective now were developed with Microsoft's DNA (Distributed interNet Application) platform. This model successfully served the purpose of architecting N(any number of)-Tier (levels) applications. In its basic sense, as in most robust, distributed applications, you'll architect 3 main layers or Tiers: presentation, business rules and data access.

How to improve the performance of ASP.NET MVC web applications

It is an in depth analysis of a Digg-like site and how it went from serving (on a test machine) 6 req/sec to 390 req/sec.

The biggest gain, 74 req/sec to 390 req/sec happened when he introduced data caching and another 25 req/sec where gained when he introduced the compilation of LINQ queries.

High Performance ASP.NET - Speeding Up ASP.NET Pages

have been writing a series of blog posts, which I have named High Performance ASP.NET Websites Made Easy! There is no rhyme or reason to the order of these posts and certainly can be read in any order:

Developing High Performance and Scalable ASP.NET Websites
Avoid Chatty Interfaces Between the Tiers in Your ASP.NET Web Application
ASP.NET Page Profiling - Page Tracing - High Performance and Scalable ASP.NET Websites Made Easy
Web Applications: N-Tier vs. N-Layer - Benefits and Trade-Offs
Query Analyzer - Sql Server Database Indexes and Execution Plans

Improving Application Performance in .Net

Application performance has always been a concern for Web Application developers. This article contains guidelines and tips for maximizing application performance in ASP.NET.

Tips to Improve Your ASP.NET Web site performance

ASP.NET has been developed with lots of features which makes developer life easy. Start from ViewState, PostBack, Server Controls , User Controls and etc. All of them can make your web development experience fun and easy, but improper use of them can cause your site performance slowdown. Most of the controls generate overhead to the client and server. Therefore in this article, I I will list out about things you can do and don't to improve your ASP.NET website performance.

10 Tips for Writing High-Performance Web Applications

Writing a Web application with ASP.NET is unbelievably easy. So easy, many developers don't take the time to structure their applications for great performance. In this article, I'm going to present 10 tips for writing high-performance Web apps. I'm not limiting my comments to ASP.NET applications because they are just one subset of Web applications. This article won't be the definitive guide for performance-tuning Web applications-an entire book could easily be devoted to that. Instead, think of this as a good place to start.

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.

Best Coding Techniques To Improve Performance for SharePoint Applications

As more developers write custom code by using the SharePoint Object Model, they encounter common issues that can affect application performance.

The following areas reflect the main issues encountered by developers as they write custom code by using the SharePoint object model:

§ Disposing of SharePoint objects

§ Caching data and objects

§ Writing code that is scalable

LINQ to SQL Performance Considerations

As developers we have a wide array of tools at our disposal. One of the most time consuming, but most beneficial tasks we must undertake is to determine which of those tools to use for a given situation.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend