Why would the Parallelism (Repartition Streams) Operator Reduce Row Estimates to 1? I'm using SQL Server 2012 Enterprise. I've come across a SQL Plan that is exhibiting some behavior I don't find entirely intuitive. After a heavy Parallel Index Scan operation, a Parallelism (Repartition Streams) operation occurs, but it is killing the row estimates being returned by the Index Scan (Object10.Index2), reducing the estimate to 1. I've done some searching, but haven't come across anything that explains this behavior. The query is quite simple, though each of the tables contain records in the low millions. This is part of a DWH load process and this intermediate data set is touched a few times throughout, but the question I have is related to the row estimates in particular. Can someone explain why accurate row estimates go to 1 within the Parallelism (Repartition Strems) Operator? Also, is this something I should be concerned with in this particular situation?

I've posted the full plan to Paste the Plan.

Here's the operation in question:

Including the Plan Tree in case that adds any more context:

Could I be running into some variation of this Connect item filed by Paul White (further in-depth explination on his blog here)?  At least it's the only thing I've found that seems to be even remotely close to what I'm running into even though there is no TOP operator in play.

Query plans with bitmap filters can sometimes be tricky to read. From the BOL article for repartition streams (emphasis mine):

  The Repartition Streams operator consumes multiple streams and produces multiple streams of records. The record contents and format are not changed. If the query optimizer uses a bitmap filter, the number of rows in the output stream is reduced.

In addition, an article on bitmap filters is also helpful:

  When analyzing an execution plan containing bitmap filtering, it is important to understand how the data flows through the plan and where filtering is applied. The bitmap filter and optimized bitmap is created on the build input (the dimension table) side of a hash join; however, the actual filtering is typically done within the Parallelism operator, which is on the probe input (the fact table) side of the hash join. However, when the bitmap filter is based on an integer column, the filter can be applied directly to the initial table or index scan operation rather than the Parallelism operator. This technique is called in-row optimization.

I believe that's what you're observing with your query. It is possible to come up with a relatively simple demo to show a repartition streams operator reducing a cardinality estimate, even when the bitmap operator is IN_ROW against the fact table. Data prep:

create table outer_tbl (ID BIGINT NOT NULL);

INSERT INTO outer_tbl WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

create table inner_tbl_1 (ID BIGINT NULL);
create table inner_tbl_2 (ID BIGINT NULL);

INSERT INTO inner_tbl_1 WITH (TABLOCK)
SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 2000000 - 2) NUM
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

INSERT INTO inner_tbl_2 WITH (TABLOCK)
SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 2000000 - 2) NUM
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Here is a query that you should not run:

SELECT *
FROM outer_tbl o
INNER JOIN inner_tbl_1 i ON o.ID = i.ID
INNER JOIN inner_tbl_2 i2 ON o.ID = i2.ID
OPTION (HASH JOIN, QUERYTRACEON 9481, QUERYTRACEON 8649);

I uploaded the plan. Take a look at the operator near inner_tbl_2:

You may also find the second test in Hash Joins on Nullable Columns by Paul White helpful.

There are some inconsistencies in how the row reduction is applied. I was only able to see it in a plan with at least three tables. However, the reduction in expected rows seems reasonable with the right data distribution. Suppose that the joined column in the fact table has many repeated values that aren't present in the dimension table. A bitmap filter might eliminate those rows before they reach the join. For your query the estimate is reduced all the way to 1. How the rows are distributed amongst the hash function provides a good hint:

Based on that I suspect that you have a lot of repeated values for the Object1.Column21 column. If the repeated columns happen to not be in the stats histogram for Object4.Column19 then SQL Server could get the cardinality estimate very wrong.

I think that you should be concerned in that it might be possible to improve the performance of the query. Of course, if the query meets response time or SLA requirements then it may not be worth further investigation. However, if you do wish to investigate further there are a few things that you can do (other than updating stats) to get an idea on if the query optimizer would pick a better plan if it had better information. You could put the results of the join between Database1.Schema1.Object10 and Database1.Schema1.Object11 into a temp table and see if you continue to get nested loop joins. You could change that join to a LEFT OUTER JOIN so the query optimizer won't reduce the number of rows at that step. You could add a MAXDOP 1 hint to your query to see what happens. You could use TOP along with a derived table to force the join to go last, or you could even comment out the join from the query. Hopefully these suggestions are enough to get you started.

Regarding the connect item in the question, it is extremely unlikely that it is related to your question. That issue doesn't have to do with poor row estimates. It has to do with a race condition in parallelism that causes too many rows to be processed in the query plan behind the scenes. Here it looks like your query isn't doing any extra work.


What is contained in the controlfile of oracle database? As we all know that controlfile contains datafile locations and redo log locations along with their names.It also contains dbname,timestamp,checkpoint info,log sequence number,rman backup info.In addition to this,what more a controlfile contains? Does it contain user backup info?

Lets define the controlfile in oracle: it is a small binary file that records the physical structure of the database and its include:

1) The database name.

2) Names and locations of associated datafiles and redo log files.

3) The timestamp of the database creation

4) The current log sequence number

5) Checkpoint information


SQL Server 2005 upgrade service pack via command line I have been wracking my brain over upgrading sql server 2005 to sql server 2005 sp4. I have a script for installing new instances of sql servers for all versions and they all work wonderfully. Problem is, sql server 2012 can't upgrade sql 2005 SP3 and below. So, I have to install up to sql 2005 SP4, AND THEN upgrade through sql 2012, which is highly annoying.

I need to do this silently via the command line, but I cannot get it to work at all. Here are some samples I tried:

 "SQLSP4.exe /qb /ACTION=Upgrade /INSTANCENAME="FOOBAR""
 "SQLSP4.exe /quiet /instancename="FOOBAR""

among a couple other variations. I could not find anything online at all. In fact, the one helpful thread ended in saying that Microsoft didn't have any support for this, but I'm not sure about that.

What can be done here? Does anyone have any experience in updating service packs via command line?

I have used below in past to upgrade all instances running sql 2005 to SP4. Just put this in a .bat file

@echo off
set hour=%time:~0,2%
if "%hour:~0,1%"==" " set hour=0%time:~1,1%
echo      Started at %date% %time%
SQLServer2005SP4-KB2463332-x64-ENU.exe /allinstances /quiet

echo      Completed at %date% %time%


Oracle 9: Convert date from mm/dd/yyyy hh:mm:ss format to iso8601 formatted datetime Note: I only have read access to this database.

I need to convert an Oracle 9 date type from d/mm/yyyy hh:mm:ss format to the iso8601 standard. Like the example below:

4/22/2015 12:02:56 AM => 2015-04-22T04:02:56.000Z

Below is a a query that will convert an Oracle date type to a iso8601-formatted string. I need an is8601 formatted date.

SELECT sysdate,
    to_char((from_tz(to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'America/New_York') at time zone 'UTC'),'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"') "iso8601"
FROM dual

Thanks for any and all help.

EDIT: The end goal is to grab these dates and insert them as datetimes into a postgres database that powers a rails app.

You should use SYSTIMESTAMP instead of SYSDATE, in order to get fractional seconds (which you desire as I assume).

SYSTIMESTAMP returns data type TIMESTAMP WITH TIME ZONE, thus you don't have to specify explicitly.

So it is:

SELECT
    TO_CHAR(
        SYSTIMESTAMP AT TIME ZONE 'UTC',
        'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
    )
FROM dual


Regarding sync issue in mobilink sever E. <52> [-10117] Stream Error: Internal error: DelayExpired: _state.http_state returned 0.
W. <52> [10088] The current SQL statement has been running for too long in the consolidated database.  The connection ID is SPID '76'

I am unable to sync the server db to android mobile and it gives the above error.

By restarting SQL Server and MobiLink seems to have resolved.But it is taking too long time to sync the 10mb data.


Xen snapshots and live Innodb tables? I am wondering if I can trust my host doing it's nightly snapshots with live Innodb tables.
From what I have read:
"Innodb is ACID-compliant, so you can "safely" use fs snapshot with Innodb tables."

Is this true?
Can you safely snapshot Mysql instances with large Innodb tables and have no ill effects?
Is this documented anywhere?

Snapshot are a common practice for backups. See http://www.mysqlperformanceblog.com/2009/02/05/disaster-lvm-performance-in-snapshot-mode/

For this to work safely though you'll want to ensure innodb_flush_log_at_trx_commit is set to 1.

That being said, the general practice with backups is test restoring them.  You want to be sure your backup method is working functionally and you want to be sure you know the steps to restore them.  If you're having to restore a backup it likely means your in a situation you're loosing money while it's down.  You should be able to restore a backup while being groggy after getting a 3am wake up call.

If you have a dev environment you might setup a weekly script to restore the backup into your dev environment.

You now have a push button script to restore in a pinch; just point it to production instead of dev
Your dev env. now has a production style dataset to work against. (You may want to have a secondary scrub script to anonymize customer, financial or other sensitive information).
You get automatic weekly tests of your restore process.  If there's a problem you can be alerted of it and fix it before it's needed for real.


Need a solution on dataset as given below Data Set:
col1,col2,col3,col4.col5
(A,b,c,d,e)
(A,z,p,q,r)
(F,s,t,y,v)
(F,w,x,y,z)
(F,p,x,r,t)
(G,m,n,o,p)
(G,m,i,q,s)

Actually I need result set that has all the columns and only single row for each capital letter values among the all same capital letter values.

Result set should be like:
(A,b,c,d,e)
(F,p,q,r,t)
(G,k,i,q,s)

or
(A,z,p,q,r)
(F,w,x,y,z)
(G,m,n,o,p)

any of three rows.

  Actually I have detail records of so many companies in which some
  where company name are redundant and I have to select single records
  for each company on the basis of their name. Here I use Capital
  letters for company name.

  I hope you realize the question well.

  Please provide a select statement I will be thankful to you all.

;with LetterSet as
(
    select *,
        row_number() over(partition by FirstCol order by SecondCol) as rownum
    from YourLettersTable
)
select
    FirstCol,
    SecondCol,
    ThirdCol,
    FourthCol,
    FifthCol
from LetterSet
where rownum = 1

Likewise, you could have rownum = 2 for your second result set.


Multiple rows v/s multiple columns My requirement is as follows:  An app with internationalization support and starting with EN and FR versions of text. So, I have 2 approaches for data in the table:

1. One column for each language:  So, my table columns will be as below, which means it will have one column for each language. So, in future if I add one more language, lets say German then I will add one more column LABEL_GM and will write update queries to add German labels.

    "LABEL_EN" VARCHAR2(1000 BYTE),
    "LABEL_FR" VARCHAR2(1000 BYTE),

2. One row for each language:  So, my table columns will be as below, which means it will have one row for each language. So, in future if I add one more language, lets say German then I will write insert queries to add German labels.

    "LABEL" VARCHAR2(1000 BYTE),
    "LANGUAGE" VARCHAR2(2 BYTE),

Questions:

Which is more space and performance efficient way in today situation when I am having only 2 language support.
Which is more space and performance efficient way from future perspective, in case if we need to add one more language support, so main concern is how efficient will be data insertion/updation.

This question is very similar with your other question.

As already stated there, I would prefer a translation table like this:

Table Translations

Code (PK) (Your translation code)
type (mobile, desktop, maybe something else later)
language_id (your language code - 1:EN, 2:FR, 3:DE,...)
value (your translation)


Can SQL Server system tables be defragmented? We have several databases in which a large number of tables are created and dropped. From what we can tell, SQL Server does not conduct any internal maintenance on the system base tables, meaning that they can become very fragmented over time and bloated in size. This puts unnecessary pressure on the buffer pool and also negatively impacts the performance of operations such as computing the size of all tables in a database.

Does anyone have suggestions for minimizing fragmentation on these core internal tables? One obvious solution could to avoid creating so many tables (or to create all transient tables in tempdb), but for the purpose of this question let's say that the application does not have that flexibility.

Edit: Further research shows this unanswered question, which looks closely related and indicates that some form of manual maintenance via ALTER INDEX...REORGANIZE may be an option.

Initial research

Metadata about these tables can be viewed in sys.dm_db_partition_stats:

-- The system base table that contains one row for every column in the system
SELECT row_count,
    (reserved_page_count * 8 * 1024.0) / row_count AS bytes_per_row,
    reserved_page_count/128. AS space_mb
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.syscolpars')
    AND index_id = 1
-- row_count:       15,600,859
-- bytes_per_row:   278.08
-- space_mb:        4,136

However, sys.dm_db_index_physical_stats does not appear to support viewing the fragmentation of these tables:

-- No fragmentation data is returned by sys.dm_db_index_physical_stats
SELECT *
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('sys.syscolpars'),
    NULL,
    NULL,
    'DETAILED'
)

Ola Hallengren's scripts also contain a parameter to consider defragmentation for is_ms_shipped = 1 objects, but the procedure silently ignores system base tables even with this parameter enabled. Ola clarified that this is the expected behavior; only user tables (not system tables) that are ms_shipped (e.g. msdb.dbo.backupset) are considered.

-- Returns code 0 (successful), but does not do any work for system base tables.
-- Instead of the expected commands to update statistics and reorganize indexes,
-- no commands are generated. The script seems to assume the target tables will
-- appear in sys.tables, but this does not appear to be a valid assumption for
-- system tables like sys.sysrowsets or sys.syscolpars.
DECLARE @result int;
EXEC @result = IndexOptimize @Databases = 'Test',
        @FragmentationLow = 'INDEX_REORGANIZE',
        @FragmentationMedium = 'INDEX_REORGANIZE',
        @FragmentationHigh = 'INDEX_REORGANIZE',
        @PageCountLevel = 0,
        @UpdateStatistics = 'ALL',
        @Indexes = '%Test.sys.sysrowsets.%',
        -- Proc works properly if targeting a non-system table instead
        --@Indexes = '%Test.dbo.Numbers.%',
        @MSShippedObjects = 'Y',
        @Execute = 'N';
PRINT(@result);

Additional requested info

I used an adaptation of Aaron's query below the inspect system table buffer pool usage, and this found that there are tens of GB of system tables in the buffer pool for just one database, with ~80% of that space being free space in some cases.

-- Compute buffer pool usage by system table
SELECT OBJECT_NAME(p.object_id),
    COUNT(b.page_id) pages,
    SUM(b.free_space_in_bytes/8192.0) free_pages
FROM sys.dm_os_buffer_descriptors b
JOIN sys.allocation_units a
    ON a.allocation_unit_id = b.allocation_unit_id
JOIN sys.partitions p
    ON p.partition_id = a.container_id
    AND p.object_id < 1000 -- A loose proxy for system tables
WHERE b.database_id = DB_ID()
GROUP BY p.object_id
ORDER BY pages DESC

Are you sure you have positively and accurately identified this system table as the sole source of "unnecessary pressure on the buffer pool and also negatively impacts the performance of operations such as computing the size of all tables in a database"? Are you sure this system table isn't self-managed in such a way that (a) fragmentation is minimized or kept in check secretly or just (b) managed efficiently in memory so that defragmentation levels really don't affect anything much?

You can see how many pages are in use, and you can see how much free space is on the pages that are in memory (page_free_space_percent is always NULL in the allocations DMF, but this is available from the buffer DMV) - this should give you some idea if what you're worrying about is really something you should be worrying about:

SELECT
  Number_of_Pages = COUNT(*),
  Number_of_Pages_In_Memory = COUNT(b.page_id),
  Avg_Free_Space = AVG(b.free_space_in_bytes/8192.0)
FROM sys.dm_db_database_page_allocations
(
  DB_ID(),
  OBJECT_ID(N'sys.syscolpars'),
  NULL,NULL,'DETAILED'
) AS p
LEFT OUTER JOIN sys.dm_os_buffer_descriptors AS b
ON b.database_id = DB_ID()
AND b.page_id = p.allocated_page_page_id
AND b.file_id = p.allocated_page_file_id;

If your number of pages is small (like probably < 10000 for system tables) or if the free space is "low" (not sure what your typical thresholds are for reorg/rebuild), focus on other, more interesting, low-hanging fruit.

If your number of pages is large and the free space is "high", ok, then maybe I'm giving SQL Server too much credit for its own self-maintenance. As you showed from the other question, this works...

ALTER INDEX ALL ON sys.syscolpars REORGANIZE;

...and does reduce fragmentation. Though it may require elevated permissions (I did not try as a peon).

Maybe you can just do this periodically as part of your own maintenance, if it makes you feel good and/or you have any evidence that it has any positive impact on your system at all.


Error in PostgreSQL/PostGIS Access to offline bands disabled I am using postgis functions i.e. ST_Clip, ST_PixelHeight.

But I am getting this error -

  ERROR: rt_raster_load_offline_data: Access to offline bands disabled

Actually, I have loaded my raster to postgis using

  raster2pgsql

In raster2pgsql I have used -R option for saving only the metadata of the raster and path location to the raster in the database (not the pixels).

How to solve this issue ?

Have you set the environment variables to enable rasters ?

  As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables: POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment.

  If you want to enable offline raster:

  POSTGIS_ENABLE_OUTDB_RASTERS=1
    Any other setting or no setting at all will disable out of db rasters.

  In order to enable all GDAL drivers available in your GDAL install, set this environment variable as follows

  POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

From http://postgis.net/docs/postgis_installation.html#install_short_version


What to do when handed a server the first time? I'm a new SQL Server DBA in a new company. Just recently, they have handed me access to the server. There are about 50 OLTP databases with different sizes, some ranging 250MB and the biggest database is 25GB with a total size of 150GB data, 100GB logs for all 50 databases.

I would like to know what could be done if you are handed out access to the instance the first time? The action plan in my head right now is to  run a server-side trace and perfmon to see what's going on with the database

Is this a good plan to begin with running that server-side trace? I would like to get some insights on what to normally do in this case.

Also, they have this job that does full backup of all the 50 databases in one job every day.  Is that something that needs to be separated in chunks? My thought on this one is that it's probably OK to backup all the databases in one job since the size is not that big, and still can afford to do daily full backups because of the size. (Let me know if im wrong). And according to them, they can live with the server being down for 5 days. So it seems like the RTO is very flexible.

What are the necessary things that I need to know to better perform the administration of the databases?

I think sp_Blitz by good folks at Brent Ozar Unlimited is the best way to go with a new to me server.

https://www.brentozar.com/blitz/

https://www.brentozar.com/first-aid/sql-server-downloads/


How to use numeric operator between varchar value? Here detail my table :

id | meta

01 | {"key1":"value1","data":"10"}

02 | {"key1":"valuex","data":"15","key3":"y"}

03 | {"data":"20","key3":"x"}

*id is primary key
*meta is field that store json data

At this table, each record having category and each category having difference data

The problem is, can we select record that only having data greater than 15 ?

That record with id 02 and 03

The short answer is that you shouldn't. It is almost certainly possible using the various string manipulation functions to find and extract the right value then cast it to a numeric for comparison, but that would be massively inefficient, difficult to get right (you have to account for the keys you are looking for appearing as partial matches within other key names or values for instance, you would be trying to write a full JSON parser in SQL string functions), and a pain to maintain if your requirements change over time.

If you genuinely need to operate in this manner, consider using a document-store database (like many of the noSQL options that are out there), particularly one designed around JSON as the storage format (CouchDB, Mongo, ...) as that seems to be the format you are using.

Otherwise if you want (or have) to use a relational database then store your data in a relational manner. Of course if you have little control of what keys will be in the data then you migth be forced to use the key/value pair structure (also called the EAV (Entity-Attribute-Value) model and often considered an anti-pattern also) so this may not be optimal depending on your application.

Some relational databases are starting to offer direct support for indexing properties found in arbitrary JSON strings (see http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/ for notes on the support recently added to postgres) allowing you to mix both worlds, but I can't comment on the relative efficiency of this compared to a pure relational model or a pure document store based model as I've never used such features myself yet (and anyway, mySQL isn't one of DBMSs that support it).


Data migration between databases in the same instance I have two almost identical (structurally) databases:

Documents
DocumentsOld

The original database existed before table partitioning was available, so the solution they found was to create the second database and migrate all the old data to it after each year. Now they want to merge both databases again.

I've done some research and I found that both database have just minor structure differences between tables and no IDENTITY columns.

So, aside from the minor structural differences, I'd say it'd be mostly possible to just INSERT values from the old database back to the first one.

The database is actually somewhat big and has more than 100 tables.

What I want to know is, would it make sense to use SSIS to do this, or is SSIS suited to this kind of job? Or should I just write T-SQL code to do the job?

As an additional question, are there any tools more suited than the first two options above to this kind of job?

I'd go straight SSIS myself. Besides having configuration, logging and error handling out of the box, you can get some really nice performance out of it.

You could probably save quite a bit of time by using something like EzAPI to script out the majority of the packages and then tweak the remaining 10%.

I recently used that approach to script out replicating ~150 tables from our Informix instance to SQL Server. Saved me a ton of time by getting the basics set up, all I had to do was fix some data type incompatibility between the systems. I'm behind on blogging about EzAPI but I could put together a sample if you're interested in the approach.

Edit

I put together an demo of replicating tables with SSIS EzAPI. You'd need to modify the values in ReplicateTables and then modify the query in GenerateTableList to ensure it's identifying all the applicable tables.


SQL Server 2012 memory consumption outside the buffer pool I've got an instance of SQL Server 2012 SP2 Enterprise Edition consuming ~20GB of memory higher than the max. memory limit. The instance is limited to 65GB but the physical memory in use from the below query shows 86GB

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

The server is physical with 2 NUMA nodes. Is there a way that can I find out what is consuming the memory outside of the buffer pool (I'm assuming that is what's happening)?

Here's the output of DBCC MEMORYSTATUS:-

And here'e the set memory limit:-

Thanks in advance.

UPDATE:- I've run the query that Aaron suggested

SELECT TOP (20) * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC

Here's the output:-

The SUM of pages_kb comes to ~60GB

UPDATE 2:- Full output of DBCC MEMORYSTATUS is here:- http://pastebin.com/nGn6kXEc

UPDATE 3:- Output of Shanky's scripts in excel file here:- http://jmp.sh/LKRlH4K

UPDATE 4:- Screenshot of the output of:-

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

So this seems to indicate that SQL Server is using more than the 65GB set.

Max server memory controls buffer pool and all page size allocations, but still does not control things like direct Windows allocations (linked servers, sp_OA, XPs), memory required for threads/thread stacks, etc.

You can probably expect this to be higher on NUMA (though I'm not sure 20 GB is normal); the point is, you can't expect max server memory to fully control the memory used by an instance of SQL Server. If you want the whole instance (not just buffer pool, plan caches, and CLR) to use no more than 64GB, you should set max server memory to something lower.

Some potential ideas for tracking this down (I will normalize everything to MB):

performance counters

See if anything jumps out here as excessively large:

SELECT counter_name, instance_name, mb = cntr_value/1024.0
  FROM sys.dm_os_performance_counters
  WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total')
  OR (instance_name = N'' AND counter_name IN
       (N'Connection Memory (KB)', N'Granted Workspace Memory (KB)',
        N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)',
        N'Log Pool Memory (KB)', N'Free Memory (KB)')
  ) ORDER BY mb DESC;

top 20 clerks

You've already done this, but for completeness:

SELECT TOP (21) [type] = COALESCE([type],'Total'),
  mb = SUM(pages_kb/1024.0)
FROM sys.dm_os_memory_clerks
GROUP BY GROUPING SETS((type),())
ORDER BY mb DESC;

thread stack size

First, make sure this is zero, and not some custom number (if it is not 0, find out why, and fix it):

SELECT value_in_use
  FROM sys.configurations
  WHERE name = N'max worker threads';

But you can also see how much memory is being taken up by thread stacks using:

SELECT stack_size_in_bytes/1024.0/1024
  FROM sys.dm_os_sys_info;

3rd party modules loaded

SELECT base_address, description, name
  FROM sys.dm_os_loaded_modules
  WHERE company NOT LIKE N'Microsoft%';

-- you can probably trace down memory usage using the base_address

memory-related DMVs

You may also be able to spot something out of the ordinary looking at these DMVs:

SELECT * FROM sys.dm_os_sys_memory;
SELECT * FROM sys.dm_os_memory_nodes WHERE memory_node_id <> 64;

This article was written before SQL Server 2012, so some column names and calculations may have to be adjusted, but may give some other avenues to try as well:

Troubleshooting SQL Server Memory

Some good background in another article on that site too:

SQL Server 2012 Memory

Some good info about the types of things that use memory outside of max server memory (but no good data about how to collect the actual usage):

MTL Consumers


Online resource for writing Access SQL statements I am looking for an online resource for writing Access SQL statements. I write queries using SQL because the UI puts limitations on what can be queried. I have been using w3schools for reference, however MS-Access sometimes has slightly different syntax, so when I run the query I get an error. Then I end up wasting time trying to figure out what I did wrong only to find out that Access does it differently.

Here is Microsoft's page about Access SQL syntax:

http://office.microsoft.com/en-us/access-help/access-sql-basic-concepts-vocabulary-and-syntax-HA010256402.aspx

and here is a tutorial about Access SQL syntax for Access 2000.  A bit dated, but the concept is still there:

http://www.cwnresearch.com/resources/databases/access/tutorials/access2000/SQLQueries/SQLQueries.html


Index performance for CHAR vs VARCHAR (Postgres) In this answer (https://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye:

  Also keep in mind that there's often a very big difference between a
  CHAR and a VARCHAR when doing index comparisons

Does this apply / still apply for Postgres?

I found pages on Oracle claiming that CHAR is more or less an alias for VARCHAR and so index performance is the same, but I found nothing definitive on Postgres.

CHAR and VARCHAR are implemented exactly the same in Postgres (and Oracle). There is no difference in speed when using those data types.

However, there is one difference that can make a difference in performance: a char column is always padded to the defined length. So if you define a column as char(100) and one as varchar(100) but only store 10 characters in each, the char(100) column uses 100 characters for each value (the 10 characters you stored, plus 90 spaces), whereas the varchar column only stores 10 characters.

Comparing 100 character with 100 characters is going to be slower than comparing 10 characters with 10 characters - although I doubt you can actually measure this difference in a SQL query.

If you declare both with the length of 10 characters and always store exactly 10 characters in them, then there is absolutely no difference whatsoever (this is true for Oracle and Postgres)

So the only difference is the padding that is done for the char data type.

  Also keep in mind that there's often a very big difference between a CHAR and a VARCHAR when doing index comparisons

The above quote is only true if (and only if) the char column is defined too wide (i.e. you are wasting space due to padding). If the length of the char column is always used completely (so no padding occurs), then the above quote is wrong (at least for Postgres and Oracle)

From my point of view, the char data type does not really have any real-word use. Just use varchar (or text in Postgres) and forget that char exists.


The server principal "" is not able to access the database "" under the current security context When I try to install a new database, I'm getting this error from SQL Server 2008.

The server principal "<mydbuser>" is not able to access the database "<mydatabase>" under the current security context.

I looked it up and it says it has something to do with orphaned users when doing a restore.  This connection has nothing to do with a restore, it's a brand new database, user and connection, so why am I getting this problem?

Also the process I'm using to create the database and user/login, is the same for all of my databases, so why does this occur sometimes?

Any ideas?

Thanks.

EDIT: per request here's some code that fails, but understand that this works with every other application I've built.  Also this is built into an ASP.NET MVC application.

System.Web.Management.SqlServices.Install(
    config.Database.DataSource.Trim(),
    config.Database.UserName,
    config.Database.Password,
    config.Database.InitialCatalog,
    System.Web.Management.SqlFeatures.All);

if (!Roles.RoleExists(role.Name))
    Roles.CreateRole(role.Name);  <-- this fails

Oh, another thing that doesn't make sense is that the database in the error message is not the database I'm trying to install to.  It is one of the databases in this SQL Server instance (along with a bunch of others) but it's completely different.  I can't figure out where it's getting this.

Example: the database I'm trying to install to is 'Rapper' and the database shown in the error message is 'GPS'.  Why?

I can't create any comment yet. I don't have the required reputation. So I will post an answer!!!

You can view orphanage users in a database using:

exec sp_change_users_login 'report'

And it also might be because the default database of you login try:

exec sp_helplogins 'Login1'

You can change the default db with:

ALTER LOGIN Login1 WITH DEFAULT_DATABASE=test1


Select multiple columns in IF clause conditionally? Is it possible to select different fields inside a SELECT conditionally?

Pseudocode:

CREATE VIE myview AS SELECT
  id,
  IF(signal_field == 1)
     firstname as first,
     lastname as last
  ELSE
     concat(firstname + lastname) as firstlast
  FROM mytable;

SELECT
  id,(CASE WHEN signal_field =1 THEN firstname END) as first,
     (CASE WHEN signal_field =1 THEN lastname  END) lastname as last,
     (CASE WHEN signal_field!=1 THEN concat(firstname,lastname) END) as firstlast
FROM mytable;


When using blitzindex should are the reads the number of times the index was used to read or is that the op stats? Im using blitz index to identify some indexing issues.  Im concerned that I am reading the output incorrectly.  In my output, im seeing reads, compared to writes in the usage column, and then im seeing a lot more reads in the op stats column.  Which column tells me how many times the index has been used for returning data?

The "Usage Stats" column is from sys.dm_db_index_usage_stats.

The "Operational Stats" column is from sys.dm_db_index_operational_stats.

I won't rehash the Books Online documentation for each DMV here, but the short story is that they both measure how many times the index has been used - they just measure it in different ways.


postgres: Database replica with some schemas writable I have two computers.  Computer A is dedicated to collecting data and storing it in its local database.  Computer B is for analysis.  A is running flat out just with the data collection, so I want to replicate the collected data onto B.  The catch is, analysis will generate its own derived data which should also be stored in the database (but only on B).  My understanding is that the off-the-shelf replication mechanisms require the recipient database to be read-only.

Is it possible to do something like streaming replication, but which allows the recipient database to be read-write?  Ideally, the schema containing the tables replicated from A would be read-only to analysis users, but separate schemas owned by those users would be read-write for them.

It is okay for the replica to be hours out of date.

I want to keep data collection on A writing to a local database, so that it can continue whether or not B is available.

(Currently I'm on 9.3 but about to upgrade to 9.4 betas for unrelated reasons.)

I'd use Londiste for this. Set up the data collector as a publisher and the analytics DB as a subscriber.

The main issue is that Londiste imposes a significant load due to write-amplification - each write must be written once to the table and once to a trigger-maintained replication queue. So using Londiste for replication may add too much load to an already struggling data collector server.

After comments: No PRIMARY KEY means you're pretty stuffed.

I haven't checked if any of the trigger based replication systems support append-only replication of tables without a PRIMARY KEY but I'd be surprised.

Streaming replication doesn't support replication of only some tables, nor is a replica writeable.

The only solution I know of that'll meet your use case is a unidirectional variant of BDR, which we're calling "UDR", but it's still a few months from being ready.


Am I compromising effiency by using CTE instead of indexed tables? Consider scenario where I am creating multiple intermediate tables in CTEs before joining the last intermediate result to some other table. The intermediate results can use joines as well.

The alternative is that I rewrite the code so that each intermediate CTE gets its own table with clustered index on columns required for efficient subsequent joins. I would expect queries on tables with indexes to perform faster.

I understand the intermediate CTE results are written to disk anyway, so I/O constraints should not be considered here, right?

Let’s assume we are considering joining tables with 50m-500m of rows in SQL Server.

A CTE is just a disposable view. As Erik commented, CTE results are not stored to disk.

In terms of perf, a temp table has many advantages of creating indexes, stats, etc which will outperform CTE when dealing with large tables and complex queries.

When we migrated from sql server 2008 to 2014 (SP2+CU8), I saw queries performing poor (generating bad query plans) and we had to change them to use temp tables.

So - based on my experience - if you need speed, go with temp tables (they are not free as depending on how much data you are dealing with, it hits temp table and memory).

Read Martin's answer at : Which are more performant, CTE or temporary tables?


Connect to SQL Server using windows authentication from another PC without Active Directory I have SQL Server 2008 R2 Express installed on a PC.

I have some other PCs that are connected to this PC on a local network.

Currently, those PCs connect to SQL Server using SQL Authentication, however I want them to connect to SQL Server using Windows Authentication.

We don't have an Active Directory domain.  All PCs are part of the "WORKGROUP" workgroup.

If none of the machines involved have Active Directory authentication enabled via Kerberos, which precludes using an online-based Microsoft Account (Windows 8 and higher), you should be able to connect to SQL Server using Windows Authentication assuming you follow these points:

Ensure SQL Server is configured to use TCP connections.
Ensure the Windows firewall is either disabled or has SQL Server connections allowed.
The machine where SQL Server is installed needs Windows user accounts for all the people who will be connecting via the network.  For instance, if the SQL Server computer is named MyPC, and you have User1, User2, and User3 who will be using the SQL Server, MyPC must have accounts setup for User1, User2, and User3 with the same password those user accounts use on their own PC.
You create logins in SQL Server for those user accounts, i.e.:

CREATE LOGIN [MyPC\User1] FROM WINDOWS;
CREATE LOGIN [MyPC\User2] FROM WINDOWS;
CREATE LOGIN [MyPC\User3] FROM WINDOWS;

The client machines may need to connect to the server using the server's IP address instead of it's name.   If the server's IP address is 192.168.0.100, and it is listening on port 33333 then they would use this as the server name to connect to:

tcp:192.168.0.100,33333

If you follow all the above advice, then receive the following error when connecting, then the client PC is using Kerberos authentication, and nothing will help you connect.

  The target principal name is incorrect.  Cannot generate SSPI context.


Mongodb shard balancing failed Data balancing fails on Mongo cluster with 1 mongos and 3 mongod instances. All the data for the collection fs.chunks stays in the same shard with the following error.

Env: Ubuntu- AWS m3.large,

error from sh.status()

  6 : Failed with error 'could not acquire collection lock for test.fs.chunks to migrate chunk [{ : MinKey },{ : MaxKey }) :: caused by :: Lock for migrating chunk [{ : MinKey }, { : MaxKey }) in test.fs.chunks is taken.', from shard0001 to shard0000
  3826 : Failed with error 'moveChunk failed to engage TO-shard in the data transfer: can't accept new chunks because there are still 7 deletes from previous migration', from shard0000 to shard0001
  7655 : Failed with error 'moveChunk failed to engage TO-shard in the data transfer: can't accept new chunks because there are still 4 deletes from previous migration', from shard0001 to shard0000

As I am able to see the chunks error such as

  3826 : Failed with error 'moveChunk failed to engage TO-shard in the
  data transfer: can't accept new chunks because there are still 7
  deletes from previous migration', from shard0000 to shard0001 7655 :
  Failed with error 'moveChunk failed to engage TO-shard in the data
  transfer: can't accept new chunks because there are still 4 deletes
  from previous migration', from shard0001 to shard0000

As Asya Kamsky google groups documentation here When importing a large amount of data it is always preferable to pre-split the collection and distribute chunks evenly across the shards and disable balancer  before starting the data load.   Otherwise you will see the migrations and new data load compete for the same resources, reducing the performance of both.

Yes, you should/can re-enable the balancer after the import is done.   But of course it's better to presplit/pre-balance as described  here.

For further your ref here


Does teradata provides free or trial setup of multinode (at least 2 node) environment I want to test my code on Teradata on 2 node environment. It doesn't need a power of multiple AMPs multiple nodes ( more than 2 ).
Like Teradata provides VM of single node setup of its every version,
does it provide vm for 2 node setup

Teradata Express is a single node 2 AMP only VM, but there's the free Teradata on VMware Developer Tier running on VMware ESXi which supports up to 2 nodes.

Or you could check Teradata on AWS & Azure, there's some free version, too, I don't know if they support multiple nodes.

But the main question is:

Why do you think you need that for testing? There's absolutely no difference if you got a single node or a multi node system.


MySQL Insert NULL Inserts 0 I am using MySQL and PhpMyAdmin, I have a table named items - the structure is as follows;

+------+---------+------+---------+
| name | type    | Null | Default |
+------+---------+------+---------+
| id   | int(11) | No   | None    |
+------+---------+------+---------+
| year | int(4)  | Yes  | Null    |
+------+---------+------+---------+

Within PhpMyAdmin I run the following query;

UPDATE `item` SET `item_publication_year` = '' WHERE `item_id` = '5'

This executes successfully.

Then I run this query;

SELECT * FROM `item` WHERE `item_id` = '5'

This executes successfully and produces the following;

+-----+------+
| id  | year |
+-----+------+
| 5   | 0    |
+-----+------+

Why is the year field 0 - shouldn't it be NULL? Am I doing something wrong?

'' is an empty string, it's not a NULL value.

Other DBMS would rightfully reject your update statement in the first place (because  it's invalid to assign a string to a number).

If you want to set something to null then use null not an empty string

UPDATE item
  SET item_publication_year = null
WHERE item_id = 5

You should also use the correct literal type for the data type. '5' is a string, it's not a number . 5 is a number. Not using the correctly typed literal might result in indexes not being used.


Does a Key-Value type table still needs a auto increment key? Suppose that I have a very simple table to store variable-like data, like this:

+-----------+-----------------+
|    key    |      value      |
+-----------+-----------------+
| site_url  | helloworld.com  |
| site_name | Hello World     |
| time_zone | Asia Taipei     |
+-----------+-----------------+

Do I still need to create a ID column?
or just simply set key column as primary key
(key would be unique

Always start by identifying your natural keys, in this case key. If these are too complex (too many columns) or not stable enough (changes to often), consider adding a surrogate key (such as the ID column you mention). The criteria I use for keys are:

- unique
- stable
- irreducible
- complexity
- familiarity

In many situations there will be a conflict between these criteria, and one have to compromise. There is nothing wrong with adding a surrogate key under certain circumstances, but sadly many people design by auto-pilot and blindly add surrogate keys no matter what.


How can I delete a corrupted table space? Problem:
On an Oracle 11.2g DB;
We have a corrupted TS that does not allow starting the database instance above mount state. The TS cannot be removed with standard methods (i.e., enterprise manager).

What can we do?
What other information is needed to diagnose the problem?

(We've meanwhile checked the file system, which is ok, but the DBF file for the tablespace is lost.)

If you just want to remove the tablespace have you tried logging in as sys and dropping it?

DROP TABLESPACE <tablespace>
INCLUDING CONTENTS
CASCADE CONSTRAINTS;

If so, what error message do you receive? (I'm assuming you've made a cold backup of the database at this point in case the tablespace is needed.)

Alternatively, if you can determine which datafile is corrupt you can remove this from the tablespace:

ALTER DATABASE DATAFILE '<filename>' OFFLINE DROP;

Make sure that <filename> is the filename known to Oracle, especially if any links may be involved on disk! Otherwise you will get misleading errors about non-existent files.


EventSequence in Trace using profiler We have a stored procedure sp_foo that contains dynamic sql and at the end of sp_foo the dynamic sql is executed using EXECUTE (@query)

While analyzing a trace profile i noticed that the Eventsquence (ES), the start and end time do not match my expectation.

Row   EC   ES     Start_Time    End_Time    D_ms  SQL
C     45   1293   44.240        45.603      1363  Select ...
D     45   1294   43.737        45.603      1866  execute (@query)
E     12   1296   43.730        45.600      1871  execute sp_foo @id=34

Questions

Why has E the highest EventSequence (ES=1296), is started first and finishes first?
Why has C the latest start_time, but the lowest EventSequence (ES=1293)

I used this query

Select EventClass as EC, EventSequence as ES
    , Duration/1000 as duration_ms
    , convert(time, StartTime) as [Start_Time]
    , convert(time, EndTime) as [End_Time]
    ....
From TraceImport
WHERE EventClass in (10, 12, 45)

Update to comment on first answer

Gareth points out that my Eventsequence might be running from high to low.
I believe that this is not the case yet

my Eventsequence values are ascending (later start_time == higher ES value)
My ES numbers are in the 2.73E7 range; According to SQL Server Planet Bigint Max Value the maximum value for an Bigint in SQL Server is 8 bytes big from: -9223372036854775808 through 9223372036854775807 If i am not mistaken this would mean that my ES values had to be in the range of 9.2E17 or 9.2E18

Therefore i assume that bigint was not reached yet. We are using 2008R2 Version 10.50.4339.0

According to Importance of the Trace Event Sequence Column and SQL Server 2005 SP2 Changes

  SQL Server 2005 shipped with an Event Sequence stored in a LONG.
  [The MSFT] Support has seen a few traces where the bounds of the signed long are
  exceeded and the Event Sequence becomes negative.

Event Sequence in SQL Server 2005 counts up from 0 to the max of bigint long, and then down from there to 0 again.

"Step 1:  Count up from 0 to 0x7FFFFFFFF
Step 2:  Count down from 0x7FFFFFFF to 0

Start at step 1 again."

  SQL Server 2005 SP2 expands the column to a LONGLONG (signed 64 bit
  integer)

So I guess you're currently in step 2.


Using dynamic query to loop through Db for a truncate statement Please, any help with my SQL query below will be appreciated. I am not familiar with how to loop through DBs to truncate a table. What I have tried is below:

DECLARE @DynamicSQL AS VARCHAR(MAX) = '';
DECLARE @dbName     nvarchar(1000) ='SP_%%';

SELECT @DynamicSQL = @DynamicSQL + 'Use' + QUOTENAME(NAME) + ';  '
FROM sys.databases
WHERE NAME LIKE  'SP_%%';

SELECT @DynamicSQL = @DynamicSQL + 'TRUNCATE TABLE ' + QUOTENAME(NAME) + '; '
FROM sys.tables
WHERE name LIKE 'log%'

PRINT @DynamicSQL
--EXEC (@DynamicSQL)

The Result I get is below:

Use[SP__20180314];  Use[SPAUT__06092018];

But I want something like:

Use[SP_20180314] Truncate Table log;  Use[SPAUT_06092018] Truncate Table log;

I am trying to truncate log tables from my databases on SQL Server 2016.
Thanks

As Jacob H mentioned in the comments, you'll need to query sys.tables with the appropriate USE statement for each database, or by explicitly using the three-part syntax {database}.sys.tables for database.

One way to do this is to query all databases, and while doing so aggregate a string that queries sys.tables for that database. Since you didn't specify a version of SQL Server, I'll use the STRING_AGG() function that is now available in SQL Server 2017. On earlier SQL Server versions, you can use your choice of methods, such as the (officially undocumented and unsupported) variable concatenation used for @DynamicSQL in your question.

-- Print the first 8,000 bytes of TRUNCATE TABLE statements for each database
-- (Comment out the PRINT and enable the EXEC if you want to execute)
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = 'DECLARE @truncateTableSql NVARCHAR(MAX)
        ' + STRING_AGG('
        USE ' + CONVERT(NVARCHAR(MAX), QUOTENAME(name)) + '
        SET @truncateTableSql = NULL
        SELECT @truncateTableSql = STRING_AGG(
            CONVERT(NVARCHAR(MAX),''TRUNCATE TABLE '') +
            QUOTENAME(DB_NAME()) + ''.'' +
            QUOTENAME(SCHEMA_NAME(schema_id)) + ''.'' +
            QUOTENAME(name),
            CHAR(10))
        FROM sys.tables
        WHERE is_ms_shipped = 0 /* Avoid Microsoft-provided tables */
            AND name LIKE ''log%''
        -- Note that PRINT will truncate the string to the first 8,000 bytes
        PRINT(@truncateTableSql)
        --EXEC(@truncateTableSql)
    ', CHAR(10))
FROM sys.databases
WHERE state = 0 /* ONLINE */
    AND is_read_only = 0
    AND name NOT IN ('master','model','msdb','tempdb')

EXEC(@sql)

Simplified query for a single table across databases

An update makes it sounds like only the dbo.log table needs to be truncated. If you only need to target a single table with a known name, you can simplify the script quite a bit. For example:

-- Print the first 8,000 bytes of TRUNCATE TABLE statements
-- (Comment out the PRINT and enable the EXEC if you want to execute)
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = STRING_AGG('TRUNCATE TABLE ' + QUOTENAME(name) + '.dbo.log', CHAR(10))
FROM sys.databases
WHERE state = 0 /* ONLINE */
    AND is_read_only = 0
    AND name NOT IN ('master','model','msdb','tempdb')
    AND OBJECT_ID(name + '.dbo.log') IS NOT NULL
PRINT(@sql)
--EXEC(@sql)

Simplified query for a single table across databases in earlier versions of SQL Server

There are many methods for concatenating strings in SQL Server, and many blog posts explore and vigorously debate these options. I prefer STRING_AGG() if you are using SQL Server 2017 or higher, but using XML to concatenate strings is one option if you are unable to upgrade to SQL Server 2017. For example:

-- Print the first 8,000 bytes of TRUNCATE TABLE statements
-- (Comment out the PRINT and enable the EXEC if you want to execute)
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = CONVERT(NVARCHAR(MAX), (
    SELECT CONVERT(NVARCHAR(MAX), 'TRUNCATE TABLE ') + QUOTENAME(name) + '.dbo.log; '
    FROM sys.databases
        WHERE state = 0 /* ONLINE */
        AND is_read_only = 0
        AND name NOT IN ('master','model','msdb','tempdb')
        AND OBJECT_ID(name + '.dbo.log') IS NOT NULL
    FOR XML PATH('')
))
PRINT(@sql)
--EXEC(@sql)


Does PostgreSQL optimize adding columns with non-NULL DEFAULTs? When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?

In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default value - which could be very painful, obviously. With the optimization the DB would instantaneously create the new column, store just one copy of the default value which would be returned when no non-default value is found for that column in a suitable index data structure.

For example Oracle 11g has such an optimization.

There is no such mechanism in PostgreSQL.

However, you can still avoid the excessive effects of such a table change.

The following statement acquires an access exclusive lock on the table for the duration of the statement/transaction:

ALTER TABLE your_table
    ADD COLUMN new_column integer NOT NULL DEFAULT 0;

This statement changes the catalog, then rewrites the whole table so that the new column contains the default value in all rows.  If the table has many rows and being accessed frequently enough, this would cause some temporary problems.

To avoid it, try to hold the exclusive lock for as short as possible:

ALTER TABLE your_table
    ADD COLUMN new_column integer;
ALTER TABLE your_table
    ALTER COLUMN new_column SET DEFAULT 0;

As this is basically only a (actually two) change to the catalog (no data change happens), it will complete pretty fast.  Then depending on your needs and table usage, you can update the new column to the default in one step or in batches, and when finished, set the column to NOT NULL.

Update about a wish coming true: PostgreSQL 11 will have this feature.  See https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/ for more.


What are the undocumented values for referencing_class in sys.sql_expression_dependencies? In SQL Server, the sys.sql_expression_dependencies system catalog view "Contains one row for each by-name dependency on a user-defined entity in the current database", to quote the docs. One of the returned columns is referencing_class, of which the docs say this:

referencing_class   tinyint     Class of the referencing entity.

1 = Object or column

12 = Database DDL trigger

13 = Server DDL trigger

Is not nullable.

However, I'm looking at a database right now in which there is a row with referencing_class equal to 7. What does 7 mean, and what is the correct list of possible values here?

This deficiency in the docs was raised as an issue in August 2019, with MS comment being

We've created an internal work item (1595947) to investigate and
potentially update the article.
We're going to close to this item and track with our internal work
item

Until such time as MS update the article (or accept a PR against the docs), here is a probably-partial list of the other possible values:

referencing_class referencing_class_desc
----------------- ----------------------
7                 INDEX
9                 STATISTICS

7 = Filtered index (note that regular unfiltered indexes do not feature in this system view at all)
9 = Filtered statistics

This Microsoft Support Document shows referencing_class = 7 in relation to filtered indexes.
Based on the Remarks section there are no others probably.


How do I build index for MySQL table that inserts a lot and queries only most recent data I have a MySQL database table using InnoDB and looks like this:

OrgID (INTEGER)
MachID (INTEGER)
Date (DATETIME)
IdleTime (INTEGER)

I do two operations on this table:

Insert a new record, with Org ID, Mach ID, Date and IdleTime
Query for either a given Organization/Machine ID pair, for last day, the sum of IdleTime:

SELECT SUM(IdleTime) FROM DBTable WHERE OrgID=1 AND MachID=2 AND Date BETWEEN 'YYYY-mm-dd 00:00:00' AND 'YYYY-mm-dd 23:59:59';

We may want to get the sum of total IdleTime for given OrgID, but it is just an extra bonus. In most cases, YYYY-mm-dd is yesterday.

We concern more on the performance ofINSERT operation but we do not want too slow SELECT. My boss recommended adding a new column as the primary key, namely something like INSERT_TIME(TIMESTAMP) , to ensure insertion is sequential. I am wondering if using a PRIMARY KEY (OrgID, MachID, Date) may help. Any suggestions on how do I use the index to boost the performance?

PRIMARY KEY (OrgID, MachID, Date) would be good for that query.  But, a PRIMARY KEY is necessarily "unique"; is that combination unique?  If not unique, then make it a plain INDEX and have something else as the PRIMARY KEY.

If we are talking about millions of rows per day, then Summary tables would be worth doing.

If it is usually "yesterday", then consider:

AND Date >= CURDATE() - INTERVAL 1 DAY
AND Date  < CURDATE()

Or, more generically, (where you insert the days ago for '?'):

AND Date >= CURDATE() - INTERVAL ?   DAY
AND Date  < CURDATE() - INTERVAL ?-1 DAY

More

Insuring sequential -- not that important.  With that 3-part PK, you will be inserting sequentially in several spots, namely each combination of OrgID and MachID.

Insert do need to update secondary keys, but this is usually not a big deal.  (To get really technical, see "InnoDB's change buffering".)

If you are going to be inserting more than 100 rows per second, then I recommend gathering them up and batch inserting.  Also, see innodb_flush_log_at_trx_commit = 2.


RAID 10 array configuration for SQL Server I have a question regarding RAID 10 and SQL Server. My plan is to purchase an HP DL380 G9 with the 24 drive config. The question is how to break up the array.

The drives will be divided between the 3 bays to even out the traffic on the controller. Keeping in mind random IOPs are better with additional drives in the array, would be be better to:

Make 1 large RAID 10 array with logs, tempdb, and data split into separate logical drives. This would be 8-12 drives.
Make 2-3 smaller RAID 10 arrays and split up logs, tempdb, and data files by array. This setup would be 4 drives in each RAID 10 array.

The 8-12 drives would be better performing overall (our read/writes are probably 50/50). In scenario 2 would the controller serving 3 different logical drives take a performance hit?

We will use MS SQL Standard 2012 with the max memory (64GB + RAM for OS). Enterprise is probably not an option due to cost.

Based on what you've described I would go with your second option and here is why.

Creating just one array and slicing it up into several logical volumes won't be any different than just having one large logical volume when it comes to I/O since you are still sharing the same underlying drives that make up the arrays.

SQL server runs best if you can separate the I/O types (sequential/random). Log files are written sequentially and any more than one database log on them will change the sequential pattern to random slowing the write operations to the database. Data files are written randomly so you are fine to mix multiple database data files on the same drive array.

For option 2 I would put the log file of tempdb on one array, the log file of your database on another array and the data files for your database and tempdb on the last array.

If you can swing a some additional dollars at the hardware here are a few suggestions that could help you offload some of the I/O.

You can offload the tempdb from the storage array by adding some PCI drives like Fusion IO to place the data and log files on. You should be able to purchase the smallest size they offer unless your database will be doing things that will cause a lot of tempdb usage.  You can add those drives from that third array back to the data array which will increase the IOPS available.

Buy more memory for the server. The 64GB limit only applies buffer pool. SQL server will use as much memory that the OS can support for everything else. Kendra Little from Brent Ozar Unlimited discusses the details in her blog post.

The final item would be to use the top end RAID controller that HP offers. You will get 1GB of on board memory to help with write caching to the disks.  You should also be able to control the percentage of that memory that is used for read and write caching. You should set it to 100% write as SQL server can't really utilize any read caching on the controller.


Creating report based on the sql statement I would like my report to display something image like as below

so can i know is it how my sql for table has to be when creating it

    create table attendance
    (
       Student_ID VARCHAR2 (15) primary key,
       Student_Name VARCHAR2 (50) not null,
       "Date1" VARCHAR2 (15) not null,
       "Date2" VARCHAR2 (15) not null,
       "Date3" VARCHAR2 (15) not null,
       "Date4" VARCHAR2 (15) not null,
       "Date5" VARCHAR2 (15) not null,
       Present number (10),
       Late number (10),
       Absent number (10),
       Total_Attendance number (10)
    );

for present, late, absent and total attendance i will be using like countif but this i have to research on it how to use in oracle. I would like to know is this correct the way it should be used for sql in creating table so that i will get a report the looks as in the picture

You can "translate" the appropriate text values to 1 or 0, then add them.

select
  student_id,
  student_name,
  date1,
  date2,
  date3,
  date4,
  date5,
  decode(date1, 'Present', 1, 0) +
  decode(date2, 'Present', 1, 0) +
  decode(date3, 'Present', 1, 0) +
  decode(date4, 'Present', 1, 0) +
  decode(date5, 'Present', 1, 0) as Present,
  decode(date1, 'Late', 1, 0) +
  decode(date2, 'Late', 1, 0) +
  decode(date3, 'Late', 1, 0) +
  decode(date4, 'Late', 1, 0) +
  decode(date5, 'Late', 1, 0) as Late,
  decode(date1, 'Absent', 1, 0) +
  decode(date2, 'Absent', 1, 0) +
  decode(date3, 'Absent', 1, 0) +
  decode(date4, 'Absent', 1, 0) +
  decode(date5, 'Absent', 1, 0) as Present,
  decode(date1, 'Present', 1, 'Late', 1, 0) +
  decode(date2, 'Present', 1, 'Late', 1, 0) +
  decode(date3, 'Present', 1, 'Late', 1, 0) +
  decode(date4, 'Present', 1, 'Late', 1, 0) +
  decode(date5, 'Present', 1, 'Late', 1, 0) as Total_attendance
from
  attendance;

CASE also works, but that is a bit longer:

...
case when date1 = 'Present' then 1 else 0 end +
case when date2 = 'Present' then 1 else 0 end +
case when date3 = 'Present' then 1 else 0 end +
case when date4 = 'Present' then 1 else 0 end +
case when date5 = 'Present' then 1 else 0 end as Present,
...


Create tablespace "error in creating database file, access denied (OS 5)" I try to create a database file in this location

I've checked the route and everything, I've tried several times to create the tablespace like this...

...but as you can see, I was just lead to several errors. What can I do? What am I doing wrong?

Do you have permission on this directory?
Under which user are you running SQL*PLUS? Administrator? –
if no than got permission first

if yes

CREATE TABLESPACE lmtbsb DATAFILE 'file_path/lmtbsb01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;


Index rebuild, data compression generated Large TLog We are following vendor recommendations for a index rebuild changing the fillfactor and enabling data compression, on the test server the results have us wondering what is going on that would cause the TLog to exceed 650 GB, (ran out of drive space).  Why is the tlog growing so much?

SQL Server Version   12.0.2456.0

Vendor code provided:

alter index all on dbo.ACC_LOG_DTL_IX rebuild with
( data_compression=PAGE, fillfactor=85, online=ON)

This is the script for the table and the one index.

CREATE TABLE [dbo].[ACC_LOG_DTL_IX](
    [ACCESS_INSTANT] [numeric](16, 6) NOT NULL,
    [PROCESS_ID] [varchar](254) NOT NULL,
    [DATA_MNEMONIC_ID] [varchar](15) NOT NULL,
    [STRING_VALUE] [varchar](2000) NULL,
    [INTEGER_VALUE] [numeric](18, 0) NULL,
 CONSTRAINT [PK_ACC_LOG_DTL_IX] PRIMARY KEY CLUSTERED
(
    [ACCESS_INSTANT] ASC,
    [PROCESS_ID] ASC,
    [DATA_MNEMONIC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Regardless of your recovery model whenever an operation (index rebuild or reorganize) modifies a data page that page modification is put into the log.  In your case changing the fill factor can add more data pages, and move data to new pages.  SQL Server has to make room for the new data and to do that it has to keep track of the page changes.  The more pages that are changed, the more entries will be put into the transaction log.

In your case I would ask, what storage are you using, and what is your fragmentation like?  If you're using flash storage, I'd tell you that rebuilding indexes becomes much less important than it does if you're using magnetic storage.


Query on large database much slower on HDD I have the following table:

CREATE TABLE `Geo1` (
  `id` int(8) UNSIGNED NOT NULL,
  `nameRef` int(8) DEFAULT NULL,
  `placeRef` mediumint(7) UNSIGNED DEFAULT NULL,
  `category` enum(...) COLLATE utf8_bin DEFAULT NULL,
  `parentRef` mediumint(7) DEFAULT NULL,
  `incidence` int(9) UNSIGNED NOT NULL,
  `percent` decimal(11,9) UNSIGNED DEFAULT NULL,
  `ratio` int(11) NOT NULL,
  `rank` mediumint(7) UNSIGNED DEFAULT NULL,
  `year` smallint(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `Geo1`
  ADD PRIMARY KEY (`id`),
  ADD KEY `placeRef_rank` (`placeRef`,`rank`),
  ADD KEY `nameRef_parentRef` (`nameRef`,`parentRef`),
  ADD KEY `nameRef_placeRef` (`nameRef`,`placeRef`),
  ADD KEY `nameRef_category_year` (`nameRef`,`category`,`year`) USING BTREE;
MODIFY `id` int(8) UNSIGNED NOT NULL AUTO_INCREMENT;

The table is 55GB in total, 36GB of indexes.

On my local server (which uses SSD) it can execute the following query in 0.001 seconds:

SELECT SQL_NO_CACHE placeRef, incidence FROM Geo1
    WHERE nameRef = 1
      AND category = "country";

However on a remote server (which uses HDD) it takes about 0.1-0.6 seconds. This server has 32GB RAM. Here is the mysql.cnf.

The EXPLAIN result on both servers are identical.

Any ideas as to why this is so slow?

There are two problems
PROBLEM #1 : MyISAM
MyISAM does not cache data (stored in the .MYD) which means data must be read for disk every time. Indexes may be cached (read once from .MYI), but not data.
See my old post in What are the main differences between InnoDB and MyISAM? under the MyISAM subheading
PROBLEM #2 : HDD vs SSD
An HDD must use spindles to move its read/write heads across a disk
An SSD is a random read/write device. It zeros in on blocks to disk faster than HDD.


ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance I have a problem with a SPFILE as shown below.How can I get rid of that problem?Any suggestions?

 SQL> startup
 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
 ORACLE instance started.

 Total System Global Area 1068937216 bytes
 Fixed Size                  2182592 bytes
 Variable Size             784335424 bytes
 Database Buffers          276824064 bytes
 Redo Buffers                5595136 bytes
 Database mounted.
 Database opened.
 SQL> alter system reset log_archive_start scope=spfile sid='*';
 alter system reset log_archive_start scope=spfile sid='*'
 *
 ERROR at line 1:
 ORA-32010: cannot find entry to delete in SPFILE

Well, you can hack around with an SPFILE easily enough:

SQL> create pfile='/tmp/initSID.ora' from spfile;

Now edit that ordinary text file, then

SQL> create spfile from pfile='/tmp/initSID.ora';

And you can play with the temporary one with

SQL> startup pfile='/tmp/initSID.ora';

Note tho' there is no actual problem with specifying obsolete parameters other than the message; the only issue is if you were relying on them to set some desired behavior, and they were ignored.


Can you explain this execution plan? I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects execution plan. Here is the script that I used to generate random test data:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('t') AND type in (N'U'))
DROP TABLE t
GO

CREATE TABLE t
(
 c1 int IDENTITY(1,1) NOT NULL
,c2 int NULL
)
GO

insert into t
select top 1000000 a from
(select t1.number*2048 + t2.number a, newid() b
from [master]..spt_values t1
cross join  [master]..spt_values t2
where t1.[type] = 'P' and t2.[type] = 'P') a
order by b
GO

update t set c2 = null
where c2 < 2048 * 2048 / 10
GO

CREATE CLUSTERED INDEX pk ON [t] (c1)
GO

CREATE NONCLUSTERED INDEX i ON t (c2)
GO

Now, given this data, I invoked the following query:

select *
from t
where
      c2 < 1048576
   or c2 is null
;

To my great surprise, the execution plan that was generated for this query, was this. (Sorry for the external link, it's too large to fit here).

Can someone explain to me what's up with all these "Constant Scans" and "Compute Scalars"? What's happening?

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
       |--Merge Interval
       |    |--Sort(TOP 2, ORDER BY:([Expr1013] DESC, [Expr1014] ASC, [Expr1010] ASC, [Expr1015] DESC))
       |         |--Compute Scalar(DEFINE:([Expr1013]=((4)&[Expr1012]) = (4) AND NULL = [Expr1010], [Expr1014]=(4)&[Expr1012], [Expr1015]=(16)&[Expr1012]))
       |              |--Concatenation
       |                   |--Compute Scalar(DEFINE:([Expr1005]=NULL, [Expr1006]=NULL, [Expr1004]=(60)))
       |                   |    |--Constant Scan
       |                   |--Compute Scalar(DEFINE:([Expr1008]=NULL, [Expr1009]=(1048576), [Expr1007]=(10)))
       |                        |--Constant Scan
       |--Index Seek(OBJECT:([t].[i]), SEEK:([t].[c2] > [Expr1010] AND [t].[c2] < [Expr1011]) ORDERED FORWARD)

The constant scans each produce a single in-memory row with no columns. The top compute scalar outputs a single row with 3 columns

Expr1005    Expr1006    Expr1004
----------- ----------- -----------
NULL        NULL        60

The bottom compute scalar outputs a single row with 3 columns

Expr1008    Expr1009    Expr1007
----------- ----------- -----------
NULL        1048576        10

The concatenation operator Unions these 2 rows together and outputs the 3 columns but they are now renamed

Expr1010    Expr1011    Expr1012
----------- ----------- -----------
NULL        NULL        60
NULL        1048576     10

The Expr1012 column is a set of flags used internally to define certain seek properties for the Storage Engine.

The next compute scalar along outputs 2 rows

Expr1010    Expr1011    Expr1012    Expr1013    Expr1014    Expr1015
----------- ----------- ----------- ----------- ----------- -----------
NULL        NULL        60          True        4           16
NULL        1048576     10          False       0           0

The last three columns are defined as follows and are just used for sorting purposes prior to presenting to the Merge Interval Operator

[Expr1013] = Scalar Operator(((4)&[Expr1012]) = (4) AND NULL = [Expr1010]),
[Expr1014] = Scalar Operator((4)&[Expr1012]),
[Expr1015] = Scalar Operator((16)&[Expr1012])

Expr1014 and Expr1015 just test whether certain bits are on in the flag.
Expr1013 appears to return a boolean column true if both the bit for 4 is on and Expr1010 is NULL.

From trying other comparison operators in the query I get these results

+----------+----------+----------+-------------+----+----+---+---+---+---+
| Operator | Expr1010 | Expr1011 | Flags (Dec) |       Flags (Bin)       |
|          |          |          |             | 32 | 16 | 8 | 4 | 2 | 1 |
+----------+----------+----------+-------------+----+----+---+---+---+---+
| >        | 1048576  | NULL     |           6 |  0 |  0 | 0 | 1 | 1 | 0 |
| >=       | 1048576  | NULL     |          22 |  0 |  1 | 0 | 1 | 1 | 0 |
| <=       | NULL     | 1048576  |          42 |  1 |  0 | 1 | 0 | 1 | 0 |
| <        | NULL     | 1048576  |          10 |  0 |  0 | 1 | 0 | 1 | 0 |
| =        | 1048576  | 1048576  |          62 |  1 |  1 | 1 | 1 | 1 | 0 |
| IS NULL  | NULL     | NULL     |          60 |  1 |  1 | 1 | 1 | 0 | 0 |
+----------+----------+----------+-------------+----+----+---+---+---+---+

From which I infer that Bit 4 means "Has start of range" (as opposed to being unbounded) and Bit 16 means the start of the range is inclusive.

This 6 column result set is emitted from the SORT operator sorted by
Expr1013 DESC, Expr1014 ASC, Expr1010 ASC, Expr1015 DESC. Assuming True is represented by 1 and False by 0 the previously represented resultset is already in that order.

Based on my previous assumptions the net effect of this sort is to present the ranges to the merge interval in the following order

 ORDER BY
          HasStartOfRangeAndItIsNullFirst,
          HasUnboundedStartOfRangeFirst,
          StartOfRange,
          StartOfRangeIsInclusiveFirst

The merge interval operator outputs 2 rows

Expr1010    Expr1011    Expr1012
----------- ----------- -----------
NULL        NULL        60
NULL        1048576     10

For each row emitted a range seek is performed

Seek Keys[1]: Start:[dbo].[t].c2 > Scalar Operator([Expr1010]),
               End: [dbo].[t].c2 < Scalar Operator([Expr1011])

So it would appear as though two seeks are performed. One apparently > NULL AND < NULL and one  > NULL AND < 1048576. However the flags that get passed in appear to modify this to IS NULL and < 1048576 respectively. Hopefully @sqlkiwi can clarify this and correct any inaccuracies!

If you change the query slightly to

select *
from t
where
      c2 > 1048576
   or c2 = 0
;

Then the plan looks much simpler with an index seek with multiple seek predicates.

The plan shows Seek Keys

Start: c2 >= 0, End: c2 <= 0,
Start: c2 > 1048576

The explanation for why this simpler plan cannot be used for the case in the OP is given by SQLKiwi in the comments to the earlier linked blog post.

An index seek with multiple predicates cannot mix different types of comparison predicate (ie. Is and Eq in the case in the OP). This is just a current limitation of the product (and is presumably the reason why the equality test in the last query c2 = 0 is implemented using >= and <= rather than just the straightforward equality seek you get for the query c2 = 0 OR c2 = 1048576.


Replace consective numbers from a string So if I have a table with values:

Name   ID
----   ---
James  100
James  101
James  102
James  200
James  201
James  202
Alex   100
Alex   101
Alex   102
Alex   105
Alex   106
Alex   109
Alex   109
Alex   500
Alex   501
Alex   102

I want the result to look like this

Name   ID
----   ---
James  100-102, 201-202
Alex   100-102, 105-106, 109, 500-502

I know, I can transform the data in a row by using FOR XML PATH(''). but I am not sure how to replace consecutive numbers with a - from them.  Any help in directing me to right direction will be appreciated. thanks.

I used the following, which gets you part of the way there.

USE tempdb;
GO
CREATE TABLE Test
(
    uname VARCHAR(255)
    , num INT
);

INSERT INTO Test (uname, num) VALUES ('Max', 1);
INSERT INTO Test (uname, num) VALUES ('Max', 2);
INSERT INTO Test (uname, num) VALUES ('Max', 3);
INSERT INTO Test (uname, num) VALUES ('Max', 4);
INSERT INTO Test (uname, num) VALUES ('Max', 5);
INSERT INTO Test (uname, num) VALUES ('Max', 11);
INSERT INTO Test (uname, num) VALUES ('Max', 12);
INSERT INTO Test (uname, num) VALUES ('Max', 13);
INSERT INTO Test (uname, num) VALUES ('Max', 14);
INSERT INTO Test (uname, num) VALUES ('Max', 15);

INSERT INTO Test (uname, num) VALUES ('Jen', 22);
INSERT INTO Test (uname, num) VALUES ('Jen', 23);
INSERT INTO Test (uname, num) VALUES ('Jen', 24);
INSERT INTO Test (uname, num) VALUES ('Jen', 25);

SELECT uname
    , CAST(min(num) as varchar(255)) + ' - ' + CAST(max(num) as varchar(255)) AS NumRange
FROM (
    SELECT uname, num - ROW_NUMBER() OVER (PARTITION BY uname ORDER BY num) groupnum, num
    FROM dbo.Test
    ) t
GROUP BY uname, groupnum;

Results:

Here is the sample code provided so kindly by @AaronBertrand

;WITH cte as (
    SELECT uname
        , min(num) As MinNum
        , CAST(min(num) as varchar(255)) + ' - ' + CAST(max(num) as varchar(255)) AS NumRange
    FROM (
        SELECT uname, num - ROW_NUMBER() OVER (PARTITION BY uname ORDER BY num) groupnum, num
        FROM dbo.Test
        ) t
    GROUP BY uname, groupnum
    )
SELECT DISTINCT uname
    , STUFF((
        (
            SELECT ', ' + NumRange
            FROM cte as cte1
            WHERE cte.uname = cte1.uname
            ORDER BY cte1.MinNum
            FOR XML PATH, TYPE
        ).value('.[1]','VARCHAR(255)')
    ), 1, 2, '') as NumRange
FROM cte;

The results:

STUFF is used here to remove the leading , from the results.  It does that by replacing the first two leading characters in the concatenated NumRange with a zero-length string ''.


How to visualize joins between inner queries to their respective outer queries? I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query.

Here's an example:

SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE EXISTS
(
    SELECT TOP 1
    FROM Ninjas AS N
    INNER JOIN NinjaWeapons AS NW
        ON N.WeaponId = NW.WeaponId
    WHERE N.NinjaId = NM.NinjaId
)

Is there a way to represent this (or a similar kind of inner query joined to outer query) in a table or diagram of sorts?

Just break it down into stages and consider what the output of each stage represents

SELECT *
FROM Ninjas AS N
INNER JOIN NinjaWeapons AS NW
    ON N.WeaponId = NW.WeaponId

This can be thought of as "all ninjas with at least one weapon along with their weapons". The data model does not appear to tie weapons to particular movies.

So you are now left with the simpler query to analyse (there is no value in including TOP 1 in an EXISTS so that has also been removed.)

SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE EXISTS
(
    SELECT *
    FROM WeaponOwningNinjasWithTheirWeapons AS N
    WHERE N.NinjaId = NM.NinjaId
)

There seems to be a normalisation issue here as apparently NinjaMovies will contain multiple rows for the same movie starring more than one Ninja and repeats the title in each one - but perhaps NinjaMovies is itself a view resulting from a join of normalised tables.

In any event SELECT NM.MovieId, NM.MovieTitle clearly selects the Movie Ids and Title from it so the only thing left to analyse is the EXISTS.

This simply says to only return rows where the corresponding Ninja exists in the WeaponOwningNinjasWithTheirWeapons result set.

So to sum up. The query returns all rows in NinjaMovies where the corresponding Ninja has at least one weapon.

The semantics of the query seem a bit odd as NM.NinjaId is not projected so in the event that the movie stars multiple ninjas - some with weapons and some without - there is no indication as to which led to the inclusion of the row. If you don't care about this then you should probably add a DISTINCT to remove duplicates in the event that the movie stars multiple weapon bearing ninjas.


General info on Resources for Running SQL Server queries Does anyone know of online or otherwise resources for running queries?
My copy of SQL Server did not install correctly, after many attempts on my part.

Does anyone know of any resource in which I can test my SQL queries?
I am in New York City, in case there are public places with SQL Server access.

EDIT: Someone suggested I may be able to run queries with MySQL (including running a query that allows me to create the database I want to query ).

Is this true?

Thanks.

One popular site is SQL fiddle that currently provides online test facilities for MS SQL Server 2008 and MS SQL Server 2014 (among others).

Another one is "our very own" data.stackexchange.com, where you can run arbitrary queries against recent snapshots of the site data on SQL Server.


MySQL innodb scaling I am using Jamf to manage 25K Macs and 13K iOS devices. Every time I try to update iOS apps or configuration profiles for a large number of devices my database gets overwhelmed. I have MySQL 5.6 running on Windows Server 2008R2 with 16 cores and 32GB RAM. I have tried adjusting things like query_cache or thread concurrency without any success. Any help would be greatly appreciated.

innodb_buffer_pool_size = 25G

--------------
ROW OPERATIONS
--------------
72 queries inside InnoDB, 0 queries in queue
71 read views open inside InnoDB
Main thread id 2572, state: flushing log
Number of rows inserted 269699, updated 352837, deleted 170748, read 2807450339
9.11 inserts/s, 8.43 updates/s, 6.83 deletes/s, 64676.96 reads/s

There's really not enough information here to make any specific recommendations.

64676.96 reads/s seems pretty high for a 16 core server, and it seems disproportionately large compared to the number of inserts/updates/deletes.

I would guess (again, with a big grain of salt because I know nothing about your app, your database, or your queries) that this is so high because you have queries that are not optimized with indexes.

Creating the right indexes for your queries usually helps reduce the average rows examined per query (but of course this depends a lot on the types of queries you run).

You might like to read my presentation How to Design Indexes, Really or a video of me giving this presentation: https://www.youtube.com/watch?v=ELR7-RdU9XU

Re your comments:

In most cases, you can optimize with indexes without changing any queries. You can get the log of queries from MySQL without looking at any code in Jamf.

Tuning settings can be important, but only up to a point. You should read:

https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/


MySQL is trimming my numbers and I can't figure out why I've got a table with a bunch of decimal(12,2) fields that are supposed to hold money amounts.  For some reason which I can't figure out one of them is trimming the value when either inserted or updated to a whole number.  So if I enter in 2.2 into the PHP web form all that is saved is 2.00.  It's not rounding because if I put in 2.9 it still saves 2.00.

All the columns are updated with a single update statement to the table, and this is the only one with a problem.  For all the values all I'm doing is grabbing the value via $_POST['n'] and sticking the value into an array which I'm then passing to the WordPress $wpdb->query via $wpdb->prepare as shown below.

$wpdb->query(
    $wpdb->prepare($sql,$params)
);

If I go into phpmyadmin I can change the value and it'll take it.

The only difference between this column and the other columns is that this one was added to the table after the table was created, so it's farther to the right and was added via an ALTER TABLE command instead of being part of the CREATE table command.

If I dump the array from $params I can see the correct value, so it's being passed into the the database engine correctly (best I can tell).

I've tried changing the data type to decimal(12,3) and back but that didn't help any.  Anyone seen this before or have an idea where to look?

UPDATE:
As requested, the code to create the table.

        $sql = "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}timesheet` (
            timesheet_id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            start_date datetime NOT NULL,
            entered_date datetime NOT NULL,
            client_name mediumtext NOT NULL,
            project_name mediumtext NULL,
            monday_hours numeric(12,2) NOT NULL,
            tuesday_hours numeric(12,2) NOT NULL,
            wednesday_hours numeric(12,2) NOT NULL,
            thursday_hours numeric(12,2) NOT NULL,
            friday_hours numeric(12,2) NOT NULL,
            saturday_hours numeric(12,2) NOT NULL,
            sunday_hours numeric(12,2) NOT NULL,
            total_hours numeric(12,2) NOT NULL,
            monday_desc mediumtext NULL,
            tuesday_desc mediumtext NULL,
            wednesday_desc mediumtext NULL,
            thursday_desc mediumtext NULL,
            friday_desc mediumtext NULL,
            saturday_desc mediumtext NULL,
            sunday_desc mediumtext NULL,
            per_diem_days numeric(6,2) NULL,
            hotel_charges numeric(12,2) NULL,
            rental_car_charges numeric(12,2) NULL,
            tolls numeric(12,2) NULL,
            other_expenses numeric(12,2) NULL,
            other_expenses_notes longtext NULL,
            week_complete tinyint(1) NOT NULL,
            marked_complete_by bigint(20) NULL,
            marked_complete_date datetime NULL,
            approved tinyint(1) NOT NULL,
            approved_by bigint(20) NULL,
            approved_date datetime NULL,
            invoiced tinyint(1) NOT NULL,
            invoiced_by bigint(20) NULL,
            invoiced_date datetime NULL,
            invoiceid bigint(20) NULL,
            ClientId bigint(20) NULL,
            mileage bigint(20) NULL,
            EmbargoPendingProjectClose tinyint(1) NULL,
            project_complete tinyint(1) NULL,
            ProjectId bigint(20),
            PRIMARY KEY (timesheet_id),
            INDEX IX_user_id_start_date (user_id, start_date),
            INDEX IX_approved (approved),
            INDEX IX_invoiced (invoiced)
            ) $charset_collate";
        $wpdb->query($sql);

Then later on the new problem column was added.

        $sql = "ALTER TABLE `{$wpdb->prefix}timesheet`
            ADD COLUMN flight_cost decimal(12,2)";
        $wpdb->query($sql);

Sounds like your PHP code is converting the string to a integer and chopping off the decimal place. Possibly in the prepare statement. You could try binding the param as a string, or possibly a cast as a (float).

Example PHP:

  $number='2.90';
  echo (int)$number;

Will output 2

$number='2.90';
echo (float)$number;

Will output 2.9

I would just leave it as a %s string.


What happens when two separate processes perform a full backup against the same database? I have an instance of SQL Server 2012 RTM running. I have two separate applications that want to do a backup of a database lets call them "AppFull" and "AppFullCopyOnly".

AppFull does a full database backup followed by a truncate of the transaction log. AppFullCopyOnly also does a full database backup but with the COPY_ONLY flag so as not to interfere with the AppFull process.

So my question is: Will these applications interfere with each other if they are both processing the same database? I realise there will be an impact on the server, but what I need to know is whether either backup will not complete, integrity be compromised etc. I need to understand both scenarios namely they run in these orders:

AppFull and then AppFullCopyOnly
AppFullCopyOnly and then AppFull

PS. I cannot control when the applications run so that there is no collision and hence also not their order of execution. I also have no control over them both running - they both have to both run.

Will these applications interfere with each other if they are both processing the same database?

Yes they would affect each other, in terms of blocking, when running "concurrently", the one would block the other. But if they are not running concurrently like you schedule copy_only backup after full backup or before full backup, copy_only full backup is not going to affect full backup and you recovery planning. Basically copy_only full backup just backups up the data without changing the differential base and is not going to affect the restore procedure you have planned.

As a fact a lot people use copy_only backups to get replica of database when its required urgently without affecting the total backup and restore sequence.

Few points:

A full backup would block other concurrent full or copy only full backup.
A full backup would not block transaction log backup if you are using SQL server 2008 and above. But log will only truncate when full backup finishes. Paul has series about backup I suggest you read it.
A differential backup would block concurrent differential backup.
A log backup would block concurrent log backup.
If you are using SQL Server 2008 and above you can run transaction log backup when any of full or differential backup is running.

This blogs.msdn article has details

  but what I need to know is whether either backup will not complete, integrity be compromised etc. I need to understand both scenarios namely they run in these orders:

If you are running concurrently the one started first will block other and the other will finish after the fist one allows it to start. But at the end both will finish

If you are not running concurrently, both will just finish fine. Unless there is some other issue.

In both cases the integrity of backup is NOT compromised.


mysql to mongodb migration I have table, with around 125 million records in MySql. I want to move it to mongodb.

What would be the best approach to migrate this huge table to mongodb?

I was thinking to fetch one by one row using a nodejs application and create an array for bulk insert in mongodb. But I think it will take too much memory (Haven't check it though).

Export that table to JSON format (f.ex. MySQL Workbench) and use mongoimport to import it.


Table compression on 500GB table using Enterprise edition with online=on I am using MS SQL2016 enterprise edition and we have several tables that need to be compressed to save memory and backup space. Right now they are each around 500GB and I am trying to get some space to do test runs, but unfortunately we are not able to try this out. My question is first - I was thinking of using the Online=On option to allow the table to still be used while the compressing is happening, but how will that effect the backup? We have diff occurring 2x daily and a backup, with the table so large I imagine it will take days not hours to use the online approach. What have other done in this case, bit the bullet and take the table offline for 1.5 days over a weekend?

Agreed with everything sepupic said, especially reinforcing the fact you won't be saving space on backups if you're using standard compressed backups anyways.
I also wanted to specifically add you aren't guaranteed to save Memory either. Compression is a way to save space on Disk. When the data is loaded into Memory from a compressed table, it may first use CPU to decompress the data before loading it into Memory. Visa-versa when writing to a compressed Table as well (the CPU runs the compression algorithm on the decompressed data in Memory and hardens it to the compressed data on Disk). (This isn't always the case though, and in some cases it does stay compressed in Memory as well.)
Basically data compression is great for reducing how much Disk space is being consumed by a particular Table, and it helps reduce I/O bottlenecks from the Disk itself (which is usually the slowest piece of the hardware pie). That's about it for its main uses.
You can read more about this in this great article Compression And Its Effects On Performance.


Postgres View with content from another Databaseserver Is it somehow possible to create a view on a Postgres database which gets its original data from a table of a SQL Server database on another server?

I want to have the data from a table of the SQL Server database on server A inside a table on my Postgres database on server B, but I can't use export import, also no scripted solution because I need them nearly realtime.

You can use a FDW1 to connection on your MSSQL database and get your data. A FDW expose a remote service (a database, webservice, api, etc) like a table for read and write.

For MSSQL, the tds_fdw2can do the trick.

Let me know if you need a example or more details how the FDW work.

References:

https://wiki.postgresql.org/wiki/Foreign_data_wrappers
http://pgxn.org/dist/tds_fdw/


while other values of a column are NULL, easily you can use this query:

SELECT Name,MAX(property_1),MAX(property_2),MAX(property_3)
FROM YourTable
GROUP BY Name


Override default SERVICE_NAME in init.ora I'd like to define several service names for my instance in such a way that users should not know the actual instance name. This is what I have done in my init.ora:

db_domain='company.com'
db_name='tdbket'
instance_name='tdbket'
service_names=tedab+ket.company.com, tedab.company.com, ket.company.com

By definition, the default value DB_UNIQUE_NAME.DB_DOMAIN of the service_names attribute should be overridden.

Though, the Listener still lists this default value:

Dienst "ket.company.com" hat 1 Instanzen.
  Instanz "tdbket", Status READY, hat 6 Handler für diesen Dienst...
Dienst "tdbket.company.com" hat 1 Instanzen.
  Instanz "tdbket", Status READY, hat 6 Handler für diesen Dienst...
Dienst "tedab+ket.company.com" hat 1 Instanzen.
  Instanz "tdbket", Status READY, hat 6 Handler für diesen Dienst...
Dienst "tedab.company.com" hat 1 Instanzen.
  Instanz "tdbket", Status READY, hat 6 Handler für diesen Dienst...
Der Befehl wurde erfolgreich ausgeführt.

Why?

This is the default behaviour.
As long as you don't have the default service in your tns_aliasses that are in use it should not be a problem.


On Oracle 12c. is there any way to enable the audit trail on SELECT on whole schema of some users? Is there any way to enable the audit trail on SELECT on whole schema of some users?

I can only see there is example on auditing on certain table in a schema, but is there any way to audit on whole schema?

Please try the following:

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
ON <Schema_name>
BY ACCESS;

Using Oracle fine grain auditing we can achieve auditing of all statements of entire schema:

begin
    for c1 in (select * from all_tables where owner = '&schema') loop
        dbms_fga.add_policy (
            object_schema   => c1.owner,
            object_name     => c1.table_name,
            statement_types => 'SELECT,UPDATE,DELETE,INSERT',
            policy_name     => c1.table_name
        );
    end loop;
end;
/


Update large table with millions of rows in Azure SQL Server, in Parallel We have a large ProductOrders table with many rows, and plan to update it. The table has range of ProductType Ids from 1-1000.
Table has around 2 million rows, 15 columns. Common ProductOrders table: ProductOrderId, ProductId, ProductTypeid, ManufacturerId, OrderDate, etc, indexes are placed properly on foreign keys and common queries.
Two options are proposed to update the table,

Setup 10,000+ multiple queries (grouped by range of Product TypeIds) which are sent out in Parallel, and update the ProductOrders Table all at once.

Or the second proposition, is using following method. Update them sequentially proposed here to avoid locking up the whole table. https://stackoverflow.com/a/35931214/12425844

I would assume option 1) having multiple update queries can lead to multiple Writer-Writer Deadlocks.
Is it safe to have multiple queries sent in parallel? Which option is best practice? Additionally, thoughts it best practice to let Sql automatically conduct DDL items in parallel with configured Maxdop setting, rather than user attempting.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15
first option is being proposed by our Application software team .
Currently using AzureSQl.

Why not update the table with a single UPDATE statement?
If your database is set to READ COMMITTED SNAPSHOT and you can afford to block other sessions from writing to the table during the update, that's the simplest option.
Otherwise update them sequentially in batches (option 2).  Running lots of parallel DML statements (Option 1) is too complicated and too prone to blocking and deadlocking issues.  IE it's not going to solve all the problems of using a single UPDATE statement, and it introduces new ones.