Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
24
votes
3 answers

Is the overhead of frequent query cache invalidation ever worth it?

I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that are being executed on many of the tables. What I'm…
Craig Sefton
  • 395
  • 1
  • 3
  • 7
24
votes
4 answers

Merge statement deadlocking itself

I have the following procedure (SQL Server 2008 R2): create procedure usp_SaveCompanyUserData @companyId bigint, @userId bigint, @dataTable tt_CoUserdata readonly as begin set nocount, xact_abort on; merge CompanyUser with…
Sako73
  • 435
  • 3
  • 5
  • 8
24
votes
2 answers

Get query plan for SQL statement nested in a PL/pgSQL function

Is it possible to get the query plan for an SQL statement executed inside a user defined function (UDF)? Like with using EXPLAIN in pgAdmin or psql as client. I see the UDF abstracted away into a single operation F() in pgAdmin. Currently, I pull…
Hassan Syed
  • 417
  • 1
  • 3
  • 9
24
votes
2 answers

How to change the instance name of SQL Server Express 2008?

On my development machine I've installed SQL Server Express 2008, but it has the instance name .\SQLEXPRESS. I want it to be the default instance. I'm pretty sure that I selected the "Default Instance" option in the setup, but apparently it didn't…
Vilx-
  • 724
  • 1
  • 5
  • 12
24
votes
4 answers

Enable TLS 1.2 for SQL Server 2016 database mail

I have been puzzled by this issue for almost 1 week. Hopefully someone in our community has experienced the same issue and already found a solution. So here is my problem: As per our company policy, we want database mail to be able to send emails…
jyao
  • 3,043
  • 1
  • 11
  • 26
24
votes
4 answers

If a database only ever has one insert, is it bad to index every possible column combination?

I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is probably a better way to design a system like this,…
Lopsided
  • 365
  • 2
  • 7
24
votes
5 answers

Suppress SQL Server Management Studio auto complete

Is there a way to temporarily suppress SQL Server Management Studio's auto-complete while typing a query? I don't want to disable auto-complete completely just say hold down some key while typing in a particular word so that it doesn't get in the…
Jack
  • 341
  • 1
  • 2
  • 5
24
votes
2 answers

Do I need separate indexes for each type of query, or will one multi-column index work?

I somewhat know the answer to this question already, but I always feel as though there is more I need to pick up on the topic. My basic understanding is that generally speaking, a single index that just includes all the fields you might be…
Andrew Barber
  • 397
  • 3
  • 12
24
votes
2 answers

What to set innodb_buffer_pool and why..?

I have 170GB of InnoDB index and Data. I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB. So what should be the optimal size of innodb_buffer_pool. UPDATE we are going to…
Abdul Manaf
  • 9,677
  • 16
  • 71
  • 84
24
votes
3 answers

How do I install pgcrypto in PostgreSQL 8.4?

I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto first. But I don't know how to install it. There is no…
Jonas
  • 32,975
  • 27
  • 61
  • 64
24
votes
2 answers

PostgreSQL: Immutable, Volatile, Stable

I'm unclear about the true meaning in the definitions for IMMUTABLE, VOLATILE and STABLE functions. I read the documentation, specifically the definitions of each. IMMUTABLE indicates that the function cannot modify the database and always…
Brooks
  • 531
  • 3
  • 6
  • 14
24
votes
2 answers

Is it possible to give the optimizer more or all time it needs?

Given that the optimizer cannot take all the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off. I was wondering if this can be overridden so that you can…
buckley
  • 582
  • 3
  • 12
24
votes
1 answer

Why doesn't this query use an index spool?

I'm asking this question in order to better understand the optimizer's behavior and to understand the limits around index spools. Suppose that I put integers from 1 to 10000 into a heap: CREATE TABLE X_10000 (ID INT NOT NULL); truncate table…
Joe Obbish
  • 32,165
  • 4
  • 71
  • 151
24
votes
3 answers

Why is 'LOAD DATA INFILE' faster than normal INSERT statements?

I've read an article that mentioned we can achieve 60,000 inserts per second by using the LOAD DATA IN FILE statement, that reads from csv files and inserts the data into a database. Why should it differ from normal inserts? EDIT: I reduced the…
Alireza
  • 3,636
  • 10
  • 37
  • 44
24
votes
2 answers

What does [FROM x, y] mean in Postgres?

I'm just getting started with Postgres. Reading this document I came across this query: SELECT title, ts_rank_cd(textsearch, query) AS rank FROM apod, to_tsquery('neutrino|(dark & matter)') query WHERE query @@ textsearch ORDER BY rank DESC LIMIT…
andrerpena
  • 961
  • 2
  • 9
  • 12
1 2 3
…
99 100