Automatically Execute Stored Procedure After Any RESTORE DATABASE Event Is it possible to have SQL Server 2008 R2 Standard automatically execute a stored procedure in any given database that is restored or attachced to the instance?

I've got close to a solution by creating a server level trigger that executes a stored procedure in a given database after the DDL event CREATE_DATABASE or ALTER_DATABASE is fired. Unfortunately, this does not work for databack backup restores.

To elaborate, we have a 'clean up' stored procedure that exists in every database that we restore and I'm looking for a way to have this get executed automatically whenever a backup is restored to the instance.

Googling has pointed me to configuring either Audits or Policies in SQL Server to get this functionality, but these these features are quite overwhelming at first glance, so I can't tell if Audits or Polices are the avenue to start investigating.

Does it have to execute immediately after the restore finishes, or can it be briefly delayed? One idea I had is to have a job that runs every minute, and checks for the Audit Backup/Restore Event in the default trace.

DECLARE @fn VARCHAR(MAX);

SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1)
  + CHAR(92) + 'Log.trc'
FROM sys.traces
WHERE is_default = 1;

SELECT
    DatabaseName,
    StartTime,
    TextData
FROM sys.fn_trace_gettable(@fn, DEFAULT)
WHERE EventClass = 115
AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore

You could store the cleanups you've already done based on StartTime, and even limit the trace query that runs every minute (or whatever your acceptable delay threshold is) to only look at StartTime values greater than the last row you pulled, or the last time the job ran, whichever is less.


Best practice for testing database disaster recovery This is regarding database disaster recovery testing. The production server has 20+ databases. I'm developing procedures for regular testing of disaster recovery and am wondering what is the best practice, testing ALL databases or is one sufficient?

If one database successfully restored, it certainly does not mean that the next database has no problems. (E.g. I once, many years ago now, had some disk array problems that resulted in about 20% of the databases on a server manifesting corruption.)

I run a weekly process that RESTOREs and runs DBCC CHECKDB on all databases from over 70 servers. This is done one database at a time and for me takes most of a week. For fully logged databases we also test the latest full backup by restoring all the logs as well.

I have a dedicated machine for doing this, so that there is no conflict with anything happening on the standard landscape of servers.

The truth is that most everything works fine, but occasionally there is a corruption and that is followed as quickly as possible with a resolution.

Statistically you can test fewer databases and feel pretty safe, but checking them all gives you a quicker heads up.


Error during upgrade : Oracle 11g to 12c While upgrading catupgrade.sql, we are getting below error.

  Due to errors found during the upgrade process, the post
  upgrade actions in catuppst.sql have not been automatically run.

  * THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE *

  Evaluate the errors found in the upgrade logs
  and determine the proper action.

  Execute the post upgrade script as described in Chapter 3
  of the Database Upgrade Guide.

  REASON:
  catuppst.sql unable to run in Database: EluxTest Id: 0

          ERRORS FOUND: during upgrade CATCTL ERROR COUNT=6

  Identifier CATALOG 20-01-27 02:20:04 Script = c1102000.sql
  ERROR = [ORA-12801: error signaled in parallel query server P000 ORA-01115: IO error reading block from file  (block # )

  ORA-01115: IO error reading block from file 2 (block # 215616)

  ORA-27072: File I/O error

  Additional information: 4

  Additional information: 215616

  Additional information: 212992

  ORA-06512: at line 8
  ]
  STATEMENT = [BEGIN
   EXECUTE IMMEDIATE
    'create unique index WRH$_EVENT_HISTOGRAM_PK on WRH$_EVENT_HISTOGRAM
      (dbid, snap_id, instance_number, event_id, wait_time_milli, con_dbid)
     local nologging parallel
     tablespace sysaux';
  EXCEPTION
   WHEN OTHERS THEN IF (SQLCODE = -942) THEN NULL; ELSE RAISE; END IF;
  END;]

Tried various solutions mentioned below posted online in various forums, but nothing worked.

Increased SGA to 6GB
Altered Data file sysaux01 to AUTOEXTEND OFF
Altered datafile to ONLINE

The real error is:

ORA-12801: error signaled in parallel query server P000 ORA-01115: IO error reading block from file (block # )
ORA-01115: IO error reading block from file 2 (block # 215616)
ORA-27072: File I/O error
Additional information: 4
Additional information: 215616
Additional information: 212992

The "solutions" you tried make no sense when you get something like this.

Fix the storage/filesystem first.


SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically  is:

  SQL Error: ORA-12991: column is reference in a multi-column constraint
  Cause: an attempt was made to drop a column referenced by some constraints
  Action: Drop all constraints referencing the dropped column or specify CASCADE CONSTRAINTS in statement.

So i have gone ahead and searched for all the constraints that referenced the column by doing something like:

  SELECT * FROM USER_TAB_COLS
  WHERE COLUMN_NAME = 'My_Column_Name'

After finding and removing all the referenced constraints on the column that i want to drop I tried to drop the column again but i am still getting the same issue. Before posting the question i did take some time to research before this and have read the potential of this being a "Supplemental Logging" issue. Again i am very new to oracle DB programming as such could someone please shed some light on this and maybe suggest how and why i can not drop the column after having temporarily removed the constraints that reference the column i want to drop? Thanks in advance!

PS: Using Cascade Constraint Personally i don't think will fix the issue and i also do not want to remove all constraints without knowing which of them are being removed. So this is not a viable solution for me.

Setup:

create table t3 (c1 number, c2 number);
alter table t3 add  supplemental log group sl1 (c1, c2) always;
alter table t3 add constraint t3_uk unique (c1, c2);
insert into t3 values (1, 2);
commit;

Try to drop the column:

SQL> alter table t3 drop column c2;
alter table t3 drop column c2
                           *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

So far so good, goes as expected. Drop the constraint:

SQL> alter table t3 drop constraint t3_uk;

Table altered.

SQL> alter table t3 drop column c2;
alter table t3 drop column c2
                           *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

Still doesn't work, same error. Check the supplemental log group:

select * from user_log_groups where table_name = 'T3'

OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE      ALWAYS      GENERATED
----- -------------- ---------- ------------------- ----------- --------------
BP    SL1            T3         USER LOG GROUP      ALWAYS      USER NAME

Drop the supplemental log group, then try dropping the column again:

SQL> alter table t3 drop supplemental log group sl1;

Table altered.

SQL> alter table t3 drop column c2;

Table altered.


Using an index for both uniqueness and fast lookup? Right now I have a postgresql 8.4 database set up for players in a multiplayer game. I want the username column to be unique. I also want to be able to lookup a player by username quickly. Here's the output of \d players:

                            Table "public.players"
   Column   |  Type   |                      Modifiers
------------+---------+------------------------------------------------------
 id         | bigint  | not null default nextval('players_id_seq'::regclass)
 username   | text    | not null
 trophies   | integer |
Indexes:
    "pk_id" PRIMARY KEY, btree (id)
    "players_username_key" UNIQUE, btree (username)
    "players_trophies_index" btree (trophies DESC NULLS LAST)
    "players_username_index" btree (username)

I'm not a DBA, so bear with me. This seems like an inefficient use of disk space by having two indices on the username column: one for uniqueness and one for fast lookup. Is it possible to combine them into one index, maintaining uniqueness and fast lookup? If so, are there any drawbacks to such an approach?

You only need a single index, the unique one. Queries to lookup by username will use the unique index. There's no advantage to having a second index on the same column marked as non-unique.

You can verify that this by performing an EXPLAIN on the query. Connect to you database and compare the results of the following before and after you drop the index and confirm that they are the same:

EXPLAIN VERBOSE
SELECT *
FROM public.players
WHERE username = 'foobar'

In both cases it should show something like:

Index scan using using players_username_key on public.players (cost=...[truncated])
  Output: id, username, trophies ...


SQL Server Authentication vs Windows Authentication Good Afternoon,

I've been working on a problem where connecting to a remote default SQL instance (using the server's IP address) via Windows Authentication has been taking a lot longer than SQL Authentication.

Originally I thought that the problem was down to reverse DNS lookups but now I am not too sure. There was a record in the forward lookup zone for the server so connections using the server's name were quick. Once a record for the server was entered into the reverse lookup zone, the connections using the server's IP address were much faster.

So the issue appears to be fixed but I'm not so sure. Why wouldn't SQL authenticated connections have a similar problem? Do SQL authenticated connections not resolve against DNS?

Does anyone have any links to documentation on the internals of the two types of connections? I've been doing some research but have come up short.

Any links or advice would most appreciated.

Andrew

For Windows Authentication "SQL Server validates the account name and password using the Windows principal token in the operating system."

For SQL Server Authentication "logins are created in SQL Server that are not based on Windows user accounts. Both the user name and the password are created by using SQL Server and stored in SQL Server."

So SQL Server authenticated connections do not use DNS.

See this topic "Choose an Authentication Mode" in MSDN: http://msdn.microsoft.com/en-us/library/ms144284.aspx

EDIT: Even with SQL Server Authentication the initial connection to the SQL Server requires resolution of the SQL Server's name. It is, however, also possible to connect to the SQL Server using only the IP Address as you noted above.

See SQL Server name formats at:
http://msdn.microsoft.com/en-us/library/ms188642.aspx


SQL 2008 permissions - cumulative or not? I have a SQL 2008 production database which gets replicated using log shipping to a second, completely different SQL 2008 server. There is also another copy which is restored at irregular intervals to a third SQL instance, again on a completely separate server.
The primary instance of the database is the backbone of our case management system and as such, all rights to insert/update data are consigned by the CMS system itself using a series of permission groups. The secondary (log shipped) instance is used for reporting so that intensive queries aren’t kicking the daylights out of the CMS backend. The tertiary instance is used for development and testing of new code.
I don’t want my team of developers to be able to do anything on the Primary instance than have read-only access to the tables, and no ability to manipulate or execute stored procedures. I do however want them to be able to execute stored procedures on the Secondary instance (which is read-only anyway) and to have full access to the Tertiary instance.
If I only had to worry about individual user rights on the three instances, it would be easy, but there’s a snag: as well as having individual user accounts on all three SQL instances, there is also an Active Directory group which contains my team members but also contains a couple of other user accounts that DO need full access to the production database. That AD group also has permissions on MSDB to enable manipulation of the SQL agent.
So my question is this: if I have the team members set up with explicit per-user-per-database access on the Primary instance, will those permission sets take priority over any rights that might be conferred by membership of the AD group?  The log shipping happens every 20 minutes throughout the day so I don’t want to set something up that necessitates having to change the permissions on the Secondary instance after every log-shipping occurrence.
Just to add a further complication, there is an additional database on the Primary instance that I DO want my team to have full control of. That additional database doesn’t get log-shipped though and is only restored to the Tertiary instance when I decide the need has arisen.

I don't know how much you already know about the user and permission system in SQL Server. It is possible that you need to do some studying in order to fit all pieces together, to your liking.
Restore (which includes log shipping) gives you a binary copy of the database. This includes all users in the database with the assigned database level (and lower) permissions. Nothing you can do about this. Except for running post-restore scripts, but that seems undesirable and also impossible since it means breaking log shipping.
Then you have the instance level. This means logins. The only privilege at the instance level that "trickle down" to the database level is sysadmin (including CONTROL SERVER). This allow somebody to do everything, including SELECT, INSERT ect on all tables in all databases. There are of course other server level roles and server level assignable permissions, but those are more "special things", and as far as I can see won't affect traditional object level privileges.
Permissions are cumulative. You have the sum of all privileges. But that excludes any DENY. DENY overrides all types of GRANT. (There is an exception with column level DENY, but my guess is that it won't affect you.) Well, except for sysadmin, DENY isn't checked for those who are sysadmin.
I've tried to follow your description to be more to the point, but I'm afraid I get a bit lost in the details. Perhaps above can get you started and you can get back with specifications or more precise questions?


Is it possible to have something like Union-FS for Oracle databases? I wonder if it is possible to have something like what Union-FS is for filesystems also for databases?
Usecase
You have a webapp (with Oracle as database in the backend) with several instances: One production app and several work areas in which developers can make their changes and push them to production.
The 'workarea-webapp' should "see" all data stored in the production DB but if one of the developers changes data or inserts/deletes data (stored in the db) this should not affect the production db.
For files we use Union-FS which works fine but I wonder how to provide something like this for Oracle databases (Standard Licence)?

You might be able to construct something like this using database links and views, but performance of those views in the development environment would likely be horrendous. That said, in general it is considered an extremely poor security practice to have production data be visible to non-production application instances, or to share any infrastructure (servers, network, storage) with them. In most computing environments it is specifically prohibited by best practice, if not by law, depending on the type of data being stored.

In most of the places I have worked, according to the rules developers were not even allowed read-access to production data for any reason; no links between dev and production were allowed; and no copies of production data could be placed in dev environments. Any exceptions required senior management and security approval from multiple organizations and generally meant that dev environments wound up secured to the same level as production.


PostgreSQL extension installation, best practices I have a SQL only postgres extension I want to make public and share on GitHub, but I want to make sure the installation process is clear to users.

I've just been copying them to the correct directory, but is there a best practice I should follow?  Like providing a makefile and documenting that as the correct way of installing -- with an aside to point out that actually for this extension at present it's acceptable to just copy the SQL and control files

The extension in it's early state is visible here

Thanks

Edited to include link to GitHub extension

I can immediately think of two extensions which have nothing to compile, only files to be copied.  I would look to their Makefiles for inspiration.

https://github.com/glynastill/table_log_pl/blob/master/Makefile

https://api.pgxn.org/src/first_last/first_last-0.1.1/Makefile


Sybase internal tables for ORM generation I am working on a Perl application that uses DBIx::Class::Schema::Loader in order to generate the ORM layer of the application, against a Sybase ASE 15.5 database.

We recently migrated from Sybase 12.1, where the ORM generation worked fine, to our current database, where things have gone screwy.  Our ORM layer doesn't change much, so this wasn't immediately apparent.

The driver for accessing the database information uses the syscomments, sysobjects, syscolumns, and systypes tables, but I can't find any reference to them in the ASE 15.5 system table documentation.

Searching through the documentation has led me to conjecture that these tables were retired sometime around 15.1, but I don't have enough expertise to be able to comment either way.  Does anyone know where this information is stored in 15.5?

Those tables are still in ASE 15.5

syscolumns

syscomments

sysobjects

systypes


mysql finding field population of multiple columns the efficient way? I have a large table in mysql with 150 columns (not my design)

and Im working on finding the population of each column (not empty string)

I have a table

email | fname | lname | ... | ... |
aaa   | bbb   | ccc   | rrr | qqq |
bbb   | eee   | ''    | ddd | bbb |
...   | ...   | ''    | ccc | eee |

desire result (format does not matter actually)

fields | # population     | % population
email  | 1501203          | 100
fname  | 1450000          | 95
lname  | ...              | ....
...

my code so far

SELECT 'email' as col1  ,COUNT(NULLIF( Email, '' )) as '#Populated'
                        , (COUNT(NULLIF( Email, '' ))  * 100/ (Select count(*)
from mytable)) as '% pupolation'
FROM mytable
union
SELECT 'fname'...
*repeat code above 150 times...*

is there a better way to do this?

i dont necessarily need to list the column names in 1 column, it just looks nicer

(COUNT(NULLIF( Email, '' )) --> COUNT(Email)

COUNT(col) is the same as COUNT(*) except for ignoring rows when col is NULL.

So..

SELECT
    COUNT(Email)              AS '#Email',
    COUNT(Email)*100/COUNT(*) AS '%Email',
    COUNT(fname)              AS '#fname',
    COUNT(fname)*100/COUNT(*) AS '%fname',
    ...

would give you 300 columns.

For 150 rows:

SELECT
    'Email'                   AS 'Column',
    COUNT(Email)              AS '# Populated',
    COUNT(Email)*100/COUNT(*) AS '% Populated'
    FROM tbl
UNION ALL
SELECT
    'fname',
    COUNT(fname),
    COUNT(fname)*100/COUNT(*)
    FROM tbl
UNION ALL
...

Notes:

The former uses one pass over the data; the latter needs 150 passes.
If you are using the commandline mysql, use \G to make the display more readable.
UNION defaults to UNION DISTINCT, which makes an extra pass over the result to de-dup.  UNION ALL skips that; it is unnecessary in your case.
The AS is needed on only the first SELECT of a UNION.  (If present on others, it is ignored.)

If you want to turn NULL into 0, be more efficient by doing the test outside the count:

    IFNULL(COUNT(Email), 0)              AS '# Populated',
    IFNULL(COUNT(Email), 0)*100/COUNT(*) AS '% Populated'

Other counting techniques:

SUM(Email != '')        -- excludes both NULLs and empty strings
SUM(Email IS NOT NULL)  -- excludes NULLs  (Same as COUNT(Email))
SUM(Email IS NULL)      -- includes only NULLs

A boolean expression evaluates to 0 or 1 (or NULL); SUM then adds up the ones.


Why is it specifically Immediate If (IIF)? I am quite new to MS Access (and databases in generally) but I have noticed that IF() statements are actually written using the IIF() function.

A quick search online told me that it stands for Immediate If, I assume because it is evaluated immediately.

Support.Office.com tells me that IIF() has the same functionality I am used to from IF() in other programs and languages, so my question is why is it IIF() and not IF()?

I am unable to find this answer anywhere.

Is there a "delayed" IF (Or any other kind of IF) that justifies the need for Immediate IF? If so, what are the possible use cases for the other if?

If there is not a different IF, why is the IIF() function not just called IF()?

Within Access, IIf ( expr , truepart , falsepart ) is a function with three parameters.

Also available in Access is VBA (Visual Basic for Applications) and it has the usual If ... Then ... ElseIf ... End If conditional execution blocks, but in that case If and the other terms are all keywords of the Basic language.

The functional form IIF() can be used within SQL statements or as part of single executable statements within certain object properties, e.g. Control Source properties of form controls.  In both of those cases, a complete multi-line If construct (i.e. a full VBA code block) is NOT allowed.  Without the functional form, there would be no way to execute conditional expressions in such contexts.

One reason for the different names is to avoid the conflict of having a function with the same name as a keyword.  It is interesting to note that Excel also supports VBA, but it still has a spreadsheet function named IF().  Although some of the reasons are perhaps just convention (style) decided by the original application designers, there are likely other more subtle reasons.  For instance, Access is more likely to have mixed expressions in different contexts (as mentioned earlier), so it is important to avoid conflicts with VBA keywords.  On the contrary, it is not possible to execute Excel "spreadsheet commands" in any other context outside spreadsheet cells.  Although it is possible to execute properly-defined VBA functions from an Excel cell, one cannot execute VBA code directly from within the spreadsheet commands.  In other words, the execution contexts are completely separate in Excel, so there is never a conflict between function names and language keywords.

In the case of Access, the concept of "immediate" or "delayed" versions of If are not relevant.  Some languages (Java, C++, C#, etc.) support short-circuited logical operators (like && -- logical and) which only execute a 2nd statement when logically necessary.  But neither the IIF() function nor the VBA If statement support that behavior.  All expressions and/or function calls that are "passed" to either form will be fully evaluated/executed.  In some contexts, like within an SQL statement, such expressions might evaluate to NULL and so the overall expression might also be NULL and effectively ignored, but ignoring the result of the expression is not the same as skipping the execution of individual statements.


Should I write to MySQL slave (replica) for reporting? I intent to put reporting/analytics database on slave and run job scheduler every night to aggregate and insert data from operational database to reporting database.

Should I do this or I have to setup dedicated server for reporting/analytics database and use some tools to aggregate and insert data from slave to reporting server?

Thank for help,

I would do the nightly aggregation into a Summary Table on the Master.  This abides by the principle that the Master and Slave are always in sync (within a slight delay).  It also allows for multiple slaves to share the read load.


Limit connection numbers with pgbouncer So I have this pgbouncer config:

[databases]
my_db = host=10.10.10.10
my_db_with_conn_limit = host=10.10.10.10 dbname=my_db pool_size=55

max_client_conn = 300
default_pool_size = 65
reserve_pool_size = 5
reserve_pool_timeout = 1

And the goal is to limit the number of connections from a specific app - when the db name is database_with_conn_limit, only 55 connections are allowed.

Basically, the apps are identical, but I want one to be limited in connections and the other one grab as many as it wants.

Is this a correct setup?
Or should I specify pool_size for my_db, too, giving it all Postgres has minus 55?

You don't have to "specify pool_size for my_db, too".

But your config won't limit the number of connections though. You need to limit max_db_connections for that like here:

[databases]
my_db = host=10.10.10.10
my_db_with_conn_limit = host=10.10.10.10 dbname=my_db pool_size=55  max_db_connections=55

Otherwise you limit the number of sessions in pool, but not the number of connections...

You can also just set different limits per database+user pair instead (which I believe is more common practice).


Service broker internal spid hogging tempdb We've recently decommissioned a rather badly implemented service broker setup after a number of near outages, however while doing my morning checks I've noticed that there are 2 Service Broker internal SPIDs (<50) which are still holding ~75% of TempDB space.

Now, I know these are service broker tasks because the CMD column on SysProcesses shows 'BRKR EVENT HNDLR' and 'BRKR TASK', while the last wait types are 'BROKER_EVENTHANDLER' and 'SLEEP_TASK' respectively.  Both have 'BACKGROUND' as a status, 0 open trans and a last batch time of over a month ago.

Currently the service broker implementation has been fully disabled (from a code perspective), the queues are disabled (is_activation_enabled and is_receive_enabled = 0), there is nothing in the transmission_queue, conversation_endpoints or actual queues, but the database in question does still have is_broker_enabled = 1 (as do TempDB and MSDB).

The server is currently running SP2 with a security fix (11.0.5343.0) and the TempDB usage appears to persist even after a cluster failover.  We've not tried restarting as this is a production system, but I would have thought that a failover would have had the same effect.

Has anyone encountered this issue previously and if so, what did you do to resolve it?

Thanks,

As you can see on this answer, have a look at which databases have service broker enabled (on both target and source servers)

you can use the following query to check which databases have the broker enabled:

--=====================================================================
-- checking what we have and where we point to
--=====================================================================
SELECT @@SERVERNAME
-- my_target_server

USE [master]
GO
SELECT   [name]
 ,[is_broker_enabled]
 ,[service_broker_guid]
FROM [sys].[databases]
WHERE 1=1
  AND is_broker_enabled = 1
ORDER BY  NAME
GO

SELECT name,is_broker_enabled,service_broker_guid,
is_db_chaining_on,  is_trustworthy_on FROM sys.databases
    order by 2 desc, 3 desc,  1

I see you mention the security update and patching - well done for applying it!

Depending on the environment we sometimes use db chaining ownership on, and some times trustworthy on, instead of module signing which is the best way in most cases.

anyway, if you are not using the broker, please switch of all of these features for each database, i.e. disable the broker, and switch it off trustworthy and db ownership chain.

--one of my examples of enabling some of these features and renewing the broker id:

  ALTER DATABASE ORCASTG SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
  ALTER DATABASE ORCASTG SET NEW_BROKER with rollback immediate
  ALTER DATABASE ORCASTG SET trustworthy on with rollback immediate

You want something like these (provided you are not using any of these features):

  USE MASTER
  ALTER DATABASE [APCore] SET DISABLE_BROKER WITH ROLLBACK  IMMEDIATE
  ALTER DATABASE [APCore] SET TRUSTWORTHY OFF WITH ROLLBACK IMMEDIATE
  ALTER DATABASE [APCore] SET DB_CHAINING OFF WITH ROLLBACK IMMEDIATE

and keep monitoring the tempdb usage, I don't think you need neither to restart the service, nor do a failover.

In any case if you are on alwaysOn - Availability Group you may need to remove the database from the availability group before you can disable the broker.


Creating stored procedure in MySQL-8 I am trying to create a SP which will run batch update on about 100 million rows in a table. I created the below SP for it:

delimiter //

CREATE PROCEDURE `bulk_update`(IN table_name varchar(30))
BEGIN
DECLARE rows INT;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET rows = 1;
WHILE rows > 0
do
SET autocommit=1;
SET @query =CONCAT('update ',table_name,' set is_summarized="N";');
PREPARE update_stmt FROM @query;
EXECUTE update_stmt;
SET rows = row_count();
select sleep(1);
commit;
DEALLOCATE PREPARE update_stmt;
END WHILE;
END //

It gives below error >>

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rows INT;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET rows = 1;
' at line 3

Strange part is that I am able to create the SP in MySQL-5.6, but it fails in MySQL-8 only. Can someone point me to what I am missing? Thank you

Either use autocommit=1 throughout, or use BEGIN and COMMIT.

And, once set, you do not need to set autocommit again.

So, get rid of COMMIT and move the SET out of the loop.

Is some other process changing the value of is_summarized while this proc is sleeping?  I doubt if your technique will catch all edge cases correctly.

Instead of using a declared rows, use a variable:  @rows.  It does not need to be DECLAREd.

Most of the lines of the proc can be pulled out of the loop.  (However, this won't really make any difference.)

As for ROWS -- Most major versions and some minor versions introduce new keywords.  ROWS apparently was added between 5.6 and 8.0.  I avoid some such errors by prefixing any arguments to stored procs and declared variables in stored procs with an underscore.  This convention also helps avoid confusion between variables and column names.


How to get a 2 row output in a single line in mysql? I want to get the single line output from the below query output

select @@hostname,count(*) "RECOMMENDED_PATCHES" from test.PATCHES
union all
select @@hostname,count(*) "PATCH_VERSIONS" from test.PATCH_VERSIONS

output:
ct-test-02 79
ct-test-02 0

expected output:
ct-test-02 79 0

select @@hostname,
(select count(*) from test.PATCHES) "RECOMMENDED_PATCHES",
(select count(*) from test.PATCH_VERSIONS) "PATCH_VERSIONS"


Sql Server Measuring Query Memory Consumption I am optimizing a stored procedure in sql server 2005, and while I know of several ways to measure timing differences, I am not quite sure of a good way to measure memory consumption differences.

How do you measure memory consumption of a stored procedure in sql server 2005? My goal is to be able to convince myself something like "it is about the same as it was" or "it uses X times more memory"

You should be using dynamic management view (sys.dm_exec_query_memory_grants or sys.dm_exec_cached_plans ) to investigate memory consumption .
Example:-

Select * from sys.dm_exec_cached_plans where objtype='Proc';

I suggest you read Understanding SQL server memory grant article


Combining data from multiple sources in one column Let's say I have a tables like

    create table dbo.A
    (
        Id int identity(1,1) not null,
        BId int null,
        CId int null,
        SomeData nvarchar(50),
        constraint [PK_A] primary key clustered ([Id] asc),
        constraint [FK_A_B] foreign key (BId) references [dbo].[B]([Id]),
        constraint [FK_A_C] foreign key (CId) references [dbo].[C]([Id]),
    )
    go

    create table dbo.B
    (
        Id int identity(1,1) not null,
        constraint [PK_B] primary key clustered ([Id] asc)
    )
    go

    create table dbo.C
    (
        Id int identity(1,1) not null,
        BId int null,
        constraint [PK_C] primary key clustered ([Id] asc),
        constraint [FK_C_B] foreign key (BId) references [dbo].[B]([Id]),
    )
    go

I would like to get back data that would have following in row:
A.Id, C.Id, A.SomeData where C.Id could be coming either from A.CId directly or as C.Id if you join A to by C on A.BId = C.BId. So let's say in my tables I have following data:

    B
    1
    2

    C
    1 1
    2 1
    3 2
    4 2

    A
    1 1 3 'test1'

Sample SQL to insert test data:

    SET IDENTITY_INSERT ON
    go

    insert B(Id)
        select 1
                union all
        select 2;

    insert C(Id, BId)
        select 1, 1
            union all
        select 2, 1
            union all
        select 3, 2
            union all
        select 4, 2;

    insert A(Id, BId, CId, SomeData)
        values(1, 1, 3, 'test1');
    go

    SET IDENTITY_INSERT OFF
    go

What I would like to get back is:

    1 1 'test1'
    1 2 'test1'
    1 3 'test1'

It seems you want a UNION:

SELECT id AS aid,  cid,  somedata
FROM a

UNION -- ALL

SELECT a.id,  c.id,  a.somedata
FROM a JOIN c ON a.bid = c.bid ;

It's not clear if you want a UNION DISTINCT or UNION ALL. If it's the first, you could combine the subqueries into one, using OR. That may produce different plan though, which may be less efficient:

SELECT a.id AS aid,  c.id AS cid,  a.somedata
FROM a JOIN c ON a.bid = c.bid
              OR a.cid = c.id ;


Mongodump Backup Resume My database size is approx 1.5 TB and I have approx 2TB free on backup mount point. But after 10 hours my backup fail due to lack of storage and its just 60% complete. Is their any why through which i can resume my backup from the failure point??

mongodump -u admin -p admin -d tex --host xx.xx.xx.xx --authenticationDatabase admin

Do you have any other option then please also share with me.

If you're using the WiredTiger storage engine, data in the database is compressed (using snappy by default). In contrast, mongodump output is not compressed. This is why 2TB may not be enough to backup a 1.5TB database.

To determine the uncompressed size of your database, you can run db.stats().dataSize in the mongo shell (see manual).

To determine the uncompressed collection size, use db.collection.stats().size.

Having said that, there is no built-in way to "resume" a dump. However, you can pass a query into mongodump using --query parameter. If you have some kind of timestamp/ordering field in your collection, you may be able to use this parameter. This could also help you to split up your backup so you don't have to do all 1.5TB at once.

Alternatively, you also can pass --gzip into mongodump as suggested by JJussi to ensure the output is compressed.


Large Sleep counts causing THREADPOOL wait? When I run sp_whoisactive throughout the day, I usually see we have around 500-700 connections on our server. We are occasionally getting THREADPOOL waits and high signal wait ratios (so we are thread starved at times).

However, these 500-700 connections only 10-25 of them are active. I wasn't sure if a sleeping SPID is still bound to a thread while it sleeps?

Could that be causing our high signal wait ratio and THREADPOOL waits?

I have a feeling our 3rd party application is not properly closing out connections and is causing us issues, but wanted to check first before I bring this up with them. Thanks!

What I really want to know is do sleeping sessions hold a thread?

No, they do not,

  A SQL Server worker thread, also known as worker or thread, is a
  logical representation of an operating system thread. When executing
  serial requests, the SQL Server Database Engine will spawn a worker to
  execute the active task.

But instead of actually spawning a new worker, one is typicall available in a pool

  The max worker threads option enables SQL Server to create a pool of
  worker threads to service a larger number of query requests, which
  improves performance.

Thread and Task Architecture Guide -- SQL Server task scheduling

which you can see from running

select count(*)
from sys.dm_os_workers

select count(*)
from sys.dm_exec_sessions

Since you typically have more sessions than workers.


Merge replication parameterised filters We are looking at using merge replication to maintain a 2-way data flow between a central SQL Server 2012 Standard database and, eventually, 100's of international satellite SQL Express 2012 installations.  The central database being the publisher with the SQL Express installations being subscribers.

I understand that I can setup a parameterised filter so that data can be sent specifically to each satellite database - e.g. using the unique ID of each satellite installation.

Is there a maximum number of subscribers that one publication on the SQL Server Standard 2012 can have?  From my investigation I have read some people saying 25 but others saying they have many more than that.  I can find nothing definitive.  Obviously if it is as low as 25 then this solution will not work.

There is no hard limit but you will limited by hardware considering the agents will consume cpu, memory, and i/o. There is also a desktop heap issue that some have run into when running a large number of push subscriptions, ymmv.

I personally have 1 topology I administer which currently has approximately 3,000 pull subscribers.


How to run insert statement as much times as select statement gives unique values? DB Firebird.

I have the following statement

SELECT a.ID FROM CLIENTS a

And I want my INSERT statement into another table to use each of this value, something like

INSERT INTO BANKS (ID, NR, MONEY)
VALUES (
   gen_id(gen_name, 1),
   "there should be client ID from the SELECT statement above",
   3000
)

Can I achieve this with one statement?

To do this you can use insert ... select:

insert into banks(ID, NR, MONEY)
select
   gen_id(gen_name, 1),
   ID,
   3000
from CLIENTS

If you have a trigger on CLIENTS that generates the ID on insert, or - Firebird 3 - ID is an identity column, then you can simplify this to:

insert into banks(NR, MONEY)
select
   ID,
   3000
from CLIENTS


Urgent help with TempDB log file growth mystery I need some urgent guidance for a tempdb issue for past last week:
The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down after 10 or so hours.
I have used various queries listed here but nothing shows up as in that duration there is no long running transaction.
Queries of mix workload keeps coming and going. There is no stuck transaction as such.
The user databases on this instance are all part of Log shipping setup and log backups happen every 15 mins.
In addition i added the Xevent
CREATE EVENT SESSION [tempdb_file_size_changed] ON SERVER ADD EVENT
sqlserver.database_file_size_change(SET collect_database_name=(1)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.is_system,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE ([database_id]=(2))) ADD TARGETpackage0.event_file(SET filename=N'C:\ExtendedEvents\TempDBGrowth.xel',max_file_size=(100),max_rollover_files=(25)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

Nothing gets captured even in XE and its just blank in those 10 hours.
I have no clue what is eating my tempDB log as none of the queries or XE gets me the data. I have never seen this thing and generally get database growth using XE but here i am just clueless.
I am running DBCC SQLPERF(logspace) and i can see log used just keeps increasing by 1 Gb every 5 mins or so. We have TempDB log set to max 200 GB but by 10 hours its almost 160-180 GB and then suddenly comes down.
Running below query almost every time gives ACTIVE TRANSACTION.
SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases d
WHERE database_id = 2;

Please help what i might be doing wrong and why i cant see any transaction using TempDB log file usage?
Update- As requested in answer from @J.D, i checked and verified that there is no database mail or Service broker is being used internally to cause the growth:
However over last week i see the growth in log file of tempdb is zig zag and the trend of that zig zag growth is bit higher:
I need to help understand :-
How is the transaction log growth increasing upto 80% and then lowering down back to 0 and then increasing and growing and so on , with reaching lmoast max 90-95%.
I confirmed there is no manual shrinking going on. Could there be multiple processes contributing as this is OLTP server with tempdb usage throughout the day. Just dont understand how in last week it has grown near its max capacity?
thanks

What you are you seeing is by design for tempdb Transaction Log file. I wish Microsoft had better documentation on this.

How is the transaction log growth increasing upto 80% and then
lowering down back to 0 and then increasing and growing and so on ,
with reaching almost max 90-95%.

From the First reference article:

Tempdb is not recovered in the event of a crash, and so there is no
need to force dirty tempdb pages to disk, except in the case where the
lazywriter process (part of the buffer pool) has to make space for
pages from other databases.
A checkpoint is only done for tempdb when the tempdb log file reaches
70% full – this is to prevent the tempdb log from growing if at all possible (note that a long-running transaction can still
essentially hold the log hostage and prevent it from clearing, just
like in a user database).
Of course, when you issue a manual CHECKPOINT, all the dirty pages
are flushed, but for automatic checkpoints, they’re not.

I suggest you monitor your tempdb Transaction Log usage and set it to a reasonable size that under normal workload it does not have to grow and shrink.
I do not know exactly why your tempdb logfile filled to 100%. There has to be a reason why the log file did not get truncated.
Will indirect checkpoint help?
In most cases no.

Any non-logged “bulk” operation that qualifies for an “eager write” in
tempdb is not a candidate to be flushed by the recovery writer (the
internal thread that runs the indirect checkpoint).
This raises an important question: which data load operation is
minimally logged on tempdb? This is important to know because
minimally logged operations on tempdb will not be flushed by the
indirect checkpoint. The following list can be used to assist you in
understanding which load operations on tempdb will be minimally logged
and which will not.

For details read Tempdb – Here’s a Problem You Didn’t Know You Had by Fabiano Amorim
I noticed this question is for SQL Server 2014, if someone is reading this for SQL Server 2016 read the below documentation by Microsoft.

Database Checkpoints (SQL Server) - check the "Indirect Checkpoint" section.
Change the Target Recovery Time of a Database (SQL Server).
Indirect Checkpoint and tempdb – the good, the bad and the non-yielding scheduler by Parikshit Savjani

Reference:

What does checkpoint do for tempdb? by Paul Randal
See question and answer section Inside the Storage Engine: What’s in the buffer pool? by Paul Randal


Recover raw Databases from corrupted MySQL 8 + Windows Server Implementation I have a (Virtualized/Hyper-V) MySQL 8 (8.0.16.0) DB server whose installation has become corrupt and will no longer start the service, or report any errors as to why it won't start (this is a virtual Windows 2008R2 server).

So I went and created a brand-new, duplicate, VM from scratch, installed (the same version of) MySQL on it, and am now trying to figure out how to get the tables from the old server into the new one.

If I simply copy the entire Data folder over, as is the process described in virtually every article I've found on the subject, the new instance also won't start (exactly like the old one won't) but if I then copy a pristine MySQL.ibd file (from the clean installation) into the migrated Data folder, the service will, once-again, start normally, but none of the copied Databases/Tables will exist in it.

All I want/need to do is get the shiny, new, clean, MySQL to see and incorporate all of the old databases from their original files, but after more than a day of reading many guides on how to do this (from here and elsewhere), I have not been able to find a solution that works.

So here I am, asking again, in 2020, for MySQL 8(.0.16.0), on Windows 2008R2, what exactly do I need to do in order to fully recover these tables into a new server when some elements of the base structure are corrupt/invalid?

Thanks!

Well, after three days of fighting with this, I have found a means of recovering at least some of the data:

With a lot of searching, I was able to locate an old virtual machine in the archives that still had a working copy of MySQL (5.x) that contained a version of one of the databases that I need to recover. From that resource I was able to generate all of the appropriate CREATE statements for all of the associated Schemas and Tables. So then, armed with my fresh server, and a new installation of MySQL, and all of the CREATE statements, I was finally able to regenerate all of the schemas and (empty) tables that this customer uses. So, upon shutting down the MySQL server service, and copying only the specific directories for that customer's databases into the new Data folder, then re-starting the service, the databases were, miraculously, fully restored!

IDK if it matters or not, but all of the schemas this particular customer had were MyISAM tables; I haven't yet tried this technique with any InnoDB tables, but this, at least, is progress, of a sort! Also, I don't know that I'll be able to find legacy functioning VM's for all of the schemas that I need to recover (and that are up to date/accurate!) so there's still a lot of work ahead...

I find it stunning that there's no simple tool for analysing a backed-up raw MySQL Data folder and then integrating the contents thereof onto an active server. You'd think that such a tool would have tremendous value for innumerable people in situations like this!?

Anyway, back into the archives!


One and only one relationship - ER diagram to relational schema I'm taking this term a DB course as part of my CS studies.
This week we began studying the subject of ER diagrams, which I find a bit confusing.
I have some uncertainty about the picture above (Note: the rounded arrows indicate "One and only one" - that is, every subject from B can and must be directed to exactly one subject in A, and vice versa).
I have to create a suitable relational schema for this diagram, and here is my try:

A(a, b, c, d) (b is the key)
B(e, f) (e is the key)

This is my uncertainty:
R(b, e) - no matter which key I'm creating out of those foreign keys, the "one and only one" constraint isn't held.
How can I create the schema where this constraint holds?
What about if you had "at most one" arrows, and not exactly?
How the answer would change?

You have several possibilities, each of them with advantages and disadvantages.
Solution 1
Define a single table:
AB(a b c d e f)

with two candidate keys, b and e. You chose one of them as primary key, and declare the other as unique.
Advantages: All the constraints of the association among the two entities are satisfied.
Disadvantages: If there are other entities related to A or B, then the foreign key for the relation corresponding to this entity can be not “natural” if the primary key chosen for AB is the “other” key.
Solution 2
Define one of the two relations with a not nullable foreign key for the other, for instance add fke (“foreign key for attribute e in B”) to relation A. Then declare that this attribute is unique.
Advantages: it is more “natural” to keep the two entities in separate relations. You keep the constraints that each element of A is related to one and only one element of B (since the attribute fke is not nullable and it is unique in all the relation).
Disadvantages: you lose one constraints: that each element of B is related to some element of A. This constraint must be enforced in the applications developed on the database.
Solution 3
As before, exchanging the role of A and B.
Solution 4
As you have mentioned, with a bridge table R(b,e). Then declare both b and e as unique.
Advantages: you keep the constraint that each element of A can be related only to one element of B, and vice versa.
Disadvantages: you lose the constraints that each element of A is related to some element of B and vice versa. Those constraints must be enforced by the applications. Moreover, you have one more table, and this make the queries more complex.
Solution 5
You put a foreign key in each of the two tables to refer the other table. These foreign keys are also declared unique.
Advantages: you keep the constraint that each element in A is related to one and only one element in B, and vice versa.
Disadvantages: you lose the constraint that if a certain element b1 of B is associated to an element a1 of A, then a1 is associated to b1. In fact this solution allows inconsistences: an element a1 could be associated to b2, a2 could be associated to b1, while at the same time the foreign key for b2 could refer to a2, and the foreign key for b1 could refer to a1. So this constraint should be enforced by the application. Moreover, there is a certain redundancy in the data.

The solutions are listed in order from the more “secure”, to the more “dangerous”, at least in my opinion.
Finally, is worth to note an important point from this case: when translating from the ER model to the Relational Data Model, there are situations in which the translation is not completely satisfactory.
For your last question: what happen if at most one arrow. In this case the second or third solution can be used. And normally, if one arrow is total (i.e. you have exactly one element in the other entity set) while the other is partial, you put the foreign key in the relation for which the relation is total. If both are partial, then you can choose freely the relation in which to put the foreign key.


MariaDB and MySQL versions equivalence Is there any kind of equivalence between MariaDB versions and MySQL versions?
Obviously the 2 DBMS are different, but I was hoping the MariaDB team to "catch up" with MySQL when starting a new major version (like 10.2, 10.3...).
Equivalence in

errors, issues, bugs, ...
syntax,
features and behavior

On this MariaDB page all MariaDB versions are listed. Is there a way to know for each major version which MySQL release is the closest?

MariaDB-10.2.2 InnoDB merged from 5.7.14
MariaDB-10.2.35 Merge new release of InnoDB 5.7.32
Lots of miscellany features have been merged at random times.
MySQL 8.0 and MariaDB 10.5 are quite different.  Still, you may not notice any differences unless you reach into rarely-used features.
The two play leapfrog.  For example.
MariaDB had CTEs and Windowing functions in 10.2, long before they were added in MySQL 8.0.
On the other hand, MySQL led the way with JSON and some functions (eg ST_distance_sphere).
Better links:
https://mariadb.com/kb/en/function-differences-between-mariadb-and-mysql/
https://mariadb.com/kb/en/mariadb-vs-mysql-features/
And there maybe more.  Note: it is in MariaDB's best interest to list the differences; MySQL prefers to mostly ignore the existence of MariaDB.
Errors, issues, bugs -- They look at each other's bug list, then add fixes as they see fit.
Syntax - Not much difference (except for CTEs, see above)
Features and behavior - MariaDB moved toward standards more aggressively than MySQL.  For example, in a subquery with an ORDER BY, the ordering became ignored in MariaDB long before in MySQL.  ONLY_FULL_GROUP_BY is another point of difference.  In both of these examples, the "new" version is "more correct", though incompatible.
The Optimizers have diverged.  I deal with performance a lot, but I would say only a few percent of queries show a noticeable difference between the products.  And the difference could be in favor of either product.
If you are just starting, don't worry about which one you pick; you have a long learning curve before you get into the esoteric things I have listed.


MySQL replica is lagging without resource saturation I am running MySQL 5.6.41 on AWS RDS. I have one multi-AZ master and one multi-AZ read replica. It is a vanilla configuration MySQL configuration, so the binlog is in MIXED format and the replication process on the replica is single threaded.

When I attach a new replica, it is booted with ~1 hour of lag, and it takes 10 hours to catch up. When I turn the binlog format to ROW on the replica, the lag starts increasing indefinitely if sync_binlog=1.

I have tracked this down to the SQL_THREAD in particular on the replica that is struggling to keep up.

I'm trying to work out why replication is so slow, given that no system resources appear to be saturated.

The write IOPS on the master does not exceed 2,500/s during short-lived spikes. The average is more like 470/s. Read IOPS are very low: no more than 100/s. We have a baseline capacity of 2,700/s IOPS because we have a 900GiB General Purpose SSD. See AWS docs on this. I also don't think the disk is the bottleneck because the DiskQueueDepth doesn't exceed 1.

I don't believe the issue is saturating the network because the IO_THREAD on the replica is keeping up with the master.

The CPU and memory usage on the replica seem ok. CPU doesn't go above 8% utilization on an 8 vCPU instance, and it has 8 GiB freeable memory.

Does anyone have any ideas for how I can track this down further?

Single-threading is very likely to be the cause of getting "behind".

A Master can execute multiple transactions at the same time.  But a single-threaded Slave is limited to one transaction at a time.  This is the old, simple, way of doing Replication.

Newer replication techniques are more complex.  Depending on what version of MySQL you are running, multi-threading may be limited to "one thread per database", thereby not involving much extra complexity.

(I, too, need at least A and B, plus RAM size.)

Find some of the slowest DML queries; there may be ways to speed them up.  Also, do you have a lot of Reads on the Replica?  If so, it could be adding to the sluggishness.

Analysis

Observations:

Version: 5.6.41-log
30 GB of RAM
Uptime = 6d 07:36:37
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Increase innodb_io_capacity, currently 200, to 1000.  (Check AWS documentation on this topic.)

Assuming you have SSDs, turn off innodb_flush_neighbors.

Is some other process purging binlogs?  (cf expire_logs_days = 0)

Ignoring errors seems dangerous.  (cf binlog_error_action)

The traffic heavily leaning toward INSERTs; is this normal?  What do the INSERTs look like?  Single-row inserts?  Multi-row?  Other?  We may be able to tweak them to avoid some of the slave lag.

Related to that, please discuss the apparent 275 transactions per second.

Since the main thrust of the Question is about slave lag, let's also approach this from the viewpoint of "slow queries".  Set long_query_time=1 and turn on the Slow Log.  (AWS may have a web page laying out details.)  Do this for both the Master and the slave.  I prefer these ancillary settings, especially for the "slave lag" situation:

log_queries_not_using_indexes = OFF
log_slow_admin_statements = ON
log_slow_slave_statements = ON

Details and other observations:

( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((3078624 + 150107660) ) / 545797 = 280 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size (now 22548578304)?

( Innodb_buffer_pool_pages_flushed ) = 150,107,660 / 545797 = 275 /sec -- Writes (flushes)
-- Increase innodb_buffer_pool_size (now 22548578304)?

( Innodb_os_log_written ) = 318,630,569,472 / 545797 = 583789 /sec -- This is an indicator of how busy InnoDB is.
-- Very busy InnoDB.

( Innodb_log_writes ) = 150,500,027 / 545797 = 275 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 318,630,569,472 / (545797 / 3600) / 2 / 128M = 7.83 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 545,797 / 60 * 128M / 318630569472 = 3.83 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 134217728). (Cannot change in AWS.)

( Innodb_dblwr_writes ) = 6,573,796 / 545797 = 12 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.

( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 4568524 + 150107660 ) / 545797 / 200 = 141.7% -- If > 100%, need more io_capacity.
-- Increase innodb_io_capacity (now 200) if the drives can handle it.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_connections ) = 2,496 -- Maximum number of connections (threads). Impacts various allocations.
-- If max_connections (now 2496) is too high and various memory settings are high, you could run out of RAM.

( character_set_server ) = character_set_server = latin1
-- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.  Caution:  there can be index definition problems with utf8mb4 (but utf8 does not have these problems).

( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue

( (Queries-Questions)/Queries ) = (303268906-1169727)/303268906 = 99.6% -- Fraction of queries that are inside Stored Routines.
-- (Not bad if high; but it impacts the validity of some other conclusions.)

( Created_tmp_disk_tables ) = 570,444 / 545797 = 1 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size (now 16777216) and max_heap_table_size (now 16777216).
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Questions ) = 570,444 / 1169727 = 48.8% -- Pct of queries that needed on-disk tmp table.
-- Better indexes / No blobs / etc.

( Created_tmp_disk_tables / Created_tmp_tables ) = 570,444 / 729780 = 78.2% -- Percent of temp tables that spilled to disk
-- Maybe increase tmp_table_size (now 16777216) and max_heap_table_size (now 16777216); improve indexes; avoid blobs, etc.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (150283122 + 50516 + 10949 + 0) / 150328440 = 1 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 778,039 / 545797 = 1.4 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 778,039 / 1129934 = 68.9% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (150283122 + 10949 + 0 + 0 + 50516 + 0) / 545797 = 275 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( binlog_error_action ) = binlog_error_action = IGNORE_ERROR -- What to do if the binlog cannot be written.
-- IGNORE_ERROR is the default for backwards compatibility, but ABORT_SERVER is recommended.

( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.
(Not relevant if log_bin (now ON) = OFF)

( innodb_autoinc_lock_mode ) = 1 -- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional".
-- Galera desires 2; 2 requires BINLOG_FORMAT=ROW or MIXED

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show.
-- It can be helpful in the slowlog to see writes that could be interfering with Slave reads.

( Aborted_connects / Connections ) = 2,404 / 4995 = 48.1% -- Perhaps a hacker is trying to break in? (Attempts to connect)

( thread_cache_size / Max_used_connections ) = 32 / 17 = 188.2%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

Abnormally small:

(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.00752

Abnormally large:

Binlog_cache_use = 275 /sec
Com_begin = 275 /sec
Com_commit = 275 /sec
Com_commit + Com_rollback = 275 /sec
Com_flush = 12 /HR
Com_insert = 275 /sec
Com_kill = 3 /HR
Com_purge = 12 /HR
Com_show_grants = 0.66 /HR
Com_show_slave_status = 60 /HR
Com_slave_start = 0.0066 /HR
Connection_errors_peer_address = 0.0066 /HR
Handler_commit = 1377 /sec
Handler_commit/Questions = 642
Handler_delete = 330 /sec
Handler_prepare = 1101 /sec
Innodb_buffer_pool_pages_misc = 135,294
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 9.8%
Innodb_buffer_pool_write_requests = 9230 /sec
Innodb_data_fsyncs = 300 /sec
Innodb_data_writes = 563 /sec
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 275 /sec
Innodb_data_written = 9595901 /sec
Innodb_dblwr_pages_written = 275 /sec
Innodb_os_log_fsyncs = 276 /sec
Innodb_os_log_pending_fsyncs = 1
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 1,002.1MB
Innodb_pages_written = 275 /sec
Innodb_rows_deleted = 330 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 669 /sec
Prepared_stmt_count = 4
Ssl_accepts = 1,019
Ssl_finished_accepts = 1,019
Ssl_session_cache_overflows = 340
Ssl_used_session_cache_entries = 128
performance_schema_max_cond_instances = 12,884
performance_schema_max_socket_instances = 5,012
performance_schema_max_thread_instances = 5,092

Abnormal strings:

Slave_running = ON
innodb_fast_shutdown = 1
log_output = TABLE
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
read_only = ON
relay_log_recovery = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
time_zone = UTC


CREATE TABLE in Postgres locks pg_class forever I have a PostgreSQL cluster with few databases. In one of the DBs, if I try to create a new table, the command never ends until I kill the the linux proccess. I checked pg_lock and found that pg_class and the indexes on pg_class are locked, lock type is relation and mode is RowExclusiveLock.
I can add a column to a table but cannot create an index on a table.

If I create a table in another database, there are no locks on pg_class.

Every hint can help.

Here there is description of locks:

relname                             locktype    mode
pg_class_tblspc_relfilenode_index   relation    RowExclusiveLock
pg_class_relname_nsp_index          relation    RowExclusiveLock
pg_class_oid_index                  relation    RowExclusiveLock
pg_class                            relation    RowExclusiveLock

Normal operations don't place a persistent lock on pg_class.

There must be a DDL command (CREATE TABLE, DROP TABLE or similar) that is blocked because of a lock, so that it cannot complete the operation.

Check for active sessions in pg_stat_activity, their query and if they are hanging.

Your CREATE TABLE is not blocked by a table lock, but by a row lock in pg_class.


SQL Server 2008R2 Cluster: Spread tempdb over several files AND shrink original single file On my SQL 2008 R2 active/passive cluster, I've received an error message regarding I/O requests on tempdb.mdf taking longer than 15seconds. Googled that and it as suggested I spread tempdb over several files. I have 2 4-core CPUs and figured I'd spreaad tempdb over 8 files.
The current tempdb.mdf is 8GB in size, most of which is unused space. I figured I'd start out with all 8 files being 512MB with filegrowth set to 64MB increments.

Ive executed the T-SQL to create the extra files and set the original tempdb.mdf's initial size to 512MB. My question is:

If I now rollover the cluster to the other node, will the tempdb.mdf file shrink to 512MB?
And will this allow the SQL server to spread tempdb across all 8 files?

Or must I perform any other operation?

Or - hope this is not the case - have I made a mistake that means the SQL server will crash when I rollover ?

Don't know if anyone will read this, but... I performed the rollover this weekend without problems. All 8 tempdb-files are now 512MB. "Case closed"


A query submitted from different applications has differing DOP I have a query which selects from multiple views and is fairly I/O heavy. If I execute this query using Management Studio it uses parallelism across most of the 16 CPUs and completes in under 10 seconds. However, when executing it from SpotFire (a product from Tibco) it uses only 1 CPU and can take hours to complete.

I am able to replicate the issue in Management Studio if I using the query hint OPTION (MAXDOP 1).

I was thinking that it may be a SpotFire problem, but it uses the Microsoft JDBC driver for connection and I see no connection properties regarding parallelism. I have confirmed using Profiler that the queries from SpotFire and from Management Studio look exactly. the. same. The only difference appears to be parallel execution.

Can anyone offer insight as to why this might occur?

!SOLVED! [kind of]

I reran the SQL Profiler trace and found that the queries are indeed the same, but the way that they are executed is different.

From SpotFire:
declare @p1 int
set @p1=0
declare @p2 int
set @p2=0
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT a, b, c, d FROM MyView',16,8193,@p7 output
select @p1, @p2, @p7

From Management Studio:
SELECT a, b, c, d FROM MyView

I then added two new events to SQL Profiler and reran the queries:
 - Performance / Degree of Parallelism
 - Performance / Showplan All

This showed the the query from SpotFire was using DOP 0 (BinaryData = 0x00000000) and from Management Studio was DOP 12 (BinaryData = 0x1200000). Next, the SHOWPLAN showed me that the SpotFire query was inserting the results from the view into a temporary table before returning the data to the client.

So why does it do this? Possibly because of the sp_cursorprepexec statement. But why should that cause the DOP to drop to 0? I don't know.

I think that the "solution" is going to be working with SpotFire and possibly tuning the JDBC connection string.

Here is an article from MSDN regarding all connection parameters for JDBC.

Here is another article from someone who had the same exact problem I have, also with no resolution.

There are 2 standard ways to affect this

MAXDOP hint per query
max degree of parallelism Option for the whole server

The SQL Server 2008 resource governor and some trace flags will affect it. I doubt Tibco is using the resource governor so it could be a trace flag.

One more option I've seen is SET IMPLICIT TRANSACTIONS ON.

If no trace flags, no implicit transactions, and no MAXDOP hints, then the queries are not the same.

Edit, after question update

You should be able to change some prepare and/or cursor setting. Example: http://www.streamreader.org/serverfault/questions/177391/impact-on-sql-2000-performance-of-jdbc-selectmethodcursor


After doing latest windows10 update 1909 SQL Server Management Studio 17 not working yesterday I have an update for windows version 1909, after updating to the latest version, When I tried to open SSMS it shows me an error like this(please check the screenshot).

Two options:

Install "Microsoft Visual C++ 2015 Redistributable"

From some reason the link that worked yesterday now does not work (thank you Microsoft!). The working link is The latest supported Visual C++ downloads

Upgade to a newer version from SQL Server Management Studio 18.4

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, and build queries and scripts.
Use SSMS to query, design, and manage your databases and data warehouses, wherever they are - on your local computer, or in the cloud.
SSMS is free!

My, personal opinion, choose option2. SSMS 18.4 is available since 4 November 2019 and has (AFAIK) the same functionality compared to SSMS 17.x


Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup? Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup?

There would be two nodes in the AlwaysOn Failover Cluster Instances setup and two Domain Controllers.

We are not using AlwaysOn Availability Groups.

Will CDC work? and will it failover?

Refer to Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server)
From the reference :

Change Data Capture:
Databases enabled for change data capture (CDC) are able to leverage AlwaysOn Availability Groups in order to insure not only that the database remains available in the event of failure, but that changes to the database tables continue to be monitored and deposited in the CDC change tables.
The order in which CDC and AlwaysOn Availability Groups are configured is not important. CDC enabled databases can be added to AlwaysOn Availability Groups, and databases that are members of an AlwaysOn availability group can be enabled for CDC. In both cases, however, CDC configuration is always performed on the current or intended primary replica.
Harvesting Changes for Change Data Capture Without Replication
If CDC is enabled for a database, but replication is not, the capture process used to harvest changes from the log and deposit them in CDC change tables runs at the CDC host as its own SQL Agent job.
In order to resume the harvesting of changes after failover, the stored procedure sp_cdc_add_job must be run at the new primary to create the local capture job.


UDF And Query Plan Caching I have a multi statement table value UDF. That builds a temp table then returns it as the result.

The first time its executed it takes 5 seconds, subsequent executions with the same parameters take 1 second. If I change the parameters it then takes 5 seconds the first time then it goes back to 1 second. If I then change the parameters back to the first ones it still takes one second.

I cant work out what's causing it to take longer the first time a new parameter is used. Do UDF's compile a separate query plan each time different parameters are used? Can I monitor this in Profiler? I can see the sp_Recompile event but I'm not sure what event is fired when a UDF compiles.

Unfortunately I cant post the code but am hoping there is enough information for someone to give me some sort of explanation.

It's unlikely it's related to the query plans.  I've only every seen very complicated queries take more than 1 second for the plan to get created, much less 4 seconds as your describe.

It sounds like it's page caching - basically SQL Server keeps recently used data in memory.  The first run with each set of parameters you are paying the (normally expensive) cost of physical disk access.

Subsequent executions can access the data pages which are already stored in memory without waiting for the disk subsystem.


Oracle: what permission is necessary to "grant create table" to another user? What permission should A have in order to grant B permission to create a table?

logged in as user A, I execute

grant create table to B;

and receive the following error:

SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

It looks like you are attempting to grant permissions one schema to another schema.  Such a thing does not exist in Oracle, but there are workarounds.

The easiest way to make this happen is to grant the CREATE ANY TABLE privilege to B, but there are obvious security concerns with doing so.


sp_cursoropen and parallelism I'm running into a performance problem with a query that I can't seem to get my head around.

I pulled the query out of a cursor definition.

This query takes seconds to execute

SELECT A.JOBTYPE
FROM PRODROUTEJOB A
WHERE ((A.DATAAREAID=N'IW')
AND ((A.CALCTIMEHOURS<>0)
AND (A.JOBTYPE<>3)))
AND EXISTS (SELECT 'X'
FROM PRODROUTE B
WHERE ((B.DATAAREAID=N'IW')
AND (((((B.PRODID=A.PRODID)
AND ((B.PROPERTYID=N'PR1526157') OR (B.PRODID=N'PR1526157')))
AND (B.OPRNUM=A.OPRNUM))
AND (B.OPRPRIORITY=A.OPRPRIORITY))
AND (B.OPRID=N'GRIJZEN')))
AND NOT EXISTS (SELECT 'X'
FROM ADUSHOPFLOORROUTE C
WHERE ((C.DATAAREAID=N'IW')
AND ((((((C.WRKCTRID=A.WRKCTRID)
AND (C.PRODID=B.PRODID))
AND (C.OPRID=B.OPRID))
AND (C.JOBTYPE=A.JOBTYPE))
AND (C.FROMDATE>{TS '1900-01-01 00:00:00.000'}))
AND ((C.TODATE={TS '1900-01-01 00:00:00.000'}))))))
GROUP BY A.JOBTYPE
ORDER BY A.JOBTYPE

The actual execution plan looks like this.

Noticing the server wide setting was set to MaxDOP 1 I tried playing around with maxdop settings.

Adding OPTION (MAXDOP 0) to the query, or changing the server settings results in much better performance and this query plan.

However, the application in question (Dynamics AX) doesn't execute queries like this, it uses cursors.

The actual code captured is this.

declare @p1 int
set @p1=189527589
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS<>0) AND (A.JOBTYPE<>3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

resulting in this execution plan (and unfortunately the same multiple-second execution times).

I've tried several things such as dropping cached plans, adding options in the query inside the cursor definition, ... But none of them seem to get me a parallel plan.

I've also searched google for quite a bit looking for parallelism limitations of cursors but can't seem to find any limitations.

Am I missing something obvious here?

The actual SQL build is SQL Server 2008 (SP1) - 10.0.2573.0 (X64) which i realise is unsupported, but I cannot upgrade this instance as I see fit. I would need to transfer the database to another server and that would mean pulling a fairly large uncompressed backup over a slow WAN.

Trace flag 4199 doesn't make a difference, and neither does OPTION (RECOMPILE).

The cursor properties are:

API | Fast_Forward | Read Only | Global (0)

FAST_FORWARD cursors do not support parallelism (though the server generating the plan would need to be 2012 or above to get NonParallelPlanReason as part of the showplan XML).

When you specify FAST_FORWARD, the optimizer chooses between STATIC and DYNAMIC for you.

The provided execution plan shows the optimizer choosing a static-like plan.
Because the query contains aggregation, I doubt a dynamic cursor plan is even possible here. Nevertheless, requesting a FAST_FORWARD cursor type is preventing a parallel plan.

You should change the cursor type explicitly to either STATIC or KEYSET, for example. Both these cursor types can use parallelism.

That said, because this is an API cursor, changing the type of cursor would likely require an application change. Naturally, you would need to benchmark performance to check that changing the cursor type really is the best option for you.


"In Use" memory on a SQL Server box I have a SQL Server 2012 database server with 64GB of RAM. A process called DataPumpService runs on the same box and loads around 400 million records from 2700 flat files.

I want to understand what process is consuming all the physical memory on the box, so I go to the Resource Manager (under Task Manager):

As you can see from the screenshot, the In Use memory is around 61GB, but the Committed Bytes and Private Bytes for the two most memory-intensive processes are around 3.3GB.

How can I find out what process contributes to such high In Use memory on this server?

Thanks.

It's SQL Server that's consuming all of this memory.  You aren't seeing the memory it's consuming because SQL Server is probably using "locked pages" which are not part of the server's working set, which is often all you'll see in Task Manager.

There's a detailed description on why this is on one of the MSDN Blogs.


Is it worth making a separate DATE table or not? If I have a table like this:

my_table
=======================
id    date    some_data

Does it make any sense to pull the date out to it's own table? The date field would use the date data type. So it would now look like this:

my_table
==========================
id    date_id    some_data

dates
==========
id    date

According to MySQL, date data type = 3 bytes. The date_id would need to be a max of MEDIUMINT (3 bytes) in order to get the same result. A normal INT or BIGINT exceeds the storage requirements of a DATE type.

A downside to a separate table is that the queries would be more complicated because I am joining another table.

Are there any other ramifications that need to be taken into account as to whether or not to split the date field into it's own table? My gut reaction is that it's not worth doing unless you are dealing with a small set of dates (small enough to use TINYINT or SMALLINT).

Seems to me that is a classic example of over-thinking your design.

Keep the dates in the same table so they can be easily indexed with other columns if necessary, and not take up 3 times as many bytes, and not make life a living hell for future developers.


How to step through linked servers after failing to connect to one I would like to change my stored procedure so it continues executing after failing to connect to a linked server. How could I write this stored procedure better?

I am using TRY...CATCH to query servers that I am creating from a list to track transaction log backup history.

ALTER PROC [dbo].[spLogBackup]

AS

TRUNCATE TABLE dbo.tLogBackup

DECLARE servers_cursor CURSOR
FOR
select  distinct LinkedserverName
from dbo.Environment
join master..sysservers on srvname COLLATE DATABASE_DEFAULT  =
LinkedserverName COLLATE DATABASE_DEFAULT
where LinkedServerName not in ('TestDB')

and ServerUse in ('PROD', 'DR')
and IncludeInstats = 1
order by LinkedServerName

OPEN servers_cursor
DECLARE @Servername varchar(250)

DECLARE @sql varchar(8000)

FETCH NEXT FROM servers_cursor INTO @ServerName
WHILE (@@FETCH_STATUS = 0)
BEGIN TRY
--Looping throught the servers.
set @sql = 'insert into dbo.tLogBackup (servername, rundate, runtime,
jobname, Status, CurrentStatus, enabled) select ''' + @ServerName +
''',last_run_date, last_run_time, name, last_run_outcome,
 current_execution_status, enabled
 from openquery([' + @ServerName + '],''exec
 msdb.dbo.sp_get_composite_job_info'' )'
 print @ServerName
exec (@sql)
--SELECT (@sql)
FETCH NEXT FROM servers_cursor INTO @ServerName
END TRY

BEGIN CATCH
SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

CLOSE servers_cursor
DEALLOCATE servers_cursor

You just need to add the BEGIN/END for your WHILE LOOP and move the FETCH NEXT outside of the TRY/CATCH

ALTER PROC [dbo].[spLogBackup]

AS

TRUNCATE TABLE dbo.tLogBackup

DECLARE servers_cursor CURSOR
FOR
select  distinct LinkedserverName
from dbo.Environment
join master..sysservers on srvname COLLATE DATABASE_DEFAULT  =
LinkedserverName COLLATE DATABASE_DEFAULT
where LinkedServerName not in ('TestDB')

and ServerUse in ('PROD', 'DR')
and IncludeInstats = 1
order by LinkedServerName

OPEN servers_cursor
DECLARE @Servername varchar(250)

DECLARE @sql varchar(8000)

FETCH NEXT FROM servers_cursor INTO @ServerName
WHILE (@@FETCH_STATUS = 0)
BEGIN

BEGIN TRY
--Looping throught the servers.
set @sql = 'insert into dbo.tLogBackup (servername, rundate, runtime,
jobname, Status, CurrentStatus, enabled) select ''' + @ServerName +
''',last_run_date, last_run_time, name, last_run_outcome,
 current_execution_status, enabled
 from openquery([' + @ServerName + '],''exec
 msdb.dbo.sp_get_composite_job_info'' )'
 print @ServerName
exec (@sql)
--SELECT (@sql)

END TRY

BEGIN CATCH
SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

FETCH NEXT FROM servers_cursor INTO @ServerName

END

CLOSE servers_cursor
DEALLOCATE servers_cursor


Is there a tool like Microsoft's "SQL Server Profiler" for MySQL? While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler.

For the MySQL folk who have not seen Microsoft's SQL Profiler, here is a screenshot

At my previous job we had a tool that trumped SQL Profiler and even gave us stack traces

Does anyone know of any tools like the ones I mentioned that works with MySQL.

(FYI, I can get the Altiris Profiler to work with MySQL but it will involve running Windows furthermore its not really a Symantec sku so licensing is really tricky)

MySQL has never come up with Query Profiling. Now that MySQL is being grandfathered by Oracle, I know this will continue to be the case.

Yet, all hope is not lost.

Since 2007, Percona has come up with some absolutely marvelous tools for everything a Developer and DBA would want, including Query Profiling.

Percona's first set of tools, known as MAATKIT, created a realm for the serious user of MySQL. It features many things, such as:

Query Profiling
Replication Heartbeat
Replication Slave Management
Table Checksum and Synchronization

Percona has recently forked MAATKIT into a more up-to-date set of tools, known today as Percona Toolkit. These tools picked up where MAATKIT left off by expanding the realm of activity for the serious MySQL user to include things such:

Foreign Key Error Checking
Online Schema Changing
Visual Explain Plans
and more ...

Getting back to the original question, the tools out there for query profiling are

pt-query-advisor
pt-query-digest
mk-query-profiler (for MAATKIT users)
mk-query-digest (for MAATKIT users)

Here is an example of the kind of rich information that can come from using one of these tools:

I helped a client implement mk-query-digest to report the 20 worst-performing queries every 20 minutes. I got the idea from this YouTube video. The client would move any bad query's output to memcached thus lowering the incidence of the query's taking a toll on the the database.

Here is the script I made to call mk-query-digest (examing the processlist only)

#!/bin/sh

RUNFILE=/tmp/QueriesAreBeingDigested.txt
if [ -f ${RUNFILE} ] ; then exit ; fi

MKDQ=/usr/local/sbin/mk-query-digest
RUNTIME=${1}
COPIES_TO_KEEP=${2}
DBVIP=${3}

WHICH=/usr/bin/which
DATE=`${WHICH} date`
ECHO=`${WHICH} echo`
HEAD=`${WHICH} head`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
SED=`${WHICH} sed`
CAT=`${WHICH} cat`
WC=`${WHICH} wc`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
LS=`${WHICH} ls | ${TAIL} -1 | ${AWK} '{print $1}'`

HAS_THE_DBVIP=`/sbin/ip addr show | grep "scope global secondary" | grep -c "${DBVIP}"`
if [ ${HAS_THE_DBVIP} -eq 1 ] ; then exit ; fi

DT=`${DATE} +"%Y%m%d_%H%M%S"`
UNIQUETAG=`${ECHO} ${SSH_CLIENT}_${SSH_CONNECTION}_${DT} | ${SED} 's/\./ /g' | ${SED} 's/ //g'`

cd /root/QueryDigest
OUTFILE=QP_${DT}.txt
HOSTADDR=${DBVIP}
${MKDQ} --processlist h=${HOSTADDR},u=queryprofiler,p=queryprofiler --run-time=${RUNTIME} > ${OUTFILE}

#
# Rotate out Old Copies
#

QPFILES=QPFiles.txt
QPFILES2ZAP=QPFiles2Zap.txt
${LS} QP_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9].txt > ${QPFILES}

LINECOUNT=`${WC} -l < ${QPFILES}`
if [ ${LINECOUNT} -gt ${COPIES_TO_KEEP} ]
then
        (( DIFF = LINECOUNT - COPIES_TO_KEEP ))
        ${HEAD} -${DIFF} < ${QPFILES} > ${QPFILES2ZAP}
        for QPFILETOZAP in `${CAT} ${QPFILES2ZAP}`
        do
                ${RM} ${QPFILETOZAP}
        done
fi

rm -f ${QPFILES2ZAP}
rm -f ${QPFILES}
rm -f ${RUNFILE}

Here is the user I made to connect to mysql using mk-query-digest

GRANT PROCESS ON *.* TO 'queryprofiler'@'%' IDENTIFIED BY 'queryprofiler';

Here is the crontab I ran every 20 minutes (less 10 seconds) keeping the last 144 copies (which is 48 hours of profiling)

*/20 * * * * /root/QueryDigest/ExecQueryDigest.sh 1190s 144 10.1.1.8

The incredible part: The output of mk-query-digest

Here is a profile that ran 2011-12-28 11:20:00 for 1190 sec (20 min less 10 sec)

The last 22 lines

# Rank Query ID           Response time    Calls   R/Call     Item
# ==== ================== ================ ======= ========== ====
#    1 0x5E994008E9543B29    40.3255 11.2%     101   0.399263 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    2 0x392F6DA628C7FEBD    33.9181  9.4%      17   1.995184 SELECT mt_entry mt_objecttag
#    3 0x6C6318E56E149036    26.4695  7.3%     102   0.259505 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
#    4 0x00F66961DAE6FFB2    25.5472  7.1%      55   0.464495 SELECT mt_entry mt_placement mt_category
#    5 0x99E13015BFF1E75E    22.3618  6.2%     199   0.112371 SELECT mt_entry mt_objecttag
#    6 0x84DD09F0FC444677    22.3516  6.2%      39   0.573118 SELECT mt_entry
#    7 0x440EBDBCEDB88725    21.1817  5.9%      36   0.588380 SELECT mt_entry
#    8 0x8D258C584B858811    17.2402  4.8%      37   0.465951 SELECT mt_entry mt_placement mt_category
#    9 0x4E2CB0F4CAFD1400    16.9768  4.7%      40   0.424419 SELECT mt_entry mt_placement mt_category
#   10 0x377E0D0898266FDD    16.6979  4.6%     150   0.111319 SELECT polls_pollquestion mt_category
#   11 0x3B9686D98BB8E054    16.2089  4.5%      32   0.506529 SELECT mt_entry mt_objecttag mt_tag
#   12 0x97F670B604A85608    15.6158  4.3%      34   0.459287 SELECT mt_entry mt_placement mt_category
#   13 0x3F5557DA231225EB    14.4309  4.0%      36   0.400859 SELECT mt_entry mt_placement mt_category
#   14 0x191D660A10738896    13.1220  3.6%      31   0.423290 SELECT mt_entry mt_placement mt_category
#   15 0xF88F7421DD88036D    12.1261  3.4%      61   0.198788 SELECT mt_entry mt_blog mt_objecttag mt_tag mt_author
#   16 0xA909BF76E7051792    10.3971  2.9%      53   0.196172 SELECT mt_entry mt_objecttag mt_tag
#   17 0x3D42D07A335ED983     9.1424  2.5%      20   0.457121 SELECT mt_entry mt_placement mt_category
#   18 0x59F43B57DD43F2BD     9.0533  2.5%      21   0.431111 SELECT mt_entry mt_placement mt_category
#   19 0x7961BD4C76277EB7     8.5564  2.4%      47   0.182052 INSERT UNION UPDATE UNION mt_session
#   20 0x173EB4903F3B6DAC     8.5394  2.4%      22   0.388153 SELECT mt_entry mt_placement mt_category

Notice that this the list of the 20 worst-performing queries based on Query Response Time divided by the Number of Times the query was called.

Looking at Query ID #1, which is 0x5E994008E9543B29, we locate that Query ID in the output file and here is the report for that particular query:

# Query 1: 0.09 QPS, 0.03x concurrency, ID 0x5E994008E9543B29 at byte 0 __
# This item is included in the report because it matches --limit.
#              pct   total     min     max     avg     95%  stddev  median
# Count          4     101
# Exec time      7     40s   303ms      1s   399ms   992ms   198ms   293ms
# Lock time      0       0       0       0       0       0       0       0
# Users                  1      mt
# Hosts                101 10.64.95.73:33750 (1), 10.64.95.73:34452 (1), 10.64.95.73:38440 (1)... 97 more
# Databases              1     mt1
# Time range 1325089201 to 1325090385
# bytes          0 273.60k   2.71k   2.71k   2.71k   2.62k       0   2.62k
# id             4 765.11M   7.57M   7.58M   7.58M   7.29M    0.12   7.29M
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ######
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_occurrence'\G
#    SHOW CREATE TABLE `mt1`.`schedule_occurrence`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_eventschedule'\G
#    SHOW CREATE TABLE `mt1`.`schedule_eventschedule`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_event'\G
#    SHOW CREATE TABLE `mt1`.`schedule_event`\G
#    SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_eventtype'\G
#    SHOW CREATE TABLE `mt1`.`schedule_eventtype`\G
#    SHOW TABLE STATUS FROM `schedule_occurrence` LIKE 'start'\G
#    SHOW CREATE TABLE `schedule_occurrence`.`start`\G
# EXPLAIN
SELECT `schedule_occurrence`.`id`, `schedule_occurrence`.`schedule_id`, `schedule_occurrence`.`event_id`, `schedule_occurrence`.`start`, `schedule_occurrence`.`end`, `schedule_occurrence`.`cancelled`, `schedule_occurrence`.`original_start`, `schedule_occurrence`.`original_end`, `schedule_occurrence`.`all_day`, `schedule_occurrence`.`ongoing`, `schedule_occurrence`.`featured`, `schedule_eventschedule`.`id`, `schedule_eventschedule`.`event_id`, `schedule_eventschedule`.`start`, `schedule_eventschedule`.`end`, `schedule_eventschedule`.`all_day`, `schedule_eventschedule`.`ongoing`, `schedule_eventschedule`.`min_date_calculated`, `schedule_eventschedule`.`max_date_calculated`, `schedule_eventschedule`.`rule`, `schedule_eventschedule`.`end_recurring_period`, `schedule_eventschedule`.`textual_description`, `schedule_event`.`id`, `schedule_event`.`title`, `schedule_event`.`slug`, `schedule_event`.`description`, `schedule_event`.`host_id`, `schedule_event`.`cost`, `schedule_event`.`age_restrictions`, `schedule_event`.`more_info`, `schedule_event`.`photo_id`, `schedule_event`.`contact_email`, `schedule_event`.`event_type_id`, `schedule_event`.`featured`, `schedule_event`.`staff_pick`, `schedule_event`.`futuremost`, `schedule_event`.`creator_id`, `schedule_event`.`created_on`, `schedule_event`.`allow_comments`, `schedule_event`.`mt_entry`, `schedule_eventtype`.`id`, `schedule_eventtype`.`parent_id`, `schedule_eventtype`.`name`, `schedule_eventtype`.`slug`, `schedule_eventtype`.`lft`, `schedule_eventtype`.`rght`, `schedule_eventtype`.`tree_id`, `schedule_eventtype`.`level`, T5.`id`, T5.`title`, T5.`slug`, T5.`description`, T5.`host_id`, T5.`cost`, T5.`age_restrictions`, T5.`more_info`, T5.`photo_id`, T5.`contact_email`, T5.`event_type_id`, T5.`featured`, T5.`staff_pick`, T5.`futuremost`, T5.`creator_id`, T5.`created_on`, T5.`allow_comments`, T5.`mt_entry`, T6.`id`, T6.`parent_id`, T6.`name`, T6.`slug`, T6.`lft`, T6.`rght`, T6.`tree_id`, T6.`level` FROM `schedule_occurrence` INNER JOIN `schedule_eventschedule` ON (`schedule_occurrence`.`schedule_id` = `schedule_eventschedule`.`id`) INNER JOIN `schedule_event` ON (`schedule_eventschedule`.`event_id` = `schedule_event`.`id`) INNER JOIN `schedule_eventtype` ON (`schedule_event`.`event_type_id` = `schedule_eventtype`.`id`) INNER JOIN `schedule_event` T5 ON (`schedule_occurrence`.`event_id` = T5.`id`) INNER JOIN `schedule_eventtype` T6 ON (T5.`event_type_id` = T6.`id`) WHERE (EXTRACT(MONTH FROM `schedule_occurrence`.`start`) = 8 AND EXTRACT(DAY FROM `schedule_occurrence`.`start`) = 6 AND `schedule_occurrence`.`start` BETWEEN '2011-01-01 00:00:00' and '2011-12-31 23:59:59.99') ORDER BY `schedule_occurrence`.`ongoing` ASC, `schedule_occurrence`.`all_day` DESC, `schedule_occurrence`.`start` ASC\G

Although the histogram is text-based, it gives an accurate picture of the query's overall performance, sometimes running over 1 sec, and most of the time between 0.01 and 0.1 seconds. From here, one can proceed to do performance tuning by refactoring the query, placing query results in memcached, adding missing or covering indexes, etc.

CONCLUSION

IMHO If Percona ever placed the profiler tools into a Windows GUI, it would easily rival Microsoft's SQL Server Profiler.

Defense Rests !!!


How to organize the tables of a MySQL database with several statistics data of several years? I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the interested city, a year for which the statistics data will be recovered and provide the amount he spent in taxes.

Then it should see for example that if he spent 4.000 dollars in taxes, the 45% of that amount (1800 dollars) was invested by the city for public education, the 20% (800 dollars) was invested in the improvement of public infrastructure, the 10% was invested in culture and the 25% was invested in administration.

I am using a MySQL database and my database structure is like follows:

I have one table 'Cities' containing the names of all the concerned cities of the statistics;
Another table 'Statistics_Entry' containing all the statistics entry names that will be used (for example, "public education", "administration", "public infrastructure", and so on...;
A lookup table 'Statistics' which binds the two tables explained before and contains the percentage of each Cities<->Statistics_Entry association (the primary key of the lookUp table consists of two INT fields, which corresponds respectively to the INT ID primary key column of the 'Cities' table and the INT ID primary key column of the 'Statistics_Entry' table.

So here is a simple explanaition of the db structure. But here comes the need of having different statistics results for different years.

At a first look I thought of using another column in the lookUp table named "year" which gives the proper year of the statistics data. But I am not sure if this is the best way.

So I am asking some advices on how structurizing this kind of information system.
Should I create another table 'Years' which will contain all the years of the statistics or is it better to store the years in a single field inside the 'Statistics' lookUp table?

Is the db structure I showed good or bad? What could be improved?

Thanks! Hope for some help!

Well , i would add a new column on the Statistics_Entry as your year column ! Next depending on the volume of your data i would partition that table using this column(this will speedup your query when using the year column as your predicate).
 This lookup table can be a view !
I would not create a new table just for the year column, this would just make thing complicated ! in my opinion !