Most Popular
1500 questions
20
votes
1 answer
Postgres: check disk space taken by materialized view?
I know how to check the size of indexes and tables in Postgres (I'm using version 9.4):
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
WHERE…
Richard
- 343
- 1
- 5
- 11
20
votes
2 answers
Why did creating this new index improve performance so much when existing index included all columns in new index?
I have Log and LogItem tables; I'm writing a query to grab some data from both. There are thousands of Logs and each Log can have up to 125 LogItems
The query in question is complicated so I'm skipping it (if someone thinks it's important I can…
Nate
- 1,727
- 4
- 22
- 33
20
votes
2 answers
Why adding SQL Server user to "Perform volume maintenance tasks" improves the speed of database resizing so much?
If I want to create 5GB database with
CREATE DATABASE [test]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'test', FILENAME = N'E:\2012\test.mdf' , SIZE = 5529600KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME =…
Nikolay Kostov
- 321
- 1
- 3
- 10
20
votes
5 answers
Infrastructure for Highly Concurrent, High Write DB
My requirements are:
3000 Connections
70-85% Write vs Read
Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of concurrent connections as the memory is fine. The write…
Justin
- 301
- 2
- 3
20
votes
4 answers
What is the Query to display the failed sql jobs
What is the Query to display the failed sql jobs,so that I can get all the failed job information with one query
Avi
- 317
- 1
- 3
- 6
20
votes
2 answers
Scaling PostgreSQL TRIGGER(s)
How Postgres triggers mechanism scales ?
We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s).
Basically we would like to create a TRIGGER for each table we want to be…
Ugo Matrangolo
- 301
- 1
- 2
- 3
20
votes
2 answers
How to measure or find cost of creating a query plan?
I have a typical case where parameter sniffing causes a "bad" execution plan to land in the plan cache, causing subsequent executions of my stored procedure to be very slow. I can "solve" this problem with local variables, OPTIMIZE FOR ... UNKNOWN,…
Jeroen
- 909
- 2
- 14
- 34
20
votes
4 answers
Why are NULLs sorted first?
Why is it that when we have a NULL value in a column and we order by the value ascending, the NULLs are sorted first?
select 1 as test
union all
select 2
union all
select NULL
union all
select 3
union all
select 4
order by test
results…
Richard
- 1
- 8
- 42
- 62
20
votes
3 answers
Cascade primary key update to all referencing foreign keys
Is it possible to update a primary key column value with cascading the update among all the foreign keys referencing it ?
# EDIT 1:
When I run followinq query
select * from sys.foreign_keys where referenced_object_id=OBJECT_ID('myTable')
, I…
mounaim
- 659
- 5
- 13
- 26
20
votes
5 answers
How to recursively find gaps where 90 days passed, between rows
This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this.
SELECT SomeId, MyDate,
dbo.udfLastHitRecursive(param1,…
Independent
- 213
- 2
- 10
20
votes
8 answers
Professor told us to store serialized Java objects as blobs instead of defining relational tables
Instead of actually defining a tables with the correct attributes, my professor told us we could map objects to ids like this:
id (int) | Serialized Object (blob)
1 10010110110
I can see so many problems with this; data…
Tyler Davis
- 301
- 1
- 2
- 6
20
votes
1 answer
How to index a query with `WHERE field IS NULL`?
I have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, setting uploaded_at to current date.
How should I index the…
Kirill Zaitsev
- 303
- 1
- 2
- 7
20
votes
2 answers
What are database statistics, and how can I benefit from them?
I've heard mention of statistics that SQL Server keeps by default. What are they tracking, and how can I use this information to improve my database?
goric
- 1,646
- 3
- 18
- 25
20
votes
3 answers
Does "WHERE 1=1" usually have an impact on query performance?
I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language).
Generally speaking, does this addition to a SQL statment…
transistor1
- 333
- 1
- 2
- 8
20
votes
2 answers
Detecting the locked table or row in SQL Server
I'm trying to understand/learn how to track down the details of a blocked session.
So I created the following setup:
create table foo (id integer not null primary key, some_data varchar(20));
insert into foo values (1, 'foo');
commit;
Now I…
user1822