Create a user account login that cannot login from SQL Server Management Studio Can we create a new user that can access the database, but cannot access it via SQL Server Management Studio (SSMS)?

I need this to create a "safe" connection to the database through VBA of Office application, but in case the username and password is leaked, users still cannot connect through the SSMS. I made the user as data reader only, but for peace of mind, I want to make sure users cannot access it via SSMS, so that unwanted viewing of data may be prevented.

I'm using SQL Server 2008 R2. Please kindly help or share if there are other ways to do this.

This is what an Application Role is meant for.

  An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles work with both authentication modes. Application roles are enabled by using sp_setapprole, which requires a password. Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest. Therefore, any database in which guest has been disabled will be inaccessible to application roles in other databases.


CPU impact, Scan count 9, logical reads 1686, physical reads 0 vs Scan count 1, logical reads 10253, physical reads 20 I have some 3rd party software that is doing so often the same update query, on 10 million rows table with 1 GB data

(@P1 bigint,@P2 bigint,@P3 bigint,@P4 bigint,@P5 bigint)
UPDATE top(1000) hsi.hspendingitems
   SET status = @P1
WHERE handlertype = @P2
AND status in (@P3,@P4,@P5)

Initial Results (without modifications)

Query 1 plan:

https://www.brentozar.com/pastetheplan/?id=Syv7OxRHW

Query IO statistics

Table 'hspendingitems'. Scan count 9, logical reads 1686, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Additional Index

I then added an index:

CREATE NONCLUSTERED INDEX [ix_test_dba] ON [hsi].[hspendingitems]
(
[handlertype] ASC,
[status] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON [DBSpace8]

Results with New Index

Query 2 plan:
https://www.brentozar.com/pastetheplan/?id=S1yjulAS-

Query statistics:

Table 'hspendingitems'. Scan count 1, logical reads 10253, physical reads 20,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Is it better for SQL Server to do less scans but more logical reads?

I noticed that the cost plan is also different:

Initial Cost 102.937 vs with Index 0.0232853 ?

Vendor is aware to add that index, what do you think I should push vendor for adding that index?

The context is important when trying to compare the IO statistics of queries. Let's just start by describing what the queries do based on their plans.

The first query scans the heap in parallel until it finds 1000 rows that need to be updated. The update is then performed on the table. In the worst case scenario for performance, the entire table will need to be scanned.

The second query does an index seek until it finds 1000 rows that need to be updated. Both the table data and the index needs to be updated, because you're changing a column in the index. The query's cost is so low that it isn't eligible for parallelism.

Now let's go back to the IO statistics to see if they make more sense. For the first query:

  Table 'hspendingitems'. Scan count 9, logical reads 1686, physical
  reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
  0, lob read-ahead reads 0.

The scan count of 9 means that the query executed with a DOP of 8. The logical read count represents the number of reads needed to find the first 1000 rows and activity required to update the table's data. I'm assuming that the UPDATE query runs in a loop because it only updates 1000 rows. As the query runs it's likely that the logical read count will increase because SQL Server will need to scan more of the table to find the next 1000 rows to update.

For the second query:

  Table 'hspendingitems'. Scan count 1, logical reads 10253, physical
  reads 20, read-ahead reads 0, lob logical reads 0, lob physical reads
  0, lob read-ahead reads 0.

The query doesn't run in parallel so the scan count is 1. The nonclustered index that you created is stored as a separate object so both the table and the index's data need to be updated. I would expect the performance of this query to remain roughly constant as the query runs in a loop.

Should the index be created? It depends. Is there a performance problem right now with the current code? Does the index solve the performance problem in the best way? Are there other queries that could benefit from the index? Will the index slow down other DML on the table too much? We can't answer those questions for you, but I will say that updating 1000 rows against an unindexed heap in a loop often isn't a good strategy in terms of performance.


SQL Server 2016 not using index used in 2012 2016 plan here

2012 plan here

I have almost identical tables with very small difference in row count. One on 2012, and the other on 2016. Indexing is identical. These VMs are in exact same environment with upgraded OS and SQL Server versions.  Same number vcores, same memory, same server settings (max degree of parallelism = 8, cost threshold for parallelism = 30).

This drop dead simple query for single record using a single column for filter and single column return. The column in the where filter is the sole column in the index.

2016 version has 8254356 rows
2012 version has 8254427 rows

They are the same queries.  2016 is missing the index and doing full table scan for no apparent reason.  2012 does a RID lookup (Heap) on the table after the index scan.

I tried WITH (index = CONTACT_RC_NUI1) on 2016 server and the cost jumped from 991 to 1889. On 2012 the cost was 29.

I tried adding AND 1 = (SELECT 1) and that made no difference.
I tried removing parameter sniffing as a possible problem by using OPTION (RECOMPILE) and that made no difference.

The DBA ran index rebuilds after restoring the database. Both servers had fairly recent index stats updates (we run Ola's index update script).  And to be sure I rebuilt the index on 2016 which had no effect on the 2016 explain plan.

I added hint below in the query...

select address1_stateorprovince
from dla.dcrm.CONTACT_RC WITH (index = CONTACT_RC_NUI1)
where wv_partyid = 343083;

This resulted in taking cost from 991 to 1889 even though it shows almost identical to 2012 explain plan (2016 just added parallelism (gather streams)).

What 2016 appears to be doing is costing the index for only 1% but the RID lookup is 99%.  In 2012 this was reversed. It appears 2016 used the index to scan all the entries and then looked up every RID in the table?  Could that be true?  I think 2016 optimizer has been smoking something seriously strong.

wv_party_id is nvarchar(100)

You see different plans because from SQL Server 2014 there is a new cardinality estimator in SQL Server. And then they added some new features to SQL Server 2016 for the the new CE.

First some test data to reproduce what you see.

create table dbo.T(C1 char(10) default '', C2 varchar(11));

go

insert into dbo.T(C2)
select top(800000) row_number() over(order by (select null))
from sys.columns as c1, sys.columns as c2, sys.columns as c3

go

create index IX_T_C2 on dbo.T(C2)

And the queries that will produce the two different plans for you so you can compare them in the same version of SQL Server.

-- Table scan version
select C1
from dbo.T with (index = 0)
where C2 = 100000
option (maxdop 1);

-- Index Scan version
select C1
from dbo.T with (index = IX_T_C2)
where C2 = 100000
option (maxdop 1);

The table scan version in SQL Server 2012 scans all rows and returns one. No surprise there.

The Index scan version of SQL Server 2012 scans all rows in the index and returns one row. There is something there that needs to be looked on further but for now you should take an extra look at the Estimated Number of Rows for the Index Scan operator.

The Table Scan version of SQL Server 2016 is no different than the version in 2012. It scans all rows returning one row.

The Index Scan version looks the same as in 2012 but the cost is much higher and that is because Estimated Number of Rows is much higher than in 2012

So SQL Server now thinks it has to do 80000 RID Lookup to return 1 row and that is why it chooses the Table Scan in SQL Server 2016 with the new cardinality estimator.

The new estimator sees the predicate where CONVERT_IMPLICIT(int,C2) = 100000 and gives up. It uses the standard guess of 10% selectivity for an equality predicate, where 10% of 800,000 rows = 80,000. The original estimator used more complex logic to produce a non-guess (accurate!) estimate of one row.

Now to the issue with the Index Scan. That is probably not what you want. You want SQL Server to do an Index Seek finding the row you are looking for. Currently SQL Server can't do that because you have a type mismatch in the where clause and you do get warnings about it in the query plan.
Fix that and you should see a plan with an Index Seek and a RID Lookup in both versions of SQL Server.

Note also that cost percentages in execution plans are always based on optimizer estimates, not real run time information.


Dynamic LIMIT based on query time I'm writing scripts in Python for cleaning up a database. It mainly is finding and deleting billions of rows of data based on some logic. I have it running in a loop with a LIMIT that will run until there are no longer any rows affected by a DELETE. It is running on a production database that gets different load during different times of the day, so the time to do these DELETE queries varies based on the load. I don't want to lock up any tables by running a large DELETE, so I've been trying to benchmark the query times for different LIMITs. I found these times varied a lot based on the time of the day, so I started looking into making a dynamic LIMIT that increases or decreases based on the query time. It will increase or decrease between a floor and ceiling based on the the last query time. So the basic logic is

if QUERY_TIME < DECREASE_TIME and LIMIT < MAX_LIMIT:
  LIMIT = LIMIT * 10
elif QUERY_TIME > MAXIMUM_TIME and LIMIT < MIN_LIMIT:
  LIMIT = LIMIT / 10

My question is if this is a good strategy? I'm still learning a lot of optimizations for database work, so I'm not sure if this is a common strategy or what pitfalls I may encounter.

There may be a problem with that design.  As you search for the next batch to delete, you may be skipping over previously checked rows.  So, each subsequent delete will be slower.  The time to finish is (sort of) quadratic.

Two better techniques:

If there are only a "small" number of rows to keep, then build a new table with just them; then swap tables.  Time: linear.  Advantage:  The result is already defragmented.
Walk through the table 1K rows at a time.  Use the PRIMARY KEY for tracking where you are.  And remember where you left off.  Time: linear.

Both of those techniques (and others) are discussed here.


Improve SQL Performance with LIKE '%ABC%' QUERY I know that using a LIKE '%ABC%' query will not use indexes and there is little that can be changed to the query to improve this, however what can do to make execution quicker?  At this stage we cannot change the query to use Full-text indexing.

Some background...

We are bringing systems up in Azure VMs (NOTE: Not Azure SQL, but SQL Server running on Windows 2012) as a 2nd location for additional resilience (offline backup) and have 'built' an SQL server using a basic server spec.  Performance of a LIKE query on our old platform took 2 seconds to run, whereas on this Azure platform its taking 10 seconds.

This is clearly server spec limitations, however what can i do to improve this?

I can see the CPU spike during the query run, so it would seem a 'faster' azure cpu will help, but know that these figures can be misleading too!

So my question is, do i need to concentrate on improving CPU, or could it be more than that?

The DB in question is only 300mb on disk, and the table being queried has about 160k rows so it isn't large by any means.

Please let me know if i am barking up the wrong tree here, or if i need to check anything else first?

The SQL server is Windows 2012 R2 with SQL Server 2014 Std, and has been built following the Azure SQL performance guidelines (i.e. data on a dedicated striped drive).

EDIT
As requested, this is the query i am testing:

SELECT Name
FROM Users
WHERE Name like '%ABC%'

Thats it.  Nothing complex here, just retrieving data from a small database!

Incidentally, This query takes 10s to run, whereas adding the clause 'AND Description like '%ABC%' reduces the time to 6s?

EDIT 2

Ok, some more information following feedback in the comments...

I have followed the information from this page: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

I have run the query shown, and these are the results shown:

To me, it initially looked like it was Disk IO bound, but the Average wait time seems low?  I am no expert in this area so please advise...

Without full-text search, no, there's no magic to making string parsing faster within SQL Server, other than pre-calculating results or throwing more resources at the problem.

If you have a narrow set of search patterns that are repeated over and over again, it's possible you could maintain a skinnier materialized portion of the table that meet those criteria (e.g. a table of just the PK columns representing the rows in the main table that match '%ABC%' - you could maintain these through triggers). This will reduce the amount of reads required, but may not have a serious impact on duration.

If people are entering arbitrary search strings in a non-repeatable and unpredictable way, that may not help anyway.

10 seconds seems like a long time for a table with 160K rows. If you are on V12 (and can run this query in relative isolation), you should be able to determine the waits that changed during that query, using sys.dm_db_wait_stats - it may be that you can't keep 300MB of data in memory and the wait time is all disk churn. In this case it may just be that you are sharing an overwhelmed server, so one consideration would be to move up to a better tier that provides for better performance.

Another option you can consider is application-side caching (think memcached, redis, etc.), where you have a copy of the data in your application's memory, and perform the searching there instead of within SQL Server.


Relationships in a school database I am designing database for a school app. Student is the main entity in the database. Student has relation with Parent & Guardian.

I am confused as where to store references. As will student table have foreign keys from parent & guardian table or vice versa.

UPDATE: Need to store student current data like course / batch / roll no etc which keeps on changing. For this purpose I am creating a new table student_current to store the current data with one to many relation from student to student_current table. At any time, i need to create a join with student_current table to fetch latest student_current record. To improve performance, I am storing primary key of student_current table as foreign key in student table and then making a simple join on this to fetch data. Is this approach right??

Also would appreciate your help in suggesting how to store revisions for student data.

Current schema is like:

student { id, student_current_id, name .... }
student_current { id, student_id, course .... }

Please help.

You might need an intermediate table to store this relationship, as it's possible that a student has more than one parent/gaurdian (a mother and a father) and it's possible that a parent/gaurdian has more than on student.

student
-------
  id

gaurdian
--------
  id

student_gaurdian
----------------
  student_id
  gaurdian_id

The table student_gaurdian can store all the relations between students and gaurdians, and allows many-to-many relations.

RE: your new question

Having two tables with keys to each other looks a little weird to me, but if you really are having performance problems and this relieves them, then it's fine.


querying a table of events for reporting The background (masking a real-world problem behind a word problem)
Farmer Brown has a peach orchard in California and ships hundreds of
thousands of peaches all across the country. When a shipment of peaches
reaches its destination, the deliveryman inspects the peaches and keeps
all the peaches that fail his inspection.

When the peaches that failed inspection arrive back at Farmer Brown's farm,
the peach defects are repaired at Farmer Brown's Peach Hospital and
get re-shipped with the next batch of peach shipments. And if the original peaches
fail inspection again, they go through the same process, as long as the peach is salvageable.

Peach Hospitals are expensive, and so is the cost of shipping,
so Farmer Brown starts thinking, "For the peaches I shipped within any time range, how many total did I ship? Of that total, how many shipped for the 2nd time? for the 3rd (or more) time?”

At a high-level, the peach shipments tracking table (on a SQL Server 2000 database) has 9 million+ records with different kinds of shipments, and looks (grossly) akin to:

ShipmentDate   PeachID    Shipment Type
8/10/2005         7112      Shipment to Customer
7/15/2007         8798      Shipment to Customer
4/3/2009          8798      Shipment to Customer
4/15/2009         8798      Shipment to Customer
4/21/2009         8798      Shipment to Customer
4/21/2009         145751    Shipment to Customer
4/22/2009         7112      Shipment to Customer
4/22/2009         12121     Shipment to Customer
4/25/2009         8798      Shipment to Customer
5/12/2009         8798      Shipment to Customer

If we attempted to return a sample of what Farmer Brown was looking for,
it would be results like this:

given the following Parameters:
 StartDate = 4/1/2009,   EndDate = 4/30/2009

Results
-- The easy part
Total Peaches Shipped to Customer: 100,000
(PeachID 8798 contributes 4 to the total. PeachID 7112 contributes 1 to the total.)

-- The messy part
Total Peaches Shipped for the 2nd time: 5,000
(PeachID 8798 contributes 1 to the total. PeachID 7112 contributes 1 to the total.)

Total Peaches Shipped for the 3rd (or more) time: 800
(PeachID 8798 contributes 2 to the total.)

The issue
What would be the most efficient way of building a holding table(s) of this data (refreshed once a day - real-time is not important), so that when retreiving Farmer Brown's requested data, the data can be returned speedily? I'm thinking that if the shipments starting from the beginning of Farmer Brown's opening day to present were aggregated by day into a holding table(s), then performing an aggregate on whatever the date range parameters are, the proc will be run faster than querying the Shipments table and its millions of records.

Apologies if I'm not explaining the situation clearly enough. I'd really appreciate an outside perspective on this.

I'm not sure how you're going to be reporting this data back to said farmer, here's a quick way of doing what you're requesting:

declare @test table (
    sd datetime,
    id int,
    st nvarchar(100)
);

insert @test (sd,id,st) values ('05/25/2012',1,'Shipment to Customer');
insert @test (sd,id,st) values ('05/26/2012',1,'Shipment to Customer');
insert @test (sd,id,st) values ('05/30/2012',1,'Shipment to Customer');
insert @test (sd,id,st) values ('05/26/2012',2,'Shipment to Customer');
insert @test (sd,id,st) values ('05/30/2012',3,'Shipment to Customer');
insert @test (sd,id,st) values ('05/14/2012',2,'Shipment to Customer');
insert @test (sd,id,st) values ('05/30/2012',5,'Shipment to Customer');

select
   id
   , count(*) ShippedById
from @test
where sd >= '05/25/2012' and sd <= '05/28/2012'
group by id
compute sum(count(*));

(with emphasis on the select statement)

You will get the number of times a peachid shipped, by id and then you'll get a second set that gives you the total number of peaches shipped.

If you're going to be using SSRS, you can take out the compute clause and then use SSRS to sum the results of ShippedById. Such a solution would also work with Excel.


Restore Database on Active Passive Failover Cluster Well, I helped my boss's boss install 2 nodes of SQL Server 2008 on a Windows server 2008 failover cluster. I did this remotely and I don't have accesse to it.
(I run a active-active cluster on Server 2003).

I am waiting for the call on how to Restore a database from a stand-alone database to the new cluster (this is ACTIVE-PASSIVE).

I know the two nodes share the same disk resource,
but do I need to RESTORE it to the Active and then to the Passive ?

Or since they share the master database, do I just need one restore?

Welcome to StackExchange.

The SQL database will only need to be installed on 1 node, as the nodes share the storage already since SQL Server is 'cluster aware'.  However if you use something that is not 'cluster aware' such as SSIS, then packages will have to be installed on both nodes.

The reason you need to do 2 installations is because the nodes do not share installation files found on the local disk of the machine, but anything you do on shared storage and within SQL Server will be retained when failing over.  It's 1 database engine and 2 nodes.

Off topic, but a thought might be to have scripts that copy backup files and such so I make my 'backups' drive a cluster resource, and point all of my scripts to the virtual cluster address so even in case of a failover, they work.


How to get SQL for DDL commands in Sql developer? In Sql developer it is possible to get DDL for an existing table -

However, how do I get SQL for operations performed through UI? For example, how do I see DDL generated when I alter the table to drop a column? Sql developer does the drop operation and does not show me anything.

Edit:
I add a column -

And then switch over to SQL tab -

Not what I want.

Oracle knew you wanted this feature. I'm using SQL Developer 4.0.0.13.80 and it works like you want it to.  You were probably using an older version when you posted your question.  I go to Edit Table, add a column in the GUI, and then pick the item called DDL in the dialog.  There is a radio button to show either the full CREATE statement DDL, or just the DDL update for the current edit.


Identical SQL Server databases hosted in the same physical DB server We have hosted similar SQL Server 2014 databases TestDB and TESTDB_Copy on the same physical server. The two databases are almost the same and there may be minor differences in the number of records (less than 1000) in few tables.

One database is responding quickly and other is very slow. etc., Query in TestDB takes 1 sec, but TestDB_copy takes 5-10 seconds. We have ensured the indexes are similar in both the databases and the rebuilt the index in both the databases.

Queried master.sys.dm_os_performance_counters and sys.dm_exec_cached_plans and they are almost the same for both the databases.

What else would be the issue with the slow responding database (TESTDB_Copy)?

Finding 1) The clustered index seek is 16% in fast database and 27% in slow database.

The no of rows is close to 400k in the table undergoing index seek.

If these are quite large databases I would ask if they are on different physical disks, with one disk significantly slower or busier than the other? (i.e. one is SSD, and the other is a standard HDD).

For smaller databases though, the data will quickly be cached, and so speed of the disk would stop being relevant at that point.

I'd suggest turning on Client Statistics and Show Actual Execution Plan to narrow down where the queries against the two databases are not aligned.


Queries using GIN index on f_unaccent() seem slow? I have almost 20M rows in one table that contains product, including their names.

I wish to search fulltext and fast by the name so I created this index:

CREATE INDEX uprice_item_occurrence_unaccent_name_trgm_idx ON price_item_occurrence USING gin (f_unaccent(name) gin_trgm_ops);;

I was hopping the following query would take less then (lets say) 500ms

select * from price_item_occurrence as oo
where f_unaccent(oo.name) % f_unaccent('iphone');

But it takes almost 2s:

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) % f_unaccent('iphone');
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on price_item_occurrence oo  (cost=1956.42..63674.14 rows=16570 width=287) (actual time=247.918..1880.759 rows=94 loops=1)
   Recheck Cond: (f_unaccent((name)::text) % 'iphone'::text)
   Rows Removed by Index Recheck: 87838
   Heap Blocks: exact=76663
   ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..1952.28 rows=16570 width=0) (actual time=195.418..195.418 rows=88962 loops=1)
         Index Cond: (f_unaccent((name)::text) % 'iphone'::text)
 Planning time: 0.444 ms
 Execution time: 1880.833 ms

It's possible that the DB is busy, but i am not sure.

I tried to play with select set_limit(0.9); (increased) and it helps a bit but not much.

I am using Postgres 10, can change the Postgres config and I am open for suggestions.

I tried it with ilike and it improved by some:

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) ilike ('%' || f_unaccent('iphone') || '%');
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on price_item_occurrence oo  (cost=3135.08..416823.45 rows=166075 width=286) (actual time=50.258..670.085 rows=65917 loops=1)
   Recheck Cond: (f_unaccent((name)::text) ~~* '%iphone%'::text)
   Rows Removed by Index Recheck: 10
   Heap Blocks: exact=59750
   ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..3093.56 rows=166075 width=0) (actual time=37.385..37.385 rows=67700 loops=1)
         Index Cond: (f_unaccent((name)::text) ~~* '%iphone%'::text)
 Planning time: 0.545 ms
 Execution time: 675.776 ms
(8 rows)

About 2x faster.

i tried limit 10:

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) % f_unaccent('iphone') limit 10;
                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=373.27..410.51 rows=10 width=287) (actual time=268.718..589.131 rows=10 loops=1)
   ->  Bitmap Heap Scan on price_item_occurrence oo  (cost=373.27..62493.45 rows=16680 width=287) (actual time=268.715..589.123 rows=10 loops=1)
         Recheck Cond: (f_unaccent((name)::text) % 'iphone'::text)
         Rows Removed by Index Recheck: 18917
         Heap Blocks: exact=17100
         ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..369.10 rows=16680 width=0) (actual time=165.958..165.958 rows=69268 loops=1)
               Index Cond: (f_unaccent((name)::text) % 'iphone'::text)
 Planning time: 0.397 ms
 Execution time: 589.187 ms
(9 rows)

this is also faster, maybe almost good enough

I have seen GIN indexes to generally perform much faster than GiST for these queries. Try this index instead:

CREATE INDEX price_item_occurrence_name_trgm_gin idx ON price_item_occurrence
USING GIN (f_unaccent(name) gin_trgm_ops);

Related:

Does PostgreSQL support “accent insensitive” collations?
Using ILIKE with unaccent and with only right end wildcard
How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
Trigram index for for ILIKE patterns not working as expected

All the basic advice for performance optimization applies. For starters, your table needs to be VACUUMed and ANALYZEd enough to be fast.

Configuring PostgreSQL for read performance


Get count estimates from pg_class.reltuples for given conditions Is it possible to query the reltuples column for a given table with additional conditions like table.name LIKE 'hello%'?

Currently on my bigger tables the SELECT count(*) query takes long and I wouldn't need an exact count. So I wanted to know if it's possible to add WHERE clauses to the reltuples as well?

Not out of the box. But you can achieve it with a ...

Partial index

CREATE INDEX tbl_name_hello_idx ON tbl(tbl_id) WHERE name LIKE 'hello%';

SELECT reltuples
FROM   pg_class
WHERE  oid = 'tbl_name_hello_idx'::regclass;  -- or schema-qualify table name

The actual index column (tbl_id in the example) is irrelevant (unless you have additional use for the index). Best pick a small column that is never changed: a serial PK column would be a perfect candidate. Or you could use a constant (which defeats additional purposes of that index):

CREATE INDEX tbl_name_hello_idx ON tbl((1)) WHERE name LIKE 'hello%';

Every index has its own entry in pg_class and its own reltuples count. The documentation:

  reltuples ...
  Number of rows in the table. This is only an
  estimate used by the planner. It is updated by VACUUM, ANALYZE, and a
  few DDL commands such as CREATE INDEX.

Hence, a partial index can be (ab-)used to get count estimates for any set of predicates, updated by autovacuum automatically. Or maybe you already have the index because you need it anyway?

This might actually be a very clever idea. But you have to weigh cost and benefits: the partial index is small for a rare condition, but the cost gets bigger for a common condition. And while keeping the statistics up to date is comparatively cheap, it's not free of cost.

Related:

Index that is not used, yet influences query
Fast way to discover the row count of a table

TABLESAMPLE SYSTEM (n) in Postgres 9.5+

You will love the new feature in the upcoming Postgres 9.5, which only looks at a random sample of n % of blocks in the table to get a quick estimate. Example for 1 %:

SELECT 100 * count(*) AS estimate
FROM   tbl TABLESAMPLE SYSTEM (1)
WHERE  name LIKE 'hello%';

Details in the answer already linked above:

Fast way to discover the row count of a table

Alternative for given example

For the given example name LIKE 'hello%' you could get very fast exact results using the right index anyway:

CREATE INDEX tbl_name_text_pattern_idx ON tbl(name text_pattern_ops);

Details:

How can one optimize a query involving a sort and a LIKE?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


Is there a specific SQL Server 2008 R2 Permission to run DBCC CHECKIDENT? MS Docs (Books online) states the caller must either own the containing schema or be a member of fixed DB roles. (sysadmin, db_owner, db_ddladmin).

Is there a specific permission or set of permissions, that could be granted to allow this in SQL Server 2008 R2?

I have a case where I need to give the permission but with the least amount of privilege and want to avoid creating a stored procedure (with execute as) that allows the specific set of tables they need to perform this against.

They also need TRUNCATE however that's covered with the ALTER permission.

I think that MS-Docs on DBCC CHECKIDENT are really clear:

Permissions

  Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.


SQL Server 2008 R2 Lockup for 10 minutes Last Saturday night, our site went down for about 10 minutes. Analysis of the logs showed the following errors on the principal SQL Server (in a mirrored pair), during the outage and not outside the outage:

06/06/2015 23:14:41,spid[various],Unknown,Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT' id [various] type 4 Task [various] : [various] waittime 300 flags 0x1a owning task [various]. Continuing to wait.
06/06/2015 23:06:54,spid19s,Unknown,Time-out occurred while waiting for buffer latch type 3 for page (5:159076157) database ID 6.
06/06/2015 23:06:54,spid19s,Unknown,Error: 845 Severity: 17 State: 1.
06/06/2015 23:06:54,spid19s,Unknown,A time-out occurred while waiting for buffer latch -- type 3 bp 0000000518FA1200 page 5:159076157 stat 0xc0000b database id: 6 allocation unit Id: 72057793340899328 task 0x0000000008EDA748 : 0 waittime 300 flags 0x100000001a owning task 0x0000000004472BC8. Not continuing to wait.

There were over a hundred of the first error mostly before but also after the others, which only occurred once. These errors occurred about 2 hours after doing a failover, applying OS updates, and failing back to the original server. We've been running on these servers for about 2 years now and have never seen this issue. The software calling into the servers was most recently updated on Thursday afternoon (about 55 hours before the outage).

I'm finding very little information here or through google about this timeout. The closest thing I've found is the second answer to this question: https://stackoverflow.com/questions/3149310/time-out-occurred-while-waiting-for-buffer-latch-type-2-error-in-sql-server, which talks about type 4 latch error being tempdb-related and caused by a bug in 2008, but that bug was resolved in 2009, before 2008 R2 was released. The exact version reports as:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

The servers both use mirrored Intel Enterprise SSDs for everything (dual RAID 10 arrays for the data), and none of the drives are reporting issues. The tempdb volume has 200GB free. There are 20 tempdb files (it's a BIG server). SQL traffic is down significantly from our peak over a year ago due to optimizations in the schema, stored procedures, and the software calling SQL, so it's probably not a load-related issue.

Is type 4 definitely related to the tempdb as many posts seem to indicate (database 6 referenced in the error message is our main database, not the tempdb)? What can I do to prevent this issue from happening again?

We had another mysterious SQL crash yesterday afternoon (no logs for that one either--just a SQL error message). We failed over to the mirror and ran some burn-in tests on this system and it turns out this server had a memory module that must have recently developed issues. I believe this is likely the cause of these issues. Thanks to all who looked into this!


How can I speed up this 2m5s query that has indices? How can I speed up this 2m5s query that has indices?

select urls.id as urlId,
    count(case when s1.hit_type = 0 then 1 end) as aCount,
    count(case when s1.hit_type = 1 then 1 end) as bCount,
    count(case when s1.hit_type = 2 then 1 end) as cCount,
    count(distinct s1.source_id) as sourcesCount
from urls join stats s1 on urls.id = s1.url_id
where s1.hit_date >= '2017-12-12'
group by urls.id
order by aCount desc
limit 0,100;

mysql> show create table stats;

| stats | CREATE TABLE `stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `url_id` varchar(100) DEFAULT NULL,
  `hit_date` datetime DEFAULT NULL,
  `hit_type` tinyint(4) DEFAULT NULL,
  `source_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `url_id_idx` (`url_id`),
  KEY `source_id` (`source_id`),
  KEY `stats_hit_date_idx` (`hit_date`),
  CONSTRAINT `stats_ibfk_1` FOREIGN KEY (`url_id`) REFERENCES `urls` (`ID`),
  CONSTRAINT `stats_ibfk_2` FOREIGN KEY (`source_id`) REFERENCES `sources` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6027557 DEFAULT CHARSET=latin1 |

mysql> describe select...
| id | select_type | table   | type   | possible_keys                                                                                | key     | key_len | ref                      | rows    | Extra                                        |
+----+-------------+---------+--------+-------------------------------------------------------------------------------------------------+---------+---------+--------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | s1      | ALL    | url_id_idx,stats_hit_date_idx                                                                   | NULL    | NULL    | NULL                     | 5869695 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | urls    | eq_ref | PRIMARY,urls_email_idx,urls_status_idx,deptId_idx,deptId_status_email_idx                       | PRIMARY | 102     | db.s1.url_id             |     1   | Using index                                  |

It doesn't seem to be using the hit_date index or url_id index.

I tried using a sub-select (select count(*) from stats where url_id = ... and hit_date >= ... and hit_type = 0) as aCount and it was faster and took 24s. Is there a way to make it less than 5s? The limit for the entire request is 30s.

MySQL Server version:         5.6.35-log MySQL Community Server (GPL)

Your query is equal to

select /* urls.id */ s1.url_id as urlId,
    count(case when s1.hit_type = 0 then 1 end) as aCount,
    count(case when s1.hit_type = 1 then 1 end) as bCount,
    count(case when s1.hit_type = 2 then 1 end) as cCount,
    count(distinct s1.source_id) as sourcesCount
from /* urls join */ stats s1 /* on urls.id = s1.url_id */
where s1.hit_date >= '2017-12-12'
group by /* urls.id */ s1.url_id
order by aCount desc
limit 0,100;

except in your query output there are only records which "pairs" exists in urls table.

But the constraint

CONSTRAINT `stats_ibfk_1` FOREIGN KEY (`url_id`) REFERENCES `urls` (`ID`)

do not allow those records.

So my query is absolutely equal to your one, and you can use it instead.

To increase this query speed you may create covering index

ALTER TABLE stats ADD INDEX idx (url_id, hit_date, hit_type, source_id)

And the best way is to move url_id to a separate table and replace it with a reference of numeric type (grouping by VARCHAR field is expensive).

Additionally - count(case when s1.hit_type = N then 1 end) can be replaced with short SUM(s1.hit_type = N).

To speed up the whole query I'd recommend try to divide it to 4 separate queries:

SELECT urlId,
       MAX(aCount) aCount,
       MAX(bCount) bCount,
       MAX(cCount) cCount,
       MAX(sourcesCount) sourcesCount
FROM (  select  s1.url_id as urlId,
                COUNT(*) as aCount,
                0 as bCount,
                0 as cCount,
                0 as sourcesCount
        from stats s1
        where s1.hit_date >= '2017-12-12' AND s1.hit_type = 0
        group by s1.url_id
      UNION ALL
        select  s1.url_id, 0, COUNT(*), 0, 0
        from stats s1
        where s1.hit_date >= '2017-12-12'  AND s1.hit_type = 1
        group by s1.url_id
      UNION ALL
        select  s1.url_id as urlId, 0, 0, COUNT(*), 0
        from stats s1
        where s1.hit_date >= '2017-12-12'  AND s1.hit_type = 2
        group by s1.url_id
      UNION ALL
        select  s1.url_id as urlId, 0, 0, 0, count(distinct s1.source_id)
        from stats s1
        where s1.hit_date >= '2017-12-12'
        group by s1.url_id
    ) x
GROUP BY urlId
order by aCount desc
limit 0,100;

The index by (url_id, hit_type, hit_date) will speed up first 3 subqueries, and by (url_id, hit_date, source_id) will speed up the last subquery.


Installing SQL Server 2014 Enterprise in Windows 7 Ultimate 64 Bit Incompatibility Error This error occurs when I run the setup.exe of SQL 2014.

It tried changing the compatibility mode of the setup from Windows 95 through Windows Vista (Service Pack 2) and run the setup as Admin but same error occurs.

I also installed .Net Framework 4.0 and 4.5. Under the Programs And Features > "Turn Windows features On and Off" > I enabled the "Microsoft .NET Framework 3.5.1" but I found a guide that there should be .Net Framework 2.0 and 3.0 under the Microsoft .NET folder but those 2 versions are missing in my pc.

https://answers.microsoft.com/en-us/newmsn/forum/newmsn_install-newmsn_updateapp/i-cant-install-sql-server-2014-on-windows-10/6d311401-796f-4b3f-b5be-d310cfa6454c?auth=1

When I tried installing those two versions I get the following error
"You must use Turn Windows features On and Off in the Control Panel to install or configure Microsoft .NET Framework 2.0 SP2"

same as the SP1 version of .Net Framework 3.0

According to Microsoft you have to pay for these versions of their operating system if you want to run their "Enterprise" SQL Server.

Windows Server 2016 Datacenter
Windows Server 2016 Standard
Windows Server 2012 R2 Datacenter 64-bit
Windows Server 2012 R2 Standard 64-bit
Windows Server 2012 R2 Essentials 64-bit
Windows Server 2012 R2 Foundation 64-bit
Windows Server 2012 Datacenter 64-bit
Windows Server 2012 Standard 64-bit
Windows Server 2012 Essentials 64-bit
Windows Server 2012 Foundation 64-bit
Windows Server 2008 R2 SP1 Datacenter 64-bit
Windows Server 2008 R2 SP1 Enterprise 64-bit
Windows Server 2008 R2 SP1 Standard 64-bit
Windows Server 2008 R2 SP1 Web 64-bit
Windows Server 2008 SP2 Datacenter 64-bit
Windows Server 2008 SP2 Enterprise 64-bit
Windows Server 2008 SP2 Standard 64-bit
Windows Server 2008 SP2 Web 64-bit

It also seems to run with the same versions in 32-bit, it just stores them in a separate column and amusing Microsoft can't fill an HTML table properly (nor can they generate Markdown-friendly urls.)
Interestingly, "SQL Server Standard"  runs on

Windows 7 SP1 Ultimate 64-bit
Windows 7 SP1 Enterprise 64-bit
Windows 7 SP1 Professional 64-bit

So the question is
What feature does Enterprise do that Standard doesn't that requires Windows Server 2008 (released in February 27, 2008) instead of the newer Windows 7 Ultimate (released in October 22, 2009)?
I assume nothing, except licensing and marketing wank. It seems you're at impasse, so I suggest upgrading your operating system, and upgrading your database server.
It may also be worth nothing that other people have managed to do this.
Sean Gallardy, whose profile claims insider information,

commented on DBA.se

it may physically install, but it's not supported at all. Kind of like those trying to use wine, etc, to get it to run on Linux ;) I'm also with you, use developer edition if absolutely needed for something like this (if dev applies to the workload). - Sean Gallardy

Of course, details omitted we have no idea why it wasn't supported or what the "serious limits" are (from the context of the conversation).. Betting it's still a hold up for more cash.
Perhaps you can find a new version of Enterprise without this software-imposed restriction.


Best practice for a DBA to login to a database I've read that a DBA never should login to a database as system user. That makes me wondering... how should a DBA login to a database? Should he/she create a DBA account and use it for login purposes? But I think that would be the same as login as system. Or should he login to each schema as the owner of that schema ?

Log in as sys, create an account for yourself and grant that account just the privileges you require.  Give yourself a reasonable password.  "Password" is not a good password.

Here are the top ten most used passwords and even if it is your intention that only you log on as DBA these are still not good passwords:

password
123456
12345678
1234
qwerty
12345
dragon
pussy
baseball
football

Here is a script I have used on Oracle 9i and 11g that grants more than I've ever needed.  Further pruning of the permissions on a production system would be a good exercise in security.

Edit @ miracle173  asks what is the difference between the script below and just granting DBA privilege?
The most important difference is that by granting individual privileges you can remove what is unnecessary. If you grant a DBA role then you cannot pick and choose unless you edit the role which is not advisable.  The requirements change from when you are developing a database where you could need everything to a database that has been deployed to production where you want to have the least privileges necessary. Best practice is exactly what Leigh Riffel answered: do what you need to do with the least privileges.

Edit @miracle173 correctly points out that SYSTEM tablespace is not recommended for a user. I have changed it to USERS in the script and on my primary development database.

Edit @miracle173 also makes some good points on the distinction in privileges between a DBA who maintains and a developer.  I do DBA and application development for the databases I work on so you could break down the script according to what you are doing. Practices and standards vary by industry, organization and habit so what you need to work with can change from instance to instance.  It's common in my shop for the development database to have more privileges for admins and developers than production.

CREATE USER ADMIN
  IDENTIFIED BY <choose a good password>
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

A DBA might need these depending on what Oracle options are in use:

  GRANT ADMINISTER DATABASE TRIGGER TO ADMIN;
  ALTER USER ADMIN DEFAULT ROLE ALL;
  GRANT ALTER ANY CLUSTER TO ADMIN;
  GRANT ALTER ANY DIMENSION TO ADMIN;
  GRANT ALTER ANY INDEX TO ADMIN;
  GRANT ALTER ANY INDEXTYPE TO ADMIN;
  GRANT ALTER ANY LIBRARY TO ADMIN;
  GRANT ALTER ANY MATERIALIZED VIEW TO ADMIN;
  GRANT ALTER ANY OUTLINE TO ADMIN;
  GRANT ALTER ANY PROCEDURE TO ADMIN;
  GRANT ALTER ANY ROLE TO ADMIN;
  GRANT ALTER ANY SEQUENCE TO ADMIN;
  GRANT ALTER ANY TABLE TO ADMIN WITH ADMIN OPTION;
  GRANT ALTER ANY TRIGGER TO ADMIN;
  GRANT ALTER ANY TYPE TO ADMIN;
  GRANT ALTER DATABASE TO ADMIN;
  GRANT ALTER PROFILE TO ADMIN;
  GRANT ALTER RESOURCE COST TO ADMIN;
  GRANT ALTER ROLLBACK SEGMENT TO ADMIN;
  GRANT ALTER SESSION TO ADMIN;
  GRANT ALTER SYSTEM TO ADMIN;
  GRANT ALTER TABLESPACE TO ADMIN;
  GRANT ALTER USER TO ADMIN;
  GRANT ANALYZE ANY TO ADMIN;
  GRANT AUDIT ANY TO ADMIN;
  GRANT AUDIT SYSTEM TO ADMIN;
  GRANT AUTHENTICATEDUSER TO ADMIN WITH ADMIN OPTION;
  GRANT BACKUP ANY TABLE TO ADMIN;
  GRANT BECOME USER TO ADMIN;
  GRANT COMMENT ANY TABLE TO ADMIN;
  GRANT CREATE ANY CLUSTER TO ADMIN;
  GRANT CREATE ANY CONTEXT TO ADMIN;
  GRANT CREATE ANY DIMENSION TO ADMIN;
  GRANT CREATE ANY DIRECTORY TO ADMIN;
  GRANT CREATE ANY INDEX TO ADMIN;
  GRANT CREATE ANY INDEXTYPE TO ADMIN;
  GRANT CREATE ANY LIBRARY TO ADMIN;
  GRANT CREATE ANY MATERIALIZED VIEW TO ADMIN;
  GRANT CREATE ANY OPERATOR TO ADMIN;
  GRANT CREATE ANY OUTLINE TO ADMIN;
  GRANT CREATE ANY PROCEDURE TO ADMIN;
  GRANT CREATE ANY SEQUENCE TO ADMIN;
  GRANT CREATE ANY SYNONYM TO ADMIN;
  GRANT CREATE ANY TABLE TO ADMIN;
  GRANT CREATE ANY TRIGGER TO ADMIN;
  GRANT CREATE ANY TYPE TO ADMIN;
  GRANT CREATE ANY VIEW TO ADMIN;
  GRANT CREATE CLUSTER TO ADMIN;
  GRANT CREATE DATABASE LINK TO ADMIN;
  GRANT CREATE DIMENSION TO ADMIN;
  GRANT CREATE INDEXTYPE TO ADMIN;
  GRANT CREATE LIBRARY TO ADMIN;
  GRANT CREATE MATERIALIZED VIEW TO ADMIN;
  GRANT CREATE OPERATOR TO ADMIN;
  GRANT CREATE PROCEDURE TO ADMIN;
  GRANT CREATE PROFILE TO ADMIN;
  GRANT CREATE PUBLIC DATABASE LINK TO ADMIN;
  GRANT CREATE PUBLIC SYNONYM TO ADMIN;
  GRANT CREATE ROLE TO ADMIN;
  GRANT CREATE ROLLBACK SEGMENT TO ADMIN;
  GRANT CREATE SEQUENCE TO ADMIN;
  GRANT CREATE SESSION TO ADMIN;
  GRANT CREATE SYNONYM TO ADMIN;
  GRANT CREATE TABLE TO ADMIN;
  GRANT CREATE TABLESPACE TO ADMIN;
  GRANT CREATE TRIGGER TO ADMIN;
  GRANT CREATE TYPE TO ADMIN;
  GRANT CREATE USER TO ADMIN;
  GRANT CREATE VIEW TO ADMIN;
  GRANT DBA TO ADMIN WITH ADMIN OPTION;
  GRANT DEBUG ANY PROCEDURE TO ADMIN;
  GRANT DEBUG CONNECT SESSION TO ADMIN;
  GRANT DELETE ANY TABLE TO ADMIN;
  GRANT DELETE, SELECT ON SYSTEM.MVIEW_FILTERINSTANCE TO ADMIN WITH GRANT OPTION;
  GRANT DELETE_CATALOG_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT DROP ANY CLUSTER TO ADMIN;
  GRANT DROP ANY CONTEXT TO ADMIN;
  GRANT DROP ANY DIMENSION TO ADMIN;
  GRANT DROP ANY DIRECTORY TO ADMIN;
  GRANT DROP ANY INDEX TO ADMIN;
  GRANT DROP ANY INDEXTYPE TO ADMIN;
  GRANT DROP ANY LIBRARY TO ADMIN;
  GRANT DROP ANY MATERIALIZED VIEW TO ADMIN;
  GRANT DROP ANY OPERATOR TO ADMIN;
  GRANT DROP ANY OUTLINE TO ADMIN;
  GRANT DROP ANY PROCEDURE TO ADMIN;
  GRANT DROP ANY ROLE TO ADMIN;
  GRANT DROP ANY SEQUENCE TO ADMIN;
  GRANT DROP ANY SYNONYM TO ADMIN;
  GRANT DROP ANY TABLE TO ADMIN;
  GRANT DROP ANY TRIGGER TO ADMIN;
  GRANT DROP ANY TYPE TO ADMIN;
  GRANT DROP ANY VIEW TO ADMIN;
  GRANT DROP PROFILE TO ADMIN;
  GRANT DROP PUBLIC DATABASE LINK TO ADMIN;
  GRANT DROP PUBLIC SYNONYM TO ADMIN;
  GRANT DROP ROLLBACK SEGMENT TO ADMIN;
  GRANT DROP TABLESPACE TO ADMIN;
  GRANT DROP USER TO ADMIN;
  GRANT EXECUTE ANY INDEXTYPE TO ADMIN;
  GRANT EXECUTE ANY LIBRARY TO ADMIN;
  GRANT EXECUTE ANY OPERATOR TO ADMIN;
  GRANT EXECUTE ANY PROCEDURE TO ADMIN;
  GRANT EXECUTE ANY TYPE TO ADMIN;
  GRANT EXECUTE_CATALOG_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT EXP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;
  GRANT FLASHBACK ANY TABLE TO ADMIN;
  GRANT FORCE ANY TRANSACTION TO ADMIN;
  GRANT FORCE TRANSACTION TO ADMIN;
  GRANT GATHER_SYSTEM_STATISTICS TO ADMIN WITH ADMIN OPTION;
  GRANT GLOBAL QUERY REWRITE TO ADMIN;
  GRANT GRANT ANY OBJECT PRIVILEGE TO ADMIN;
  GRANT GRANT ANY PRIVILEGE TO ADMIN;
  GRANT GRANT ANY ROLE TO ADMIN;
  GRANT HS_ADMIN_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT IMP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;
  GRANT INSERT ANY TABLE TO ADMIN;
  GRANT LOCK ANY TABLE TO ADMIN;
  GRANT LOGSTDBY_ADMINISTRATOR TO ADMIN WITH ADMIN OPTION;
  GRANT MANAGE TABLESPACE TO ADMIN;
  GRANT OEM_MONITOR TO ADMIN WITH ADMIN OPTION;
  GRANT OLAP_USER TO ADMIN WITH ADMIN OPTION;
  GRANT ON COMMIT REFRESH TO ADMIN;
  GRANT QUERY REWRITE TO ADMIN;
  GRANT QUEUE_USER_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT RECOVERY_CATALOG_OWNER TO ADMIN WITH ADMIN OPTION;
  GRANT RESOURCE TO ADMIN WITH ADMIN OPTION;
  GRANT RESTRICTED SESSION TO ADMIN;
  GRANT RESUMABLE TO ADMIN;
  GRANT SELECT ANY DICTIONARY TO ADMIN;
  GRANT SELECT ANY SEQUENCE TO ADMIN;
  GRANT SELECT ANY TABLE TO ADMIN WITH ADMIN OPTION;
  GRANT SELECT ON SYSTEM.DEF$_AQCALL TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_CALLDEST TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_DESTINATION TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_ERROR TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.DEF$_LOB TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.REPCAT$_REPPROP TO ADMIN WITH GRANT OPTION;
  GRANT SELECT ON SYSTEM.REPCAT$_REPSCHEMA TO ADMIN WITH GRANT OPTION;
  GRANT SELECT_CATALOG_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT UNDER ANY TABLE TO ADMIN;
  GRANT UNDER ANY TYPE TO ADMIN;
  GRANT UNDER ANY VIEW TO ADMIN;
  GRANT UNLIMITED TABLESPACE TO ADMIN WITH ADMIN OPTION;
  GRANT UPDATE ANY TABLE TO ADMIN;
 BEGIN
 SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE
  (GRANTEE_NAME   => 'ADMIN',
   PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
   ADMIN_OPTION   => FALSE);
END;
/

A developer might need these privileges depending on what you are working with

 GRANT OLAP_DBA TO ADMIN WITH ADMIN OPTION;
  GRANT AUTHENTICATEDUSER TO ADMIN WITH ADMIN OPTION;
  GRANT RESOURCE TO ADMIN WITH ADMIN OPTION;
  //java privileges
  GRANT EJBCLIENT TO ADMIN WITH ADMIN OPTION;
  GRANT JAVAUSERPRIV TO ADMIN WITH ADMIN OPTION;
  GRANT JAVA_DEPLOY TO ADMIN WITH ADMIN OPTION;
  GRANT JAVADEBUGPRIV TO ADMIN WITH ADMIN OPTION;
  GRANT JAVAIDPRIV TO ADMIN WITH ADMIN OPTION;

      -- 4 Java Privileges for ADMIN
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.lang.RuntimePermission'
        ,permission_name   => 'createClassLoader'
        ,permission_action => ''
        ,key               => KEYNUM
        );
    END;
    /
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.io.FilePermission'
        ,permission_name   => '<<ALL FILES>>'
        ,permission_action => 'execute'
        ,key               => KEYNUM
        );
    END;
    /
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.lang.RuntimePermission'
        ,permission_name   => 'writeFileDescriptor'
        ,permission_action => '*'
        ,key               => KEYNUM
        );
    END;
    /
    DECLARE
     KEYNUM NUMBER;
    BEGIN
      SYS.DBMS_JAVA.GRANT_PERMISSION(
         grantee           => 'ADMIN'
        ,permission_type   => 'SYS:java.lang.RuntimePermission'
        ,permission_name   => 'readFileDescriptor'
        ,permission_action => '*'
        ,key               => KEYNUM
        );
    END;
    /
  --advanced queue
  GRANT AQ_ADMINISTRATOR_ROLE TO ADMIN WITH ADMIN OPTION;
  GRANT AQ_USER_ROLE TO ADMIN WITH ADMIN OPTION;
      BEGIN
    SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
      PRIVILEGE    => 'MANAGE_ANY',
      GRANTEE      => 'ADMIN',
      ADMIN_OPTION => TRUE);
    END;
    /
      BEGIN
    SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
      PRIVILEGE    => 'ENQUEUE_ANY',
      GRANTEE      => 'ADMIN',
      ADMIN_OPTION => TRUE);
    END;
    /
      BEGIN
    SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
      PRIVILEGE    => 'DEQUEUE_ANY',
      GRANTEE      => 'ADMIN',
      ADMIN_OPTION => TRUE);
    END;
    /
  //moving data in and out
  GRANT EXP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;
  GRANT IMP_FULL_DATABASE TO ADMIN WITH ADMIN OPTION;


How to check if column exists in case statement? I think a case statement would be the best approach to this but am open to any other suggestions. What I am trying to do is this. The query is generated based on a users input regarding the type report that they are seeking. The thing is all possible tables except for one contain a message_id column. On the odd table, I can get the data though another field which is time_sent. Here is an example of a generated query after user input.
  SELECT
                ps.account_id,
                ba.account_name,
                NULL AS total_users,
                COUNT(DISTINCT(ps.uid)) AS unique_users,
                COUNT(DISTINCT r.msg_timestamp) AS messages_received,
                NULL AS total_clicks,
                NULL AS unique_clicks,
                COUNT(DISTINCT s.message_id) AS messages_sent, // THIS IS WHERE I NEED TO USE A CASE OR SOME OTHER MEANS TO DETERMINE IF THE message_id COLUMN EXISTS
                COUNT(DISTINCT CASE WHEN ps.added BETWEEN '2020-01-01' AND '2020-06-30' THEN ps.uid END) AS new_users
            FROM conversation_facebook_page_subscribers ps
                LEFT JOIN conversation_facebook_page_received r ON r.account_id = ps.account_id AND r.uid=ps.uid AND r.received_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN conversation_facebook_page_sent s ON s.account_id = ps.account_id AND s.uid=ps.uid AND s.send_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN business_assets ba ON ba.business_id = '3' AND ba.account_type = 'facebook_page' AND (ba.account_id = ps.account_id OR SUBSTRING_INDEX(ba.account_id, '_', -1) = ps.account_id)
            WHERE
                (ps.account_id='104522080903905' OR ps.account_id='180004075382887' OR ps.account_id='85825440048')
                AND
                (r.account_id IS NOT NULL OR s.account_id IS NOT NULL)
            GROUP BY ps.account_id, ba.account_name

I have tried to use a case statement to check the name of the table and based on what the table name is select the appropriate field, but that didn't work. I have searched numerous questions on SO to see if I could find something that would work, but nothing I have found works.
Can someone please point me in the right direction? Thank you in advance for any assistance.

You can use INFORMATION_SCHEMA COLUMNS Table
But you can't use it in a normal sql statement, because mysql will parse all parts.
I still have problems to understand, why you have missing columns in the first place.
CREATE PROCEDURE `new_procedure` (IN _account_id1,IN _account_id2)
BEGIN
        IF (EXISTS (SELECT 1 FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'conversation_facebook_page_sent '
                    AND COLUMN_NAME = 'message_id')) THEN
            SELECT
                ps.account_id,
                ba.account_name,
                NULL AS total_users,
                COUNT(DISTINCT(ps.uid)) AS unique_users,
                COUNT(DISTINCT r.msg_timestamp) AS messages_received,
                NULL AS total_clicks,
                NULL AS unique_clicks,
           COUNT(DISTINCT s.message_id) AS messages_sent,
                COUNT(DISTINCT CASE WHEN ps.added BETWEEN '2020-01-01' AND '2020-06-30' THEN ps.uid END) AS new_users
            FROM conversation_facebook_page_subscribers ps
                LEFT JOIN conversation_facebook_page_received r ON r.account_id = ps.account_id AND r.uid=ps.uid AND r.received_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN conversation_facebook_page_sent s ON s.account_id = ps.account_id AND s.uid=ps.uid AND s.send_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN business_assets ba ON ba.business_id = '3' AND ba.account_type = 'facebook_page' AND (ba.account_id = ps.account_id OR SUBSTRING_INDEX(ba.account_id, '_', -1) = ps.account_id)
            WHERE
                (ps.account_id='104522080903905' OR ps.account_id='180004075382887' OR ps.account_id='85825440048')
                AND
                (r.account_id IS NOT NULL OR s.account_id IS NOT NULL)
            GROUP BY ps.account_id, ba.account_name;
        ELSE
            SELECT
                ps.account_id,
                ba.account_name,
                NULL AS total_users,
                COUNT(DISTINCT(ps.uid)) AS unique_users,
                COUNT(DISTINCT r.msg_timestamp) AS messages_received,
                NULL AS total_clicks,
                NULL AS unique_clicks,
            COUNT(DISTINCT s.send_time) AS messages_sent,
                COUNT(DISTINCT CASE WHEN ps.added BETWEEN '2020-01-01' AND '2020-06-30' THEN ps.uid END) AS new_users
            FROM conversation_facebook_page_subscribers ps
                LEFT JOIN conversation_facebook_page_received r ON r.account_id = ps.account_id AND r.uid=ps.uid AND r.received_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN conversation_facebook_page_sent s ON s.account_id = ps.account_id AND s.uid=ps.uid AND s.send_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN business_assets ba ON ba.business_id = '3' AND ba.account_type = 'facebook_page' AND (ba.account_id = ps.account_id OR SUBSTRING_INDEX(ba.account_id, '_', -1) = ps.account_id)
            WHERE
                (ps.account_id='104522080903905' OR ps.account_id='180004075382887' OR ps.account_id='85825440048')
                AND
                (r.account_id IS NOT NULL OR s.account_id IS NOT NULL)
            GROUP BY ps.account_id, ba.account_name;
        END IF;

END


Indexed varchar column efficiency On a table with several million rows, with an index on the only column in the where clause (varchar 100, full length indexed), would there be performance gains in making sure that the differences between those rows were early in them? i.e. would it be better if those columns contained:
variable stuff-constant stuff
rather than
constant stuff-variable stuff?

It sounds like the column is a composite of two or more items.  Creating an index on two or more columns can increase the chance that a value in the key is different from any other value in the index key.  This can have its benefits.  But as you add more columns than will be used in the where clause, the performance of the index degrades because it becomes excessively complex to evaluate.

The varchar data in the index is ultimately stored in a binary format.  Therefore the order of the parts of the varchar strings (variable then a constant contrasted with constant then a variable) in the column has no affect on performance.  Operations invoking an index use a binary tree for filtering and matching.

One way to optimize the query would be to add where clauses to the query.  But sometimes there is no way to do this for business/functional reasons.

Another way would be to find a key (e.g., a composite key) with combinations of values that will likely be different from other combinations of values on a row-by-row basis.  The next step would be to create an index based on this composite key.  But sometimes there is no way to do this for business/functional reasons.


How to upgrade MySQL to a New version..? I have installed mysql using the package management system.I am using ubuntu machine.

After that i have very well configured the server for InnoDB and other server related parameters.

I am using the version

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.1.49-1ubuntu8.1-log |
+-----------------------+
1 row in set (0.00 sec)

Now i want to upgrade my MySQL server to some Higher version say 5.5.
what steps should I follow to accomplish the same...?

Thanks...!

There's a PPA that someone has created which includes MySQL 5.5.  Since PPA's are community-contributed, the usual warnings about using with caution apply.

There is a guide in the MySQL docs for some standard practices to follow when upgrading from one version level to another.

Edit:

Here is some information from the mysql_upgrade manual page that should give you an idea of why it's important. This is in response to your comment.

  mysql_upgrade examines all tables in all databases for
  incompatibilities with the current version of MySQL Server.
  mysql_upgrade also upgrades the system tables so that you can take
  advantage of new privileges or capabilities that might have been
  added.

  mysql_upgrade should be executed each time you upgrade MySQL. It
  supersedes the older mysql_fix_privilege_tables script, which has been
  removed in MySQL 5.5.

  If mysql_upgrade finds that a table has a possible incompatibility, it
  performs a table check and, if problems are found, attempts a table
  repair.


Running select filename from dbo.sysfiles returns short foldernames with tilde I am running the following statement in SQL Server 2014(in Windows 2012R2):
select filename from dbo.sysfiles where fileid = 1

However it returns:
c:\progra~1\micros~1\mssql1~1.sql\mssql\data\master~1.mdf

I wish for the full path without the tilde.
(e.g C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\master.mdf)
I tried upgrading the SQL Server 2014 Express to 2016 Express but it made no difference.
Any ideas?
Thanks.
Dave

If you use the non-deprecated equivalent sys.database_files view's column physical_name, does it provide you the full path?


How to restore mysql dump file? We have created a dump file (.sql) using the command mysqldump in mysql5.5.
Now we want to restore the same. We are using the command :-

-u[username] -p[pwd] [dbname] < [.sql file]

But we are not able to restore.
We are using Win7 SP1 32 bit OS.

Looks like you left part of your command off.  To be clear, you'll need to use the mysql client for the import.  But, in general that should work.  Another option is to just connect to the client (same command as before, but with out the the

Once in you can run

mysql> source /path/to/outfile.sql


Mariadb error 1071: Specified key was too long; max key length is 1000 bytes A bit of context: I am trying out flowable, however tomcat chokes at a certain point while populating the database, the usual exception wall of text boils down to the title of this post. First time I put my hands on tomcat in a long time, I may be missing the obvious.
Update: this query now works.
CREATE TABLE flowable.FLW_EVENT_DEPLOYMENT (
ID_ VARCHAR(255) NOT NULL,
NAME_ VARCHAR(255) NULL,
CATEGORY_ VARCHAR(255) NULL,
DEPLOY_TIME_ datetime(3) NULL,
TENANT_ID_ VARCHAR(255) NULL,
PARENT_DEPLOYMENT_ID_ VARCHAR(255) NULL,
CONSTRAINT PK_FLW_EVENT_DEPLOYMENT PRIMARY KEY (ID_)
)

What I did: I switched from utf8mb4 to plain old utf8 thanks to this bit "I know UTF8mb4 can only support up to VARCHAR(191)" (thanks @Lennart for making me think harder about this). So there is progress but I have the same error later on another query:
CREATE UNIQUE INDEX ACT_IDX_EVENT_DEF_UNIQ ON flowable.FLW_EVENT_DEFINITION(KEY_, VERSION_, TENANT_ID_)

Those three columns are respectively varchar(255) utf8_general_ci, int(11), varchar(255) utf8_general_ci.
tomcat 8.5.61
mariadb 10.4.17
I found quite a number of suggested solutions, but none seem to apply to my mariadb version, innodb-large-prefix=ON in particular has been deprecated, tried a few different collations without success. I have spent quite a bit of time on this already, tried to swap mariadb connector for mysql connector too to rule out a bug in the library itself.
For example I tried the accepted solution here to no avail, probably because:

innodb_file_format is deprecated and has no effect. It may be removed in future releases.
innodb_file_format_max surprisingly causes an error on config check, Antelope is deprecated anyway and mariadb wouldn't use it for a new table
innodb_large_prefix was removed in 10.3.1 and restored as a deprecated and unused variable in 10.4.3 for compatibility purposes.

Use ENGINE=InnoDB, not MyISAM.  Your attempts at changing settings were useless because you are not using InnoDB for the table in question.
That can be specified as the default in my.cnf before creating tables.  Else do it explicitly on CREATE TABLE.
Also, don't blindly use 255, instead use sensible limits.


SQL join query to show rows with non-existent rows in one table I am trying to get some reporting done for employee time records.

We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of work they've performed and is stored in the Time_Entry table.

Example setup with SQL Fiddle: http://sqlfiddle.com/#!3/e3806/7

The end result I'm going for is a table which shows ALL the Members in a column list and then will show their sum hours for the date queried in the other columns.

The problem seems to be that if there is no row in the Time_Entry table for a particular member, there is now row for that member. I've tried several different join types (Left, Right, Inner, Outer, Full Outer, etc.) but none seem to give me what I want, which would be (based on the last example in SQL Fiddle):

/*** Desired End Result ***/

Member_ID   | COUNTTime_Entry | TIMEENTRYDATE | SUMHOURS_ACTUAL | SUMHOURS_BILL
ADavis      | 0               | 11-10-2013    | 0               | 0
BTronton    | 0               | 11-10-2013    | 0               | 0
CJones      | 0               | 11-10-2013    | 0               | 0
DSmith      | 0               | 11-10-2013    | 0               | 0
EGirsch     | 1               | 11-10-2013    | 0.92            | 1
FRowden     | 0               | 11-10-2013    | 0               | 0

What I'm currently getting when I query for specific date of 11-1:

Member_ID   | COUNTTime_Entry | TIMEENTRYDATE | SUMHOURS_ACTUAL | SUMHOURS_BILL
EGirsch     | 1               | 11-10-2013    | 0.92            | 1

Which is correct based on the one Time Entry row that is dated 11-10-2013 for EGirsch, but I need to see zeros for the other members in order to get reports and eventually a web dashboard/report for this information.

This is my first question, and while I searched for Join queries, etc. I'm honestly not sure what this function might be called, so I hope that this isn't a duplicate and will help others too trying to find a solution to similar problems.

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy
consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.


SQL Server MCSA Certification I want to take MCSA: Sql Server 2016 Database Administration certification.
I checked this link :

https://www.microsoft.com/en-us/learning/mcsa-sql2016-database-administration-certification.aspx

It says, I have to attend 70-464 and 70-465 exams. Is there any other precondition? I couldn't find clear certification path.

PS: I never attend certifications exam before. This will be my first.

Thanks.

You are just looking at an old page. It even says that it is only being maintained for reference purposes only:

  This page is being retained for reference purposes only. The information on this page, including statements about upgrade and/or substitution options, is not being updated and may no longer be accurate.

For the certification track you wish to take use the link below instead:

MCSA: SQL 2016 Database Administration

In brief, you just need to pass your 70-764 and 70-765 to earn the MCSA: SQL 2016 Database Administration Certificate. No more, no less.


SQL Server Agent Notification Email if a Step has an error, but the job is succesfull? Is it possible, that I can receive Email from Server Agent, if a step in an Agent has an error, but the Job is successful? In the history, I can see, that for example one Step has an Error, but all others were successful.

Yes you can achieve this with help of SP:

CREATE PROCEDURE TestEmailOnFail
AS
BEGIN
    BEGIN TRY
        /*
            Perform some action that might fail
        */
        SELECT 0/0; --THIS WILL FAIL
    END TRY
    BEGIN CATCH
        DECLARE @subject nvarchar(max) = 'Job Failure Notification';
        DECLARE @body nvarchar(max) = 'TestEmailOnFail Job Failed'
            + CHAR(10) + CHAR(13) + 'Error Number:  ' + CAST(ERROR_NUMBER() AS nvarchar(max))
            + CHAR(10) + CHAR(13) + 'Error Message: ' + ERROR_MESSAGE();
        DECLARE @to nvarchar(max) = 'somebody@email.com';
        DECLARE @profile_name sysname = 'SQLMailProfileName';
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
            @recipients = @to, @subject = @subject, @body = @body;
    END CATCH
END

You can refer here how it is being used.

Adding@ You can recieve email if Database mail is configured in very first place. On how to set that up read How to Set Up Database Mail for SQL Server Job Failures

Also, In SQL Agent Job Properties-> step > edit> Advanced --you can also check Include step output in history to get the message from step failure.

Leave the On failure action to Quit the job reporting failure.


Database design - model a 'Company' with 'Owner', 'Partner', 'Employee' I'm working on an app that has mainly two entities: Company and User. A User can be an employee, owner, or a partner.

My current approach of modelling this is:

Capacity is enum with possible values: employee, owner, partner

Someone got me confused suggesting I should have three separate tables for each of these capacities, which I don't think is right.

I'm terribly unsure whether the capacity could grow to more in future.

Should I create another capacity table and reference it in Personnel as a foreign key, in case we wish to add more capacity? Would that be a scalable (and right) approach?

Thank you.

I would not have separate tables but you might want to consider not using enum for the capacity column.

Instead use a table named, perhaps, capacity that has an id referenced in the personnel table (much like user ID and company ID). That makes it trivial to add new titles/positions without disturbing anything else.


"Rewinding" a Postgresql database I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came after it.

Anyone know how I can do this?

Thanks

At one point, this was in principle possible.  All you had to do in theory was to reset the current transaction ID, and you would see a previous state of the database.  Search for "time travel" in very old documents.

That, however, was before WAL, HOT, lazy vacuum, freespace map, virtual transaction IDs, and all the other goodies that were introduced among other things to work around the drawbacks of the append-only system for the majority of applications that don't need to look back into the past.  Now, the system is more like append-mostly-but-clean-up-old-stuff-as-quickly-as-possible.

So, if you want to look back at an old state of the database, you need to use point-in-time recovery, which is a log-based mechanism and has nothing to do with the nature of the storage management.


SQL Server Failover Cluster Instance compatible with VMware HA? Imaginary Setup: Microsoft SQL Server Failover Cluster Instance on virtual machine in an ESX-Cluster.

Question: In case of a hardware failure, what will happen with both high availability configurations?

will VMware migrate the VM to another ESX-host?
will the failover cluster istance failover to another VM?
both high availability options try to act, what happens?

Please let me know if my question is unclear or any information is missing. Thanks in advance!

+---ESX------------+                               +---ESX--------------+
|                  +--------ESX-heartbeat----------+                    |
| +-VM-----------+ |                               | +-VM-------------+ |
| |              +---------------WSFC----------------+                | |
| | +----------+ | |                               | | +------------+ | |
| | |DBMS      +---------------MSSQL FCI---------------+DBMS        | | |
| | +----+-----+ | |                               | | +------+-----+ | |
| |      |       | |                               | |        |       | |
| |      |       | |                               | |        |       | |
| |      |       | |                               | |        |       | |
| |      |       | |                               | |        |       | |
| |      |       | |                               | |        |       | |
| |      |       | |                               | |        |       | |
| |      |       | |                               | |        |       | |
| +--------------+ |                               | +----------------+ |
+------------------+                               +--------------------+
         |                                                    |
         |                                                    |
         |              +------------------+                  |
         |              |       SAN        |                  |
         +--------------+                  +------------------+
                        |                  |
                        |                  |
                        +------------------+

There are so many factors in place here as you can have multiple failover paths defined both in VMWare and the failover cluster. So the answers are almost as many as there are configuration options.

But in general if a single EXI host stops. VMWare will migrate it's VM's to another and as all the virtual machines will have shut down the second windows failover cluster node will become the primary node.


How to guarantee rows order I'm trying to implement a methodology to check the integrity of a restored database. It goes as following:

Create a snapshot of the database.
Gather various metadata:

On disk used space.
Name of tables.
Number of rows in each tables.
...

Generate a cryptographic hash using the data in each table.

My issue goes with the 3rd step to generate the cryptographic hash. I plan to read each row one by one and update my hash with the data they contain. But for my check to match I have to make sure that the row order stays the same.

How can I make sure to always get the same row order, assuming that I don't know upfront the table structure and on what column I can order on.

ORDER BY is the only way to guarantee order. If you have a unique key (primary key, unique index) then order by this, otherwise you'll need to order by all the columns in the table. If you have multiple unique keys and one of them is the clustering key for the table (or the first part of a larger clustering key) then ordering by that would be most efficient. As you don't know ahead of time what table definitions you have you will need to work out what candidate sorting keys you have by checking the contents of sys.indexes and friends.


weird postgresql query runnning on aws postgres db got this weird query on my postgres aws server

SELECT
    ix.relname AS index_name,
    upper(am.amname) AS index_algorithm,
    indisunique AS is_unique,
    REPLACE(regexp_replace(regexp_replace(pg_get_indexdef(indexrelid), ' WHERE .+', ''), '.*\((.*)\)', '\1'), ' ', '') AS column_name,
    CASE WHEN position(' WHERE ' IN pg_get_indexdef(indexrelid)) > 0 THEN
        regexp_replace(pg_get_indexdef(indexrelid), '.+WHERE ', '')
    ELSE
        ''
    END AS condition
FROM
    pg_index i
    JOIN pg_class t ON t.oid = i.indrelid
    JOIN pg_class ix ON ix.oid = i.indexrelid
    JOIN pg_namespace n ON t.relnamespace = n.oid
    JOIN pg_am AS am ON ix.relam = am.oid
WHERE
    t.relname = 'sms_verif'
    AND n.nspname = 'public'
    AND indisprimary = FALSE;

anyone ever got this weird query running ? is this somekind of malicious query ? thanks for the help in advance

It looks like the kind of query that would be run by a DB management tool, such as PgAdmin or DBeaver, so that it can populate its navigation pane with a list of database objects or offer a context menu when a specific object has been drilled down into.  It does not appear to be malicious, although it is possible someone could be using such a tool maliciously.


Proper indexes or anything else to optimize I need help to optimize the query to avoid using "Using temporary", "Using filesort".

CREATE TABLE `target_bahrain_pepsi` (
    `id` int(11) DEFAULT NULL,
    `col_value` varchar(50) DEFAULT NULL,
    `source_file_id` int(11) DEFAULT NULL,
    KEY `id` (`id`)
) ENGINE=InnoDB

SELECT su.adbrand_id, sum(st.col_value) as amount
FROM statex_with_id su  INNER JOIN target_bahrain_pepsi st  ON st.id = su.id
GROUP BY su.adbrand_id
ORDER BY amount DESC ;

Table statex_with_id has also index.

mysql> EXPLAIN select su.adbrand_id, sum(st.col_value) as amount
    -> from statex_with_id su
    -> INNER JOIN target_bahrain_pepsi st
    -> ON st.id = su.id GROUP BY su.adbrand_id ORDER BY amount DESC ;
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows    | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | st    | index  | id            | id      | 58      | NULL             | 1804021 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | su    | eq_ref | PRIMARY       | PRIMARY | 4       | dashboard1.st.id |       1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+

According to the MySQL docs, you can't avoid "using temporary" and "using filesort" when you have different ORDER BY and GROUP BY expressions. You need to rewrite the query--which isn't always possible--or live with the pain. (Or install faster hardware.)

About "using filesort",  MySQL docs say,

  In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

  (many lines snipped)

  You have different ORDER BY and GROUP BY expressions.

About "using temporary" MySQL docs say,

  To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

There are many different and related reasons you probably can't avoid "using temporary" and "using filesort". The docs are pretty good about explaining the reasons.


How to verify an oracle database license We have bought an oracle 11g database license the supplier gave us a paper with oracle logo and said that is the license, we were expecting our company name to appear on it, but it doesn't.

My question is :

Is there anyway to verify if that paper is really a license,
what does an oracle license look like.

Thanks

Anybody can download and install oracle software without any installation codes being necessary. So in effect an Oracle licence is simply an entry in their Licence Management Services (LMS) database. Depending upon what you buy you will get a CSI number. In working with Oracle for more than 15 years this is all I have ever seen at customer sites: a piece of paper with a description of the licence(s), the total number bought and a CSI number. One way to verify your licence would be to create an account on their support site (support.oracle.com) and add your CSI to this account. Then when you log in you can see which products are associated with this CSI. An alternative would be to do as Threaten suggests and simply email LMS and they will send you a list of what they think you own. For the amount of money you pay for an 11gDB licence I would expect something more substantial but unfortunately a sheet of plain text is all you get.


Do threads in cache still use memory? I was studying about how and how much memory allocated to each connection thread and found below MySQL tutorial:

http://dev.mysql.com/doc/refman/5.1/en/memory-use.html

There is a point mentioned as below which I am unable to understand.

When a thread is no longer needed, the memory allocated to it is
released and returned to the system unless the thread goes back into
the thread cache. In that case, the memory remains allocated.

My concerns are the following:

Does it mean thread occupied the memory even system is using threads from cache?
A cache in thread still use memory?

I wrote these posts

Mar 28, 2014 : MySQL not releasing memory
Apr 24, 2012 : How costly is opening and closing of a DB connection?
May 24, 2011 : What are user connections - when are the created and destroyed?

and discussed the many buffers that are allocated for each connection. They are part of RAM and not encapsulated within the cache. Since the buffers are set based whether it is defined in my.cnf or defaulted if it is not, it would not make sense that the memory is released if a thread needs to be reused and will set its buffers to the same sizes again. As I mentioned in How costly is opening and closing of a DB connection? it would be an expensive operation to deallocate and then immediately allocate the buffers.
Therefore, with regard to your actual questions

Does it mean thread occupied the memory even system is using threads from cache?
A cache in thread still use memory?

The answer to both would be Yes


Index maintenance Azure SQL I have installed Ola's scripts:

https://ola.hallengren.com/scripts/CommandLog.sql
https://ola.hallengren.com/scripts/CommandExecute.sql
https://ola.hallengren.com/scripts/IndexOptimize.sql

and ran:

EXECUTE dbo.IndexOptimize
@Databases = 'mydbnamehere',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

It took about 20 minutes. and after that I ran this SQL statement:

SELECT name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (
   DB_ID(N'mydbnamehere')
 , NULL
 , NULL
 , NULL
 , NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 30

And I still had about 19 rows with an avg fragmentation between 99 and 40 %

99,65075669383
99,0767455279861
98,7414187643021
98,5574148874784
98,4420080784766
97,8723404255319
97,5903614457831
97,4522292993631
96,8911917098446
96,551724137931
95,4356846473029
87,5
76,1904761904762
75
51,063829787234
50
50
40
33,3333333333333

Did I make a mistake in my parameters?
It did run and rebuilded and reorganized several indexes. It took down the list from about 25 to 19 rows. (4x foreign keys, 3x primary keys, 4 indexes and the rest without a name)

Remember indices with less that 1000 pages are ignored by default, this can be changed via PageCountLevel parameter but it's based on Microsoft's recommendation according to ola hallegreen's reference. Check this other answer for further details.


Extended Events Collation resolution error When trying to expand the sessions under extended events I am getting a Collation Resolution error in SSMS 18.2 (15.0.18142):

Looking at a trace I have found that this query is what is giving me the collation error but I can't see why? All of the system DB's have the same collation

SELECT * FROM sys.server_event_sessions AS session
LEFT OUTER JOIN sys.dm_xe_sessions AS running ON running.name = session.name

This appears to be a conflict between the instance (i.e. server) -level collation and the collation being used in the [master] database. Both sys.server_event_sessions and sys.dm_xe_sessions are system catalog views, and the collation of their [name] fields should be the same.

sepupic had the O.P. execute the following:

select top 1 sql_variant_property(name, 'collation') from sys.server_event_sessions;
select top 1 sql_variant_property(name, 'collation') from sys.dm_xe_sessions;

The results were:

sys.server_event_sessions = Latin1_General_CI_AS
sys.dm_xe_sessions        = SQL_Latin1_General_CP1_CI_AS

That difference should not be possible, but at least it explains the error message.

I requested that the O.P. execute the following to determine the instance-level collation:

SELECT SERVERPROPERTY('collation');

O.P. replied with:

  ... the server collation is "SQL_Latin1_General_CP1_CI_AS". I was under the impression that the Server collation was whatever the master DB collation was. This server was installed from a Microsoft Azure image so the collation was changed from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS.

Clearly something went wrong with the collation change process. I was able to reproduce the error by creating a database containing one table with one string column. I used a case-sensitive collation and that one column was also the Primary Key (meaning: a unique index). I added two rows: N'A' and N'a'. I then used the sqlservr.exe -q method (details linked below) of globally updating the collations to change the instance's collation to a different one, one that was case-insensitive. When the process tried to rebuild the PK, it failed as the case-insensitive collation rendered the second row not-unique from the first. But, the system DBs has already been updated. However, the instance level collation was not changed to the new collation as the process never completed.

To fix this, I believe the following would work:

Detach all User DBs (to minimize impact of the operation: the user DB's are not a problem, and some columns might have different collations set that shouldn't be changed)
Update the instance's collation using the sqlservr.exe -q method
Re-attach the User DB's


Reducing size of image returned by view We are storing a list of images in a table in an image column. These are stored as their raw image size so they can be viewed in their details at full resolution.

The problem I have is I want to show them in a listing in our application and I just want to show them as thumbnails. When I retrieve them via a view our entityFramework code bombs with out of memory errors trying to resolve all the images into fields in the code.

I'm wanting to know whether I can resize the image column at the database view level before it hits the application. i.e. In our view have a function that takes the full size image and resizes it to 80x80 and returns it. e.g.

Select fxShringImage(EntityImage) From EntityImage

Any ideas? Is this possible?

First and foremost do not use the IMAGE data type, is an old deprecated type:

  text , text, and image data types will be removed in a future version
  of Microsoft SQL Server. Avoid using these data types in new
  development work, and plan to modify applications that currently use
  them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

You need to store multiple images, one for each resolution desired (full/preview/thumb). In general these resized images must be generated during the upload of the image. Strictly speaking it is possible to generate the preview/thumb on-demand on first display, or even on each access (ie. not saved), but this is seldom the right choice. In general a file is uploaded once and viewed multiple times, so it makes more sense to pay the penalty of generating the thumbnail at upload (write) time. And users are perceive the site/application as 'slow' if the image is generated on-demand on first access. See Asynchronous Execution for how to run jobs like uploaded image re-size in an reliable asynchronous manner.

As for loading fat content into entity lists in the client (irrelevant whether is images, documents or whatever other BLOB), just don't do it. You will kill your server, your network, your appserver, your client app, everything. Load narrow lists with only the fields needed for display. Lazy load on-demand expensive content. This is CS101 and applies to every technology.

PS. I won't even go into the topic of whether is better to store images on disk or in db. I assume you already did due diligence for that.


MySQL percona 5.6 to MySQL 5.5 replication Scenario 1 I am having a MySQL DB on 5.5 which is compliance, So need to upgrade it to MySQL 5.6. while doing this we see that 5.6 is running slow, were as it runs perfectly on 5.5 backup.

Scenario 2 We have installed 5.6 percona MySQL on one system & just want to know if we can replicate on MySQL 5.5 using MySQL replication.

Please advice what issue will be faced in this 2 scenario. any suggestion is acceptable.

It isn't clear what you're asking in scenario 1.

As for scenario 2, you can have a slave that's a higher version than the master, but not vice-versa. From the documentation:

  MySQL supports replication from one release series to the next higher release series. For example, you can replicate from a master running MySQL 5.5 to a slave running MySQL 5.6, from a master running MySQL 5.6 to a slave running MySQL 5.7, and so on.


Transform a Lease relation into Third Normal Form (3NF) Consider the relation LEASE, where a person can live in only one building, and a building can charge only one rental rate:

RELATION = LEASE (PersonID, BuildingID, Rent), Primary Key = {PersonID}

I try:

LEASE ( PersonID, BuildingID )
BUILDING ( BulidingID, RentID)
RENT ( RentID, Rent)

Is it right?

In the real world a person can obviously hold more than one lease at the same time - a primary residence, holiday home and business premises, for example.  Then the primary key of lease would be both PersonID and BuildingID together.  If in your case you artificially constrain this to only one lease per person at a point in time then you are mostly there.  However

You need some sub-division of Building.  Otherwise a person must
lease the entire building.  This may be true for houses; it is
unlikely for office blocks.
You state "a building can charge only one rental rate." Remove rent from LEASE. It can be found via the relationship through BUILDING.
You will likely want a PERSON relation
to hold things like name, date of birth etc.
RENT is only useful if there is some external agency defining a small set of rent values which it is permissible to charge.  If each building's owner can charge what he likes, remove RENT and add an attribute to BUILDING.  Just because Building A's owner charges the same rate as Building B's owner does not make them the same thing.  If Building A's rent changes there is no rule which says Building B's must also change at the same time.
LEASE will require a unique constraint on PersonID to ensure a person does indeed rent only once.


SQL query returns different results when index is dropped In SQL Server 2000 SP4, I have a query

SELECT <fields no aggregates>
  FROM PS_VED_VCH_TAO5 A
    ,PS_BUS_UNIT_TBL_AP B
    ,PS_RT_DFLT_VW C
    WHERE   A.PROCESS_INSTANCE = 594170
    AND A.VAT_CALC_AMT <> 0
    AND A.VAT_ENTRD_AMT <> 0
    AND A.VAT_ENTRD_AMT <> A.VAT_CALC_AMT
    AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
    AND A.VCHR_SRC <> 'PROC'
    AND B.VCHR_VAT_MSG_OPT = 'E'
    AND A.VAT_CALC_TYPE = 'E'
    AND A.VAT_RCRD_INPT_FLG = 'Y'
    AND A.VAT_RCRD_OUTPT_FLG = 'N'
    AND C.FROM_CUR = A.TXN_CURRENCY_CD
    AND (
            (C.TO_CUR = A.VAT_TOL_CUR_CD AND C.RT_TYPE = A.VAT_TOL_RT_TYPE)
            OR
            (C.TO_CUR = A.TXN_CURRENCY_CD  AND C.RT_TYPE = A.RT_TYPE  AND A.VAT_TOL_CUR_CD = ' '  AND A.VAT_TOL_RT_TYPE = ' ')
        )
        AND C.EFFDT =
        (
            SELECT MAX(I.EFFDT)
                FROM PS_RT_DFLT_VW I
                    WHERE I.FROM_CUR = C.FROM_CUR
                        AND I.TO_CUR = C.TO_CUR
                        AND I.RT_TYPE = C.RT_TYPE
                        AND I.EFFDT <= A.INVOICE_DT
        )
        AND
        (
            ((A.VAT_TOL_AMT < (((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( C.RATE_DIV))) * ( C.RATE_MULT)))
            OR
            (A.VAT_TOL_AMT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( C.RATE_DIV))) * ( C.RATE_MULT))) * ( -1)))
        )
        AND
        (
            (A.VAT_TOL_PCT < (((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( A.VAT_CALC_AMT))) * ( 100)))
            OR
            (A.VAT_TOL_PCT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( A.VAT_CALC_AMT))) * ( 100))) * ( 1)))
            )
        )

PS_BUS_UNIT_TBL_AP has a unique clustered index on it. If the index is present, the above query results in a division by zero in

(A.VAT_TOL_PCT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( A.VAT_CALC_AMT))) * ( 100))) * ( 1)))

despite the fact that the query explicitly excludes rows where VAT_CALC_AMT is zero.
If the index is dropped it doesn't ( and the query plan changes).
Also, if the query is changed to add a bogus condition "A.OPERATORNAME <> 'Non existent user'", it also works normally.

is this a bug in SQL or is something else going on?

When you create/drop the index, you also affect SQL Server Query Optimizer's plan created to service your query.

Have a look at this Connect item.

Basically, SQL Server is free to optimise the expressions in the SELECT clause prior to applying the WHERE conditions.  Not very intuitive, but someone decided it should do so.

Normally, to avoid divide by zero errors, I throw a NULLIF in there which is sufficient to get past the error while not really affecting the result (since the WHERE clause actually prunes them).

e.g.

(A.VAT_TOL_PCT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( NULLIF(A.VAT_CALC_AMT,0)))) * ( 100))) * ( 1)))


Item availability in row or another table I'm a database newbie, so please bear with me.

Say I'm creating a library database. I have a table containing information about books. Do you put the book availability as a row in this table or would you create another table containing only the book (using a foreign key) and the availability?

Any pointers would be gratefully received.

It's depend on what you want achieve. What your database will do? Do you want track who lend book and when? You can create table with transaction and based on return date set availability. Another question is how you want to implement this logic. It could be controlled by your front-end or you can create view, stored procedures or implement triggers. For simplicity you can add column IsAvailable as bit (1,0).
I don't now what database you are using.