Give users permission to read subsets of data in tables in SSRS I'd like to create a SSRS report which utilizes data from our Data Warehouse, DW. This DW has summarized, for example, the sales of 100 or so stores into one table FactStoreSale.

In the report I've managed to see which AD user who has started the report, by using the expression =User!UserID as an default value in a parameter.

Each AD user is included in a AD Security Group. This Group typically includes the manager Domain\first.lastname and the employees shared account Domain\Store1000User. The name of the Security Group is typically GRP_1000 (for StoreKey 1000).

Is it possible to allow users in a Security Group to only see data corresponding to that StoreKey, i.e. if some user included in the Security Group GRP_1000 uses the report, the resulting set will be:

Select Sales, Quantity FROM FactStoreSale
WHERE StoreKey = 1000

Note: I do not wish to create a report for each Store, as that would require 100 reports. Some managers can manage more than one store, so they may be included in multiple Security Groups and need the corresponding permissions.

I do not want to split up the underlying table FactStoreSale, even though it should be possible to split this up into 100 or so schemas, and give members of the Security Groups access to these scehmas accordingly.

A solution I have in mind is to create a table using SSIS which gets updated every day with the appearence:

SecurityGroup    StoreKey
GRP_1000         1000
GRP_1001         1001
GRP_1002x        1002

and then use this table to reduce the amount of stores a user can see data from.

I do not know how I can utilize the Security Group in SSRS though, seeing as I have only found UserID in the Built-in Fields in SSRS, but nothing about their respective Security Groups.

I am also not sure if this is the optimal way to go, as it requires additional tables and manually updating the table (because I do not wish to automate this procedure yet to have a higher Control over this).

EDIT

I found a way to get the permission path, i.e. Group belonging to a user using SQL server and the Query:

EXEC xp_logininfo 'Domain\User.Name', 'all'

I've tested in SSRS that I can replace 'Domain\User.Name' with my @User_id variable. I've tested to create a dataset with this query. This seems to give the correct data, but what it lacks is the StoreKey column which I thought could be Cast(RIGHT(Permission_path,4) as int).

I'd like to be able to have a variable called @StoreKey in SSRS where Available Values from Query would be something like:

Select Cast(RIGHT(Permission_path,4) as int)
FROM EXEC xp_logininfo @User_id, 'all'

, but naturally I can't make an EXEC in the FROM clause. I also tried to make a new Dataset in SSRS which would use data from another Dataset within the same Project but that did not work either (and I Googled that it is not possible).

Edit: final solution

What I did to implement this was to create a table of the appearence:

ADGroup     StoreKey
Group_HQ    1010
Group_HQ    1011
Group_HQ    1012
Group_HQ    1000
Group_1010  1010
Group_1011  1011

I then created a variable called StoreKey in SSRS where I used Available values from dataset and where I had a dataset where I selected StoreKey using a JOIN on the current user's AD Group using the Query provided by sepupic and my table I had created. Thus I only got the StoreKeys available to certain AD Groups.

You can use this code to obtain all the Windows groups of which current login is a member:

select distinct name
from sys.login_token
where type = 'WINDOWS GROUP';

You can insert all these groups into a table variable/temp table and then use it in a join with your table that maps Win Groups to StoreKeys which you further join with the table of interest


What does this code do? I found one of my queries was over written with following code:

SELECT
    cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM
    sys.dm_exec_cached_plans AS cp
CROSS APPLY
    sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY
    sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE
    st.text LIKE  '%StatRiskPrem_udf%'

I believe maybe one of the admins might have done this accidentally because I do not remember having written this code. As a matter of fact I don't even know what the code does but I might have copied it from somewhere and pasted in and saved by mistake myself. Before I go asking the admins if they overwrote my original query, does anyone here know what this code seems to be trying to do? If i knew what the code was trying to do then I might be able to remember if I did this myself or not.
Thanks!

That query looks in the procedure cache for a cached plan that contains the text StatRiskPrem_udf.  The result set will contain the following:

The object type that is cached
The cached object name
The amount of times the plan was used
The cached SQL text
The cached query plan

I would guess that the objective is to find out how many times that UDF was referenced.  I wouldn't say that's extremely accurate, though, because the procedure cache is a revolving door, especially under memory pressure.  But that's the gist of the query.


Does DBMS (e.g. of SQL SERVER 2008) use virtual memory? Or in other words ... . Does the DBMS somehow circumvent the OS Cache manager and acccess the memory directly OR it lets the OS do its job, behave as a normal process and use the concept of virtual memory?

Virtual Memory is an indirection mechanism that map virtual addresses to physical addresses and the translation occurs in hardware layer through PTEs. A process can refer and address only virtual memory. Only privileged execution (kernel) can address physical memory. I recommend you read a book on the subject, the classic being Windows Internals by Russinovich/Solomon.

Your explanation is asking though a completely different question, whether OS 'Caches Manager' is circumvented. Virtual Memory is not a cache and there is never any sort of 'caching' in any discussion of a memory manager architecture (on any OS). It is not clear what 'cache manager' are you referring to. Does SQL Server do buffered IO (ie. uses OS file cache)? Generally no, see How It Works: Bob Dorr's SQL Server I/O Presentation.


How to populate additional rows after some calculation or addition I have following table,

Table's Weight entries goes up to 100, now I need to increase this to 10000. I am able to make a pseduo code of what I want but I am not sure how to do it in T-SQL,

Loop and add 100 to weight and cost of 100 which is not visible in above table but let's assume it's 600.

If weight is more then 100, then following formula will be used,

number of 100's in weight x equilent cost

E.g. if weight is 350 then calculations will be,

Final Cost = (3 x 600) + (50 x 400)

-- 600 is cost of 100 weight
-- 400 is cost of 50 weight

I am only assuming weights here but in script I want it to read directly from table. Is there any way to achieve this easily ? or i will need to create an app ?

insert into table1
(
    Weight ,
    Cost ,
    CategoryId
)
select
    Weight + 100,
    Cost + (select Cost from table1 where weight = 100 and CategoryId = of respective row ????),
    CategoryId
from
    #temp_table1

not sure how to complete this

It would help if you could give us the DDL and insert statements for your data.

I have made a test setup with records available for weight 100 and 50, and wrote a generator function that includes new records for weight between 101 and 10000, but only execute it for weight 350 and category 1 (else I need too much additional samples available).

Fiddle SQL-Server 2017

Start

Weight  Cost    CategoryId
   100   600             1
    50   400             1

End

Weight  Cost    CategoryId
   100   600             1
    50   400             1
   350  2200             1            -- 2200 = 3*600+400

SQL:

    with
      x (n) as (
            select cast(101 as decimal(8,1)) union all
            select (cast(x.n + 0.5 as decimal(8,1))) n from x where x.n <= 350 /*10000*/
    )
    , y (m) as (
            select 1 union all select (y.m + 1) m from y where y.m <= 1 /*10*/
    )
    , input as (
            select n, cast(floor(n)/100 as int) h, n%100 u, m from x
            cross join y
    )
    insert into table1
    (
        Weight,
        Cost,
        CategoryId
    )
    select
        i.n Weight,
        (i.h * t1.Cost + t2.Cost) Cost,
        i.m CategoryId
    from input i
    inner join table1 t1 on t1.CategoryId = i.m and t1.Weight = 100
    inner join table1 t2 on t2.CategoryId = i.m and t2.Weight = i.u
    -- TEST
    where i.n = 350
    option (maxrecursion 0);


MS SQL Server - Working Mechanism of Top clause Intro
This question is basically asked to understand how the TOP clause works in SQL.
Example
There is a table Table1 in which there are 10 Million records.
If I fire the following query SELECT Top 10 Id from Table1 where Id>0 order by Id it returns the first Top 10 Id in the table.
Question
How the data is actually retrieved?
Assumption 1: Does it get all the 10 Million records from the database and searches the Top 10 records from it? [More CPU, RAM intensive]
OR
Assumption 2: Does it directly searches for Top 10 records based on the where clause on the table and returns only those 10 records? [Less CPU, RAM intensive]

As well as the documentation MBuschi pointed to, Bren Ozars free "how to think like the SQL engine" is a good resource as you start to dive into how SQL Server works in more depth to help understand how to optimise things. Also Use The Index, Luke is an excellent free resource, and not just for MS SQL Server.
To answer your question specifically:
If there is an index on Id, which is likely as it is probably the table's primary key, SQL server can use that for both the ORDER BY and WHERE clauses: it will use the index to seek to the first instance of Id being >0 only needing to read a couple of index pages to do so, then it will walk down the index to pick off the next 9 rows too. As you are only asking for Id to be output, it doesn't need to look for any more information after that.
If there is no index on Id then it has to work harder. It will scan the table from the beginning until it finds ten rows that match the where clause (which could be the first ten it reads, or it could have to scan all the way to the end, if there are not 10 there for it to find then it will have to scan all the way to the end). Once it has found those rows it will sort them before output. For such a small number this will be done in memory, but if you'd asked for a lot of rows it would have to spill the data to disk to perform the sort. In terms of complexity you can think of it as essentially creating a temporary table with an index on Id and put the matching rows into that to sort them then reading them back out - in fact if you look at the IO stats (add SET STATISTICS IO ON before your query, or click the relevant toolbar button in SSMS) you'll see this internal entity referenced as "Worktable".


Shared connection pool in SQL Server and prevention of cross-database queries Microsoft recommends that a shared connection pool should be used when running e.g. a SaaS software with one database per customer but with a shared codebase to prevent pool fragmentation:
https://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.110%29.aspx

When taking out a connection from the pool the database to access should be first be changed by the command "USE Database_A".

However, as I see it, all the databases would then use the same login with let's say the db_datareader - role right.

Is there any easy way to prevent the user from making cross-database queries or directly querying other databases using a syntax like "SELECT * FROM Database_B.dbo.Person"?

How Connection Pooling works

The issue with connection pool fragmentation is that each connection pool is bound to the exact text of the Connection String, including the SID of the Windows Login when using a Trusted Connection / Integrated Security. The connection pool works because the connection is exactly the same in every way.

Hence, changing the database after connecting doesn't change anything from a security perspective. The advice to change the database after connecting (which can also be done via the SqlConnection.ChangeDatabase method), is to get around the fact that putting the "Initial Catalog" (or "Database") key/value pair into the Connection String means that those connections aren't using the exact same connection string.

With the above in mind, whether you are using Windows Logins (i.e. Trusted Connection / Integrated Security) or SQL Server Logins (specifying UserID & Password OR using a SqlCredential),

if the web app connects as the same Login for all requests, then as long as you don't specify the database in the ConnectionString (or at least always specify the same database), and only change the database context after making the connection, then you will get the most re-use out of connection pooling since all connections will be able to use the same pool. Else you will get a pool per Database.
if the web app connects with different Logins, then:

in the one-database-per-client model (assuming they are only allowed to connect to their database, hence using a different Login per client), you can either always specify the same database for that Login, or not specify any database in the ConnectionString and only change the database context after making the connection, and you will get a pool per Login (or per Database: they are kinda the same thing in this model).
in the multi-tenant model (assuming the app makes calls to multiple databases), as long as you don't specify the database in the ConnectionString (or at least always specify the same database), and only change the database context after making the connection, then you will get a pool per Login. Of course, if you specify the database in the ConnectionString, then you will get a pool per Login per Database!

What this all means in practical terms

No, you cannot have a single connection pool and "prevent the user from making cross-database queries". The very nature of a connection pool it is that it is the same connection because the connection is not closed. You cannot differentiate permissions when there is nothing to differentiate; the same Login is still logged in, just issuing another command.

Reducing the number of connection pools down to a single pool is at odds with the desire to have different permissions:

If you want to prevent a Login from accessing more than their database, or maybe confined to their database and any "shared" databases, that requires different Logins, and each one will have their own pool. If you go with Contained Databases, you still need to specify either the UserId & Password OR the Initial Catalog in the Connection String, and hence the result will still be one connection pool per each variation.
If you want to have just one connection pool, then you have no choice but to always use the same security credentials (regardless of them being based in Windows or SQL Server). And the same credentials means the same permissions.

The bottom line is, the wording of that MSDN article is a bit confusing / awkward, and you are over-estimating their advice. The issue of Connection Pool Fragmentation really shows up when:

you are changing both the security credentials AND the "Database" / "Initial Catalog" across connections
the app is multi-tenant (i.e. not one database per customer) such that each Login can connect to multiple Databases.

Having multiple Logins that connect to multiple Databases will give you more pools that are less frequently reused. Given that the unused connections linger for "approximately 4-8 minutes" (taken from the MSDN page you linked to, the "Removing Connections" section), this creates the inefficient situation of having too many idle connections.

But, having one Connection Pool per-Login OR per-Database is actually just fine.

Please note, however, that even with one Connection Pool per-Login OR per-Database, you can still experience too many idle connections if you have a lot of clients (e.g. Web Apps, back-end processes, etc.) hitting SQL Server from different servers.


Are these MySQL queries efficient? It took me quite a while to learn how to deal with timestamps in MySQL and I think I accomplished my goals, but I want to double-check two things:

1) Are these doing what I expect

2) Are there faster replacements for these queries / functions

Today

SELECT COUNT(*) AS count FROM log WHERE DATE(datet) = DATE(NOW())

This Week

SELECT COUNT(*) AS count FROM log WHERE YEARWEEK(NOW()) = YEARWEEK(datet)

Last Week

SELECT COUNT(*) AS count FROM log WHERE YEARWEEK(datet) = YEARWEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK))

This Month

SELECT COUNT(*) AS count FROM log WHERE datet > DATE_SUB(NOW(), INTERVAL 1 MONTH)

Last Month

SELECT COUNT(*) AS count FROM log WHERE datet > DATE_SUB(NOW(), INTERVAL 2 MONTH) AND datet < DATE_SUB(NOW(), INTERVAL 1 MONTH)

The queries where you have your datet column wrapped within a function (e.g. DATE(), YEARWEEK()) are not efficient due to the fact that MySQL has to evaluate the output of the function for each row in your table. This renders the query as non-sargable (i.e. not able to utilize indexes on the datet column).

It appears you want to select the count of all records within a particular timeframe. To do this, you have to use >=, not =. Also, there is a difference between "this month" and "within one month". I'm assuming you want the former (i.e. if say today was August 3rd, you'd only want records within August, and not from July 3rd, and same thing for week, etc...).

Assuming datet is a DATETIME/TIMESTAMP column and the values of the column will never be "in the future", you can rewrite your queries like so (each of which can utilize an index on the datet column):

-- Today
SELECT COUNT(*) AS count
FROM   log
WHERE  datet >= CURDATE()

-- This Week
SELECT COUNT(*) AS count
FROM log
WHERE datet >= CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY

-- Last Week
SELECT COUNT(*) AS count
FROM log
WHERE datet >= (CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY) - INTERVAL 1 WEEK
      AND
      datet < CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY

-- This Month
SELECT COUNT(*) AS count
FROM   log
WHERE  datet >= CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY

 -- Last Month
SELECT COUNT(*) AS count
FROM log
WHERE datet >= (CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY) - INTERVAL 1 MONTH
      AND
      datet < CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY

-- This Year
SELECT COUNT(*) AS count
FROM   log
WHERE  datet >= MAKEDATE(YEAR(CURDATE()), 1)


Determining that Indexes on a table are unused I’ve been running this script to try to find extraneous indexes

select o.name as TableName, i.name as IndexName, p.reserved_page_count * 8.0 / 1024 as     SpaceInMB, s.*
from     sys.dm_db_index_usage_stats s
inner join sys.objects o on s.object_id = o.object_id
inner join sys.indexes i on i.index_id = s.index_id and i.object_id = o.object_id
inner join sys.dm_db_partition_stats p on i.index_id = p.index_id and o.object_id =      p.object_id
where o.name = ‘TableName’

I know that when last_user_seek/scan/lookup are all null, that no users have used the index since last restart.  But I’m wondering what system_scans/lookups/seeks …are?  Because on a certain table I found 5 that had no user activity, but one had system activity 10 days ago.  Do anyone have any insight on what system scans/seeks/lookups might be?  These tables seem really over-indexed and I’d like to trim the fat.

Index maintenance (rebuild/reorganize) and DBCC CHECKDB activity most likely, possibly statistics updates. Any scheduled maintenance configured?

If there is no user access, bin them. Just be mindful of the time frame over which you decide they are no longer used. Are there any weekly or monthly reporting tasks for instance?

While you're looking, dig around for duplicate indexes as well.

Edit: regarding SSC link

From a quick scan through the thread, looks like the SSC folk had similar thoughts. They are however taking a more cautious stance on the possible "occasional" use of these indexes, taking the position that someone put them there for a reason, a perfectly reasonable argument. The counter argument is that all too often it's the exact opposite, someone put them there because they thought it was the right thing to do but through a lack of understanding or lack of testing, it wasn't.

I've brought a couple of systems back from the brink by doing nothing other than dropping unused and duplicated indexes. Over indexing can cause chaos.

It's your system, you need to understand and weigh the risks of leaving these indexes in place or dropping them. If you decide to go ahead with the drop, document what you do, why you're doing it, script the indexes and publish to all interested parties.


JOIN FOR SYSTEM TIME with column as the effective system time Imagine I have a schema that includes Products, Orders, and OrderLineItems, with Products being a system-versioned temporal table.

Schema:

CREATE TABLE dbo.Products
(
    ProductID INT NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(255) not null,
    SysStart DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    SysEnd DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.Products_History, DATA_CONSISTENCY_CHECK = ON));
GO

CREATE TABLE dbo.Orders
(
    OrderID int not null identity primary key,
    OrderDate datetime2 (7) not null
);

CREATE TABLE dbo.OrderLineItems
(
    OrderID int not null,
    ProductID int not null,
    CONSTRAINT FK_OrderLineItems_Orders FOREIGN KEY (OrderID) REFERENCES dbo.Orders (OrderID),
    CONSTRAINT FK_OrderLineItems_Products FOREIGN KEY (ProductID) REFERENCES dbo.Products (ProductID),
    CONSTRAINT PK_OrderLineItems PRIMARY KEY (OrderID, ProductID)
);
GO

-- Load Sample data
insert into Products (Name) values ('a'), ('b');
waitfor delay '00:00:02';
insert into orders (OrderDate) values (getutcdate());
waitfor delay '00:00:02';
update products set name= 'c' where name = 'a'
waitfor delay '00:00:02';
insert into orders (OrderDate) values (getutcdate());
insert into OrderLineItems (OrderID, ProductID) values (1, 1), (1, 2), (2, 1);

How would I write a query which joins Products to OrderLineItems using the date from Orders?

For example, to query "Orders which contains products whose names have changed":

-- Fake syntax:
SELECT o.OrderID, p_then.Name as [Old Name], p_now.Name as [New Name]
FROM dbo.Orders o
INNER JOIN dbo.OrderLineItems oi on o.OrderID = oi.OrderID
INNER JOIN dbo.Products as p_then
    for system time o.OrderDate
    on oi.ProductID = p_then.ProductID
INNER JOIN dbo.Products p_now
--  for system time now
    on oi.ProductID = p_now.ProductID
WHERE p_then.Name <> p_now.Name

You can retrieve all historical rows from a temporal table using FOR SYSTEM_DATE ALL.  That allows a manual comparison of the OrderDate to the SysStart and SysEnd dates:

SELECT o.OrderID, p_then.Name as [Old Name], p_now.Name as [New Name]
FROM dbo.Orders o
INNER JOIN dbo.OrderLineItems oi on o.OrderID = oi.OrderID
INNER JOIN dbo.Products FOR SYSTEM_TIME ALL as p_then
    on o.OrderDate >= p_then.SysStart and o.OrderDate < p_then.SysEnd
    and oi.ProductID = p_then.ProductID
INNER JOIN dbo.Products p_now
    on oi.ProductID = p_now.ProductID
WHERE p_then.Name <> p_now.Name


Two identical MySQL tables, one not using indexes I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data.

Schema of one table

CREATE TABLE `measuresHistory` (
  `keyMeasureHistory` int(10) unsigned NOT NULL COMMENT 'Key of the measure.',
  `keyMeasurePoint` int(10) unsigned NOT NULL COMMENT 'Measure point associated with the value.',
  `date` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'Date of the measure.',
  `value` double NOT NULL COMMENT 'Value of the measure.',
  PRIMARY KEY  (`keyMeasureHistory`),
  KEY `iDate` (`date`),
  KEY `iKeyMeasurePoint_Date` (`keyMeasurePoint`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$

Problem

One of the tables has incredibly long query time, doing the same query on other tables is extremely fast, example :

SELECT
    *
FROM acs_spectotechnologies_com_enerconcept_0007.measuresHistory
WHERE
    date >= "2013-05-01 00:00:00"
    AND date <= "2013-05-01 05:59:59"
    AND keyMeasurePoint IN(1,2,3,4)
ORDER BY
    date;

Result : 1440 rows, 0.001sec (23487659 total rows)

SELECT
    *
FROM acs_spectotechnologies_com_enerconcept_0005.measuresHistory
WHERE
    date >= "2013-05-01 00:00:00"
    AND date <= "2013-05-01 05:59:59"
    AND keyMeasurePoint IN(1,2,3,4)
ORDER BY
    date;

Result : 1440 rows, 3.703sec (11749327 total rows)

Doing the EXPLAIN command in MySQL show me that indexes are used for the table acs_spectotechnologies_com_enerconcept_0007.measuresHistory but not for the table acs_spectotechnologies_com_enerconcept_0005.measuresHistory.

I've tried to remove indexes, recreated them, no changes. The two tables are on the same MySQL server. Also tried to force using the index, without difference.

Anyone have an idea?

Finally I've been able to figure out the problem, yet I still don't know why it happenned!

I've found this answer.

Doing ALTER TABLE acs_spectotechnologies_com_enerconcept_0005.measuresHistory ENABLE KEYS; fixed the usage of keys for this table, now every requests are lightning fast!


PostgreSQL, integer arrays, index for equality I have a huge list of integer arrays (300,000,000 records) stored in Postgres 9.2 DB. I want to efficiently search these records for an exact match (equality only). I have heard of the intarray module and the corresponding gist-gin indexes. I would like to ask the following questions:

Does PostgreSQL uses a hash function for checking equality of integer arrays or does it perform a brute-force algorithm comparing one-by-one the elements of the array?
If PostgreSQL uses a hash function, is there some PostgreSQL function code to actually get the hash function result for a particular array?
Which index will be best for such a task? B-tree, or the gist - gin indexes provided by the intarray module? The dataset will be static, i.e, once all records are inserted there will no more insertions. So, building index / updating index time is not important to me.

Q: Does PostgreSQL uses a hash function for checking equality of integer
 arrays or does it perform a brute-force algorithm comparing one-by-one
 the elements of the array?

Not according to Array Functions and Operators in the doc:

  Array comparisons compare the array contents element-by-element, using
  the default B-tree comparison function for the element data type

No mention of hashing.

intarray provides other operators but does not replace the equality operator between int[]. The  closest function _int_same() that it exposes is semantically different (the order of elements does not matter) and is implemented as sorting+sequential comparison, not hashing.

Fortunately  implementing fast hash-based search at the SQL level is not hard, and in your case (large arrays, no updates, exact match) it may even be the most effective method.

Steps:

1) choose a hash function. I'd suggest md5 on the text representation of the array:

create function arr_hash(int[]) returns bytea as
$$ select digest($1::text, 'md5');$$
language sql immutable;

The function digest(text,text) is part of the pgcrypto extension. Compared to md5 it has the advantage of producing binary (16 bytes) instead of hexadecimal (32 bytes) for a leaner index.

2) create a functional index:

create index index_name on table_name(arr_hash(col_name));

It will be several orders of magnitude faster than a GIN index for the kind of dataset you have (actually I would worry about the GIN index creation taking a really unreasonable time, but do try it).

3) use it like this:

select 1 from table_name
 where arr_hash(col_name)=arr_hash('{10,20,30,...lot of values}'::int[])
 and   col_name='{10,20,30,...lot of values}'::int[];


Migration of chunks not taking place in sharding I am trying sharding in MongoDB. I am working with

1 config server
1 Query router
2 shards

I tried hash based sharding. The data is getting partitioned but sharding is not successful. In one shard some documents are present but in another shard the number of documents is zero. In logging I could see the warning:

  warning: moveChunkCmd deleted data already in chunk

I can get a point that the data is lost while the chunk is migrating from one shard to another shard. But I am not sure how to avoid the data lost while the chunk is migrating.

The data is not being lost - that message means that when the migration was attempted from the source shard to the target, there was already data present that matched the chunk range, which was then deleted (this is what is supposed to happen, which is why that is not an error, but a warning).  Since you are starting from scratch, it implies that you have data present in that collection, in that range before any migrations are attempted.

This condition can happen occasionally on sharded clusters that have been running for a while (and chunks move around), hence the warning rather than an error.  However in this case it suggests that this was not a clean database on at least one shard when you began.  There are either some steps missing from this description, or possibly you failed to clean up properly after a previous attempt.

In any case, I would suggest posting the full procedure that brought this about - I have tested hashed sharding with 2 shards, 1 config server and 1 mongos many, many times (occasionally live in front of others as part of a demo) and never caused this to happen with normal operations.


Cardinality estimate outside the histogram Setup

I'm having some trouble understanding a cardinality estimate.  Here's my test setup:

the 2010 version of the Stack Overflow database
SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2
the new CE (compatibility level 140)

I have this proc:

USE StackOverflow2010;
GO

CREATE OR ALTER PROCEDURE #sp_PostsByCommentCount
    @CommentCount int
AS
BEGIN
    SELECT *
    FROM dbo.Posts p
    WHERE
        p.CommentCount = @CommentCount
    OPTION (RECOMPILE);
END;
GO

There are no nonclustered indexes or statistics on the dbo.Posts table (there is a clustered index on Id).

When asking for an estimated plan for this, the "estimated rows" coming out of dbo.Posts is 1,934.99:

EXEC #sp_PostsByCommentCount @CommentCount = 51;

The following statistics object was automatically created when I asked for the estimated plan:

DBCC SHOW_STATISTICS('dbo.Posts', [_WA_Sys_00000006_0519C6AF]);

The highlights from that are:

The statistics have a pretty low sample rate of 1.81% (67,796 / 3,744,192)
Only 31 histogram steps were used
The "All density" value is 0.03030303 (33 distinct values were sampled)
The last RANGE_HI_KEY in the histogram is 50, with EQ_ROWS of 1

Question

Passing any value higher than 50 (up to and including 2,147,483,647) results in the 1,934.99 row estimate.  What calculation or value is used to produce this estimate?  The legacy cardinality estimator produces an estimate of 1 row, by the way.

What I've Tried

Here are some theories I had, things I tried, or additional bits of information I was able to dig up while looking into this.

Density Vector

I initially thought it would be the density vector, the same as if I had used OPTION (OPTIMIZE FOR UNKNOWN).  But the density vector for this stats object is 3,744,192 * 0.03030303 = 113,460, so that's not it.

Extended Events

I tried running an Extended Event session that collected the query_optimizer_estimate_cardinality event (which I learned about from Paul White's blog post Cardinality Estimation: Combining Density Statistics), and got these sort of interesting tidbits:

<CalculatorList>
  <FilterCalculator CalculatorName="CSelCalcColumnInInterval" Selectivity="-1.000"
                    CalculatorFailed="true" TableName="[p]" ColumnName="CommentCount" />

  <FilterCalculator CalculatorName="CSelCalcAscendingKeyFilter" Selectivity="0.001"
                    TableName="[p]" ColumnName="CommentCount" UseAverageFrequency="true"
                    StatId="4" />
</CalculatorList>

So it appears the CSelCalcAscendingKeyFilter calculator was used (the other one says it failed, whatever that means).  This column isn't a key, or unique, or necessarily ascending, but whatever.

Doing some Googling of that term led me to some blog posts:

Joe Sack - The CSelCalcAscendingKeyFilter Calculator,
Itzik Ben-Gan - Seek and You Shall Scan Part II: Ascending Keys

These posts indicate the new CE bases these outside-the-histogram estimates on a combination of the density vector and the stat's modification counter.  Unfortunately, I've already ruled out the density vector (I think?!), and the modification counter is zero (per sys.dm_db_stats_properties anyway).

Trace Flags

Forrest suggested I turn on TF 2363 to get some more information about the estimation process.  I think the most relevant thing from that output is this:

Plan for computation:

  CSelCalcAscendingKeyFilter(avg. freq., QCOL: [p].CommentCount)

Selectivity: 0.000516798

This is a breakthrough (thanks, Forrest!): that 0.000516798 number (which seems to have been unhelpfully rounded in the XE Selectivity="0.001" attribute above) multiplied by the number of the rows in the table is the estimate I've been looking for (1,934.99).

I'm probably missing something obvious, but I haven't been able to reverse engineer how that selectivity value is produced inside the CSelCalcAscendingKeyFilter calculator.

Based on my testing, the out-of-bounds cardinality estimate is simply the square root of the row count, bounded below by the number of added rows since the last stats update, and bounded above by the average rows per value.

In your case, 1,934.99 = SQRT(3744192)

Testing setup below:

--setup
USE TestDB
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS OFF
GO

DROP TABLE IF EXISTS dbo.Hist

CREATE TABLE dbo.Hist (
ID int identity primary key,
Num int
)

INSERT dbo.Hist
SELECT TOP 300
(ROW_NUMBER() OVER(ORDER BY(SELECT 1/0)))%3
FROM master..spt_values a
CROSS JOIN master..spt_values b

--Get estimated plan
--don't forget to run right after setup to auto-create stats
SELECT *
FROM dbo.Hist
WHERE Num = 1000

--gradually add rows, then rerun estimate above
INSERT dbo.Hist
SELECT TOP 100
-1
FROM master..spt_values a

--I sure hope you weren't testing this in prod (cleanup)
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS ON
GO

Surprisingly even row estimates were generated from this approach:
20 at 400 total rows,
30 at 900,
40 at 1600, etc.

Past 10000 though, the row estimate maxes out at 100, which is the number of rows per value in the existing stats. Adding only 10 rows will set the estimate to 10, since sqrt(300) > 10.

Thus the estimates could be expressed using this formula:

Estimate = MIN(SQRT(AC), MIN(AR, MC))

Note that if statistics are sampled, then MC is not considered. So the formula becomes:

Estimate = MIN(SQRT(AC), AR))

Where

MC is the "modification count" (# of modifications since the statistics were created)
AC is the "adjusted cardinality" (# of rows from the statistics plus MC),
AR is the average rows per value (# of rows from the statistics divided by distinct values in the column)

The formulas for these estimates, and other details about the calculator, can be found in this blog post: Analyzing Estimates from the CSelCalcAscendingKeyFilter Calculator


Prevent Modification of dbo objects while allowing other schemas to be modified I have a vendor solution that uses a sql server (2008r2) database. We are allowed to do anything we want in our own schema, but cannot modify dbo objects without vendor permission. Any customization we make is in our own schema (cust).  We have full control of this server and have always allowed sysadmin rights to any developer. Lately, I've noticed these rules have not been followed, so I want to see if I can set up permissions to stop this in the first place.

Here is what I would like to accomplish:

Tables: read/write to all, alter cust schema only, view design

Views/Sprocs/Functions: view any definition, create or alter cust schema only

I created a new user (dev) and applied the following:

deny alter on schema::dbo to dev
grant alter on schema::custom to dev
grant view definition to dev

Will this accomplish what I want, or am I missing something else?

Solution (thanks to AMtwo)

use master
go
create login dev with password = 'test', check_expiration = off, check_policy = off
grant control server to dev

use CustomerDB
go
if not exists(select * from sys.database_principals where name = 'dev') begin
    create user dev for login dev
    grant select, insert, update, delete, execute, alter on schema::custom to dev
    grant view definition to dev
    deny alter on schema::dbo to dev
    -- deny other schemas here
end`

We have full control of this server and have always allowed sysadmin
  rights to any developer.

The sysadmin privilege effectively short-circuits any other permissions. If a user is a member of the sysadmin fixed server role, then any DENY permissions will be ignored.

You can work-around this by revoking sysadmin and instead using the CONTROL SERVER permission. CONTROL SERVER is similar to the sysadmin fixed server role, except that CONTROL SERVER will obey DENY permissions.

GRANT CONTROL SERVER TO dev
EXEC sp_dropsrvrolemember 'dev', 'sysadmin'
DENY ALTER ON SCHEMA::dbo TO dev

If you want to completely undo the developers' need to have high-level access on the server (which is a pretty good idea), then you could take two approaches:

Grant wide access to the database, then explicitly deny permissions on dbo.

USE MyDB
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,ALTER ON DATABASE::MyDB TO dev
GRANT VIEW DEFINITION TO dev
DENY ALTER ON SCHEMA::dbo TO dev
EXEC sp_dropsrvrolemember 'dev', 'sysadmin'

Explicitly grant just the permissions the users require; do not grant ALTER permission in dbo.

USE MyDB
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,ALTER ON SCHEMA::custom TO dev
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON SCHEMA::dbo TO dev
GRANT VIEW DEFINITION TO dev
EXEC sp_dropsrvrolemember 'dev', 'sysadmin'

Ultimately, you want to follow (or be close to) the principal of least privilege. But you also have to balance complexity and manageability.

I'd suggest you create a few test scenarios to test your permissions when running as dev, so that you can make sure the user has the right permissions. You can do this using EXECUTE AS LOGIN:

EXECUTE AS LOGIN='SomeDeveloper';
CREATE TABLE custom.AM2 (foo BIT);
GO
CREATE PROCEDURE custom.GetAM2 AS SELECT * FROM custom.AM2;
GO
DROP TABLE custom.AM2;
DROP PROCEDURE custom.GetAM2;
GO
REVERT;


Cluster network name resource 'Cluster Name' failed registration I have a fail-over cluster set between two Windows Server 2016 machines, and I'm seeing errors regarding the DNS record, both for the cluster itself and for any listener I try to add in SQL high availability.

  Cluster network name resource 'Cluster Name' failed registration of one or more associated DNS name(s) for the following reason:
  DNS server failure.

  Ensure that the network adapters associated with dependent IP address resources are configured with at least one accessible DNS server.

Properties of the cluster resource - img

I've looked through this link and I do see the 8.8.8.8 DNS on my machines, after the records for the domain DNS - these DNS settings are automatically pushed from our DC and I'm not sure I can change them.
Has anyone experienced this? Is there another solution?

I have come across this issue with my dev environment usually when during the setup of the cluster, i skip the warning for network binding. When you run a cluster validation, do you receive any warnings or errors on the network.
Anyways this link fix my issue. @Amr provided the solution to issue.
https://social.technet.microsoft.com/Forums/ie/en-US/c77c0b69-1f9d-4467-a0dd-6844e87e2d13/cluster-name-failed-to-update-the-dns-record?forum=exchange2010

Cause:

The cluster name resource which has been added to the DNS prior to setup active passive cluster ( or any type) need to be updated by the Physical nodes on behalf of the resource record itself. When the active node owns the resources it want to update the A record in the DNS database and DNS record which was created won’t allow any authenticated user to update the DNS record with the same owner

Solution:

Delete the existing A record for the cluster name and re-create it and make sure select the box says “Allow any authenticated user to update DNS record with the same owner name “Don’t worry about breaking anything , this has “ZERO” impact to cluster simply delete the A record and re-create as it is suggested here.

http://amradmin.wordpress.com/2011/01/27/event-id-1196-1119-dns-operation-refused-cluster-servers/


Get first part of result faster in sorting query on hundreds of millions of rows? I have a single table containing around 280 million rows in MariaDB 10.4.10.

I need to process this full table in an external program, sorted by timestamp. The external program is fast; the largest factor is the query speed. This query takes around 37 minutes to run (old laptop, SSD, virtual machine with 8GByte RAM). This is normal, I think.

However, the first result is only returned after 17 minutes, in which the client program is just waiting.

Is there a way to make MySQL return the first results faster, even if the total query time is unchanged, or even slightly longer?

Table structure:

CREATE TABLE `eventlog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `moment` datetime NOT NULL,
  `source_id` int(11) NOT NULL,
  `code` binary(5) NOT NULL,
  `category` binary(2) DEFAULT NULL,
  `type` enum('single','double','triple') CHARACTER SET binary NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_moment` (`moment`)
) ENGINE=InnoDB AUTO_INCREMENT=283252852 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

It is around 12GByte on disk (data length, excluding indexes). The index is 6.5GByte.

Query:

SELECT
  id,
  moment,
  source_id,
  code
FROM
  eventlog
ORDER BY
  moment ASC

EXPLAIN shows it is not using the index on moment to run the query.

MariaDB [test]> explain select id, moment, source_id, code from eventlog order by moment asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: eventlog
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 282499875
        Extra: Using filesort
1 row in set (0.017 sec)

Profiling the query shows it spends practically all time on "Creating sort index", even though an index on the ORDER BY field already exists. (All other stages in the profile are each less than 0.02s):

*************************** 14. row ***************************
             Status: Creating sort index
           Duration: 999.999999
           CPU_user: 999.999999
         CPU_system: 110.469717
  Context_voluntary: 307668
Context_involuntary: 18439
       Block_ops_in: 74089616
      Block_ops_out: 50343784
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 111
  Page_faults_minor: 6653
              Swaps: 0
    Source_function: <unknown>
        Source_file: sql_select.cc
        Source_line: 21167

I have tried:

Forcing the index: I thought this would make sense, since the query is basically returning the rows in index order. EXPLAIN then shows the index will be used, but the first result is even later (175 minutes?!).
Increasing sort_buffer_size from 2M to 20M, without effect.
Running with LIMIT and OFFSET: the first batch of 500 000 rows is returned in 9 seconds, which is nice, but the total query time (getting all 280 million rows in batches of 500 000) increases drastically as the OFFSET increases, making the total time the external program is idle even longer.

How can I make make MySQL return the first results faster without making the total runtime several times longer?

First, I have to ask what the client will do with 280M rows?  If it is a web page, that much data will crash the user's machine.

Here are two things to speed up getting data from that query:

Change the indexes to these:

PRIMARY KEY(moment, id),  -- to cluster the data in the order to be fetched
INDEX(id)   -- to keep AUTO_INCREMENT happy.

What you have now will fetch all the data, then sort it by moment, before delivering even the first row.  With the indexing change, the sort is gone, and the rest can be 'streamed' directly.  But will the client take advantage of that?  Well...

Change the fetching mechanism to receive the data in chunks instead of "all at once".  (The details of this choice are buried in the API of how the client talks to MySQL.  I only use the "all at once".  But I never ask for 280M rows.)

Drawbacks of "chunking":

The entire query takes longer.  (That is, more than 37 minutes.)
Other connections may be impacted by such a long-running query.

Consider this alternative:  SELECT ... INTO OUTFILE ... and then process the resulting file as you might process a CSV file.


Use same Postgres WITH in multiple queries I would like to use same WITH in multiple queries.

WITH x AS (INSERT ... RETURNING id)
INSERT INTO t2 VALUES (x);
INSERT INTO t3 VALUES (x);

How would I do that? Do I have to create a temporary table instead?

You can chain multiple CTEs that perform an INSERT:

with x (id) as (
  insert into ...
  returning id
), t2_insert as (
  insert into t2 (id)
  select id
  from x
)
insert into t3
select id
from x;


Postgres 9.5 Materialized View from a table with jsonb columns For the purpose of improving performance of queries on a rather large table (5M+ rows) containing jsonb columns, I would like to create materialized views with data derived from those columns. Following is a simplified version of the problem:

               Definition

CREATE TABLE my_table (id int8, content jsonb);

               Contents

id |  content
-----------------------------------------------------------------------------
 1 | { "customer": "John Doe", "items": {"product": "Whiskey","qty": 3}}
 2 | { "customer": "John Doe", "items": {"product": "Whiskey","qty": 4}}
 3 | { "customer": "Danny Boy", "items": {"product": "Beer","qty": 2}}

Now I'd like to create two materialized views, one will contain all customer names and the other will contain the count of each product.

So the end result should be:

               Materialized View 1 / the names of the customers
customer_name
-------------
John Doe
Danny Boy

               Materialized View 2 / the quantity of all products

product | quantity
------------------
Beer    | 2
Whiskey | 7

For the first materialized view I tried the following:

CREATE MATERIALIZED VIEW
  customers AS
SELECT DISTINCT
  content->'customer' AS customer_name FROM my_table;

This works fine, but I am having trouble with the second materialized view.

How should I go about aggregating quantities for each product in an efficient way?

You just need to GROUP BY the product to build the sum() of the quantities of the products.

CREATE MATERIALIZED VIEW products
AS
SELECT "content"->'items'->>'product' "product",
       sum(("content"->'items'->>'qty')::integer) "quantity"
       FROM "my_table"
       GROUP BY "content"->'items'->>'product';


Recover lost data from .LDF file Background:
Using Business Contacts Manager 2010 with SQL 2008.
Master database is hosted through SQL and each computer connects. When the connection is stopped, each computer switches to local copy of the database. When connection is available again, the changes are synced into the master database.

Issue:
When syncing the data from local to master, an error occured and all records after the error didn't trasfer the changes and erased the changes from the local database.
Is there any way to retrieve the data that was changed only on the local machine before the sync operation occured?

A transaction log LDF file of the local database copy is the only thing that may have that data in it but didn't have any luck retrieving the data so far. No local backups were made prior to the incident. The only way I believe this can be done is through the LDF file unless there is a way to roll back the local copy to a previous date even without a backup log file being created.

Notes: The last backup of the local machine database copy was from October 2011. The master database from the server was backed up much more recently but the information that I'm trying to retrieve is in the "SQL Serve Database Transactions Log File" aka the .LDF file.

So if I restore the local database to the backup from October 2011 could I redo all changes since and up until when the data was lost using the .LDF file? I don't think any recovery mode was ever set or changed from the default settings. Also, the database is not very large, only 500MB, and the data that was lost was only from a notes field and a call-date field for around 100 contacts so maybe its not worth all that work.

You need to restore the last full backup, then all transaction logs in sequence, issuing a STOP AT for the final transaction log restore at a time prior to the loss of data. See this reference.


Can not find 'NT AUTHORITY\NETWORK SERVICE'. on Sql Server 2012 Hi How do i fix this Error?
when i run my MVC app i get this error
though my web.config is find. the error is on my DB call Sample:

Cannot open database "Sample" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Also cannot find ‘NT AUTHORITY\NETWORK SERVICE’. on my sql server management studio 2012 object explorer under Security > Logins to right click and set the Properties.

How can i add it, is this a win vista issue ?

You can't find the NT AUTHORITY\NETWORK SERVICE because it hasn't been added. You need to explicitly add it.

Goto Security-> Logins (in SSMS) and add that user.  Then go to your database and add as a login with relevant permissions.

Better still, script it:

CREATE LOGIN

CREATE USER


What is the relevance of 'SET default_with_oids = true' in a Postgresql dump? I have an SQL backup from an old database which may have been a PostgreSQL  7 or 8 database I am importing which contains a SET default_with_oids - true statement, into a an existing 8.4 database and wonder what the relevance is and how it might affect the existing database.

I don't think the OIDs were actually used in the original database. Can I simply ignore it?

The documentation states the following:

  default_with_oids (boolean)

  This controls whether CREATE TABLE and CREATE TABLE AS include an OID column in newly-created tables, if neither WITH OIDS nor WITHOUT
  OIDS is specified. It also determines whether OIDs will be included in
  tables created by SELECT INTO. The parameter is off by default; in
  PostgreSQL 8.0 and earlier, it was on by default.

  The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that
  require OIDs for a particular table should specify WITH OIDS when
  creating the table. This variable can be enabled for compatibility
  with old applications that do not follow this behavior.

Note that the feature was deprecated with the coming of 8.0.

This means that if you plan to use the DB as it is, you can safely remove this line before restoring (you have to be really sure that they are not referred to).  It will be left set to the default false.

If you have a legacy application working on this DB, you will want to check it, too, if it uses the OIDs in any way.  (At the moment I cannot really think of such a use case, but it may be the weakness of my imagination.)

A side note: you may experience some other issues restoring the dump - I remember some problems with changed implicit cast behaviour.


Cannot start SQL Server replication as already running I'm trying to configure a sql replication on the sql server 2012 .

I am logged in as "sa", when I attempt to start replication i get the following error :

How can I stop this job and restart the replication ?

You can do this via several methods. (from memory as don't have a replication environment in front of me!)

1- My Preferred intial method -  Open the Replication Monitor. You can do this by right clicking the 'Replication' folder in Management Studio and selecting Replication Monitor.

You will then have to register your publisher if this is the first time and add your Server into the list with the correct credentials (This can be local to the Publisher or can be setup on a remote Distribution server).

When you have selected your publisher you should now be able to select go to the agents tab on the right hand side. You can then right click and select 'view details' This should hopefully give you further insight into why the current replication is not working and its status. You can also stop and start agents from the monitor.

2-  The other method is to go to the SQL agents job on the server that is running your distribution DB and open the SQL Agent. you will see several jobs relating to replication.

Expand the jobs that are marked failing and look at the job steps (you will often have to go to the step before the retry to discover the real reason why the agent is failing). Obviously you can stop and start any agents here, but nearly always you will experience the same result. you should try to remedy the underlying issue first.  This will all depend upon what step is failing but permissions is a fair bet with Replication.


Dealing with empty strings while loading a table from a CSV I am trying to load a CSV file into a table. The CSV file contains some empty strings for example:

1,2,,4

I want to load the empty field as null. One way I know is to use \N i.e:

1,2,\N,4

but it won't suit my condition because my files are large and I don't want to change the original data.

Is there a way that I can specify in the Load command itself that wherever it finds an empty string, it should convert it to a NULL?

According to the documentation, you can use SET statements to transform the data on the way in.

 [SET col_name = expr,...]

The expr expression can include the column name, which will be interpreted as the data being read from the file and destined for that column... so, for example, at the end of your LOAD DATA INFILE statement you might use:

SET latitude = IF(latitude + 0 = 0,NULL,latitude),
    area_code = IF(area_code = '',NULL,area_code)

This example transforms 2 columns.  If latitude + 0 is 0, latitude gets set to NULL, and otherwise it gets set to the value from the file as the data is inserted; if area_code contains an empty string, it gets set to NULL, otherwise to the data from the file.  The more appropriate choice will depend on how MySQL handles casting the data, but I suspect either of these constructs would work in your situation.

You do not have to reference columns you don't intend to transform.  They'll be inserted as-is.


Syncing two databases in SQL Server I have two SQL Server databases. One is client (Windows application) and the second is on the server. I want to sync these two databases every so often (e.g. every 2 minutes!).

I have read about different ways of syncing like replication, time stamp, log tables using triggers, Microsoft Sync Framework and so on.

Actually I don't like to use a syncing method which might be a black box (like replication) because I don't want the SQL Server specific tables to be blocked while I'm updating them and syncing them with the server.

which method do you think that I should use in such circumstance? Remember that every several minutes I must send several table changes from client to the server and fetch also two table changes from server.
I have found a method which is strange but new. Is that possible that I log all executed (for specific preferred) stored procedures in client and send them with their parameters in a .sql file to the server and execute them there? The same will happen on the server and sent to the client. Do you think that this is a simple yet useful method or not?
please suggest me any useful approach if you can. Thank you so much.

EDIT: Remember that this is a real-time synchronization and this makes it special. It means when the client user is using the table, the synchronization process with server must happen every several minutes so none of the tables must be locked.

Well I might not get it, but I try to answer it.

You said you need a high performance solution which runs often (minimum all 2 minutes) and you need a good approach which should be fast without locking. But you don't want a blackbox system.

Instead of a blackbox system, which is used on millions of installations with good results, you try to invent the wheel again and build your own solution? Hm, sounds a bit weird.

In fact these are my suggestions.

Replication even if you said you won't use it. It's quite the easiest and best solution you can use for this. The replication is easy to setup, replicate fast and you don't have to invent the wheel again. If you just weird about locking, you may try to set the ISOLATION LEVEL to READ_COMMITTED_SNAPSHOT. You can read more about it here. This will use up a part of your tempdb, but your table is always read- and writeable and the replication can work in the background.

See the example below:

ALTER DATABASE yourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE yourDatabase SET READ_COMMITTED_SNAPSHOT ON

CDC (Change Data Capture) can also be a solution. But this way you need to build nearly everything on your own. And I've made the experience that CDC can be a fragile thing in some circumstances. CDC will capture all data on a watched table (you need to specify each watched table manually). Afterwards you'll get the value before and the value after an INSERT, UPDATE or DELETE. CDC will hold back those information for a period of time (you can specify it on your own). The approach could be to use CDC on certain tables you need to watch and manually replicate those changes to the other database. By the way, CDC uses the SQL Server Replication under the hood too. ;-) You can read more about it here.

  Warning: CDC will not be aware of DDL-changes. This means, if you change a table and add a new column, CDC will watch the table but ignore all changes to the new column. In fact it only records NULL as value before and value after. You need to reinitialize it after DDL-Changes to a watched table.

The way you described above is something like capturing a workload using SQL Server Profiler and run it again on another database for some benchmarks. Well it could work. But the fact that there are too many side effects is a bit too heavy for me. What do you do if you capture a procedure call on your client. Afterwards running the same command at your principle database as it is out of sync? The procedure may run through, but it may delete/update/insert rows which were not present in your client. Or how do you handle multiple clients with one principle. I think this is too tricky. In the worst case, you probably destroy your integrity.
Another idea could be application based or using a trigger. Depending on how many tables you want to be synced. You can write all changes to a separate staging table and run an SQL Server Agent Job all x Minutes to sync those rows in the staging table with your master. But this may be a bit to heavy if you try to sync (e.g.) 150 tables. You would have a big overhead.

Well these are my 2 cents. Hopefully you have a good overview and maybe you found one solution which works for you.


double (3,3) > sending 1.3 > stored 0.999 It is knocking my socks off...
MySQL DB

weight is defined as double (3,3)

INSERT INTO xxx (`weight`) VALUES ( 1.3)

why does the DB store 0.999?

(3,3) indicates a total length of 3 digits, with 3 decimal places.  Perhaps you want (6,3).

See the MySQL Docs for details.


Mysql - tablename.ibd file is huge in size I'm using MySQL 5.6 and innodb file per table has been enabled.

I noticed that one file named as tablename.ibd is 211GB, then query the information schema there it's showing 21GB.

I have done a huge delete operation on this table. And daily archiving some old data.

Is this because of that?
Optimize table - command is the only way to reclaim the space?

If you have enabled innodb_file_per_table, the only way in Operative System can recover and demand to MySQL the used space is through OPTIMIZE TABLE command, you should run that command for you could see equally the data in both ways through .ibd file and using query.


Amazon Aurora MySQL conditional comment queries How does Amazon Aurora handle conditional queries based on MySQL version? I was unable to find any documentation on this.

For example, the code below inserts into a database for MySQL 5.6.4 or newer. What is result in Aurora?

I could spin up an instance but wanted to read the documentation about this to understand what incompatible features there are.

/*!50604 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('supports_full_text', '1')*/;

Aurora runs as mysql 5.6.10a so the comment queries still work as expected in regular mysql


How to Reclaim Data Free in InnoDB Tables? I came up with an idea on how to shrink ibdata1 in mysql database then i got this solution on stackoverflow

STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql schema)

STEP 03) Shutdown mysql

STEP 04) Add the following lines to /etc/my.cnf
    innodb_file_per_table       =1

STEP 05) Delete ibdata1, ib_logfile0 and ib_logfile1

STEP 06) Restart mysql

STEP 07) Reload SQLData.sql into mysql

after that steps, i executed SELECT TABLE_NAME, TABLE_SCHEMA, Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql') AND Data_Free >0;

at sqlyog, this is the output:

after that i run:

mysqlcheck -u root -p$pw --debug-info --all-databases --auto-repair

mysqlcheck -u root -p$pw --debug-info --all-databases --optimize

then i query show table status on sqlyog, what i am expecting is under data_free column will be a value of zero, since on my current setup its should release unused space space.

and my question is, is there something wrong in what i did? how am i gonna do it right in reclaiming unused space in innodb tables?

mysql 5.5 on rhel 6.3

Regards,

John

Everything you have done is correct in order to drastically defragment innodb tablespaces (there are other ways, but exporting, deleting files and importing certainly works).

Trying to optimize the tables (which, by the way, the correct way to do it for innodb is running ALTER TABLE mytable ENGINE=InnoDB- OPTIMIZE table just calls this, and REPAIR table does nothing for InnoDB) just after an in-primary-key-order import is useless. The way InnoDB works is optimising for access and write performance, not for data size. You will always have overhead in disk space (even if free space says 0), as innodb reserves spaces in whole extend beyond a certain size. Also, some data types, like blobs and random inserts and deletions can lead to extra fragmentation, but I presume that the size you are showing is less than 10% of the table size, so you should no worry. After a proper import, it is the least fragmented way innodb can work with the default options. Under normal operation, the file size should be constant when inserting new data until no more free space is available.

Of course, you can try to change parameters like extent or page sizes, but if you are concerned about saving disk space over performance I would recommend you to try compression (InnoDB Barracuda file formant has it) or use a different engine. However, please note that fragmentation and having huge files with lots of free space is usually not a concern with innodb_file_per_table = 1 under normal loads.


Enforce NOT NULL for set of columns with a CHECK constraint only for new rows I have a table and need to add a new column without a default value:

Constraint:

ALTER TABLE integrations.billables
DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables,
ADD CONSTRAINT cc_at_least_one_mapping_needed_billables
CHECK ((("qb_id" IS NOT NULL) :: INTEGER +
    ("xero_id" IS NOT NULL) :: INTEGER +
    ("freshbooks_id" IS NOT NULL) :: INTEGER +
    ("unleashed_id" IS NOT NULL) :: INTEGER +
    ("csv_data" IS NOT NULL) :: INTEGER +
    ("myob_id" IS NOT NULL) :: INTEGER) > 0);

Column:

ALTER TABLE integrations.billables
DROP COLUMN IF EXISTS myob_id,
ADD COLUMN myob_id varchar(255);

Question:

How can I add the constraint for the next values and not for those which are already in there? (Otherwise I'd get the error check constraint "" is violated by some row).

This is related to my previous question: ERROR: check constraint is violated by some row

If you have a serial column or an integer one that's automatically populated with a nextval (so that you are never supposed to insert new rows with an explicit value for that column), you could additionally check whether the value of that column is greater than a specific value:

(
  (("qb_id" IS NOT NULL) :: INTEGER +
  ("xero_id" IS NOT NULL) :: INTEGER +
  ("freshbooks_id" IS NOT NULL) :: INTEGER +
  ("unleashed_id" IS NOT NULL) :: INTEGER +
  ("csv_data" IS NOT NULL) :: INTEGER +
  ("myob_id" IS NOT NULL) :: INTEGER) > 0
  OR
  YourSerialColumn <= value
)

where the value should be determined as currval of the column/corresponding sequence at the time of altering/recreating the constraint.

This way the IS NOT NULL checks will apply only to the rows whose YourSerialColumn value is greater than value.

Note

This can be viewed as a variation on David Spillet's suggestion. The principal difference lies in the fact that this solution does not require structural changes (partitioning). Both options, however, rely on existence of a suitable column in your table. If there is no such a column and you can add it specifically to solve this problem, going with the partitioning idea might be the better option of these two.


MySQL Cluster Steps To Recover Data If Catstrophic Failure Occurs I want to know if a catstrophic failure occurs (like a power outage), and all nodes in MySQL Cluster are shutdown in cluster then data will be lost even though a backup was taken? Or it possible that data can still be retrieved?

If yes, then please tell me how it can be retrieved.

MySQL Cluster checkpoints and logs in-memory data to disk (assuming you leave the default of checkpointing on). As these are streaming writes, fast HDDs are very efficient to handle this activity. Also, all updates are synchronously replicated between data nodes in a node group, and these can be split across data centers using multi-site clustering:
https://blogs.oracle.com/MySQL/entry/synchronously_replicating_databases_across_data

So there are multiple mechanisms available to protect data

HTH!
Mat


Two dichotomy views of a table, and means to express this as a stored procudure? There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events.

How to express this? Should I create a view, a procedure, or a function which tells for every view ID whether it is archive (in order to use it in two above mentioned views)?

MySQL.

There is a very good reason to create two different views rather than to create a single view and query against the archive flag: indexes.

If you have two views, and the definition of each includes the criteria for determining whether archive is true or false, and the queries are properly written to take advantage of indexes, then they will perform better than if you evaluate whether each row is archived or not, because in the latter case, the server will be forced to evaluate the archive condition for each row and then filter those rows out of the final result based on the WHERE clause outside the view... because the optimizer cannot "see through" the view when you're deriving values.

For a relatively understated example, I have a history table with 151,534 records and an index on event_date.  I want to find records from last June.

mysql> CREATE VIEW get_history_window AS SELECT id, amount, event_date,
       IF(event_date BETWEEN '2012-06-01 00:00:00' AND '2012-06-30 23:59:59',1,0) AS is_in_june
       FROM history;
Query OK, 0 rows affected (0.04 sec)

mysql> EXPLAIN SELECT * FROM get_history_window WHERE is_in_june = 1;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | history | ALL  | NULL          | NULL | NULL    | NULL | 151534 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Oops.  The server has to do a full table scan, and is going to read each of the 150,000+ rows of the table, in order to evaluate the IF() expression so it can know which rows properly satisfy my WHERE.

The optimizer cannot reverse-engineer my query to an extent that it will realize "oh, you just want the rows with event date within a specific range, so I'll just use the index for this."  It has to do the comparison on each row.

However... if my view is more properly defined for the intended purpose:

mysql> CREATE VIEW get_last_june_events AS SELECT id, amount, event_date
       FROM history WHERE event_date BETWEEN '2012-06-01 00:00:00' AND '2012-06-30 23:59:59';
Query OK, 0 rows affected (0.03 sec)

mysql> EXPLAIN SELECT * FROM get_last_june_events;
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | history | range | event_date    | event_date | 4       | NULL | 1830 | Using where |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Now, thanks to the fact that the index can be used, only 1830 rows are examined, cutting my actual query time dramatically.

Of course, the tables need good indexes and the queries inside the views need to be optimized to take advantage of them... you can easily get this wrong.

If, for example, my view were defined with WHERE YEAR(event_date) = 2012 AND MONTH(event_date) = 6 instead of the way I illustrated above, WHERE event_date BETWEEN '2012-06-01 00:00:00' AND '2012-06-30 23:59:59' then I am right back where I started:

+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | history | ALL  | NULL          | NULL | NULL    | NULL | 151534 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

The reason for this, of course, is that when you use functions in a WHERE clause, this almost always prevents the optimizer from being able to use indexes on columns used as arguments to the functions.  In the first wrong example, event_date was used as an argument to IF() and here it's used as an argument to YEAR() and MONTH(), so these queries are not optimal, but the one in the middle is.

Assume you wanted to find records more than 6 months old:  By now, hopefully you will see that one of these is right and one of these is wrong:

WHERE DATE_ADD(event_date,INTERVAL 6 MONTH) < NOW();  -- wrong

WHERE event_date < DATE_SUB(NOW(), INTERVAL 6 MONTH); -- right

The expressions are logically equivalent and will produce the same result, but the first one is "wrong" in the sense that uses an indexed column as an argument to a function, preventing the index from being usable, while the second one uses a function to calculate a constant against which the indexed values of event_date can be compared.


How to restore a pg_dumpall dump without CREATE INDEX? I am attempting to migrate from Postgres 9.6 to 10.3 and during the restore each index is recreated one by one - this is a problem.

So far I thought pg_dumpall is a good option.

pg_dumpall -U postgres -h localhost -p 5432 --clean --file=dumpall_clean.sql

Once this is done the file is around 1.2TB in size and I can load it to the new 10.3 instance with

psql -U postgres -h localhost -p 5433 < dumpall_clean.sql

simple.

Problem

As I learned the indicies are not backed up like tables are, they are simply recreated, and that is my problem.

The cluster has thousands of partitions each with several million rows and two indices (one BTREE and one GIST). This takes days since each index is created one by one.

As I have enough resources and I know which indices have to be created, I would perfer to do this step after the dump has been restored. Initially I made 8 FOR loops (to run in parallel) to go through the partitions, and created an index by moving a partition to a faster tablespace (SSD), create the index, then move the table and the index back to the default tablespace. So far this has worked for me.

Question
How can I have the same result* of a pg_dumpall dump without recreating the indices when loading the dumpall_clean.sql file?
A pg_dumpall --without-index would be nice.

  "This currently includes information about database users and groups, tablespaces, and properties such as access permissions that apply to databases as a whole." - pg_dumpall manual

I can see one workaround for this, by using pg_dumpall in two steps:

pg_dumpall --schema-only ....

Then edit the file and extract the index definitions into a second file. You also need to extract the foreign keys, because you have to run them manually after the import (probably together with the index creation script)

Then run that script (without the indexes) to create the (empty) tables. You

pg_dumpall --data-only ....

Then run that script to import the data into the new database. After that run the FK and index creation scripts.


PostgreSQL - WAL segment file creation and naming conventions I was under the impression that WAL segments files are normally generated before they are being written to and archived, and are continuously renamed and reused to save disk space. I was wondering if I safely assume that when restoring these unarchived WAL segments, they are renamed to match that of the database's current timeline?

All of the files from 00000004...13 to 00000004...19 previously had the timeline ID of 1 (00000001...13 to 00000001...19 respectively), and these were placed in the pg_wal directory before the restore process was executed.

  Whenever an archive recovery completes, a new timeline is created to identify the series of WAL records generated after that recovery.

Since these WAL segments were generated in a previous timeline but were never written to, does this allow Postgres to rename those same WAL files to match that of the current timeline without any issues?

According to the source code (assuming I'm looking at the correct location, Postgres will take an old WAL file and rename it based on the current timeline ID.  According to this comment, timeline ID of old files are ignored -- basically, the assumption is that if you have files with LSN < current LSN, it doesn't matter if it's from another timeline: it's just "old" and can be recycled


Modeling different kinds of posts Say we have two kinds of posts, the first we'll call A and the second we'll call B. Both of them share columns, such as the submission date, etc. but also have distinct ones. Posts can also be replies - they have a parent_id, which can either be null (doesn't reply to anything) or an id which refers to another post.
What I'm having trouble with is understanding the optimal solution to model such a system.
The obvious method would be to just put all of them inside a single table, which would work fine but you'd also get a ton of null values (e.g. an entry of type A will have NULL in every field of type B).
Another method, which I'm currently thinking about, would be to use inheritance, so something like this:
CREATE TABLE post (
              post_id                     SERIAL PRIMARY KEY,
              submission_date             TIMESTAMP NOT NULL,
              ...
              FOREIGN KEY (parent_id)     REFERENCES post(post_id)
)

CREATE TABLE table_A (
              ...
) INHERITS (post)

CREATE TABLE table_B (
              ...
) INHERITS (post)

The problem with this approach is that from an entry in table A I'd have no way of finding the parent_id (since it could either refer to B or A), so (to my knowledge) for every lookup I'd have to join all the tables and do it that way, which seems suboptimal.
I'm not sure if I'm missing something, any pointers to a potentially better solution would be much appreciated, thank you.

If you really want to avoid NULLs, go with the inheritance.
Unless you want to query all posts with all replies all the time, I think joins won't be a problem if you index your tables.


How do you increase MySQL performance using current server resources? I have a MySQL database and some of my queries became slow.  Query time is not stable. Most of the queries are fast, but some of them (may be that reads and returns less data) take long time.

I know that best practices is add indexes or refactor code, but i'm already added indexes and I don't want to refactor code (at least while I have other variants).

I have 8 Gb free memory and CPU is loaded only to 25% at peak. So I want to use all my resources.

I try to tune MySQL configuration but I havn't experience in such tuning, so i increase productivity no so much that I want. Here is an example:

# Query_time: 3.019647  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 504
SET timestamp=1313380874;
SELECT COUNT(inboxentities.id) FROM inboxentities  WHERE (active=true)AND(deleted=false)AND((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0'))AND(notification=true);

P. S. inboxentities has 500.000 records. I have index1 (idx_to) : to_ and index2 (idx_complex): deleted,notification,active,to_

Here is result of explain select:

+----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+
| id | select_type | table         | type | possible_keys                                  | key    | key_len | ref   | rows | Extra       |
+----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | inboxentities | ref  | idx_status,idx_statusToname,idx_to,idx_complex | idx_to | 768     | const |  286 | Using where |
+----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+

Here is your original query from the slow log

SELECT COUNT(inboxentities.id) FROM inboxentities
WHERE (active=true)
AND (deleted=false)
AND ((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0'))
AND (notification=true);

The most effective way to index for a query like this is to generate an index that covers as many of the fields in the WHERE clause as possible. An index produced with that concept in mind is called a covering index.

Look at the WHERE clause

'active' is true or false (2 values). An index of just that field is lopsided.
'deleted' is true or false (2 values). An index of just that field is lopsided.
'notification' is true or false (2 values). An index of just that field is lopsided.
'to_' is a values worth indexing and narrowing down.

Although you have idx_complex defined, there is one more element to the query causing the idx_complex index not be used: the COUNT(inboxentities.id) clause.

You are counting something in a table. The index has no reference point to the table except to_. The MySQL Query Optmizer would choose idx_to, the simplest index. To force the MySQL Query Optimizer to choose the covering index you want (idx_complex) just COUNT from the index rather than the table. My suggestion is to change the query slightly:

SELECT COUNT(to_) FROM inboxentities
WHERE (active=true)
AND (deleted=false)
AND ((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0'))
AND (notification=true);

Give it a Try !!!

UPDATE 2011-08-15 15:16 EDT

Some have seen marginal-to-significant performance increases scaling up by changing my.cnf to satisfy Storage Engine performance needs.

You need to set the MyISAM Key Cache and InnoDB Buffer Pool.

This will recommend the right size MyISAM Key Cache for your given data set:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will recommend the right size InnoDB Buffer Pool for your given data set

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

BTW

(SELECT 0 PowerOf1024) generates the answer in Bytes
(SELECT 1 PowerOf1024) generates the answer in KB
(SELECT 2 PowerOf1024) generates the answer in MB
(SELECT 3 PowerOf1024) generates the answer in GB

If you go beyond this, email me.

Once you have these settings, make sure that the combined number for recommended_key_buffer_size and recommended_innodb_buffer_pool_size does not exceed 75% of installed RAM.

UPDATE 2011-08-15 15:35 EDT

You may want to run mysqltuner.pl on the server to let it tell you how to tweek memory useage for DB Connections. Settings governing memory usage per DB Connection include

join_buffer_size
sort_buffer_size
read_buffer_size

These are usually multiplied by max_connections.

You can get mysqltuner.pl get downloading it as follows from the Linux command line:

wget mysqltuner.pl

The output from it is something like this:

$ perl mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: username
Please enter your MySQL administrative password: (password hidden)

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.47-community-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 7G (Tables: 6)
[--] Data in InnoDB tables: 2G (Tables: 382)
[!!] Total fragmented tables: 84

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 20h 24m 6s (54M q [49.462 qps], 555K conn, TX: 287B, RX: 95B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 9.3G global + 48.2M per thread (1250 max threads)
[!!] Maximum possible memory usage: 68.2G (291% of installed RAM)
[OK] Slow queries: 0% (647/54M)
[OK] Highest usage of available connections: 5% (69/1250)
[OK] Key buffer size / total MyISAM indexes: 256.0M/19.4M
[OK] Key buffer hit rate: 100.0% (51M cached / 10 reads)
[OK] Query cache efficiency: 89.3% (47M cached / 52M selects)
[!!] Query cache prunes per day: 60670
[OK] Sorts requiring temporary tables: 0% (239 temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 47% (1M on disk / 2M total)
[OK] Thread cache hit rate: 99% (103 created / 555K connections)
[OK] Table cache hit rate: 21% (722 open / 3K opened)
[OK] Open file limit used: 0% (70/32K)
[OK] Table locks acquired immediately: 99% (12M immediate / 12M locks)
[OK] InnoDB data size / buffer pool: 2.4G/8.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Increasing the query_cache size over 128M may reduce performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 1G) [see warning above]
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)


Why do my Batch Requests increase when doing a Differential Backup I have started doing some long-term tracking of various activities on our 2012 SQL Server and I have noticed an increase in Batch Requests during our incremental backups.

To give an idea, normal day-to-day we are around 10-20 batch requests per second but during the time our differential backup runs, it jumps to 100-130 per second.

I know this is not a lot but it made me curious as to what is going on that it increases 10-fold.

Not sure what info you might need to help troubleshoot this issue.

After monitoring the active sessions and queries during the high activity period, I have come to the conclusion that the third party software we use (LiteSpeed) to perform backups is the culprit.

There seems to be a lot of queries to get information about the maintenance plan, updating backup statistics, getting information about the server and the database, etc. This server is not only the one being backed up at this time but it is also the "Central Repository" for all our servers.

It appears I jumped the gun by posting here but at least this might prevent others from spinning their wheels on something that does not appear to be a real issue or one you can do anything about, other than not using the third party software. If our environment becomes bogged because of this, I would bet moving the "Central Repository" to another server would reduce the load on this one.


Invoke SQLCMD doesn't work when used with xp_cmdshell I am trying to get  disk space report from bunch of servers and insert them into sql table..

below is sample of what i am trying to do,Server names will be populated from a table and passed as a comma seperated list.Even passing one server also has the same result

set @ps = 'powershell.exe -noexit -c "

$computers=Get-WmiObject -Class Win32_Volume '+@servernames+'
foreach($computer in $computers)
{
$pscomputername=$computer.pscomputername
$name=$computer.name
$capacity=$computer.capacity
$freespace=$computer.freespace
$Label=$computer.Label

$insertquery="
INSERT INTO [dbo].[temp_disksdata]
           (
           [servername] ,
           [DiskName] ,
           [Capacity(GB)] ,
           [FreeSpace(GB)],
           [label]
           )
     VALUES
           (''$pscomputername'' ,
           ''$name'' ,
           ''$capacity'',
           ''$freespace'',
           ''$Label''
           )
GO
"

Invoke-SQLcmd  -query $insertquery -ServerInstance ''someserver'' -Database dbname

}

"';

Now i try to pass the variable to xp_cmdshell like below

execute xp_cmdshell @ps;

When invoked in SSMS,above returns null,but works in powershell..

Any ideas why ?

Below are a few things i tried

1.Same account(admin) is used in both shell and ssms
2.tried multiple things like import modules
3.XP_CMDshell works,but this returns null only for this query
4.I have tried to add -nowait,but that doesn't help as well

I have been trying to get this done from more than a day,but this doesn't work..
I have to use xp_cmdshell because writing a c# app is not allowed.Bat file doesn't help because server names are passed as a comma seperated list.

I am modifying the code and was told not to rewrite when asked

Please let me know if you need any further info

Repro:
Below is entire command from print,if you remove powershell.exe and -c ,below will run in powershell..

powershell.exe  -c "

$computers=Get-WmiObject -Class Win32_Volume 'servername'
foreach($computer in $computers)
{
$pscomputername=$computer.pscomputername
$name=$computer.name
$capacity=$computer.capacity
$freespace=$computer.freespace
$Label=$computer.Label

$insertquery="
INSERT INTO [dbo].[temp_disksdata]
           (
           [servername] ,
           [DiskName] ,
           [Capacity(GB)] ,
           [FreeSpace(GB)],
           [label]
           )
     VALUES
           ('$pscomputername' ,
           '$name' ,
           '$capacity',
           '$freespace',
           '$Label'
           )

"

Invoke-SQLcmd  -query $insertquery -ServerInstance 'servername' -Database dbname

}

"

There are several problems here:

The main issue is that the CMD shell executes each line as it comes in via a return; it does not wait for an "end of command" indicator such as ;, nor does it attempt to figure it out via parsing (like SQL does upon a batch being submitted) since there is no way to know if a return ends a "command" or not. Typically you can use ^ for line continuation, but that does not seem to work within an input paramter (at least not one with an unclosed quote).

So, for this you need to reduce everything to a single line. That is simple enough as you can just replace Carriage Return (character 13) with an empty string and Line Feed/Newline (character 10) with a space. But, that alone will not work since PowerShell statements need to be separated by either a newline or a semicolon. Currently newlines are being used, but if we replace those with spaces, then semicolons will be required.

Hence, Step 1 is to append a semicolon to each actual PowerShell command. This is probably good-form in general, same as with T-SQL.
Step 2 would be doing the two REPLACE calls against the @ps variable to transform this into a single-line script.
You have embedded double-quotes, used for creating the INSERT statement. Those need to be escaped with a backslash ( \ ).
Get-WmiObject does not seem to work as you are attempting to use it here. I had to add -ComputerName to pass in the name of a system. If I left it blank then it returned the local system info, but if you want to return info for remote servers then you will probably need to use the -ComputerName switch and pass in one server at a time. That will most likely require an additional outer loop to handle multiple server names (and it would be there that you pass in @ServerNames to get split for iterating through the list).
Are "capacity" and "freespace" really stored as strings in the DB? If not, you might want to remove the single-quotes around those "values" in the INSERT statement.

The following at least executes. You can debug from there. I was getting errors on it not finding the "Invoke-SQLcmd" module or something.

DECLARE @ServerNames NVARCHAR(4000);
SET @ServerNames = N'ALBRIGHT';

DECLARE @ps NVARCHAR(4000);
SET @ps = N'powershell.exe -noexit -c "

$computers=Get-WmiObject -Class Win32_Volume -ComputerName ' + @ServerNames + N';
foreach($computer in $computers)
{
$pscomputername=$computer.pscomputername;
$name=$computer.name;
$capacity=$computer.capacity;
$freespace=$computer.freespace;
$Label=$computer.Label;

$insertquery=\";
INSERT INTO [dbo].[temp_disksdata]
           (
           [servername] ,
           [DiskName] ,
           [Capacity(GB)] ,
           [FreeSpace(GB)],
           [label]
           )
     VALUES
           (''$pscomputername'' ,
           ''$name'' ,
           ''$capacity'',
           ''$freespace'',
           ''$Label''
           )
GO
\";

Invoke-SQLcmd  -query $insertquery -ServerInstance ''someserver'' -Database dbname;

}

"';

SET @ps = REPLACE(REPLACE(@ps, NCHAR(13), N''), NCHAR(10), N' ')
PRINT @ps;

EXEC xp_cmdshell @ps;


ORA-12899: importing a .dmp file + character set conversion I'm working on a django project on Ubuntu 10.04 with Oracle Database Server , so I've installed: Oracle Database 10g xe universal Rel.10.2.0.1.0 cx_Oracle-5.0.4-10g-unicode-py26-1.x86_64

When I import a .dmp file which is generated by oracle 10gr2 enterprise edition (on Windows XP) I get errors as listed below(it happens with data types varchar2), probably connected to the characterset conversion (my db contains greek) and my data don't sync completely.

  Username: Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Export file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) export client uses EL8MSWIN1253 character set (possible charset conversion) . . importing table "xxx" IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "xxx"."xxx"."xxx" (actual: 41, maximum: 40) and so on

My oracle express server is running with these settings:

NLS_CHARACTERSET AL32UTF8
NLS_DATE_LANGUAGE AMERICAN
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_TERRITORY AMERICA

And the database server which generated the .dmp file is running with:

NLS_CHARACTERSET EL8MSWIN1253
NLS_NCHAR_CHARACTERSET AL16UTF16

Does anyone have a hint how I could solve this problem?

One solution is to have both databases using the same character set. Considering your destination is a free XE version why not do a new install?  Just make sure you use EL8MSWIN1253 character set and the same UTF version.


Two "Connect SQL" permissions in SQL Server 2012 What is the difference between these two "Connect SQL" permissions in SQL Server 2012?

While I can't be certain because you blanked out the Grantor I think you will find that the rows have a different entry in that column.  It is possible to have multiple entries of a permission in that list if the individual who granted the permission is different.


MySQL Storage Engine Changed from InnoDB to MyISAM on restoring backup in lower version I have two production instance, old production instance have MySQL version "5.1.73-community-log" and the new production instance have MySQL version "5.1.72-community"

When I take backup from the old production instance to new production instance some of the InnoDB tables changed to MyISAM tables.

I have almost same setting in both the instance the difference is I have changed "innodb_log_buffer_size" and "innodb_log_file_size" on new production instance to speedup the restore process.

Change effect:

Because of this all referential integrity were lost where the tables engine was previously InnoDB and now MyISAM.
"LOCKING" occurred frequently in "SELECT, INSERT, UPDATE" queries.

I am not able to find correct cause for this because when I check the SHOW ENGINES it shows below result which means InnoDB is enabled:

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

If the backup was restored from a .sql dump which didn't include the ENGINE on the CREATE TABLE statements, MySQL would fall back to the "default_storage_engine" (5.7 manual version), which before 5.5.5 was "MyISAM"


mysql column naming conventions for foreign keys Lately I've been thinking about what's a better name for foreign keys, either user_id, or id_user style.

Personally I think id_user style is better for handling within database (db queries) where it's clear from the first three characters that the column is containing ID.

On the other hand I find user_id style more suitable e.g. with use of HTML autocomplete fields where there are two input fields; one name="user" for text, the other name="user_id" for his ID; which makes it easier to recognize that they belong to each other because they both start their name by defining what the content will be about.

What do you think?

I prefer user_id but I think it doesn't really matter as long as you stay consistent and not mix them up so you keep your overview structured and readable.