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

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

Performance perspective of Lookup function in SQL Server 2008 R2

Posted By:      Posted Date: September 13, 2010    Points: 0   Category :Sql Server
Hi, Recently I had presented a session on SSRS 2008 R2 enhancements. As part of that I explained about LOOKUP function and its way of working. There was a question while presenting this feature. From performance perspective, what are the advantages of LOOKUP function over conventional implementation of JOINING the lookup tables in stored procedure itself. I tried explaining that, it enables to make best utilization of resources in scaleout environment because the logic of JOIN evaluation has been shifted to Report Server than a database server. Some how the participants were not convinced. I would request experts to comment on this and help me in understanding whether my evaluation is correct or is there anything I am missing over here during my explanation. Please help.Phani Note: Please mark the post as answered if it answers your question.

View Complete Post

More Related Resource Links

SQL Server 2008 performance: Decision to spend budget to upgrade to enterprise edition

Hi, Currently, we use SQL Server 2008 standard edition. We are planning to improve the performance of our application. The application is built by 3<sup>rd</sup> party and we can’t change/modify database objects and they seem to be designed fine. The options are spending our budget to upgrade to sql server hardware, or upgrade to SQL Server Enterprise edition. I am a bit confused over the benefits of SQL Server Enterprise edition. The following chart suggests that SQL EE includes “Parallel Index Operations” and “Enhanced Read-ahead and Scan”: http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx  How significant are “Parallel Index Operations” and “Enhanced Read-ahead and Scan”? Considering the fact that new hardware is cheaper than sql enterprise license, Should I spend budget to upgrade enterprise edition instead of buying as server with faster CPU and RAM? Thank you, Max

Training on Performance Point 2010 and SQL Server 2008 BIDS



Me and my team are developing a POC in Performance Point 2010 using SSASCubes,we are planning to recommend trainign for the team  but i had a couple of questions.

1.The Team is totally new to this technology as we dont know what is .NET,MOSS 2007 and SQL Server 2008 R2.

2.Does working in PPS 2010 requires basics of Sharepoint 2010 and .NET,if yes then what are the areas we need to look into it before proceeding with PPS 2010 training


Can anybody kindly guide us with the same




lookup function in SQL 2008



I would like to insert information from one field in a table (Period Master) into all rows in one column in another table (transaction table). The Periodmaster looks like this   

Name      Period
Current     2010-11
Previous    2010-10
Next       2010-12

Is it possible to insert the current Period "2010-11" into a column in an transaction table. I woudl like to do this because the transaction table dont have the period. Is there any Lookup function in SQL that i could use and how should i write that to work or is it some other function that i could use?

Best regards


Arne Olsson

SQL Server 2008 - why is performance WORSE with xml index?

It's crazy, but query performance is about 50% WORSE after I add a primary XML index to my xml field. 

Here's what I'm doing. I have a table containing an XML field ActivityStepLog (contains LogData, XML, UNTYPED)

I generate sample data to insert into this table by running the following
INSERT INTO dbo.ActivityStepLog (
 LogGUID = newid()
 ,LogContextID = newid()
 ,LogTypeID = 2
 ,LogSourceName = 'test test test'
 ,LogContent = (SELECT top 1 * FROM ##SampleData SampleData1 where DecisionLogID = SampleData.DecisionLogID FOR XML AUTO, ELEMENTS, ROOT('BusinessRule') )
 ,LogDate = current_timestamp
 ,CreateDate = current_timestamp
 ,CreatedBy = 'test create by'
from ##SampleData SampleData
SampleData has 100,000 rows, I run it in a loop 5 times so end up with 500,000 rows. The LogContent field has data such as the following:

WCF service IIS7 Server 2008 custom User Name Authentication performance



I have WCF service. Nothing special. Everithing from "best-practice".

  • WCF Service hosted on Server2008(64)\IIS7
  • Application pool - integrated\.NET 4.0
  • binding: basicHttpBinding; security:TransportWithMessageCredential
  • transport clientCredentialType="None"
  • message clientCredentialType="UserName
  • custom class inherited from UserNamePasswordValidator provide simple validation.

Everything works as designed. But I have a performance issue.

I traced and logged the server and WCF class and figured out that there is no new "http request" in process until previous one passed "Verification". It looks like concurrent calls do not have any sense because UserName Password Validator may serve them one by one only.

It can't be a true. I hope I just missed some settings.

I will appreciate any help.


2008 Report Server performance


We are in the process of migrating our SSRS 2005 reports onto a new SSRS 2008 server.

We are seeing execution times of the same reports on each server considerably slower on the new server. 

A sample large report I've used for benchmarking by scheduling it to run hourly over a day typically runs in 4-5 minutes on the old server and in 20-25 minutes on the new server.

Wheras is I run the query itself on each server through management studio they both do it in about 3 minutes.

This suggests to me that the problem is prbably ion the configuration of the report server. There is no hardware or operating system difference between the two that could account for the differencem, if anything the new server should be faster.

I know in 2005 there where IIS tweaks that improved performance but assume these no longer apply in 2008 as IIS is not used.

Can anyone suggest articles on what settings etc I should be looking at to resolve this?



Bruce < a href="http://homepages.woosh.co.nz/bf/">http://homepages.woosh.co.nz/bf/

CLR-based Function using System.Drawing throwing 6522 after update of server to 2008 SP2


I wrote a DLL that uses system.drawing to shrink/resize images stored in SQL field.  Worked yesterday.  Today, all references to that dll (via SQL function field) blow up with this error. 


Msg 6522, Level 16, State 1, Line 2

A .NET Framework error occurred during execution of user-defined routine or aggregate "ImageScaler":

System.IO.FileLoadException: Could not load file or assembly 'System.Drawing, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.


at UserDefinedFunctions.ImageScaler(SqlBytes inImageBytes, Double ImgHeight, Double ImgWidth)


I believe this KB article explains the situation:


How to install SQL Server 2008 on VMWare for optimal performance

We have SQL Server 2008 Standard licensed for a single processor. We wish to install it on our VMWare platform. Obviously we intend limit the virtual machine to one virtual processor in order to comply with the licence.

As we understand it, a virtual processor is never distributed over more than one physical processor, but will have access to multiple cores on the single processor. If so, that sounds ideal both for performance and for complying with the licence. However...

The potential problem is that our SQL guru insists that SQL Server needs to be able to see that the CPU has 4 cores in order for it to allocate the work efficiently. VMWare does not seem to do this. It presents the virtual CPU as having one core and presumably manages the physical cores itself, transparently to SQL Server.

Is this really a problem? Does it matter that SQL Server can't see the cores and manage them itself? Will this really impact performance, or will VMWare manage the workload across the cores just as efficiently as SQL Server would on a physical server?

If it is going to affect performance, then how do we work around this?

It sounds like we probably need to provide SQL Server with one virtual processor that has distinct virtual cores (which may or may not have any relationship to the physical cores) in a way that the scheduler can use bu

SQL Server 2008 CLR Table-Valued function works exactly twice, then fails unless assembly reloaded.


Very simple TVF UDF uses WebRequest and HttpWebReponse, passing the response stream off to custom class that implements IEnumerable. First invocation has a good delay (seen many other posters on this issue), second invocation is lightening fast, then all calls after that produce this error:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "tvf_OneMinuteBars":
System.Net.WebException: The operation has timed out
   at System.Net.HttpWebRequest.GetResponse()

Now, I've tried all manner of settings on the webrequest: KeepAlive, Timeout (currently 15 secs), Proxy = null. But I just can't figure it out. What is really a bummer is that I can debug invoke this against a local SQL Server as many times as I like. But if I deploy (in VS) to two different servers, or if I manually copy the assembly to the servers and do the CREATE ASSEMBLY / CREATE FUNCTION manually, it always times out after the second invocation.

Here is the tricky part: the object calling GetResponse calls HttpWebResponse.GetResponseStream(), storing the reference in a property. Then the object reference passed to the constructor of a custom class implementing IEnumerable, which does the right things with the response stream. Super fast, n

Sql Server 2008 Tutorials

This Link Provides total Sql Server 2008 Tutorials

Seven reasons to care about SQL Server 2008 R2

Microsoft begins to fully realize its vision of SQL Server as an information platform and not "just" a database. Hence the main theme for this release -- at least according to Microsoft -- is self-service BI. The PowerPivot plug-ins for Excel 2010 and SharePoint 2010 are easily going to make the biggest splash of all the new features, not least because they're the most complete. But then, SQL Server 2008 R2 isn't strictly necessary for PowerPivot for Excel, which works with plenty of other data sources.

SQL Server Performance

Improving .NET Application Performance and Scalability
SQL: Scale Up vs. Scale Out
Stored Procedures
Execution Plans
Deployment Considerations

Error upgrading SQL Server 2008


i was normally using VS2005 and SQL server 2005...

i upgraded to SQL Server 2008... and then...

i could not build my project anymore... because of this error:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
Exception Details: System.IO.FileNotFoundException: The specified module could not be found. (Exception from HRESULT: 0x8007007E)
Source Error: 

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly.


I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

 I have two SQL Server instances installed.

 1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?

sql server 2008 adventureworks database


dear Friends

For my MCTS 70 -433 examination, I recently installed sql server 2008 and installed the sample databases too. but my issue is, in that book they have mentioned a database called "AdventureWorks2008" and the nice thing is I don't have a such a database with me in my sql server 2008. 

And the query is 

-- MERGE Statement
USE AdventureWorks2008
MERGE INTO	Sales.SalesOrderDetailHistory AS SODH
USING Sales.SalesOrderDetail AS SOD
ON SODH.SalesOrderID = SOD.SalesOrderID
AND SODH.SalesOrderDetailID = SOD.SalesOrderDetailID
INSERT (LineTotal, SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty
, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
, rowguid, ModifiedDate, Cancelled)
VALUES(LineTotal, SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty
, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
, rowguid, ModifiedDate, DEFAULT)
UPDATE SET SOHD.Cancelled = 'True'
OUTPUT $action , inserted.* ;	

Please help me to overcome this issue and let me know where can I download that database too.

Thanking you

happy Coding

PostGreSQL to sql server 2008?



I am new to POSTGREsql.

I have a database built in POSTGresql and I have to migrate it to SQL SERVER 2008.

How can I do that?



Word Automation Issue in Windows Server 2008 Hosting



The problem I am posting here is that I was facing nearly 2 weeks around. Any body comes with this stuff please help.

Word Automation in sample ASP.NET(C#) application.

I am using Microsoft.Office.Inetrop.Word Assembly for automation. Here I am reading a XXX.dot template file and fill the contents with dynamic data.

When i am executing my code in localhost:someportnumber the automation is working fine and I could get expected result and when I am hosting in my inetmgr(Windows XP is my OS) it is also working fine.

But the problem is that when I am hosting in my production server(Windows Server 2008 Standard Edition) I am not able to perform automation and results in the following error.

Data: System.Collections.ListDictionaryInternal
Message: Word has encountered a problem.
Source: Microsoft Word

The code gets failed in the following line:

ApplicationClass wordApp = new Microsoft.Office.Interop.Word.ApplicationClass();

Document wordDoc = wordApp.Documents.Add(ref oTemplate, ref oFalse, ref oMissing, ref oMissing); // Error in this line

I cannot able to proceed further. Can anybody please help me in solving this issue?

Thank you.

With Regards,


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