.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

epased time for few SQL queries is 0 in sys.dm_exec_query_stats table?

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server


I get the elapsed time from sys.dm_exec_query_stats table for each SQL query that I run in MSSQL. I see that the elapsed time for some of the SQL queries are shown as 0 while oracle performance statistics data show some time for the same query.

I believe MSSQL can't take 0 microseconds to execute a query. Should I need to force MSSQL to monitor the lesser time taking queries?

I also used "SET STATISTICS TIME ON", I get 0 elapsed time from this too for those queries which had 0 elapsed time from sys.dm_exec_query_stats.

Any help will be helpful

View Complete Post

More Related Resource Links

Time Difference in Linq queries


Hi All,


I have my Linq query in which i need to find time difference(not days difference).

The field in database is datetime.

Dim ldtToday As DateTime = TimeOfDay

Dim SR_RM_InnerJoin = From SR In lcntxRT60Entities.RtSReports _

Join RM In lcntxRT60Entities.RtRMaster On SR.RtRMaster.ReportID Equals RM.ReportID _

Select SR, RM _

Showing available time slot in table format


I'm currently developing a system for booking of discussion room in a college. This system will allow the staff to help the students to make booking in advanced as well as for instant walk in usage.

I've got a table to keep track of bookings, another for walk in and one for time slot.

For the time slot table, it includes start time slot and end time slot, which is 30 minutes interval for each.

What i'm currently facing is that the system could not display the correct available time slot. I think it's the formula problem which I still can't get it solved.


If the booking time is 8.30am - 9.00am, I have no problem showing the 1st slot as N/A.

But if the time is 8.45am - 9.15am, the system only update the 1st slot as N/A while the 2nd slot still remain as AVAILABLE which is wrong. Because since the time ends at 9.15, I would like the slot 9.00am - 9.30am to be N/A as well.

This would be my code:

for (int x = 0; x < countTimeSlot; x++)
     if (int.Parse(arrEndTimeSlot[x].ToString()) > intBookedStartTime && int.Parse(

How to insert the Current date and time in to SQL Table..

Hello Members,              I have create the table as per the following ..create table company(  empname varchar(30),  empid int,  joindate smalldatetime)I tried,insert into table values('Kumar',202, ??????)I want to insert Current date and time into table Company.....Please give me the solution...Thanks.. 

Count numbers of days (no time table in datasource)

I read the following regarding adding a measure for counting the number of days: The trick is to create new measure group, which will have only one dimension - Time, and a measure with Aggregation Type 'Count' bound to the Day attribute of Time dimension (or any other attribute if we need to count something different from days). The most important setting on this new measure group is to leave IgnoreUnrelatedDimensions=true - this will allow to use this measure across any other dimension in the cube. Howerver, this solution assumes that one has a time dimension based on a table in the datasource. I have a Time dimension that created was by the BIDS wizard that is not in my source database but appearantly invisibly hidden on the AS server without direct access to it. (If I try to add a new measure group (as needed according to the post above) then I can only choose from tables that have nothing to do with time). Can somebody explain how I can do this without a specific timetable in my datasource? Do I need to add a timetable just for this purpose? Can it be stand-alone or do I need to add a relationship with all current tables that have date-columns in them? (I am a finance manager that is building a sales cube based on Navision 3.6, which is already working quite nicely, but still very much a newby)

15 minute incrementable UTC time inserts in the table


What is the most efficient way of creating a 15 minute increment table. This table needs to continue to increment with time so its not a static table.

While loop? how will that affect the performance, if I have to add dates starting 2005 till today and then basically after that with every hour a value needs to be added.. How can I go about doing that?




Insert data in parent and child table at the same time


I have two (parent-child) tables that are bound by a primary key (PK)/foreign key (FK) relation.each PK is identity (idStudent and idBac)

to insert data, i'm using an little application in c# i programmed myself where i insert these columns for the Student table: F_Name, L_Name, Age, Mobile, Adresse, City ,and  ( i don't know how to handle the idBac wich is foreign key of the other table wich still not inserted yet)

for the table Bac, i insert section, school, and city.

what i want is when i click on "OK" button, the idBac ,wich is a foreign key of the other table (BAC), receives the same idBac given the moment i inserted the BAC informations too ...

i don't have any problem wich ado.net ...i just want the right query/procedure.

thanks in advance and sorry for my english, it's not my mother tongue.

Adding rows in table at run time?



    I have a table which has few rows.

    A row contain few textboxes, one file upload field and a dropdown list in its cells.

    I want to make it a dynamic table that is i should be able to add extra rows in table at runtime, but I dont know how to do it.

    Please Help.

XML with PrimaryKey values to delete rows in database table at a time



I'm having a gridview in my aspx page with checkbox, User can select one or more than one record(s) at a time and clicks on delete button, Then i'm constructing an object of my DTO and adding all the checked row's Primary Key (DataKeyNames - GUID's in my case) to a serializable class and generating an XML and sending it as parameter to the Stored Procedure.

My Doubt is, can i bulk delete all the rows from database table those are present in my XML document??

If it works, I can do bulk update with XML only, i already did bulk insertion by generating an XML..

My Another Question is, Will it affect the performance? Each time when i construct an DTO object and adding it to the Generic List??

WHich one is better, I mean ObjectDataSource or SQLDataSource or my XML method??

Can anyone please help me out clarifying my doubts and also XML Delete Query..


My XML will be like this











Will bulk delete happen in the above case?



Returning record from History Table as it would have existed at a specific date/time


I have a table that uses a trigger to save changes (old values) to a history table. This process is working fine, and I have reports that detail the history of a record for the end user. My problem is that the end user now wants a report that will retrieve how the record looked at a specific date/time.

Source Table:

Id	First	Last	Active	Date
1	John	Doe	1	10/25/2010
2	Jane	Smith	1	10/25/2010

History Table:

Id	SourceId	First		Last	Active	Date
1	1		Jonathon	NULL	NULL	5/1/2000
2	2		NULL		Gray	NULL	10/1/2000
3	2		NULL		Smith	NULL	6/15/2003
4	2		NULL		Doe	NULL	11/23/2008

If the user queries for SourceId 2 on 1/1/2009, they need to get:

Id	First	Last	Active
2	Jane	Smith	1

I've used COALESCE searching multiple fields, but I need to figure out how to

How To Store Task Start Time and Task End Time in log table using SSIS?


Hi All,


Present my log table storing each task functionality  Same log table in that  I want store every task start time and end time also. How to implement this in ssis?





Create a table with Time ranges


HI all,

I need to create a table with two columns TimeKey and TimeRange with the following data.But the data has seconds and minutes .

what is the correct format to create table with these two columns and insert the below data .is it correct to insert time in two different formats into the table as seconds and minutes or that should be in single format.

0 <15 seconds

1 15-30 seconds

2 30-60 seconds

3 1-3 minutes

4 3-5 minutes

5 5-10 minutes

6 10-15 minutes

7 15-20 minutes

8 20-30 minutes

9      >30 minutes


Can I write multiple queries in Execute SQL Task Editor at a time


Can I write multiple queries in Execute SQL Task Editor at a time???


Can I write these two queries???

ALTER Table [Islamabad_campus]. [dbo]. [BS_Students] ADD Location   VARCHAR ( 10);

UPDATE [Islamabad_campus]. [dbo]. [BS_Students]

SET Location = 'Islamabad' ;


Users use Excel-Pivot table to query the cube, how to capture those queries?


Most of our users use Excel'10 for querying the cube,

I've set up the querylog-properties in SSAS engine but it only captures the query from SSAS engine and not from the other platform such as Excel. So, when I use Usage based aggregation wizard, it shows the users/ueries from SSAS engine only. Is there any way to capture the user/query information that comes through Excel pivot table and somehow create aggregation through that information?


Any suggestion/help is very much appreciated,


UTC Offset Lookup Table for different Time Zones with Daylight Saving.



I am trying to populate a table for on SQL 2008 Server which should have start datetime and end datetime for daylight saving for various world time zones. So that I can lookup if a particular date is between startdate and enddate and get UTC offset.

Something like the following table. Checked this link but not sure if I can use it in anyway for the information I am looking for or need something completely different.


I not an SQL expert and finding it really difficult to get solution for this problem which I am facing.

Appreciate help.



Round off time to the nearest minute

How would you round this up to the nearest minute? There isn't a built in function to do this so you have to use a little bit of maths to get there. There are 60 seconds in a minute. We already have 38 seconds on the clock. So we need to add on 60 - 38 = 22 more seconds.

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.
The Time Zone
DST Start DST End Offset for UTC
EST 3/13/11 4:00 11/6/11 3:00 240
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