.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

Weird query plan problem where total is over 100%

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :Sql Server
Hi All, I am looking at the query plan for a stored procedure and I am seeing things like 1400% on the execution plan, the stored procedure I believe is called within a loop quite a lot of times, but I don’t quite understand why the percentages will be over 100 for one section of the execution plan. Any explanation for this ? Thanks in advance.

View Complete Post

More Related Resource Links

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

problem with merge statement query and parallelism plan


I have the query below which is using a parallel query plan, I am not quite sure why its doing so, I think its to do with the design of the query but apart from adding indexes and specifying maxdop 1, I was wondering if there is anything else I can do in order to rewrite the query to be more efficient and stop the use of the parallel plan.


Thanks in advance.

MERGE tblPersonProfile merge_target
USING #newrecs	sourc ON merge_target.PersonURN = sourc.PersonURN AND [AddressURN]=AddressURN AND MatchCode =[ProfileMatchCode] 
INSERT (PersonURN, AddressURN, ProfileMatchCode)VALUES (sourc.PersonURN, sourc.AddressURN, sourc.MatchCode)

Weird problem in Detailsview - Dropdownlist in



I'm new in this forum and I really hope you'll be able to help me.

I converted a field in the detailsview into a template field and put a dropdownlist in the <InsertItemTemplate> and it worked great. This is the code I put:

<InsertItemTemplate><asp:DropDownList runat="server" DataSourceID="sdsGenresPossible" DataTextField="Genre"  DataValueField="Index" SelectedValue='<%# Bind("Genre") %>' ></asp:DropDownList></InsertItemTemplate>

Now, I tried to do the same thing for the <EditItemTemplate> . This is the code I put there:

<EditItemTemplate><asp:DropDownList runat="server" DataSourceID="sdsGenresPossible" DataTextField="Genre" DataValueField="Index" SelectedValue='<%# Bind("Genre") %>' ></asp:DropDownList></EditItemTemplate>  

When I change the DV properties I do see that a dropdownlist was  added but when I run the program and click on "Edit" in the DV nothing happens (however when I click on "Insert" it works fine).

This is the Select command for the data source I used:

<asp:SqlDataSource runat="server"

LINQ query with multiple joins, problem


I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits in assets_units.DefaultIfEmpty()
                   join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins
                   from build in assets_bins.DefaultIfEmpty()
                   join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins
                   from test in test_bins.DefaultIfEmpty()
                    join testTrgt in db.TEST_SUITEs on assets.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new

weird problem of using multithread to querying sql server

Hi,   I'm working on a project which needs query the sql server many times to pull the statistics info. the whole process is very slow. so, I'm trying to use the multithread to do the work. However, I noticed one weird thing:   In the program, I need pull, say 10000 documents from the sql to local PC, then can do some analysis. Each document size is different. If I used 1 thread, say for document A whose size is 5MB, it will take about 20 seconds for the SELECT query returns the result. Now, if I used 10 threads, I found that it will take much longer time for SQL to return the SELECT query for document A, say maybe 90 seconds. I got the run time for the SELECT from the sql profiler.   Overall, the performance of multithread is very similar to single thread even I use 50 threads. I used the same code for another project, the only difference is that that project scrapes data from website and this project gets data from sql. The other project works very well when using multithread. so, I'm kind of believe that the multithread part code works fine.    Could this because some settings on sql server? some bandwidth limitation? any clues?   any help/suggestion will be appreciated.   thanks reader      

Having problem accessing multi-choice parameter in SQL Query in Report.

Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

Problem with Content Query Webpart and Custom Userfields

Dear Community, I am using Sharepoint 2007's content query webpart to access content types from 2 lists each contained in a different sitecollection. To achieve this, I used this tutorial: http://www.heathersolomon.com/blog/articles/CustomItemStyle.aspx. The tutorial worked great. By using the property CommonViewFields and modifing the XML Stylesheets accordingly, i could access almost every field. Even User Fieldtypes like editor and author were accessible by adding (editor,User) or (author,User) to the CommonViewFields property. But when I tried to access the custom made userfield "Username" in the same way (adding Username,User to the property), the webpart threw an error and couldn't be displayed anymore. When I changed it to Username,Text , no error occured but it naturally didn't show up, since it is the wrong fieldtype. I hope some of you can help me, since all my researches on this topic only brought up some more people with the same problem, but no solution to it. Thanks in advance, SoundofSilence

sql query conversion problem

Hi i have a query which gives me the value 25.255555555 but i need output as 25.25..what should i use to achieve that

sql query conversion problem

Hi i have a query which gives me the value 25.255555555 but i need output as 25.25..what should i use to achieve that

Weird memory problem

Hi all.   I am quite new to c#, I have the following code segments and both of them cause the program to throw System.ComponentModel.Win32Exception indicating that memory is not enough. Both of the code segments is in a form.   while(true) { System.Drawing.Graphics g = this CreateGraphics(); System.Drawing.BufferedGraphicsContext c = System.Drawing.BufferedGraphicsManager.Current; System.Drawing.BufferedGraphics b = c.Allocate( g, new System.Drawing.Rectangle(0,0,3000, 3000) ); } while(true) { this.Controls.Clear(); this.Controls.Add( new Label() ); }   My question is shouldn't the resource created handled automatically by garbage collector ?   Thanks a lot.  

Sharepoint 2010 FullTextSqlQuery query problem with where condition

Hello, i´m developing a webpart under Sharepoint 2010, i need to launch some different query´s to filter data but i´m having problems. First of all, i launch this query and i get all the users; SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') ORDER BY "Rank" DESC with that results i fill different dropdownlist with each of the columns, then I can filter for each column. For example: I filter by PreferredName, Lastname or WorkEmail, with this query SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') AND (("LastName" Like '%MOSS%')) ORDER BY "Rank" DESC But when i try to use the columns Department, OfficeNumber and Jobtitle every query returns 0 rows. SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') AND (("Department" Like '%Dirección%')) ORDER BY "Rank" DESC The main thing is if i get all the data i see that these columns (officeNumber, Jobtitle, Department) are not empty. Some of them have values some of them don´t. All the columns have the property "Reduce storage recquirement for text propert

syntax problem passing parameter into Indexing Service Query

Hi everyone, I have the following query which works fine: select OriginalFileName from Document_Entries where EntryType like 'File%' and substring(entry,charindex('file_',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, 'SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS('' "green" '') ') )  It finds all documents in the document system which contain the word "green" using the index catalog.  My problem is that i need to include this query in a larger stored procedure which accepts a parameter for key words amongst others. I can't work out the syntax to get the @keywords parameter into query. The closest I've come is the following which runs but comes back with "incorrect syntax near keyword 'green'".  The @keywords parameter will contain any key words the user enters.   declare @keywords nvarchar(500) set @keywords='green'   Declare @query nvarchar(max) set @query = ' select OriginalFileName from Document_Entries where EntryType like ''File%'' and substring(entry,charindex(''file_'',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, ''SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS(''' + @keywords + ''') '')     )  )' exec(@query)   Any ideas? thanks Gus

MDX newbie query problem

Hi,   I have following measures - Period - NumberOfPeriods - Amount   I want to add a calculated member to a cube where I get the sum of amount for all entries with "Number of Periods" = Period. Is there a way to do that?

Weird problem with session cookie

Hi all,I'm having a weird problem with my web application, and I can't find the solution to it.I'm using a Java applet to read some data from an electronic ID. This applet posts its captured data to a HttpHandler class and stores the data back in some session variables. It all works smoothly, unless in some cases.Every time a do a new publish to the webserver or the website isn't touched for some time. There is a problem with the session.The applet can't find a session cookie, and so it crashes.I did some tests and indeed, the cookie ("ASP.NET_SessionId") does not exists. But it seems that other session variables (user login, etc) are working fine.To make sure that the cookie for the session ID is readable from the applet I do the following in the global.asax: Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs) ' Code that runs when a new session is started Dim isSessionFound As Boolean = False If Response.Cookies.Count > 0 Then For Each s As String In Response.Cookies.AllKeys If s = "ASP.NET_SessionId" Then Response.Cookies("ASP.NET_SessionId").HttpOnly = False isSessionFound = True 'just for test End If Next End If End SubI tried to do something with the isSessionFound, wh

problem with cross apply query

Hey guys. This is one of the queries pasted from BOL. I'm having problems excuting this query. The problem lies in the CROSS APPLY part. When I copy this query and run it in SSMS, it gives me an error saying 'Incorrect syntax near .' It doesn't like the qs.sql_handle part. If I remove that and pass the actual handle in for some query, it works. Can someone please tell me what I'm doing wrong?????? Also, I've sp1 installed on my SQL Server 2005 Enterprise, just in case if this matters. Below is the query pasted which is giving me problems. Thank you. SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time/execution_count DESC;

Weird Paint Event Problem

I am creating a .net program which requires a rectangle to be drawn on the screen,  the code is triggered by a menu item click, which runs the code:    this->Paint += gcnew System::Windows::Forms::PaintEventHandler(this, &Form1::PaintForm);   this event draws a rectangle fill, then a rectangle line around it (anybody know how to make that one shape instead of two?) like this: Graphics^ g = this->CreateGraphics(); Pen^ blackPen = gcnew Pen(Color::Black); SolidBrush^ whiteBrush = gcnew SolidBrush(Color::White); Rectangle stage = Rectangle(70, 70, 339, 439); g->DrawRectangle(blackPen, 69, 69, 340, 440); g->FillRectangle(whiteBrush, stage) the code runs fine, but the shape will not appear (the screen won't blit) until I minimize the form, then restore it. I've tried all sorts of things like using e->Graphics-> Instead of the Graphics^ g = this->CreateGraphics();, and nothing seems to fix it. Any help? By the way, I'm fine with receiving code samples in codes other than c++.

Getting Query Time Out Problem in Particular Dimension?

We are getting query time out problem in particular dimension in SSAS. can any one help on this?  
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