Tag Info

Hot answers tagged

5

The addition of the redundant predicate can make a difference in SQL Server. In the execution plans below notice the @1 in the first plan vs the literal 'foo' in the second plan. This indicates that SQL Server considered the first query for simple parameterisation to promote execution plan reuse - however the comparison of two constants prevents this ...


5

There is an easy solution if you have Enterprise (expensive) Edition in use: Set maxdop at instance level as high as you want and use the resource governor to restrict all CRM users with their own workload group to maxdop 1.


4

There is no hidden impact in raising the cost threshold for parallelism. As low as it is, SQL Server ships with a default cost threshold of 5, which is always taken into account when the optimizer compiles query plans. So, in this regard, the amount of work for the optimizer is the same. Whether raising the CTFP to a very high value is a good idea or not, ...


4

For Microsoft Dynamics CRM database server instance, you have to set MAXDOP 1. If using Enterprise edition, above answer using Resource governor makes sense. You can also look into using query hint OPTION (MAXDOP N) where N is a sensible value calculated based on number of logical processors and NUMA nodes of your sql server instance. Make sure that ...


4

The latest version of the RML Utilities (09.04.0051 as at today) support Extended Events tracing, so maybe you could work with your DBAs to do some controlled tracing. A screenshot from the help file: They do however have a point because the default trace template provided with RML includes statement-level events (SQL:StmtStarting, SQL:StmtCompleted, ...


3

If you are looking for significant performance improvements to dnoeth's answer, consider using a native C-function and creating the appropriate operator. Here is an example for int4 arrays. (A generic array variant and the corresponding SQL script). Datum _int_sequence_contained(PG_FUNCTION_ARGS) { return DirectFunctionCall2(_int_contains_sequence, ...


2

You should perform an UPDATE LEFT JOIN and set enabled based on the right side being NULL UPDATE table1 A LEFT JOIN table2 B ON A.code = B.id SET A.enabled = 1 - ISNULL(B.id); Why should this work ? If ISNULL(B.id) is 0, that means A.enabled is set to 1 (1 - 0) because it is in table2 If ISNULL(B.id) is 1, that means A.enabled is set to 0 (1 - 1) ...


2

If these are quite large databases I would ask if they are on different physical disks, with one disk significantly slower or busier than the other? (i.e. one is SSD, and the other is a standard HDD). For smaller databases though, the data will quickly be cached, and so speed of the disk would stop being relevant at that point. I'd suggest turning on ...


2

I would recommend taking a look at the resource governor... In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU, physical IO, and memory. By using ...


1

On your indices Ok, first things first. Assuming some structure like this { _id: new ObjectId(), date: new ISODate(), message: "Hello, Multikey Indices!", tags: ["MongoDB","Indices","Multikey"] } indexing tags would result in a multikey index. For the document above, the index would have three entries: "MongoDB", "Indices" and "Multikey", all ...


1

With MyISAM, an INSERT/UPDATE/DELETE will request an exclusive lock, and have to wait for any SELECTs to finish. If another SELECT comes in after the write, then it will be blocked waiting for the write to get its lock and finish its action. That is, one simple write can snowball into the mess you are seeing. Changing to InnoDB is likely to avoid the ...


1

Community Wiki answer generated from comments on the question, from Bob Klimes, Sean Gallardy, and Aaron Bertrand. You'll need to enable Instant File Initialization (IFI) on the server holding the disk. Restart SQL Server for it to take effect. You can test if IFI is working on the share by creating a database on the share with trace flags 3004 and 3605 ...


1

Community Wiki answer generated from comments on the question by Aaron. Unless you are having performance issues, the high percentage of CXPACKET waits may only be an indicator that a large portion of the queries are going parallel and not actually a problem. What is the CXPACKET wait type and how do you reduce it? High CPU could be a indicator, but ...


1

SELECT u.usrid, u.username, u.rank, u.extras, x.dtime FROM ( SELECT userid, MAX(datetime) dtime FROM log GROUP BY userid HAVING dtime < NOW() - INTERVAL 1 YEAR ) AS x JOIN users u ON u.usrid = x.userid WHERE ( u.rank = 'P' OR u.extras LIKE '%W%' ) ORDER BY ...


1

I must say this is normal and nothing to worry about. This blog tells how target server memory calculated. Please see the forumula below Target1 = Current committed pages of SQL Server + ( Available Physical Memory - min (Total Physical Memory Pages / 20, Available Physical Memory Pages / 2)) ullAvailPageFile: The maximum amount of memory the ...


1

My reason why to not to use innodb_file_per_table is performance. I did some tests for our database with 450 tables on mysql 5.5.45 Linux CentOS release 6.7 For unit tests which inserts fixtures into the database before each test (not using all tables everytime) and also tests itself does work with database a lot (inserts, update, deletes, selects) the ...


1

You have PK clustered index as a uniqueidentifier? That index has massive fragmentation. You want a PK that is inserted in the order of the PK Why not just use an identity? Why are are you updating a value rather than just insert the correct value? Are you inserting just one row at a time? One row one round trip is not efficient. Fast inserts with ...


1

For SQL Server, using a similar example with a clustered index on the Id, the plans both use a "Clustered Index Seek" which is a broad explanation for what may happen internally. select ObjectId from oav.Object where ObjectId in (3105680,3105681,3105682,3105683,3105684,3105685,3105686,3105687,3105688,3105689) select ObjectId from oav.Object where ...


1

we have run into one point of contention between developers and dba's: they will not run traces against production database servers. It depends on what you asking your DBAs to trace (I am only talking about server side trace). We have a 24x7 trace running which captures minimal stuff which helps us in troubleshooting specific scenarios e.g. User error ...


1

Has anyone done an research as to the overhead created by enabling AWE on the 64-bit OS/SQL combination? This is one of the Myths that Paul Randal clearly debunks in his famous series - A SQL Server DBA myth a day On 64-bit systems (2005+): AWE is not required (and in fact enabling it does nothing). Also refer to Fun with Locked Pages, AWE, Task ...



Only top voted, non community-wiki answers of a minimum length are eligible