| Author |
Topic |
|
Rauken
Posting Yak Master
108 Posts |
 Posted - 2008-03-25 : 10:12:07
|
| 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. /MagnusJesus saves. But Gretzky slaps in the rebound. |
|
|
CShaw
Yak Posting Veteran
65 Posts |
Posted - 2008-03-25 : 11:50:43
|
| When you say the writes are not committing, does this mean your users are sitting there waiting for the 15 min and the transaction is still sitting open? I am thinking out loud here, but is there a chance that your recovery interval is set at 10 or 15 and what you may be seeing is the check point occurring and not the actual commit? I would verify you recovery interval.If you could give us a little more information on what the users sees and what you see that believes that a write is taking 10 to 15 min.Thanks,Chris Shawwww.SQLonCall.com |
 |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2008-03-25 : 15:52:31
|
| SQL Server buffers writes in memory. Checkpoint operations, which can be initiated by a variety of things, flush dirty pages to disk. If by writes you are refering to rows showing up for other users, they should see them as soon as the transaction is committed reguardless if a checkpoint is issued. As CShaw mentioned, you should be more clear about what the term "writes" is refering to. It sounds like you are starting a transaction and not issuing a commit, only to have the data committed automatically once the connection is closed.- Eric |
|
|
|
Rauken
Posting Yak Master
108 Posts |
Posted - 2008-03-25 : 17:32:15
|
| Thank you for your answers. As I said I'm not at all into the "core" of sql server. I thought when running an insert statement it would automatically show something else than 0 in the "writes" cell. When I create orders it runs maybe 40 insert statements but nothing in the writes cell. After 10 minutes all these 40 insert statments are shown in the writes cell. stephe40 - What you say is very interesting. I know they've had huge problems with connection timeouts because of the application ran out of connections due to not closing connections. It might be connected to this, I'll check it out tomorrow.Thanks!Jesus saves. But Gretzky slaps in the rebound. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-25 : 23:29:11
|
| Do they run 40 inserts in a transaction? If so, you may don't see them until transaction committed. |
|
|
|
Rauken
Posting Yak Master
108 Posts |
Posted - 2008-03-26 : 05:45:21
|
| Sorry there is no transaction handling involved. Should the insert show up in "Writes" right away then?Jesus saves. But Gretzky slaps in the rebound. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-26 : 05:53:42
|
You mean WRITES column in SQL Profiler?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Rauken
Posting Yak Master
108 Posts |
Posted - 2008-03-26 : 07:31:33
|
| YesJesus saves. But Gretzky slaps in the rebound. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-26 : 07:42:52
|
WRITES column in SQL Profiler has nothing to do with INSERTs.Profile this simple codecreate table #test (i int)insert #test (i) values (99) You will see that no writes occur!WRITES column is about writing temporary data/records to TempDB.This often happens when joining two BIG tables and SQL Server has to store the HASH JOIN values somewhere during operation, for example.See this blog entry http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/12/11/492.aspx
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
Writes are not commited right away