Plan cache size and reserved memory When running a query including the Actual Execution Plan, the root operator (SELECT) tells me that the Cached Plan Size is 32KB.

A query that joins sys.dm_exec_cached_plans and sys.dm_os_memory_objects, looking at the plan in question, says that values for pages_in_bytes and max_pages_in_bytes are 32768 (32KB), which matches the cached plan size.

What I don't understand is what the value in sys.dm_exec_cached_plans.size_in_bytes, which is 49152 (48KB) stands for. I've read BOL on all these columns, and especially size_in_bytes which says:

  "Number of bytes consumed by the cache object."

I can't get that last bit of the puzzle in place, to understand what it really means.

I know that all operators (not talking about the additional memory grant used for sorts and hashes) requires some amount of fixed memory, to store state, make calculations etc., which is stored with the optimized plan in the cache, but where?

So, my questions are:

What does size_in_bytes really mean
Why is it a higher value than "Cached plan size"?
Where is the fixed amount of memory for all operators/iterators reserved, is it with the "Cached plan size" (32Kb in my example), or anywhere else?

I know they're different DMVs with different functions, but they are related. The compiled (cached) plans in sys.dm_exec_cached_plans joins sys.dm_os_memory_objects on memory_object_address column. The reason I post the questions here, is that I'm asking for help on this, understanding how to interpret the DMVs and their columns.

If size_in_bytes is the cached plan size, why does SQL Server say another value in actual execution plan?

New query, new numbers:

Actual plan

Cached Plan Size 16KB
CompileMemory 96KB

DMVs:

sys.dm_exec_cached_plans.size_in_bytes 24KB
sys.dm_os_memory_objects.pages_in_bytes, .max_pages_in_bytes 16KB.

Also, note that this query doesn't require any additional memory grants for sorts and hash operations.

Microsoft SQL Server 2012 - 11.0.5343.0 (X64)

The reason that the size_in_bytes field of the sys.dm_exec_cached_plans DMV, at least in terms of "Compiled Plans", is larger than the CachedPlanSize attribute of the QueryPlan node in the XML plan is because a Compiled Plan is not the same thing as a Query Plan. A Compiled Plan is comprised of multiple Memory Objects, the combined size of which equates to the size_in_bytes field. So, the description of "Number of bytes consumed by the cache object" that you found in the documentation is accurate; it's just that it is easy to misinterpret what is meant by "cache object" given the name of the DMV and that the term "plan" has multiple meanings.

A Compiled Plan is a container that holds various pieces of information related to the query batch (i.e. not just a single statement), one (or more) of those pieces being the query plan(s). Compiled Plans have a top-level Memory Object of MEMOBJ_COMPILE_ADHOC which is the row in sys.dm_os_memory_objects that is linked via the memory_object_address field in both DMVs. This Memory Object contains the symbol table, parameter collection, links to related objects, accessor cache, TDS metadata cache, and possibly some other items. Compiled Plans are shared amongst Sessions/Users that are executing the same batch with the same Session settings. However, some related objects are not shared between Sessions/Users.

Compiled Plans also have one or more dependent objects that can be found by passing the plan_handle (in sys.dm_exec_cached_plans) into the sys.dm_exec_cached_plan_dependent_objects DMF. There are two types of dependent objects: Executable Plan (Memory Object = MEMOBJ_EXECUTE) and Cursor (Memory Object = MEMOBJ_CURSOREXEC). There will be 0 or more Cursor objects, one per each cursor. There will also be one or more Executable Plan objects, one per each User executing that same batch, hence Executable Plans are not shared between Users. Executable Plans contain run-time parameter and local variable info, run-time state such as the currently executing statement, object ids for objects created at run-time (I assume this refers to Table Variables, Temporary Tables, Temporary Stored Procedures, etc), and possibly other items.

Each statement within a multi-statement batch is contained within a Compiled Statement (Memory Object = MEMOBJ_STATEMENT). The size of each Compiled Statement (i.e. pages_in_bytes) divided by 1024 should match the CachedPlanSize="xx" values of the <QueryPlan> nodes in the XML plan. Compiled Statements will often have one (possibly more?) associated runtime Query Plans (Memory Object = MEMOBJ_XSTMT). Finally, for each runtime Query Plan that is a query, there should be an associated Query Execution Context (Memory Object = MEMOBJ_QUERYEXECCNTXTFORSE).

With respect to Compiled Statements, single-statement batches do not have separate Compiled Statement (i.e. MEMOBJ_STATEMENT) or separate runtime Query Plan (i.e. MEMOBJ_XSTMT) objects. The value for each of those objects will be stored in the main Compiled Plan object (i.e. MEMOBJ_COMPILE_ADHOC), and in that case, the pages_in_bytes value for that main object divided by 1024 should match the CachedPlanSize size in the <QueryPlan> node of the XML plan. Those values will not equate, however, in multi-statement batches.

The size_in_bytes value can be derived by summing the entries in the sys.dm_os_memory_objects DMV (the items noted above in bold), all related by dm_os_memory_objects.page_allocator_address for that Compiled Plan. The trick to getting the correct value is to first get the memory_object_address from sys.dm_exec_cached_plans for a particular Compiled Plan, then use that to get the corresponding MEMOBJ_COMPILE_ADHOC row from sys.dm_os_memory_objects based on its memory_object_address field. Then, grab the page_allocator_address value from sys.dm_os_memory_objects for that row, and use it to grab all rows from sys.dm_os_memory_objects that have the same page_allocator_address value. (Please note that this technique does not work for the other Cached Object types: Parse Tree, Extended Proc, CLR Compiled Proc, and CLR Compiled Func.)

Using the memory_object_address value obtained from sys.dm_exec_cached_plans, you can see all of the components of the Compiled Plan via the following query:

DECLARE @CompiledPlanAddress VARBINARY(8) = 0x00000001DC4A4060;

SELECT obj.memory_object_address, obj.pages_in_bytes, obj.type
FROM   sys.dm_os_memory_objects obj
WHERE  obj.page_allocator_address = (
                               SELECT planobj.page_allocator_address
                               FROM   sys.dm_os_memory_objects planobj
                               WHERE  planobj.memory_object_address = @CompiledPlanAddress
                              )
ORDER BY obj.[type], obj.pages_in_bytes;

The query below lists all of the Compiled Plans in sys.dm_exec_cached_plans along with the Query Plan and statements for each batch. The query directly above is incorporated into the query below via XML as the MemoryObjects field:

SELECT cplan.bucketid,
       cplan.pool_id,
       cplan.refcounts,
       cplan.usecounts,
       cplan.size_in_bytes,
       cplan.memory_object_address,
       cplan.cacheobjtype,
       cplan.objtype,
       cplan.plan_handle,
       '---' AS [---],
       qrypln.[query_plan],
       sqltxt.[text],
       '---' AS [---],
       planobj.pages_in_bytes,
       planobj.pages_in_bytes / 1024 AS [BaseSingleStatementPlanKB],
       '===' AS [===],
       cplan.size_in_bytes AS [TotalPlanBytes],
       bytes.AllocatedBytes,
       (SELECT CONVERT(VARCHAR(30), obj.memory_object_address, 1)
               AS [memory_object_address], obj.pages_in_bytes, obj.[type]
               --,obj.page_size_in_bytes
        FROM   sys.dm_os_memory_objects obj
        WHERE  obj.page_allocator_address = planobj.page_allocator_address
        FOR XML RAW(N'object'), ROOT(N'memory_objects'), TYPE) AS [MemoryObjects]
FROM   sys.dm_exec_cached_plans cplan
OUTER APPLY sys.dm_exec_sql_text(cplan.[plan_handle]) sqltxt
OUTER APPLY sys.dm_exec_query_plan(cplan.[plan_handle]) qrypln
INNER JOIN sys.dm_os_memory_objects planobj
        ON planobj.memory_object_address = cplan.memory_object_address
OUTER APPLY (SELECT SUM(domo.[pages_in_bytes]) AS [AllocatedBytes]
             FROM   sys.dm_os_memory_objects domo
             WHERE  domo.page_allocator_address = planobj.page_allocator_address) bytes
WHERE  cplan.parent_plan_handle IS NULL
AND    cplan.cacheobjtype IN (N'Compiled Plan', N'Compiled Plan Stub')
--AND cplan.plan_handle = 0x06000D0031CD572910529CE001000000xxxxxxxx
ORDER BY cplan.objtype, cplan.plan_handle;

Please note that:

the TotalPlanBytes field is just a re-statement of the sys.dm_exec_cached_plans.size_in_bytes field,
the AllocatedBytes field is the SUM of the related memory objects that typically matches TotalPlanBytes (i.e. size_in_bytes)
the AllocatedBytes field will occasionally be greater than TotalPlanBytes (i.e. size_in_bytes) due to the memory consumption increasing during execution. This seems to happen mostly due to recompilation (which should be evident with the usecounts field showing 1)
the BaseSingleStatementPlanKB field should match the CachedPlanSize attribute of the QueryPlan node in the XML, but only when using a single query batch.
for batches with multiple queries, there should be rows marked as MEMOBJ_STATEMENT in sys.dm_os_memory_objects, one for each query. The pages_in_bytes field for these rows should match the individual <QueryPlan> nodes of the XML plan.

Resources:

What are the different cached objects in the plan cache?
1.0 Structure of the Plan Cache and Types of Cached Objects
2.0 Sql_Handle and Plan_Handle Explained
Documentation for sys.dm_exec_cached_plans
Documentation for sys.dm_os_memory_objects
Documentation for sys.dm_exec_sql_text
Documentation for sys.dm_exec_query_plan


MySQL Replication Master Slave Architecture - Web app that is based on the SLAVE DB We are developing a web application (Spring mvc based app) that uses a MySQL DB. (let's call it the "original app")

The DB architecture uses a MASTER-SLAVE configuration with replication.
The web app uses the MASTER for the functionality.

We are now developing a Back-office application for our "original app".
This back office is in itself a web app (spring mvc based).

In order to reduce the performance impact on the "original app" we would like to use the SLAVE replica as the DB for the back-office.

This replica DB will include unique tables (that will exists only on the SLAVE, to serve the back-office app).

Non of us ever worked in such architecture, and we are pretty intimidated by this, therefore we have some questions:

Is this architecture even viable?
In case of a replication-error , How hard will it be to recover? remember, that the SLAVE will consist of tables that are not in the MASTER
Any other tips \ information we can use?
We cannot reconstruct the slave-only tables from the replicated tables. No That is one of my biggest concerns. Is that a show stopper?

Yes
It's not going to be a problem but of course it depends on the problem itself. Lets use the term - incident. Problem is already a problem by definition. Solve incidents, analyze the cause , prevent the problem!
In case of an error, try to think what caused it. You are right about worrying, as it is a very common cause of issues.

Try to strictly separate the replicated objects from the unique ones in slave. Different DB
Use some constraints and "tips" for the replication process, like:

--replicate-wild-ignore-table=db_name.tbl_name
include slave status verification in your backup procedures
redirect all reads to the slave (of course the important reads should be done in master and ONLY they)
backup the slave only
include the GTID (you might need it)

Be more AGILE with it. Do it, start testing and you will see how it goes. It's easy and you sound competent enough to do it.

Always monitor your replication. Using GTID and row-based replication may help you a bit with the consistency.


MYSQL Select, where rows exists I have a Mysql Database and tables with the structure:

Table 'photos' - ID, Name
Table 'photos_tags' - Key, Photo_ID, Tag

My goal is to select all photos, which has one tag (it's not a problem) and more tags at once.

I've tried this query, but it's not work

SELECT photos.id FROM photos, photos_tags WHERE photos.id = photos_tags.photo_id AND (tag = 'cute' OR tag = 'cool')

The main problem - this query will select both photos with one of these tags or with two of them. But i need only photos with two these tags at one photo.

Can you help me please? I'm new at MySQL

UPDATE: May be there is a better way to organize this schema? My current realization has 'tags' text-field right in table 'photos'. And I use FIND_IN_SET to make a select. But my table has about 1.5M rows - and some queries are very slow.

This is a "relational division" problem and there are several ways to solve.

One way is with two EXISTS subqueries:

SELECT p.id
FROM photos AS p
WHERE EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id
          AND pt.tag = 'cute'
      )
 AND  EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id
          AND pt.tag = 'cool'
      ) ;

Another with two joins (assuming that the photo_tags table has a unique constraint on (photo_id, tag_id), i.e. no photo can been tagged with the same tag twice):

SELECT p.id
FROM photos AS p
  JOIN photos_tags AS pt1
    ON  p.id = pt1.photo_id
    AND pt1.tag = 'cute'
  JOIN photos_tags AS pt2
    ON  p.id = pt2.photo_id
    AND pt2.tag = 'cool' ;

One more way is @Abhik's answer, with one join and GROUP BY.

You can see a few more ways (more than 13) in this awesome answer: How to filter SQL results in a has-many-through relation. Not all of them will work in MySQL as the question is for Postgres but the problem is identical.

Strictly speaking - and if there is a FOREIGN KEY from photo_tags to photos - the photos table can be removed from all the above queries. You can return the photo_id instead. But I guess you may want more columns from the photo table in the result set.


Merge with multiple records at once There is a piece of code, from a previous dev, that is doing a merge statement with variables, and processing one record at time.
I'm sure the merge can deal with multiple records at one.
create table dbo.Staging
(
    transactionID bigint not null primary key nonclustered,
    transactionDate date not null,
    productId int not null,
    orderId int not null,
    qty int not null,
    unitPrice decimal(8,2) not null
)

create table dbo.Transactions
(
    transactionID bigint not null primary key clustered,
    transactionDate date not null,
    productID int not null,
    ReferenceOrderID  int not null,
    Qty int not null,
    ActualCost decimal(8,2) not null
)
insert into dbo.Staging(transactionID,transactionDate,productId,orderId,qty,unitPrice)
values(1001,'20210101',45889,1501,5,105),
      (1002,'20210102',45892,1503,2,8),
      (1003,'20210103',45555,1555,5,5),
      (1004,'20210104',44444,1444,4,4)

insert into dbo.Transactions(transactionID,transactionDate,productId,ReferenceOrderID,qty,ActualCost)
values
      (1003,'20210101',41111,1111,1,1),
      (1004,'20210101',41111,1111,1,1)

The code is much longer, but the here is the code :
declare
    @bi_transactionID bigint ,
    @bi_transactionID_init bigint =0,
    @d_transactionDate date,
    @i_productId int,
    @i_orderId int,
    @i_qty int ,
    @dm_unitPrice decimal(8,2)

SELECT @bi_transactionID = MIN(s.transactionID)
FROM dbo.Staging as s
WHERE s.transactionID > @bi_transactionID_init

WHILE @bi_transactionID is not null
BEGIN
        SELECT
            @bi_transactionID= s.transactionID,
            @d_transactionDate=s.transactionDate ,
            @i_productId =s.productId,
            @i_orderId =s.orderId,
            @i_qty = s.qty,
            @dm_unitPrice = s.unitPrice
        FROM dbo.Staging as s
        WHERE
            s.transactionID = @bi_transactionID

        MERGE dbo.Transactions AS T
        USING (SELECT s.transactionID FROM dbo.Staging as s WHERE s.transactionID = @bi_transactionID) AS s
        ON (t.transactionID = s.transactionID)
        WHEN MATCHED THEN
        UPDATE  SET
                transactionDate = @d_transactionDate,
                productId = @i_productId,
                ReferenceOrderID=@i_orderId,
                qty = @i_qty,
                ActualCost = @dm_unitPrice

        WHEN NOT MATCHED THEN
        INSERT (transactionID,transactionDate,productId,ReferenceOrderID,qty,ActualCost)
        VALUES (@bi_transactionID, @d_transactionDate,@i_productId,@i_orderId,@i_qty,@dm_unitPrice);

        SET @bi_transactionID_init = @bi_transactionID

        SELECT @bi_transactionID = MIN(s.transactionID)
        FROM dbo.Staging as s
        WHERE s.transactionID > @bi_transactionID_init

END

I believe it can be done in one statement, with merge with out the while and varibles.
Can this be done ?

Is this the right ? from my side, the results are the same...
MERGE dbo.Transactions AS T
        USING (SELECT s.transactionID,s.transactionDate,s.productID,s.orderID,s.qty,s.unitPrice FROM dbo.Staging as s) AS s
        ON (t.transactionID = s.transactionID)
        WHEN MATCHED THEN
        UPDATE  SET
                transactionDate = s.transactionDate,
                productId = s.productId,
                ReferenceOrderID=s.orderId,
                qty = s.qty,
                ActualCost = s.unitPrice

        WHEN NOT MATCHED THEN
        INSERT (transactionID,transactionDate,productId,ReferenceOrderID,qty,ActualCost)
        VALUES (s.transactionID, s.transactionDate,s.productId,s.orderId,s.qty,s.unitPrice);


Select query with row constructors I am using RDBMS MySQL 5.7.
I have two tables with the following structures:

Table A - primary table - products:

+----+-----------+-------------+------------+--------+
| id | title     | description | createdAt  | status |
+----+-----------+-------------+------------+--------+
|  1 | Product A | Desc A      | 2019-07-19 |      1 |
|  2 | Product B | Desc B      | 2019-07-04 |      1 |
+----+-----------+-------------+------------+--------+

Table B - product_categories -> one-to-many model

+----+-----------+------------+
| id | productId | categoryId |
+----+-----------+------------+
|  1 |         1 |          3 |
|  2 |         1 |          5 |
|  3 |         2 |          5 |
|  4 |         2 |          7 |
|  5 |         2 |          9 |
+----+-----------+------------+

productId and categoryId are foreign keys.

I want to select rows from products table which have categories 3 and 5 both. The query should return the only row with id 1.

I have such a query:

SELECT p.id
FROM products p
inner join product_categories pc on p.id = pc.productId
WHERE (pc.productId, pc.categoryId) IN ((p.id, 3), (p.id, 5))
GROUP BY productId
HAVING COUNT(productId) = 2

How you would simplify it? Without using row constructors.

I want to select rows from products table which have categories 3 and 5 both.

SELECT productId
FROM product_categories
WHERE categoryId IN (3, 5)
GROUP BY productId
-- JOIN products ON products.id = product_categories.productId
HAVING 2 = COUNT(DISTINCT categoryId)

If (productId, categoryId) is set as unique by index, you may remove DISTINCT.

products table not needed at all (except the case when some value of productId exists in product_categories but not exists in products).

  How can I do it with two tables: I have another table similar to product_categories, let's say product_highlights and it has one-to-many relationship as well. How can I query results with values (3, 5) in product_categories and (2, 4) in product_highlights?

SELECT t1.productId
FROM product_categories t1
JOIN product_highlights t2 ON t1.productId = t2.productId
WHERE t1.categoryId IN (3, 5)
  AND t2.highlightId IN (2, 4, 6) -- changed to 3 values
GROUP BY productId
-- JOIN products ON products.id = product_categories.productId
HAVING 2*3 = COUNT(DISTINCT categoryId, highlightId)

If (productId, parameterId) is set as unique by index in BOTH tables, you may remove DISTINCT.

Alternatively you can select productId for each table separately in subqueries, then join them in main query.


Required free Disk Space for Index rebuild offline Operations I have SQL Server 2017 Standard Editon with two Databases.

The current total index size of Database1 is 111 GB and for Database2 is 122 GB.

Since I'm on Standard Edition I can only rebuild the Index offline.  We have split the Database-Files on Disk D: and Log-Files on E:. Free disk space on D: is 290 GB and on E: 35 GB.

How can I calculate the required free disk space for offline index rebuild on D: and E:?

For online Index rebuilds, I've found a Microsoft article stating that total space required is approximately 1.2 times the total index size. Is the same estimate true for offline rebuilds?  Do I need this free disk space for Database-Files and for Log-Files?

The Offline index rebuild operation creates a copy of the original index, then drops the original, and replaces it with the copy.

However, you should realize this is per index, not per database.  The typical index rebuild solution will rebuild indexes one-at-a-time in a serial fashion; so you only need space available on the data drive equal to double the size of the largest index.

Since index rebuilds are a logged operation, the log drive should allow enough space for at least a copy of the largest index to be rebuilt, plus any other concurrent operations that may be occurring.  You might consider the use of SORT_IN_TEMPDB = ON for the index rebuild operations, since that allows much of the index rebuild operation to occur in tempdb - if tempdb is located on a different drive that may potentially reduce disk space requirements on your target database's log drive.

Microsoft Docs has some good advice about the transaction log requirements for index operations.

If you regularly run index rebuilds, the database and log file should already have enough unallocated space to run the rebuild operation without requiring these files to grow on disk.  This is assuming, of course, that you don't shrink your database and log files after each rebuild.


In which order does a SQL Replication write the data I use a unidirectional Replication of a database.
In the Database I have a column with the datatype "timestamp" (not datetime).
When the replication is initiated it writes table after table to the subscriber.
My question is: Is the data within a table writen to the subscriber in the same order as it was created in the published database?
So is the timestamp in a table of the subscriber also usable as a sort order as it was on the publisher e.g. for last inserted and updated records? Becouse unfortunatly I do not have a column like EditDateTime.

I'm not sure if you mean Transactional Replication or Snapshot Replication in your current situation, but in either case the ordering of the data should be preserved. You can read more on how Transactional Replication works in that linked Brent Ozar article.
Transactional Replication uses the the Transaction Log to ensure Atomicity and Consistency of the ACID principals in SQL Server. Snapshot Replication is a literal copy (snapshot) of your data in a point in time and exactly preserved that way when distributed to the Subscriber database.
That being said, the data type TIMESTAMP is uniquely generated based on the rowversion by the server and therefore will not match between your Publisher and Subscriber database(s).
For Transactional Replication it should be in the same sequential ordering, for Snapshot Replication I don't know if it can be guaranteed and you'd have to test, but my guess is there's still a consistent row ordering by the underlying rowid of the table, if not the primary key, and therefore should also be consistent (despite being new values in the TIMESTAMP field.)


Generate fake data for several tables with relationships I have 3 tables where I want to generate fake data in UsersCategoryLink.

How can I insert in the table UserCategoryLink the column UserID with random users from the table User and a random id from the table Categories. In this SQL fiddle you can see the tables with some values.

UsersCategoryLink must be filled with random users and categories.
Every user must have two categories.
UserID and CategoryID are primary keys, so every value must be unique.
I am using SQL Server Express.

This is my version of the cross-reference table where you'll store users and the categories they are members of.  Notice there is a primary key clustered index on (UserID, CategoryID); this ensures each row is unique:

CREATE TABLE dbo.XREFUserCategories
(
    UserID INT NOT NULL
        CONSTRAINT FK_XREFUserCategories_UserID
        FOREIGN KEY
        REFERENCES dbo.Users(UserID)
    , CategoryID INT NOT NULL
        CONSTRAINT FK_XREFUserCategories_CategoryID
        FOREIGN KEY
        REFERENCES dbo.Categories(CategoryID)
    , CONSTRAINT PK_XREFUserCategories
        PRIMARY KEY CLUSTERED (UserID, CategoryID)
);
GO

Since you already have data in the Users and Categories tables, you can use a CTE with the ROW_NUMBER windowing function partitioned by UserID and ordered by an essentially random value, NEWID().  This allows us to pick precisely two "categories" for each "user":

;WITH rs AS
(
    SELECT u.UserID
        , c.CategoryID
        , rn = ROW_NUMBER() OVER (
            PARTITION BY u.UserID
            ORDER BY NEWID()
    )
    FROM dbo.Users u
        , dbo.Categories c
)
INSERT INTO dbo.XREFUserCategories(UserID, CategoryID)
SELECT rs.UserID
    , rs.CategoryID
FROM rs
WHERE rs.rn <= 2;

The following is a sample of rows I generated on my system:


Does PostgreSQL btree_gin extension use btree or gin data structure? In order to define a GIN index on an array and scalar field (for example) you need to enable the btree_gin extension in PostgreSQL. Then you can define your indexes as a GIN index.

However I don't understand if, under the hood, PostgreSQL is using a btree structure or a GIN structure.

The resulting index is a true blood GIN index. These operator classes are not installed by default because they are rarely needed.

A single-column GIN index on a column of scalar type would be inferior to a B-tree index in almost every way.


Is there any way to reduce/shrink tempdb.mdf size without restarting SQL Server Is it necessary to restart SQL Server after DBCC SHRINKFILE on tempdb.mdf or is there any other method to reduce the size of tempdb.mdf without restarting SQL Server?

Kindly help as I need this for a production server and I'm hoping to do the shrinking without any down time.

You can do that this way:

-- write everything from your buffers to the disc!
CHECKPOINT;
GO
-- Clean all buffers and caches
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');
DBCC FREESESSIONCACHE;
GO
-- Now shrink the file to your desired size
DBCC SHRINKFILE (TEMPDEV, 40960);
-- Make sure that there is no running transaction which uses the tempdb while shrinking!
-- This is most trickiest part of it all.
GO

The last step is the most trickiest. During the shrink process, no other action should use the tempdb, as this could cause an abort of your SHRINKFILE operation. Due to the fact that the tempdb is quite easy to shrink, it shouldn't take to long to shrink it.

Beware that this is something like a "soft restart". Everything will be removed from the buffers and written to the disc. This means an impact on your I/O subsystem (write) as it have to handle all the write operations. After that you can shrink the file (which has an impact on read and write performance) and at the end, all processes which query any table will need to retrieve the data back from the I/O subsystem into the buffers. This may hurt more than an restart.

If you're running a development system, you should just restart the machine instead of this way. But on some production systems without a failover partner, this may be useful.


How to create index on long data type field I have a MySQL table with a long data type field:

`create_date` long NOT NULL,

I want to index it:

CREATE INDEX ix_logs_create_date ON `logs` (create_date) USING BTREE;

But I encounter the following exception:

Error Code: 1170. BLOB/TEXT column 'create_date' used in key specification without a key length 0.049 sec

Is it possible to index a long data type field?  I have searched but cannot find anything discussing this particular issue

For long (mediumtext) you have to specify how much of the string you want to index. If you want the first 100 bytes in the column to be indexed:

CREATE INDEX ix_logs_create_date ON logs (create_date(100)) USING BTREE;

May I ask why you named a column of type long create_date?


How to Import all the files in a directory with a single command using mysqlimport? I'm trying to dump and import some data from a MySQL (5.7) database.

Running the command

mysqldump --tab=/mysqldump mydatabase

I'm able to get a .sql and .txt file for each table.

How can I import all the files/tables using mysqlimport in a single command?

I have tried mysqlimport mydatabase /mysqldump but it does not work.

Thanks.

Do not use --tab option, use general syntax mysqldump [options] db_name tbl_name ... > dump.sql instead. You'll get an ordinary sql-script that can be executed via CLI-client mysql such way:

mysql -u dbauser -p < /path/to/dump.sql

Anyway to import different dumpfiles you have to use some shell script that build the list of files in the given directory and then proceed that list one by one like that:

#!/bin/sh
for file in /path/to/*.txt
do
   mysqlimport database $file
done
####


Homebrew MariaDB Server Start Error with 'mysql.server start' When starting MariaDB 10.3.8 on MacOS 10.13.6 the following strange behavior occurs:

mac:~user$ mysql.server start

Starting MariaDB

.180813 17:50:11 mysqld_safe Logging to '/usr/local/var/mysql/mbp.local.err'.

180813 17:50:11 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql.. ERROR!

There are actually many more dots in the output before ERROR! is displayed; the process takes a couple of minutes. The odd thing is the server does run. And shutdown appears normal:

mac:~user$ mysql.server stop

Shutting down MariaDB.. SUCCESS!

Any idea how to determine what is causing the error message on startup and fix it?

I can reproduce your issue. Don't use mysql.server start in macOS.

If you installed MariaDB with homebrew, use brew services to start and stop the database. It takes only a second and doesn't write strange error to stdout.

brew services stop mariadb
Stopping `mariadb`... (might take a while)
==> Successfully stopped `mariadb` (label: homebrew.mxcl.mariadb)

brew services start mariadb
==> Successfully started `mariadb` (label: homebrew.mxcl.mariadb)

overview of services in homebrew:

brew services list
Name     Status  User     Plist
logstash stopped
mariadb  started Kim /Users/Kim/Library/LaunchAgents/homebrew.mxcl.mariadb.plist
mysql    stopped

general help

brew services --help
brew services command:
    Integrates Homebrew formulae with macOS' launchctl manager.

    [sudo] brew services list
    List all running services for the current user (or root)

    [sudo] brew services run formula|--all
    Run the service formula without starting at login (or boot).

    [sudo] brew services start formula|--all
    Start the service formula immediately and register it to launch at login (or boot).

    [sudo] brew services stop formula|--all
    Stop the service formula immediately and unregister it from launching at login (or boot).

    [sudo] brew services restart formula|--all
    Stop (if necessary) and start the service immediately and register it to launch at login (or boot).

    [sudo] brew services cleanup
    Remove all unused services.

    If sudo is passed, operate on /Library/LaunchDaemons (started at boot).
    Otherwise, operate on ~/Library/LaunchAgents (started at login).


Zerofill Auto Increment - mySql Ok,

So I'm working on an app and I can't remember, for the life of me, why I set a zerofill on an Auto_Increment column when I created the database. And I couldn't exactly find any compelling info while searching as to why I'd use or not use zerofill on this column.

After all, it does not affect the data in the filed (or the int value), it is just for formatting sake.

So, I figured I would get your take on this matter.

Why Use (or not Use) Zerofill on an Auto_Increment field (or for that matter any int field).

I have only seen one instance where this made a difference.  The int was being cast as a string, and then sorted alphabetically.  The zero fill prevented a strange sort order, such as 10 coming before 9.


MySQL .frm file format: how to extract column info? Is there a more up-to-date specification for MySQL's .frm files than the one on dev.mysql.com? I'm trying to parse it manually (don't ask... just experimenting; not trying to recover anything).

The first part on "Table 11.1 .frm File Header Section" mostly matches up, but after that I'm not sure what to do.

"Table 11.2 .frm File Key Information Section" says the "Name of engine" is at 0x101a, but I'm finding "InnoDB" at 0xAA39 in my .frm file. I'm guessing those offsets aren't actually exact, and they depend the number of indexes and such.

How can I figure out the actual offset for where the "Table 11.4 .frm File Column Information Section" begins?

This is bit late but in case anyone is still interested.....

I have been helping a student to write a program (in PHP!) to recreate the CREATE TABLE statement that is implicit in mySQL .frm files, by attempting t parse the .frm files directly. (yes we know it can be done with various tools!!).

It was very difficult to track down the structure of the file and what is available at the site reference by mpen above is incomplete and sometimes incorrect.

Eventually I was able to track down the source code for mysqlfrm.exe and from there I located the particular class file that does the .frm file parse. It is written in Python, which is very readable - even if you dot know the language.

I was able to use this file (apart from a few minor corrections) to work out the indices in the .frm file where everything is stored and how to recover the various fields.

Here is the link...

http://www.commongrounds.co.uk/Library/lib/python2.7/site-packages/mysql/utilities/common/?DA and the required file is aptly names frm_reader.py/

Let it not be said that the .frm file format is forgotten!!


Throttling SQL Server database cpu usage I'm wondering if there is away to limit specific database from using server's resources like setting max CPU and RAM for each database [simpler to IIS App pools]?

I'm working with SQL Server 2014 Enterprise & I want to prevent some databases from slowing down the whole server.

Thanks

Edited for clarity and consistency:

By the database itself? No. However, you can through the connection (what you should be trying to manage anyways) called the Resource Governor.

CPU
MEMORY
Physical IO, depending on the version.

There are a number of Concepts which the Docs explain in greater detail:

Resource Pool

a group of default, internal (cannot be changed), and custom made groups that have a set value on the amount of CPU, Memory, and Physical IO that a session may consume.

Workload Group

a class of session requests that have  similar characteristics. These are mapped to  The appropriate resource pool and are set at the time of connection settings. Meaning the default database connect is used not the first USE statement or any statement afterwards.

The governor must be activated Or configured.  A governor specific function is run against the connection's  default settings  and map to a workload group.

.  This workload is attached to a specific resource pool where the limitations are enforced on all subsequent sessions that occur out of the connection.

This function is the most important part and you should keep it simple.

For more reading, visit the Docs:

Main Page -Resource Governor

Resource governor Resource pool

Research governor Workload group

Resource governor classifier function  | Docs Microsoft


SSMS is crashing and SSMS Toolbar I'm new in SQL Server and i have installed SQL Server on my system for testing purpose.
Issue 1: I want to convert the name of all the toolbar in menu tab to icon. I have no clue from where does it got changed and i need to change to icon option. I have option to add and/or remove buttons but it doesn't have any icon in it. So, it's confusing without the icon to change it.

Attached are the image 1 and 2

enter image description here

Issue 2: SSMS keep on crashing. If i open any new table and select top 100 rows, it gives me result correct but when i select any row values and copy any values from the table, it crashes and automatically restart.

For second problem, i have followed few links to solve it, but after running the query, if i copy a value from the result grid, it crashes there.

https://social.technet.microsoft.com/Forums/en-US/baaace41-1286-4303-b31f-0ac935452e95/why-is-ssms-1791-crashing?forum=sqltools

If anyone have any suggestion, it will be very useful for me.

It looks like your instance of Management Studio is either corrupt or is in need of repair.

This could be for many reasons, including installing it on an unsupported version of Windows, or an unsupported version of .NET. I have previously seen problems like this when someone has installed Sql Server 2012 on Windows Server 2016 without enabling .NET 3.5 Runtime.

What I recommend you do, is to remove the version of SSMS that comes pre-packed with Sql Server 2012 and install the standalone version instead.

To uninstall your existing version of SSMS, follow the steps below:

Open Programs and Features (type appwiz.cpl in Run/CMD)
Locate Sql Server 2012 and select Uninstall/Change.
Go through the wizard, choosing the Sql Server instance you want to modify.
Untick Management Tools - Basic and Management Tools - Complete.
Click next to skip through all of the remaining steps.
Click finish to uninstall SSMS.

Once you have completely unsintalled the existing version of SSMS, go to Download Sql Server Management Studio (SSMS), then download and install the latest version.

As always, check that your system meets the requirements for SSMS before attempting the install.


Unexpected PostgreSQL restart My PostgreSQL server unexpectedly restarted with such messages:

2017-08-16 03:44:34 GMT LOG:  received fast shutdown request
2017-08-16 03:44:34 GMT LOG:  aborting any active transactions
2017-08-16 03:44:34 GMT FATAL:  terminating connection due to administrator command
2017-08-16 03:44:34 GMT FATAL:  terminating connection due to administrator command
2017-08-16 03:44:34 GMT LOG:  autovacuum launcher shutting down
2017-08-16 03:44:34 GMT LOG:  shutting down
2017-08-16 03:44:34 GMT LOG:  database system is shut down
2017-08-16 03:46:04 GMT LOG:  incomplete startup packet
2017-08-16 03:46:04 GMT LOG:  database system was shut down at 2017-08-16 03:44:34 GMT
2017-08-16 03:46:04 GMT LOG:  MultiXact member wraparound protections are now enabled
2017-08-16 03:46:04 GMT LOG:  database system is ready to accept connections
2017-08-16 03:46:04 GMT LOG:  autovacuum launcher started

what could be the reason?

Configuration details:

OS: Ubuntu 16.04.1 LTS (Windows Azure VM)
PostgreSQL version: 9.5.8
listen address: * (my bad, but Azure have to block another ports from external access.
                   I didn't enable PG 5432 port for external access)
HBA config:
local   all             postgres                                ident
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             10.0.0.0/24             md5
host    replication     app_replicator 10.0.0.5/32              md5
local   all             all                                     peer

2017-08-16 03:44:34 GMT LOG:  received fast shutdown request

Someone did a pg_ctl -m fast stop then pg_ctl start, either manually or via the Windows service controller (services.msc, net service, ...).

If it wasn't you, it was a scheduled job, automatic update for some tool related to PostgreSQL from some unofficial 3rd party, etc. Because unless your system got hit by the most mystically lucky cosmic ray in history, that's what happened.

There's no way to restart PostgreSQL from within the PostgreSQL SQL environment (a SQL connection via PgAdmin, psql, etc). You could do it by sending a signal from an untrusted procedural language like plpythonu or plperlu if you're connected as the database superuser and these languages are installed. But otherwise it'd be done at the command-shell level with pg_ctl, by signalling the postmaster process, etc.


Why is it necessary to grant access to PostgreSQL sequences? Using PostgreSQL I see that the pg_dump command (and probably other commands) requires access to 'sequences'. I understand these to just be incremental IDs that are necessary for row identification...
Why is it necessary for server roles to be granted access to sequence objects? What are the potential security problems if every role had access to all sequences of accessible schemas?

pg_dump needs to dump the current position of the sequence, so that it can be restored. Otherwise, the restored sequence would start at 1 (or whatever its start value is) again and would generate duplicate keys.
Granting SELECT on the sequence should be enough for that purpose. Then the user can see the current value, but cannot increase or reset the sequence.


adding pgadmin3 to windows start menu I have installed postgres (different versions actually) as well as pgadmin3 on windows using the package manager:

https://www.openscg.com/bigsql/package-manager/
https://www.openscg.com/bigsql/docs/pgcli/pgcli/

I also added the bin directories into the path environment on windows. Is there any way I can install program shortcuts to the start menu in windows?

Simply right-click over pgadmin.exe and select "Pin to start".


Full Outer Joins Slowing Down Query I am relatively new to SQL, so I apologize if I ask some basic questions.

Our database is setup on Azure, we have automatic indexes turned on. The query I have takes about 6 seconds and gets 2217 rows. As far as I have been able to figure out is that it is my Full Outer Joins that are slowing down my query. I am fine if I can just get it down to 2~3 seconds.

Here is my query:

SELECT j.JobId, j.OrderId, j.OrderType, j.ShopName, j.ShipDate, j.CompletionDate, j.InstallDate, j.DayPriority,
        j.ShipDateConfirmed, j.IsAddon, j.CashOnDelivery, ds.ShopLabel, ds.DesignSetId, dsp.DesignSetProcessId, dsp.DueDate AS DesignSetProcessDueDate,
        dsp.DatetimeStarted, dsp.DatetimeComplete, dsp.BatchNumber, p.Name AS ProcessName, p.ProcessId, p.ZoneId, X.CabinetCount
FROM Jobs j
JOIN DesignSets ds ON j.JobId = ds.JobId
FULL OUTER JOIN Schedule.DesignSetProcessesWithDueDates dsp ON ds.DesignSetId = dsp.DesignSetId
FULL OUTER JOIN Schedule.Processes p ON dsp.ProcessId = p.ProcessId
OUTER APPLY(Select SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount FROM Products pr WHERE ds.DesignSetId = pr.DesignSetId AND
                                        (dsp.BatchNumber = pr.BatchNumber OR dsp.DesignSetId IS NULL)) AS X
WHERE j.Status >= 60 AND j.Status <= 70
ORDER BY j.CompletionDate, j.DayPriority DESC, j.ShopName, j.OrderId, ds.ShopLabel, p.SortOrder, dsp.BatchNumber

This query gets a master view of the jobs and the processes within the job. All the information that I am getting is needed and all the rows are to.

Is there anything that I can use as a substitute for the Full Outer Joins?

If I can just get one of them to go faster I think the query would work better.

If you have any suggestion to how I can speed up the rest of my query I am willing to give it a shot.

EDIT

Here is the link to paste the plan: https://www.brentozar.com/pastetheplan/?id=SytXw1dum

UPDATE

Turns out one of the columns from a view was slowing everything down, so I got rid of dsp.DueDate AS DesignSetProcessDueDate and now it runs in less than a second. I was able to find out how that view was getting the information and put it right into my query.

updating answer to Use LEFT JOINS..

SELECT j.JobId
     , j.OrderId
     , j.OrderType
     , j.ShopName
     , j.ShipDate
     , j.CompletionDate
     , j.InstallDate
     , j.DayPriority
     , j.ShipDateConfirmed
     , j.IsAddon
     , j.CashOnDelivery
     , ds.ShopLabel
     , ds.DesignSetId
     , dsp.DesignSetProcessId
     , dsp.DueDate AS DesignSetProcessDueDate
     , dsp.DatetimeStarted
     , dsp.DatetimeComplete
     , dsp.BatchNumber
     , p.Name AS ProcessName
     , p.ProcessId
     , p.ZoneId
     , (
               SELECT SUM(pr.Quantity * pr.DeliveryCnt) AS CabinetCount
               FROM Products pr
               WHERE ds.DesignSetId = pr.DesignSetId
                     AND (
                            dsp.BatchNumber = pr.BatchNumber
                           -- OR dsp.DesignSetId IS NULL    Note : Since dsp.DesignSetId = ds.DesignSetId this condition can be omitted. uncomment it if necessary.
                         )
            ) AS CabinetCount
FROM Jobs j
JOIN DesignSets ds
   ON j.JobId = ds.JobId
LEFT OUTER JOIN Schedule.DesignSetProcessesWithDueDates dsp
   ON ds.DesignSetId = dsp.DesignSetId
LEFT OUTER JOIN Schedule.Processes p
   ON dsp.ProcessId = p.ProcessId
WHERE j.Status >= 60
      AND j.Status <= 70
ORDER BY j.CompletionDate
       , j.DayPriority DESC
       , j.ShopName
       , j.OrderId
       , ds.ShopLabel
       , p.SortOrder
       , dsp.BatchNumber;


Slow backup and extremely slow restores I don't normally work with MySQL but with MS-SQL and am having issues restoring a dump backup of a 9 GB database. I converted it to MS-SQL and it takes a grand total of 4 minutes to restore but the MySQL DB takes over an hour on the same server. The MySQL database is using InnoDB but is there an alternative to speeding up the restores? Both databases are on the same machine, Windows 2008R2 in a VM with a dymanic SANs.

Correction - it takes MS-SQL 1 minute to restore, 1 hour to restore the same database in MySQL

EDIT: mysql.ini (with commented lines removed):

[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:\Program Files\MySQL\MySQL Server 5.5\"
datadir="C:\ProgramData\MySQL\MySQL Server 5.5\data\"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="CM999-SV510.log"
slow-query-log=0
slow_query_log_file="CM999-SV510-slow.log"
long_query_time=10
log-error="CM999-SV510.err"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=22M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=43M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=124M
innodb_log_file_size=63M
innodb_thread_concurrency=9

Try adding this setting to your configuration:

innodb_flush_log_at_trx_commit=0


Unexpected extremely long query time (~5 minutes using nested WHEN-INs) For the first time, I am running up against a horrifically long execution time of a MySQL query (~5 minutes).

The data in the database is highly (and not arbitrarily) normalized. It very efficient at organizing and shuffling data to be displayed in a lot of very helpful ways for different purposes, except this one particular query is throwing a wrench into it.

I can't understand the reason for it. However, some background information that may shed some light onto otherwise arbitrarily convoluted queries.

The company has divided the world up into many many teams (macroregions). Everybody belongs to one or two teams, based on their expertise.

For example, there are many diverse teams. A few examples are Spanish, Sahara, Iberia, Portuguese, Jungle teams. Each of the teams has considerable overlap with other teams, but is in some senses independent.
The Arabic team works quite closely with the Sahara team, by virtue of the fact that the database tells them they have to work together on certain assignments, because of overlapping geographic locations. The Spanish and the Portuguese team also work closely together, they both work with the Americas and Europe teams and the Portuguese team also works with the Africa team, as does the Arabic team.
Each team has a given set of regions, which are also not unique to that particular team. For example, the Mediterranean region belongs to around 12 teams, and when an event occurs there, they all work on it together.
Each country belongs to one or more regions. Turkey belongs to Central Asia, Europe and even Mediterranean, and a few others.

Given all that, it is necessary to show each individual what other people on their teams are doing, as well as people who are not on their team, but have overlapping regions.

Query 1 accomplishes this perfectly, and very quickly less than .09 seconds.

                SELECT report_name
                FROM reports
                WHERE region IN (
                    SELECT distinct region
                    FROM macroregions
                    WHERE macroregion IN (
                        SELECT distinct macroregion
                        FROM users
                        WHERE callsign = '$thisuser'
                    )
                )

It is also important people can see every occurrence of the report_name, when it Query 1 deems that the individual logged in should know about it. Reports_names are automatically generated and reused for each geographic location

If I am on the East Asia team and someone in my area is working with the Spanish team, there would be two entries in the database:

20120210JOMX01 Japan Okinawa
20120210JOMX01 Mexico Nuevo Leon

Query 2 takes Query 1 and wraps one layer of WHEN-INs around it. It allows the East Asia team members know that someone on their team is working in Mexico. But the performance delay is unacceptable and totally unusable; it takes nearly 5 minutes to complete a single query!

        SELECT *
        FROM reports
        WHERE report_name IN (
            SELECT report_name
            FROM reports
            WHERE region IN (
                SELECT distinct region
                FROM macroregions
                WHERE macroregion IN (
                    SELECT distinct macroregion
                    FROM users
                    WHERE callsign = '$thisuser'
                )
            )
        )

Despite the fact that the query works, it takes too long to be effective. Again, it is startling how long this particular query takes. Whereas nothing else in the interface has given me any sort of performance issue (i.e. they all take less than one second).

What steps can I take to fix this?

First step: get rid of subqueries.  Convert them to proper joins, or co-related subqueries.


Query To Show YTD Total Summing Each Month Prior To Current I need a query to give a YTD total that should sum each month before it.  For example, January YTD would equal January Total, February YTD would equal January Total + February Total, March YTD would equal January Total + February Total + March Total.  My issue is that my query is showing the SUM() of the Total for all months.
This is DDL that illustrates my issue
Create Table empSales
(
    spendType varchar(100)
    ,spendAmt decimal(10,2)
    ,spendMonth varchar(100)
);

Insert Into `empSales` (`spendType`, `spendAmt`, `spendMonth` ) VALUES
('James', '1.00', 'January'),
('Richard', '3.28', 'January'),
('Barb', '4.13', 'January'),
('James', '3.00', 'February'),
('Richard', '3.28', 'February'),
('Barb', '4.13', 'February'),
('James', '2.00', 'March'),
('Richard', '5.28', 'March'),
('Barb', '7.13', 'March');

And this is the query I tried that only gives the overall total instead of just the YTD total I'm after
with MonthAgg as (
select IFNULL(spendType,'Total') spendType
, Sum(Case when spendMonth='January' then spendAmt else null end ) January
, Sum(Case when spendMonth='February' then spendAmt else null end ) February
, Sum(Case when spendMonth='March' then spendAmt else null end ) March
, Sum(Case when spendMonth='April' then spendAmt else null end ) April
, Sum(Case when spendMonth='May' then spendAmt else null end ) May
, Sum(Case when spendMonth='June' then spendAmt else null end ) June
, Sum(Case when spendMonth='July' then spendAmt else null end ) July
, Sum(Case when spendMonth='August' then spendAmt else null end ) August
, Sum(Case when spendMonth='September' then spendAmt else null end ) September
, Sum(Case when spendMonth='October' then spendAmt else null end ) October
, Sum(Case when spendMonth='November' then spendAmt else null end ) November
, Sum(Case when spendMonth='December' then spendAmt else null end ) December
FROM empSales
GROUP BY spendType WITH ROLLUP)
, mycteSum as (
select spendMonth , Sum(spendAmt) Amt
FROM empSales
GROUP BY spendMonth
)
,mycteYTD as (
select spendMonth , sum(Amt) Over(order by Cast(spendMonth+ ' 01, 1900' as date) ) YTD
from mycteSum)
Select * from MonthAgg
UNION ALL
Select 'YTD' as summarySpend,
max(Case when spendMonth='January' then YTD else null end ) JanuaryYTD
, max(Case when spendMonth='February' then YTD else null end ) FebruaryYTD
, max(Case when spendMonth='March' then YTD else null end ) MarchYTD
, max(Case when spendMonth='April' then YTD else null end ) AprilYTD
, max(Case when spendMonth='May' then YTD else null end ) MayYTD
, max(Case when spendMonth='June' then YTD else null end ) JuneYTD
, max(Case when spendMonth='July' then YTD else null end ) JulyYTD
, max(Case when spendMonth='August' then YTD else null end ) AugustYTD
, max(Case when spendMonth='September' then YTD else null end ) SeptemberYTD
, max(Case when spendMonth='October' then YTD else null end ) OctoberYTD
, max(Case when spendMonth='November' then YTD else null end ) NovemberYTD
, max(Case when spendMonth='December' then YTD else null end ) DecemberYTD
from mycteYTD

It is always a bad idea to save Dates Month as rtext mysql really has issues with that
Your approach what very good, but i had a problem to get the right order

with MonthAgg as (
select IFNULL(spendType,'Total') spendType
, Sum(Case when spendMonth='January' then spendAmt else null end ) January
, Sum(Case when spendMonth='February' then spendAmt else null end ) February
, Sum(Case when spendMonth='March' then spendAmt else null end ) March
, Sum(Case when spendMonth='April' then spendAmt else null end ) April
, Sum(Case when spendMonth='May' then spendAmt else null end ) May
, Sum(Case when spendMonth='June' then spendAmt else null end ) June
, Sum(Case when spendMonth='July' then spendAmt else null end ) July
, Sum(Case when spendMonth='August' then spendAmt else null end ) August
, Sum(Case when spendMonth='September' then spendAmt else null end ) September
, Sum(Case when spendMonth='October' then spendAmt else null end ) October
, Sum(Case when spendMonth='November' then spendAmt else null end ) November
, Sum(Case when spendMonth='December' then spendAmt else null end ) December
FROM empSales
GROUP BY spendType WITH ROLLUP)
, mycteSum as (
select DISTINCT spendMonth , Sum(spendAmt) Over(PARTITION by spendMonth  order by MONTH(STR_TO_DATE(CONCAT("01.",spendMonth,".1990"),"%d.%M.%Y"))) Amt
FROM empSales
)
,mycteYTD as (
select spendMonth , sum(Amt) Over( ORDER BY MONTH(STR_TO_DATE(CONCAT("01.",spendMonth,".1990"),"%d.%M.%Y"))
ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) YTD
from mycteSum)
Select * from MonthAgg
UNION ALL
Select 'ytd' as summarySpend,
max(Case when spendMonth='January' then YTD else null end ) JanuaryYTD
, max(Case when spendMonth='February' then YTD else null end ) FebruaryYTD
, max(Case when spendMonth='March' then YTD else null end ) MarchYTD
, max(Case when spendMonth='April' then YTD else null end ) AprilYTD
, max(Case when spendMonth='May' then YTD else null end ) MayYTD
, max(Case when spendMonth='June' then YTD else null end ) JuneYTD
, max(Case when spendMonth='July' then YTD else null end ) JulyYTD
, max(Case when spendMonth='August' then YTD else null end ) AugustYTD
, max(Case when spendMonth='September' then YTD else null end ) SeptemberYTD
, max(Case when spendMonth='October' then YTD else null end ) OctoberYTD
, max(Case when spendMonth='November' then YTD else null end ) NovemberYTD
, max(Case when spendMonth='December' then YTD else null end ) DecemberYTD
from mycteYTD

spendType | January | February | March | April |  May | June | July | August | September | October | November | December
:-------- | ------: | -------: | ----: | ----: | ---: | ---: | ---: | -----: | --------: | ------: | -------: | -------:
Barb      |    4.13 |     4.13 |  7.13 |  null | null | null | null |   null |      null |    null |     null |     null
James     |    1.00 |     3.00 |  2.00 |  null | null | null | null |   null |      null |    null |     null |     null
Richard   |    3.28 |     3.28 |  5.28 |  null | null | null | null |   null |      null |    null |     null |     null
Total     |    8.41 |    10.41 | 14.41 |  null | null | null | null |   null |      null |    null |     null |     null
ytd       |    8.41 |    18.82 | 24.82 |  null | null | null | null |   null |      null |    null |     null |     null

db<>fiddle here


Moving MySQL Cold Backup to Another DB Server I am new to MySQL and want to know the info whether taking a cold backup from source and copying it onto new server with same version require any additional configurations settings to be changed.

As I have gone through few of the tutorials, none of the config changes are required.

Steps I am following

Shutting down source server
copying the backup (/var/lib/mysql).
importing backup to new server (/var/lib/mysql).
Start the new server.

Thanks in advance

You missed three(3) things

On DB1, mysql -uroot -ppassword -e"SET GLOBAL innodb_fast_shutdown = 0"
On DB1, service mysql stop (DB1)
Copying the backup (/var/lib/mysql) on DB1
Copy /etc/my.cnf on DB1 to /etc folder on DB2
Importing DB1 backup to DB2 (/var/lib/mysql)
On DB2, chown -R mysql:mysql /var/lib/mysql
Start mysql on DB2

Step 1 flushes everything InnoDB not yet committed to disk. That makes for a faster mysql startup.

From here, you have the option of changing settings

Give it a Try !!!


Empty LONGTEXT or NULL? In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use LONGTEXT rather than VARCHAR.

When the field is not set or left empty would you say it is better (alias more efficient for the DB) to set it to NULL or to empty string?

I read this: When to use NULL and when to use an empty string? but it talks about empty strings in general (probably small strings and not LONGTEXT).

I was wondering if with LONGTEXT is a different story, whether it saves a lot of space or execution time to use NULL instead of leaving empty LONGTEXT around.

A good starting point to make a decision in order to use or not this kind of value is the MySQL official manual.

In short:

Optimization

  Declare columns to be NOT NULL if possible. It makes SQL operations
  faster, by enabling better use of indexes and eliminating overhead for
  testing whether each value is NULL. You also save some storage space,
  one bit per column. If you really need NULL values in your tables, use
  them. Just avoid the default setting that allows NULL values in every
  column.

Problems with NULL values

  In SQL, the NULL value is never true in comparison to any other value,
  even NULL. An expression that contains NULL always produces a NULL
  value unless otherwise indicated in the documentation for the
  operators and functions involved in the expression

  When reading data with LOAD DATA INFILE, empty or missing columns are
  updated with ''. To load a NULL value into a column, use \N in the
  data file


a filtered index to support a query - is it possible to consider nulls? I have been dealing with this query:

SELECT @amount = sum(accountAmount)
FROM dbo.accountTransaction
WHERE invoiceID = @invoiceID
 AND (notCountedInTotal = 0 OR notCountedInTotal IS NULL)

ans as I have had good experiences with filtered indexes

I can create this one:

 CREATE NONCLUSTERED INDEX F_INVOICEID  ON dbo.accountTransaction (  INVOICEID  )  INCLUDE ( accountAmount)
 where (notCountedInTotal is null)

But that would not solve my query problem above.
So I would like to have the following filtered index:

  CREATE NONCLUSTERED INDEX F_INVOICEID  ON dbo.accountTransaction (  INVOICEID  )  INCLUDE ( accountAmount)
 WHERE (notCountedInTotal IN (null,0))

  Msg 10620, Level 16, State 1, Line 38 Filtered index 'F_INVOICEID'
  cannot be created on table 'dbo.accountTransaction' because the filter
  expression contains a comparison with a literal NULL value. Rewrite
  the comparison to use the IS [NOT] NULL comparison operator to test
  for NULL values.

is it at all possible?

would it be worth creating a computed column, persisted, that would put the zeros and nulls together and create the index on that?

is there a work around for when you want to put an OR inside a filtered index?

would I not get into the same issue as below?

Unable to create a Filtered Index on a Computed Column

The solution I found on this occasion, having a look at the data, constraints, applications etc, I could change the column notCountedInTotal to not null having a default zero, as you can see on the table definition below.

CREATE TABLE [dbo].[accountTransaction] (
[accountTransactionID]     INT              IDENTITY(1,1)   NOT NULL,
[accountCode]              VARCHAR(30)                          NULL,
[transactionType]          CHAR(3)                              NULL,
[transactionReason]        VARCHAR(100)                         NULL,
[applicationID]            INT                                  NULL,
[invoiceID]                INT                                  NULL,
[entityID]                 INT                                  NULL,
[entityType]               CHAR(1)                              NULL,
[transactionCurrencyCode]  CHAR(3)                              NULL,
[transactionAmount]        DECIMAL(10,2)                        NULL,
[accountCurrencyCode]      CHAR(3)                              NULL,
[accountAmount]            DECIMAL(10,2)                        NULL,
[chequeNo]                 VARCHAR(20)                          NULL,
[issuingBank]              VARCHAR(50)                          NULL,
[issuingCountryCode]       CHAR(2)                              NULL,
[STReference]              VARCHAR(30)                          NULL,
[paymentMethod]            VARCHAR(10)                          NULL,
[GPStatus]                 INT                                  NULL,
[transactionDate]          DATETIME                             NULL  CONSTRAINT [DF_accountTransaction_transactionDate] DEFAULT (getdate()),
[transactionStatus]        VARCHAR(10)                          NULL,
[userID]                   INT                                  NULL,
[parentID]                 INT                                  NULL,
[transactionComment]       VARCHAR(max)                         NULL,
[notCountedInTotal]        BIT                              NOT NULL  CONSTRAINT [DF_accountTransaction_notCountedInTotal] DEFAULT ((0)),
[enteredDate]              DATETIME                             NULL  CONSTRAINT [DF_accountTransaction_enteredDate] DEFAULT (getdate()),
[chequeDetails]            VARCHAR(1000)                    NOT NULL  CONSTRAINT [DF_accountTransaction_chequeDetails] DEFAULT (''),
CONSTRAINT   [PK_accountTransaction]  PRIMARY KEY CLUSTERED    ([accountTransactionID] asc) WITH FILLFACTOR = 100)

After that it was easy to add a filtered index because there is no more the either the value is null or the value is zero thing.

execution plan using the newly created index:


SSIS Data Transfer vs. Linked Server I'm currently designing a SSIS package, which will do following:

Clear unwanted records in destination
Do ETL using stored procedures and put everything into a table prepared for export in source
Inserts data from source to destination

So the final step is pretty much to transfer whole table data from Source into Destination and I've got two options now:

SSIS Data Flow
Linked Server

Ideally I'd like to use Data Flow task in SSIS, but looking at performance comparison, it looks like Linked Server wins. I'm doing it that way:
In SSIS I do the following

Run a select statement using OLE DB Source
Insert into destination using OLE DB Destination
Takes about 4 seconds when deployed, about 10 seconds on my machine

Using Linked Server I do the following

Insert records into #Table querying remote server
Insert into actual table
Takes about 2-4 seconds

Here's query I'd use:
SELECT *
INTO #Table
FROM RemoteServer.MyDB.dbo.MyTable;

INSERT INTO dbo.MyTable
SELECT *
FROM #Table AS T;

I'm testing with relatively small data set, just about 14k rows (75 columns in total, most of them are FLOATs).
Both servers are under the same network.
I've configured connection managers in SSIS to use Microsoft OLE DB Provider for SQL Server with Packet Size of 32767 as suggested here:
https://gqbi.wordpress.com/2013/06/19/ssis-fastest-data-flow-task-item-for-transferring-data-over-the-network/
Am I doing something wrong, because I'd expect SSIS to run faster than Linked Server?
Is there another way to speed performance up in SSIS package and win against Linked Server?

hi Linked servers in general not best practice.  I generally recommend against them.  To many reasons to explain here....this would be come a white paper.  hahahah  I have been doing ETL and data security for 25 years.  please consult an architect and don't ask people on internet claiming to be architect.  there are very few people out there with my level of experience.  there is never one answer either...it depends on many factors how you approach this. AND testing small amount of records is not valid test.  do more intense testing.   best of luck!


How to detach a sql server 2008 database that is not in database list? I installed sql server 2008 on windows 7 then i create a Database! after 2 days, i reinstall the windows and install the sql server! now i am trying to attach my database file, but i encounter to bellow error. i think that the files are like a attached file and i can't attach them. what is difference of an attached file and a not attached file? how can i to attach this file? Please Help Me.

Error Text:

  TITLE: Microsoft SQL Server Management Studio

  Attach database failed for Server 'AMIR-PC'. (Microsoft.SqlServer.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Unable to open the physical file "F:\Company.mdf". Operating system
  error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

I'm pretty sure the problem is that due to the uninstall/reinstall of SQL Server, the database engine no longer has permissions on the database files.

If you move the .mdf and .ldf files to the data folder that you specified on install, it should work for you. If you want to keep the files where they are, make sure the permissions are configured to allow appropriate access to the database engine service account group.


Equivalent of Oracle RR date format in SQL Server Having a bunch of SQL insert scripts generated in Oracle, I have the requirement to adjust them to be usable with T-SQL in Microsoft SQL Server.

The Oracle export has date formats like this:

to_date('01.02.28','DD.MM.RR')

The RR means that values between 0 and 49 assume the current century, whereas 50 to 99 asume the previous century.

I currently see no SQL Server equivalent to that notation.

The only solution I can think about is to write a quick script that does some Regex and/or string operations to detect the numeric values and add the century by myself so that I could use YYYY for SQL Server.

My question:

Is there a way to express RR in T-SQL?

My question:

  Is there a way to express RR in T-SQL?

It can be done, for example, as

-- from date to string
SELECT FORMAT(datetime_field, CASE WHEN YEAR(datetimefield) > 49
                                   THEN 'yyyy-MM-dd'
                                   ELSE 'yy-MM-dd'
                                   END) AS date_formatted_as_RR, ...

-- from string to date
SELECT DATEFROMPARTS(CASE WHEN CAST(SUBSTRING(string_field,7,2) AS INT)>49
                          THEN CAST(SUBSTRING(string_field,7,2) AS INT)+2000
                          ELSE CAST(SUBSTRING(string_field,7,2) AS INT)+2100
                          END,
                     CAST(SUBSTRING(string_field,4,2) AS INT),
                     CAST(SUBSTRING(string_field,1,2) AS INT)) AS date_interpreted_as_RR, ...


What could be causing this rogue CPU spike on MS SQL Server running on EC2? We've just had a rogue CPU spike on one of our main SQL boxes, and we're struggling to work out what caused it. We use Red Gate SQL Monitor to track various performance metrics, and we saw a couple of interesting performance monitor things around around the time of this spike, but they all appear to have been caused by the CPU usage spike - so the originating cause of the spike is a complete mystery.

All other metrics - I/O performance, network, memory usage - were completely normal during this spike, and restarting the MSSQLSERVER process on the box appears to have cleared it.

We've seen spikes like this before, but historically they have always coincided with a sudden increase in website traffic or somebody running a particularly intensive SQL maintenance job or similar; this one is a complete outlier and we've never seen anything like it. The box in question is running:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4297.0 (X64)
Nov 22 2013 17:24:14
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

and it's hosted on an i2.2xlarge instance on Amazon EC2. We're looking into a number of things that could have caused it, but one potential explanation would be CPU throttling or contention on the underlying hosting infrastructure. Has anyone encountered similar issues with either SQL Server, or specifically with SQL hosted on EC2 instances?

EDIT: We'd provisionally dismissed memory consumption as a potential cause of this, because we're monitoring both SQL Server's total memory usage, and the amount of 'free' memory being reported by MSSQL itself within SQL Monitor. Memory usage metrics for the 24-hour period surrounding the spike are here; there's no sign of anything changing or growing in the period immediately preceding the spike. Does this data shed any further light on the matter?

For a start, be aware that memory usage (if based on the simple used memory counter, which it likely is) will probably be always static - and likely (for a SQL Server that has been operating for a little while) be the same as the max memory configured for the instance (SQL Server will grab all available memory and won't release it).

It's worth just checking my guess here - is the memory used in you monitoring tool the same as the configured max server memory (it might be lower after a reboot and slowly increase to the max, dependent on usage)

The stats in your screenshot indicate memory pressure (or possibly slow I/O, but likely memory). The CPU spike isn't exactly the problem here, just a symptom of a problem.

A sudden drop in page life expectancy (the length of time a page stays in memory)
A rise in latch wait time (lots of things trying to modify the same page)
The CPU spike (thrashing pages rapidly in and out of memory because the buffer isn't large enough to hold all pages that are needed).

Seeing these counters, I'd definitely think memory pressure. The reason why can be trickier to diagnose, but the other stat on your screenshot indicates something. All of these indicators corresponded with a spike in user connections to the database, I know you mention that their was nothing unusual about site traffic, something/s were connecting to your db and doing something - a lot of things, it seems.

The reboot cleared it because it cut all of those connections. Unless you have a history somewhere else showing more in depth information about connections, you're not likely to be able to tell (from SQL server side) what was connecting and doing what - it might be worth investigating any connected apps (looking for a process or something going loopy - but I wouldn't spend too long doing so). I doubt you'll be able to ascertain with any amount of certainty exactly what caused it this time.

For the future, if this occurs again, I'd recommend seeing what is connected (and doing what) before you bounce the box (although I know sometimes the pressure is just to get things working again, but taking a quick look at connections doesn't take long).

There's a number of ways to do this but a good start would be Adam Machanic's sp_WhoIsActive (Info, Download, Brent Ozar talking about it)

Edit: A bit more
For some more performance monitoring stuff I like Brent Ozar's PerfMon best practices (It's an older post, Sir, but it checks out...). It has a nice video.

Alternate explanation
Yes, it's always possible the cause is infrastructure issues, and you can't be sure on that without separate hardware monitoring information (and even then you can't really be sure...). However, the spike in user connections corresponding to the other metrics behavior really does point to memory pressure due to a spike in activity (although, never assume it's only one problem).


How can we drop a constraint without losing the automatically created index? How can we drop a constraint without losing the automatically created index?

If it's possible using a single query.

Use KEEP INDEX.

drop table demo purge;
create table demo (id number, constraint demo_pk primary key (id));
alter table demo drop constraint demo_pk keep index;
select index_name from user_indexes where table_name = 'DEMO';

INDEX_NAME
------------------------------
DEMO_PK


How to handle removing ASP.NET users where their UserID is tied to rows that should be maintained even after a user is deleted? I have a website that uses ASP.NET membership and so there are multiple aspnet_... tables in our database.

We are creating a service for the users that allow them to essentially create laboratory testing orders for an employer. An employer can have multiple users that can order tests.

If someone effectively loses access to the service (they get fired, quit, retire, or get their permissions revoked by their employer) the laboratory testing orders that they created should remain in the system and so should the user's user name. This way when someone views a list of all the laboratory orders, they know what user ordered what testing and for who.

Right now, when a user loses access, we "physically" remove their user data row from the aspnet_users table and the aspnet_membership table. This removes their username, email, profile, password etc... We can't do this if we create a relationship between the aspnet_users table and the table that holds the laboratory orders. If we do, we either need to:

cascade the deletion (which will remove the orders that a user created)
set the value in the table that holds the laboratory orders to either "NULL" or some sort of default value. If we do that, we lose all of the user's information, such as their username etc... which we want to maintain.

If we opt to change to a "soft-delete" approach and just mark a user as not being allowed to log in (not approved), but keeping their user row in the aspnet_users and aspnet_membership` tables, is there any issue that might arise from this that I can't see? Security issues? Performance issues?

I generally think leaving user data in a system when the user cannot access the system is just... wrong. It leaves a bad taste in my mouth.

What is the best way to handle storing data like this that must be "persisted" even if the "parent" row is removed? Should I not use a relationship and just copy the user data to another table? If I do that there's a chance for inconsistent data and updates would be difficult. I can easily see it growing into a monster and impossible to maintain. There's also the chance of conflicts if a username is reused in the future.

Any advice would be very much welcomed.

What I recommend is separating out the concern of "Who are people we've heard of?" from the concern of "Who can get into the system now and how do they do that?"

What this means in practice is that you should not refer directly to your aspnet_users table to note who did what and when, etc.

Instead, you should have a Person table into which you dump every new user once you add them to the system.  Your transactions should point at this Person table instead of your aspnet_users table.

Now, if you think it's important for any reason, you can have the Person table point to the aspnet_users table.  You can also do things like keep a status column on the Person table to note current status, etc.

However, when you no longer permit access to a given user, you should take them out of the ASP.NET access control tables (and update Person, if appropriate).  Doing this will not interfere with the values in your transaction tables.


SQL Preserve the Order in group by I have records like below in my table.

I want distinct records and when I do group by it loses the Order. I want to maintain the order. I have written below query to get the desired however its not working:

 select route_id,fixcode,fixdescription from
 route_fixcodes group by route_id,fixcode,fixdescription
 having route_id = 12345 Order by fixcode

I want Result like below:

Table DDL:

CREATE TABLE [dbo].[route_fixcodes](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [route_id] [int] NOT NULL,
  [fixcode] [varchar](4) NOT NULL,
  [fixdescription] [varchar](32) NOT NULL,
  CONSTRAINT [PK__route_fi__3213E83FD7609D27] PRIMARY KEY CLUSTERED ([id] ASC)
)

If you do not specify an order by for the result set of your first query then your data is not guaranteed to be ordered.

It does appear that you want to order by the minimal id field in your table based on PRIMARY KEY CLUSTERED ( [id] ASC).

One way would be by using a CTE and MIN(id)

WITH CTE
AS
(
SELECT route_id,fixcode,fixdescription, MIN(id) as minid
FROM route_fixcodes
WHERE route_id = 995063
GROUP BY route_id,fixcode,fixdescription
)
SELECT route_id,fixcode,fixdescription
FROM CTE
Order by minid;

Test data

CREATE TABLE #route_fixcodes( [id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL ,route_id int,fixcode int,fixdescription nvarchar(255));

INSERT INTO #route_fixcodes(route_id,fixcode,fixdescription)
VALUES(995063,100,'Issue_Observed'),(995063,100,'Issue_Observed'),(995063,137,'Swap Altice One Pack')
,(995063,137,'Swap Altice One Pack'),(995063,247,'Defective CPE Equip.'),(995063,247,'Defective CPE Equip.')
,(995063,112,'outside coax repair'),(995063,112,'outside coax repair')

Result

route_id    fixcode fixdescription
995063  100 Issue_Observed
995063  137 Swap Altice One Pack
995063  247 Defective CPE Equip.
995063  112 outside coax repair


differences between SSRS 2016 and Report Builder 2016 I would like to know the differences between SSRS 2016 and Report Builder 2016
which one can be use for implementing AD security

SQL Server Reporting Services (SSRS) is a server-based platform for hosting reports. Report builder is client tool that may be used to create reports to deploy to SSRS. As such, you would use SSRS to manage security on for the reports that you may have deployed with Report Builder.


Cleaning up transaction replication del/ins/upd stored procedures? I'm referring to the ones that start with:

dbo.sp_MSdel...
dbo.sp_MSins...
dbo.sp_MSupd...

I've noticed they are not removed when I run exec sp_dropsubscription on the Publisher then run sp_removedbreplication on the subscriber (on the database).  I also tried sp_subscription_cleanup to no avail.

Do these have to be deleted manually if I want them removed?

Subscriber is SQL 2000
Publisher is SQL 2008 (Distribution database is on the Publisher)

When you remove a subscription nothing is done to the subscriber.  You'll need to remove these procedures manually.  They are safe to remove is replication has been removed.


Convincing my DBA there's a sporadic issue with the SQL Server How do I convince my DBA there's a sporadic issue with the SQL Server?

The issue: basically, major slow down... Some queries, generating execution plans, even generating scripts of existing DB objects can sometimes take forever. Even operations that should have no performance issue, like generating the create table script, can sometimes take forever.

The situation - I have a SQL Server database on which I can create tables / views / etc..

The DB is hosted on a SQL Server (MS SQL Server 2016) managed by the company's IT.

I've asked him to look into it, and I believe he's been earnest in checking logs and he says the load on the server at times I noticed massive performance issues showed nothing out of the ordinary. In fact, he said it was all under 5% (very low).

I have the exact same code and queries running in staging and development environments, it's fine there - it's even super speedy relative to the production environment.

I'm at a loss at what could be the issue to be honest. And I don't know where to start.

Start with Tara Kizer's post on Getting Help with a Slow SQL Server. (Disclaimer: it's on my company's blog.)

She walks you through:

Getting your server's wait stats since startup with sp_BlitzFirst @SinceStartup = 1
Getting server health data in Markdown format so you can copy/paste it into your Stack question with sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1


Cannot get rid of creation of mysql.log I have these settings in my.cnf:

[mysqld]
...
disable_log_bin
general-log      = false
general_log_file = /var/log/mysql/mysqld.general.log

long_query_time     = 1
slow_query_log      = false
slow_query_log_file = /var/log/mysql/mysqld.slow.query.log

[safe_mysqld]
log-error = /var/log/mysql/mysqld.log

[mysqld_multi]
...
log        = /var/log/mysql/mysqld_multi.log

Each time I start the MySQL server an empty file /var/log/mysql.log is created and it never gets filled.
I was searching in web for some solution but I don't find anything regarding the mysql.log as it seems to be outdated.

Can it be it's created by a mistake, again, as I don't find anything regarding mysql.log for MySQL 8.x or do you know how to avoid the creation?

I am running MySQL 8.0.20

I posted it in the MySQL bug database and got an answer:

Adding log-error = /var/log/mysql/mysqld.log in the [mysqld] section in the file my.cnf creates mysql.log in the directory I want to have.

However, I still don't understand why it remains empty when the above setting isn't mentioned.


How should I represent a midnight end time? I have a table which represents time ranges.

   Column    |            Type             |                                Modifiers
-------------+-----------------------------+--------------------------------------------------------------------------
 id          | bigint                      | not null default nextval('exploded_recurring_sessions_id_seq'::regclass)
 schedule_id | bigint                      |
 start_time  | time without time zone      |
 end_time    | time without time zone      |

I'll then do a query such as this:

select from my_table where localtime >= start_time and localtime < end_time;

The business logic here makes sense in the case where end_time is midnight, because I consider a range to be inclusive of start_time and not inclusive of end_time.

Of course, this query doesn't actually work, because localtime will never been < end_time when end_time is 00:00:00.

There are two solutions to this I can think of.

instead of 00:00:00, use '00:00:00'::time without time zone - interval '1 microsecond' for midnight. The likelihood of this every causing an incorrect calculation is probably low enough to never happen. And even if it does happen a handful of times per year, the consequences are essentially nonexistent, at least as my system now stands. However, it's an ugly solution, and using wrong data like this might have other consequences down the line.
select from my_table where localtime >= start_time and (localtime < end_time or end_time == '00:00:00'). This seems pretty straightforward, but it makes it necessary to make one or more special indexes. Not a problem if it's the only solution, but feels like a drawback worth avoiding if possible.

Is there any other way to go about this? Some sort of special datetime function which accommodates this calculation? A better way to represent the data?

Introduction

You might want to consider that 23:59:59.999 belongs to the current day. 00:00:00.000 is considered to be the transition from one day to the next. Let me explain...

Reference: Midnight (Wikepedia).

24 Hour Notation

Depending on whether you are using the 24 hour notation or the 12 hour am/pm notation the following bits of information might be equally interesting.

Midday / Lunch

Midday is not an issue with the 24 hour notation because the step from 11:59:59.999to 12:00:00.000 and onward to 12:00:00.001 have no impact on the time format.

Midnight

Looking at the times around midnight you will find that 23:59:59.999 is still the same day, whereas 00:00:00.001 belongs to the next day. Following this logic the static value 00:00:00.000 should not belong to either day. It is the point of transition.

However, some RDBMS systems will switch the date when adding 0.001 s to 2018-03-07 23:59:59.999.

12 hour notation

Midday / Lunch

Midday in 12 hour notation is neither am nor pm, even though clocks will tend to add a PM as soon as the clock jumps from 11:59:59.999 to 12:00:00.000. Midday should actually have a single m added when static, seeing that a.m. is defined as ante-meridiem which translates to before midday, and p.m. which is post-merdiem translates to after midday.

Midnight

This is equally valid for the 12 hour notation around midnight. 11:59:59.999 p.m is near midnight. 12:00:00.001 a.m. is just after midnight. 12:00:00 a.m./p.m. is midnight and is exactly 12 hours after midday or before the new midday.

However, most systems decide to add an a.m. as soon as the clock reaches midnight at 12 o'clock.

Examples

PostgreSQL

Adding a millisecond to a timestamp value.

Input:

postgres=# select TIMESTAMP '2018-03-07 23:59:59.999' + interval '1 ms';

Output:

 2018-03-08 00:00:00

SQL Server

Adding a millisecond to a datetime value.

Input:

select dateadd(ms,001,cast('2018-03-07 23:59:59.999' as datetime))

Output:

2018-03-08 00:00:00.000

Recommendations

As you can see with the examples, both tested RDBMS systems determine 00:00:00.000 to be the time (after or roundabout) midnight but for the next day.

If you aren't using dates together with the time, then avoid using 00:00:00.000 as the notation for midnight following 23:59:59.999 (or the 11:59:59.999 p.m.)the same day. This corresponds with your solution number 1.
If you are using dates together with the times, then find out which notation your RDBMS supports when adding a microsecond to either 2018-03-07 11:59:59.999 or 2018-03-07 11:59:59.999 p.m.

Full Reference List

How should “midnight on…” be interpreted? (English Language & Usage)
When is it AM and when is it PM? (Arstechnica)
12 am - 00.00 (WordReference.com)
Is midnight 12 a.m. or 12 p.m.? (FAQ - Time) (National Physical Laboratory [U.K.])
A.M. and P.M. (Greenwichmeantime.com)
Midnight (Wikepedia)
9.9. Date/Time Functions and Operators (PostgreSQL)
8.5. Date/Time Types (PostgreSQL)
DATEADD (Microsoft Docs)


Print Parameters in Dynamic SQL I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement.

EG:

Declare @SQL nvarchar(max), @Params nvarchar(max), @DebugMode bit, @Foobar int
select @DebugMode=1,@Foobar=364556423

set @SQL='Select @Foobar'
set @Params=N'@Foobar int'

if @DebugMode=1 print @SQL
exec sp_executeSQL @SQL,@Params
    ,@Foobar=@Foobar

The print results of the above code are simply "Select @Foobar". Is there any way to dynamically print the values & variable names of the sql being executed? Or when doing the print, replace parameters with their actual values so the SQL is re-runnable?

I have played with creating a function or two to accomplish something similar, but with data type conversions, pattern matching truncation issues, and non-dynamic solutions. I'm curious how other developers solve this issue without manually printing each and every variable manually.

One way to get this done is probably something you have already done, and that is to replace your line:

if @DebugMode=1 print @SQL

with

if @DebugMode=1 print @SQL + ' ' + convert(nvarchar(max), @Foobar)

And you would have to do it this way for all your variables, you will need to convert them manually to avoid conversion errors.

You could also use RAISERROR in a similar fashion:

if @DebugMode=1 RAISERROR (N'We used a value of %d for @Foobar', 10, 1, @Foobar)

HTH


Why is SQLite adding unused disk space to "free-list" after deleting a lot of data in a database? The SQLite FAQ states the following:

  (12) I deleted a lot of data but the database file did not get any
  smaller. Is this a bug?

  No. When you delete information from an SQLite database, the unused
  disk space is added to an internal "free-list" and is reused the next
  time you insert data. The disk space is not lost. But neither is it
  returned to the operating system.

Can you explain to me the motives behind that decision (as opposite to returning free space to the OS immediately)? What are the pros and cons of such decision (including non-obvious ones)?

As Phil wrote, freeing pages from the free-list would require that pages are moved around (and that all references to those pages are adjusted).

When data is deleted, it is very likely that some other data will be inserted afterwards, and that data can then just use the pages in the free-list. Returning those pages to the OS only to allocate new ones afterwards would be inefficient, and will increase fragmentation (both of the database file on disk, and of the objects in the database file). But if you really want SQLite to do that, you can enable it with PRAGMA auto_vacuum.

In practice, deleting lots of data usually doesn't happen during normal operation, and if you know that you will not insert new data after deleting, you can run the VACUUM command manually.


How to group by and count with mongodb? I am trying to group by and count with mongodb. Whatever I tried I couldn't do it.
This is my query

db.getCollection("requests").find(
    {
        "updatedAt" : {
            "$gt" : ISODate("2020-05-14")
        },
        "status" : "SUGGESTED"
    },
    {
        "user.email" : 1.0,
        "user.name" : 1.0,
        "updatedBy.displayName" : 1.0,
        "requestTerm" : 1.0
    }
)

I would like to group and count based on updatedBy.displayName

So result will be for me distinct displayname ( Column1), count ( number of times )

How can I achieve this?

This is what I tried and it seems it doesn't work

db.requests.aggregate(
    {$match: {         "updatedAt" : {
            "$gt" : ISODate("2020-05-14")
        },
        "status" : "SUGGESTED" }},
    {$group: {updatedBy: "$updatedBy.displayName", number: {$sum: 1}}}
)

According to the mongo manual on $group, the command should be like this.

db.requests.aggregate(
    [
        {$match: {
                "updatedAt" : {"$gt" : ISODate("2020-05-14")},
                "status" : "SUGGESTED"
        }},
        {$group: {
            _id: {updatedBy: "$updatedBy.displayName"},
            number: {$sum: 1}
        }}
    ]
)


Running SQL Query on all databases I have one hosting account using cPanel and phpmyadmin.

I have 50 databases under this account, all WordPress.

I need this query modified so that it runs through all databases to update the password.

UPDATE 'wp_users' SET 'user_pass' = MD5('somepassword') WHERE 'user_login' ='admin' LIMIT 1;

Hoping for a solution that can target all databases instead of having to go through them one by one.

Thank you

I can't say anything about cPanel and phpmyadmin but In general i can do it by writing a simple script.

I have written a shell script for you

#!/bin/bash

# mysql credential
user="root"
pass="root"

# list of all databases
all_dbs="$(mysql -u $user -p$pass -Bse 'show databases')"

for db in $all_dbs
     do
        if test $db != "information_schema"
            then if test $db != "mysql"
            then mysql -u$user -p$pass $db -sN -e "UPDATE wp_users SET user_pass = MD5('somepassword') WHERE user_login ='admin' LIMIT 1;"
        fi
    fi
     done


Backing up a 400-500 GB database on MySQL/Amazon RDS? We have an Amazon RDS small instance for a MySQL datawarehousing database.
Database size will be around 400-500 GB very shortly.
What is the best way to backup our database?

Can it be done efficiently with automatic backups and DB snapshots available with RDS?

Or should we consider some other options? What would those be?
Using EC2, hosting in some other cloud, or using any other database?

RDS provides automated backups as a feature.  Those backups are system automated (persisting through the life of the RDS instance), or user initiated (persisting outside the life of the RDS instance).  They also automate binlog backup for point-in-time restores.  But you can't take those backups out of the RDS service, as it's all hooked together for automating restores, etc.

FAQ: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/RDSFAQ.Backup.html

I assume you want a backup outside of RDS.  mysqldump would be the only way to do it, and unless you use some creative ETL to facilitate some sort of daily delta, you might save time.  Otherwise, that's a good chunk of data to backup on a regular basis.