Most Popular
1500 questions
32
votes
5 answers
List Jobs, Schedules, and Next Scheduled Run Datetimes
List Jobs, Schedules, and Next Scheduled Run Datetimes
I'm trying to list Jobs, Schedules, and Next Scheduled Run Datetimes. However, I don't appear to be succeeding.
sysjobactivity has next_scheduled_run_date, which is what I need, but I cannot…
Kennah
- 455
- 1
- 4
- 7
32
votes
3 answers
innodb_file_format Barracuda
I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda.
I was looking to play around with some compresses innodb tables. My understanding is this is only available…
atxdba
- 5,273
- 5
- 40
- 60
32
votes
3 answers
How to find the SQL statements that caused tempdb growth?
The tempdb of a server (SQL Server 2008) increases to 500GB+ several times every month. Is it possible to find out which SQL statements caused this problem? The problem is usually not caused by create table #temp...; insert into #temp... or select…
u23432534
- 1,535
- 4
- 20
- 31
32
votes
4 answers
How to add a column with a foreign key constraint to a table that already exists?
I have the following tables,
CREATE TABLE users (id int PRIMARY KEY);
-- already exists with data
CREATE TABLE message ();
How do I alter messages table such that,
a new column called sender is added to it
where sender is a foreign key…
Hassan Baig
- 1,959
- 8
- 29
- 41
32
votes
2 answers
What Problems Does an Eager Spool Indicate
Currently running on SQL Server 2008 R2
I am attempting to increase performance of an UPDATE statement. I notice an Eager Spool operation in the showplan popping up. My understanding of spooling operations is pretty basic - they create temporary…
Nick Vaccaro
- 603
- 3
- 9
- 16
32
votes
1 answer
Very strange performance with an XML index
My question is based on this: https://stackoverflow.com/q/35575990/5089204
To give an answer there I did the following test-scenario.
Test scenario
First I create a test table and fill it with 100.000 rows. A random number (0 to 1000) should lead to…
Shnugo
- 553
- 1
- 6
- 13
32
votes
1 answer
Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?
I have been playing with arrays in one of my PostgreSQL databases.
I have created a table with a geometry array with at least one element:
CREATE TABLE test_arrays (
polygons geometry(Polygon,4326)[],
CONSTRAINT non_empty_polygons_chk
…
Adam Matan
- 11,659
- 29
- 80
- 95
32
votes
1 answer
When to use sort_in_tempdb when rebuilding indexes?
We are debating whether to use the SORT_IN_TEMPDB option for our DW tables. My understanding is that there are more writes when using this option, although they are more sequential. We have a SAN (which has has been notoriously slow at times), so in…
Gabe
- 1,334
- 1
- 17
- 27
32
votes
1 answer
SET STATISTICS IO- worktable/workfile
I am executiong query, that produces plan:
Statistics IO:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 128,…
Jānis
- 1,248
- 2
- 13
- 21
32
votes
4 answers
Query the definition of a materialized view in Postgres
I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view:
SELECT * FROM information_schema.views WHERE table_name = 'some_view';
which…
Sean the Bean
- 580
- 1
- 4
- 11
32
votes
3 answers
Configuring PostgreSQL for write performance
One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed data for that particular instant. The data rate is…
Daniel Lyons
- 824
- 1
- 7
- 14
31
votes
5 answers
How can I change the default storage engine in phpmyadmin?
I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the default storage engine?
Kaji
- 937
- 1
- 9
- 16
31
votes
3 answers
GRANT USAGE on all schemas in a database?
I want to GRANT USAGE to a user/role for a given database. The database has many schemas.
I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but that's obviously wrong (it doesn't actually…
300D7309EF17
- 421
- 1
- 4
- 6
31
votes
3 answers
How to grant permissions on a table-valued function
Am I doing it right...?
I have a function that returns money...
CREATE FUNCTION functionName( @a_principal money, @a_from_date
datetime, @a_to_date datetime, @a_rate float ) RETURNS money AS BEGIN
DECLARE @v_dint money set @v_dint =…
Jack Frost
- 461
- 1
- 5
- 8
31
votes
2 answers
Using indexed views for aggregates - too good to be true?
We have a data warehouse with a fairly large record count (10-20 million rows) and often run queries that count records between certain dates, or count records with certain flags, e.g.
SELECT
f.IsFoo,
COUNT(*) AS WidgetCount
FROM Widgets AS…
Justin
- 920
- 1
- 7
- 11