Tag Info

New answers tagged

0

Assuming my comment about Archiving vs. Backup is accurate, go with System 2. You don't mention it in your post, but System 1 would require some sort of count operation on each insert into the sweepstakes_entries and sweepstakes tables. Initially the performance associated with the count will not be a problem. However, as your tables grow the ...


0

This is your query: Select vname.Value, vgender.value, vindustry.value from ABC [vname] with (nolock) join ABC [vgender] with (nolock) on vname.id = vgender.id join ABC [vindustry] with (nolock) on vgender.id = vindustry.id where vname.field = 'Name' and vgender.field = 'Gender' and vindustry.field = 'Industry'; You can try ...


1

Late post, but not a lot of good info available around multiple instances.. I have been advised by MS to install multiple instances of SQL server to work around spinlock problems due to Always On. It has not helped with push locks in Clustering Services though, as they sit below SQL Server instances.


0

I believe it's necessary to refer what kind of data your table is storing. Let's say that from C1-C10 you're keeping one group of data and it may be possible that the data in C1-C10 may be redundant (ie, it's possible that you may have the same row of data from C1-C10 throughout your Master Table). Hence, it's wasteful to have redundant data because you're ...


0

COM_SET_OPTION is a special command in the MySQL protocol which is currently used exclusively to enable or disable multi-statement support as detailed here. Some database clients (such as certain versions of the MySQL libraries for PHP) will do this every time a new connection is established.


1

No, please don't store this data redundantly - you already have all of the information you need in the base table, without incurring the overhead of triggers or additional writes. Just create an index that supports your query, somewhat similar to what @Colin suggested, but I'm adding the output columns to avoid key lookups: CREATE INDEX ix_last_position ...


0

Let me do a small summary here: The Average Disk Queue Length is about read and write operations on your hard disk. The goal is to minimize these operations, you want the data to be read from memory since it's way faster and efficient. The recommended value for RAM on an SQL Server is minimum 50% of your databse size. And of course you should reserve at ...


2

1. Table definition Better: CREATE TABLE test( test_id serial PRIMARY KEY, age INT, name text, data JSONB ); Due to alignment requirements of the types integer and varchar / text it's better to put the two int columns first and together. More: Configuring PostgreSQL for read performance Also, "age" is a dubious column to begin with. Normally ...


3

Don't go to DTA. It can have severe performance impacts in production and can really do more harm than good (I learned that lesson the hard way when I started out as a DBA). The first step is to find out what exactly is causing your performance issues. Is it a CPU issue? Memory? Improper indexes? Also, how do you know you are experiencing a performance ...


0

Don't do it. First eliminate the obvious - it's not bad drive on the SAN, there isn't filegrowth happening, indexes aren't fragemented badly... etc. This is a last gasp effort from a desperate man... you're not that guy :) Edit (append): Fair enough comment: Elaborate :) As DBA I mistrust running processes on an already labouring server that are ...


0

It sounds like you may have overflowed the RAM required for the working set for some of the denormalization operations. High CPU usage often means lots of page reads. Check the cache hit ratio as well as the logical page reads during the denormalization process. Somewhere around when a table gets larger than available memory (or the amount allowed by single ...


0

It's look like your reporting query lock for a while the tables needed by general queries. There is no general recipe for such cases and you have to refactor your reporting to avoid locking. Sometimes you can achieve this with splitting one big query into the series of smaller ones. Sometimes the good approach is to copy some tables for exclusive use by ...


0

Have you checked your actual rows vs estimated rows in the actual execution plan? Also, have you checked the reads from your query compared to the reads from a table made up of just the fields you are selecting on? Without even seeing any of that I would say you definitely need a non-clustered index. My guess is either one or both of the following is ...


1

From the output the two inserts are doing gap locking on the index index_shipments_on_ci_ai_tn_sti. Without knowing the table definition, I think the index is a UNIQUE index. Here is a decent blog explaining the reason UNIQUE constraints can cause deadlocks on concurrent inserts. Snippet here: Mysql innodb engine performs row locking on inserts. If ...


-3

Actually when we use the where clase then sql creates seperate pages for each where clause. then its make join based on AND , OR and then produce the output. So, here switching the where clause is not a matter.


0

Use DISTINCT everywhere you can and I don't understand why not to join instead of correlating SELECT psc FROM pso_history AS c WHERE ( c.project = 121 OR c.project IS NULL ) AND EXISTS (SELECT * FROM pso_history AS x ...


3

The question, as Remus pointed out, is too generic to get an answer as the answer depends on the context of what functionality is to be used and how will it be used. Regarding "Security": If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = SAFE, then there aren't any issues that I have ever been able to find. And ...


3

SQLCLR assemblies can be installed with three levels of security access: SAFE | EXTERNAL_ACCESS | UNSAFE. This is amply documented, refer to CREATE ASSEMBLY and Designing Assemblies: Managing Assembly Security You can control how much an assembly can access resources protected by .NET Code Access Security when it runs managed code. You do this by ...


0

As you can see, the process that takes most time is "query end". There is an interlocking problem when multiple threads want to write the file at the same time, this way the log will be flushed every second: innodb_flush_log_at_trx_commit = 0 in the /etc/my.cnf file Also, you should try Optimize InnoDB as explained above for future query improvement.


-3

How about: select exists(T.B is null) as 'B is null', exists(T.C is null) as 'C is null' from T; If this works (I haven't tested it), it would yield a one-row table with 2 columns, each one either TRUE or FALSE. I didn't test the efficiency.


1

I am a SQL Server DBA - I am biased towards SQL Server. However if the budget is limited, I would suggest trying a mature open source database, perhaps MySQL, PostGreSQL or MariaDB. Performance isn't your real issue here. The security of your data and the safety of strong ACID transactions are what you should really prioritise. How are you going to backup ...


1

SQL Server Compact is mostly used for building standalone and occasionally connected applications for mobile devices, desktops, and Web clients. There are lots of features not supported in SQL Server Compact check differences and with lack of these features hosting provider with exceptional bandwidth, up-time, etc will not help in terms of performance and ...


1

I have not used SQL Server Compact, but because of its design, there are warnings in the SQL Server 2012 documentation about concurrency issues. See: http://technet.microsoft.com/en-us/library/ms171845(v=sql.110).aspx for warnings on: Lost updates. Inconsistent analysis (nonrepeatable reads). Phantom reads. If you need a free and much more functional ...


0

Just changing the recovery model can take quite a while, depending on the activity in the database. If you are running a denormalization process, I'm guessing you set the database to read/write as part of the process? Then you would set it back to read only afterwards. One reason you could be seeing the overall process running much slower is that your ...


0

I would also say it depends on the volume and what you want to do. Does your graph need to use real time data ? and does it use all data from these 6 databases ? In some situation a classic ETL (extract,transform,load) to a new database supporting heavy queries is the answer If you need quasi-real time data: Replicating heterogeneous databases is not easy, ...


1

You say Then we gather statistics on the table (and implicitly on the index): but statistics on indexes aren't gathered unless you do this: dbms_stats.gather_table_stats(null,'PART',cascade=>true); What appears to be happening is index statistics are not being gathered (or not gathered correctly) on 10.2.0.5; if specifying cascade=>true ...


0

You have to refer Percona tool for configure my.ini file. go to following link http://www.percona.com/resources/percona-tools-for-mysql find Create a MySQL Configuration sign in or create account.It will create my.ini file according to your system requirement.


2

You can test this variation. In theory, it would use an (id, value) index to find the min and max and would not have to count the distinct values at all: SELECT id FROM t GROUP BY id HAVING MIN(value) < MAX(value) ;


3

On principle, I would always use EXISTS rather than IN. EXISTS has the opportunity to short circuit, though it won't always. IN has the opportunity to be optimized as an EXISTS, though it won't always. So, since EXISTS can never be worse than an equivalent IN, but can sometimes be better...


4

Exists and IN will give the same query plan because they are both "LEFT SEMI JOINs" internally. I prefer EXISTS generally because of consistency. NOT EXISTS is safer then NOT IN. Caching does not apply and don't think about it. This is a set based operation and the SQL Server optimiser will work out the best way: It may spool the subquery or it may not. ...


1

Assuming that the app will be treating these various shop-types in the same manner for some operations (operations that do not care what type of shop that it is), then my preferred approach is to use a subclass / inheritance model. I have detailed this out in a couple of other answers here: Don't know how to transform variable entity into relational ...


1

The amount of memory specified in innodb_buffer_pool_size is allocated when the MySQL Server daemon starts up, and if the system doesn't have that much memory free at that moment, MySQL won't start. The amount of memory you specify is what the buffer pool gets, and it neither grows nor shrinks. InnoDB reads pages from disk into the pool as they are ...


2

Before I start, I am proponent of FIXING MAX SERVER MEMORY always 1. I will first address SQL Server versions before SQL Server 2012. That means if you are using SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 (no I will not include SQL Server 2000) If you are using SQL Server version before 2012 and your operating system is Windows Server 2008 R2 ...


2

Generally speaking, no, there is no detrimental effect from maxing out memory once the OS and other applications have been accounted for. This is exemplified through the default settings of SQL Server: The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB. SQL Server Memory Configurations (MSDN) ...


0

You should test it on you system and compare the the execution data and plans. But the database systems I know only optimize statement by statement and not pairs and groups of statements. And for an optimal optimizer that possesses enough information about the data and optimizes statement by statement I would expect that he can create a better plan if the ...



Top 50 recent answers are included