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

Top 5 Contributors of the Month
Post New Web Links


Posted By:      Posted Date: October 10, 2010    Points: 0   Category :Sql Server

Hi all,

I have a situation. When a user updates a fact table it locks the table and thus selects take a long time.

I am thinking of using either READ_COMMITTED_SNAPSHOT or Dirty Reads.

Can someone tell me why one would be better then the other and visa versa ie pros and cons of each?


View Complete Post

More Related Resource Links

ASP.NET 4.0: WebForm Routing (quick n dirty version)

One of the things we're adding in ASP.NET 4.0 is built-in support for using Routing together with WebForms. Now, I'm totally aware that this is possible to do already and that a number of people have posted ways of doing this with the Web Routing feature which shipped with ASP.NET 3.5 SP1. We're just adding some features to make it easier to do

{ End Bracket }: Dirty Rectangles.


Here Jon Schwartz discusses a programming environment designed just for kids.

Jon Schwartz

MSDN Magazine September 2006

SSIS Deployment Reads local Environment Variable and not Server

Hi All, I have an SSIS package that uses an Environment variable to hold the connection string for the local SSISConfiguration table.  This works properly on my local machine.  However, when I import the package to Integration Services(through SSMS) it reads the environment variable from the local machine and not the server it is deployed to.  If I log on to the server remotely and do the same process it uses the the correct environment varible... (SQL Server 2005) Is there a way to deploy the package to the server and get it to use the correct Env variable without having to log-in to the server and import it?  Let me know if anyone is looking for additional information.   Thanks!

Question about logical/physical reads figures

My script is: set   statistics io off set   nocount on --drop table mytbl create   table mytbl (x int) --create clustered index iii on mytbl(x) declare   @i int; set @i = 0 while   (@i < 10000) begin   insert into mytbl values (@i)   set @i = @i + 1 end set   statistics io on select   * from mytbl The result is: Table 'mytbl'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. My questions: 1. Why do I get 17 logical reads? an integer is 4 bytes. a data page is 8000 bytes. I have 10 thousand records times 4 bytes means 40 thousands bytes which fits to 5 data pages. why are there 12 extra logical reads? (ok there might be additional haeder and footer to the data page but that shouldn't get to an extra 12 pages) 2. why are there no physical reads? It's the first time i read from this table. Was the table stored in the cash when I inserted it? was it actually written to the disk data pages? 3. How can I know what data pages are currenctly stored in the buffer cache? (of which tables/indices)? 4. Is there a logical/physical writes couter? 5. When I add the clustered index I get 19 logical reads - why is that? thanks, Dror  

Returning last 4 digits of an ssn populated by dirty data

I have an ssn column that some times has a valid 9 digit ssn, sometimes has format characters, sometimes has "garbage" (including less that 4 characters as it is a test database), null, etc. I would like to write a substring statement that will return the last 4 digits of the ssn if it is valid. What would be the best way to do that in t-sql?

CalendarExtender shows dirty calendar for the first time


Why calendarExtender shows dirty calendar for the first time? (see the image) How to fix it?

Visual indication of whether binding target is 'dirty'

I would like to be able to show users whether data they can see is synchronised with the source object the control is bound to.  E. g. a text box receives an update from the data source so its background is set to white.  When the user starts typing the background is set to yellow.  When they press enter (running code to explicitly update the source) or the textbox loses focus or press escape (running code to revert to the source value), the background should revert to white.  If the source should changes while they're editing and the target updates, the background should also revert to white.  The aim of this is to make it totally clear the the user what data is committed and what is not.

First I tried to do this using an attached behaviour to handle SourceUpdated and TargetUpdated events from the textbox.  Unfortunately TargetUpdated only fired on they first time the textbox was updated and SourceUpdated never fired at all.  I assumed this might be because it was a routed event that was being handled elsewhere.  Unfortunately I wasn't able to get round this by calling TextBox.AddHandler with handleEventToo = True because SourceUpdatedEvent is not a member of TextBox.  I guess this means it's not a RoutedEvent because it belongs to a Binding object which is not



Hi all,


User A inserts 1million rows into fact table
User B selects rows from fact table at the same time.

If i do the following


what will this do?

I have read from the following link:http://msdn.microsoft.com/en-us/library/tcbchxcb%28VS.80%29.aspx

but dont understand this?

1. Will doing the above help me on my sceario?

please help me?

SSIS package which reads excel files fails on SQL Server Agent



I have a SSIS package which reads an excel file and loads data into a table using script component(C#) as a source. The package runs without any errors when I manually run it on my machine and on the server. But the package fails when run as a SQL Server Agent job.

I tried all the possible fixes I found on the web but still can't get it to work.

Could you shed some light on it?



Writing openquery with select statement for Informix DB which to set dirty read



I currently have an openquery statement that gets data from an informix database.  We are experiencing issues with not getting all of the data.  We would like to add a set isolation to dirty read statement.  Just can't seem to get the Informix statement to work with the T-SQL openquery statement:

Original statement:



loc, container, [order], po, parcelcarr,

SSIS - Data Flow Task Reads Fast But Importing the Output,Update & Deleting Extremely Slow & runs lo


Good day, I need help please.

In my source I read about 4mil records in a matter of seconds But Importing the Output to the Same Table takes about an 1 hour to update.

My Source Read is the Same Table to which i'm Importing, Updateing,Deleting

Importing: Table Lock & Check Constraints are checked

Please any ideas & suggestions will be great.

Thank You

DataSource xml reads xml with extra letters



I receive lists from a web service and then I convert the lists to datatable and then I add the datatables to a dataset and from there I get the xml from the dataset where I can save it into a sql database. But the problem is that the xml has /r/n between all attributes and elements. I dont know how to get this out of the xml. Can this be because of my converter. If so can anyone give me a better way to get multiple lists into a dataset.

Kind Regards


12255 reads..


Is there any better option to calculate like this in a query..it costs 12255 read, which I guess is huge, according to Profiler..

select @sum=

		isnull(case when end_hour+end_minutes/60.0-start_hour-start_minutes/60.0<0.0
		isnull(case when end_hour2+end_minutes2/60.0-start_hour2-start_minutes2/60.0<0.0
		isnull(case when end_hour3+end_minutes3/60.0-start_hour3-start_minutes3/60.0<0.0
		isnull(case when jour_enkel_end_hour+jour_enkel_end_minutes/60.0-jour_enkel_start_hour-jour_enkel_start_minutes/60.0<0.0
		isnull(case when jour_kval_end_hour+jour_kval_end_minutes/60.0-jour_kval_start_hour-jour_kval_start_m

reduce logic reads



I have a 200 records table and I noticed lot's of logic reads from it:

SELECT count(*) from mytable where year(mydate) = 2010 and month(mydate)=11 and day(mydate) = 2 and type = 'N'

It's a table scan, can I add index to speed it up?




ForEach Loop reads next file when child task fails


We have a ForEach loop that reads over a list of files in a directory.  We are placing the current file name into a parent variable and passing it into children packages through the Parent Package Variables configuration.  We have attached a Execute Package task for the ForEach loop Failure constraint.  The package is an exception handler that moves the current file (set in the ForEach loop variable mapping) to a failure folder.

The issue is that when the exception handler is called, the file name being passed in is the next file in the directory, not the file currently being processed.  Is this due to read ahead by the ForEach loop?  I pass this variable exactly the same way to child packages within the ForEach loop and it works fine.  It is just when I pass the variable to the constraint path from the ForEach loop that changes the value of the variable.  Is this because the exception handler sits outside of the loop container?

Thanks for your help.


SQL Profiler Recording not recoring reads of view from other databases



I'm in the process of shutting down a database and want to trace all access to it, so that I can identify what is using it. without monitoring the whole of the server

I'm currently tracing the following events





exec prepared SQL

I'm filtering on where the databasename = mydatabase

but I am missing access to this, for example I can read a view in this database without it being visible in this trace.

I'd be grateful if you could suggest what I should be monitoring.

thanks in advance.








Low reads and high duration in sql trace on a proc with dynamic sql

I have a SP which builds a sql stmt including order by etc based on the parameters. I am seeing this proc varies a lot in the duration some where between 2 to 10 seconds, but the reads do not exceed 2000 pages, so indexing may not be the reason here. Could it be parameter sniffing ?
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