Hot answers tagged

10

Processing a cube largely consists of 3 steps, Getting the data building indexes calculating aggregations Step 2 and 3 are the least expensive (during processing) in my opinion so let's start with that. Building indexes does little more than calculating bitmap indexes for your attribute relationships. So depending on how many of those you have designed ...


8

It's not a bug. This behaviour is expected. If you need to increase the connections, just change the processes limit: SQL> show parameter processes processes integer 150 SQL> alter system set processes = <integer> scope=[...]; SQL> shutdown immediate; SQL> startup; -- Either using pfile or spfile, you'll ...


7

You could try --gdb. That will make it respond to SIGINT, though I'm not sure if that has potential to corrupt the database. --one-thread may also be of some help.


6

To me all these arguments are particularly invalid Ok. But they aren't to the rest of us. Why? Patch level consistency between development servers and production the patching would be proven out when an application progressed through Test Patching can fix stability and data corruption issues which would otherwise plague developers. It ...


4

BEGIN TRANSACTION SELECT * FROM YourTable WITH (TABLOCKX, HOLDLOCK) WAITFOR DELAY '00:05:00' -- 5 minutes ROLLBACK TRANSACTION And then in another Query Window: SELECT * FROM YourTable


4

This is pretty easy to do. Open two query windows in Management Studio. Run something like this in the first one (choose a test table that nobody is using, because this will block them, and make sure it has at least one row in it): BEGIN TRANSACTION SELECT * FROM sometesttable WITH (TABLOCK, XLOCK, HOLDLOCK) Then in the second query window: SELECT * FROM ...


4

The only way to find out what process caused the autogrowth is to use Extended events esp. EVENT --> sqlserver.database_file_size_change & sqlserver.databases_log_file_size_changed and ACTION --> sqlserver.sql_text. Looks like @DBA_ANDY already did the hard work of writing an XEvent -- Original Author : @DBA_ANDY http://nebraskasql.blogspot.com/2016/06/...


4

This is the query I am using to identify idle transactions holding locks. SELECT CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName ,s.session_id ,s.status ,s.login_time ,s.host_name ,s.program_name ,s.host_process_id ,s.original_login_name ,s.last_request_end_time ,CAST(t.text AS nvarchar(4000)) AS [text] ...


4

Non-committed transactions will be automatically rolled-back if the session is terminated. You can easily test this by: USE tempdb; CREATE TABLE dbo.TestTran ( ID INT NOT NULL CONSTRAINT PK_TestTran PRIMARY KEY CLUSTERED IDENTITY(1,1) ); BEGIN TRAN INSERT INTO dbo.TestTran DEFAULT VALUES; SELECT * FROM dbo.TestTran; Results ...


4

Don't be fooled by what you see. mysqld will not multiple instances on the same datadir and ibdata1. How do I know this? On Sep 26, 2014 I answered MySQL got signal 11 error and slave is down. In my answer, I mentioned how InnoDB Architecture only allows one mysqld process to open a Master Thread to ibdata1. Therefore, if you do not see the following ...


4

Without seeing any additional information, here is my best guess why MySQL is trying to shutdown for a long time: I suspect mysqld no longer has a socket file. About 1.5 years ago, I answered mysql restart issue after move database. I learned that mysqld depends on the presence of a socket file. If there is no socket, mysqld just draws lots of dots on the ...


4

Yes. All developers should have a local instance of SQL Server AND a shared SQL server instance. They exist for different purposes. Both are needed. Perhaps your current environment looks like this? Above, several developers are authoring changes and deploying them into a communal SQL Developer database. This is bad. Microsoft MVP Troy Hunt documents ...


4

In my server I have a SQL process that consumes CPU (~10%) but no one is using the server. There are many background tasks that run, regardless of user activity inside of SQL Server. The key is to understand if 10% CPU is abnormal. I have 2 virtual processors. You have 2 processors, 10% cpu is not a problem. The system, idling, is probably going to use ...


3

Conceptually speaking, you are on the right track. For an organization that has a mature development process / Software Development Life Cycle (SDLC) that includes source code control, continuous integration (CI), automated testing, and an IT group that knows how to manage various environments and workstations to keep them in synch regarding software and ...


3

It's a simple select. Why is this using fetch_cursor? The SELECT is system-generated by the Distributed Query framework, and is associated with the UPDATE you found. The Remote Update query plan operator uses the sp_cursor model to fetch rows from the remote data source. This is the cause of all the cursor API calls. I believe the cursor plan you show in ...


3

I'm not sure if Symantec still owns it, but Backup Exec is Netbackup's little brother. I don't remember the feature differences but Netbackup is more for Enterprise environments and Backup exec for smaller ones. Are you sure it's Backup Exec that's running DBCC? I've used Netbackup for SQL for many years and it's always done checksum checking during backup. ...


3

First you need to determine whether the blocking is a problem or not. Some applications are going to have numerous connections running INSERTS, UPDATES, or DELETES as fast as they can. This is by design (perhaps not good design), and as long as it's something that users are not impacted by, then you may see periods where there are a lot of blocked ...


3

EDU is an abstraction over the underlying OS scheduling unit, a sort of a surrogate key assigned to whatever is doing actual work in the OS. If the OS has true threads, distinct from processes, such as Windows, an EDU will map to a thread. On modern Linux, which doesn't have threads but rather lightweight processes (LWPs), an EDU would map to one of such ...


2

Setup SQL Server replication between the production servers and the reporting server so that the data on the reporting server is updated in real time automatically.


2

If installing the initscript and using the standard sudo service mysql start | stop aren't on the list of things you consider reasonable options, you could always compile your own version of mysqld with some custom hackery to the signal handling code in sql/mysqld.cc where it looks like some signals aren't defined yet, so presumably no-ops just waiting for ...


2

Oracle up to 11g used a thread based model on Windows and a process based model on Linux. Quote from the manual: A process is a mechanism in an operating system that can run a series of steps. The mechanism depends on the operating system. For example, on Linux an Oracle background process is a Linux process. On Windows, an Oracle background process is ...


2

DB Ghost DB Ghost is my favorite tool for managing databases. Benefits All of the objects in your database are stored as scripts in source control. You can script 'static data' (lookup table data) too. You can update source control manually or by scripting a 'model' development database. You can build a database (quickly) from the scripts in source ...


2

Uncommitted transactions are held open until the connection is terminated, and then they are rolled-back. So in connection-pooling scenarios, they will he held open longer since the connection has not terminated. For a SQL Agent job, it might not be using connection pooling, so the distinction between Session and Connection might not be obvious.


2

I've been using the following stored procedure (I can't even remember where I got the code from) for a long time. It allows you to start a job and waits for the job to complete before returning control to the user. You could stack 15 occurrences of this call in a tsql script CREATE procedure [dbo].[StartAgentJobAndWait](@job nvarchar(128), @maxwaitmins ...


2

Look for any lines in the PROCESSLIST with a Time higher than that one (1188). It probably indicates the villain. The owner of both rows may give a clue. A "metadata lock" involves something other than simple queries -- possibly ALTER TABLE or LOCK TABLES (as for mysqldump), etc. "Field list" sounds like probing information_schema for the columns in the ...


2

What is Symantec Backup Exec (TM)? Symantec Backup Exec is a backup software that works on many types of platforms in your environment. One of those features is to back up SQL Server Databases. It is running DBCC CheckDB on different databases and running Backup on different databases. Can anybody give some info on this? Best practice dictates that ...


2

Obtain this info from information_schema.PROCESSLIST. If your user have PROCESS privilege, and you need to view only your processes - use WHERE user = USER() filter. Link


2

Sadly, sometimes we get less than helpful information to deal with throughout our busy days. Q. What do Quantified Alerts tell us? That service A is doing or enduring something. That information is happening on a system. Q. What will it take to Qualify my alerts? Understand the difference between causation , correlation , unrelated events that a ...


1

This is something you quickly learn with GCP (CloudSQL), AWS (RDS), and all Cloud Providers of MySQL: Even root does not have all privileges. You could run SHOW GRANTS when logged in as root. Regardless of the privileges it says it has, the Cloud Provider will restrict it. You asked for a difference between root@localhost and root@% root@localhost will ...


1

Yes, it depends on your user count. But not only that, it also depends on which way your users connect to the database and the number of background processes your instance requires. There are basically two types of server processes that services user processes (client processes): dedicated server process, where there is an one-to-one mapping between user ...


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