.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

SQl 2008 execution plan problem

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
Hi We are in the middle of upgrading a PeopleSoft database from SQL 2000 to SQL 2008. After the upgrade a select from a view takes 8 min compared to the 1 second it took in SQL 2000. I can see that the execution plan is the problem. What is causing 2008 to be worse than 2000. Is there a patch to fix this 2008 problem or should we stick with 2000 for now. Fixing the system with new indexes and trouble shooting the query is out off the quetion as the people lost there faith in SQL as a system and is saying if this happens now it will keep on happening with other dev and to much time is spent on trouble shooting the SQL 2008 shortcomings.

View Complete Post

More Related Resource Links

SQl 2008 execution plan problem

Hi We are in the middle of upgrading a PeopleSoft database from SQL 2000 to SQL 2008. After the upgrade a select from a view takes 8 min compared to the 1 second it took in SQL 2000. I can see that the execution plan is the problem. What is causing 2008 to be worse than 2000. Is there a patch to fix this 2008 problem or should we stick with 2000 for now. Fixing the system with new indexes and trouble shooting the query is out off the quetion as the people lost there faith in SQL as a system and is saying if this happens now it will keep on happening with other dev and to much time is spent on trouble shooting the SQL 2008 shortcomings.

Query execution plan problem

Hi, I have encountered a problem with a query execution plan on MS SQL Server 2008. It is a simple query on a single table. The table has a primary key RNUM (number(10)) with a clustered index. The query is executed via ODBC using fast forward cursors and is constructed like this: select [field_list_here] from table_name where RNUM>@P and TYPE='A' order by RNUM. The field TYPE has 2 possible values and is not indexed. The table has about 2 000 000 rows of static data (only reads, no inserts and updates). For some time my query executes using the efficient query execution plan. Below a copy from Management Studio from an ad-hoc query: SELECT (0%) <- Clustered Index Seek  (100%) But after 2 days of executing other type of queries SQL Server starts to use other execution plan (live copy):                             Fetch query (0%) <- Clustered Index Seek [CWT_PrimaryKey] (0%)                            |                           \/ Fast forward (0%) <- Population quer

SSIS 2008: Problem reading dynamic file during execution



I have a SSIS 2008 project that reads a bunch of files from a file (always the same file) from Machine A.
My package is running on Machine B and this is where the information is being imported to, my Reporting DB (sql 2008) is also located in Machine B. 
I have a dynamic log file that stores the execution errors every time the package is executed and its name is made up of the timestamp of when package starts executing. The "DelayValidation" option for this connection is set to "TRUE". When i execute my Package from Visual Studio 2008, it runs without a problem.
The problem is when i deploy my package into SQL Server 2008 and try running it through DTEXEC. I always get the following error:


Error: 2010-11-03 14:57:29.06
   Code: 0xC001401E
   Source: MyImportPackage Connection manager "ImportLog"
   Description: The file name "\\ip-address\d$\Logs\ImportManager\20101103_145726.txt" specified in the connection was not valid.

Crystal Report vs 2003 convert to vs 2008 parameter problem


Hi friend,

I have a project develop with Visual Studio 2003, when i convert the project to Visual Studio 2005 is work well. But when i convert to Visual Studio 2008, the crystal report when have pass parameter will prompt the parameter field to re-type then show the report.

But in this converted project i create a new report and pass the parameter is ok. That means i wan re-do all report @.@??

Does someone can help me solve this problem??

Thank you

Problem in Installation of SQL Server 2008


Dear All,

I have Sqlserver2008 setup.

It is work in wndows Xp.

But I have to Install it on Windows 7 ,during installation process it requires some patch and I am unable to get it.

Plz some buddy can help the same.


Rakesh gupta

Problem opening dotnet 1.1 projet on 2008 web server


 We recently upgraded to a 2008 development web server and I installed all the stuff to get dot net 1.1 app working on the server.  I can host them and they work.  My problem is that when I try to open one in VS 2003 I get the following error.  I set up the web server the same as my previous one, and I think a long time  ago I got this error when we set up current one but do not remember how to resolve it.  So I moved my project files from the old server to the new server and then I get these errors. 


The web Server reported the following error when attempting to create or open the Web Project located at the following url: Http://localhost:/MobTracker. 'Http/1.1 500 server error

Problem adding .NET 3.5.1 feature on Server 2008 R2

Having an issue with a 2008 R2 64-bit Server becoming unresponsive/locking up when trying to activate the .NET 3.5.1 feature.  Did not have any previous version of .NET installed but ran the .NET Cleanup utility and completed successfully but still have same issue.  It installed .NET 4 via the Microsoft Updates OK but still hangs part way through the 3.5.1 install via the Server Manager/Add Features.  Any ideas?  Thanks!

Deploying problem with SQL Express 2008 R2

Scientific software package works fine with SQL Express 2008 R2 on developing (mother) PC and Admin login mode, but difficulties are found elsewhere during installing and deploying on the client PCs.  We have used from year 2000 BDE (Borland Database Engine), which worked fine until now, but recently customers had been installing problems with Vista, Windows 7 and the local administration rights or permissions.   Thus I thought to test NT AUTHORITY\NETWORK SERVICE authentication, as this system is hopefully working again decades with coming new Windows versions. As not need to touch databases, ADO and authentication matters during last decade, I’m newbie with those topics.  Worked now few days with this problem, installed and unistalled SQL servers according to instructions, tested different connecting strings and now cannot found correction.  Solution is maybe and hopefully simple.   Attached is log data cut from (old and many times corrected) the main mother XP platform in Admin and Client (Guest) modes.  Same tests are done also with fresh XP installation on VirtualBox.  Used connecting strings are presented inside log files:           Main mother XP at Admin login:   2010-08-20 08:24:45.82 Server      Server local connection provider is ready to accept connection on [ \\.\pip

SQL 2008 Installation problem (Could not fix registry key)

Hello, I want to install SQL 2008 client tools on a Win 2008 machine. The machine already contains an instance of SQL 2005 Express which I don't want to unistall. The problem is that the installation wizard complains about Consistency Validation for SQL registry keys. The log shows the following: 2010-08-26 10:14:46 Slp: Could not fix registry key HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\ConnectTo. 2010-08-26 10:14:46 Slp: System.NullReferenceException: Referencia a objeto no establecida como instancia de un objeto. 2010-08-26 10:14:46 Slp:    en Microsoft.SqlServer.Configuration.FixSqlRegistryKey.Program.ContainsInheritedAce(Byte[] securityDescriptor) 2010-08-26 10:14:46 Slp:    en Microsoft.SqlServer.Configuration.FixSqlRegistryKey.Program.FixRegistryKey(String keyName) 2010-08-26 10:14:46 Slp:    en Microsoft.SqlServer.Configuration.FixSqlRegistryKey.Program.FixRegistryKeyAndSubKeys(RegistryKey key) I have created the "ConnecTo" key, which didn't even exist. I have also tried to set premissions to HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer to "Everyone/Full Control" but the installation fails over and over. Is there anything else I could try to get the SQL tools installed? Thanks in advance.

Problem connecting to SQL Server 2008 R2 Express from Access 2007

I have a problem with SQL Server 2008 R2 Express with MS Access 2007. I am running Windows 7 with 8 gigs memory. I installed SQL Server 2008 R2 Express. Do I now need to install SP1 because I am running with Windows 7? I can open SQL Server using the Management Screen but I cannot connect to MS Access 2007 using SQL Server Express as the database. When trying to connect to SQL Server from Access 2007, I keep getting the message that SQL Server 2005 cannot be found. It will not allow me to sign onto sql server 2008 stating that it cannot be found. Do I have to now run the SQL Server 2008 R2 Express SP1? Is there an upgrade to MS Access 2007 which will allow me to use SQL Server 2008 R2 Express?

Improper execution plan with scalar function

Run the following sql in an empty db(in my case, sql 2008): create table t (id int) go declare @i int set @i = 1 while @i <= 1000 begin insert t values (@i) set @i = @i + 1 end go create function f(@i int) returns int as begin return (select count(*) - 10000+@i from t) end go Then run the following sql a, it's slow, used 6 seconds, yet sql b is very fast. Their execution plans are totally different. Can sql a run as fast as sql b? If not, why? -- sql a declare @i int set @i = 500 select * from t where id = (select dbo.f(@i)) -- sql b select * from t where id = (select dbo.f(500)) Thanks!

Cannot create maintenance plan in SQL 2008 SP1

I receive the following error trying to create a maintenance plan in SQL 2008 SP1 on a Windows 2008/R2 server: The action you attempted to perform on a remote instance of SQL Server has failed because the action requires a SQL Server component that is not installed on the remote computer. To proceed, install Microsoft SQL Server Management Tools on the remote computer, and then try again. The components that installed are Database Engine Services, Reporting Services, Client Tools Connectivity, Management Tools Basic/Complete, and SQL Client Connectivity SDK.  I have another independent SQL 2008 SP1 server running on the same platform with the same features installed and I have no problem creating maintenance plans there.  However, it does have IIS as it was upgraded from a SQL 2005 server which required IIS for reporting services while SQL 2008 does not. I'm familiar with http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/06c80a78-a877-4a48-a032-b0aef78a598c but that doesn't seem to apply as this was a new SQL 2008 installation on a freshly installed OS.  I've seen several other postings of this on the web but they all appear specific to SQL 2005. What else could be missing? Thanks.

CPU killer when showing execution plan when having a missing index

Hi you all! Do you have problems with flickering and CPU getting high when you show the execution plan for a SQL Statement AND there is a missing index suggestion? See related connect id: https://connect.microsoft.com/SQLServer/feedback/details/360457/hovering-mouse-over-execution-plans-query-line-is-cpu-killer I really hope someone from Microsoft can take a look at this a give us a status update, over TWO years since I reported it and still an issue. Have you experienced the same? Please vote the bug up to "raise" the flag.Best regards Arild Røksund, Norwegian developer (SQL & .net), Omega AS (please Vote as helpful if helpful)

Compatibility problem: an "sql server 2008 R2" db backup cannot be restored to an "sql server 2008"

To verify if the "2008 R2" version can be restored back to a "2008" dbserver I make an experiment:  construct a database on R2 version ( version : 10.50.xxx )  and backup it; then I try to restore the db backup to a "sql server 2008" db-server ( version : 10.0.xxx ) but get failed. Error message is as shown: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.   Originally I want to upgrade a "2008" server to "2008 r2" , but this problem withdraws me: if I do so , I am afraid all the existing clientuser's dbs cannot be backed up to themselves if they don't have "2008 r2" locally:   if a database is backed up under "2008 r2" environment whereas client user only has "2008" db server, does it mean he cannot restore it anymore?   So I wonder if there is an approach that helps me to create a "backward-compatible" database; when creating database on wizard there is an option to choose "compatibility level" but I don't think it meets my requirement..    Anyone can help me on this ??    

Execution Plan in Activity Monitor (Recent Expensive queries)

If we see the execution plan of a query in Activity Monitor > Recent Expensive Queries is it Estimated Execution Plan or Actual Execution Plan ?

Sharepoint 2003 + Windows Terminal Server 2008 + Office 2007 problem?

Hi, We have a customer where some users runs Windows Terminal Server 2008 with Office 2007. They open documents from a Sharepoint 2003 site. When they open an excel sheet they get a button in the document which says "Server workbook: Click edit workbook to edit this workbook" and there is a button: "edit workbook" The users clicks "edit workbook" but get an error message saying there is no connection to the server.  They are able to edit the document, but not save it back to the sharepoint site. Most other users work on their local computer with Office 2003. Here, everything works fine and there are no button/message stating its a server workbook.   Is this caused due to some compatability issues between Sharepoint 2003 and Office 2007?   Best regards, Thomas Hansen ErgoGroup AS

Is the saved execution plan for a stored procedure really relevant?

The execution plan of a stored proc is saved for future use in the compilation process - but sometime in the future it could be irrelevant... for example: my assumption is that the heuristics of the optimizer are for example available indices and table size. So: 1. If an index is dropped then the execution plan can't be done... BUT when I checked this case, I created a simple stored proc with a simple select and a where clause, and ran it - the execution plan (which i assume is the saved one) contained an index seek. Now I dropped the index and got a table scan instead - so what happened? the saved execution plan (which was there from the time I run "create proc") was NOT performed unless the actual execution plan checks for existence of index (obviously 2. If an index is added then we'd have a better execution plan than the compiled one. BUT - when checking this.... before adding the index I had a table scan... after adding the index i still expected a table scan (because of the saved execution plan)... BUT i got an index scan - THIS LEADS ME TO THE CONCLUSION THAT after adding the index the proc was automatically recompiled by the sql server (I never read such a thing anywhere - so either i misundertand the entire theory or my conclusion is right). 3. Table size... first - is table size really  the optimizer heuristics? meaning for exa
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