.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

No Physical Reads In SQL2005?

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

I have the same test database restored to separate Virtual Machines with SQL2005 and SQL2008 installed. Indexes have been rebuilt and statistics updated. I boot either VM, open Management Studio, set STATISTICS TIME and IO on, and run a simple query that groups and sums rows in a table containing 2.3 million rows.

SQL2005 returns:
Scan count 1, logical reads 111522, physical reads 0, read-ahead reads 111522

SQL2008 returns:
Scan count 1, logical reads 111522, physical reads 2836, read-ahead reads 111522

How did SQL2005 return the same 6200 row result set without performing any physical reads? Did the way these statistics were generated alter between the two versions?



View Complete Post

More Related Resource Links

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  

SharePoint Tutorial -SharePoint Physical Architecture

The SharePoint physical architecture consists of services running on one or more servers.

There are three types of services that together run the SharePoint farm: web, application and database services. Web services through Internet Information Server is what processes the ASP.Net and sends back the html to a user's browser. Application services is where the "brains" of SharePoint are located. All of the SharePoint specific logic and services are processed there. Database services are run by SQL Server and it's where all the data/content for SharePoint is stored.

Draw lines excactly on physical device pixels

When you draw a line in WPF you will experience that they often appear blurry. The reason for this is the antialiasing system that spreads the line over multiple pixels if it doesn't align with physical device pixels.

SQL2005 backup Issue

After moving the SPS 2003 portal from one disk to another, I am having a problem in backing up the portal from the spsbackup tool as well as through the SQL Server 2005 database backup option . Following is the error am getting:-   ============================================================================================================================= Backup failed for Server 'sql server name'.  (Microsoft.SqlServer.Smo)   ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476   ------------------------------ Program Location:      at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)    at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)   ===================================   System.Data.SqlClient.SqlError: The backup of the file or filegroup "sysft_ix_ContentDatabaseName" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)   ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=

Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Er

I am trying to attach a database to SQL 2005. This database has not previously been attached. I have only just installed SQL.   I get the following message: Unable to open physical file "C:\ArrowSQL\Arr@Data\Arrow_data.mdf" Operating system error 5: "5(error not found)" (Microsoft SQL Server: Error 5120)".   I have loaded SQL and the database fiel and directory with the same user acccount which is a local adminstrator ont hsi machine. I have checked that I have read/write access to the file.   The machine runs Windows Vista Business. SQL has SP 2 loaded.   What causes this?



How large do you make your MSDTC Physical Disk Resource?

MSDTC requires a physical disk resource for the 4mb log it uses.  How large do you typically make the disk used as the MSDTC Physical Disk resource?  I don't know if there is a need to go beyond 4MB.  I'm guessing 100MB is a safe number.  Do you agree? Thanks,    Dave

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!

Problem importing text files with binary zeros (0x00) via SSIS(SQL2005). It is all fine when using D

Hi.   There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following: A17     123.17  first row          BB29    493.19  second             ZZ3     18947.1 third row is longer And in hex format: 00:  41 31 37 20 20 20 20 20 ? 31 32 33 2E 31 37 20 20  A17     123.17  10:  66 69 72 73 74 20 72 6F ? 77 0D 0A 42 42 32 39 20  first row??BB29 20:  20 00 20 34 39 33 2E 31 ? 39 20 20 73 65 63 6F 6E     493.19  secon30:  64 0D 0A 5A 5A 33 20 20 ? 20 20 20 31 38 39 34 37  d??ZZ3     1894740:  2E 31 20 74 68 69 72 64 ? 20 72 6F 77 00 69 73 20  .1 third row is 50:  6C 6F 6E 67 65 72       ?                          longer          I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).   The table is: CREATE TABLE eng ( src varchar (512) )   When i upload this file into SQL2000 using DTS or Import wizard, the table contains: select src, substring(src,9,8), len(src) from eng <               src                ><substr>             <len> A17     123.17  first row           123.17                  25BB29                                493.19                  22ZZ3     18947.1 third row           18947.1                 35   As one can see, everything was importe

Documents linking to physical cluster member names

I have a WSS 3.0 SP2 environment with two front-end servers behind a load balancer.  For some reason, when a user tries to open a document, they get routed directly to one of the cluster member servers using their physical name, instead of the DNS entry hosted on the load balancer.  I believe this is the cause of a second login prompt each time a user attempts to open a document.  Any ideas?

Sql2005 is not installing on windows 7

HI i just bough a new PC with windows 7 preloded. I tryed to instal SLQ 2005 but the instalation faild, THe fetures intald are: SQL Setup Suport files SQL Native Client SQL VSS Writer SQL Backward comp... SQLXML4 SQL Server Books Online. Faild Components are: OWC11 SQL Server Database Service Analysis Services Notification Services Integration Services Workstation Components, Books Online. I have SQl Standart edition and Developer Edition. Windows 7 Profesional 32bit thanks in advance

Express 2008 R2 performance dire on VM vs Physical Machine

Like a lamb to the slaughter we upgraded a 2005 Express VM to 2008 R2 at first it seemed fine, then the users compained of poor performance. The database was now above 4GB so no going back. Performance was degrading, we noted the VM only had 512 ram, reconfiguered to 3GB and the machine ground to a halt  Literally - more than 1 user could not log on. A simple query would MAX the processor to 100% for 2 or 3 seconds. No stored proceedure was a standout as a bad performer. Only one user could log in at a time. All indexes were rebuilt (took 4 hours!) still unusable performance. Move the machine to a single processor xp with 2GB ram and 8 users can now log on simutaneously CPU bumps along at 5-20%. Is SQL 2008 R2 Express a no no on VMware? Should we recommend against SQL 2008 R2 upgrades?

In a SQL2005 Reporting Subscription I need to revert the parameter back to the default value "overri

Hi, In a SQL2005 Reporting Subscription I need to revert the parameter back to the default value "override-default." This value was updated to a specific value and now I am unable to have the report updated daily with the new data.  Within Report Manager, drill into the specific report, click on the properites tab, and chose the second link on the left "Parameters."  For the value in question (DataID) someone unchecked the "Has Defalult" button.  This results in the "Use Default" check box being removed from the UI in the Subscription tab.  I can get the Use Defalut box back by entering a specific value in the 'defalut value' but the report runs against that one value (instead of updating daily.) Before the "Has Defalult" was unchecked, the Defalut Value is a buttton, which says "Override Defalut."  It is this 'button' that I am trying to get back. I've googled this and looked thru MS kb without luck.  (I think it maybe a matter of using the right language...) Thank you for your reply.   Victoria  

Unable to tranfer jobs from sql2000 to sql2005

Hi   Im in the process of upgrading my sqlserver 2000 to 2005.I want to migrate all the jobs from sql2000 to 2005. I scripted the job (right click job--->all tasks--->generate sqlscript). I ran the same script on sql2005,i was getting an error like this   "Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column 'owner_sid', table 'msdb.dbo.sysjobs'; column does not allow nulls. INSERT fails. The statement has been terminated."   What does the error mean? cant i run the same script generated in sql2000  on sql2005. Wht is the approach to transfer the jobs from sql2000 to sql2005 ? I know ther is one more way i can transfer.It is through sqlserver business intelligence development studio and using transfer job task.But i  dont want to use that.I want to script the job and execute it on sql2005.   Regards Arvind

Questions on SQL2005 implementation

Hope someone can help me answer my DBA's questions. "We are in the process of creating the plan for the move to SQL 2005.   We are now leaning more towards a flash conversion of all the SQL databases to SQL 2005 rather than an application by application phased in approach.  We currently have a SQL 2005 instance loaded to the ' ServerName' server with the original default instance of sql200 still in use.  Since the sql2005 server is considered a named instance it currently must be used by referring to the full instance name of ‘ServerName\ ServerName 5’. I have just checked online and cannot find any valid way to rename an existing sql server, which for our current situation would be ideal.  My original idea was to connect all the databases to the SQL 2005 instance, have the SQL 2000 software uninstalled, and rename the named sql2005 instance to websql so that all the applications will run without modification.  I have just found that this will not be possible.  According to everything I found, the sql server cannot be renamed.  The software must be uninstalled and reinstalled with the desired name.   Our plan of the flash conversion requires that we list the possible steps to accomplish this conversion based on the knowledge that we can’t rename the sql server.  Can you give us your take on

How to copy SQL2005 from PC1 to PC2

Hi I just got a new PC and need to install my applications from my old PC. I instaled SQL 2005 but now i need to instal the databases that are instaled on my old PC, Is there an easy way to to move databases from server1 to server2? thanks

Does "Microsoft OLE DB Provider for SQL Server" get different versions for SQL2005 and SQL2008?

We know SQLServer OLE DB have two driver types: SQL Native Client and Microsoft OLE DB Provider. I know The former one have different versions for SQLServer2005 and SQLServer 2008 (SQLNCLI.DLL, SQLNCLI10.DLL), right? And for latter one, does it have different versions? I only got SQLOLEDB.dll under system32 folder, but have no idea whether it have different versions. Can some one help me?   Thanks Spring  
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