Large Binary Writes Slower Than Expected
Jul 23, 2005
Hello, we are investigating the use of SQL Server as a backend to our
scientific imaging application. We have found that when we write a
large image (60 Megabytes) the performance is quite a bit slower than
writing 60 single megabyte images. The tests were performed running
SQL Server 2000 on Windows 2003 Enterprise on a single machine to
eliminate the network's contribution. Perhaps there is a configuration
option that will allow us to tune SQL Server to better handle large
writes?
TIA
View 1 Replies
ADVERTISEMENT
Feb 27, 2007
Hi,I am having some trouble with my ASP page sending a file to a SQL Server 2005 database running on another machine. An exception is generated as follows:A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) It seems to work ok with smaller files, but when I attempt to upload a 11MB file, it gives me the above exception. I upped the httpRuntime maxRequestLength in the web.config to 2048. Any Ideas? Here is my code:1 if (FileUploader.HasFile)
2 {
3
4 // call the stored proc
5 SqlConnection conn = new SqlConnection();
6 conn.ConnectionString = "Password=password;Persist Security Info=True;User ID=MyUser;Initial Catalog=MyDb;Data Source=MySqlServerMachine;Connect Timeout=300";
7 conn.Open();
8
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = conn;
11 cmd.CommandType = CommandType.StoredProcedure;
12 cmd.CommandText = "spAddTestBinary";
13
14 SqlParameter param = new SqlParameter();
15 param.ParameterName = "@binaryParam";
16 param.SqlDbType = SqlDbType.VarBinary;
17 param.Direction = ParameterDirection.Input;
18 param.Value = FileUploader.FileBytes;
19 cmd.Parameters.Add(param);
20
21
22 cmd.ExecuteNonQuery();
23
24
25 conn.Close();
26 }
Here is my table:1 BinaryTable(
2 [id] [int] IDENTITY(1,1) NOT NULL,
3 [myBinary] [varbinary](max) NULL,
4 CONSTRAINT [PK_BinaryTable] PRIMARY KEY CLUSTERED
5 (
6 [id] ASC
7 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
8 ) ON [PRIMARY]
My stored proc:1 ALTER PROCEDURE [dbo].[spAddTestBinary]
2 -- Add the parameters for the stored procedure here
3 @binaryParam varbinary(MAX)
4 AS
5 BEGIN
6 -- SET NOCOUNT ON added to prevent extra result sets from
7 -- interfering with SELECT statements.
8 SET NOCOUNT ON;
9
10 -- Insert statements for procedure here
11 Insert into BinaryTable
12 (myBinary)
13 Values
14 (
15 @binaryParam
16 )
17 END
View 3 Replies
View Related
Oct 13, 2006
Hi,
I am currently importing (and exporting) binary flat files to and from Db fields using the TEXTPTR and UPDATETEXT (or READTEXT for export) functions. This allows me to fetch/send the data in manageable packet sizes without the need to load complete files into RAM first.
Given that some files can be up to 1Gb in size I am keen to find out a new way of doing this since the announcement that TEXTPTR, READTEXT and UPDATETEXT are going to be removed from T-SQL.
I had a quick foray into SSIS but couldn't find anything suitable which brings me back to T-SQL. If anyone knows a nice elegant way of doing this and is prepared to share, that would be grand.
Thanks for your time,
Paul
View 9 Replies
View Related
Sep 7, 2006
From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?
If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?
TIA
Pat
View 7 Replies
View Related
Jul 14, 2007
Hi I've followed a tutorial on how to write and read varbinary(max) data to and from a database. But when i try to read the data i get the error that the data would be truncated, but only when the varbinary(max) is greater then 8kB. I've used a system stored procedure (sp_tableoption) to set the table that holds the data to store data outside rows. To select the data i'm using a stored procedure: SELECT imageData , MIMEType FROM Pictures WHERE (imageTitle = @imageTitle) And then using an .aspx page to Response.Write the data:Using conn As New sql.SqlConnection conn.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString Dim getLogoCommand As New sql.SqlCommand getLogoCommand.CommandType = Data.CommandType.StoredProcedure getLogoCommand.CommandText = "GetPicture" getLogoCommand.Connection = conn Dim imageTitleParameter As New sql.SqlParameter("@imageTitle", Data.SqlDbType.NVarChar, 200) imageTitleParameter.Value = Request("imageTitle") imageTitleParameter.Direction = Data.ParameterDirection.Input getLogoCommand.Parameters.Add(imageTitleParameter) conn.Open() Using logoReader As sql.SqlDataReader = getLogoCommand.ExecuteReader logoReader.Read() If logoReader.HasRows = True Then Response.Clear() Response.ContentType = logoReader("MIMEtype").ToString() Response.BinaryWrite(logoReader("imageData")) End If End Using conn.Close() End Using Can anyone please help me with this?!
View 2 Replies
View Related
Jun 2, 2015
I have a well-structured but also very large binary data-set that is generated by a C++ application every five minutes. The data needs to be accessed by SQL applications. Since data is generated every five minutes, performance is key, both for write and read. The data set is about 500MB.If data is written to the file system, the write performance doesn't involve SQL server. For reading it, I have a CLR to read the portions of the data that I need based on offset and length. That works and is very fast. The problem is that data is stored in the file system, so it is not self-contained within the database.
A second option that I haven't explored yet, is to write the data into a table as VARBINARY(MAX). I would read the data using SUBSTRING with appropriate offset and length. Performance of SQL write/read of binary data of this size, and whether there is a third option I haven't thought off. I'm using SQL Server 2014.
View 5 Replies
View Related
Jul 20, 2005
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View 15 Replies
View Related
Mar 1, 2008
hi, friends, please look at this:
I have a SSIS package, and inside it I do something like below:
1. I have a SQL component, to give back a object to store the records.
2. I have a VB script component, I direct the object I got in 1 step into the script as a dataset.
My problem is:
I run the package in the SQL SERVER 2005 Store Procedue like this:
do
dtexec.exe package.dtsx
loop untill i>t
I control the it runs 30 times. But I found that the speed is slower and slower.
the first time, it takes about 600 s, but the last time, it takes the 1800 s.
Why?
The package don't drop the object it create during the loop in the Store Procedue ?
Thanks!
View 11 Replies
View Related
May 27, 2008
Hi All,
i have a table in MS Access with CandidateId and Image column. Image column is in OLE object format. i need to move this to SQL server 2005 with CandidateId column with integer and candidate Image column to Image datatype.
its very udgent, i need any tool to move this to SQL server 2005 or i need a code to move this table from MS Access to SQL server 2005 in C#.
please do the needfull ASAP. waiting for your reply
with regards
View 1 Replies
View Related
May 22, 2014
Server is SQL 2000
I have a table with 10 rows with a varbinary column
I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)
example as follows
TableA
Column1 Column2 Column3
aaa 001 <some binary value>
aaa 002 <some binary value>
aaa 003 <some binary value>
aaa 004 <some binary value>
aaa 005 <some binary value>
desired results in TableB
Column1 Column2
aaa <concatenated value of above binary columns>
View 9 Replies
View Related
May 7, 2015
I'm using a bit-wise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.you also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?
View 9 Replies
View Related
Sep 26, 2005
I have an application that is insertting thousands of records houlry. The server's hard drives are staying maxxed out. My boss says there is an index problem. I say it is a drive subsystem issue.
Any help would be appreciated to understand this performance problem.
View 2 Replies
View Related
Oct 30, 2006
How can You find the reads and writes per second of your hard drives in sql. I am reading my SQL book and it says that your average disk should have 125 or less i/o's. And it gave the forumal but as mentioned I don't know how to find the reads and writes.
View 4 Replies
View Related
Mar 25, 2008
Hi!
First of all I want to tell you that I'm not a dba or tuning expert but I've ran a trace on a database with perfomance problems and I've found a strange thing.
The user creates orders for their service people in the organisation. I can see in the trace that inserts are done but they don't produce any writes rightaway. However after 10-15 minutes all the writes are done, what could make the actual write be delayed so much. The application is developed using .net.
/Magnus
Jesus saves. But Gretzky slaps in the rebound.
View 8 Replies
View Related
Jul 21, 2000
Is there a way to get a total count of all SELECT, UPDATE, DELETE and INSERT statements to a SQL Server 6.5 database during a 12 hour period? I'm thinking maybe someone knows of a software that reads the log or monitors the server... I've been looking at the performance monitor and, although it has good information, it doesn't capture DML's.
FYI - it's for capacity planning.
TIA,
Mike
View 1 Replies
View Related
Mar 5, 2008
GUys,
Is there any way track tables which have most no of reads and writes from a database of 400 tables.
Thanks
View 9 Replies
View Related
Feb 2, 2004
Hi,
i am experiencing SQl write performance problems on a very shiny server. Got data files on a Raid 1+0, log files on a separate drive, all SCSI, Win2003 server, 6G RAM, 2 Xeon processors. I've created a small benchmarking program and run it on my desktop pc and this 'big' server. Here are the results:
Desktop: SQL server inserts: 78 Seconds, Direct writes to the harddisk(Just write a string to the file 10000 times): 13 seconds
SQLServer: SQL server inserts: 422 Seconds, Direct writes to the harddisk: 16 seconds
So, for some reason, my 'shiny' machine is 6 times slower on writes than my desktop. When i tried comparing the select performance, my shiny server is 10 times faster than my desktop.
Initially i had Raid5 on my server and it had poorer direct write performance but now, direct writes seem to be ok, so, i recon this is a problem related to SQL server.
What can i do to improve the insert performance?
Thanks in advance
View 8 Replies
View Related
Feb 18, 2004
Hi everyone! I'm new to this forum and I suspect I'll be using this forum frequently. Good stuff.
Allow this question may appear to be Web-related, I think the problem is with what I'm doing with the database. Please read.
I'm trying to implement a page tracking solution using ASP and SQL 2000. It basically writes a new record to a table every time a user visits a page on the site. It appeared to work fine at first, then I've increasingly been getting time out errors on my pages -- all pointing to the include file that fires the database write.
Here's the code that's referenced on every page:
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "dsn=x;uid=y;pwd=z;"
Set objRecordset1= Server.CreateObject("ADODB.Recordset")
objRecordset1.Open "SELECT * FROM table",Conn,1,2
objRecordset1.AddNew
objRecordset1.Fi elds("PAGE") = Left(request.servervariables("SCRIPT_NAME"),100)
objReco rdset1.Fields("QUERY_STRING") = Left(request.servervariables("QUERY_STRING"),100)
objRec ordset1.Fields("DATE") = Date()
objRecordset1.Fields("TIME") = Time()
objRecordset1.Fields("PLATFORM") = Left(request.servervariables("HTTP_USER_AGENT"),100)
obj Recordset1.Fields("REFERRER") = Left(request.servervariables("HTTP_REFERER"),100)
objRec ordset1.Fields("USER_IP") = Left(request.servervariables("REMOTE_ADDR"),20)
If Request.Cookies("TEST")("ID")<>"" Then
objRecordset1.Fields("VISITOR_ID") = Request.Cookies("TEST")("ID")
End If
objRecordset1.Update
Conn.Close
Set Conn=Nothing
%>
After taking out the reference to the above code everything speeds back up. So, I know the performance hit and time out issues have to do with the code above.
Is it the simultaneous write to the table, the constant opening and closing of the recordset, the cursor type, the lock type – or combination of things?
HELP!! Thanks!
David
View 3 Replies
View Related
Apr 17, 2008
Problem Statement........
Lets say user A accesses a record and is making an update to a column... next user B accesses the same record and makes an update to the same column and saves the data... how can user A check to see if an update has been made to prevent overwriting the data..
Is there a query statement that user A can write to check for this?
I understand locking can be used to prevent this but is there an alternative to locking.
View 5 Replies
View Related
Nov 25, 2007
Ok, here is my situation.....
When someone navigates to a user's profile page on my site, I present them with a slideshow of the user's photos using the AJAX slideshow extender. I obtain the querystring value in the URL (to determine which user's page I'm on) and feed that into a webservice via a context value where an array of photos is created for the slideshow. Now, in order to create the array's size, I do a COUNT of all of that specific user's photos. Then, I run another SQL statement to obtain the path of those photos in the file system. However, during the time of that first SQL query's execution (the COUNT statement) to the time of the second SQL query (getting the paths of the photos), the owner of that profile may upload or delete a photo from his profile. I understand this would be a very rare occurrence since SQL statements 1 and 2 will be executed within milliseconds of each other, but it is still possible I suppose. When this happens, when I try to populate the array, either the array will be too small or too large. I'm using SqlDataReader for this as it seems to be less memory and resource intensive than datasets, but I could be wrong since I'm a relative beginner and newbie. This is what I have in my vb file for the webservice.....Public Function GetSlides(ByVal contextKey As String) As AjaxControlToolkit.Slide() Dim dbConnection As New SqlConnection("string for the data source, etc.") Try dbConnection.Open() Dim memberId = CInt(contextKey) Dim photoCountLookupCmd As New SqlCommand _ ("SELECT COUNT(*) FROM Photo WHERE memberId = " & memberId, dbConnection) Dim thisReader As SqlDataReader = photoCountLookupCmd.ExecuteReader() Dim photoCount As Integer While (thisReader.Read()) photoCount = thisReader.GetInt32(0) End While thisReader.Close() Dim MySlides(photoCount - 1) As AjaxControlToolkit.Slide Dim photoLookupCmd As New SqlCommand _ ("SELECT fullPath FROM Photo WHERE memberId = " & memberId, dbConnection) thisReader = photoLookupCmd.ExecuteReader()
Dim i As Integer For i = 0 To 2 thisReader.Read() Dim photoUrl As String = thisReader.GetString(0) MySlides(i) = New AjaxControlToolkit.Slide(photoUrl, "", "") Next i thisReader.Close() Return MySlides Catch ex As SqlException Finally dbConnection.Close()
End Try
End FunctionI'm trying to use the most efficient method to interact with the database since I don't have unlimited hardware and there may be moderate traffic on the site. Is SqlDataReader the way to go or do I use something else? If I do use SqlDataReader, can someone show me how I can run those 2 SQL statements in best practice? Would I have to somehow lock writing to that table when I start the first SQL statement, then release the lock after I execute the second SQL statement? What's the best practice in this kind of scenario.
Thanks in advance.
View 3 Replies
View Related
Apr 18, 2002
I upgraded from 6.5 to 7.0 SP3. Now when I save (write) an invoice it takes about 10-12 seconds, at 6.5 it was 1-3 seconds. SQL Server and my Materials App are the only thing running on this box. This is the only area that has gotten slower everything else works great. I have 3 users saving invoices and about 15 people total using the system at one time. It's a compaq DL580 loaded with memory, database is 2,195MB in size. Same 6.5 client to access system as before. Should I rebuild/reindex the database? Is there something from the old 6.5 version I need to remove?? Thanks in advance!!!
View 1 Replies
View Related
Nov 5, 2015
How can I measure the disk reads and writes to see if I need to add aditional disks to the server?
View 2 Replies
View Related
Aug 1, 2006
Is it possible to find the reads/writes to a sql server table ?
View 2 Replies
View Related
Oct 25, 2006
Hi everyone,
Every time that my application throws an .DTSX file I don't know who or what is writing on eventviewer.application if failed or successful.
Execute method implements a customized class which implements IDTEVENTS but I promise that in any place of my code I'm writing that information.
app.execute(nothing,... MYEVENTS)
Public Class MYEVENTS
Implements IDTEVENTS
..
..
..
..
All the methods are declared although empty but OnQueryCancel which is customized.
How to disable this behaviour?
I'm concerned for that because of we could launch (when it's gonna in live) 300 or 400 packages on-daily basis!!!
Thanks in advance and regards,
View 3 Replies
View Related
Jul 6, 2015
We are in the process of moving existing clustered SQL server databases to AWS. There is one major database that has intensive reads and writes transactions. I'm wondering what is the best design to optimize the performance for both R/W since we have constant issues historically with the current environment when massive updates are happening. Reads shall have higher priority over writes.
View 2 Replies
View Related
Jul 17, 2015
I have inherited a database that is over-indexed, i.e. there are sometimes 10-20 indexes on a table. The performance is at times not great due to blocking from long running queries. I want to clean up the indexes as a starting point.
Through a query I found some time ago on the SQLCat blog I have discovered a large number of indexes in the database that have a huge disparity between reads and writes. The range of difference is sometimes almost 2 million more writes than reads. Should I just drop the indexes that have say, more than 100,000 more writes than reads and then see what the Missing Index DMVs tell me after a few days of running without those indexes?
In some cases there are a few hundred thousand reads but maybe a million writes on the index. Thus, there are a fair number of reads happening, just not in comparison to the number of writes. In some cases there are almost no reads and a million or more writes. I am obviously dropping those indexes. I just am not sure what to do about the indexes that do have a fair number of reads.
View 9 Replies
View Related
Oct 18, 2007
I am looking into various options to improve latency of our application (we figured the latency is mainly because data persistence - writes and reads from DB). I am looking into In-Memory databases also. But, before making that decision (of using in memory databases), I would like to see if there is a way to configure SQL Server 2005 to get as close performance as in-memory databases?
My question:
1. Is there a way that I can configure SQL Server 2005 to use a CACHE that gets loaded as needed basis, so that future database reads/writes will happen to the cache as opposed to disk (db writes)?
2. Is SQL Server 2005 recoverable in such configurations?
3. Are there any ideas/resources where I can get more details? (Such as sample configurations with bench mark numbers, rpevious experiences..etc)
Thanks
Murthy
View 1 Replies
View Related
Jul 4, 2006
I need to write back to a legacy system in the form of flat file --the first row would be a header and the remaining rows would be the actuals rows of data--each field would have a column delimiter of , and a row delimter of CRLF.
The source is a SQL Server 2005 table.
Im looking for a good example of a script task in the dataflow section that writes to a file.
Can anyone show me the code how to do this or point me to a link.
thanks in advance
Dave
View 10 Replies
View Related
Aug 22, 2007
I am using an error handler that was provided to me from another source. However, I notice that there's something in the code that writes the error message twice. I tried to discover what it was, but could not seem to pinpoint it. Here's an example of what my email messages look like:
Is activity file current?
The Script returned a failure result.
The extracts in D:myFolder are not current! Data NOT loaded.
Is activity file current?
The Script returned a failure result.
The extracts in D:myFolder are not current! Data NOT loaded.
Obviously, I just want my email to read:
Is activity file current?
The Script returned a failure result.
The extracts in D:myFolder are not current! Data NOT loaded.
Somewhere, errorMessages is being written to more than once. Need help finding the error.
Thanks!
Here is the code from my Event Handlers:
OnError event:
Public Sub Main()
Dim messages As Collections.ArrayList
Try
messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)
Catch ex As Exception
messages = New Collections.ArrayList()
End Try
messages.Add(Dts.Variables("SourceName").Value.ToString())
messages.Add(Dts.Variables("ErrorDescription").Value.ToString())
messages.Add(Dts.Variables("scriptError").Value.ToString())
Dts.Variables("errorMessages").Value = messages
Dts.TaskResult = Dts.Results.Success
End Sub
On PostExecute:
Public Sub Main()
Dim errorDesc As String
Dim messages As Collections.ArrayList
Try
messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)
Catch ex As Exception
Return
End Try
For Each errorDesc In messages
Dts.Variables("emailText").Value = Dts.Variables("emailText").Value.ToString + errorDesc + vbCrLf
Next
Dts.TaskResult = Dts.Results.Success
End Sub
View 4 Replies
View Related
Apr 2, 2015
I built a SSIS(writing out to a flat file ) in 32 bit machine and it woks fine . But however when I deploy to the produciton server(64 bit) the SSIS writes out garbage data . After some research I found out that the problem with the 32 bit OS and 64 bit OS problem.What is my next step. Am I out of luck that now I will have to redesing the SSIS in 64 bit?
View 5 Replies
View Related
Apr 20, 2015
Im backing up to a network directory thats actually a mount point on a different server.My backup was slower than usual so i opened up perfmon to have a look.
When selecting the mount point from the Logical Disks section in perfmon i can see that writes/sec & write bytes/sec both show zero for a long period of time, even though the backup percent complete is increasing.Then all of a sudden the writes to the network share jump massively.
Is there some caching mechanism for backups in sql where during a backup data is only flushed to the disk periodically during backup?
View 1 Replies
View Related
Nov 12, 2014
I read , When sql server Database having multiple data files within single filegroup then sql server writes data in multiple proportional file algorithm where the amount of data written to a file is proportionate to the amount of free space in that file, compared to other files in the filegroup.
so if there is no filegroups created and multiple secondary files are attached in databse , is there same way data stored and writes data in multiple files by the same algorithm or any different way.
View 2 Replies
View Related
Mar 22, 2007
I am not sure if this is the correct forum but here goes!
We rebuilt our SqlServer 2000 Trans replication the other night. It used to run in 3 hours but now it runs in 9.5 hours (7 hours bcp out, 2.5 hours bcp in). We have a dedicated distributor box (4 processors), a 4 processor publisher, and a 2 processor subscriber. None of the systems exhibited any processor stress or unusual disk activity. The network tests OK (tested with file xfers). But the bcp's wrote data at 2.5 to 4 minutes per 100k rows, and they loaded the data at about 100k rows in 10 seconds or less.
As you know, Replication Snapshot uses bcp on each source table to build a collection of flat-files. Then it uses bcp to load those files into the subscribing tables. Because bcp is the workhhorse here, I decided against posting this in the Replication forum.
The only change I know of is increasing MaxBcpThreads from 4 to 8. This parameter specifies the number of bulk-copy operations that can be performed in parallel. I was thinking that 8 bcp's might somehow be killing the drive where all the bcp files are written.
Any ideas?
View 2 Replies
View Related
