MariaDB 10.5 How to Prevent High Memory Usage on Windows

Hmmm...  I don't see "high" memory usage.  Instead, I see "low" usage.
Analysis of GLOBAL STATUS and VARIABLES:

Observations:

Version: 10.5.9-MariaDB
16 GB of RAM
Uptime = 1d 22:31:42
Are you sure this was a SHOW GLOBAL STATUS ?
You are running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:
key_buffer_size = 20M           -- save some RAM
innodb_buffer_pool_size = 11G   -- more caching (mostly saves I/O)
table_open_cache = 3000
query_cache_size = 0            -- to turn off

Why is innodb_lru_scan_depth so high (1536)?
There were a lot of rows deleted; do you think there is a problem with DELETEs?  (There may be techniques to make them more efficient.)
Details and other observations:
( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 128M = 0 -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size (now 134217728) to avoid unnecessary memory usage.
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 128M / 0.70)) / 16384M = 5.0% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory
( Table_open_cache_misses ) = 543,542 / 167502 = 3.2 /sec
-- May need to increase table_open_cache (now 2000)
( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 543,542 / (274450 + 543542) = 66.4% -- Effectiveness of table_open_cache.
-- Increase table_open_cache (now 2000) and check table_open_cache_instances (now 8).
( innodb_buffer_pool_size ) = 128M -- InnoDB Data + Index cache
-- 128M (an old default) is woefully small.
( innodb_buffer_pool_size ) = 128M / 16384M = 0.78% -- % of RAM used for InnoDB buffer_pool
-- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)
( innodb_lru_scan_depth ) = 1,536
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs.
-- Reads could be slugghish or spiky.
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain
-- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)
( Innodb_buffer_pool_read_ahead_evicted / Innodb_buffer_pool_read_ahead ) = 7,541 / 158 = 4772.8% -- Utility of read_ahead.
-- Turn off innodb_random_read_ahead (now OFF).
( Innodb_dblwr_pages_written/Innodb_pages_written ) = 73,383/706683 = 10.4% -- Seems like these values should be equal?
( innodb_log_files_in_group ) = 1 -- Number of InnoDB log files
-- 2 is probably the only reasonable value.
A large number may cause performance problems.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 24,911,135,232 / (167502 / 3600) / 1 / 96M = 5.32 -- Ratio
-- (see minutes)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 167,502 / 60 * 96M / 24911135232 = 11.3 -- 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 100663296). (Cannot change in AWS.)
( innodb_flush_method ) = innodb_flush_method = unbuffered -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( 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.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = OFF -- Usually should be ON.
-- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890).
( 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.
( join_buffer_size ) = 262,144 / 16384M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- If large, decrease join_buffer_size (now 262144) to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.
( innodb_ft_result_cache_limit ) = 2,000,000,000 / 16384M = 11.6% -- Byte limit on FULLTEXT resultset. (Possibly not preallocated, but grows?)
-- Lower the setting.
( character_set_server ) = character_set_server = utf8
-- Charset problems may be helped by setting character_set_server (now utf8) to utf8mb4. That is the future default.
( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10%
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (79156 + 8 + 43352 + 0) / 67922 = 1.8 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.
( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 22104 - 0 ) / ( 22104 + 0 ) = 100.0% -- Are you closing your prepared statements?
-- Add Closes.
( Com_stmt_prepare - Com_stmt_close ) = 22,104 - 0 = 22,104 -- How many prepared statements have not been closed.
-- CLOSE prepared statements
( Com_stmt_close / Com_stmt_prepare ) = 0 / 22104 = 0 -- Prepared statements should be Closed.
-- Check whether all Prepared statements are "Closed".
( Com__biggest ) = Com__biggest = Com_stmt_execute -- Which of the "Com_" metrics is biggest.
-- Normally it is Com_select (now 143366). If something else, then it may be a sloppy platform, or may be something else.
( 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
( back_log ) = 80 -- (Autosized as of 5.6.6; based on max_connections)
-- Raising to min(150, max_connections (now 151)) may help when doing lots of connections.
( Max_used_connections / max_connections ) = 19 / 151 = 12.6% -- Peak % of connections
-- Since several memory factors can expand based on max_connections (now 151), it is good not to have that setting too high.
( Aborted_clients / Connections ) = 263 / 306 = 85.9% -- Threads bumped due to timeout
-- Increase wait_timeout (now 28800); be nice, use disconnect
( Aborted_connects / Connections ) = 136 / 306 = 44.4% -- Perhaps a hacker is trying to break in? (Attempts to connect)
( thread_cache_size / Max_used_connections ) = 151 / 19 = 794.7%
-- 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:
Aria_pagecache_write_requests = 1.5 /HR
Com_set_option = 1.2 /HR
Com_show_fields = 0
Created_tmp_files = 0.086 /HR
Handler_icp_attempts = 0.34 /HR
Handler_icp_match = 0.34 /HR
Handler_read_rnd = 33 /HR
Handler_tmp_update = 0
Handler_tmp_write = 0.13 /sec
Handler_update = 0.17 /HR
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_buffer_pool_pages_total = 8,065
Innodb_ibuf_merged_inserts = 0
Innodb_ibuf_merges = 0
Innodb_log_writes / Innodb_log_write_requests = 0.35%
Innodb_max_trx_id = 688,304
Innodb_mem_adaptive_hash = 0
Innodb_mem_dictionary = 892,392
Innodb_num_open_files = 17
Innodb_rows_updated = 0.17 /HR
Innodb_secondary_index_triggered_cluster_reads = 0.41 /HR
Memory_used_initial = 3.52e+7
Rows_tmp_read = 0.13 /sec
Sort_priority_queue_sorts = 0
Sort_rows = 33 /HR
Sort_scan = 1.8 /HR
Table_locks_immediate = 3.2 /HR
aria_checkpoint_log_activity = 1.05e+6
aria_pagecache_buffer_size = 128MB
innodb_adaptive_max_sleep_delay = 0
innodb_background_scrub_data_check_interval = 0
innodb_background_scrub_data_interval = 0
innodb_buffer_pool_chunk_size = 128MB
innodb_max_undo_log_size = 10MB
innodb_online_alter_log_max_size = 128MB
innodb_sort_buffer_size = 1.05e+6
innodb_spin_wait_delay = 4
lock_wait_timeout = 86,400
query_cache_limit = 1.05e+6

Abnormally large:
Com_create_index = 0.021 /HR
Com_drop_index = 0.064 /HR
Com_rename_table = 0.064 /HR
Com_show_open_tables = 0.13 /HR
Handler_delete = 447 /sec
Handler_discover = 1.7 /HR
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 2.27
Innodb_buffer_pool_pages_lru_flushed = 1,606
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 35029.2%
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 673
Innodb_dblwr_pages_written / Innodb_dblwr_writes = 71.4
Innodb_os_log_pending_writes = 1.84e+19
Innodb_rows_deleted = 447 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 1274 /sec
Innodb_rows_inserted = 826 /sec
Memory_used = 42.6%
Open_streams = 4
Prepared_stmt_count = 967
Select_range / Com_select = 71.8%
Threadpool_threads = 4
aria_sort_buffer_size = 256.0MB
innodb_lru_scan_depth / innodb_io_capacity = 7.68
max_relay_log_size = 1024MB
performance_schema_max_cond_classes = 90
performance_schema_max_statement_classes = 222

Abnormal strings:
aria_recover_options = BACKUP,QUICK
binlog_row_metadata = NO_LOG
disconnect_on_expired_password = OFF
have_crypt = NO
histogram_type = DOUBLE_PREC_HB
innodb_fast_shutdown = 1
innodb_log_optimize_ddl = OFF
log_slow_admin_statements = ON
lower_case_file_system = ON
lower_case_table_names = 1
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
opt_s__optimize_join_buffer_size = on
optimizer_trace = enabled=off
slave_parallel_mode = optimistic
thread_handling = pool-of-threads
time_zone = +00:00
use_stat_tables = PREFERABLY_FOR_QUERIES
version_compile_machine = x64


Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server

I'll answer your second question first, which is typically it's ideal to host your SQL Server instance on its own dedicated server, to maximize the resources it has available to it without causing contention for other applications or battling contention from other applications.
The Memory, CPU, I/O, and Disk Space available on the server are all key resources utilized by a SQL Server instance, and some of them are even eagerly acquired and held onto to maximize efficiency and performance of that SQL Server instance. For example, SQL Server will generally continually acquire more Memory (out of the total allocated to the instance) over time for multiple uses of caching, and generally won't release that Memory back to the server, unless the Windows OS indicates a critical Memory pressure event, or the server or instance is restarted.
To answer your first question, we can't accurately tell you the issue you're currently facing without more details and context, unfortunately. It's too complicated, and broad of a question, without observing actual concrete examples with metrics. But if you're certain
your SQL Server instance is affecting performance of your web application, then you're likely under-provisioned resource-wise. 12 GB of Memory sounds suspiciously low for both the OS (which likely needs a minimum of 4 GB itself) and the web application itself.
Aside from that, I wouldn't doubt you're seeing other resource contention either from the CPU or I/O. Your best bet is to try to isolate a specific example of a performance issue your web application is experiencing so that it's reproducible, and then measuring what's actively running on your SQL Server instance concurrent to those performance issues of the application. I find Adam Machanic's sp_whoisactive very helpful as a starting point to see what is currently running on my SQL Server at any given instant.


Connection settings, including encryption, are instance level. Since connections can switch databases at any moment (simply issue an USE statement) it makes no sense at all to have such settings at database level.

Security constrains ('all traffic to and from this DB must be encrypted') often result in splinting the DB into its own instance just to satisfy the constraint.


SQL Server 2000 Page Splits per second higher han 12 million?

Please read Calculating Counter Values to understand what performance counter values are and how you use them. Per second values are computed from two samples delta divided by time.

Jason Strate has a SQL Server specific discussion here.


Nested subquery giving eror of unknown column

The problem here must be with the id reference in the innermost SELECT. For a long time MySQL has had trouble with resolving outer references in correlated subqueries if those references were placed inside derived tables in the correlated subqueries.
Apparently the problem has been resolved in 8.0.14:

Optimizer Notes

Previously, derived tables and common table expressions could not contain outer references. Outer references are now permitted.

If you want the query to compile both in 8.0.14 and in 8.0.11, apparently you need to rewrite it to somehow avoid referencing the id column in a derived table. Before making any suggestions, I must note that your example has other issues than the unresolved id reference. The very first line in this correlated subquery:
select max(SETNO)
from ( select sum(SETNO)
       from AUC1
       where SETNO=id
     ) a

already has a problem, because it references an unknown column SETNO. The derived table a does not have such a column.
So, for the sake of showing how to resolve the id reference issue, I am going to assume that sum(SETNO) was meant to be written as sum(SETNO) AS SETNO. That way at least* the outer level SETNO reference becomes valid.
Basically, the solution is to get the MAX(SETNO) in an alternative way, such that a derived table is actually not needed at all and thus the id column can be referenced directly in the correlated subquery. For that you will use ORDER BY and LIMIT 1. Specifically, you rewrite your correlated subquery as follows:
SELECT sum(SETNO)
FROM AUC1
WHERE SETNO = id
ORDER BY sum(SETNO) DESC
LIMIT 1

This way the correlated subquery is still getting the same value but no longer references outer columns from a derived table.

*The query still does not make much sense as it is, though. But at least it should compile now and allow us to demonstrate the solution.


Is there any danger in granting SUPER privileges to a user?

It is always a bad idea to give a user more access than it needs to perform its duties.

  This user is the same user used in my web application

If your application is hacked due to a bug in your code or one of the libraries/modules that you are using, the attacker may end up being able to take action as this user - so if this user has privileges that allow the complete destruction of the application database and other databases on the same server then a successful attacker gets these dangerous privileges too.

An application user should have the absolute minimum privileges that it needs to act on behalf of normal tasks required by the application. For larger systems it is not uncommon to have multiple application users for different parts of the system each with restricted rights that block them from interacting with unrelated objects in the database. Other privileges that allow management of the database and server more generally should be give only to separate administration users.

If you are in a situation where an application user needs administrative privileges then it is recommended that you try refactor your design to remove such need (or limit it as much as is possible).

Lookup the Principal of Least Privilege (i.e. https://en.wikipedia.org/wiki/Principle_of_least_privilege) for more information about why this is a good idea generally.


msdb.dbo.sp_send_dbmail temp table does not exist

Personally, when I email results (and it's not 100K rows or whatever) I like to mark it up with CSS and send it as HTML so it is readable. Shnugo has a great function for doing this over on Stackoverflow.

To use it, aside from his instructions, you can do something like so:

declare @body xml =
    (select dbo.ufn_CreateHTMLTable (
        (SELECT FROM #WODups
         for xml path ('row'),elements xsinil)
    ,null,null,null))

--Maybe add some borders, Shnugo shows how to do it with his funciton too

declare @body_html varchar(max)
set @body_html =
    '<style type="text/css" media="screen,print">
    .center
    {
        text-align: center;
    }
    table,th
    {
        border: 1px solid black;
    }
    table,tr
    {
        border: 1px solid black;
    }
    table,td
    {
        border: 1px solid black;
    }
    </style>'
    + cast(@body as varchar(max))

    exec msdb..sp_send_dbmail
    @profile_name = 'SQL Mail',
    ,@recipients = 'someone@domain.com'
    ,@subject = 'This is my Subject'
    ,@body = @body_html
    ,@body_format = 'HTML'


Possible benefits of storing multiple values in one field of one row instead of as separate rows

To start with, the current Question title referring to "storing data as string instead of columns" is a little confusing. When speaking of storing data as strings instead of something else, that usually refers to serializing everything to a string format instead of a proper / strong datatype (e.g. INT or DATETIME). But if asking about storing data as multiple values in a single field as opposed to separate rows, that is a bit different. And to be fair, while concatenating values is most easily done with strings, it can also be done with INT and BINARY types as well, either by bit-masking or similarly reserving certain positions to have different meanings. Since the second interpretation is what is actually being asked about, based on the text of the Question, let's address that.

In a word: No. If you are storing actual data points then it will only bring pain (in terms of code and performance) as it is unnecessary complication. If it is a value that will only ever be stored as a single unit, updated as a single unit, and never disassembled within the database, then that could be ok as it is roughly analogous to storing an image or PDF. Otherwise, any attempt to parse the data will invalidate using any indexes (e.g. using LIKE '%something%', or CHARINDEX, or PATINDEX, or SUBSTRING, etc).

If you need to store separate values in a single field of a single row then there are more appropriate means of doing that: XML or JSON. These are parseable formats (XML / JSON) and XML can even be indexed. But ideally this data would be stored in properly-typed fields so that it can be truly useful.

And please do not forgot that the purpose of an RDBMS is to store data such that it can be retrieved and manipulated as efficiently as possible, within the constraints imposed by being ACID-compliant. Retrieving concatenated values is bad enough due to the need to parse the values first, and that is not indexable. But manipulating often means replacing the entire blob just to update a part of it (assuming that no pattern exists to use with a REPLACE function). The XML datatype at least allows for XML DML for simplistic updates, though those are still not as fast as a simple update of properly modeled data.

Also, given a scenario such as what is shown in the Question above, by concatenating all of the StateCodes together, you would be unable to Foreign Key (in either direction) those values.

And what if the business requirements change over time and you need to track additional properties of these items? In terms of "states", what about the capitals, or population, or a sort-order, or anything else? Stored properly as rows you can add more columns for additional properties. Sure, you can have multiple levels of parsable data, such as |StateCode,Capital,Population
|StateCode,Capital,Populate|... but hopefully anyone can see the problem growing exponentially out of control. Of course, this particular issue is rather easily dealt with the XML and JSON formats, and that is their value as mentioned above. But you would still need a very good reason for using either of those as an initial means of modeling as neither will ever be as efficient as using discrete fields in separate rows.


Is there anything else I can do, or is there a step I missed, to revert back to the old Cardinality Estimator?

In SQL Server 2014, reverting to OLD CE can be done in 2 ways

Setting the database compatibility mode to less than 120.
(or) Enabling trace flag 9481 globally or at query level using option querytraceon

Now that you have reverted to old CE, your next steps in troubleshooting are finding out/isolating queries that are poorly performing - using XEvents or server side trace.

You should tune those queries.

My Suggestions :

Dont revert to old CE, instead isolate poor performing queries and tune them. Use option (querytraceon 9481) as query hint if you are not able to tune and want specific queries to use the OLD CE.
Since you are on SQL Server 2014 + SP1, you should enable trace flag 4199.

Also refer to

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads


I am not sure from where you have got this script however it seems you are trying to rebuild indexes if the fragmentation level is more than 30 and reorganize if the same is less than 30. Your script is actually command to issue reorg and rebuild statement based on  other part of code which is not copied here.

You may try below piece of code for inquiring the fragmentation level within a given database:

1st Query:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
and indexstats.page_count >=1000 and
dbindexes.name is not null
ORDER BY indexstats.avg_fragmentation_in_percent desc

2nd Query:

DECLARE @DatabaseID int

SET @DatabaseID = DB_ID()

SELECT DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000

Kindly note that, page count dictates how big is the table and contains how many pages. For performing reorg or rebuild, you may check wonderful script from Mr. Ola Hallengren and use them for your maintenance job, here also you can customize the fragmentation level on which you want to perform reorg or rebuild, it also has option of offline and online, you may read more details at this link.

https://ola.hallengren.com/

You may also check the best practice and possible impacts on website of Mr. Brent Ozar and can be referred as below:

https://www.brentozar.com/archive/2017/12/index-maintenance-madness/

If fragmentation is not showing showing any sign of improvement, that could mean its a very small table having page count much lesser than 1000. Standard practice is to consider only those tables which is at least 1000 pages or more.

Kindly refer below links explaining same:

Why is my database still fragmented after I rebuilt and reindexed everything?

SQL Server 2012, rebuild not lowering avg fragmentation

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c419f809-c4c6-4c7f-9f36-c9a56d21d37a/index-still-fragmented-after-rebuild?forum=sqldatabaseengine

In case, you have bigger tables however doesn't have clustered index meaning its a heap then you should go for table rebuild with below command:

Alter table SchemaName.TableName rebuild

You can read more details about that on this link.

The most important aspect is to see "How to detect whether index fragmentation affects SQL Server performance". please check more details about that here.

Hope above helps.


Best way to implement concurrent table based queue

First: MySQL is one of the worst possible pieces of software to implement this, specially if it is very dynamic. The reason is that engines like MEMORY and MyISAM have only full-table locks while more suitable engines like InnoDB have a higher write penalty (to provide ACID properties) and are optimized for accessing records that are spatially and temporally close (those are set on memory). There is also not a good change notification system for MySQL- it has to be implemented as a polling. There are dozens of pieces of software more optimized for that task.

Having said that, I have seen successfully implement this kind of access if the performance/efficiency requirements are not very high. Many people cannot afford to introduce and maintain a complete separate piece of technology just for a small part of the business logic.

SELECT FOR UPDATE is what you are looking for- read serialization. While an UPDATE/DELETE will always lock the row during a running MYSQL transaction, you may want to avoid a large transaction while the process is going on, so:

START TRANSACTION;
SELECT * FROM your_table WHERE state != 'PROCESSING'
  ORDER BY date_added ASC LIMIT 1 FOR UPDATE;
if (rows_selected = 0) { //finished processing the queue, abort}
else {
UPDATE your_table WHERE id = $row.id SET state = 'PROCESSING'
COMMIT;

// row is processed here, outside of the transaction, and it can take as much time as we want

// once we finish:
DELETE FROM your_table WHERE id = $row.id and state = 'PROCESSING' LIMIT 1;
}

MySQL will take care of locking all the concurrent selects except one when selecting rows. As this can lead to a lot of locked connections at the same time, keep the initial transaction as small as possible and try to process more that 1 row at a time.


Is there any way to monitor execution of xp_cmdshell in SQL Server 2012?

Audit events are actually not available through Extended Events. You would need to use SQL Server Audit, which more or less works on top of the Extended Events engine (at least from what I understand).

Steps to go with:

Create a Server Audit
Create a Server Audit Specification
Then go back and enable them.

It is fairly straight forward in using the UI via SSMS to create them but for purposes of this post, this would be the T-SQL to do the same thing:

USE [master]
GO

CREATE SERVER AUDIT [cmdshell-20150603-124954]
TO FILE
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Log\' )
WHERE object_name = 'xp_cmdshell';
GO

CREATE SERVER AUDIT SPECIFICATION [Audit-cmdshell]
FOR SERVER AUDIT [cmdshell-20150603-124954]
ADD (SCHEMA_OBJECT_ACCESS_GROUP);
GO

Then when you go back and view the Audit Collection it you would see something similar to this:

In addition with regard to the audit being turned off you can create a seperate server audit that will specifically log when any SQL Server Audit is disabled or started. This will at most give you who stopped it.


what is the maximum capacity allowed for SQL managed Instance?

At this stage, IaaS is the only option for exceeding this size. Currently, Managed Instances carry the 4TB/8TB limit depending on your service tier and I don't believe there are any announcements about increasing this. Azure SQL Database can be sized up to 4TB, again depending on the service tier chosen.

There is a new tier for Azure SQL Database in preview called Hyperscale (https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tier-hyperscale) which may provide a solution, but this is not generally available yet. This only appears to apply for Azure SQL Database, and not Managed Instances according to the documentation available so wouldn't be an exact answer for the Managed Instance limits.


Firstly, I'd encourage you to stop using maintenance plans for your backups.  Ola Hallengren's maintenance scripts are used by a LOT of people.  Simply schedule them using the SQL Agent.

I agree that taking steps to ensure your backup process is optimal, but I'd also encourage you to think about your restore strategy as well.

By taking transaction log backups every hour, you are saying it's ok to lose up to 1 hour of updates should you need to restore. IMO, implementing more granular backups is really no more difficult and brings some additional piece of mind.  See Back Up Transaction Logs Every Minute. Yes, Really.

When researching and developing a restore strategy, the main two things you'll tend to see over and over are: RTO (Recovery Time Objective) and RPO (Recovery Point Objective).

RTO basically is the maximum time allowed to get things back to 'normal' or at least functioning. You would need to take into consideration small problems (server went down) to big problems (the building just exploded).

RPO basically is, how much data could we 'possibly' lose should a bad problem happen? 5 minutes, 1 hour, 1 day, NONE?

Take some time to read The 9 Letters That Get DBAs Fired
(Basically RPO, RTO and CYA).

Also, The Accidental DBA (Day 8 of 30): Backups: Planning a Recovery Strategy, discusses Designing a Restore Strategy.

One last thing - develop a plan to routinely test your backups.

The only way to verify a backup is good/usable is to restore it.

REPEAT – you don’t know you have a good backup until you have restored it!

I provided an example in my answer to How to conduct integrity test on SQL Server database backup file?


Adding constraint to an existing table column

To alter ypour existing table use

ALTER TABLE SomeTable
ADD CONSTRAINT `Student_type2` CHECK (Type IN
('Exchange-Student','Independent student','program Student'))

Or ad following to the CREATE TABLE seperated by comma

CONSTRAINT `Student_type2` CHECK (Type IN
('Exchange-Student','Independent student','program Student')


How do I keep a SQL Server Profiler trace from jumping to the bottom entry?

Simple Answer :

From the toolbar, select Window>Auto Scroll


What should be architecture of multi client web application?

Let me start with: you miss 3: Mutliple databases each containing data for a number of clients - allows both scale out (important) as well as not having possibly hundreds of thousands of databases.

But even then, you really have some bad logic - basically it is a lot less a problem than you think:

  If single database is used then data size will increase very fast and
  performance issue can be encountered due to load by multiple company users
  hitting the same database.

Ah, yes, and if you have a dozen databases and your server is too slow the exact same happens. Also data size increase is identical unless you have a lot of shared data that you only need to load once.

  The benefit of this approach is that schema is very easy to manage schema.

No. You deal with a product, so schema management should be fully automated. After all, you will likely have to manage schemata of a dozen or two database copies ANYWAY - development, a separate schema possibly per developer, some for testing, then quality control. Unless you plan everything to happen manually db changes will use change scripts and be automated.

And once automated - managing 1000 copies of a database is not really much more a problem, or?

  The separate database on the other hand has benefit of reduced performance
  overhead, privacy of company data.

No. I mean, yes, it has less performance overhead. But this is cent wise and irrelevant - I doubt the overhead is more than a couple of percent. Privacy CAN be important, it makes a lot of scenarios a lot easier to separate customers. But only if you have separate web servers for every customer (or a good maintenance window) - otherwise you have to update all databases at exactly the same moment as code. Have fun with that.

Privacy can be important. It also allows easy import and export of data. As well as easily transporting data to a separate instance in case of a bug so people can look at it.

It also will likely use a lot more resources on the web server and database server as connection management will use a lot more connections because they are not reusable. Unless you program partially around it.

  But the hard part is that a single change in schema needs to be replicated in
  all the company databases

Only if your development process is really backward and 1990 or so. Agile and DevOps already demand this to be solved, so this is a long solved problem and you have it anyway. Not sure how your team works, but I am in a smallish team (around 6 developers) in a project at the moment. One production database. Total db copy count is around 20 - multiple test environments, every developer has a separate database for his work, quality control, hallway testing, all have their own copy of the database. As such, schema management is fully automated. I could not care less about having 1000 other copies to run the scripts on. It is a little more scripting (and the time and parallelism requirements are an issue) but the core problem is solved anyway. If you do not have this, I fear your product will have manually maintained databases and the result of that will be a horrifically botched development process.


Transactional replication works well in this scenario for replicating your data to your reporting databases.

It's cheaper than availability groups as you can replicate back to your single server instance to create a reporting database, rather than having an availability group where you have to pay for a second licence if you are reading the secondary data.

SQL Server 2014 Standard edition supports transactional replication for the publisher (you can use a lower edition for the subscriber as per the editions here), whereas AlwaysOn Availability Groups require Enterprise edition.

Your options for replication setup would be

Production Instance > Separate Distributor > Reporting Instance
Production Instance > Separate Distributor > Production Instance
Production Instance > Distributor on Production Instance > Production
Instance

Option 1 requires a licence for each instance, however would remove reporting load from the production server if required (remember you can use lower editions for subscribers)

Option 2 requires a licence for the production instance and the distributor, but gives a balance between cost and load on your production instance

Option 3 just requires the single licence but can cause additional load if you're replicating a lot of data.

You can read more about it here, although I'm sure you have checked it out already. others may have views on other replication methods, but this works in a environment I work on well. It all depends on balancing cost vs up time (AlwaysOn AGs vs replication)


MySQL count rows from other table AND get most recent title

You could do something like this (see fiddle here - it shows development of all stages of the query). There is a small possible flaw in your design - see discussion and (slightly changed) DDL and DML in an alternative schema available here (with slightly changed SQL also!).
In future, could you please provide us with some sample tables (DDL) data and data (DML) so that we have a Single Source of Truth for the question - it avoids errors and eliminates duplication of effort on behalf of those trying to help you!
I couldn't test my solution because I didn't have any data and I've stopped doing my own data mockups since when I do, the OP (frequently) comes back with something like "Oh, but, if I put in records with x, y and z...) your solution doens't work..." - that's then me then going #FF0000/rgb(255,0,0) and swearing at the computer (and the OP)...
I simulated data and tested - my previous solution fails - this one now works - it even takes account (through LEFT JOINs) of students who haven't attended any events!
Please test with real datasets with a large number of records and apply indexes to your tables' fields as appropriate. You can use MySQL 8.0.18 and above's new EXPLAIN ANALYZE (and see here) functionality to get a good idea of where indexes are required! If you run into problems, ask a new question here.
CREATE TABLE student
(
  s_id INT NOT NULL PRIMARY KEY,
  s_name VARCHAR (100) NOT NULL,
  s_email VARCHAR (255) NOT NULL
);

CREATE TABLE event
(
  e_id INT NOT NULL PRIMARY KEY,
  e_name VARCHAR (100) NOT NULL,
  e_date_scheduled DATE NOT NULL
);

CREATE TABLE attendance
(
  a_id INT NOT NULL PRIMARY KEY,
  a_sid INT NOT NULL,
  CONSTRAINT a_sid_fk FOREIGN KEY (a_sid) REFERENCES student(s_id),
  a_eid INT NOT NULL,
  CONSTRAINT a_eid_fk  FOREIGN KEY (a_eid) REFERENCES event(e_id)
);

Populate the tables:
INSERT INTO student VALUES
(1, 'name_1', 'email_1@domain_1.net'),
(2, 'name_2', 'email_2@domain_2.net'),
(3, 'name_3', 'email_3@domain_3.net'),
(4, 'name_4', 'email_3@domain_3.net'); --   <<<---- attends no events!

INSERT INTO event VALUES
(1, 'event_1', '2020-01-01'),
(2, 'event_2', '2020-02-01'),
(3, 'event_3', '2020-03-01'),
(4, 'event_4', '2020-04-01'),
(5, 'event_5', '2020-05-01'),
(6, 'event_6', '2020-06-01');

INSERT INTO attendance VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),

(5, 2, 2),
(6, 2, 3),
(7, 2, 5),

(8, 3, 5),
(9, 3, 6);

The run the query:
SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail,
       COUNT(a.a_id) AS a_cnt, t2.eds, COALESCE(t2.enam, '-----') AS "Latest event"
FROM
  student s
  LEFT JOIN attendance a
    ON s.s_id = a.a_sid
  JOIN
  (

    SELECT sid, eid, eds, enam
    FROM
    (
      SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam,
      ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn
      FROM
        student s2
      LEFT JOIN attendance a2
        ON s2.s_id = a2.a_sid
      LEFT JOIN event e
        ON a2.a_eid = e.e_id
    ORDER BY s2.s_id, e.e_date_scheduled DESC
  ) AS t
  WHERE t.rn = 1
) AS t2
ON s.s_id = t2.sid
GROUP BY s.s_id, s.s_name, s.s_email, t2.eds, t2.enam
ORDER BY s.s_id;

Result:
sid snam                   smail  a_cnt         eds  coalesce
1   name_1  email_1@domain_1.net      4  2020-04-01   event_4
2   name_2  email_2@domain_2.net      3  2020-05-01   event_5
3   name_3  email_3@domain_3.net      2  2020-06-01   event_6
4   name_4  email_3@domain_3.net      0        NULL     -----

Alternative schema:
The "flaw" to which I referred at the beginning is that you have an attendance table with an identity type PRIMARY KEY - it is not required - at least in this case.
Your use case (at least for this query) is a classic example of the JOINing table design pattern - aka a linking table (and many other names - there are 16 listed on the reference below). The formal name for this type of database object is an Associative Entity and the PK for such a table is normally just the two JOINing fields - no need for an identity column at all!
So, your attendance table definition should look more like this:
CREATE TABLE attendance
(
  -- a_id INT NOT NULL PRIMARY KEY,   -- << NOT necessary!
  a_sid INT NOT NULL,
  a_eid INT NOT NULL,
  CONSTRAINT a_s_e_pk PRIMARY KEY (a_sid, a_eid),
  CONSTRAINT a_sid_fk FOREIGN KEY (a_sid) REFERENCES student(s_id),
  CONSTRAINT a_eid_fk  FOREIGN KEY (a_eid) REFERENCES event(e_id)
);

The final query is now as below - the joins are now done exclusively using the JOINing fields and not the, no longer needed, surrogate PRIMARY KEY.
SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail,
       COUNT(a.a_sid) AS a_cnt, t2.eds, COALESCE(t2.enam, '-----') AS "Latest event"
FROM
  student s
  LEFT JOIN attendance a
    ON s.s_id = a.a_sid
  JOIN
  (

    SELECT sid, eid, eds, enam
    FROM
    (
      SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam,
      ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn
      FROM
        student s2
      LEFT JOIN attendance a2
        ON s2.s_id = a2.a_sid
      LEFT JOIN event e
        ON a2.a_eid = e.e_id
    ORDER BY s2.s_id, e.e_date_scheduled DESC
  ) AS t
  WHERE t.rn = 1
) AS t2
ON s.s_id = t2.sid
GROUP BY s.s_id, s.s_name, s.s_email, t2.eds, t2.enam
ORDER BY s.s_id, t2.eds  -- << Can invert depending on requirements

Result identical!
Performance seems very similar - see the profiling at the end of the fiddle - however, the composite PK appears on average - tried 5 runs of each - to be slightly better but YMMV depending on your data distribution?


Triggers not being run on a partitioned table

The trigger is AFTER INSERT OR UPDATE. As you can see in your function, it ends with:

RETURN NULL;

That means that the row is discarded and any changes to the row are void. Actually, the row returned from an AFTER trigger is discarded in any case. The RETURN NULL is just a reminder.

You need to do this in a BEFORE trigger or issue an explicit UPDATE statement. The second option is very tricky, however, because you could end up with an endless loop. Better make it a BEFORE trigger.

As an aside: the conditional expression could be simplified with a CASE statement (since version 8.4):

CASE NEW.timeleft
WHEN 'SHORT' THEN
    NEW.expires := NEW.updated + interval '30 minutes';
WHEN 'MEDIUM' THEN
    NEW.expires := NEW.updated + interval '2 hours';
...
ELSE
    RAISE EXCEPTION 'timeleft was unrecognised. ...';
END CASE;


Handling 'Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements'

Is there any way I can catch and handle this error?

No.  This isn't your problem.  You have some other error, and your stored procedure is coded to rollback on error.  Performing a ROLLBACK in a stored procedure that didn't start the transaction is also an error, and will generate this message.

You need to look at the previous error.  Before the ROLLBACK the procedure should be calling RAISERROR or THROW to return the underlying error details to the client.  In SSMS you'd see both error messages, in Visual Studio debugging the SqlException has a collection of Errors you can interrogate.  But other clients sometimes only display the last error message.

If it isn't, it should.  Also you can use Profiler to see it regardless, using the "Errors and Warnings"/"User Error Message Event" or similar XEvent.


Help me to query this two tables properly

If you want all the rows that have Orders and Payments but maybe don't have user go for the Inner Join/Left join

SELECT `pg`.`id`, ref, business_name, `usr`.`full_name`, date, `pmt`.`amount`, `pg`.`status`
  FROM kv_ad_orders AS `pg`
INNER JOIN kv_payments AS `pmt` ON `pg`.`ref` = `pmt`. `transId`
LEFT JOIN kv_users AS `usr` ON `pg`.`sales_rep_id` = `usr`.`ID`

If all the data must match, go for this:

SELECT `pg`.`id`, ref, business_name, `usr`.`full_name`, date, `pmt`.`amount`, `pg`.`status`
  FROM kv_ad_orders AS `pg`
INNER JOIN kv_payments AS `pmt` ON `pg`.`ref` = `pmt`. `transId`
INNER JOIN kv_users AS `usr` ON `pg`.`sales_rep_id` = `usr`.`ID`

If you are talking about query projection (columns to be returned regardless of the matching data) go for select pg.*

SELECT `pg`.*, `usr`.`full_name`, `pmt`.`amount`
  FROM kv_ad_orders AS `pg`
LEFT JOIN kv_users AS `usr` ON `pg`.`sales_rep_id` = `usr`.`ID`
RIGHT JOIN kv_payments AS `pmt` ON `pg`.`ref` = `pmt`. `transId`


I just solved it by doing this:
select sum(units) / (select count(distinct week) from record_sales_data rs2 where rs.customer_country_id=rs2.customer_country_id group by customer_country_id) as average
from record_sales_data rs
group by customer_country_id


Generally, I've seen these intermediate levels referred to by the "grain" of the aggregation at which the data is stored.

For example, if you have an intermediate aggregation in which the data is stored at the level of a person for a day, we would call that the person-day grain. This can be applied generically throughout most schemas.


I would say the most advanced database access layer these days is Spring Data JPA. It builds fast, correct DAOs for you at startup time. I would check that out, maybe read some of the source, and gain inspiration from there.

As for connections, it depends on the app. For desktop to db apps, you can keep a connection open for the entire user session.

For web apps, you want to open a connection at the last possible moment, and close it as early as possible. You might read from 30 tables in a page request though, so use the same connection in that time.

Database connections are "expensive" in terms of memory and startup time, so one creates a pool of them. You don't really close your db connection, you just release it back to the pool.


Can a database be created with no user and/or owner?

MySQL allows for a blank user with a blank password (anonymous access) to connect.

See Securing the Initial MySQL Accounts

  If accounts for anonymous users were created, these have an empty user
  name. The anonymous accounts have no password, so anyone can use them
  to connect to the MySQL server.

Besides that, MySQL does not have the concept of "database owner", like SQL Server and Postgres do. There is a privilege system, where users can be assigned various types of access to databases, tables, etc, with GRANT statement.


Help with stored procedure and update table

If this is SQL Server procedure, leave out the parenthesis and commas:

Create Procedure sp_PersonEditor
  @BusinessEntityId int not null,
  @PersonType nchar(2) not null,
  @NameStyle bit not null,
  @Title nvarchar(8) null,
  @FirstName nvarchar (50) not null,
  @MiddleName nvarchar (50) null,
  @LastName nvarchar (50) not null,
  @Suffix nvarchar (10) null,
  @ModifiedDate datetime not null
as
  update BusinessEntityId
  set Title = @Title, MiddleName = @MiddleName
  where BusinessEntityId = @BusinessEntityId
end

Also, I would not recommend naming procedures sp* since that's meant for system procedures and causes performance overhead. Fixed also the typos (Business vs Bussiness). You don't have variables New* so I removed that prefix too.


index maintenance strategy if few insertion points relative to # of rows

A page split is a page split.  You can't control what SQL Server does when it requires a page split.  That is not a variable and configurable setting.  When SQL Server needs to split a page, it'll put roughly half of the data in one page and half in the new page.

If you are having "multiple" insertion points into the index, causing hot spots in the index to undergo many page splits and much fragmentation then there isn't much you can do to alleviate that with the current design.  It sounds like the way your data is dispersed that could be the root of your problem.  If you insert into the middle of an index, you will cause page splits/fragmentation.

There are a couple of things you can do, depending on how predictable your "insertion points" are.  You could utilize table partitioning, and this would allow you to rebuild the indexes on a particular partition (or partitions).  This will keep cold data from receiving the maintenance, and minimize the duration of the rebuild to the hot and fragmented partitions.

Depending on your edition, you could also utilize online index rebuild operations to reduce the availability impact on this maintenance procedure.


Representing ownership of heirarchical tree structure

I've thought of several possible solutions, but they all leave something to be desired. For example, I could add an owner_id column to the folders table, referencing users (id). The negative is that the owner would have to be repeated on every row, and ensuring that the entire tree has the same owner would be... difficult. (Maybe a foreign key on (parent_id, owner_id)? That seems... a bit gross.)

That's not true. PostgreSQL provides for CHECK constraints which make this really easy.

CREATE TABLE users (
    id   serial PRIMARY KEY,
    name text   NOT NULL
);

CREATE TABLE folders (
    id        serial PRIMARY KEY,
    parent_id int    REFERENCES folders,
    owner_id  int,
    name      text   NOT NULL,
    CHECK (
      (parent_id IS NULL AND owner_id IS NOT NULL)
      OR (parent_id IS NOT NULL AND owner_id IS NULL)
    )
);

INSERT INTO users(id,name) VALUES
  (1, 'Evan Carroll');

INSERT INTO folders(id,parent_id,owner_id,name) VALUES
  (1, null, null, '/usr');
ERROR:  new row for relation "folders" violates check constraint "folders_check"
DETAIL:  Failing row contains (1, null, null, /usr).

INSERT INTO folders(id,parent_id,owner_id,name) VALUES
  (1, null, 1, '/usr');
INSERT 0 1

INSERT INTO folders(id,parent_id,owner_id,name) VALUES
  (2, 1, null, '/usr');
INSERT 0 1

As for the space concern, that's hardly something I would worry about. It's 4 bytes on a row. Joining for that and establishing a 1:1 relationship sounds utterly silly.


Here is my somewhat hand-wavy approach to shrinking files. This has worked pretty well for me for over ten years.

Shrinking files is not necessarily evil, but that doesn't mean that it is always the best thing to do.

Firstly, think about why you are shrinking. Most databases will only grow larger. If you expect to need the space in the foreseeable future, you probably don't want to shrink.

The best reason to shrink is that you are trying to recover from some mistake, where the size of the data file has grown well past anything that is required in the near future.

Another good reason is that you need to restore the affected database onto development or test servers that simply do not have the storage capacity to handle the extra, unused, space.

The worst reason to shrink is that you are trying to jam yet another database onto storage that is already nearly full. You are going to run out of space, accepting it is the first step. The next step is to work on a plan for more storage (or fewer databases), not robbing Peter to pay Paul.

Shrinking tempdb is almost always painful. It is hard to get timely shrinks without restarting the instance and restarting the instance is a bad habit.

Secondly, make sure that you leave extra space in the MDF and NDF files. Reindexing will need some working space. How much? It depends. Find the size of your largest table and use that as a guide. I've never gone wrong by leaving that much space in the data files. If you don't have enough space in your files, they will try to autogrow. If SQL Server lacks contiguous space in the file, you may have problems reindexing large tables; they won't ever seem to be property defragged after the reindex routines run.

Do not use auto shrink. It was intended for databases running on users workstations, not on dedicated production servers and was thought up so long ago that using it for any reason whatsoever isn't a good idea at this point.

Use dbcc shrinkfile, not dbcc shrinkdatabase. If you need to shrink a database with several *MDF*s, do it in a round-robin fashion, paying attention to the file groups that each file belongs to. In order to effectively spread I/O around, SQL Server wants files of equal size in a file group.

Generally, shrinkfile doesn't put a lot of load on the server, but when I shrink files I avoid peak demand periods and I like to watch a little more closely with Performance Monitor than I normally would.

Do not try to shrink out all of the space in one go. If you try to shrink out a large amount of space in one go, the dbcc command will often seem to go catatonic. Disk I/O will drop, but the command will not complete.

After running dbcc shrinkfile, you will want to reindex to put the data back in order. Depending on your tables, you may be able to do online reindexing, which is widely documented.

If you have shrunken the files too far the files will try to autogrow. You don't want that, so be careful to not shrink things too much. when in doubt, leave an extra GB.

File-level fragmentation tends to occur most on volumes that have lots of data files that are often grown. Frequent drop/recreation of databases may exacerbate the problem. If you are shrinking files, they should get smaller and there should be fewer fragments. There is no particular reason that defragging should make things worse, though it will put I/O load on your server, which may hurt if the storage is already heavily-loaded. Some admins swear by third-party defragging tools, but I have only used Microsoft's tool on servers. Again, I wouldn't do this during peak demand times.


There are several things you can do
THING #1
Expand all VARCHAR(1024) to VARCHAR(10240) in all Stored Procedures. This will accommodate longer Family Tree Listings. This is vital because queue_children can grow longer and longer if you have a lopsided tree structure. It also possible that when queue_children was 1024 in length and did not delimit properly, it probably went into some infinite loop trying to terminate properly and never reached the condition when the LENGTH(queue_children) would be 0.
THING #2
GROUP_CONCAT()'s maximum  length is limited by group_concat_max_len (default is 1024). Try expanding group_concat_max_len to something ridiculously large (5000000000).
THING #3
Increase your thread_stack, perhaps 512K or 1M. This is needed for Stored Procedures with either recursive operations or, in this case, longer local variables to make room for.


Table statistic vs estimated rows query plan

You are checking the statistics for changes in the wrong place in your code. They won't be auto updated immediately after the delete. This will happen when a query is executed or compiled that needs them (and in fact your code contains no query that needs them anyway apart from the delete itself).

But SQL Server does maintain cardinality information for the partitions of the table/indexes as a whole after every insert/delete etc.

You can see this with the below (only returns 1 row as the table is not partitioned and has no indexes)

SELECT row_count
FROM   sys.dm_db_partition_stats
WHERE  object_id = object_id('dbo.TestPerf');

This shows it that the current row count is 750. The query

SELECT *
FROM   dbo.TestPerf

Doesn't benefit from column statistics. It can just use that directly and even if statistics are disabled will be accurate.

A query with WHERE  ID > 0 would use the statistics and return the whole table. As it happens the statistics don't get updated in your case anyway when such a query is executed as you haven't hit the threshold for modifications for an auto update to occur. But the estimates are still accurate.

The statistics records that there were 1,000 rows at sampling time.

So it would be simple to just scale the estimates down to adjust for the new table cardinality by multiplying by 0.75.

The scaling down works fine for WHERE Id>0 as 1,000 * 0.75 gives the correct result but if you try querying the 250 rows that you just deleted this will not show correct estimates.

The statistics indicate that it will return 250 rows, multiplying that by 0.75 gives 187.5.


How to scatter/arrange all the tables in database model (workbench)?

There's no perfect layout tool. What you get is the best you can get automatically. After that fine tune your layout manually. Btw, make sure you have enough space for all objects by adding more pages if necessary (see menu -> Model -> Diagram Properties and Size...) before you run the auto layout.


You can

CREATE EXTENSION pageinspect;

Then you can use the function

SELECT * FROM bt_page_stats('indexname' , 42);

to get some information about block 42 of the index: the number of live and dead entries, the amount of free space and the pointers to the left and right sibling (btpo_prev and btpo_next) and the type (root, intermediate or leaf).


How can I justify making changes to my redo logs?

You don't necessarily need to duplex online redo. But if you have the disk space it should not hurt anything. You should probably add additional log groups. That will give your database more time to archive each online redo log before Oracle needs to use it again. Try adding 3 additional online redo log groups.


Stored procedure support in MySQL Workbench?

In  workbench you find all to stored procedures, when you make a right cick on stored procedures.
In your case use Alter Stored procedures
It is basically all the same for view tables columns etc.


Retaining access to table that will be dropped and recreated

Answer left in comments by spaghettidba

Do you have control over the schema where the table gets created?

If so, you could grant privileges on the schema.

Another option is a DDL trigger to grant permissions on the table upon creation.


bridge table joined table have both the keys equal to both the keys

If I understand the task correctly, then look for

SELECT mt.id, mt.name
FROM main_table AS mt
JOIN bridge_table AS bt ON bt.main_table_id = mt.id
JOIN other_table AS ot ON bt.other_table_id = ot.id
GROUP BY mt.id, mt.name
HAVING 2 = COUNT( DISTINCT CASE WHEN ot.name IN ('Other 1', 'Other 2') THEN ot.name END )
    OR 1 = COUNT( DISTINCT CASE WHEN ot.name IN ('Other 3')            THEN ot.name END )

To make it dynamic use

-- constant part
HAVING 1 = 0
-- dynamic parts
    OR (cnt_1 = COUNT( DISTINCT CASE WHEN ot.name IN ( list_1 ) THEN ot.name END ))
    OR (cnt_2 = COUNT( DISTINCT CASE WHEN ot.name IN ( list_2 ) THEN ot.name END ))
-- ...

where list_N - values list, and cnt_N - unique values count in the list.


Is there a way to specify which schema the database task "Export Data-tier Application" exports for the other entities besides the tables?

There is an option to include stored procedures in export data, uncheck the option  to exclude stored procedures from exporting data


Troubleshooting vanishing fulltext index in SQL Server 2012

Catalog and index are separate. What do you see in sys.fulltext_indexes? Did you confirm data there after the index was created?

You could check for recent Object:Deleted events in the default trace:

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
-- AND StartTime > 'yyyymmdd' -- rough date/time you created the index
ORDER BY StartTime DESC;

Now, your default trace may be very busy with a lot of relevant events, in which case the drop could have rolled off the current set of trace files, but this is a rare scenario.

Another case where it might not show up in the default trace is if the database was restored to an earlier point in time - you may find some evidence of this in SQL Server's error log (though this only keeps so much history too - if you've restarted SQL Server six times, for example, any information from before that is gone).


Possible to use libmysqlclient-dev.lib functions (native c client) with mySQL Router?

The immediate answer, without more information, is that you are trying accessing MySQL (the protocol, not the server), using unix socket/named pipes, which is the only reason I can think from the top of my mind why a mysql connector may want to open a file. Check your connection options and make sure you are connecting using TCP, with a host, port combination.

It could be something else, but it is what I would try debugging first, for other issues we would need more context and code.


SQL Server - Any way to validate insert statements for mismatching column names?

I don't think there's any good way to automate this. Especially since most people, in my experience, who use INSERT ... SELECT have not written the SELECT specifically for the INSERT, but rather copied it from somewhere else. If they have written the SELECT for the INSERT, it's unlikely they went to the trouble of adding column aliases (whether the source is another table with different column names, or just a series of constants), and if they did add column aliases, it's not guaranteed that they named them in such a way that there is a 1:1 mapping  of column names. If we really are talking about INSERT ... SELECT (a bunch of constants), it's much more likely to be INSERT ... VALUES().

You could submit a feature request to Microsoft, but I think you'd have to lay out the use case very thoroughly for this to gain traction over the many other improvements the community wants to see.


ALTER DATABASE command to set log filegrowth and size?

You already wrote it, you just need to specify logical file name of the log file.

ALTER DATABASE [NewDatabase]
MODIFY FILE
(NAME = 'NewDatabase_log',
SIZE = 200MB,
FILEGROWTH = 1MB)


pgAdmin crashes or freezes when big MATERIALIZED VIEWs are refreshing

I seems that it's a known bug.
I'm answering to my own question with this workaround.

If you use PostgreSQL 9.4+, one workaround is to use REFRESH MATERIALIZED VIEW CONCURRENTLY.

From PostgreSQL's manual:

  CONCURRENTLY

  Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which
  affects a lot of rows will tend to use fewer resources and complete
  more quickly, but could block other connections which are trying to
  read from the materialized view. This option may be faster in cases
  where a small number of rows are affected.

  This option is only allowed if there is at least one UNIQUE index on the materialized view which uses only column names and includes all
  rows; that is, it must not index on any expressions nor include a
  WHERE clause.

You just need to add an UNIQUE INDEX to your materialized view and to refresh it CONCURRENTLY:

-- add index (do it only once per matview)
CREATE UNIQUE INDEX ON very_big_matview (id);
-- refresh concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY very_big_matview;

But it does not prevent other users from running long refreshes which hangs my pgAdmin.


Per JimmyStenke's suggestion, I realized I had a DB initialized from a previous install, and it seems that was causing the error. I deleted it and initialized a new DB.


You need to use escaped single quotes in your EXECUTE IMMEDIATE statement, like so:

EXECUTE IMMEDIATE 'INSERT INTO ET_SEVERITY_LEVEL(ID_SEVERITY_LEVEL, CODE, DESCRIPTION) VALUES (1, ''DEBUG'', ''Debugging'')';


How can I detect what's changing my scoped configurations?

Well, you won't have any history, but you can catch this in the act in the future, since SQL Server Audit can do this, using DATABASE_OBJECT_CHANGE_GROUP:

USE master;
GO

CREATE SERVER AUDIT ServerAudit
  TO FILE (FILEPATH = 'C:\path\to\audit\folder\', MAXSIZE = 1 GB)
  WITH (ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT ServerAudit
  WITH (STATE = ON);
GO

Now, for each database:

USE database_name;
GO

CREATE DATABASE AUDIT SPECIFICATION CatchDBConfigChangers
  FOR SERVER AUDIT ServerAudit
  ADD (DATABASE_OBJECT_CHANGE_GROUP)
  WITH (STATE = ON);
GO

You'll have to create an audit specification per database, and this will collect more than just scoped configuration changes, but once you have set up your databases, you can start checking for details immediately:

SELECT
  [when]      = event_time,
  [app]       = application_name,
  [host]      = host_name,
  [ip]        = client_ip,
  [login]     = server_principal_name,
  [statement]
FROM sys.fn_get_audit_file
  ('C:\path\to\audit\folder\ServerAudit*', NULL, NULL)
WHERE LOWER([statement]) LIKE N'%scoped%configuration%'
-- AND database_name = N'database_name'
;

My results after one change to MAXDOP (click to enlarge):


Architectural Help Needed - MySQL or NoSQL for a table with 54 million records

Ah, what is the problem?

  a table with 54 million records

A small table. Nice. I have one here with 8.5 billion rows.

  we have !300 inserts and !100 updates per minute

Yeah. Small. I know. I have one here with around 500 million inserts per day. That is 347222.2222222222 per minute.

All that running on stock hardware. Seriously. THough not totally as low end as what you suggest.

But your metric is totally off. 54 million was large 25 years ago. Today it is small, unless someone tries to run a database server on a mini virtual machine.

For example a non-server with 5gb memory - ouch.

Generally - if you need analysis and this is not document style data - stick to MySql, avoid NoSql databases and learn the relational theory so that you know what you do there.

SSD is great, but I am not sure 5gb memory is decent - come on, that is less than a decent workstation has.

For daily / weekly / mnothly aggregations I would make DAILY aggregates, then use those as basis for larger aggregations. A month is only max. 31 days - so most of hte work is done once per day (in an off time). I strongly would reconsider using ebay-level cheap used hardware, though - and that is what the specs look like.


Convert to CTE or Window function or a set or a merge statement

Without knowing your RDBMS, and with the very vague description you're giving, here's my initial approach.

It's basically taking your Subquery and converting it to CTE.

I'd suggest a more elaborate WHERE clause on that CTE, as right now it returns All rows from that table (which can take a while depending on how much data you have).

Things get a bit hairy, as you're inner joining a table to itself, and using that join to update THAT same table.  I'd tread very lightly and ensure you have a good test environment.

Maybe converting "a" to CTE is a better approach than converting "b" to CTE (since "a" actually has a WHERE clause).  Or maybe having BOTH of them as CTE is an even better approach to simplify the UPDATE.

;WITH CTE_tblsname_KS
AS
(SELECT linked_pmt_id
      ,pmt_id
      ,prop_name
      ,prop_addr1
      ,prop_city
FROM tblname_KS (NOLOCK)
)

UPDATE a
SET a.linked_pmt_id = b.linked_pmt_id
  , a.looptag = '1Mb-NBCBZ'
FROM tblname_KS a
INNER JOIN CTE_tblsname_KS b
ON ISNULL(a.prop_name,'NULL') = ISNULL(b.prop_name,'NULL') AND
   ISNULL(a.prop_addr1,'NULL') = ISNULL(b.prop_addr1,'NULL') AND
   a.prop_city = b.prop_city
WHERE   a.linked_pmt_id IS NULL AND
    (a.status <> 'U' AND  a.addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND a.addr_type IS NOT NULL) AND
    a.prop_city IS NOT NULL AND
    a.prop_zip IS NULL


Just remove community version (packets) and install enterprise version. Simple, nothing else needed. All those enterprise version features have been compiled into.

If you have replica set, you can do this without down time, just do change one (secondary) node at time.


You don't need a the function at all, this can be done with a single SQL statement:

with recursive tree as (id, parent) (
    select link as id,
           parent
    from linktable
    where id = itemid

    union all

    select c.link as id,
           c.parent
    from linktable c
      join tree p on p.id = c.parent
)
select dt.id, dt.value
from tree
  join datatable dt on dt.id = tree.id

Please see the manual for an introduction to recursive queries: http://www.postgresql.org/docs/current/static/queries-with.html


MySQL: replicating to a different table engine type

I will answer each question as follows:

  1: is statement-based replication preferred over the row-based variant or vice-versa?

If you are replicating to a Slave with all MyISAM tables, statement-based is more sensible because the SQL statement would be more compact to log and ship.

If the Slave has all InnoDB, row-based is better because of the granular changes. Just be willing to live with bloated binary logs.

  2: does either the InnoDB or the MyISAM side need some specific settings so data consistency would not break all by itself as the result of "normal" replication activity?

Data consistency falls out of the picture with MyISAM. Measures for crash-safe replication always falls in favor of InnoDB. MySQL 5.6 supports storing log file and position in an InnoDB table.

  3: are DDL executions on the master handled on the slave in a sane manner?

Yes

  4: how would I prevent an ALTER TABLE blah ENGINE=InnoDB statement run at the master from propagating to the slave?

Run it like this:

SET SQL_LOG_BIN=0;
ALTER TABLE blah ENGINE=InnoDB;
SET SQL_LOG_BIN=1;

  5: Is there a recommended way of setting up such a "split" setup? I could think of a direct ALTER TABLE blah ENGINE=MyISAM on the slave, is this viable?

Yes, I have recommended this before : Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

  6: any other caveats one should know about?

All the caveats you needs are in this post : Using MyISAM for reading and InnoDB for writing data


Hiding a SQL Server vs Turning Off SQL Browser

Can anyone explain the difference between turning off the SQL Browser service and Hiding an instance through the SQL Server Configuration Manager?

To hide an instance of SQL Server, you need to set HideInstance flag to YES.

When you hide an instance, it will indicate that sql server browser should NOT respond with information about this server instance. Note that hidden instances support only shared memory connections. TCP/IP is allowed (as long as the port is 1433 or you specify) [Thanks to @AaronBertrand for highlighting this].

SQL Server uses the SQL Server Browser service to enumerate instances of the Database Engine installed on the computer. This enables client applications to browse for a server, and helps clients distinguish between multiple instances of the Database Engine on the same computer.

Refer : Hide an Instance of SQL Server Database Engine and Why listing all of your SQL Servers does not always work ?


For special characters (% and _), you need to escape them:

WHERE column LIKE '%[%]%';

or

WHERE column LIKE '%\%%' ESCAPE '\';

This is covered in the documentation for LIKE, in the sections "Using Wildcard Characters as Literals" and "Pattern Matching with the ESCAPE Clause." Your first stop should always be the official documentation.


SQL Server Migration restore backup vs copy data and log files

For speed (eg less downtime) backup/restore. Backup/restore is quicker because of Instant File Initialisation (only for MDF). The actual backup file is a lot smaller then the MDF/LDF sizes so copies quicker.

It's arguably slighty less risky because there is no downtime on the SQL 2000 instance.

However, detach removes access to the old database. You'll have to take this offline with a backup/restore. If there is a risk of end users connecting, use this (or take offline as soon as backup is complete)

Basically, it's whatever suits you best. I've used both methods for a recent server migration.


If you were using a person's name as a primary key and their name changed you would need to change the primary key.  This is what ON UPDATE CASCADE is used for since it essentially cascades the change down to all related tables that have foreign-key relationships to the primary key.

For example:

USE tempdb;
GO

CREATE TABLE dbo.People
(
    PersonKey VARCHAR(200) NOT NULL
        CONSTRAINT PK_People
        PRIMARY KEY CLUSTERED
    , BirthDate DATE NULL
) ON [PRIMARY];

CREATE TABLE dbo.PeopleAKA
(
    PersonAKAKey VARCHAR(200) NOT NULL
        CONSTRAINT PK_PeopleAKA
        PRIMARY KEY CLUSTERED
    , PersonKey VARCHAR(200) NOT NULL
        CONSTRAINT FK_PeopleAKA_People
        FOREIGN KEY REFERENCES dbo.People(PersonKey)
        ON UPDATE CASCADE
) ON [PRIMARY];

INSERT INTO dbo.People(PersonKey, BirthDate)
VALUES ('Joe Black', '1776-01-01');

INSERT INTO dbo.PeopleAKA(PersonAKAKey, PersonKey)
VALUES ('Death', 'Joe Black');

A SELECT against both tables:

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonKey = pa.PersonKey;

Returns:

If we update the PersonKey column, and re-run the SELECT:

UPDATE dbo.People
SET PersonKey = 'Mr Joe Black'
WHERE PersonKey = 'Joe Black';

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonKey = pa.PersonKey;

we see:

Looking at the plan for the above UPDATE statement, we clearly see both tables are updated by a single update statement by virtue of the foreign key defined as ON UPDATE CASCADE:

click the image above to see it in more clarity

Finally, we'll cleanup our temporary tables:

DROP TABLE dbo.PeopleAKA;
DROP TABLE dbo.People;

The preferred1 way to do this using surrogate keys would be:

USE tempdb;
GO

CREATE TABLE dbo.People
(
    PersonID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_People
        PRIMARY KEY CLUSTERED
    , PersonName VARCHAR(200) NOT NULL
    , BirthDate DATE NULL
) ON [PRIMARY];

CREATE TABLE dbo.PeopleAKA
(
    PersonAKAID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_PeopleAKA
        PRIMARY KEY CLUSTERED
    , PersonAKAName VARCHAR(200) NOT NULL
    , PersonID INT NOT NULL
        CONSTRAINT FK_PeopleAKA_People
        FOREIGN KEY REFERENCES dbo.People(PersonID)
        ON UPDATE CASCADE
) ON [PRIMARY];

INSERT INTO dbo.People(PersonName, BirthDate)
VALUES ('Joe Black', '1776-01-01');

INSERT INTO dbo.PeopleAKA(PersonID, PersonAKAName)
VALUES (1, 'Death');

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonID = pa.PersonID;

UPDATE dbo.People
SET PersonName = 'Mr Joe Black'
WHERE PersonID = 1;

For completeness, the plan for the update statement is very simple, and shows one advantage to surrogate keys, namely only a single row needs to be updated as opposed to every row containing the key in a natural-key scenario:

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonID = pa.PersonID;

DROP TABLE dbo.PeopleAKA;
DROP TABLE dbo.People;

The output from the two SELECT statements above are:

Essentially, the outcome is approximately the same.  One major difference is the wide natural key is not repeated in every table where the foreign key occurs.  In my example, I'm using a VARCHAR(200) column to hold the person's name, which necessitates using a VARCHAR(200) everywhere.  If there are a lot of rows and a lot of tables containing the foreign key, that will add up to a lot of wasted memory.  Note, I'm not talking about disk space being wasted since most people say disk space is so cheap as to be essentially free.  Memory, however, is expensive and deserves to be cherished.  Using a 4-byte integer for the key will save a large amount of memory when you consider the average name length of around 15 characters.

Tangential to the question about how and why keys can change is the question about why to choose natural keys over surrogate keys, which is an interesting and perhaps more important question, especially where performance is a design-goal.  See my question here about that.

1 - http://weblogs.sqlteam.com/mladenp/archive/2009/10/06/Why-I-prefer-surrogate-keys-instead-of-natural-keys-in.aspx


SELECT TOP 1 DATEDIFF for each hour group

Assuming this is SQL Server, you can truncate datetime values to hours using this technique:

DATEADD(HOUR, DATEDIFF(HOUR, 0, @YourDateTimeValue), 0)

Using that as a grouping criterion, you can then use the FIRST_VALUE analytic function (assuming you are on SQL Server 2012 or later version) to get the first DATEDIFF(MINUTE, Created, Updated) per group:

SELECT DISTINCT
  x.CreatedHour,
  DiffMinutes = FIRST_VALUE(DATEDIFF(MINUTE, t.Created, t.Updated))
                OVER (PARTITION BY x.CreatedHour
                      ORDER BY t.Created ASC
                      ROWS UNBOUNDED PRECEDING)
FROM
  dbo.YourTable AS t
  CROSS APPLY
  (
    SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, t.Created), 0)
  ) AS x (CreatedHour)
;

Since the FIRST_VALUE function would return the result for every row of your table, it would return identical values for entries created in the same hour, thus giving you a lot of duplicates. It is to suppress those duplicates that the above query is using DISTINCT.


Have a look at http://dev.mysql.com/doc/refman/5.5/en/sql-mode.html

You can set this for your current session, or in your mysql config

my.cnf (Unix operating systems) or my.ini (Windows)

sql-mode="modes"

On your production server use the following query to determine the current mode:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

On your development server you can set it for the current session with

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';


Why does sqlcmd -Lc not show the local instance?

Why does sqlcmd -Lc does not show the local instance?

and in your case even if sql server service is working.

ok first will talk about what sqlcmd -Lc does

it "Lists the locally configured server computers, and the names of the server computers that are broadcasting on the network" for reference

and a note to keep in mind is

"Because of the nature of broadcasting on networks, sqlcmd may not receive a timely response from all servers" for reference

SQL server browser service is the service that Broadcasts and upon startup, SQL Server Browser starts and claims UDP port 1434.

if you unplug your machine from the network and type sqlcmd -Lc, you wont be able to see your machine listed, because If it doesn't get a reply from other available SQLs , it doesn't list itself.

Now to know more about why sometimes sql server doesnt show up, you need to know about UDP and how sql server uses it to discover other sql servers

UDP
is a connection-less protocol
UDP does use ports, it allows multiple processes to be using UDP at the same time.
Since it's connection-less, any connection-like properties between two systems must be handled by the application.
It's a fairly small protocol and therefore takes up relatively little bandwidth on the wire.

SQL Server, uses UDP in two ways

To discover other SQL Servers that are out there.
To discover how to connect to a named instance.

now after knowing how sql server service works and how it uses UDP here below is list of reasons why you dont see your local instance showing up

The UDP communications gets "lost" on the way.
UDP port 1434 are blocked on the network with firewall or windows firewall or IPSEC policy

These might be the reasons and i suggest using a product like sqlping to discover your sql on the network, because if it doesnt show up it doesnt mean its not there, its just the way its broadcasting is the problem

Updates:

Make sure (HideInstance Flag is No)

1)In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for , and then select Properties.
On the Flags tab, in the HideInstance box, make sure its No.

2)
Make sure (Sql Browser Service is Active)
go to SQL Server Configuration Manager, check properties for SQL Browser service, in Advanced tab, make sure it is active & restart it

3) Finally try this registry Key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\SQL Browser\SsrpListener

make sure it has value 1
if you cant find it under 90 folder, search for SsrpListener


With the naive tree pattern (where you just list the immediate parent/child relationships, sometimes called the adjacency list pattern) the this would be done with a recursive CTE in DBs that support them, but mySQL doesn't. If you know that each tree will never be more than a certain depth you can construct a query that looks at all possible depths and picks the last not found like so:

SELECT COALESCE(t5.parent, t4.parent, t3.parent, t2.parent, t1.parent)
FROM   tree_table AS t1
LEFT OUTER JOIN tree_table AS t2 ON t2.child=t1.parent
LEFT OUTER JOIN tree_table AS t3 ON t3.child=t2.parent
LEFT OUTER JOIN tree_table AS t4 ON t4.child=t3.parent
LEFT OUTER JOIN tree_table AS t5 ON t5.child=t4.parent
WHERE  t1.child = @given_node_id

This fails if a tree becomes deeper than expected though, and is not terribly efficient. You could also consider using a stored procedure to loop up the tree until you get to the root, which would work more generally but is more limited in its use (the above could be turned into a view which could be used as part of other select statements, a stored procedure could not).

To help with queries like this you could consider altering your data structure to contain some unrolled data. This sometimes feels wrong (breaking normal form and requiring extra work on inserts, updates, and deletes) but can be very helpful. Three options are:

1: Maintain the path to a node in an extra column:

So the record (c="p4", p="p3") becomes (c="p4", p="p3", pa="/p1/p3/p4"). You have to maintain the paths when nodes are added or moved, and to work out the root you need to use a string function to extract the first item in the path.

2: Maintain a closure table containing every relationship between nodes and their ancestors:

This is like your existing table but contains all ancestors for each node with a depth marker so for p4 you would record:

node   ancestor   distance   depth
----------------------------------
p4     p1         2          1
p4     p3         1          2

You may not need both the depth and distance columns depending on the queries you need to support and wither you keep this as well as or instead of the existing structure.

To find the root from any given point just look for the ancestor with a depth of 1. Again you have extra work to do maintaining the structure on insert/update/delete.

Of course this all assumes you have a forest of trees, not just a single tree. If you have a single tree you only have one root it is will be the same for every node.

A reading recommendation that I often give is https://pragprog.com/book/bksqla/sql-antipatterns - that covers dealing with some common tree/forest related problems, including this one, in chapter 2.


Why might a table's data space take up 4x the size of the raw data?

After discussions in the comments on the original question, it appears in this case the lost space is caused by the choice of clustered key, which has led to massive fragmentation.

Always worth checking the state of fragmentation via sys.dm_db_index_physical_stats in these situations.

Edit: Following update in comments

The average page density (prior to rebuild of the clustered index) was 24%, which fits perfectly with the original question. The pages were only 1/4 full, so the total size was 4x the raw data size.


Occasional error shrinking transaction log files

There is NO NEED to run shrinkfile command after taking transaction log backup. What is the need of shrinking a log file which would eventually grow again? This is a blunder in my opinion considering the fact that you are doing it every day.

Whenever the log file grows, transactions have to wait for this event to complete so SQL server becomes still until this growth happens and considering the fact that instant file initialization is not there for log files it takes time for these growth events to complete, in turn hampering performance.

Regarding error message

  Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed

This means that you were trying to run dbcc shrinfile command when some backup operation was running on the database. It can be any backup full,differential and transaction log. All this message is saying that please try running shrikfile command when backup operation has completed. Now you have to find what backup was running at that time. See errrlog or msdb job history you could find more details.

If you refer to BOL

  Operations that cannot run during a database or transaction log backup
  include:

  • File management operations such as the ALTER DATABASE
  statement with either the ADD FILE or REMOVE FILE options.

  • Shrink database or shrink file operations. This includes auto-shrink
  operations.


Is there a way to see what were the Candidate Execution Plans generated by the Query Optimizer?

The optimizer has heuristics to cut down the search time by discarding plans and partial plans before they are fully explored if it believes they are worse than current best solutions it has seen so far in the search.  So, it doesn't actually fully generate all of the alternatives.  Also, the XML representation is really something that gets generated at the end of the search - the internal representation is somewhat different.  Unfortunately, there is no current public feature for you to explore other possible plans for the same query.  With a debugger we can look at the structure that holds all of this during the optimization process.  It's called the Memo.  You can read more about the Cascades framework on which SQL's optimizer is based here


How can I log the way in which a job was started?

Here is script to get running jobs status along with Schedule information

SELECT  sj.name JobName,
        sj.start_step_id StartStepID,
        s.name JobScheduleName
FROM    msdb.dbo.sysjobactivity AS sja
        INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
        INNER JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = sj.job_id
        INNER JOIN msdb.dbo.sysschedules AS s ON js.schedule_id = s.schedule_id
WHERE   sja.start_execution_date IS NOT NULL
        AND sja.stop_execution_date IS NULL


Put in the wrong data type for pk, cannot change because it is FK for another table

You first need to drop the foreign key constraint between the two tables. After that you can alter the column the employee table. You may need to drop the primary key constraint as well as any other indexes on the Department table that has DeptId in it before you update the column, but you can try altering the column before you drop the indexes/pk. Once you have the column altered you can reapply the primary key constraint and the other indexes you removed from the Department table as well as the foreign key constraint between the Department table and the Employee table.

alter table employee drop constraint <put constraint name here>

If you use SMSS to generate the drop and create scripts this will save you time and ensure that you keep the names as well as the columns that are used in the index

Drop index <index name here> on Department

You will need to repeat the above for each index

alter table Department drop constraint <put primary key name here>

alter table Department alter column DeptId char(8) not null

alter table Department add constraint [your pk name] primary key(DeptId)

alter table employee add constraint [foreign key name] foreign key DeptId references Department (DeptId)

Run the create portion of the index scripts that you created


The replication setup you have cannot be maintained when upgrding to MySQL 5.5

You will have to go with Master/Slave. Here is the reason:

Someone asked a question about gibberish appearing in a binary log and I answered this question

In my answer, I explained that binary logs start at different positions in different versions of MySQL

Here are those positions:

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0 and back

With replication, these numbers apply to the relay logs as well. The net effect is that a Slave can replicate from an older version of MySQL, but not the other way around.

You are better off doing the following:

Convert Master/Master to Master/Slave
Upgrade to MySQL 5.5 on the Slave
Point all apps at the Slave (now called NewMaster)
service mysql stop on the Master
Upgrade to MySQL 5.5 on the Master (now called NewSlave) but do not start mysql
service mysql stop on NewMaster
rsync NewMaster's /var/lib/mysql to NewSlaves's /var/lib/mysql
rm /var/log/mysql/master.info on NewMaster
rm /var/log/mysql/master.info on NewSlave
service mysql start --skip-networking on NewMaster
run RESET MASTER on NewMaster
service mysql restart on NewMaster
service mysql start on NewSlave
run RESET MASTER on NewSlave
setup Master/Master Replication using position 107 as start master_log_pos

With regard to using yum to upgrade, I run in panic and scream in horror at the thought of it.

Here is my more conservative approach:

I would mysqldump everything EXCEPT the mysql schema.
Run this to generate SQL for all mysql user grants:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > /root/MySQLGrants.sql
Uninstall MySQL 5.1
Install MySQL 5.5 from RPMs by hand.
Load /root/MySQLGrants.sql into MySQL 5.5
Load the mysqldump'd data back into MySQL 5.5


Ok, figured it out with the comments from my original question. Steps I used to connect to the correct instance and get the account unlocked and password reset.

Connect to the correct SID/instance off the top. Example:

c:\sqlplus SYSTEM/<Password>@<service name> --> this can be found in the tnsnames.ora file
I then verified I was in the correct instance:

SQL>select instance_name, status from v$instance;
From there I was able to unlock the user and reset their password:

SQL>alter user <USERNAME> account UNLOCK;

SQL>alter user <USERNAME> identified by <PASSWORD>;

My problem intially was that I was just trying to connect using sqlplus / as sysdba and it wasn't putting me in the correct instance.

Hopefully this will be helpful to other Oracle devs and rookie DBA's (I'm a rookie in this since we have a dev instance for some client work and it rarely gets touched).


Normal.
On some operating systems, MySQL shows up as a single process.  (There are probably "threads" inside it.)
On other operating systems, there is one main process, but each connection has its own process.  This is what you are seeing.
A combination of max_connections, "connection pooling", thread_cache_size, user activity, and maybe some other things, lead to how many processes you will see.
Is it an issue?  Generally, no.  If you have configured things poorly, other clues will be more important than the number of processes in ps or top or iotop.  You are very likely to see that most of them are in "Sleep" mode when you do SHOW PROCESSLIST.  If you see lots of non-Sleep connections, the you may be missing some indexes (especially "composite" indexes) or have poorly formulated queries.  The slowlog is a better way to look for those.


All procedures have an integer return value.  Your method would work, there is no hard and fast rule against it.

Alternatively, you could return an output parameter.

Example:

create procedure YourProcedure
  @ID output
as
  insert into YourTable (bunch of values)
  select @ID = scope_identity()
go

You then call your procedure as follows:

declare @TheID int
exec YourProcedure @TheID output
select @TheID


after executing a back up cmd (via query) how to kill it

There are many ways to kill the backup - exactly the same as you would any query.

ALT+Break in SSMS
Right-click "kill process" from the activity monitor
CTRL+C in sqlcmd
and so on...(these seemed most appropriate given the implied knowledge of the asker)

It probably won't end immediately but just give it a little time. Also I'd say give it more time in general, a backup of a large DB can take some time and may not start producing stat updates immediately. Check out previous backup times as a guide if possible.


Normalization with two potential primary keys

Here is where I went wrong:

The important thing about transitive dependencies are these two key factors:

If B depends on A, and C depends on B, then C also depends on A (A → B → C)
B is not (or part of) a candidate key

In my example, I identified the following as a transitive dependency:

{ email } → { first_name, last_name }

However, this is not the case, as email is a candidate key, and so instead, if we use id as the primary key, we have a full dependency:

{ id } → { email, first_name, last_name }


SQL db2 remove specific item from the query

Following WHERE clause shold do the job

WHERE COR_ID <> '90003'
  AND (VAL_DT < CURRENT DATE  OR MVT_DS in ('NOSTRO', 'SCREV', 'SCRIN', 'MAN')  )


What is the most efficient way to count the number of rows in a table?

Generally, if you don't need an exact count, there is a much faster way:

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'image'::regclass;

Fast way to discover the row count of a table

As a matter of fact, in a DB with concurrent write access every count is an estimate, because the number may be outdated the instant you get it.

But, like @a_horse commented, there is something off in your DB. Counting a million should not take more than a few seconds in the worst case.

That your app will never delete images from this table makes this even more suspicious, because there shouldn't be many dead rows then. (Or are you updating a lot?) A huge amount of dead tuples could slow you down - and call for VACUUM. Normally, autovacuum takes care of this. Did you enable it? (It's the default in modern Postgres.)

Are regular VACUUM ANALYZE still recommended under 9.1?

Check for dead tuples:

Measure the size of a PostgreSQL table row

All the usual advice for performance optimization applies.