Hot answers tagged

44

Yes, it's possible There are two settings mentioned in the docs (idle_in_transaction_session_timeout is new to version 9.6x) statement_timeout (integer) Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If log_min_error_statement is set to ERROR or ...


7

majority OLTP type workload with some OLAP mixed in This is the problem, not the CXPACKET. Parallelism is a symptom, not a cause. Your 'some' OLAP workload is doing scans which trigger parallelism, which cascades into exchange wait times, likely buffer pool pollution and possible blocking (OLTP workload blocks behind OLAP scans). If the OLAP workload is ...


7

Is there any way to track the connections opened and relate it to a connection pool created from database side. Set the Application Name in your config and you can monitor from the server side in sys.dm_exec_sessions. You'll get a separate Connection Pool for each Connection String, so if you set the Application Name in the connection strings you can tell ...


7

There are two type of timeouts. One is when the client application (API) aborts the batch after a certain amount of time. Called "attention" in a SQL server trace. Default in many APIs is 30 seconds. The other is when the client requests that SQL server abort the query with error 1222 after a certain time. SET LOCK_TIMEOUT. Default is indefinitely. ...


6

The Cardinality Estimation logic was updated for SQL Server 2014 and could potentially be a reason. You would have to test the same queries with the old cardinality estimator and compare performance metrics. You could do this by lowering the compatibility level to <120. I would perform all this testing on a test server and not in production. https://...


6

There are some undocumented trace flags that allow one to increase or remove some of the limits that the optimizer uses when determining whether a "good enough" plan has been found. However, all of the results I've ever seen show that plan quality is only minimally improved, if at all, when using these flags. There is a good reason the timeouts and limits ...


5

A few possibilities: Your application closes connections without telling the PostgreSQL server it's doing so, by simply closing the network socket. It should send a PQclose(conn) if using libpq, or whatever equivalent your language/driver has. Network address translation timeouts on idle connections or other related network issues cause the TCP connections ...


5

Not out of the box. It would be pretty easy to compile a custom server that ignored your settings, though. But much more likely is that your connections are being severed by a firewall or gateway that doesn't like idle connections. If you have access to the server's log file, that should give a good clue. If the client says the server closed the ...


4

We have identified the root cause: it's the innodb_autoinc_lock_mode = 1 . Here is the summary from the official doc: 0: traditional lock mode, provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics. 1: consecutive lock mode: In this mode, “bulk inserts” use ...


4

I finally find out the problem. After days of straggling I checked the SQL Server report and noticed that there were very large occasions of autogrowth on temp db database. The cause of the problem was, after the database migration to our new server the system database file size was not configured and tempdb had a way to less file size than what it supposed ...


4

You are essentially attempting to dump the entire oplog and hence paging in all of it, not just the most recent data. That is going to cause a lot of IO, especially when your oplog is too large to fit into memory. On a smaller database it's not a problem because the oplog is relatively small. The reason why it's dumping the full table is because I believe ...


4

As mentioned, the CXPACKET is often a misleading wait type in sense that high values are not always bad, and quite often these high values are just the indicator but not the real problem. So, examining other wait types that are accompanying the CXPACKED would be a good start point in troubleshooting. It is advisable to check the following: Check the Cost ...


3

First, find the person who put that job in place and have him/her tied to a chair listening to cats scratching a chalk board while you go for lunch. When done with that, compare your resource utilization and performance data for extended periods before and after the job runs. I suspect you'll see perf dips and a number of resources getting really busy ...


3

As at MongoDB 3.4, the $maxTimeMS value is a cursor option that needs to be set by the client/driver making the request. There is currently no equivalent server configuration directive, but there is a relevant feature request to watch/upvote in the MongoDB issue tracker: SERVER-13775: maxTimeMS on an instance/database level. There are some potential ...


3

So we decided to increase database timeout for queries globally. But I have this feeling that this is wrong. Yet I'm not able to bring forward any reasons. Is it bad to increase timeout everywhere? Why shouldn't we do this? The default client API query timeout of 30 seconds is appropriate for a typical OLTP workload, albeit arbitrary. The value ...


3

SQL Server cluster is a fault tolerance solution it has nothing to do with performance. If you want your load on sql server to be shared , you should be looking at Replication or maybe log shipping, direct reporting calls to that replicated /log shipped server and keep application calls to the main server. If your data gets too large that no matter what ...


3

Some notes on MaxTimeMS behaviour as at MongoDB 2.6: MaxTimeMS sets a cumulative time limit in milliseconds for processing operations on a cursor. Network latency and idle time for a cursor do not count toward MaxTimeMS. After the MaxTimeMS is reached, the operation will be killed at the next safe interrupt point (for example, when that operation yields). ...


3

The underlying OS is capable of detecting all the above mentioned possible disconnects and break the communication channel (TCP, net pipe etc). This will result in the rollback. Don't try to outsmart this. Of course, one could ask why not use a reliable queueing communication channel (eg. MSMQ) submit the entire order in one call rather than 100 calls ...


3

There are a number of ...timeout... parameters that can be configured for a MySQL instance. Here are a few selected parameters (Ver. 8.x) for your convenience: Reference: 5.1.7 Server System Variables (MySQL | Documentation) interactive_timeout The number of seconds the server waits for activity on an interactive connection before closing it. An ...


2

Awaiting command one of the reasons can be your stored proc waiting for user input.To deal this further ,when the issue reoccurs get the spid and see dbcc inputbuffer(spid).Try to analyze the query and see where it can wait for user input and further how can be that optimized.if you can add some queries here,we can try to help.


2

Sounds like a poor performing query that is crossing over the default 30 second timeout for .net connections. SQL Server will process a query for as long as it takes, but the application is deciding to stop the show after 30 seconds. You should fire up SQL Profiler and capture long running transactions (ones that have duration > 1 second) and ...


2

AFAIK there is no way in TSQL to deal with this other than the way you currently are with XACT_ABORT. There are no structures that TSQL knows (or even cares about) as everything this deals with lives outside of TSQL. What is actually happening is called an "Attention" event which could range from a connection closing to an actual cancel (for example the ...


2

First of all there is noting like query timeout, it's "remote query timeout". Increasing it affects remote query execution (query executed by remote database server using linked server). By default it's 600 sec. Timeout period of the query directly executed on database server depends on session timeout, you configured in your application. Limiting query ...


2

Timeout settings are a configuration of the client connection, so are either set by the client application, or via the connection string. Look at how your applications is instantiating the database connections, or perhaps in your application config files that control how those connections are created. But increasing the timeout is just treating the symptom. ...


2

If it's the first UPDATE that times-out then I'd look closer at your physical reads. If [staffid] isn't indexed then you'll be performing a full table scan and pulling the entire table from the IO subsystem through to the buffer. After the first UPDATE most of the table's pages will be in the buffer and subsequent UPDATES will be able to perform logical ...


2

The code doesn't have a specific Time-out set in the Connection String to the Database but on the SQL Server, the Time-out is set to 10 minutes. You are most probably reaching a client timeout which by default is 30 seconds for C# SQL Server client (ADO.NET?). Consider increasing this timeout until you find a decent solution that does not force the ...


2

You mention two key things in your post: Mirroring Work is being performed against the failover-server I suspect what is going on is actually expected behavior. Mirroring connection strings define both a Server (e.g. the Principal Server) and a Failover_Partner (e.g. the Mirror Server). When establishing a connection to a mirrored database, if a ...


1

That timeout can be configured with the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter. For example, place the below in sqlnet.ora, which results in outbound connections timing out after 10 seconds without answer: SQLNET.OUTBOUND_CONNECT_TIMEOUT=10


1

By default SQL Server instances don't listen for TCP/IP connections, so verify that this is enabled: https://technet.microsoft.com/en-us/library/hh231672(v=sql.110).aspx https://stackoverflow.com/questions/2388042/connect-to-sql-server-2008-with-tcp-ip Also you need to make sure that firewalls between your client and the server are not blocking the ...


1

Maybe there could be a few 'master' APIs, each doing some subset of the 250-300 inserts? For ACID, each transaction requires a disk write -- that is what is limiting your speed. Not knowing details of your SQL, I can only guess at things to help: Batching INSERTs into a table (multiple rows in a single transaction). Each of the 25 threads does only one (...


Only top voted, non community-wiki answers of a minimum length are eligible