SQL 2012 :: Read And Write Speeds Not Increased With Additional Disks

Mar 13, 2014

Why I see absolutely no performance improvement when I spread my primary file group over 8 separate files on 8 separate disks, as opposed to having the primary file group all in one file on one disk.

I have set up 2 identical databases, one spread over 8 disks and one on one disk. Each database has a table called DATA and a column called VALUE. Value is NVARCHAR(200). I have filled each table up in both databases with 20,000 rows.

I then perform a select on each table in each database using CHECKPOINT and DBCC DROPCLEANBUFFERS to ensure I am reading from disk before each query and the execution times are identical in both databases.

I then ran the same queries against each database using a load testing tool and the batch requests per second on each DB is identical under load.

Surely the database with data spread over 8 disks should be FAR faster than the single file database as you have the combined reading power of 8 disks as opposed to 2??

Also, the same is happening for write speeds. When I create the data on both databases, the time it takes is identical on both.

BOL says it should be faster with multiple disks.

Just FYI this is on an Azure virtual machine and each disk is a locally redundant data disk that I have attached to the virtual machine.

Whether write speeds should increase with multiple disks or just read speeds?

View 8 Replies


ADVERTISEMENT

SQL 2012 :: Blank Database Created On Two Separate Disks - Write To Multiple Files

Mar 17, 2014

I am testing out a blank database created over two physical files on two separate disks with one table called data which has one column called values nvarchar(max).

I filled the table up with a whole load of data and ran a select * against it. If I run Permon at the same time I can see that the read load has been spread over multiple disks as each of these disks is getting read from in parallel. If I create the same database on a single file and run the same select * again it takes much longer, proving that the read load has been distributed across multiple disks.

Now moving onto writes, this is where the confusion lies. I understand that SQL server fills files evenly until they need growing, after which it will then fill files individually until they are full in a round robin fashion unless you have trace 1117 turned on. What I don't understand is why the writes aren't distributed out whilst it is filling these file groups.

I ran an continual insert into my table with go 1000000 to monitor how the files are being filled up. I monitored where SQL server was physically placing the files as they were being inserted by running the following query:

;WITH CTE AS
(SELECT
sys.fn_PhysLocFormatter (%%physloc%%) col1,
RIGHT(LEFT(sys.fn_PhysLocFormatter (%%physloc%%),2),1) AS [Physical RID],
DATAID

[Code] ....

I could see that it would a thousand or so records into file 1, then a thousand or so into file 2, then a thousand or so into file 1 etc etc. In another words it would hit one disk, then another disk, then back to disk one to fill the file evenly. Is there any way to make SQL Server distribute the writes out in parallel so that both disks are writing in tandem?

By the looks of it, multiple disks only scale reads, as with writes only one disk is ever written to at once which is annoying. Any way to harness the write power of multiple disks?

View 6 Replies View Related

SQL 2012 :: Identify Whether Files Are In Read / Write Or Read Only

Mar 24, 2015

How to identify whether the files are in read write or read only?

View 1 Replies View Related

SQL 2012 :: Change Read-only And Read-write

Aug 15, 2014

Can a user of db owner role of a database change the databse option to read only and read-write?If not what permission I need to grant to the user?

View 1 Replies View Related

SQL Server 2012 :: Determine Read / Write Frequency On Database Table

Oct 6, 2015

How do I determine the read/write frequency on a database table? I am trying to do this on a 2012 and 2008 R2 servers.

View 2 Replies View Related

Transact SQL :: Making Server Database Read / Write From Read Only

Jan 12, 2012

i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.

View 11 Replies View Related

SQL 2012 :: AlwaysOn With Clustered Disks

Mar 13, 2014

I am trying to build the 2 node 2 clusters with the AlwaysOn.

Here isthe landscape.

2 nodes PROD failover cluster (running once instance)
2 nodes DR failover cluster (running 2 instances - DR and PRE-PROD)

Both clusters are in different geographies.

PRE-PROD can be editable. So out of scope of Always On.

One instance on PROD -> DR of the other box. [Want to achive thru AlwaysON]

Now my Question:

1) Do i need to have all the 4 nodes in same failover cluster group? If yes, then this would become MultiSubnet cluster Or Is there any way those 2 diffrerent failover clusters (one DR and one PROD) can be part of AlwaysOn.

2) Can i use the clustered disks as in the above landscape for always on?

View 1 Replies View Related

Recovery :: Switch (Standby / Read-Only) DB To Be Read And Write

Aug 26, 2015

I'm trying to do Sharepoint DR with Log Shipping and every thing configured except one thing which is switch the WSS_Content (Standby /Read-Only) DB to be ready and Write. 

I tried from

GUI or ALTER DATABASE [WSS_Content] SET
READ_WRITE WITH NO_WAIT

but I received the below error: 

Database WSS_Content is in Warm Standby 

View 9 Replies View Related

Reset Database Files From Read-only To Read-write

Jan 18, 2008

I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:

Server: Msg 3415, Level 16, State 2, Line 1
Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.

What command(s) are needed to make these files read_write?

thanks

View 7 Replies View Related

SQL 2012 :: Making Mounted Disks Available On A Cluster?

May 12, 2015

OS Windows Server 2012
SQL 2012

I have a software that presents a drive to a 2 node cluster.

Not sure how I would go about presenting this newly presented drive as a Cluster Resource and make SQL depend on this resource.

View 5 Replies View Related

SQL 2012 :: No Disks Were Found On Which To Perform Cluster Validation Tests

Sep 9, 2014

I am getting following errors in my Cluster Validation report when trying to create a Windows Cluster.

I have 2 nodes DB01 and DB02 . Each has 1 public ip, 1 private ip (for heartbeat), 2 private ips for SAN1 and SAN2. The private ip's to SAN are directly connected via Network Adaptor in DB01 and DB02.

Validate Microsoft MPIO-based disks
Description: Validate that disks that use Microsoft Multipath I/O (MPIO) have been configured correctly.
Start: 9/9/2014 1:57:52 PM.
No disks were found on which to perform cluster validation tests.
Stop: 9/9/2014 1:57:53 PM.

[Code] ...

View 9 Replies View Related

SQL 2012 :: Disk / RAID Setup With Limited Disks For Windows 2008 R2

Jun 27, 2015

I am wondering what would be the best disk/RAID setup for a Windows server 2008 R2 OS and SQL Server 2012 database that has heavy read/write. I have the following disks I can use:

4x 15k 146GB
2x 10k 600GB

According to the server build requirements for the application, I need 100GB for the OS and 290GB for the drive containing the SQL mdf there are no stated requirements for the ldf, but would like to know if it should be allocated elsewhere?I should do RAID 10 for the 15k drives for SQL and RAID 1 for the OS on the 10k.

View 6 Replies View Related

SQL 2012 :: Removing Additional Unwanted Log File?

Jul 6, 2015

I wanted to remove an extra transaction log file that was no longer required, and ran the following against the database...

DBCC Shrinkfile (DB_Name_log2, Emptyfile);
go
alter database [Db_Name]
remove file DB_Name_log2;
go

I got a successful removal message. But if I go into the properties of the database, and click on files, it still shows up. Why is this and how can I get rid of it?

It shows up in sys.master_files as offline.

View 4 Replies View Related

XML Read And Write

Apr 4, 2004

Pls help guys

I need to know whether its possible to read or write in to XML documents from SQLServer directly using stored procedures.

I basically need to created a number of XML documents on a nightly basis.,..........also i need to read a no of XML docs to SQLServer...

Is it wise to use stored procedures for this?
Or are there performance issues.........and do u suggest doing in in .NET


Your comments are appreciated..

Regards

Benny

View 5 Replies View Related

Different Cpu Read Write

Apr 15, 2008



I have a query that runs for 10 sec on one database( A) and 5 min on another database(B) even though two database have identical scheam, tables, index and statistics..


I ran a profiler and got the below information
CPU READ Write
Database A: 92051 711956 8774
Database B: 91812 7621589 315822


A query runs on database has a significant larger read and write.. I don;t understand why this is happening? even though these two database have the same structure?? it has the same execution plan as well..


How can i solve this issue?

View 14 Replies View Related

Read/Write To Same Table

Oct 17, 2001

Hi, we're trying to read from a table and write back to the same table and are having a lot of trouble with blocking. What could we do to prevent our application from hanging due to blocking of this type?

View 1 Replies View Related

Write In And Read From My_File.txt

May 31, 2004

Hi ..
I want to Write in files or read from files
for example i have My_File.txt . i need a syntax and i want to call this syntax in my Store procedure and this syntax write forexample " Hello Word " in My_File.txt .
and i want another syntax that read from My_File.txt forexample "Word" from My_File.txt . what are those syntaxes do that ??

thanks

View 1 Replies View Related

Read/Write Performance

Apr 21, 2006

Hello,

We currently run sql 2005 server and also sql express in our dev environments. We use sql express as an offline store (smart client). We have a similar/exact schema on the sql 2005 server and also the express.

We use the auto attach feature to connect to the express version of the database. Both the developer machines and the one that is running the sql 2005 server have exactly the same hardware configuration. The only difference may be that the server box is not running the VS.Net environment. The disk space etc is pretty much the same. Actually we run another database server(DB2) on the 2005 server machine.

We have observed that sql express is much slower and queries execute much slower aswell. For example, this may not be a totally scientific way of checking but a long running query on the server took only 2 minutes while on express it took longer than 9 minutes. The schema and data etc are the same.

Is there something we need to look into as far as read write speed/performance goes ?

TIA,

Avinash

View 6 Replies View Related

Read/Write For Variables

Aug 3, 2007

Hi,

I am using a Script Component and I have a Read/Write Variable varStatusCase (as assigned in the Custom Properties of my Script Component). I used this inside my script to get a specific value. However, when I ran it I get this error:

The collection of variables locked for read and write access is not available outside of PostExecute.

How do I repair this?

Thanks,

cherriesh

View 6 Replies View Related

SQL Server 2012 :: How To Accumulate Additional Values To Existing Column

Nov 2, 2015

I have some transactions with the same card number that needs to add value amount to its existing balance. For example:

Card Number Balance Amount Issue Date Issue Branch.
4000111122223333 $100.00 10/1/2015 123 <= This is an existing row in Card Number SQL table.

Now, the same card number with additional $50 dollars that I want to add to this card number to make the total to become $150. This additional $50 is from another transaction table. On the contrary, I will have -$20 from the same card number in different transaction that I will need to deduce $150-$20 to become $130. How can I update the card number table with debit/credit transactions to keep the outstanding balance?

View 4 Replies View Related

SQL 2012 :: Read Committed For Read Only Database?

Jun 27, 2014

i have a database which get refreshed every day from client's data . and we need to pull heavy data from them every day as reports . so only selects happens on that database.

we do daily population of some table in some other databases from this daily refreshed DB.

will read uncommitted or NOLOCK with select queries to retrieve data faster.

there will be no dirty read as there are NO DML operation in that database so for SELECT which happens concurrently on these tables , will NOLOCK work?

View 2 Replies View Related

Read And Write A Constraint Or Default Value

Nov 20, 2007

Okay, maybe I'm getting ahead of myself.
Using SQL Server Express, VWD and .net 2.0 I've figured out how to drop a Table Column Constraint or Default Value/Binding and then Create it again using a stored procedure. What I can't figure out is how to retrieve that column's constraint value and write it to, say a label, in an aspx page, simply for reference. Is it possible? In this case the Data Type of the column is money.
I'm using it to perform a calculation to a column with a value that the user inserts into another column. (Column1(user input) minus Column2(with Default Value) = Column3(Difference). I just want to read Column2's Default Value for reference so I know whether to change it or not.

View 6 Replies View Related

Denormalization, 2 Databases , 1 Read 1 Write Db

May 28, 2008

Hi,
I was reading that many of these high traffic websites actually have 2 databases, 1 database is used ONLY for reads, while the other is for writes.

How does one go about creating such a setup? How does the database where writes are allowed replicated the data to the read only database server?

View 1 Replies View Related

Implementing Read-write Locks

Jul 23, 2005

Hi,I would like to use database locking mechanism to control access to anexternal resource (like file system).What I need is1. an exclusive (write) lock conflicting with any access to theresource (both for read and write)2. non-exlusive (read) lock conflicting with writes onlyHow this could be done?I'd appreciate any reply.Vadim

View 1 Replies View Related

Read/Write Operation On SQLCE 3.0 Using VC++

Jan 29, 2008

Dear All,

I am looking for a sample code to do read/write operation on SQLCE 3.0 using VC++. For SQLCE 2.0 I have used ATL Consumer template and works well.

Thanks in Advance.

Param

View 3 Replies View Related

Use Variables As A Storage [read/write] ?

Aug 2, 2007

Hello Guys,
in SSIS I want to get a set of data and do some modifications on it before I insert it into the destinatipn. So far so good. Some of the modifications will include comparisions between two columns and if certain field is NULL then I want to get the value from the other one I was comparing to. When using conditional splits, I only get the rows to be redirected so that I can do whatever next. However, I want like use the variables as a storage so that I can put the value of one of the two columns in this variable which will be actually loaded into the destination finally. Any help?
Thanks

View 1 Replies View Related

Read / Write Into .doc From SQL Stored Procedure

Jun 25, 2007

I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?



Thank you.

View 1 Replies View Related

Password To Read/write My Query

Feb 6, 2008

I use excel as an interface to write query to retrieve data from a database in network drive. My problem is everyone can open and edit my query. Of course, the univeral database access user name and password will ask but everyone know this.
How can I put password to prevent any query modification?

Thanks
Daniel

View 1 Replies View Related

Last Read/write Time Of Table

Jul 14, 2007

Is there a way to get the last read/write time of a table?

I want to have a few tables, but only allow them to exist if they have been used in the last 30 days. I want to set up a "purge" job to clear out any tables that have not been used in 30 days.

View 4 Replies View Related

SQL Server 2012 :: Joining Table To Itself To Get Most Recent Date As Well As Additional Fields

Mar 11, 2014

I am querying a table log file in an attempt to get the most recent status of an item. The items can have a variety of different statuses:

(A = Active, R = Repeat, L = liquidation......)

Here is a sample of the data I am trying to report off of:

CREATE TABLE [dbo].[item_status](
[item_number] [varchar](20) NULL,
[sku] [varchar](100) NULL,
[Field_Name] [varchar](50) NULL,
[Old_Value] [varchar](150) NULL,
[New_Value] [varchar](150) NULL,
[Change_Date] [smalldatetime] NULL
) ON [PRIMARY]

[code]...

I have tried join to the same table - but I am still unable to get it to work.

View 1 Replies View Related

Stored Procedure To Read And Write Between XML And SQLServer

Mar 28, 2004

Hi All,
I need some help from you experts.

I need to develop something that reads from xml files and writes in to sqlserver, also it should read from SQLServer and writes to xml.

I should be able to give this as a job to exectue daily ,etc.

Can we do this using stored procedures in SQLServer.
Pls paste some sample code, if any or direct me to any url with better info.

Thanks in advance

View 11 Replies View Related

Does Cursor Convert Table To Read/write?

Apr 10, 2008

Hello,

Any help here much appreciated.

I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write.

i suspect this is when i run a cursor through the table.

Is anyone able to confirm for me whether running a cursor changes a table's properties?

Many thanks.

Tim

Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.

View 3 Replies View Related

Read Write Same Variable In Script Task

Nov 1, 2007

I want to write a variable say testVar in PostExecute of my script. I also want to read the same variable at the start of my script block.

How to specify this variable in the script transformation editor

ReadOnlyVariables = ????
WriteOnlyVariables = ???

If i just define

WriteOnlyVariables = testVar

will it work . I mean i cannot use testVar in both read & write


Any suggestions...


Thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved