|
My guess is that you have automatic growth turned on. The stall happens when you run out of capacity in the database and the server grows the data file.
In any production server you should dedicate a disk (array) to the database, set the file to the entire size of the disk (array), and turn off automatic growth. You should then monitor the usage of the file to determine when to expand onto more disks, and to ensure that your archiving and cleanup solution - you do have one, right? - is running sufficiently frequently.
Further, you should dedicate another disk (array) to the transaction log in the same way. Ensure that no other I/Os target this drive - for best performance, you need the disk head to remain in the correct position for writing the next log record. You should ensure that you back up the transaction log regularly so that there is spare log capacity. Not backing up the log will eventually cause the database to fail as there is no more space for the log to expand into. For a production system I would advise NOT using the Simple recovery model - this model does not offer recovery to point of failure in the case of a lost or corrupted data file. If you're going to use a RAID array for failover, stick with RAID 1 (mirroring) - you get no performance benefit with striping. Particularly avoid RAID 5 which has nasty overhead.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thanks for the tips, I'll look into them.
Merry Christmas!
|
|
|
|
|
Hi,
If any one knows of a good SQL Server 2005 tuning book please let me know. Any 3rd party tools will also be appreciated. There is a tool that comes with SQL Server 2005 for tuning, is this any good?
Thanks
Brendan
|
|
|
|
|
|
Hi,
I am using SQL Server 2005. And I want to know when is it more appropriate to use a smaller data type like smallint over int? Will there be a performance increases by any chance?
I see everyone uses int for everything. I have a sequence field, which sorts my category according to the sequence. Would smallint be fine or should I just stick to int?
The same goes for varchar and nvarchar. What type of data does nvarchar cater for? My website will just be in English, should I stick with nvarchar or change it to varchar? Is there a performance between the two?
I hope to hear as many opionions as possible.
Brendan
|
|
|
|
|
This article[^] outlines the key differences between the smallint and int datatypes. I doubt that there is a noticable difference in performance between the 2 datatypes. You should only consider using smallint if you are sure you will not need more than 2^15 unique values. In a commercial application this would probably be insufficient.
Regarding varchar and nvarchar, again MSDN[^] outlines the key differences.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I have the following VB code that works very well. One problem. I don't have any results coming back to tell me if it was successful. If I copy the code from the stored procedure and executed it in Query analyzer it displays results showing how many rows I updated. How do I get the results to a textbox I have setup on my windows form? 
Below is the code I use:
Dim sqlCN As SqlConnection
Dim sqlCM As SqlCommand
Dim strSql As String
Dim strCN As String
strCN = "data source....."
sqlCN = New SqlConnection(strCN)
sqlCN.Open()
strSql = "MY_STOREDPROC"
sqlCM = New SqlCommand(strSql, sqlCN)
sqlCM.ExecuteNonQuery()
sqlCN.Close()
Lost in the vast sea of .NET
|
|
|
|
|
From the MSDN documentation for the SqlCommand.ExecuteNonQuery[^] method.
Executes a Transact-SQL statement against the connection and returns the number of rows affected
You should populate your textbox with the return value from this method.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
That works well... but...
I have several selects in my stored procedure. I guess this will only report the last count? I guess my solution is to split up my stored procedures if I want to verify counts from each select.
Thanks for the help! 
Lost in the vast sea of .NET
|
|
|
|
|
KreativeKai wrote: I have several selects in my stored procedure. I guess this will only report the last count?
Correct.
KreativeKai wrote: I guess my solution is to split up my stored procedures if I want to verify counts from each select.
Not necessarily. Parameters to stored procedures can provide output as well as input. You could define an output parameter for each record count, set the values of these parameters in your stored procedure and retrieve the results in your .NET code. This article[^] provides an example.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hello everybody.
I'm updating an application which makes use of a trigger to maintain relationships between some tables in the database. I was unable to figure out how this could work with a simple constraint, so I thought I would go for a good olde' trigger. 
Here's the deal:
I have two tables: A and B, which both depends on information in table C. Now table C contains some binary data, and some information on how this binary data relates between rows in table A and B.
If a row in table A is deleted, all references to that row in table C must also be deleted. The same thing goes for table B. Since entities in table A and B can have the same primary key, the table C use the primary key and a char to determine whether the primary key belongs to table A or B. Therefore I thought a constraint would be out of the question.
The trigger problem:
The problem is the "DELETED" logical table. As one might have guessed, it only works when there is one row in the DELETED table. The system allows the users to delete multiple rows, so I will have to construct the trigger to maintain relationships for all the rows in the DELETED table.
I would most definitely like to avoid using a CURSOR on the DELETED table.
How do I do that?
(Should anyone be able to see how I could construct a constraint for the job, I would also like to know how to do that)
Thanks for any help you can provide!
modified on Monday, March 10, 2008 4:58 AM
|
|
|
|
|
If I understand you just need to delete all rows in table C related to the rows being deleted in A or B. C uses a sort of composite key - Primary + TableLetter to reference either table. So why can't your trigger do something like:
DELETE FROM C WHERE EXISTS (SELECT * FROM DELETED WHERE C.ID = DELETED.ID) AND C.Letter = 'A'
or
DELETE C FROM DELETED
INNER JOIN C ON C.ID = DELETED.ID
WHERE C.Letter = 'A'
|
|
|
|
|
Thank you! It worked perfectly!
modified on Monday, March 10, 2008 4:58 AM
|
|
|
|
|
By default SQL Server 2005 creates new database files in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA folder. What I would like to do is set the default folder to D:\SQLDatabases.
Can anyone tell me if this is possible and if so, how?
Thanks
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
|
I have a project done in ASP.NET with SQL Server 2005. It's ecommerce application, and product id's using GUID column. What would be the advantage for this GUID over identity columns ? I think both are unique, then what would be the difference ?
|
|
|
|
|
N a v a n e e t h wrote: I think both are unique, then what would be the difference ?
The difference is that GUID's are assured to be unique across all space and time. And unlike an Identity int column, GUID's wont run out of range. But the disadvantage being that they're large in terms of storage and consequently, any indexes on them will be large and slow.
If you think that your application will need an unique key across all machines, use GUID. It's mostly used when you're using multiple instances of SQL Server and data needs to be consolidated in the end.
Regards.
|
|
|
|
|
There is another reason for using a GUID - unlike an identity, you can control the assignment, so you don't need to retrieve it and pass it back out to the calling application.
|
|
|
|
|
Pete O'Hanlon wrote: unlike an identity, you can control the assignment
Thanks pete. Can you please make it more clear ? What do you meant by "controlling assignment" ?
|
|
|
|
|
It means that you can actually assign the value yourself. For instance, if your calling code was in C# you will do this using:
Guid myGuid = Guid.NewGuid();
|
|
|
|
|
GUIDs are nice for replication. You can create a new GUID and know that its not in use - which can be easier than asking the database for the next identity value.
You can also assign a new GUID in the field, bring the record back and not have to change the primary key.
Apart from this minor inconvenience they are big, ugly, and a pain to debug.
MyBusinessObjects.PurchaseOrder#849 is a lot easier to look at than MyBusinessObjects.PurchaseOrder#aaA-aaaAAr-RRRR-GH834989-3849.
Don't use them unless you can forsee a need to.
Edit: You said ecommerce - accountancy folks and auditors love sequential numbers on things. If you can't give them a sequential invoice number, they'll probably make you stick another column on anyway.
|
|
|
|
|
Thanks mark. Thank you very much
|
|
|
|
|
I'm trying my hand at using LINQ to SQL as a data source for a little pet project. My main requirement is to capture an invoice, and for this I have a simple master-detail screen with an InvoiceBindingSource and an InvoiceLineBindingSource. The invoice source binds to several textbox edit fields etc. and lines source to a DataGridView. Now when I try and insert a second invoice record, I get a primary key violation because the invoice id 0 already exists. How do I get LINQ to recognise the identity column?
|
|
|
|
|
Sorry, that was a brain fart. I hadn't made the field IDENTITY in the DB, so the O/R designer didn't set the Auto Generated Value flag.
|
|
|
|
|
Hi, I have this error when doing 'sum' on table,
whereas I convert it fist before doing 'sum'.
here is my code:
sum(convert(decimal(5,5),value)).
After I look the data 'value', I found that there is
char '%', here is my table:
'value'
2.131 %
2.412 %
2.612 %
So any suggestion? or is there a code for mid(string,start,length) in sql
Thanks for your help.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
