%disk Time Opinion Requested
Mar 21, 2003
Trying to deal with a user complaint of slowness. Many variables looked at which look normal (Buffer cache, queue length,memory). Probably looks like a network issue. My question is what people consider acceptable when it comes to %disk time. My %disk time has increased from an average of 20% to 33% in recent months. My average disk read and average disk write have both been less than one. MY research has showed that more than 55% %disk time for ten minutes is considered a problem. Not there yet but seem to be slowly getting there. THe app running against my server is vendor written so can't change, also running log shipping which is probably inflating the numbers a little.
Any opinions appreciated.
View 1 Replies
ADVERTISEMENT
Feb 27, 2004
We are trying to restrict developer permissions in our development environment. One thought is to add developers to db_datareader, db_datawriter, db_ddladmin, db_securityadmin and then revoke various permissions from ddladmin and securityadmin. The goal is to allow developer to create stored procedures and assign permissions to the stored procedures.
Another option is to place all developers in the same role and ask them to create all procedures using that role name (ex: dev_role.sp_procedurename). By doing this each developer will be able to run stored procedures created by another developer. The down side is the permissions do not match Model Office/User Test and Production.
Any suggestions on how to handle this situation?
Thanks, Dave
View 2 Replies
View Related
Dec 23, 2004
I have about a 35000 record table. There are about 14 entries in this table that relate to "sections". Each of these sections can have up to 20 values. This lends itself to a design like:
big_table [one-to-one] big_table_has_sections [many-to-many] sections
or, more concretely:
BuildingConstructionType
relates to
ConstructionHasTypes
relates to
Types
Where BuildingConstructionType is one of 14 fields in the 35000 record "big" table, ConstructionHasTypes is the one-to-one intermediary relation that relates many-to-many with Types (the sections).
Unfortunately, with 35000 records, this big_table_has_sections seems like it might bloat. Is this a good solution?
View 2 Replies
View Related
Feb 19, 2008
Hi all,
I have a very simple time series model which processing works fine without any problem. However when I run the following query
SELECT
[TimeSeries].[PriceChange],
[TimeSeries].[Symbol],
PredictTimeSeries(PriceChange, -3, 2)
From
[TimeSeries]
WHERE
[TimeSeries].[Symbol] = 'x'
I get the following error:
TITLE: Microsoft SQL Server 2005 Analysis Services
------------------------------
Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process.
The following is the excerpt of the minding model script related to the two parameters:
<AlgorithmParameters>
<AlgorithmParameter>
<Name>MISSING_VALUE_SUBSTITUTION</Name>
<Value xsi:type="xsdtring">Previous</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_GAP</Name>
<Value xsi:type="xsd:int">1</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_COUNT</Name>
<Value xsi:type="xsd:int">10</Value>
</AlgorithmParameter>
</AlgorithmParameters>
These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?
View 3 Replies
View Related
May 26, 2004
I have a server with little control over most of the codeset and db design. Recently I have seen both the Processor - %Processor time and Processor - % User time go fom about 6.3 to about 24.3. The system queue length has also gone from about .2 to 1.1. In my humble opinion both of the are signs of a problem coming (luckily the cache hit ratio is still sitting at about 99%). I have been running profiler to catch the things that take more that 4500 MS, and I can probably tie the 2 together. Any opinions, or real world comparisons appreciated
View 3 Replies
View Related
Dec 28, 2006
hello,all
I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .
is it possible to do it ?
if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?
thanks in advanced!
View 1 Replies
View Related
May 29, 2008
Depending on the way I write a query, I come up with these 2 stats.
Is there a sure winner in this race, keeping in mind the overall health of the server?
(I'm not sure of the specs of the server, as I can't log on to it :/ but are there any sql variables that would show cpu speed and # of cpus?)
I almost am leaning towards the single cpu query because of lower resources used -
or are most of the "reads" in the parallel'd query not read directly from the HD, but using the Table Spool created internally (query plan shows it)?
CPU Reads Writes Duration
Parallel: 200k 3.2m 2400 62s
Solo: 79k 1.1m 600 79s
View 9 Replies
View Related
Sep 10, 2007
If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
View 1 Replies
View Related
Nov 13, 2007
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
View 16 Replies
View Related
Nov 15, 2006
Hello,
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
John
View 4 Replies
View Related
Feb 20, 2001
Hello,
this is my configuration :
1) 3 disks in RAID5 that hold the SQL data
2) 1 disk in RAID0 that holds the only paging file.
What will happen to the SQL data (DB) when the disk that holds the paging file crashes?
Kindest regards,
Luc.
View 1 Replies
View Related
Sep 15, 2005
I have 2 tables:
Table 1 has:ID,FName,Lname
Table2 has:ID,PID,PFName,PLName, Flag
PID is a unique number -> (hh:mm:ss)
I need to run an update to table2 by setting the Flag to 1 and also creating PID.
I could use a join query to do this, but I am concern about PID (hh:mm:ss), because it may take less then 1 second to update and I will have duplicate PID.
Any idea what is the best way of doing this?
View 1 Replies
View Related
Nov 14, 2007
Well, its kind of tricky, and i got into this.For example i have a column of zip, all US city state name and abbreviation, preffered name and an alternate name of each city in US, and also non-acceptable name of each preffered name..
Can you give me a hint, how many databse should i used?
for example, i search for a zip code, and the result will be the name of city or an acceptable name of city and there are non-acceptable name too..
well.i just need your opinion..hope you could get into this one.tnx
View 8 Replies
View Related
Nov 3, 2005
This is more a theoretical question so I do not have any DDL (working)to post.Let's say that I have a query which needs to be filtered for specificaccounts while also needing several joins to retrieve additional data.Is it better to so one big SELECT / JOIN / WHERE statement? As inSELECT * FROM T1JOIN T2 ON T2.[Col1] = T1.[Col1]JOIN T3 ON T3.[Col2] = T1.[Col2] AND T3.[Col3] = T1.[Col3]and so on...WHERE T1.[Account] IN ('123', '456', '789')OR is it better to do an inner SELECT / WHERE and pass that to a SELECT/ JOIN? As inSELECT * FROM(SELECT * FROM T1WHERE T1.[Account] IN ('123', '456', '789')) ITJOIN T2 ON T2.[Col1] = IT.[Col1]JOIN T3 ON T3.[Col2] = IT.[Col2] AND T3.[Col3] = IT.[Col3]and so on...First glance logic says that the inner select is the way to go sincethe joins would have less rows to work with, as opposed to joineverything and THEN pulling out what is not needed. But the queryplanner sometimes seems to have a mind of its own... Does it know thatrows will be pulled so it does that first? If I follow the same"structure" with many different queries does in us the same logic allthe time or do I need to try the same thing for each and check it?How does this apply to situations where there is a UNION involved? Do Ido the union and then apply WHERE and JOIN to filter out rows and getadditional data, respectively, or do I filter out rows inside the unionand take the combined set and do the JOINS?SELECT * FROM(SELECT T1.[Col1], T1.[Col2] FROM T1UNION ALLSELECT T2.[Col1], T2.[Col2] FROM T2) CTJOIN T2 ON T2.[Col1] = CT.[Col1]JOIN T3 ON T3.[Col2] = CT.[Col2] AND T3.[Col3] = CT.[Col3]and so on...WHERE CT.[Account] IN ('123', '456', '789')versusSELECT * FROM(SELECT T1.[Col1], T1.[Col2] FROM T1 WHERE T1.[Account] IN ('123','456', '789')UNION ALLSELECT T2.[Col1], T2.[Col2] FROM T2 WHERE T2.[Account] IN ('123','456', '789')) CTJOIN T2 ON T2.[Col1] = CT.[Col1]JOIN T3 ON T3.[Col2] = CT.[Col2] AND T3.[Col3] = CT.[Col3]and so on...
View 2 Replies
View Related
Mar 2, 2000
Hi,
I need your suggestions on a design issue:
I have 2 tables, Customers and Organizations. 1 Customer can be under many organizations. What would be the best way to design the db (2 choices) for performance (around 50000 customers):
1)
-Customer table
-Organization table
-Link Organization & Customer by creating a new tables with the following structure ---> CustomerID, OrgID,
2)
-A Customer tables that has a field called OrgID where the orgID is stored. If the customer has more organization related to him, we add another customer record and we put the new OrgID in the OrgID column. Examples:
Customer Table
--------------
CustomerID--Fname--Lname--Addresss-----OrgID
---------------------------------------------
1 Bob Marley 33 Africa org1
1 Bob Marley 33 Africa org2
1 Bob Marley 33 Africa org3
Please give me your suggestions,
Thank you for your time,
SASAN
View 1 Replies
View Related
Jan 7, 2004
What is the better practice when joining tables in a query example 1 or 2? I've always joined tables together like example 2, but it seems that many people are advocates of example 1. Are there any differences performance wise between the two?
Example 1:
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
ORDER BY a.au_lname DESC
Example 2:
SELECT *
FROM authors AS a, publishers AS p
WHERE a.city = p.city
ORDER BY a.au_lname DESC
View 9 Replies
View Related
Dec 18, 2007
I need to purchase a new computer for a small medical clinic which will basically only have one purpose: to answer to read and write queries to a SQL Server 2005 which is resident on that computer. Queries come from the current 8 stations (up to 14 stations in the future). Most of the time, only 3 stations will be active at a time. Queries are mostly to access patient file information, are not complex and are short-lived.
A friend of mine who owns a computer store just quoted me for a dual quad-core Xeon 5405 2GHz system with Windows Server 2003 10 Cals. I'm concerned about the following:
- What's the use in having 8 cores, each of them running at only
2GHz, when there's really only one service running (SQL Server
2005, likely Express Edition) on the computer. Does SQL Server
have the capability to make use of all cores? Otherwise, why
spend more for Xeon and so many cores instead of a single
C2D running at a faster speed of say 3GHz ?
- What would be the advantage of using a Windows Server over
Windows XP in a peer-to-peer configuration? I don't buy into
the 10 connection limit because the TCPIP.sys file can be
altered to move that limit up, so 14 stations does not trigger
the need for Windows Server in and of itself.
Thanks all!
View 5 Replies
View Related
Apr 13, 2007
Hi all,
I am having problem with SQL connection at Godaddy where my pool connection gets MAX OUT. When it happens, I cannot access the database.This is the thread about the problem: http://forums.asp.net/thread/1665023.aspx
I just created this with "THREAD". I hope someone who has experiences with thread can give me some advice about my design. This is my first time.
static object Locked = new object();
public object ExecuteCommand(string queryStr, string type){ //*************************************************************************************// // ExecuteCommand: Returns an object // //*************************************************************************************// Thread t = null; lock(Locked) { SQLString = queryStr; switch(type) { case "ExecuteNonQuery": t = new Thread(ExecuteNonQuery); break; case "ExecuteScalar": t = new Thread(ExecuteScalar); break; case "GetDataReader": t = new Thread(GetDataReader); break; } t.Start(); t.Join(); } return null;}
First of, does this work at all? It runs, but is it a good design in term of Thread? Since I use LOCK, do I still need the t.Join() function? The switch with the three cases, is that OKAY? Basically, I'm clueless. If you read my other post, you will get an idea what I'm trying to do. Any feedback would be very very appreciated.
Thank you.
View 2 Replies
View Related
Feb 7, 2008
Hi,
I was talking to my boss to day and our report request are not very consistant. We always having someone coming back to change something in our report. We were thinking of useing something called the Cube Analysis. Then it give our employees the raw data for them to run any standard query for themself. We have folks that want a report one way, but then they changed their minds and we are creating yet another report 4 or 5 times. what are your thoughts about this type of database?
View 6 Replies
View Related
Jun 4, 2007
What's the current opinion on UDTs? Are they valuable? Do the benefits outweigh the costs? Are they an absolute no-no? Has there been anything authorative or groundbreaking on the topic since Alex P's blog back in October 2005?
http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/20/428014.aspx
View 4 Replies
View Related
May 8, 2007
I need some other opinions on whether or not this is considered a proper database design structure.
Here is the relationship...We have PEOPLE, that each can belong to a COMPANY.
PERSON_TABLE
Person_ID
Company_ID
COMPANY_TABLE
Company_ID
Then each person can trust other people of other companies, but can only trust 1 person per company.
My question is this. In order to maintain a constraint of 1 person per company, is it considered OK to add a the redundant column Company_ID to the PERSON_TRUSTED_TABLE(and then creating a composite primary key on the Person_ID/Company_ID) instead of just adding a trigger to the PERSON_TRUSTED_TABLE to uphold the constraint.
PERSON_TRUSTED_TABLE
Person_ID
Trusted_Company_ID
Trusted_Person_ID
I would appreciate anyone's opinion. Thanks so much!
View 5 Replies
View Related
Apr 25, 2006
I am not that experienced in SQL 2005 and I'm trying to debug a stored proc (written by a far more experienced programmer which is why I'm appending here.)
At the top of the proc is the statement 'DECLARE @BillActualRoom bit'. If I understand correctly this is a local variable.
Later in the code we have the statement
'SELECT @BillActualRoom = 0 FROM BillingOptions WHERE CenterID = @CenterID'
where @CenterID is an input parameter.
The table BillingOptions does have a field called 'BillActualRoom' and it is a bit.
My question is this: Does this SQL statement make any sense at all? (My gut reaction is no but I'd like a second opinion. Can't get any consensus in the office.)
I would think that putting a local variable in like this would mean that @BillActualRoom is always equal to 0.
View 5 Replies
View Related
May 7, 2004
Hi all,
Ok here goes,
I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.
So my options as I understand it are:
Get Enterprise edition
or
Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.
I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.
Does anyone know how much performance increase I will get from using two arrays as opposed to one?
Any other advice on this scenario would be greatly appreciated.
Thanks
View 4 Replies
View Related
Jul 30, 2007
Hi,
I was having a conversation with another developer about the order that the join conditions are placed. More specifically, about the order the tables are referenced in the join condition. There are two ways to do it, for example:-- Here the table that is referenced first was "declared" first.
SELECT*
FROM
Logon
LEFT OUTER JOIN
Thread
ON Logon.LogonID = Thread.LogonID
LEFT OUTER JOIN
Message
ON Thread.ThreadID = Message.ThreadID
-- Here the table that is referenced first is the table being joined directly above it.
SELECT*
FROM
Logon
LEFT OUTER JOIN
Thread
ON Thread.LogonID = Logon.LogonID
LEFT OUTER JOIN
Message
ON Message.ThreadID = Thread.ThreadID
I realize this is not that big of a deal, but I was wondering if anyone had a good/valid reason for doing one versus the other.
Just curious,
-Ryan
View 10 Replies
View Related
Jul 20, 2005
i've a database where relations are hold in a special way which the projectleaders think of as "performant and uncomplicated" but which is veryquestionable to me:------------------------------------------------Table [Attributes]Fields [AttributeID] and [AttributeText]Table [Objects]Fields object stuff.... and [AttributeIDs] (varchar with 0-20 ids usually)in AttributeIDs there is a backslash separted list of Attribute-IDs like'3412278'so to get 20 object with a special attribute (which we need often) we doSELECT TOP 20 *FROM ObjectsINNER JOIN AttributesON (Objects.AttributeIDs LIKE ('%' + (CAST AttributeID AS varchar) + '\%'))ORDER BY ObjectTextps: to store data we need for communication we include a dozen of fields in*every* table and its content makes about 100 bytes/record------------------------------------------------i would do this stuff with a table to store the object/attributecorrelations.could someone tell me if that stuff makes any sense to an expert and how tovaluate it in regard of performance(we have big customers where that *is* anissue), design, scalability, pragmatism and sense ;)thanks in advance,ViperDK
View 1 Replies
View Related
Jul 1, 2007
Dear friends,
I need your feedback, tips and opinions to improve my dataflow described in my blog.
http://pedrocgd.blogspot.com/2007/07/ssis-dataflow-performance-i.html
Thanks!!
View 8 Replies
View Related
Apr 30, 2007
I inherited this website from a previous developer that uses an Access DB as a backend. Ugh. Apparently, he was extremely limited in what databases he could use. The website is filled with problems related to connecting to this database, which is no surprise. Most of them are related to permissions to the database file, multiple user access, etc. I am thinking about moving this to a SQL Server Express DB and upgrading the site to the 2.0 framework. Most of my experience is in SQL Server, but since they can't, or won't, pay for the SQL Server Database, is this a good solution? I've considered using MySQL as well, but their host won't provide support for this. Are there any other recommended alternatives?
View 2 Replies
View Related
Oct 26, 2007
Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005
I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..
View 2 Replies
View Related
Dec 14, 2006
Hi to everyone,My problem is, that I'm not so quite sure, which way should I go.The user is inputing by second part application a long string (let'ssay 128 characters), which are separated by semiclon.Example:A20;BU;AC40;MA50;E;E;IC;GREENNow: each from this position, is already defined in any other table, asa separate record. These are the keys lets say. It means, a have someproperities for A20, BU, aso.Because this long inputed string, is a property of device (whih alsohas a lot of different properities) I could do two different ways ofstoring data:1. By writing, in SP, just encapsulate each of the position separatedby semicolon, and write into a different table with index of device,and the position in long stirng nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3123 MZD12 XX77 .... any comment text124 MZD13 XY55 ... any other commentString data Tablefk_deviceId position value123 1 A20123 2 BU123 3 AC40.....123 8 GREENThe device table, contains also a pointer (position), which mightchange, to "hglight" specified position.Then, I can very easly find all necessary data. The problem is, I needto move the device record data (from other table) very often into otherhistory table (by each update). That will mean, that I also need tomove all these records from 1 -8 for example to a separate historytable, holding the index for a history device dataset. This is a littleinconvinience in this, and in my opinion, it will use to much storagedata, and by programming, I need always to shift this properities intohistory table, whith indexes to a history table of other properities.2. Table will be build nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3 stringProperty pointer123 MZD12 XX77 .... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2By writng into device table, there will be just a additional field forthis string, and I will have a function, which according to specifiedpointer, will get me the string part on the fly, while I need it.This will not require the other table, and will reduce the amout ofdata, not a lot ... but always.This solution, has a inconvinance, that it will be not so fast doing asearch over the part of this strings, while there will be no real indexon this.If I woould like to search all devices, by which the curent pointervalue is equal GREEN, then I need to use function for getting thevalue, and this one will be not indexed, means, by a lot amount ofdata, might be slow.I would like to know Your opinion about booth solutions.Also, if you might point me the other problems with any of thissolution, I might not have noticed.With Best RegardsMatik
View 7 Replies
View Related
Oct 18, 2006
My primary data files are ponting to F drive which is 200G and one db has occupied 90% of the space and the applications are not running. What is the best solution
View 1 Replies
View Related
Aug 13, 2007
Is there a way to know the tables the mostly requested in a database
How please?
Because we want to copy indexes from DB2 to SQl tables, but since there re so many tables in the DB we want to start with the most requested tables by the applications
Thanks
View 1 Replies
View Related
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
Dec 17, 2007
Hi,when i try to see the properties of database "sales" (sql server express 2005) in Management Studio Express, i get this error message: (besides, when i try to expand the database, i get the error that it's emty)Cannot show requested dialog.ADDITIONAL INFORMATION:Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)------------------------------An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)------------------------------The server principal "Myserveradmin" is not able to access the database "sales" under the current security context. (Microsoft SQL Server, Error: 916)But the webapplication still runs: i can insert records, update, delete ... from asp.net.Any way to recover it?ThanksTartuffe
View 4 Replies
View Related
