2
votes
2answers
128 views

CPU or Page Reads

While comparing query performance should i go for CPU values or Page Reads. On live server (with better hardware) a query is showing different CPU when i test it on my local machine. But Page Reads ...
5
votes
2answers
1k views

Log Reader replication agents are constantly restarting

I am recreating (push) transactional replication from a SQL 2000 instance to a SQL 2008 instance which I had to break in order to do a db migration. The 2008 instance is both the distributor and a ...
3
votes
1answer
244 views

Why are my queries getting really slow?

I own a WordPress installation, and Updated to 3.4.1. After the upgrade, I experienced a very strange slow-down. After all, I used the define('SAVEQUERIES', true); in my wp-config.php with ...
3
votes
1answer
3k views

Setting up replication for MyISAM tables?

Before setting up a replication within MySQL, the data first needs to be transferred to the slave. Since MyISAM tables can be copied as files. Would it be okay to copy MyISAM files, .myd .myi .frm, ...
5
votes
2answers
276 views

Databases entering “In recovery” mode on restore

I am facing an issue while restoring the database. I have many dump files in a folder and I try to restore them one by one to a database xyz, perform some update operation and continue my restore ...
6
votes
1answer
3k views

Downsides to using SQL 2012 Upgrade as opposed to a fresh install?

I currently have SQL Server 2008 R2 which I would like to upgrade to SQL 2012. I can either un-install SQL 2008 and install a new copy of SQL Server or run the Upgrade. The upgrade is less work but I ...
3
votes
0answers
349 views

need help finding the details of a deadlock

We are having issues with deadlocks and I am trying to isolate what database object is causing the problem. I found Deadlock error isn't returning the deadlock SQL to be very helpful and I read ...
4
votes
1answer
134 views

Where to place non clustered index

Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
4
votes
1answer
2k views

InnoDB : Deadlock from one reader and one writer

This sort of has me miffed. One query is doing a select that includes a table that is undergoing a heavy UPDATE, but that update takes under 1/2 a second, and there are no other writers to that ...
16
votes
2answers
2k views

Why do wildcards in GROUP BY statements not work?

I am trying to make the following SQL statement work, but I get a syntax error: SELECT A.*, COUNT(B.foo) FROM TABLE1 A LEFT JOIN TABLE2 B ON A.PKey = B.FKey GROUP BY A.* Here, A is a wide table ...
3
votes
2answers
793 views

Extract query fingerprints and tables from large log files using pt-query-digest

I tried to parse a large log file using pt-query-digest but it gives "Out of memory!" when I try to read the file. I have 3GB RAM, 40GB empty space on HDD, the file is 20GB. I did not found anything ...
8
votes
2answers
1k views

When do I need to backup the Service Master Key?

I'm reading some documentation and whitepaper about Transparent Data Encryption. Some documentation mention to backup the Service Master Key as well (For clarification I'm not talking about the ...
2
votes
1answer
930 views

ON DELETE cascade alternate

I am trying to create a database for 2 Tables like: Table1 : tb1-id(pk) | tb2-id | other Table2 : tb2-id(pk) | other FOREIGN KEY Table2(tb2-id) REFERENCES Table1(tb1-id) ON DELETE ...
11
votes
3answers
12k views

Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?

I have a unique compound key like fr(fromid,toid) in the table, when I run the query with explain I get the following result: Impossible WHERE noticed after reading const tables` The query I ran: ...
1
vote
1answer
149 views

Reviewing all new MySql queries on staging environment

The problem I am trying to solve: It's well known that not all developers know what is EXPLAIN in MySql, and not every knows how to use it. Large part of them consider MySql as a black box, no matter ...
5
votes
1answer
155 views

Always being logged in as SA and not Windows account

First off i would like to appologise if i sound stupid as i do not have much knowladge of SQL Server. I have recently started a project working with SQL Server 2008 R2. I have access to the database ...
7
votes
1answer
532 views

What would be a good name for a job schedule?

I have encountered many different names for job schedules in SQL Server, ranging from the job name where it is used to a short recap of the schedule itself. I would like to give a clear, descriptive ...
3
votes
1answer
7k views

Is it possible to do a datapump import with a different user than did the export?

I have one db dump below is the log file for that dump Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** ...
5
votes
1answer
8k views

InnoDB - High disk write I/O on ibdata1 file and ib_logfile0

Server Specification: VPS with following info model name : Intel(R) Xeon(R) CPU E5649 @ 2.53GHz MemTotal: 2058776 kB MemFree: 244436 kB We are running IP.Board from Invision ...
7
votes
1answer
384 views

What does duration measurement in sql server actually measure?

What precisely does the query duration measure when conducting a profiler trace? I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to ...
4
votes
1answer
471 views

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
1
vote
1answer
65 views

What is really wrong with this query please? [closed]

SELECT CONCAT_WS(parent_title, parent_fname, parent_sname) AS 'Parent' CONCAT ('£1.50', sum(activity_price) AS Amount, COUNT(*) AS 'N.Activities' FROM Activities A, Parent P, Child C, Register R WHERE ...
3
votes
3answers
2k views

SQL Server 2005 Tools makes SQL Server 2008 Express install fail

When I tried installing SQL Server 2008 Express, the install failed due to this line. How can I circumvent this, or fix the issue?
7
votes
1answer
990 views

Service Broker: Queue Monitor is dropped after poison message

I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer. When I setup event ...
6
votes
2answers
4k views

Column definition for a SQL Server equivalent to Access BOOLEAN type

The BOOLEAN datatype in Access (ie, Jet/ACE) returns a -1 for True and 0 for False; the field is always required (i.e., it cannot be set to NULL). The SQL Server BIT type returns 1 for True and 0 for ...
4
votes
1answer
84 views

Is it possible to enlist a TSX to an MSX in a different domain?

In Domain1, I have an MSX with 75+ TSXes registered. Now I've been given some SQL Server instances in another domain. Is it possible to enlist Domain2 instances to an MSX in Domain1 ?
5
votes
3answers
2k views

How can you save all user-defined functions to txt files on your local hard drive?

I am running SQL Server 2008 and need to back up the UDF scripts/queries that I have created. How can this be done in a timely manner? I tried right clicking on the folder icon where the UDF are saved ...
19
votes
2answers
27k views

Is MySql's LAST_INSERT_ID() function guaranteed to be correct?

When I do a single row INSERT to a table that has an AUTO_INCREMENT column I'd like to use the LAST_INSERT_ID() function to return the new AUTO_INCREMENT'ed value stored for that row. As many SQL ...
4
votes
2answers
11k views

Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?

I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when ...
4
votes
3answers
2k views

Optimizing DELETE Query on MySQL MEMORY Table

I run a large forum which maintains a MySQL database for backend data storage. The 'session' table tracks logged in users & guests. It is currently about 100k records, so not really that large. ...
6
votes
2answers
16k views

How can I strip non-numeric characters out of a string?

Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type. One ...
2
votes
1answer
714 views

Inserting into three different tables, using foreign keys — 500000x each

We're working on refactoring many of our offline (python) scripts. I'm very new to any kind of SQL, but have been trying to learn as much as I can, especially in the performance field. We have a ...
3
votes
1answer
611 views

Connecting Linked Server

Hi I want to fetch data from MS SQL DB of a Remote server using Linked Servers. I have linked the servers. I am fetching data as follows. select * from [IP of server].[Database Name].[dbo].[Table ...
0
votes
1answer
130 views

Similar queries taking different times to complete

I had the following query: SELECT nvl(sum(adjust1),0) FROM ( SELECT ManyOperationsOnFieldX adjust1, a, b, c, d, e FROM ( SELECT a, b, c, d, e, SubStr(balance, ...
1
vote
0answers
205 views

ETL import more dimensions from only 1 column

I was requested at work to learn ETL. I have already created a database without using ETL with some dimensions and consolidations, etc, containing above 1 billion numerical values. Now I have to ...
2
votes
3answers
2k views

sort a varchar field numerically in mysql

I have the varchar field named _tid, normally which contains numerical field, but it is also possible that may be contain string field. Now i wanted to to sort the column by numerically rather than ...
7
votes
5answers
84k views

How to update one table based on another table's values on the fly?

I have a table in the name of ips as below: CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT ...
2
votes
1answer
1k views

Default value of serial fields changes after restore

Today I found a strange behavior after restoring a PostgreSQL database: the schema of all serial fields default values are trimmed out. For example: CREATE TABLE testschema.testtable ( id serial, ...
10
votes
1answer
5k views

Should I add SET NOCOUNT ON to all my triggers?

It is fairly common knowledge that you should have SET NOCOUNT ON by default when creating new stored procedures. Microsoft has changed the default template to include this in 2012. I thought ...
0
votes
1answer
3k views

Query to find and replace text in all tables and fields of a mysql db

I need to run a query to find and replace some text in all tables of a mysql database. I found this query, but it only looks for the text in the tbl_name table and just in the column field. update ...
2
votes
1answer
11k views

MySQL: InnoDB keeps crashing - how to recover?

# free -m total used free shared buffers cached Mem: 48289 35288 13000 0 347 30399 -/+ buffers/cache: 4541 43747 ...
-3
votes
1answer
2k views

Single query to copy data from 3 tables into a single empty table

In an interview today, I was asked if I could write a single query to copy data from 3 source tables to an empty destination table. I started saying "I will use a temp table or table variables", ...
2
votes
3answers
1k views

whether a big (but not used) ibdata1 slow anything down

Hi I have an ibdata1 that is around 10 gig. I have gradually migrated to 'file per table' using the innodb_file_per_table=1 option such that there are no big tables in ibdata1 anymore. I recently ...
3
votes
1answer
440 views

Oracle: Interval partitioning and dependant tables

I'm not a DBA, but I've been asked to look at a database which is expected to handle up to 80M records in one of it's tables. This table also has two tables linking to it via foreign keys with up to ...
2
votes
2answers
644 views

SQL Server 2008 R2 Dormant Users

I would like to know if Too many Dormant Users/sessions on a SQL Server instance can force SQL server to not to create new connections or give connection denied error to the application which is ...
5
votes
1answer
4k views

Why full-text-search returns less rows than LIKE

I don't get full-text-search working as I want it to, and I don't understand the differences in the resultlists. Example statements: SELECT `meldungstext` FROM `artikel` WHERE `meldungstext` LIKE ...
8
votes
2answers
1k views

Sql server 2008 filestream maximum number of rows

We are using filestreams in Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) and Windows server 2008 to store millions of files. Since there are millions of files all of them are grouped into 100 ...
3
votes
1answer
1k views

How to restrict multi-tenant MySQL database?

I have following table: Create table `webservice`( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `hostname` text NOT NULL, `portalid` int NOT NULL, `serviceid` int NOT NULL, `customerid` ...
2
votes
4answers
289 views

How does the database decide which Index to use

DDL create table t ( id int, id1 int ) create index Example_Index On t(id,id1) create index Example1_Index On t(id1,id) DML insert into t(id, id1)values(1, 100) insert into t(id, ...
1
vote
3answers
15k views

Shutdown one node in RAC

I want to shutdown one node in RAC. I did the following steps: Shutdown database instance export ORACLE_SID=mydb1 sqlplus / as sysdba shutdown immediate; shutdown ASM instance . oraenv +ASM1 ...

15 30 50 per page