Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
29
votes
1 answer

Is sys.stats_columns incorrect?

Let's say I have a table Foo with columns ID1, ID2 and a composite primary key defined over ID2, ID1. (I'm currently working with a System Center product that has several tables defined this way with the primary key columns listed in the opposite…
James Lupolt
  • 4,258
  • 5
  • 27
  • 45
29
votes
3 answers

Proper Use of Lookup Tables

I'm having trouble figuring out exactly how to place good boundaries for when and where to use lookup tables in a database. Most sources I've looked at say that I can never have too many but, at some point, it seems like the database would be broken…
Brad Turner
  • 293
  • 1
  • 3
  • 6
29
votes
2 answers

In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?

Update 2014-12-18 With the overwhelming response to the main question being "No", the more interesting responses have focused on part 2, how to solve the performance puzzle with an explicit ORDER BY. Although I've marked an answer already, I…
JohnnyM
  • 625
  • 7
  • 10
29
votes
4 answers

Using SQL Profiler on a database that's in production

As a developer, I use SQL Profiler quite often. It's a good debugging tool, both to track what my code is doing and to analyse performance problems. But I've always used it on my development environment, and in a very controlled way. Start my…
Andrew Shepherd
  • 533
  • 1
  • 5
  • 11
29
votes
2 answers

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f dumpfile My problem is that the database contains…
Marcus Junius Brutus
  • 3,309
  • 7
  • 29
  • 44
29
votes
8 answers

Query to compare the structure of two tables in MySQL

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version). Can you think of a query that can compare two tables? Here are some example tables that you can…
sjdh
  • 747
  • 3
  • 8
  • 10
29
votes
2 answers

How to handle bad query plan caused by exact equality on range type?

I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the actual results are extremely different from those…
abeboparebop
  • 481
  • 3
  • 11
29
votes
4 answers

What is the use of "Backup set will Expire:" option while creating Backup Database task in Maintenance plan

While backing up the database, I would like to use "Backup set will Expire:" option to delete/overwrite old database backups. I am not able to use this option. Any advice on how to use "Backup set will Expire:" options while creating Maintenance…
Vijred
  • 447
  • 1
  • 4
  • 8
29
votes
2 answers

How do I find a long running query with process ID, process name , login time, user , start time and duration?

Can anyone help me to find a below given details for long running query. ProcessID, process name, database, host ,user ,process login time, query start time and query duration. I am looking for a query or an SP which gives me this data.
user3598321
  • 393
  • 1
  • 3
  • 5
29
votes
4 answers

When to use CDC to track history?

SQL Server Change Data Capture is a feature that reads historical data from the SQL Server transaction logs and stores them in a special table. Through the use of special table value functions (TVF) it then allows the user to query this data, making…
magnattic
  • 469
  • 2
  • 6
  • 17
29
votes
2 answers

How to know when/if I have too many indexes?

Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement..."). From my understanding every added index can make an SQL SELECT query faster but also…
Uwe Keim
  • 887
  • 2
  • 13
  • 24
29
votes
3 answers

Best practices for backing up a MySQL DB

I've recently discovered that our production web servers that run off MySQL are not being backed up regularly (or at all). I'm used to backing up SQL Server DB's but don't have a ton of experience with MySQL DB's. Any best practices for using…
Valien
  • 745
  • 3
  • 7
  • 14
29
votes
1 answer

mysqldump with INSERT ... ON DUPLICATE

I want to merge data from one database to another. So I create dump with mysqldump and then import it to another database (with same tables structure). I don't have any problems (such as duplicate entries or something else) in this case. But I do…
Xupypr MV
  • 465
  • 2
  • 5
  • 8
29
votes
5 answers

How can I tell WHY an insert on a certain table is slow?

I know that an INSERT on a SQL table can be slow for any number of reasons: Existence of INSERT TRIGGERs on the table Lots of enforced constraints that have to be checked (usually foreign keys) Page splits in the clustered index when a row is…
BradC
  • 9,964
  • 8
  • 48
  • 88
29
votes
2 answers

How to create an index to speed up an aggregate LIKE query on an expression?

I may be asking the wrong question in the title. Here are the facts: My customer service folk have been complaining about slow response times when doing customer lookups on the administration interface of our Django-based site. We're using Postgres…
David Eyk
  • 527
  • 1
  • 5
  • 11
1 2 3
…
99 100