I/O Component Bottleneck Theory
Jul 20, 2005
I understand that there is much involved in figuring up I/O
throughput, but I'm hoping the answer to the following question will
be a simple A, B OR C.
Given the configuration below, what hardware component would
bottleneck first.
A. Drive Spindle Throughput
B. SCSI Controller
C. Other Component(if so what component?)
Configuration:
Controller: Ultra320
RAID Config: RAID 5
Drives on RAID: 14 - 73gb/15k - SCSI Drives
Type of Writes: Random
Question Background:
I currently have the configuration above on a local box. Our company
has ordered a SAN and and has space set aside. What I'm trying to
acertain is whether or not I'd be better off staying local or if
putting my DB on a SAN would be more beneficial.
Other Notes:
I've determined using a formula that determines the max theoretical
I/O operations/sec for the # of drives (spindles) and RAID Config...
but I've not been able to establish a relationship between
Operation/sec -> MB/Sec. I'm sure this is for obvious reasons...
If anyone has had a related scenario, I'd be interested in hearing
your thoughts.
Thanks,
Ornac
View 9 Replies
ADVERTISEMENT
Mar 18, 2004
Hi,
We have been getting really bad performance from one of our databases recently.
This is a three tier system with two services (.dll) connecting to the database, we have only experienced problems since the second service has been installed.
These two services both use the same user and make about 300 connections to the database.
I have noticed IO bottleneck when the system is under load, and the Average disk queue at this time is 15.
How do I fix this? We have the logs and data on the same raid 10 array.
View 10 Replies
View Related
Feb 15, 2005
I am trying to figure out if we have a memory bottlneck on our SQL Server. We have a large database (90 GB) on Standard Edition of SQL 2000. Currently we have 2 GB of Memory. To me this seems low already, but Standard edition can't handle more than 2 GB anyway. Our cache hit ratio is very low (40%). This is also telling me we need memory. The thing that gets me is we still have 147 MB of memory available. If Memory really was the problem wouldn't that 147 MB be used?
Thanks much.
View 14 Replies
View Related
Mar 29, 2007
Hello,
I have an SP that transfers data from one DB to another.
As I run the SP with the management studio - it takes about 15min. But when I scheduled it as an SQL agent job it ran all night and never completed.
The difference I noticed was that the % disk time (of the destination DB) was very high (~100) when I used the agent (which never happened when I ran the SP with the studio).
Only when I restart the SQL service, the disk gets back to normal % disk time.
What could be the reason for that?
My server - Windows 2003 R2 SP1 / SQL 2005 SP2
p.s - could it be related to SQL SP2 ? I didn't have this problem with SP1, but then - I had many other changes since.
View 3 Replies
View Related
Aug 6, 1999
Good morning,
I've just updated my databases to V7.0 from 6.5. I am having some issues with CPU utilization after rebuilding the databases. I rebuilt the databases from scripts after modifying the scripts to deal with any SQL issues between the versions.
Symptoms:
When a bunch of stored procedures are executed the % Processor Time his 100% on a single processor system, the Processor Queue Length hits 13, the Batch Request/Sec starts at 20 and rises to 47 before dropping off, Context Switches go from 370/sec to about 833/sec before dropping off and there is an increase in page faulting.
My testing was done on a single processor but my real system is dual-300+ with 128Mb RAM. The database is only 40Mb and after running the index wizard there were no changes advised.
After reading a lot in help areas for MS and here I am not sure if this has to do with how SQL Server runs now (i.e. system configuration) or if it is a query issue with parallelism. I'm assuming query parallelism since the performance spikes occur when stored procedures are run. On a single processor is there a way to address this. Then, hopefully, I can deal with the real multiprocessor system.
Thanks much,
Don
View 1 Replies
View Related
Oct 26, 2007
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
View 7 Replies
View Related
Jan 23, 2007
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
View 4 Replies
View Related
Aug 17, 2007
How do I select * remaining Records of a table that don't fall true to the 2nd Select statement?
Select * From zz_2007_Booth_Visitors
NOT IN
Select * From zz_2007_Booth_Visitors
Where [Product Interest - Actuators] Is Null
And [Product Interest - Other Actuator] Is Null
And [Product Interest - Chillers] Is Null
And [Product Interest - Other Chiller] Is Null
And [Product Interest - Electronic Products] Is Null
And [Product Interest - Other network interfaces] Is Null
And [Product Interest - Fittings] Is Null
And [Product Interest - High Vacuum] Is Null
And [Product Interest - Other high vacuum actuators] Is Null
And [Product Interest - Pick& Place and Transfer] Is Null
And [Product Interest - Teflon Products] Is Null
And [Product Interest - Training] Is Null
And [Product Interest - Valves& Manifolds] Is Null
View 4 Replies
View Related
Oct 31, 2001
Hello,
I am having a problem with my team mates believing that primary keys need to be unique. I have the following data listed below and I am getting a violation of a primary key constraint. The primary keys for this table are request, business_req_id and test_case_id. I beleive the error is happening due to the data not being unique. Am I correct?
Thank you.
Anita
Request BUSINESS_REQ_ID BUS_TEST TEST_CASE_ID
B0K0037A1 1.1 1 7/31/01 0:00
B1C0015A4 4.1 1 8/8/01 16:53
B1C0015A2 2.1 1 8/8/01 16:53
B1C0015A1 1.1 1 8/8/01 16:53
B1C0015A3 3.1 1 8/8/01 16:53
View 1 Replies
View Related
Oct 31, 2001
Hello,
I am having a problem with my team mates believing that primary keys need to be unique. I have the following data listed below and I am getting a violation of a primary key constraint. The primary keys for this table are request, business_req_id and test_case_id. I beleive the error is happening due to the data not being unique. Am I correct?
Thank you.
Anita
Request BUSINESS_REQ_ID BUS_TEST TEST_CASE_ID
B0K0037A1 1.1 1 7/31/01 0:00
B1C0015A4 4.1 1 8/8/01 16:53
B1C0015A2 2.1 1 8/8/01 16:53
B1C0015A1 1.1 1 8/8/01 16:53
B1C0015A3 3.1 1 8/8/01 16:53
View 4 Replies
View Related
Nov 20, 2006
I have always heard that much of t-sql is based on "set theory". I had Set theory in high school and I remember it as being simple Unions, Intersections, Differences of Sets. By a Set I mean a collection such as {2,5,7,8,9, ...) That could well described a single row in a table. By unioning several of these rows we could end up with a table.
But how does that relate to t-sql such as
select * from <table name> Where <condition 1> ?
Is it simply that the result returned by the query is a Set? (if so, a Set is simply being used as a synonym for a Collection. No set theory involved.)
TIA,
barkingdog
View 1 Replies
View Related
Jun 28, 2006
I have been tasked with creating a Data Warehouse.
Problem is that old storage vs reporting debate.
I have determined that the data that I will recieve and store will be like follows (simplified form) for expandability
KEY FldKEy FldData DateTime AuditTrail
Daily I will use this data based on use input process this data into the following format and say
if fldkey/ flddata open a cycle.
populate row with null close date
if fldkey/ flddata closes cycle
update row with date
If fldkey/ flddata changes a cutable value
update row
if fldkey/ flddata changes a cutable value (type 2 table)
insert a row into detail update value and obsolete previous row.
KEY DateStart DateEnd FLDDATA1 FLDDATE2 Op_Cl_IND HEADER Record
KEY EFFdate OBSDATE FLDdata3 FLDData4 Detail Records
KEY EFFdate OBSDATE FLDdata3 FLDData4
KEY EFFdate OBSDATE FLDdata3 FLDData4
Problem: FLDKey is a finite count however the max is undefined.
IS there any way to solve the problem of not being able to nail down users to tell you what they want to cut by. What I have been instructed by mgr (old IDMS) is that they wish to see all on the FldData and have the ability to cut by all of it. However the Flddata could be anything (cannot be indexed).
400,000,000 rows at least.
Do I need to nail the users down or am I am missing something.
Sorry if so cryptic
:(
View 14 Replies
View Related
Oct 25, 2004
Hi, our application is failing sometimes, with some select queries. After making traces in the database, I found the following error: Missing join predicate.
I googled that, and I only found this useless tip:
Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. The fix to this is to add a join predicate.
So, I dont know what a join predicate is... maybe I used it, but I don't know it by that name.
Thanks!
View 9 Replies
View Related
Jun 17, 2006
I'm struggling to find anywhere some recommended techniques for synchronizing two databases.
I am writing a smart-client app that will have its own database, and then connect and synchronize with the server when available.
Can somebody send me some links that discusses some of these ideas in detail?
Thanks
View 1 Replies
View Related
Jun 14, 2004
My co worker designed a database where retail items can be placed in multiple catagories. This seems odd to me..... In general, Isnt it more normal than not to be only one catarory for each item? For example, lets say I was selling a bowling ball with a picture of Mickey Mouse on it. I can then find this item in the "Mickey Mouse" catagory or in the "bowling ball" catagory but in the database the bowling ball has only one catagoryID. When I worked for a multi-million dollar corporate retail store , an item was listed once in only one catagroy. But i am sure items can be viewed
I know there isnt a single rule, I am just looking for a solution. How should the database sturucture be built with this in mind starting out with what is listed below???? Mabey an attributes table?
Items
ItemID ItemName CatagoryID
Catagories
CatagoryID CatagoryName
View 6 Replies
View Related
Mar 6, 2007
Hi all,
I have a general theory question for best practices about upsizing an msaccess 2003 split database design to use SQL server instead of the .mdb for data storage.
My data has grown close to msaccess limit, and ive started experiencing lost connections and corruptions frequently. So the next step is to upsize to SQL.
So far DTS seems to do a better job with bringing in the tables and data (then the upsizewizard).
Does any one have a suggestion on how to deal with the front end connecting the sql backend ?
i understand an ADP project file uses OLE connection, is that a better solution then linked tables directly into SQL threw ODBC ?
Reason i ask is the linked tables seem to not break as many things inside the code.
thanks for any suggestions,
jon
View 4 Replies
View Related
Nov 4, 2015
I like writing concise and compact sql code without cursors if possible. My current dilemma has me stuck though.I have 3 tables, but one of them is optionally used and contains a key element of TimeOut to determine which Anesthesia CrnaID to use. It is the optionally used part that has me stumped.
Surgery table
CaseID
Patient
(Sample data: 101,SallyDoe 102,JohnDoe)
Anesthesia table
CaseID
CrnaID
(Sample data:
101,Melvin
102,Bart
102,Jack)
AnesthesiaTime table (this table is optionally used, only if the crna's take a break on long cases)
CaseID
CrnaID
TimeIn
TimeOut
(Sample data:
102,Jack,0800,1030
102,Bart,1030,1130
102,Jack,1130,1215)
Select Patient INNER JOIN Anesthesia produced too many case results. So, I figured out there is an AnesthesiaTime table that only gets used if the anesthesia guys take time-outs. That doesn't happen all the time. I could use TOP 1 on the Anesthesia table, but technically I need to read the AnesthesiaTime table and locate the last time and pull that crna, Jack. I'm not sure how to deal with an optional table. I believe the IF Exists will be pertinent, but not sure of how to build this query. I've tried subquery without success.
View 2 Replies
View Related
Mar 16, 2007
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
View 3 Replies
View Related
Aug 13, 2007
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
View 4 Replies
View Related
Mar 30, 2006
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
Does anyone have any suggestions?
TIA . . . Ed
View 7 Replies
View Related
Nov 27, 2007
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain
Public Sub Main()
Try
Dim xmlDoc As New XmlDocument
xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up"
Catch ex As Exception
MsgBox(ex.Message)
UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
Try
Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
Dim rd As StreamReader = New StreamReader(fileName)
Dim xml As String = rd.ReadToEnd()
Xml = Xml.Replace(invalidChar, String.Empty)
xml = xml.Replace("", String.Empty)
xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
xml = xml.Replace("]]>]]>", "]]>")
MsgBox("replaced")
rd.Close()
Dim wr As StreamWriter = New StreamWriter(fileName)
wr.Write(xml)
wr.Close()
Dim xdoc As XmlDocument = New XmlDocument()
xdoc.Load(fileName)
Catch ex As Exception
UpdateXML(fileName, ex.Message)
End Try
End Sub
End Class
View 4 Replies
View Related
Apr 21, 2006
1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,
2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.
Thanks in advance.
View 1 Replies
View Related
Nov 26, 2007
How can we get the name of the component inside the Data Flow Task . What I want is to log error stating which component in the data flow task has failed. Package and Data Flow names I am getting from system variables. I want to log like the Execution Result screen with Name of the component and [its id].
Like "Derived Column[216]" has failed with some error
It is possible?
View 8 Replies
View Related
Nov 8, 2007
I'd like to incorporate the "package component tree" UI component that is used within the Visual Studio designers into an SSIS utility I'm building. This is the one I'm talking about:
Edit: Apparently using the IMG tag on these forums works in the editor preview, but not in the actual posts, so I've replaced images with links...
Example 1
Example 2
I've done some searching online, but have not found any information about where this UI is implemented, or if it is reusable. Does anyone here know if it is possible to re-use this component in a .NET application?
Thanks in advance!
View 5 Replies
View Related
Apr 15, 2004
Hi, all.
I create a report using VB datareport.
but, when I call following statement.. it cause Error "Class not registered!"
cnn.Open "Provider=MSDataShape.1;Persist Security Info=False;User ID=" & gstrID & ";Password= " & gstrPassword & ";Initial Catalog=TimeClock;Data Provider=SQLOLEDB.1" & _
"Data Source=" & gstrLocalDBServer & ";server=" & gstrLocalDBServer
How can I solve this problem?
View 1 Replies
View Related
Nov 16, 2007
Ok. I have 2005 standard with sp2 installed. Went to create a maintenance plan and low and behold the component is missing. Ok.. no big deal, i'll just add it. Of course you know this is the error I get.
To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
Ok.. doesn't seem too bad. So I go to a dos prompt. d: oolssetup.exe skuupgrade=1
Same error.
Help please :)
View 1 Replies
View Related
Dec 6, 2007
Hi,
While using 'Script Component' in SSIS, how to use a user-defined variable created at the package level in the script?
View 2 Replies
View Related
Sep 5, 2007
Hi,
I'm trying to add an icon to a custom component but its not happening and I can't figure out why. I'm sure I've managed to do this in the past. Here's my DtsPipelineComponent attribute:
Code Snippetnamespace Jamiet.Ssis
{
[DtsPipelineComponent(
DisplayName="MyComponent",
Description="Does some stuff",
ComponentType=ComponentType.Transform,
NoEditor=true,
IconResource="MyComponent.ico"
)
MyComponent.ico is stored in the same folder as my project (i.e. at the same level as the .cs file and the C# project file).
In the application property page I've pointed the icon resource at MyComponent.ico
Everything compiles OK. But I see no icon in my component when I deploy it to the toolbox or use it in a package.
I've also tried changing the attribute to this:
Code Snippetnamespace Jamiet.Ssis
{
[DtsPipelineComponent(
DisplayName="MyComponent",
Description="Does some stuff",
ComponentType=ComponentType.Transform,
NoEditor=true,
IconResource="Jamiet.Ssis.MyComponent.ico"
)
Again, it compiles OK, but no icon!
Any ideas what I'm doing wrong?
Thanks
Jamie
View 5 Replies
View Related
Feb 17, 2006
Hi all,
I'm creating a custom interface for reporting services but I am having a few problems. This is my environment:
Using the ReportViewer component in remote mode to show my reports of the report server. There are 2 user levels of which any user can be apart of, Admin & user. In every report, there is a parameter called MERCHANTID, when the user who logs in (via custom login interface) is of group admin, then the merchantid parameter gets prompted before the report is run, if the group of the user is "user" then the merchantid parameter is hidden, and is passed programatically to the report. The report path also
My Questions:
1. When my reportviewer component loads up the report and prompts for a parameter, when you select a parameter value or type it in, it just posts back with no results, just the parameter prompt again with no data... How can this be corrected?
2. How do I pass report parameters programatically... so that when my user id of group "user" then "merchantid" gets passed automatically, but if there is any more parameters, then that gets prompted...
Thanks for your help.
View 11 Replies
View Related
May 31, 2007
i have 2 source columns testsource and testsource1 and 2 output columns
test and test2
i am passing them through a script component to check if the columns are numeric or not along with some more logic...I am able to get the first column evaluated based on the logic but not the second column
Is it that the script component can only look at one column?
this is the code i wrote
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If IsNumeric(Row.testsource) Then
Row.test = Left(Row.testsource, 4) + CStr("/") + Mid(Row.testsource, 5, 2) + CStr("/") + Right(Row.testsource, 2)
Else
If Row.testsource = "N.A" Then
Row.test = "NULL"
End If
Row.test = "NULL"
End If
If IsNumeric(Row.testsource1) Then
Row.test2 = Left(Row.testsource1, 4) + CStr("/") + Mid(Row.testsource1, 5, 2) + CStr("/") + Right(Row.testsource1, 2)
Else
If Row.testsource1 = "N.A" Then
Row.test2 = "NULL"
End If
Row.test2 = "NULL"
End If
End Sub
End Class
example of run
INPUT
,20070930
,20080331
,20070930
,20071130
,20070930
,
,
,
20070504,20070503
20080331,
,20070930
N.A. ,N.A.
N.A. ,N.A.
OUTPUT
NULL,20070930
NULL,20080331
NULL,20070930
NULL,20071130
NULL,20070930
NULL,
NULL,
NULL,
2007/05/04,20070503
2008/03/31,
NULL,20070930
NULL,N.A.
NULL,N.A.
as you can see the first column gets evaluated based on logic but the second column just gets passed through..
Thanks for any help in advance
smathew
View 4 Replies
View Related
Dec 3, 2007
Hi,
I have an XML data file and an associated XSD file with properly defined datatypes. However, the datatype of all the data elements are always "string" datatype. For example, in my current xml file, all the data elements are of Decimal datatype which is properly defined in XSD file. However, datatype of all the output columns are of string datatype.
Is it a bug or am I doing something wrong?
Thanks
Navnish
View 1 Replies
View Related
Apr 28, 2008
Exists any easy way to renew component GUID without having to recreate the component?
View 6 Replies
View Related
