Most Popular
1500 questions
26
votes
1 answer
Are there any risks to granting users SQL Server SHOWPLAN permission?
I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to understand the performance of individual queries…
RaoulRubin
- 969
- 2
- 10
- 12
26
votes
3 answers
Backup/Restore Users/Passwords/Privileges
I am moving from one server to another and I want to backup all databases + users/privileges/passwords from my MySQL Server. I found to backup a database using mysqldump, but I cannot figure out, how to backup all users and the given privileges. Is…
Nidhoegger
- 363
- 1
- 3
- 7
25
votes
3 answers
How to promote an existing index to primary key in PostgreSQL
I know how to make a primary key within a table, but how do I make an existing index a primary key? I'm trying to copy an existing table from one database to another. When I show the table, the index at the bottom is in this form:
"my_index"…
WildBill
- 525
- 1
- 7
- 15
25
votes
4 answers
Transactions within a Transaction
What behaviour would PostgreSQL display if for example the script below were called
BEGIN;
SELECT * FROM foo;
INSERT INTO foo(name) VALUES ('bar');
BEGIN; <- The point of interest
END;
Would PostgreSQL discard the second BEGIN or would a commit be…
Alex
- 355
- 1
- 3
- 5
25
votes
7 answers
SQL Server not starting at Windows startup but starts manually
My SQL Server instance's (SQL Server 2008 R2 Express) service has Startup Type = Automatic. The past couple of times I've restarted my computer the service has failed to start on its own, but it starts just fine when I manually start the…
Keith
- 535
- 3
- 6
- 13
25
votes
2 answers
How to generate a sequence in mysql
Consider this table in mysql
create table numbers (number int);
insert into numbers values (3), (2), (9);
select * from numbers;
+--------+
| number |
+--------+
| 3 |
| 2 |
| 9 |
+--------+
Is there a simple query to generate a…
sjdh
- 747
- 3
- 8
- 10
25
votes
1 answer
FlushCache messages appearing in log at specific times
We've been having a lot of database performance issues lately, and I've been trying to see if I can figure out why. We don't have a DBA (I'm a software developer) so I'm sort of just winging it, and much of what I find online reads like a foreign…
eddie_cat
- 559
- 2
- 5
- 11
25
votes
4 answers
How do you identify InnoDB table corruption?
I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and restarting replication, I'm getting InnoDB crashes with…
randomx
- 3,934
- 4
- 30
- 43
25
votes
3 answers
How do I best measure the query performance?
I have 2 stored procedures, where the second stored procedure is an improvement of the first one.
I'm trying to measure by exactly how much that is an improvement.
Measuring clock time doesn't seem to be an option as I get different execution…
bjnr
- 413
- 1
- 4
- 10
25
votes
5 answers
SQL server 2012 restore wizard freezes
I have a development laptop with SSMS Express 2012 with a 2012 db instance and a 2008 db instance. Have been using this configuration for over a year. Suddenly I am unable to use the restore wizard. The wizard will pick the backup file but when I…
Pat
- 351
- 1
- 3
- 3
25
votes
1 answer
Suppressing the SSMS prompt to save changes
Is it possible to configure SQL Server Management Studio (SSMS). so that the dialog box shown above does not appear when I close an unsaved query window?
Ario
- 1,042
- 4
- 13
- 27
25
votes
3 answers
Index on Persisted Computed column needs key lookup to get columns in the computed expression
I have a persisted computed column on a table which is simply made up concatenated columns, e.g.
CREATE TABLE dbo.T
(
ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,
A VARCHAR(20) NOT NULL,
B VARCHAR(20) NOT NULL,
…
GarethD
- 683
- 7
- 13
25
votes
2 answers
SQL Server Join/where processing order
After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be as small as possible before joining (inner joins…
Stuart Blackler
- 4,542
- 7
- 29
- 43
25
votes
3 answers
How to optimization database for heavy I/O from updates (software and hardware)
The situation
I have a postgresql 9.2 database which is quite heavily updated all the time. The system is hence I/O bound, and I'm currently considering making another upgrade, I just need some directions on where to start improving.
Here is a…
Niels Kristian
- 951
- 3
- 15
- 23
25
votes
2 answers
Need to understand parallel query execution error
Today we experienced a degradation in performance on our production sql server. Durring the time this occurred we logged several "The query processor could not start the necessary thread resources for parallel query execution" errors. The reading…
Lumpy
- 2,109
- 8
- 32
- 43